Database Reference
In-Depth Information
NOTE
When a table's primary key contains only one column, the table is automatically in second normal form because there would be
no way for a column to be dependent on only a portion of the primary key.
For another perspective on second normal form, consider Figure 5-8. This type of diagram, sometimes
called a dependency diagram, uses arrows to indicate all the functional dependencies present in the Orders
table. The arrows above the boxes indicate the normal dependencies that should be present; in other words,
the primary key functionally determines all other columns. (In the Orders table, the concatenation of
OrderNum and PartNum determines all other columns.) The arrows below the boxes prevent the table from
being in second normal form. These arrows represent types of dependencies that are often called partial
dependencies, which are dependencies on only a portion of the primary key. In fact, another definition for
second normal form is a table that is in first normal form but that contains no partial dependencies.
164
OrderNum
OrderDate
PartNum
Description
NumOrdered
QuotedPrice
FIGURE 5-8
Dependencies in the Orders table
Regardless of which definition of second normal form you use, you now can identify the fundamental
problem with the Orders table: It is not in second normal form. Although it may be pleasing to have a name
for the problem, what you really need is a method to correct it; you need a way to convert tables to second
normal form. To do so, first take each subset of the set of columns that makes up the primary key; then
begin a new table with this subset as the primary key. For the Orders table, this would give the following:
(OrderNum,
(PartNum,
(OrderNum, PartNum,
Next, place each of the other columns with its appropriate primary key; that is, place each primary key
with the minimal collection of columns on which it depends. For the Orders table, this would yield the
following:
(OrderNum, OrderDate)
(PartNum, Description)
(OrderNum, PartNum, NumOrdered, QuotedPrice)
s contents, such as Orders, Part,
or OrderLine. Figure 5-9 shows the original Orders table on top; the resulting Orders, Part, and OrderLine
tables created after the Orders table was converted to second normal form appear below it.
Now you can give each new table a name that is descriptive of the table
'
