Chapter Five – Database Design
Chapter Five – Database Design
Chapter Five – Database Design
B. Describe how you have represented weak entities, if any exist.
PURCHASE_ITEM is an ID-dependent weak entity in relation to PURCHASE and has the key of
PURCHASE added to it as part of its primary key.
AWARD is a non-ID-dependent weak entity in relation to CUSTOMER. The key of CUSTOMER is
added to it as a foreign key.
ITEM_TERMS is an ID-dependent weak entity in relation to ITEM and ARTIST. The keys of ITEM
and ARTIST are added to it as foreign keys.
C. Describe how you have represented supertype and subtype entities, if any exist.
None exist.
D. Create an IE Crow’s Foot E-R diagram similar to the one in Figure 5-27.
See the E-R diagram on the following page.
E. Document referential integrity constraint enforcement, using Figure 5-29 as a guide.
See the table following the E-R diagram.
F. Document any business rules that you think might be important.
Chapter Five – Database Design
G. Describe how you would validate that your design is a good representation of the data
model on which it is based.
Chapter Five – Database Design
Relationship
Referential Integrity Constraint
Cascading Behavior
PARENT
CHILD
ON UPDATE
ON DELETE
CUSTOMER
PURCHASE
CustomerID in PURCHASE must
exist in CustomerID in
CUSTOMER
NO
NO
PURCHASE
PURCHASE_ITEM
InvoiceNumber in
PURCHASE_ITEM must exist in
InvoiceNumber in PURCHASE
YES
YES
ITEM
PURCHASE_ITEM
ItemNumber in
PURCHASE_ITEM must exist in
ItemNumber in ITEM
YES
YES
CUSTOMER
AWARD
CustomerID in AWARD must
exist in CustomerID in
CUSTOMER
NO
YES
AWARD
PURCHASE
AwardID in PURCHASE must
exist in AwardID in AWARD
YES
YES
STYLE
ITEM
StyleID in ITEM must exist in
StyleID in STYLE
NO
NO
ITEM
ITEM_TERMS
ItemNumber in ITEM_TERMS
must exist in ItemNumberID in
ITEM
YES
NO
ARTIST
ITEM_TERMS
ArtistID in ITEM_TERMS must
exist in ArtistID in ARTIST
NO
NO
CUSTOMER
CUSTOMER_ARTIST_INT
CustomerID in
CUSTOMER_ARTIST_INT must
exist in CustomerID in
CUSTOMER
NO
YES
ARTIST
CUSTOMER_ARTIST_INT
ArtistID in
CUSTOMER_ARTIST_INT must
exist in ArtistID in ARTIST
NO
YES
CUSTOMER
CUSTOMER_STYLE_INT
CustomerID in
CUSTOMER_STYLE_INT must
exist in CustomerID in
CUSTOMER
NO
YES
STYLE
CUSTOMER_STYLE_INT
StyleID in
CUSTOMER_STYLE_INT must
exist in StyleID in STYLE
NO
YES
ARTIST
ARTIST_STYLE_INT
ArtistID in ARTIST_STYLE_INT
must exist in ArtistID in ARTIST
NO
YES
STYLE
ARTIST_STYLE_INT
StyleID in ARTIST_STYLE_INT
must exist in StyleID in STYLE
NO
YES
Chapter Five – Database Design
ANSWERS TO THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS
Convert the data model you constructed for the Queen Anne Curiosity Shop in part D at
the end of Chapter 4 (or an equivalent data model that your instructor provides for you to
use) into a relational database design for the Queen Anne Curiosity Shop. Document
your database design as follows.
A. Specify tables, primary keys, and foreign keys. Using Figures 5–26 and 5-28 as guides,
specify column properties.
See the E-R diagram in part D. See MySQL Workbench file DBC-e08-CH05-QACS.mwb.
• EMPLOYEE could be N:M to SALE.
Discuss such possibilities with your students.
PERSON (PersonID, isEmployee, isCustomer, LastName, FirstName, Address, City, State, ZIP,
Phone, Email)
CUSTOMER (PersonID, CreditCardType, CreditCardNumber, CreditCardExpirationDate)
EMPLOYEE (PersonID, DateOfHire, HourlyPayRate)
SALE (SaleID, CustomerPersonID, EmployeePersonID, SaleDate, SubTotal, Tax, Total,)
Chapter Five – Database Design
Chapter Five – Database Design
Chapter Five – Database Design
Chapter Five – Database Design
Chapter Five – Database Design
B. Describe how you have represented weak entities, if any exist.
C. Describe how you have represented supertype and subtype entities, if any exist.
D. Create a visual representation of your database design as an IE Crow’s Foot E–R
diagram similar to the one in Figure 5-27.
See the E-R diagram on the following page.
E. Document referential integrity constraint enforcement, using Figure 5-29 as a guide.
See the table following the E-R diagram.
F. Document any business rules that you think might be important.
G. Describe how you would validate that your design is a good representation of the data
model on which it is based.
Chapter Five – Database Design
Chapter Five – Database Design
Relationship
Referential Integrity
Constraint
Cascading Behavior
PARENT
CHILD
ON UPDATE
ON DELETE
PERSON
CUSTOMER
PersonID in CUSTOMER
must exist in PersonID in
PERSON
NO
YES
PERSON
EMPLOYEE
PersonID in EMPLOYEE
must exist in PersonID in
PERSON
NO
YES
CUSTOMER
SALE
CustomerPersonID in SALE
must exist in PersonID in
CUSTOMER
NO
NO
EMPLOYEE
SALE
EmployeePersonID in
EMPLOYEE must exist in
PersonID in EMPLOYEE
NO
NO
SALE
SALE_ITEM
SaleID in SALE_ITEM must
exist in SaleID in SALE
NO
YES
ITEM
SALE_ITEM
ItemID in SALE_ITEM must
exist in ItemID in ITEM
NO
NO
ITEM
ORDER
ItemID in ORDER must exist
in ItemID in ITEM
NO
NO
VENDOR
ORDER
VendorID in ORDER must
exist in VendorID in VENDOR
NO
NO