DATABASE DESIGN 1: NORMALIZATION - Concepts of Database Management - page 165

Orders

OrderNum OrderDate PartNum Description NumOrdered QuotedPrice

21608 10/20/2013 AT94 Iron 11 $21.95

21610 10/20/2013 DR93 Gas Range 1 $495.00

21610 10/20/2013 DW11 Washer 1 $399.99

21613 10/21/2013 KL62 Dryer 4 $329.95

165

21614 10/21/2013 KT03 Dishwasher 2 $595.00

21617 10/23/2013 BV06 Home Gym 2 $794.95

21617 10/23/2013 CD52 Microwave Oven 4 $150.00

21619 10/23/2013 DR93 Gas Range 1 $495.00

21623 10/23/2013 KV29 Treadmill 2 $1290.00

Orders Part OrderLine

OrderNum OrderDate PartNum Description OrderNum PartNum NumOrdered QuotedPrice

21608 10/20/2013 AT94 Iron 21608 AT94 11 $21.95

21610 10/20/2013 BV06 Home Gym 21610 DR93 1 $495.00

21613 10/21/2013 CD52 Microwave Oven 21610 DW11 1 $399.99

21614 10/21/2013 DL71 Cordless Drill 21613 KL62 4 $329.95

21617 10/23/2013 DR93 Gas Range 21614 KT03 2 $595.00

21619 10/23/2013 DW11 Washer 21617 BV06 2 $794.95

21623 10/23/2013 FD21 Stand Mixer 21617 CD52 4 $150.00

KL62 Dryer 21619 DR93 1 $495.00

KT03 Dishwasher 21623 KV29 2 $1290.00

KV29 Treadmill

FIGURE 5-9 Conversion to second normal form

Now you have eliminated the update anomalies. A description appears only once for each part, so you do
not have the redundancy that you did in the previous design. Changing the description for part DR93 from
Gas Range to Deluxe Range, for example, now is a simple process involving a single change. Because the
description for a part occurs in a single place, it is not possible to have multiple descriptions for a single part
in the database at the same time.
To add a new part and its description, you create a new row in the Part table; there is no need to have an
existing order for that part. Also, deleting order 21608 does not delete part AT94 from the Part table, and you
still have its description (Iron) in the database. Finally, you have not lost any information in the process

— you

can reconstruct the data in the original design from the data in the new design.

THIRD NORMAL FORM

Problems can still exist with tables that are in second normal form. Consider the following Customer table:

Customer (CustomerNum, CustomerName, Balance, CreditLimit, RepNum, LastName, FirstName)

The functional dependencies in this table are as follows:

CustomerNum → CustomerName, Balance, CreditLimit, RepNum, LastName, FirstName
RepNum → LastName, FirstName