Chapter Four – Data Modeling and the Entity-Relationship Model
Page 37 of 52
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 38 of 52
C. Describe how you would go about validating the model in part B.
This model assumes single owners of a property and that an owner may have multiple
properties. SUB-PROPERTY is an ID-dependent weak entity in relation to PROPERTY. We
assume only one employee performs a service, but that many services can be done by an
employee. For accounting purposes, a SERVICE pertains to only one SUB-PROPERTY. We also
assume that a TRAINING_COURSE pertains to one kind of EQUIPMENT, but that the EQUIPMENT
is discussed in many training courses. EQUIPMENT_REPAIR is modeled as a weak entity, but one
could argue that it ought not to be. We model it as weak because how can you repair
something that doesn’t exist? But such repairs typically do have a good identifier, something
like RepairInvoiceNumber, which may make users think of them as strong entities.
Validation of all cardinality assumptions should be done with the users. In this case, we could
try to teach them enough E-R to understand this diagram. If that turns out not to be possible,
we would build a prototype of two or three relationships and verify those. Then, try again to
teach the users enough E-R to validate the rest. If that’s not successful, then a full prototype
would be built and demonstrated, or PowerPoint slides of screen shots of the prototype could
be used.
ANSWERS TO JAMES RIVER JEWELRY PROJECT QUESTIONS
[NOTE: The James River Jewelry Project Questions are available online for Appendix D,
which can be downloaded from the textbook’s Web site:
www.pearsonhighered.com/kroenke. The solutions for these questions will be included
in the Instructor’s Manual for each chapter]
James River Jewelry wants to expand its database applications beyond the recording of
purchases and purchase awards. (See the description of the award program in the
James River Jewelry Project Questions for Chapter 1 that is online in Appendix D.) The
company still wants to maintain data on customers, purchases, and awards, but it wants
to include other data as well. Specifically, James River Jewelry wants to record artists
and styles and keep track of which customers are interested in which artists and styles.
James River Jewelry sells most of its jewelry on consignment, so the company does not
pay the artist for a piece of jewelry until it is sold. Typically, the company pays artists 60
percent of the sales price, but the terms are negotiated separately for each item. For
some items, the artists earn a larger percentage and for others they earn less. Artists
and James River Jewelry personnel agree on the initial sales price at the time the item is
brought to the shop. When an item has been in the shop for some time, James River
Jewelry may reduce the price; sometimes it renegotiates the sales percentage.
A. Draw an E-R data model for the James River Jewelry database schema shown in the
James River Jewelry Project Questions for Chapter 3 in this Appendix. Use the IE
Crow’s Foot E-R model for your E-R diagrams. Justify the decisions you make regarding
minimum and maximum cardinality.
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 39 of 52
Note that foreign keys are not shown in data models; they are added in the database design
phase. Therefore, the foreign keys shown in the James River Jewelry database schema in
Chapter 3 are not in the E-R diagram! The only exception is where the foreign key must be part
of the identifier of an ID-dependent weak entity. The diagrams for this project were drawn using
Visio 2016. Note that Visio uses the term PK but we use the term identifier in ER diagrams.
The E-R Crow’s Foot model above has the following cardinalities:
RELATIONSHIP
CARDINALITY
PARENT
CHILD
TYPE
MAX
MIN
CUSTOMER
PURCHASE
Strong
1:N
M-O
PURCHASE
PURCHASE_ITEM
ID–
Dependent
1:N
M-M
ITEM
PURCHASE_ITEM
Strong
1:N
M-O
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 40 of 52
We have decided that:
B. Extend and modify the E-R data model to show James River Jewelry’s award program.
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 cardinality.
Description of the awards program from Appendix D, Chapter 1 Questions:
James River JeweIry has a small but loyal clientele, and it wants to further increase customer
loyalty by creating a frequent buyer program. In this program, after every 10 purchases, a
customer will receive a credit equal to 50 percent of the average of his or her 10 most recent
purchases. This credit must be applied to the next (or 11th) purchase.
We will assume that James River wants to pay awards on the basis of the sum of the pre-tax
amounts of 10 purchases and not of 10 purchase items. Therefore, from the perspective of
awards, it is important to track purchases as a collection of purchase items. See the E-R diagram
on the next page.
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 41 of 52
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 42 of 52
The E-R Crow’s Foot model above has same cardinalities as in part A plus:
RELATIONSHIP
CARDINALITY
PARENT
CHILD
TYPE
MAX
MIN
CUSTOMER
AWARD
Non-ID–
Dependent
Weak
1:N
M-O
AWARD
PURCHASE
Strong
1:10
M-M
We have decided that:
• One customer can earn many awards, but customers do not have to earn awards and
CUSTOMER data may be entered into the database before any purchase is made or points
are earned for an award.
C. Extend and modify the E-R data model in part B to meet James River Jewelry’s new
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 cardinality.
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 43 of 52
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 44 of 52
The E-R Crow’s Foot model above has the same cardinalities as in parts A and B plus:
RELATIONSHIP
CARDINALITY
PARENT
CHILD
TYPE
MAX
MIN
CUSTOMER
ARTIST
ID–
Dependent
on both
identifiers
N:M
O-O
CUSTOMER
STYLE
ID–
Dependent
on both
identifiers
N:M
O-O
ARTIST
STYLE
ID–
Dependent
on both
identifiers
N:M
O-O
ARTIST
ITEM_TERMS
Non-ID–
Dependent
1:N
M-O
ITEM
ITEM_TERMS
Non-ID–
Dependent
1:N
M-M
ITEM
STYLE
Strong
1:1
O-M
We have decided that:
• One customer may be interested in many artists, and one artist can be of interest to many
customers. However, a customer does not have to be interested in an artist, and an artist
does not have to be of interest to any customers.
• One customer may be interested in many styles, and one style can be of interest to many
customers. However, a customer does not have to be interested in a style, and a style does
not have to be of interest to any customers.
• One artist may work many styles, and pieces in one style may be created by many artists.
However, an artist does not have to work in a style, and a style does not have to be
represented by any artist.
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 45 of 52
D. Describe how you would go about validating the data model in part C.
As with Garden Glory, it might be possible to talk the users through this E-R diagram. If not,
prototypes or demo screen pictures will need to be used. Jumping ahead a bit, all of the N:M
relationships will require intersection tables; this will make the applications harder to code and
ANSWERS TO 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.
Currently, each item is considered unique, which means that the item must be sold as a
whole, and that multiple units of the item in stock must be treated as separate items in
the ITEM table. The Queen Anne Curiosity Shop management wants the database
modified to include an inventory system that will allow multiple units of an item to be
stored under one ItemID. The system should allow for a quantity on hand, a quantity on
order, and an order due date. If the identical item is stocked by multiple vendors, the
item should be orderable from any of these vendors. The SALE_ITEM table should then
include Quantity and ExtendedPrice columns to allow for sales of multiple units of an
item.
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 that foreign keys are not shown in data models; they are added in the database design
phase. Therefore, the foreign keys shown in the QACS database schema in Chapter 3 are not in
Chapter Four – Data Modeling and the Entity-Relationship Model
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
EMPLOYEE
SALE
Strong
1:N
M-O
SALE
SALE_ITEM
ID–
Dependent
1:N
M-M
ITEM
SALE_ITEM
Strong
1:1
M-O
VENDOR
ITEM
Strong
1:N
M-O
SALE_ITEM is
ID-Dependent
on SALE
ITEM
ItemID
ItemDescription
PurchaseDate
ItemCost
ItemPrice
SALE_ITEM
SaleID
SaleItemID
ItemPrice
CUSTOMER
CustomerID
LastName
FirstName
Address
City
State
ZIP
Phone
Email
SALE
SaleID
SaleDate
SubTotal
Tax
Total
VENDOR
VendorID
CompanyName
ContactLastName
ContactFirstName
Address
City
State
ZIP
Phone
Fax
Email
EMPLOYEE
EmployeeID
LastName
FirstName
Phone
Email
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 47 of 52
We have decided that:
• 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.
• One employee can make many sales, and EMPLOYEE data may be entered into the database
before any associated sale is made or data for that sale is entered. However, every sale
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
ITEM
SALE_ITEM
Strong
1:N
M-O
ITEM
ORDER
Strong
1:N
M-O
VENDOR
ORDER
Strong
1:N
M-O
Chapter Four – Data Modeling and the Entity-Relationship Model
We have decided that:
• One item (as we now have a quantity of the item) can now be sold as a part of many sales.
• One item can be ordered on one or more orders, and ITEM data may be entered into the
database before any associated order is placed or data for that order entered. However,
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 49 of 52
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
attributes for each entity. Justify the decisions you make regarding minimum and
maximum cardinalities.
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
PERSON
CUSTOMER
ID–
Dependent
Inclusive
Subtype
1:N
M-O
PERSON
EMPLOYEE
ID–
Dependent
Inclusive
Subtype
1:N
M-O
We have decided that:
• One person can be a customer, an employee, or both. PERSON, CUSTOMER, and EMPLOYEE
data may be entered into the database before any associated purchase is made or data for
that purchase entered.
Chapter Four – Data Modeling and the Entity-Relationship Model
SALE_ITEM is
ID-Dependent
on SALE
ITEM
ItemID
ItemDescription
PurchaseDate
ItemCost
ItemPrice
SALE_ITEM
SaleItemID
ItemPrice
PERSON
PersonID
isEmployee
isCustomer
LastName
FirstName
Address
City
State
ZIP
Phone
Email
SALE
SaleID
SaleDate
SubTotal
Tax
Total
VENDOR
VendorID
CompanyName
ContactLastName
ContactFirstName
Address
City
State
ZIP
Phone
Fax
Email
CUSTOMER
CreditCardType
CreditCardNumber
CreditCardExpirationDate
EMPLOYEE
DateOfHire
HourlyPayRate
Chapter Four – Data Modeling and the Entity-Relationship Model
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.
SALE_ITEM is
ID-Dependent
on SALE
ITEM
ItemID
ItemDescription
ReorderPointQuantity
QuantityOnHand
ItemPrice
SALE_ITEM
SaleID
SaleItemID
Quantity
ItemPrice
ExtentedPrice
SALE
SaleID
SaleDate
SubTotal
Tax
Total
VENDOR
VendorID
CompanyName
ContactLastName
ContactFirstName
Address
City
State
ZIP
Phone
Fax
Email
ORDER
InvoiceNumber
DateOrdered
DateReceived
QuantityOrdered
ItemCost
OrderSubTotal
OrderTax
OrderTotalCost
CUSTOMER
PersonID
CreditCardType
CreditCardNumber
CreditCardExpirationDate
EMPLOYEE
PersonID
DateOfHire
HourlyPayRate
PERSON
PersonID
isEmployee
isCustomer
LastName
FirstName
Address
City
State
ZIP
Phone
Email
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 52 of 52
E. Describe how you would go about validating your data model in part D.