IT360 Lab 3: Relational Model and Normalization
DUE: February 6, 2011, 2359 (paper copy BEFORE start of lab next
day)
Some of the most difficult decisions that you face as a database developer are what tables
to create and what columns to place in each table, as well as how to relate the tables that
you create. Normalization is the process of applying a series of rules to ensure that your
database achieves optimal structure. Normal forms are a progression of these rules. Each
successive normal form achieves a better database design than the previous form did.
Although we discussed several levels of normal forms, this lab focuses on 1 st Normal
Form (1NF), and Boyce-Codd Normal Form (BCNF). If you do not understand functional
dependencies, then review the discussion on functional dependencies on the slides and
your notes.
Exercise 1: 1st Normal Form (1NF)
Consider the Students table, with the primary key underlined, and the following data:
Students:
Alpha Name Email Courses GradePoints
100111 John Doe doe@[Link] NN204, SI204, IT221 2,3,3
092244 Matt Smith smith@[Link] SM223, EE301 4,4
113221 Melinda Black black@[Link] SI204 3
090112 Tom Johnson Johnson@[Link] NN204, SI204, IT221 4,2,3
a) Is the Students table in 1NF? Why?
b) If the Students table is not in 1NF, redesign the tables such that all the information
currently in the Students table is found in the resulting tables, and the resulting
tables are in 1NF. For each of the resulting tables, give the table name, column
names, primary keys, and foreign keys.
Exercise 2: Boyce-Codd Normal Form (BCNF)
For a table to be in Boyce-Codd normal form, the table must be in 1NF and the
determinants of all the functional dependencies in that table must be candidate keys
(either primary key or alternate key). Below is the Rentals table created for the DVD-
by-mail division of Neatflix.
Rentals:
RentalID Title CustomerID MailedOutDate Director MovieCategory Price
1 Die Hard 1001 3/3/2010 John Old $4.25
McTiernan
1 The last 1001 3/3/2010 Walter Hill Old $4.25
man
standing
1 Wedding 1001 3/3/2010 David New $5.50
Crashers Dobkin
2 Dodgeball 1002 3/4/2010 Rawson New $5.50
Marshall
Thurber
2 Die Hard 1002 3/4/2010 John Old $4.25
McTiernan
3 As good 1003 1/7/2011 James Old $4.25
as it gets Brooks
4 Forest 1001 1/7/2011 Robert Old $4.25
Gump Zemeckis
The primary key of the Rentals table is the composite key (RentalID, Title).
a) Explain the conditions under which the following functional dependency is true:
RentalID -->CustomerID
b) Based on the sample data on the table, is the functional dependency
RentalID --> CustomerID true?
c) Explain the conditions under which the following functional dependency is true:
Director --> Title
d) Based on the sample data on the table, is the functional dependency
Director --> Title true?
e) Based on your general knowledge of movies and rentals, is the functional
dependency Director --> Title true?
f) Write a functional dependency that expresses the fact that the cost of all movies in
a given category is the same.
g) We discussed insertion anomalies, deletion anomalies and update anomalies as
examples of problems that can appear in tables that are not normalized. The
following is an example of an insertion anomaly in the Rentals table: if we want
to create a new category of movies, “Must See”, there is no way to store the price
of this type of movie in the database, until someone rents a movie in this category,
and the rental information is recorded into the Rentals table. Give one example of
a deletion anomaly in the Rentals table.
h) State what you believe are reasonable functional dependencies for the Rentals
table for a DVD-by-mail business (include the functional dependencies from
points a) to f) that you believe are/should be true).
i) Given your answer above, decompose the Rentals table such that the resulting
tables are in BCNF. For each of the resulting tables, give the table name, column
names, primary keys, and foreign keys.
Exercise 3: SQL
Given the following tables:
ITEM(ItemID, Description, PurchaseDate, Store, City, Quantity, LocalCurrencyAmt,
ExchangeRate)
SHIPMENT_ITEM(ShipmentID, ShipmentItemNb, ItemID, Value)
Write the SQL query to find the ItemID and Description for the item with the lowest
shipped Value.
Exercise 4 (Extra credit): 4th Normal Form (4NF)
For information on multivalued dependencies and 4NF, review the slides.
Suppose we have the following Courses table with columns CourseID, Instructor, Book
that stores the courses, the instructor teaching the course, and the recommended books for
the course. The book(s) recommended for a course does not depend on the teacher
teaching the course, just on the course. Here is an example of instantiation for this table:
Courses:
CourseID Instructor Book
IT360 Crainiceanu Kroenke
IT360 Crainiceanu Welling
IT360 DeLooze Kroenke
IT360 DeLooze Welling
SI440 Crainiceanu Kroenke
SI440 Crainiceanu Ramakrishnan
SI440 Crainiceanu Stonebraker
a) Give an example of a multivalued dependency in the Courses table.
b) Is the Courses table in 4NF? If answer to yes, say why. If not, decompose the table
such that the resulting tables are in 4 th normal form. For each of the resulting tables,
give the table name, column names, primary keys, and foreign keys.
Turn in BOTH electronic (due February 6, 2011 at 2359) and paper
copies (due before start of lab on February 7, 2011):
Electronic:
1. Upload the yourlastname_lab3.doc file containing all answers including the SQL
question to Lab 3 assignment on Blackboard.
Hard-copies:
1. The completed assignment coversheet. Your comments will help us improve the
course.
2. A hard copy of the yourlastname_lab3.doc.