The second rule of normalization is that you must move those columns that do not depend on the primary key of the current table to a new table. 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 following table shows a Customer table that is in the first normal form because there are no duplicate columns, and every column has exactly one value.

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

However, the table is not normalized to the second rule because it has these problems:

  • The first three rows in this table repeat the same data for the columns Cust Num, Name, and Street. This is redundant data.
  • If the customer Sue Jones changes her address, you must then update all existing rows to reflect the new address. In this case, you would update three rows. Any row with the old address left unchanged leads to inconsistent data, and your database will lack integrity.
  • You might want to trim your database by eliminating all orders before November 1, 2004, but in the process, you also lose all the customer information for Jim Hand and Steve Tan. The unintentional loss of rows during an update operation is called an anomaly.

To resolve these problems, you must move data. Note that Table 1 contains information about an individual customer, such as Cust Num, Name, and Street, that remains the same when you add an order. 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, move the Order Date and Order Amount columns to the Order tables, as shown in Table 2 and Table 3.

Table 2. 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 3. Order table
Order Number (Primary key) Order Date Order Amount Cust Num (Foreign key)
M31 3/19/05 $400.87 101
M98 8/13/05 $3,000.90 101
M129 2/9/05 $919.45 101
M56 5/14/04 $1,000.50 102
M37 12/25/04 $299.89 103
M140 3/15/05 $299.89 103
M41 4/2/04 $2,300.56 104

The Customer table now contains only one row for each individual customer, while the Order table contains one row for every order, and the 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:

  • It allows you to make updates to customer information in just one row.
  • It allows you to delete customer orders without eliminating necessary customer information.
  • It uses disk space more efficiently because no repeating or redundant data is stored.