INSTRUCTOR’S MANUAL
TO ACCOMPANY
40th Anniversary Edition
DATABASE PROCESSING
Fundamentals, Design, and Implementation
15th Edition
Chapter 4
Database Design Using Normalization
David M. Kroenke | David J. Auer | Scott L. Vandenberg | Robert C. Yoder
Chapter Four – Database Design Using Normalization
Page 4-2
CHAPTER OBJECTIVES
• To design updateable databases to store data received from another source
• To use SQL to access table structure
ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
TEACHING SUGGESTIONS
• Databases used in the text examples and solution database files for your use are
available. Student databases in MS Access with basic tables, relationships and data
Chapter Four – Database Design Using Normalization
• If you have any experience with either of these situations, discuss that experience in
class to help make the situations discussed here more real to your students.
ANSWERS TO REVIEW QUESTIONS
4.1 Summarize the premise of this chapter.
4.2 When you receive a set of tables, what steps should you take to assess their structure
and content?
(1) Examine the table structure and contents
4.3 Show SQL statements to count the number of rows and to list the top 15 rows of the
RETAIL_ORDER table.
Chapter Four – Database Design Using Normalization
Page 4-4
4.4 Suppose you receive the following two tables:
and you conclude that EMPLOYEE.DepartmentName is a foreign key to
DEPARTMENT.DepartmentName. Show SQL for determining whether the following
referential integrity constraint has been enforced:
EMPLOYEE.DepartmentName must exist in DEPARTMENT.DepartmentName
The solution to review question 4.4 is contained in the Microsoft Access database DBP-e15-IM–
Chapter Four – Database Design Using Normalization
Page 4-5
Given these two tables, an implied referential integrity relationship may not have been created.
Alternatively, in Microsoft Access, it is possible to define the relationship between the
EMPLOYEE and DEPARTMENT without enforcing referential integrity.
Chapter Four – Database Design Using Normalization
Running the query, we find that the existing data does NOT comply with the referential integrity
constraint:
We need to either (1) add the Sales department and its budget code to DEPARTMENT, or (2)
change the value of EMPLOYEE.DepartmentName to ‘Marketing’ for any employee currently
having a value of ‘Sales’.
4.5 Summarize how database design principles differ with regards to the design of
updateable databases and the design of read-only databases.
When designing updateable databases we need to be concerned about modification anomalies and
4.6 Describe two advantages of normalized tables.
4.7 Why do we say that data duplication is only reduced? Why is it not eliminated?
Chapter Four – Database Design Using Normalization
Page 4-7
4.8 If data duplication is only reduced, how can we say that the possibility of data
inconsistencies has been eliminated?
4.9 Describe two disadvantages of normalized tables.
Two disadvantages of normalized tables are that (1) more complicated SQL may be required for
4.10 Suppose you are given the table:
Chapter Four – Database Design Using Normalization
Page 4-8
First, fill the DEPARTMENT table so that DepartmentName exists if referential integrity has
/* *** SQL-Query-RQ-04-10-A *** */
INSERT INTO DEPARTMENT
SELECT DISTINCT DepartmentName, BudgetCode
FROM EMPLOYEE_DEPARTMENT;
Now, fill the EMPLOYEE table. [NOTE: The Microsoft Access table shown below has been
named as EMPLOYEE04010, and the SQL statement actually used started with ‘INSERT INTO
EMPLOYEE04010’.].
/* *** SQL-Query-RQ-04-10-B *** */
INSERT INTO EMPLOYEE
SELECT EmployeeNumber, EmployeeLastName, EmployeeFirstName,
Email, DepartmentName
FROM EMPLOYEE_DEPARTMENT;
Chapter Four – Database Design Using Normalization
Page 4-9
4.11 Summarize the reasons explained in this chapter for not placing ZIP code values into
BCNF.
4.12 Describe a situation, other than the one for ZIP codes, in which one would choose not to
place tables into BCNF. Justify your decision not to use BCNF.
Consider the following FLIGHT_ARRIVAL table, which contains data about airline arrivals at
an airport:
4.13 According to this text, under what situations should you choose not to remove
multivalued dependencies from a relation?
4.14 Compare the difficulty of writing subqueries and joins with the difficulty of dealing with
anomalies caused by multivalued dependencies.
4.15 Describe three uses for a read-only database.
Three uses for a read-only database are (1) querying, (2) reporting, and (3) data mining.
4.16 How does the fact that a read-only database is never updated influence the reasons for
normalization?
The fact that read-only databases will never be updated through normal operations (although they
Chapter Four – Database Design Using Normalization
Page 4-10
4.17 For read-only databases, how persuasive is the argument that normalization reduces file
space?
4.18 What is denormalization?
4.19 Suppose you are given the DEPARTMENT and EMPLOYEE tables in question 4.10 and
asked to denormalize them into the EMPLOYEE_DEPARTMENT relation. Show the
design of the EMPLOYEE_DEPARTMENT relation. Show an SQL statement to fill this
table with data.
The solution to review question 4.19 is contained in the Microsoft Access database DBP-e15-IM–
Chapter Four – Database Design Using Normalization
Page 4-11
4.20 Summarize the reasons for creating customized duplicated tables.
Customized duplicate tables are used to create more efficient applications when variations of an
4.21 Why are customized duplicated tables not used for updateable databases?
4.22 List four common design problems when creating databases from existing data.
Four common design problems when creating databases from existing data are (1) the multivalue,
4.23 Give an example of a multivalue, multicolumn table other than one discussed in this
chapter.
Consider the following table from a real estate database:
Chapter Four – Database Design Using Normalization
4.24 Explain the problems in your example in question 4.23.
The problem in the PROPERTY table in question 4.23 is that the number of units at various
4.25 Show how to represent the relation in your answer to question 4.23 with two tables.
Consider the following tables as an alternative to the PROPERTY table in question 4.23:
4.26 Show how the tables in your answer to question 4.25 solve the problems you identified
in question 4.24.
Since we can have as many rows or records as needed in PROPERTY_UNIT, we can
4.27 Explain the following statement: “The multivalue, multicolumn problem is just another
form of multivalued dependency.” Show how this is so.
In the multivalue, multicolumn problem, multiple values of an attribute are placed in different
4.28 Explain ways in which inconsistent values arise.
4.29 Why are inconsistent values in foreign keys particularly troublesome?
Chapter Four – Database Design Using Normalization
Page 4-13
4.30 Describe two ways to identify inconsistent values. Are these techniques certain to find all
inconsistent values? What other step can be taken?
4.31 What is a null value?
A null value is a missing value—a value of an attribute that was never provided.
4.32 How does a null value differ from a blank value?
4.33 What are three interpretations of null values? Use an example in your answer.
A null value can represent one of three conditions: (1) the value is inappropriate, or (2) the value
Chapter Four – Database Design Using Normalization
Page 4-14
4.34 Show SQL for determining the number of null values in the column EmployeeFirstName
of the table EMPLOYEE.
The solution to review question 4.34 is contained in the Microsoft Access database DBP-e13-IM–
4.35 Describe the general-purpose remarks column problem.
4.36 Give an example in which the general-purpose remarks column makes it difficult to
obtain values for a foreign key.
The general-purpose remarks column makes it difficult to obtain values of a foreign key when the
Chapter Four – Database Design Using Normalization
Page 4-15
4.37 Give an example in which the general-purpose remarks column causes difficulties when
multiple values are stored in the same column. How is this problem solved?
Consider the following table from a real estate database:
4.38 Why should one be wary of general-purpose remarks columns?
We should never use a general-purpose remarks column in our own designs because we know
Chapter Four – Database Design Using Normalization
ANSWERS TO EXERCISES
The Quincy Bay Sports Club owns and operates three sports club facilities in Boston,
Cambridge, and Quincy, Massachusetts. Each facility has a large selection of modern exercise
equipment, weight rooms, and rooms for yoga and other exercise classes. Quincy Bay offers
three-month and one-year memberships. Members can use the facilities at any of the three club
locations.
Quincy Bay maintains a roster of personal trainers who operate as independent consultants.
Approved trainers can schedule appointments with clients at Quincy Bay facilities, as long as
their client is a member of the club. Trainers also teach yoga, Pilates, and other classes.
Answer the following questions, assuming you have been provided the following three tables of
data (PT stands for personal trainer):
4.39 Identify possible multivalued dependencies in these tables.
VARIABLE ANSWER – There are several true possible multivalued dependencies based on
phone numbers, and these are shown below. There could also be a similar set for e-mail
4.40 Identify possible functional dependencies in these tables.
VARIABLE ANSWER – Based on some assumptions that have to be made—which in the real
Chapter Four – Database Design Using Normalization
Page 4-17
ASSUMPTIONS:
(1) In PT_SESSION, the Trainer’s Fee is a constant and is associated with the trainer. An
alternate assumption might be that Fee depends on the Trainer and the type of training and length
of the session.
PT_SESSION (Trainer, Phone, Email, Fee, ClientLastName, ClientFirstName,
ClientPhone, ClientEmail, Date, Time)
CLUB_MEMBERSHIP (ClientNumber, ClientLastName, ClientFirstName, ClientPhone,
ClientEmail, MembershipType, EndingDate, Street, City, State, Zip)
FOR CLUB_MEMBERSHIP: