Fall Semester 2002

Problem One (Premiere Products, page 167)

One has to understand that the table we start from is quite meaningful from a user point of view. With this structure we have, for example, part number AT94, ordered only once, with OrderNum 21608, that is, the repeating group has only one element. Other parts, however, have a repeating group of more than one element. Thus, if we choose DR93, there's 21610 and 21619, order numbers for two different orders. The first one is customer 356, and the second one is by 148.

The problem, as illustrated in the book, is when we have a part that has not been ordered yet. For that we need to work on the table, and change its structure to eliminate update anomalies.

First we determine functional dependencies:

PartNum determines

• Description
• OnHand
• Class
• Warehouse
• Price

OrderNum determines:

• OrderDate, and
• CustomerNum which determines
  • CustomerName, and
  • RepNum which determines
    • LastName and
    • FirstName

There's also (PartNum, OrderNum) which determines

• NumOrdered
• QuotedPrice

Do you have a key?

Do you see the problems?

What do you do?

(Check 143 for FD and 145-146 and 164 for transformations).

Steps:

1. Normalize the table (it has repeating groups).
2. Eliminate partial dependencies.
3. Make sure the only determinants are candidate keys.

I get four tables.

Problem Two (Premiere Products, page 167)

This is an extremely similar problem.

My understanding is that:

InvoiceNum determines

• CustomerNum which determines
  • CustomerName
  • Street
  • City
  • State
  • Zip
• and Date

• Description
• Price

At the same time we have (InvoiceNum, PartNume) determine

• NumShipped

Is my understanding correct?

If so, what do we do next?

If no, what can we do to fix this?

When you have an answer, proceed further with the following requirements:

• no repeating groups
• no partial dependencies
• only determinants are candidate keys

The next problem to be discussed in Lecture Notes 12 (that follow).