Chapter Four – Database Design Using Normalization
Page 4-18
CLASS (ClassName, Trainer, StartDate, EndDate, Time, DayOfWeek, Cost)
CLASS.(ClassName, Trainer, StartDate, Time) → CLASS.EndDate
4.41 Classify each table according to whether it is in BCNF or in 4NF. State your
assumptions.
VARIABLE ANSWER – Based on some assumptions that have to be made—which in the real
ASSUMPTIONS:
(1) In PT_SESSION, the Trainer’s Fee is a constant and is associated with the trainer. An
(2) In PT_SESSION, Trainers can have multiple sessions with the same client on the same day.
PT_SESSION is not in BCNF or 4NF. It contains information about three entities or themes—
the Trainer, the Client and the PT_Session. It also contains two multivalued dependencies, but
only one will prove to be a problem after CLUB_MEMBERSHIP is put into proper form (see
below). This multivalued dependency is:
CLUB_MEMBERSHIP is in BCNF, but not 4NF because it contains two multivalued
dependencies. Note that we are keeping ZIP in the table as a slight denormalization as explained
in the book. To be strictly in BCNF (City, Sate, and ZIP) would be moved into another table.
These can actually both be resolved by moving just one of them into its own table. The best to
move is:
Chapter Four – Database Design Using Normalization
4.42 Modify each of these tables so that every table is in BCNF and 4NF. Use the
assumptions you made in your answer to question 4.41.
PT_SESSION needs to be broken into one BCNF table for each entity or theme. It also needs to
use ClientNumber, not (ClientLastName, ClientFirstName) as the linking attribute. All the client
information is properly found in CLUB_MEMBERSHIP. Finally, we need to deal with the
multivalued dependency.
All these tables are in BCNF and 4NF.
CLUB_MEMBERSHIP needs to have one of two multivalued dependencies removed. Since
ClientNumber is unique but (ClientLastName, ClientFirstName) is not, we get these tables. Both
are in BCNF and 4NF.
4.43 Using these tables and your assumptions, recommend a design for an updateable
database.
The design is:
Primary Keys are underlined.
Foreign Keys are italicized.
Chapter Four – Database Design Using Normalization
Page 4-20
4.44 Add a table to your answer to question 4.43 that would allow Quincy Bay to assign
members to particular classes. Include an AmtPaid column in your new table.
4.45 Recommend a design for a read-only database that would support the following needs:
a. Enable trainers to ensure that their clients are members of the club.
b. Enable the club to assess the popularity of various trainers.
There are two parts to this—client sessions and class enrollees. This will require two tables:
c. Enable the trainers to determine if they are assisting the same client.
d. Enable class instructors to determine if the attendees to their classes have paid.
Chapter Four – Database Design Using Normalization
Page 4-21
ANSWERS TO MARCIA’S DRY CLEANERS CASE QUESTIONS
Marcia Wilson, the owner of Marcia’s Dry Cleaning, is in the process of creating databases to
support the operation and management of her business. For the past year, she and her staff
have been using a cash register system that collects the following data:
To help create her database, Marcia purchased a mailing list from a local business bureau. The
mailing list includes the following data:
HOUSEHOLD (Phone, FirstName, LastName, Street, City, State, ZIP, Apartment)
In some cases, a phone number has multiple names. The primary key is thus the composite
(Phone, FirstName, LastName). There are no null values in Phone, FirstName, and LastName,
but there are some null values in the address data.
There are many names in SALE that are not in HOUSEHOLD, and there are many names in
HOUSEHOLD that are not in SALE.
A Design an updateable database for storing customer and sales data. Explain how to deal
with the problems of missing data. Explain how to deal with the problems of incorrect
phone and name data.
MULTIVALUED DEPENDENCIES:
In CUSTOMER: It looks like Phone →→ (FirstName, LastName) could be a multivalued
dependency, but unless (FirstName, LastName) is unique we will have trouble when we separate
Chapter Four – Database Design Using Normalization
Page 4-22
FUNCTIONAL DEPENDENCIES:
In CUSTOMER:
CustomerID → Phone
In SALE:
InvoiceNumber → CustomerID
CANDIDATE KEYS:
In CUSTOMER:
CustomerID
(Phone, FirstName, LastName)
In SALE:
InvoiceNumber
Chapter Four – Database Design Using Normalization
Page 4-23
PRIMARY KEYS
In CUSTOMER:
FOREIGN KEYS:
In CUSTOMER:
None
In SALE:
CustomerID
In the following design, note that Zip is left in the CUSTOMER table as a denormalization. See
the discussion in the text that talks about Zip.
The design is:
Chapter Four – Database Design Using Normalization
Page 4-24
Dealing with missing data, incorrect phone and correct name data:
(1) For CUSTOMER, assuming that the data in HOUSEHOLD is correct, we will:
A. Enter as much data as possible from the cash register system (hopefully
electronically, or else we have a lot of manual work to do) into the new CUSTOMER table. For
each entry, we will verify it against the data in HOUSEHOLD. If the data is complete and
verifiable, we will consider that data accurate and enter the associated data from HOUSEHOLD.
(2) For SALE, there is simply too much missing data to link all sales successfully to customer.
The best thing to do is to validate the CUSTOMER table, and at an appropriate time (start of the
new accounting year) start using the new SALE table to record all transactions from that point on.
B Design a read-only database for storing customer and sales data. Explain how to deal
with the problems of missing data. Explain how to deal with the problems of incorrect
phone and name data.
The goal here is to either look up CUSTOMER data or extended SALE data easily. Therefore,
we will use two tables;
Dealing with missing data, incorrect phone and incorrect name data:
This should be done when we build the operational database in A above. Read-only databases are
extracts of operational databases. By the time we do a data extract to build our read-only
database, we should have resolved those problems in the operational database.
Chapter Four – Database Design Using Normalization
Page 4-25
ANSWERS TO THE QUEEN ANN CURIOSITY SHOP PROJECT QUESTIONS
The Queen Anne Curiosity Shop project questions in Chapter 3 asked you to create a set of
relations to organize and link the Queen Anne Curiosity Shop typical sales data shown in Figure
3-33 and the typical purchase data shown in Figure 3-34. The set of relations may look like the
following:
CUSTOMER (CustomerID, LastName, FirstName, EmailAddress, EncryptedPassword,
Use these relations and the data in Figures 3-33 and 34 to answer the following questions.
Chapter Four – Database Design Using Normalization
Page 4-26
A Follow the procedure shown in Figure 4-1 to assess these data.
1. List all functional dependencies.
2. List any multivalued dependencies.
There are none.
4. List all primary keys.
CustomerID, SaleID, (SaleID, SaleItemID), PurchaseID, VendorID
6. State any assumptions you make as you list these components.
Chapter Four – Database Design Using Normalization
Tested assumption: All tables in BCNF
B List questions you would ask the owners of The Queen Anne Curiosity Shop to verify
your assumptions.
C If there are any multivalued dependencies, create the tables needed to eliminate these
dependencies.
D Do these data have the multivalue, multicolumn problem? If so, how will you deal with it?
E Do these data have the inconsistent data problem? If so, how will you deal with it?
No, there is no inconsistent data problem in these data. No additional work is necessary.
F Do these data have a null value data problem? If so, how will you deal with it?
G Do these data have the general-purpose remarks problem? If so, how will you deal with
it?
Chapter Four – Database Design Using Normalization
Page 4-28
ANSWERS TO MORGAN IMPORTING PROJECT QUESTIONS
Phillip Morgan, the owner of Morgan Importing, makes periodic buying trips to various countries.
During the trips, he keeps notes about the items he purchases and basic data about their
shipments. He hired a college student as an intern, and she transformed his notes into the
spreadsheets in Figure 4-9. This is just sample data. Phillip has purchased hundreds of items
over the years, and they have been shipped in dozens of different shipments.
A Follow the procedure shown in Figure 4-1 to assess this data. List multivalued
dependencies, functional dependencies, candidate keys, primary keys, and foreign keys.
State your assumptions.
In Figure 4-9, there are two tables:
MULTIVALUED DEPENDENCIES: If any of these are based on more than one entity or
“theme” being in the table, then some may result from tables not in BCNF and will be eliminated
by putting the tables into BCNF.
Chapter Four – Database Design Using Normalization
Page 4-29
In ITEM: Note that in this data there are multiple Salespersons, but no indication of multiple
phones. In fact, phone numbers are not listed. If we need phone numbers later, we will assume a
separate phone number for each salesperson.
FUNCTIONAL DEPENDENCIES:
In SHIPMENT:
ShipmentNumber → Shipper
ShipmentNumber → Phone
In ITEM: Note that we have too small a sample of data to trust apparent functional
dependencies. For example, despite what the data shows, it is NOT true that Item → {anything}.
(Item, Date, City, Store) →Salesperson
(Item, Date, City, Store) →Price
CANDIDATE KEYS:
In SHIPMENT:
Chapter Four – Database Design Using Normalization
In ITEM:
(Item, Date, City, Store)
FOREIGN KEYS:
None – The way the tables are set up, ITEM.Item should be a foreign key. Unfortunately, there is
a general-purpose remarks column problem with SHIPMENT which prevents this from working.
B List questions you would ask Phillip to verify your assumptions.
VARIABLE ANSWER – Your students may have a different set of questions based on the
assumptions they make.
(1) Do you always use just one Contact at each Shipper?
(2) Does each shipper have just one Phone?
(3) Do you or can you buy from more than one store in each city in each country?
(4) Can more than one store in the same country, but in a different city, have the same name?
(5) Can you have the same city name in more than one country?
(6) Can the same item be bought from different stores?
(7) Can the same type of item (“brass lamps”) be bought from different stores?
(8) Are all of the items purchased in one purchase (“brass lamps”) from a store shipped on the
same shipment?
Chapter Four – Database Design Using Normalization
Page 4-31
C Create tables as necessary to eliminate multivalued dependencies, if any.
In SHIPMENT: NONE.
In ITEM:
D The relationship between shipment and item data could be inferred by matching values in
the From cells to values in the City cells. Describe two problems with that strategy.
(1) A foreign key is supposed to represent the same thing in both of the tables in which it resides.
That is, a CustomerID in one table is also a CustomerID in the other table (even if renamed as
E Describe a change to this spreadsheet that does express the shipment/item relationship.
Put ShipmentNumber into the Item purchase data. This will record which shipment contained the
Chapter Four – Database Design Using Normalization
Page 4-32
F Assume that Phillip wishes to create an updateable database from this data. Design tables
you think are appropriate. State all referential integrity constraints.
Note that the column SHIPMENT.Contents has been replaced with the SHIPMENT_ITEM table,
and that column has been eliminated to fix a general-purpose remarks column problem.
Also note that we could really use surrogate keys in some of these tables.
The design is:
Primary Keys are underlined.
Foreign Keys are italicized.
Referential Integrity Constraints are stated following each relation.
Chapter Four – Database Design Using Normalization
Page 4-33
G Assume that Phillip wishes to create a read-only database from this data. Design tables you
think are appropriate. State all referential integrity constraints.
VARIABLE ANSWER – The following design denormalizes the tables back to almost the same
set of tables that where implicit in the spreadsheet, but provides the needed foreign key linkage.
It also adds a table with Item and Store information. It would be possible to combine the first two
H Do these data have the multivalue, multicolumn problem? If so, how will you deal with it?
No, these data do not have a multivalue, multicolumn problem.
I Do these data have the inconsistent data problem? If so, how will you deal with it?
Yes, the data have inconsistent data:
1. The Arrival date for Worldwide ShipmentNumber 49100300 is impossible because it is
prior to the Departure date.
To deal with these:
(2) We should call International and verify shipment numbers. If shipments with later shipping
dates always have larger shipment numbers, we will need to write an application data checking
routine (discussed in Chapter 7).
Chapter Four – Database Design Using Normalization
Page 4-34
J Do these data have a null value data problem? If so, how will you deal with it?
Yes, the arrival date for ShipmentNumber 488955 is missing. We need to call Marilyn, our
K Do these data have the general-purpose remarks problem? If so, how will you deal with it?
Yes, the Contents columns listed multiple values of items in each shipment. Not only does this