Chapter Five – Database Design
ANSWERS TO GARDEN GLORY PROJECT QUESTIONS
Convert the data model you constructed for Garden Glory in part B 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 Garden Glory. 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-GG.mwb.
Discuss such possibilities with your students.
OWNER (OwnerID, OwnerName, OwnerEmailAddress, OwnerType)
OWNED_PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, State, ZIP,
OwnerID)
SUB_PROPERTY (PropertyID, SubPropertyID, SubPropertyType, SubPropertyName,
Description)
EMPLOYEE (EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel)
Chapter Five – Database Design
© 2018 Pearson Education, Inc. Page 42 of 66
Chapter Five – Database Design
© 2018 Pearson Education, Inc. Page 43 of 66
Chapter Five – Database Design
© 2018 Pearson Education, Inc. Page 44 of 66
Chapter Five – Database Design
© 2018 Pearson Education, Inc. Page 45 of 66
Chapter Five – Database Design
© 2018 Pearson Education, Inc. Page 46 of 66
Chapter Five – Database Design
B. Describe how you have represented weak entities, if any exist.
SUB_PROPERTY is an ID-dependent weak entity in relation to OWNED_PROPERTY and has the
key of PROPERTY added to it as part of its primary key.
PROPERTY_SERVICE is an ID-dependent weak entity in relation to SUB_PROPERTY and
EMPLOYEE, and has the keys of SUB_PROPERTY and EMPLOYEE added to it as part of its primary
key.
C. Describe how you have represented supertype and subtype entities, if any exist.
None exist.
D. Create a visual representation of your database design as an IE Crow’s Foot E–R
diagram similar to that in Figure 5-27.
See the E-R diagram on the next page.
E. Document referential integrity constraint enforcement, using Figure 5-28 as a guide.
See the table following the E-R diagram.
F. Document any business rules that you think might be important.
To record a SERVICE, a PROPERTY must have a SUB-PROPERTY. SERVICE recording logic should
ensure that one exists and create a default one, if necessary.
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
OWNER
OWNED_PROPERTY
OwnerID in
OWNED_PROPERTY
must exist in OwnerID in
OWNER
NO
NO
Chapter Five – Database Design
OWNED_PROPERTY
SUB_PROPERTY
PropertyID in
SUB_PROPERTY must
exist in PropertyID in
OWNED_PROPERTY
NO
NO
SUB_PROPERTY
PROPERTY_SERVICE
(PropertyID,
SubPropertyID) in
SERVICE must exist in
(PropertyID,
SubPropertyID) in
SUB_PROPERTY
NO
YES
EMPLOYEE
PROPERTY_SERVICE
EmployeeID in
PROPERTY_SERVICE
must exist in EmployeeID
in EMPLOYEE
YES
NO
PROPERTY_SERVICE
PROPERTY_SERVICE_
EQUIPMENT_INT
ServiceID in
PROPERTY_SERVICE_
EQUIPMENT_INT must
exist in ServiceID, in
SERVICE
YES
YES
EQUIPMENT
PROPERTY_SERVICE_
EQUIPMENT_INT
EquipmentID in
PROPERTY_SERVICE_
EQUIPMENT_INT must
exist in EquipmentID in
EQUIPMENT
NO
NO
EQUIPMENT
EQUIPMENT_REPAIR
EquipmentID in
EQUIPMENT_REPAIR
must exist in EquipmentID
in EQUIPMENT
NO
NO
EQUIPMENT
TRAINING_COURSE
EquipmentID in
TRAINING_COURSE
must exist in EquipmentID
in EQUIPMENT
NO
NO
EMPLOYEE
EMPLOYEE_TRAINING_
COURSE_INT
EmployeeID in
EMPLOYEE_TRAINING_
COURSE_INT must exist
in EmployeeID in
EMPLOYEE
YES
YES
TRAINING_COURSE
EMPLOYEE_TRAINING_
COURSE_INT
TrainingCourseID in
EMPLOYEE_TRAINING_
COURSE_INT must exist
in TrainingCourseID in
TRAINING-COURSE
NO
YES
Chapter Five – Database Design
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]
Convert the data model you constructed for James River Jewelry in part C of the James
River Jewelry Project Questions for Chapter 4 that is online in Appendix D (or for an
equivalent data model that your instructor provides for you to use) into a relational
database design for James River Jewelry. 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-JRJ.mwb.
Discuss such possibilities with your students.
CUSTOMER (CustomerID, LastName, FirstName, Phone, EmailAddress)
AWARD (AwardID, CustomerID, AwardCountNumber, AwardTotalPreTaxAmount,
isAwardComplete, isAwardUsed)
PURCHASE (InvoiceNumber, CustomerID, AwardID, InvoiceDate, PreTaxAmount,
isAwardPurchase, PreTaxAwardCreditAmout, AdjustedPreTaxAmount)
Chapter Five – Database Design
© 2018 Pearson Education, Inc. Page 51 of 66
Chapter Five – Database Design
Chapter Five – Database Design