Database Reference
In-Depth Information
are stored in each warehouse. In addition, Premiere Products now wants to manage information about the sup-
pliers from which it purchases parts. For each part, Premiere Products needs to know the number and name of
each supplier as well as the expected lead time for delivering each part. (Lead time is the amount of time a
supplier is expected to take to deliver the part after Premiere Products has ordered it.) Each part can have
many suppliers, and each supplier can supply many parts. Using this information, convert the following unnor-
malized relation to fourth normal form:
Part (PartNum, Description, Class, Price,
(WarehouseNum, WarehouseName, OnHand),
(SupplierNum, SupplierName, LeadTime) )
181
Henry Books Case
The following exercises are based on the Henry Books database.
1. Using the types of entities found in the Henry Books database (books, authors, and publishers), create an
example of a table that is in first normal form but not in second normal form and an example of a table that is in
second normal form but not in third normal form. In each case, justify your answers and show how to convert to
the higher forms.
2. Henry Books is considering selling textbooks to a local college. To do so, it must maintain information about
courses, textbooks, and instructors. Determine the multivalued dependencies in the following table, and then
convert this table to an equivalent collection of tables that are in fourth normal form. Each course is associated
with a specific set of textbooks independent of the instructors who are teaching the course. In other words,
although many instructors may be teaching the course, they all will use the same set of textbooks.
Course (CourseNum, Textbook, InstructorNum, InstructorName)
3. The following unnormalized table is similar in content to the table in Exercise 2. Note that this table has two
separate repeating groups: one listing the textbooks used for the course and the other listing the instructors who
teach the course. Convert it to fourth normal form. Did you encounter the table from Exercise 2 along the way?
Course (CourseNum, Description, NumCredits,
(Textbook), (InstructorNum, InstructorName) )
4.
Identify the functional dependencies in the following unnormalized table. Convert the table to third normal form.
Is the result also in fourth normal form? Why or why not?
Book (BookCode, Title, PublisherCode, PublisherName,
(AuthorNum, AuthorLast, AuthorFirst) )
Alexamara Marina Group Case
The following exercises are based on the Alexamara Marina Group database.
1. Using the types of entities found in the Alexamara Marina Group database (marinas, owners, boat slips, catego-
ries, and service requests), create an example of a table that is in first normal form but not in second normal
form and an example of a table that is in second normal form but not in third normal form. In each case, justify
your answer and show how to convert to the higher forms.
2. Determine the functional dependencies that exist in the following table, and then convert this table to an equiva-
lent collection of tables that are in third normal form:
Marina (MarinaNum, Name, (SlipNum, Length, RentalFee, BoatName) )
3. Determine the functional dependencies that exist in the following table, and then convert this table to an equiva-
lent collection of tables that are in third normal form:
MarinaSlip (SlipID, MarinaNum, SlipNum, Length, RentalFee, BoatName, BoatType,
OwnerNum, LastName, FirstName)