Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Database Concepts
8th Edition
David M. Kroenke • David J. Auer • Scott L. Vandenberg• Robert C. Yoder
Instructor’s Manual
Prepared by David J. Auer
Chapter 5
Database Design
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted,
in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior
written permission of the publisher. Printed in the United States of America.
Instructor’s Manual to accompany:
Database Concepts (8th Edition)
David M. Kroenke • David J. Auer • Scott L. Vandenberg• Robert C. Yoder
Chapter Five – Database Design
CHAPTER OBJECTIVES
• Learn how to transform E-R data models into relational designs
• Practice applying the normalization process
• Understand the need for denormalization
• Learn how to represent weak entities with the relational model
• Know how to represent 1:1, 1:N, and N:M binary relationships
• Know how to represent 1:1, 1:N, and N:M recursive relationships
• Learn SQL statements for creating joins over binary and recursive relationships
CHAPTER ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
be reported and corrected in the online DBC e08 Errata document, which will be
available at http://www.pearsonhighered.com/kroenke.
THE ACCESS WORKBENCH
Solutions to the Access Workbench exercises may be found in Solutions to all Sections:
The Access Workbench, which is a separate document within the Instructor’s Manual.
NOTES ON MICROSOFT WINDOWS 10
This book uses the Microsoft Windows 10 operating system as the basis for screenshots
and step-by-step instructions. However, with Windows 10, Microsoft has introduced a
continuous update system that has already resulted in some fundamental differences in
how different versions of Windows 10 look and operate.
For example, in the original version of Microsoft Windows 10, clicking the Windows
Start button (or pressing the Windows key on the keyboard) displayed the menu shown
in Figure 1. In this menu, we need to click the All apps button in order to see the All
apps menu shown in Figure 2.
Chapter Five – Database Design
© 2018 Pearson Education, Inc. Page 4 of 66
Figure 1 – Windows 10 Main Menu
The All apps button
The File Explorer button
The File Explorer icon
The All apps menu
Chapter Five – Database Design
Figure 2 – Windows 10 All Apps Menu
Microsoft then released the Windows 10 Anniversary Update (Feature update to
Windows 10, version 1607) (see the blog discussion at
https://blogs.windows.com/windowsexperience/2016/08/02/how-to-get-the-windows-10-
anniversary-update/#K1CZuiw4auiuE9A5.97 ). One of the changes introduced in the
Anniversary Update was a major change to the menu system. Now, as shown in
Figure 3, the All apps menu is immediately available when the Start button is used (or
when the keyboard Windows key is pressed).
The All apps menu
The File Explorer button
The File Explorer icon
Chapter Five – Database Design
TEACHING SUGGESTIONS
• The best way to learn database design is to do it. Students should answer all of the
Review questions and do at least one of the Garden Glory, James River Jewelry, or
the Queen Anne Curiosity Shop projects.
• You may want to assign your own version of the data model for Garden Glory,
James River Jewelry, or the Queen Anne Curiosity Shop. That way, students who
did poorly on the data modeling exercise will have the same starting point as
• Very important in this chapter is the understanding of primary and surrogate keys. A
relatively easy way to introduce surrogate keys is to remind students that most keys
we conceptually understand today were at one time surrogate keys. For example,
Chapter Five – Database Design
• The companion to referential integrity constraints is referential integrity actions.
Discuss cascading updates, cascading deletes, and “no action” (the DBMS default
when a cascading action is not specified).
• Work especially hard to get students to understand many-to-many relationships and
the referential integrity actions. This will help them understand the true purpose of
the intersection table. If they understand the requirement that no part of a primary
key can be null, they will understand why the cascading behavior for intersection
tables need to be set as it is.
• Make sure you cover what happens when you use a delete cascade integrity action
with a database structure that has several levels. You can have the cascade action
• One design area where MySQL Workbench does not have the proper tools is
supertype/subtype constructions. Our work around to this is to use a dummy table in
place of the supertype to subtype connector symbol, and make the needed
connections to this table. To illustrate this, here is the solution to Review Question
5.28 (b) shown as drawn in erwin Data Modeler (see:
http://erwin.com/products/erwin-data-modeler-standard-edition/ - note that the Erwin
Data Modeler Community Edition is no longer available as of January 23rd, 2017)
and then as drawn in MySQL Workbench. Study the MySQL Workbench diagram to
see how the dummy table is used and connected.
Chapter Five – Database Design
© 2018 Pearson Education, Inc. Page 8 of 66
One workaround in MySQL Workbench is to use a dummy table to replace the subtype connector in
the diagrams above. Here is a version of the second diagram as drawn in MySQL Workbench. See
MySQL Workbench file DBC-e08-RQ-5-28-B.mwb.
CLIENT
ClientID
LastName
FirstName
Phone
Email
HOME_BUYER
ClientID (FK)
DesiredNumberOfBedroom
_OtherAttributes_
COMMERCIAL_BUYER
ClientID (FK)
DesiredTotalFloorSpace
_OtherAttributes_
RENTER
ClientID (FK)
DesiredNumberOfParkingSpac
_OtherAttributes_
Chapter Five – Database Design
ANSWERS TO REVIEW QUESTIONS
5.1 Explain how entities are transformed into tables.
See Figure 5-1: The Steps for Transforming a Data Model into a Database Design.
5.2 Explain how attributes are transformed into columns. What column properties do you
take into account when making the transformations?
5.3 Why is it necessary to apply the normalization process to the tables created according to
your answer to question 5.1?
5.5 When is denormalization justified?
Chapter Five – Database Design
© 2018 Pearson Education, Inc. Page 10 of 66
Denormalization is justified when usability or performance considerations indicate that a fully
normalized set of tables is not optimum.
5.6 Explain the problems that unnormalized tables have for insert, update, and delete
actions.
5.7 Explain how the representation of weak entities differs from the representation of strong
entities.
5.8 Explain how supertype and subtype entities are transformed into tables.
5.9 List the three types of binary relationships and give an example of each. Do not use the
examples given in this text.
For this example, we will consider boats in a marina:
TYPE of BINARY RELATIONSHIP
EXAMPLE
1:1
BOAT to SLIP
1:N
BOAT to RENTAL_FEE
N:M
BOAT to OWNER
Chapter Five – Database Design
5.10 Define the term foreign key and give an example.
A foreign key is an attribute in one relation that is a primary key of a different relation.
Example: Given the tables:
5.11 Show two different ways to represent the 1:1 relationship in your answer to question 5.9.
Use IE Crow’s Foot E-R diagrams.
See MySQL Workbench files DBC-e08-RQ-5-11-A and DBC-e08-RQ-5-11-B.
Assume relations:
(a)
Where
LicenseNumber in SLIP must exist in LicenseNumber in BOAT
Chapter Five – Database Design
(b)
BOAT (LicenseNumber, Type, Length, SlipNumber)
SLIP (SlipNumber, Location, Charge)
Where
SlipNumber in BOAT must exist in SlipNumber in SLIP
5.12 For your answers to question 5.11, describe a method for obtaining data about one of
the entities, given the key of the other. Describe a method for obtaining data about the
second entity, given the key of the first. Describe methods for both of your alternatives in
question 5.11.
To go from BOAT (using LicenseNumber) to SLIP:
To go from SLIP (using SlipNumber) to BOAT:
5.13 Code SQL statements to create a join that has all data about both tables from your work
for question 5.11.
The SQL statement will depend on which table has the foreign key:
Chapter Five – Database Design
Or
5.14 Define the terms parent and child as they apply to tables in a database design and give
an example of each.
5.15 Show how to represent the 1:N relationship in your answer to question 5.9. Use an IE
Crow’s Foot E-R diagram.
See MySQL Workbench file DBC-e08-RQ-5-15.mwb.
BOAT to RENTAL_FEE is 1:N.
Assume relations:
Where
LicenseNumber in RENTAL_FEE must exist in LicenseNumber in BOAT
Chapter Five – Database Design
5.16 For your answer to question 5.15, describe a method for obtaining data for all the
children, given the key of the parent. Describe a method for obtaining data for the
parent, given a key of the child.
5.17 For your answer to question 5.15, code a SQL statement that creates a table that has all
data from both tables.
5.18 For a 1:N relationship, explain why you must place the key of the parent table in the child
table, rather than place the key of the child table in the parent table.
5.19 Give examples of binary 1:N relationships, other than those in this text, for (a) an
optional-to-optional relationship, (b) an optional-to-mandatory relationship, (c) a
mandatory-to-optional relationship, and (d) a mandatory-to-mandatory relationship.
Illustrate your answer by using IE Crow’s Foot E-R diagrams.
A. An optional-to-optional (O–O) relationship: BOAT to SAIL
LicenseNumber in SAIL must exist in LicenseNumber in BOAT
Chapter Five – Database Design
B. An optional-to-mandatory (O–M) relationship: BOAT to FIRE-EXTINGUISHER
See MySQL Workbench file DBC-e08-RQ-5-19-B.mwb.
LicenseNumber in FIRE_EXTINGUISHER must exist in LicenseNumber in BOAT
C. A mandatory-to-optional (M–O) relationship: BOAT to RENTAL-AGREEMENT
See MySQL Workbench file DBC-e08-RQ-5-19-C.mwb.
• A boat in BOAT must have one or more rental agreements in RENTAL-AGREEMENT,
but rental agreements are made for items, such as cars and RVs, other than boats
(these other items also have license numbers).
LicenseNumber in RENTAL_AGREEMENT must exist in LicenseNumber in BOAT
D. A mandatory-to-mandatory (M–M) relationship: BOAT to REPAIR-BILL
See MySQL Workbench file DBC-e08-RQ-5-19-D.mwb.
Chapter Five – Database Design
LicenseNumber in REPAIR_BILL must exist in LicenseNumber in BOAT
5.20 Show how to represent the N:M relationship in your answer to question 5.9. Use an IE
Crow’s Foot E-R diagram.
See MySQL Workbench file DBC-e08-RQ-5-20.mwb.
Create an intersection table having the key of BOAT and the key of OWNER.
5.21 Explain the meaning of the term intersection table.
5.22 Explain how the terms parent table and child table relate to the tables in your answer
to question 5.20.
5.23 For your answers to questions 5.20, 5.21, and 5.22, describe a method for obtaining the
children for one of the entities in the original data model, given the primary key of the
table based on the second entity. Also, describe a method for obtaining the children for
Chapter Five – Database Design
To get all of the owners for a boat:
To get all of the boats for an owner:
5.24 For your answer to question 5.20, code a SQL statement that creates a relation that has
all data from all tables.
5.25 Why is it not possible to represent N:M relationships with the same strategy used to
represent 1:N relationships?
5.26 What is an associative entity? What is an association relationship? Give an example of
an association relationship other than one shown in this text. Illustrate your answer
using an IE Crow’s Foot E-R diagram.
See MySQL Workbench file DBC-e08-RQ-5-26.mwb.
Chapter Five – Database Design
5.27 Give an example of a 1:N relationship with an ID-dependent weak entity, other than one
shown in this text. Illustrate your answer using an IE Crow’s Foot E-R diagram.
See MySQL Workbench files DBC-e08-RQ-5-27-A.mwb and DBC-e08-RQ-5-27-B.mwb.
5.28 Give an example of a supertype–subtype relationship, other than one shown in this text.
Illustrate your answer using an IE Crow’s Foot E-R diagram.
Chapter Five – Database Design
One workaround in MySQL Workbench is to use a dummy table to replace the subtype
connector in the diagrams above. Here is a version of the second diagram as drawn in MySQL
Workbench. See MySQL Workbench file DBC-e08-RQ-5-28-B.mwb.
PROPERTY
PropertyID
PropertyType
Address
City
State
ZIP
HOUSE
PropertyID (FK)
NumberOfBedrooms
_OtherAttributes_
COMMERCIAL
PropertyID (FK)
NumberOfUnits
TotalFloorSpace
_OtherAttributes_
APARTMENT_HOUSE
PropertyID (FK)
NumberOfUnits
ManagerName
_OtherAttributes_
DUPLEX
PropertyID (FK)
NumberOfParkingSpac
_OtherAttributes_
CLIENT
ClientID
LastName
FirstName
Phone
Email
HOME_BUYER
ClientID (FK)
DesiredNumberOfBedroom
_OtherAttributes_
COMMERCIAL_BUYER
ClientID (FK)
DesiredTotalFloorSpace
_OtherAttributes_
RENTER
ClientID (FK)
DesiredNumberOfParkingSpac
_OtherAttributes_
Chapter Five – Database Design
5.29 Define the three types of recursive binary relationships, and give an example of each,
other than the ones shown in this text.
In the Chapter 4 Review Questions, we illustrated the three types of recursive models by using
1:1 Recursive: Agents at the PNWREA are assigned as a mentor to one other agent.
Each NWREA mentor can only work with one agent at a time. Further, each agent is only
1:N Recursive: The PNWREA has changed the rules about mentoring. Agents at the
PNWREA may be assigned as a mentor to one or more other agents. Now each PNWREA mentor
