Fall Semester 2002


Lecture Notes Eleven: Help with Homework Four (I)

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

Can you see why?

OrderNum determines:

There's also (PartNum, OrderNum) which determines

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

I also see PartNum determine

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

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:

I again get four tables.

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


Last updated: Oct 14, 2002 by Adrian German for A114/I111