Databases 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
157
OrderNum
OrderDate
PartNum
Description
NumOrdered
QuotedPrice
21608
10/20/2010
AT94
Iron
11
$21.95
21610
10/20/2010
DR93
Gas Range
1
$495.00
21610
10/20/2010
DW11
Washer
1
$399.99
21613
10/21/2010
KL62
Dryer
4
$329.95
21614
10/21/2010
KT03
Dishwasher
2
$595.00
21617
10/23/2010
BV06
Home Gym
2
$794.95
21617
10/23/2010
CD52
Microwave Oven
4
$150.00
21619
10/23/2010
DR93
Gas Range
1
$495.00
21623
10/23/2010
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 hav-
ing 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 do you use for an order number? The only solution is to make up a
dummy order number and then replace it with a real order number after an order for the new part
is received. 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 sec-
ond normal form. Second normal form represents an improvement over first normal form because it elimi-
nates 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 non-
key column is dependent on only a portion of the primary key.
