Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-73
❖ THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS
The Queen Anne Curiosity Shop wants to expand its database applications beyond the
current recording of sales. The company still wants to maintain data on customers,
employees, vendors, sales, and items, but it wants to (a) modify the way it handles
inventory, and (b) simplify the storage of customer and employee data.
The Queen Anne Curiosity Shop management has noticed that some of the fields in
CUSTOMER and EMPLOYEE store similar data. Under the current system, when an
employee buys something at the store, his or her data has to be reentered into the
CUSTOMER table. The managers would like to have the CUSTOMER and EMPLOYEE
tables redesigned using subtypes.
A. Draw an E-R data model for the Queen Anne Curiosity Shop database schema shown in
Chapter 3’s “The Queen Anne Curiosity Shop Project Questions.” Use the IE Crow’s
Foot E-R model for your E-R diagrams. Justify the decisions you make regarding
minimum and maximum cardinalities.
NOTE: The schema referred to here is actually presented in Chapter 2, not Chapter 3.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-74
The E-R Crow’s Foot model above has the following cardinalities:
RELATIONSHIP
CARDINALITY
PARENT
CHILD
TYPE
MAX
MIN
CUSTOMER
SALE
Strong
1:N
M-O
VENDOR
ITEM
Strong
1:N
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-75
We have decided that:
o One customer can make many purchases (expressed in this model by the SALE entity), and
CUSTOMER data may be entered into the database before any associated purchase is made
or data for that purchase is entered.
B. Extend and modify the E-R data model by adding only the Queen Anne Curiosity Shop’s
inventory system requirements. Use the IE Crow’s Foot E-R model for your E-R
diagrams. Create appropriate identifiers and attributes for each entity. Justify the
decisions you make regarding minimum and maximum cardinalities.
Now we add in the ability to handle non-unique items. We also allow for the possibility of
obtaining an identical item from more than one vendor. The E-R diagram is on the next page.
The E-R Crow’s Foot model has the same cardinalities shown for part A plus:
RELATIONSHIP
CARDINALITY
PARENT
CHILD
TYPE
MAX
MIN
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-76
We have decided that:
o One item (as we now have a quantity of the item) can now be sold as a part of many sales.
o Item cost is now in ORDER, so it can vary from one order to another.
C. Extend and modify the E-R data model by adding only the Queen Anne Curiosity Shop’s
need for more efficient storage of CUSTOMER and EMPLOYEE data. Use the IE
Crow’s Foot E-R model for your E-R diagrams. Create appropriate identifiers and
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-77
RELATIONSHIP
CARDINALITY
PARENT
CHILD
TYPE
MAX
MIN
PERSON
CUSTOMER
ID–
Dependent
Inclusive
Subtype
1:N
M-O
We have decided that:
o One person can be a customer, an employee, or both. PERSON, CUSTOMER, and
PERSON
EMPLOYEE
ID–
Dependent
Inclusive
1:N
M-O
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-78
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-79
D. Combine the E-R data models from parts B and C to meet all the Queen Anne Curiosity
Shop’s new requirements, making additional modifications as needed. Use the IE Crow’s
Foot E-R model for your E-R diagrams.
E. Describe how you would go about validating your data model in part D.
One approach would be to develop some basic forms/reports describing the data model. These
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-80
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-81
❖ ANSWERS TO MORGAN IMPORTING PROJECT QUESTIONS
James Morgan of Morgan Importing has decided to expand his business and needs to staff and
support a procurement system to acquire the items sold at Morgan Importing. Suppose that you
have been hired to create and implement a database application to support a procurement
information system. Data in this procurement information system will include:
• The purchasing agents employed at Morgan Importing
• The receiving clerks employed at Morgan Importing
James Morgan and his wife Susan often make purchases themselves while traveling to various
countries (and therefore, even though they are not purchasing agents per se, they need to be
listed as purchasing agents in the system when data is entered). Purchases may be made at
the stores themselves or by Internet or phone. Sometimes several items are purchased from a
store on a single visit, but do not assume that all of the items are placed on the same shipment.
Shipping must track each item in a shipment and assign a separate insurance value to each
item. Receiving must track the arrival date and time of a shipment, who accepted receipt of the
shipment on behalf of Morgan Importing, and the condition of each item upon receipt.
A Using your knowledge, create a data model for the Morgan Importing procurement
information system. Name each entity, describe its type, and indicate all attributes and
identifiers. Name each relationship, describe its type, and specify minimum and
maximum cardinalities.
Chapter Five – Data Modeling with the Entity-Relationship Model
B List any item in your answer to A that you believe should be checked out with James
Morgan and/or his employees.
NOTE: VARIABLE ANSWER – Your students will probably create different sets of
assumptions.
Right now an item is purchased once, at a specific store (that decision is based on the database
design for MI in Chapter 2). Is that realistic? Are there other kinds of employees the Morgans
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-83
In addition to the above, in Chapters 3 and 4, we considered a number of possible functional
dependencies and multivalued dependencies. These could form the basis for many questions:
(1) What cities do you ship to (re SHIPMENT.Destination)?
(2) How many stores can exist in each city? (Checking for multivalued dependency
(3) Is each combination of City and Store Name unique? [Checking the composite
(4) Is each Email address unique? (Checking the identifiersSEMPLOYEE.EMailAddress,
(5) How many contacts are there per store? (Checking for multivalued dependencies
(6) How many phone numbers are there per store? (Checking for multivalued dependencies
(7) How many email addresses are there per store? (Checking for multivalued dependencies
(8) How many fax numbers are there per store? (Checking for multivalued dependencies
(9) How many contacts are there per shipper? (Checking for multivalued dependency
(10) How many phone numbers are there per shipper? (Checking for multivalued
(11) How many email addresses are there per shipper? (Checking for multivalued
(12) How many fax numbers are there per shipper? (Checking for multivalued
(13) Is the same item available from more than one store? (Checking for multivalued
dependencies StoreSK →→ ITEM.(ItemDescription, Category) and (City, StoreName)
If there are multivalued or functional dependencies that violate a desired normal form, then the
design will have to be revised to move them into their own entities (see the Chapter 3 and Chapter
4 Morgan Importing project questions).