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

This notation indicates a table named Orders consisting of a primary key (OrderNum) and a column named
OrderDate. The inner parentheses indicate that there is a repeating group. The repeating group contains two col-
umns, PartNum and NumOrdered. This means that for a single order, there can be multiple combinations of a part
number and a corresponding number of units ordered, as illustrated in Figure 5-5. The row for order 21617, for
example, contains two such combinations. In the first combination, the part number is BV06 and the number
ordered is 2. In the second combination, the part number is CD52 and the number ordered is 4.
To convert the Orders table to first normal form, you remove the repeating group as follows:

156

Orders (OrderNum, OrderDate, PartNum, NumOrdered)
Figure 5-6 shows the new table, which is now in first normal form.

Orders

OrderNum OrderDate PartNum NumOrdered

21608 10/20/2010 AT94 11

21610 10/20/2010 DR93 1

21610 10/20/2010 DW11 1

21613 10/20/2010 KL62 4

21614 10/20/2010 KT03 2

21617 10/20/2010 BV06 2

21617 10/20/2010 CD52 4

21619 10/20/2010 DR93 1

21623 10/20/2010 KV29 2

FIGURE 5-6 Result of normalization (conversion to first normal form)

Note that the fifth row of the unnormalized table (see Figure 5-5) indicates that part BV06 and part CD52
are both present for order 21617. In the normalized table (see Figure 5-6), this information is represented by
two rows, the sixth and seventh. The primary key to the unnormalized Orders table was OrderNum alone.
The primary key to the normalized table is now the combination of OrderNum and PartNum.
In general, when converting a table that is not in first normal form to first normal form, the primary key will usu-
ally include the original primary key concatenated with the key to the repeating group, which is the column that
distinguishes one occurrence of the repeating group from another on a given row in the table. In this case,
PartNum is the key to the repeating group; thus, PartNum becomes part of the primary key of the first normal
form table.

SECOND NORMAL FORM

A table that is in first normal form still might contain problems that will require you to restructure it. Con-
sider the following table:

Orders (OrderNum, OrderDate, PartNum, Description, NumOrdered, QuotedPrice)

This table has the following functional dependencies:

OrderNum OrderDate
PartNum Description
OrderNum, PartNum NumOrdered, QuotedPrice, OrderDate, Description