Answer to Problem 2.8

Step 1

The Invoice table as shown [#tab:invoice-example above] is not in 1NF because of the attributes containing derived values. Ext Price is calculated by multiplying Price by # used. Tax is calculated by multiplying the total of all the Ext Prices for a particular invoice by the Tax rate. Total is calculated by adding Tax to the total of all the Ext Prices for a particular invoice. Thus, Ext Price, Tax, and Total all contain derived data; therefore, the Invoice table is not in 1NF (and, therefore, not in 2NF or 3NF).

Step 2

Now, let's go through the procedure to define 3NF relations. Each attribute is already single-valued. As pointed out above, there are three attributes containing derived values. Drop these attributes. There are not any repeating groups. The primary identifier is [Inv #, Part #]}. Thus, the 1NF relation is

[Inv #, Part #] —> [Date, Cust ID, Name, Desc, Price, Tax rate, # used]

I would remove the following relations from the 1NF relation since each depends on only part of the primary identifier:

  • Inv # —> [Date, Cust ID, Name, Tax rate]
  • Part # —> [Desc, Price]

The original relation is now [Inv #, Part #] —> # used.

Now you look for some attribute(s) on the right side of the above relations that determines some other attribute(s) on the right side of the same relation. The Name attribute is functionally determined by the Cust ID attribute — and the Cust ID attribute is not part of the primary identifier. Thus we have a problem here with a transitive dependency. This is the only attribute dependent on an attribute other than the primary identifier. Create a new relation showing this dependency and drop the Name attribute from the relation with Inv # as the primary identifier. We are left with the following relations:

  • Inv # —> [Date, Cust ID, Tax rate]
  • Part # —> [Desc, Price]
  • Cust ID —> Name
  • Inv #, Part # —> # used

These four relations are in 3NF.

Return to 3rd normal form.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License