Java Reference
In-Depth Information
Second normal form
The requirements of the second normal form are as follows:
The table must be in first normal form.
The table cannot contain fields that do not contain information related to the whole of the key.
The second normal form is only relevant when a table has a multipart key. In the
example shown in
table 1-8
, which shows inventory for each warehouse, the primary
key, which is the unique means of identifying a row, consists of two fields, the Name
field and the Warehouse field.
Second normal form requires that a table should only contain data related to one
entity, and that entity should be described by its primary key. The Warehouse
Inventory table is intended to describe inventory items in a given warehouse, so all
the data describing the inventory item itself is related to the primary key.
In the example of
Table 1-8
, the second row shows that there are 97 cases of Rice
Krispies in warehouse #2, purchased at a unit cost of $1.95, and 103 cases of Rice
Krispies in warehouse #7, purchased at a unit cost of $2.05. The warehouse address,
however, describes only part of the key, namely, the warehouse, so it does not
belong in the table. If this information is stored with every inventory item, there is a
potential risk of discrepancies between the address saved for a given warehouse in
different rows, since there is no clearly defined master reference. In addition, of
course, storing the same data item in multiple locations is very inefficient in terms of
space, and requires that any change to the data item be made to all rows containing
the data item, rather than to a single master reference.
Table 1-8: Warehouse Inventory Table
Name
Warehouse
Address
Description
Qty
Cost
Corn Flakes
Warehouse #2
123 Pine
Cereal
178
1.95
Rice Krispies
Warehouse #2
123 Pine
Cereal
97
1.95
Rice Krispies
Warehouse #7
24 Holly
Cereal
103
2.05
Oatmeal
Warehouse #7
24 Holly
Cereal
15
0.98
The solution is to move the warehouse address to a Warehouse table linked to the
Inventory table by a foreign key. The resulting tables would look like
Tables 1-9
and
1-10
. These tables are in the second normal form.
Table 1-9: Inventory Table in 2NF
Name
Warehouse
Description
Qty
Cost
