Second normal form

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 6. 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 6 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 7 and Table 8.

Table 7. 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 8. 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.