Database Reference
In-Depth Information
This notation indicates that OrderNum alone determines OrderDate and that PartNum alone determines
Description but that both an OrderNum and a PartNum are required to determine either NumOrdered or
QuotedPrice. (The combination of OrderNum and PartNum also determines both OrderDate and Description
because OrderNum determines OrderDate and PartNum determines Description.) Consider the sample of this
table shown in Figure 5-7.
Orders
163
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
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
FIGURE 5-7
Sample Orders table
The description of a specific part (DR93, for example) occurs twice in the table. This redundancy causes
several problems. It is wasteful of space, but that is not nearly as serious as some of the other problems.
These other problems are called update anomalies, and they fall into four categories:
1. Update. A change to the description of part DR93 requires not one change to the table, but two
changes
you have to change each row on which part DR93 appears. Changing multiple rows
makes the update process more cumbersome; it also is more complicated logically and takes
more time to update.
2. Inconsistent data. There is nothing about the design that would prohibit part DR93 from having
two different descriptions in the database. In fact, if part DR93 were to occur on 20 rows, it
could potentially have 20 different descriptions in the database!
3. Additions. You have a real problem when you try to add a new part and its description to the
database. Because the primary key for the table consists of both OrderNum and PartNum, you
need values for both columns when you want to add a new row. If you have a part to add, but
there are no orders for it yet, what order number do you use? The only solution is to use a ficti-
tious order number and then replace it with a real order number after Premiere Products
receives an order for the new part. Certainly, this is not an acceptable solution.
4. Deletions. If you deleted order 21608 from the database, you would lose all information about
part AT94. For example, you would no longer know that part AT94 is an iron.
—
These problems occur because you have a column, Description, that is dependent on only a portion of
the primary key (PartNum) and not on the complete primary key. This problem leads to the definition of
second normal form. Second normal form represents an improvement over first normal form because it elim-
inates update anomalies in these situations. To understand second normal form, you need to understand the
term nonkey column.
Definition:
A column is a nonkey column (also called a nonkey attribute) when it is not a part of the pri-
mary key.
Definition:
A table (relation) is in second normal form (2NF) when it is in first normal form and no nonkey
column is dependent on only a portion of the primary key.
