Progress
Database Design
Guide


The Second Normal Form

A table is in the second normal form when it is in the first normal form and only contains columns that give you information about the key of the table.

The second rule of normalization is that you must remove to a new table those columns that don’t depend on the primary key of the current table.

Table 2–5 shows a Customer table that is in first normal form because there are no duplicate columns and every column has exactly one value.

Table 2–5: Customer Table with Repeated Data 
Cust
Num

Name

Street
Order
Number
Order
Date
Order
Amount
101
Jones, Sue
2 Mill Ave.
M31
3/19/91
$400.87
101
Jones, Sue
2 Mill Ave.
M98
8/13/91
$3,000.90
101
Jones, Sue
2 Mill Ave.
M129
2/9/91
$919.45
102
Hand, Jim
12 Dudley St.
M56
5/14/90
$1,000.50
103
Lee, Sandy
45 School St.
M37
12/25/90
$299.89
103
Lee, Sandy
45 School St.
M140
3/15/91
$299.89
104
Tan, Steve
67 Main St.
M41
4/2/90
$2,300.56

However, the table is not normalized to the second rule. It has these problems:

So, how do you resolve the problems? Note that this table contains information about an individual customer, such as Cust Num, Name, and Street, that remain the same when you add an order. In other words, columns like Order Num, Order Date, and Order Amount do not pertain to the customer and do not depend on the primary key Cust Num. They should be in a different table.

To reduce the Customer table to the second normal form, divide it into two tables, as shown in Table 2–6 and Table 2–7.

Table 2–6: Customer Table 
Cust Num
(Primary Key)
Name
Street
101
Jones, Sue
2 Mill Ave.
102
Hand, Jim
12 Dudley St.
103
Lee, Sandy
45 School St.
104
Tan, Steve
67 Main St.

Table 2–7: Order Table
Order Number
(Primary Key)
Order Date
Order Amount
Cust Num
(Foreign Key)
M31
3/19/91
$400.87
101
M98
8/13/91
$3,000.90
101
M129
2/9/91
$919.45
101
M56
5/14/90
$1,000.50
102
M37
12/25/90
$299.89
103
M140
3/15/91
$299.89
103
M41
4/2/90
$2,300.56
104

Note that the Customer table now contains only one row for each individual customer, while the Order table contains one row for every order, and Order Number is its primary key. The Order table contains a common column, Cust Num, that relates the Order rows with the Customer rows.

A table that is normalized to the second normal form has these advantages:


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095