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 Robert C. Yoder
Chapter Two
The Relational Model
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 Two – The Relational Model
Page 3 of 38
CHAPTER OBJECTIVES
• Learn the conceptual foundation of the relational model
• Understand how relations differ from nonrelational tables
• Learn basic relational terminology
• Learn the meaning and importance of keys, foreign keys, and related terminology
• Understand how foreign keys represent relationships
• Learn the purpose and use of surrogate keys
• Learn the meaning of functional dependencies
• Learn to apply a process for normalizing relations
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.
TEACHING SUGGESTIONS
• The Art Course database discussed in Chapter 1 is a good database to use for an in-
class demo of the concepts in this chapter. The DBMS screenshots in Chapter 2 use
that database as the example database. See the list, data and database files
supplied, and use the following:
• Microsoft Access 2016:
• “Art Course List” in DBC-e08-Lists-And-Data.xlsx
• DBC-e08-Art-Course-Database-CH01.accdb
• Microsoft SQL Server 2016 Developer Edition:
• DBC-e08-MSSQL-Art-Course-Database-Create-Tables.sql
• DBC-e08-MSSQL-Art-Course-Database-Insert-Data.sql
• NOTE: Create a database diagram for the database
Chapter Two – The Relational Model
Page 4 of 38
• Oracle Database Express Edition (XE) 11g Release 2:
• DBC-e08-ODB-Art-Course-Database-Create-Tables.sql
• DBC-e08-ODB-Art-Course-Database-Insert-Data.sql
• DBC-e08-ODB-Art-Course-Database-SQL-Queries-CH01.sql
• MySQL 5.7:
• DBC-e08-MySQL-Art-Course-Database-Create-Tables.sql
• DBC-e08-MySQL-Art-Course-Database-Insert-Data.sql
• The goal of this chapter is to present an overview of the major elements of the
relational model. This includes the definition of a relation, important terminology, the
use of surrogate keys, and basic design principles.
• Students often misconstrue the statement that only a single element is allowed in a
cell to mean that the cells must be fixed in length. One can have a variable length
memo in a cell but that is considered, semantically, to be one thing. By the way,
there are a number of reasons for this restriction. Perhaps the easiest to explain is
that SQL has no means for addressing sub-elements in a cell.
• When students execute SQL SELECTs, they may generate relations with duplicate
rows. Such results do not fit the definition of relations, but they are considered
relations nonetheless. This is a good example of “theory versus practice”.
• You may want to emphasize that foreign keys and the primary key that they
reference need not have the same name. They must, however, have the same
underlying set of values (domain). This means that the values not just look the
same; it means that the values mean the same thing. A foreign key of CatName and
a foreign key of ValentineNickName might look the same, but they do not mean the
same thing. Using ValentineNickName as a foreign key to Name in the relation CAT
would result in some weird results.
• Referential integrity constraints are important. You might ask the students to think of
an example when a foreign key does not have a referential integrity constraint
(answer: whenever a parent row is optional, say, STUDENTs need not have an
ADVISER).
• We favor the use of surrogate keys. Unless there is a natural, numeric ID (like
PartNumber), we almost always add a surrogate key to our database designs.
Sometimes a surrogate key will be added even if there is a natural, numeric ID for
consistency. Surrogate keys can cause problems (primarily patching up foreign
keys) if the database imports data from other databases that either do not employ a
surrogate key or use a different one. In some cases, institutions have developed
policies for ensuring that surrogate keys are unique globally. It’s probably best for
the students to get into the habit of using them and consider not using them as an
exception. Professional opinions vary on this, however.
Chapter Two – The Relational Model
Page 5 of 38
• If you’re using Oracle Database, then you’ll need to teach the use of sequences to
implement surrogate keys. Sequences are an awkward solution to this problem,
however, and may be why surrogate keys are less used in the Oracle-world. Maybe
there will be a better solution to them from Oracle in the future.
• The discussion of functional dependencies is critical—maybe the most important in
the book. If students can understand that all tables do is record “data points” of
functional dependencies, then normalization will be easier and seem more natural.
• In physics, because there are formulae like F = ma, we need not store tables and
tables of data recording data points for force, mass, and acceleration. The formula
suffices for all data points. However, there is no formula for computing how much a
customer of, say, American Airlines, owes for his or her ticket from New York to
Houston. If we could say the cost of an airline ticket was $.05 per mile, then we
could compute the cost of a ticket, and tables of airline flight prices would be
unnecessary. But, we cannot; it all depends on … So, we store the data points for
functional dependencies in tables.
• If we use domain/key normal form as the ultimate, then, insofar as functional
dependencies are concerned, the domain/key definition that “every constraint is a
logical consequence of domains and keys,” comes down to Boyce-Codd Normal
Form. Therefore, we proceed on good theoretical ground with the discussion as
presented in this chapter.
• Students should understand the three ambiguities in a null value. This
understanding will help them comprehend the issues addressed by INNER and
OUTER joins in the next chapter.
• Exercises 2.40 and 2.41 deal with multivalued dependencies and fourth normal form
(4NF). These are instructive as they show students how to deal with situations
where the value of one column in a table is associated with several values of another
attribute in (at least initially) the same table. This is an important concept, and after
BCNF it is the next important concept students need to understand about
normalization.
Chapter Two – The Relational Model
Page 6 of 38
ANSWERS TO REVIEW QUESTIONS
2.1 Why is the relational model important?
It is the single most important standard in database processing and is used for the design and
implementation of almost every commercial database worldwide.
2.2 Define the term entity and give an example of an entity (other than the one from this
chapter).
Entity is the formal name for a “thing” that is being tracked in a database, and is defined as
something of importance to the user that needs to be represented in the database.
Example: TEXTBOOK
2.3 List the characteristics a table must have to be considered a relation. Define the term
domain, and explain the significance of the domain integrity constraint to a relation.
• Rows contain data about an entity.
• Columns contain data about attributes of the entity.
• Cells of the table hold a single value.
• All entries in a column are of the same kind.
• Each column has a unique name.
• The order of the columns is unimportant.
• The order of the rows is unimportant.
A domain is a set of data that is of the same data type, and further, has the domain integrity
constraint in that they must come from a set of allowed values, like the set of player positions
on a baseball team.
2.4 Give an example of a relation (other than one from this chapter).
Example: TEXTBOOK (ISBN, Title, Publisher, Copyright)
2.5 Give an example of a table that is not a relation (other than one from this chapter).
Example: TEXTBOOK (ISBN, Title, Publisher, Copyright, Authors)
A table is not a relation when there are multiple author names in the Authors column.
2.6 Under what circumstances can an attribute of a relation be of variable length?
It can be of a variable length, if that attribute is considered to be a single thing like a memo or
other variable length data item.
Chapter Two – The Relational Model
Page 7 of 38
2.7 Explain the use of the terms file, record, and field.
2.8 Explain the use of the terms relation, tuple, and attribute.
2.9 Under what circumstances can a relation have duplicate rows?
2.10 Define the term unique key and give an example.
2.11 Define the term nonunique key and give an example.
2.12 Give an example of a relation with a unique composite key.
2.13 Define the terms candidate key and primary key. Explain the difference between a
primary key and a candidate key. Explain the significance of the entity integrity constraint
to a primary key.
Chapter Two – The Relational Model
Page 8 of 38
2.14 Describe four uses of a primary key.
2.15 What is a surrogate key, and under what circumstances would you use one?
2.16 How do surrogate keys obtain their values?
2.17 Why are the values of surrogate keys normally hidden from users on forms, queries, and
reports?
2.18 Explain the term foreign key and give an example.
2.19 Explain how primary keys and foreign keys are denoted in this book.
2.20 Define the term referential integrity constraint and give an example of one. How does
the referential integrity constraint contribute to database integrity?
Chapter Two – The Relational Model
Page 9 of 38
2.21 Explain three possible interpretations of a null value.
2.22 Give an example of a null value (other than one from this chapter), and explain each of
the three possible interpretations for that value.
2.23 Define the terms functional dependency and determinant, using an example not from
this book.
2.24 In the following equation, name the functional dependency and identify the
determinant(s):
Chapter Two – The Relational Model
Page 10 of 38
2.25 Explain the meaning of the following expression:
A → (B, C)
Given this expression, tell if it is also true that:
A → B
and
A → C
2.26 Explain the meaning of the following expression:
(D, E) → F
Given this expression, tell if it is also true that:
D → F
and
E → F
2.27 Explain the differences in your answers to questions 2.25 and 2.26.
Chapter Two – The Relational Model
Page 11 of 38
2.28 Define the term primary key in terms of functional dependencies.
2.29 If you assume that a relation has no duplicate data, how do you know there is always at
least one primary key?
2.30 How does your answer to question 2.29 change if you allow a relation to have duplicate
data?
2.31 In your own words, describe the nature and purpose of the normalization process.
2.32 Examine the data in the Veterinary Office List—Version One in Figure 1-34 (see page
63), and state assumptions about functional dependencies in that table. What is the
danger of making such conclusions on the basis of sample data?
PetName → (PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName,
OwnerPhone, OwnerEmail)
2.33 Using the assumptions you stated in your answer to question 2.32, what are the
determinants of this relation? What attribute(s) can be the primary key of this relation?
2.34 Describe a modification problem that occurs when changing data in the relation in
question 2.32 and a second modification problem that occurs when deleting data in this
relation.
Chapter Two – The Relational Model
Page 12 of 38
2.35 Examine the data in the Veterinary Office List—Version Two in Figure 1-35 (see page
63), and state assumptions about functional dependencies in that table.
PetName → (PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName,
OwnerPhone, OwnerEmail)
2.36 Using the assumptions you stated in your answer to question 2.35, what are the
determinants of this relation? What attribute(s) can be the primary key of this relation?
2.37 Explain a modification problem that occurs when changing data in the relation in
question 2.35 and a second modification problem that occurs when deleting data in this
relation.
Same as 2.34:
ANSWERS TO EXERCISES
2.38 Apply the normalization process to the Veterinary Office List—Version One relation
shown in Figure 1-34 (see page 63) to develop a set of normalized relations. Show the
results of each of the steps in the normalization process.
STEP ONE:
PET-AND-OWNER (PetName, PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName,
OwnerPhone, OwnerEmail)
Chapter Two – The Relational Model
Page 13 of 38
Is every determinant a candidate key?
NO—OwnerEmail and OwnerPhone are NOT candidate keys.
STEP TWO:
Break into two relations: OWNER and PET
FOR OWNER:
Functional Dependencies:
Is every determinant a candidate key?
YES—OwnerEmail and OwnerPhone are candidate keys—Normalization complete!
We can choose either candidate key as primary key.
(A) IF WE USE OwnerPhone as primary key, THEN:
Functional Dependencies:
Is every determinant a candidate key?
YES—PetName is a candidate key—Normalization complete!
FINAL NORMALIZED RELATIONS:
(B) IF WE USE OwnerEmail as primary key, THEN:
Functional Dependencies:
PET Candidate Keys: PetName
Chapter Two – The Relational Model
Page 14 of 38
Is every determinant a candidate key?
YES—PetName is a candidate key—Normalization complete! However, it may be
unreasonable to require that PetName be unique.
FINAL NORMALIZED REALTIONS:
2.39 Apply the normalization process to the Veterinary Office List—Version Two relation
shown in Figure 1-35 (see page 63) to develop a set of normalized relations. Show the
results of each of the steps in the normalization process.
STEP ONE:
Functional Dependencies:
PET-AND-OWNER Candidate Keys: (PetName, Date)
Is every determinant a candidate key?
NO—PetName, OwnerEmail and OwnerPhone are NOT candidate keys.
STEP TWO:
FOR OWNER:
Functional Dependencies:
Chapter Two – The Relational Model
Page 15 of 38
Is every determinant a candidate key?
YES—OwnerEmail and OwnerPhone are candidate keys—Normalization complete!
We can choose either candidate key as primary key. We will use OwnerPhone.
If a student chooses OwnerEmail, the steps will be similar as shown in Exercise 2.38.
IF WE USE OwnerPhone as primary key, THEN:
FOR PET-SERVICE:
Functional Dependencies:
PET-AND-SERVICE Candidate Keys: (PetName, Date)
Is every determinant a candidate key?
NO—PetName is NOT a candidate key.
STEP THREE:
Break PET-SERVICE into two relations: PET and SERVICE
PET Functional Dependencies:
Is every determinant a candidate key?
YES—PetName is a candidate key—Normalization complete!
SERVICE Functional Dependencies:
SERVICE Candidate Keys: (PetName, Date)
Chapter Two – The Relational Model
Page 16 of 38
Is every determinant a candidate key?
YES—(PetName, Date) is a candidate key—Normalization complete!
FINAL NORMALIZED REALTIONS:
2.40 What is the multivalue, multicolumn problem? What is a multivalued dependency
and how is it resolved in 4NF? To answer these questions, consider the following
relation:
STUDENT (StudentNumber, StudentName, SiblingName, Major)
A. Define and discuss the multivalue, multicolumn problem. Define and discuss a
multivalued dependency.
Chapter Two – The Relational Model
Page 17 of 38
B. Show an example of this relation for two students, one of whom has three
siblings and the other of whom has only two siblings.
StudentNumber
StudentName
SiblingName
Major
100
Mary Jones
Victoria
Accounting
100
Mary Jones
Slim
Accounting
100
Mary Jones
Reginald
Accounting
200
Fred Willows
Rex
Finance
200
Fred Willows
Billy
Finance
C. List the candidate keys in this relation.
STUDENT Candidate Keys: (StudentNumber, SiblingName)
This assumes that StudentName is not unique.
D. State the functional dependencies in this relation.
E. Explain why this relation does not meet the relational design criteria set out in
this chapter (i.e., why this is not a well-formed relation).
F. Define and discuss 4NF and how 4NF can be used to allow a set of well-formed
relations.
Chapter Two – The Relational Model
Page 18 of 38
G. Divide this relation into a set of relations that meet the relational design criteria
(that is, that are well formed).
Break into two relations: STUDENT and STUDENT-SIBLING
FOR STUDENT-SIBLING:
Functional Dependencies:
STUDENT-SIBLING Candidate Keys: (StudentNumber, SiblingName)
Is every determinant a candidate key?
YES—(StudentNum, SiblingName) is a candidate key—Normalization complete!
FOR STUDENT:
2.41 Alter question 2.40 to allow students to have multiple majors. In this case, the relational
structure is:
A. Define and discuss the multivalue, multicolumn problem. Define and discuss a
multivalued dependency.
See the answer to Part A of Question 2.40 above.
Chapter Two – The Relational Model
Page 19 of 38
B. Show an example of this relation for two students, one of whom has three
siblings and the other of whom has one sibling. Assume that each student has a
single major.
StudentNumber
StudentName
SiblingName
Major
100
Mary Jones
Victoria
Accounting
100
Mary Jones
Slim
Accounting
100
Mary Jones
Reginald
Accounting
200
Fred Willows
Rex
Finance
C. Show the data changes necessary to add a second major for only the first
student.
StudentNumber
StudentName
SiblingName
Major
100
Mary Jones
Victoria
Accounting
100
Mary Jones
Slim
Accounting
100
Mary Jones
Reginald
Accounting
200
Fred Willows
Rex
Finance
100
Mary Jones
Victoria
InfoSystems
100
Mary Jones
Slim
InfoSystems
100
Mary Jones
Reginald
InfoSystems
