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.
However, the table is not normalized to the second rule. It has these problems:
- First, note that the first three rows in this table repeat the same data for the columns: Cust Num, Name, and Street. This is redundant data.
- Second, if the customer, Sue Jones, changes her address, you must then update all existing rows to reflect the new address. In this case, three rows. Any rows with the old address left unchanged leads to inconsistent data. Thus, your database lacks integrity .
- Third, you might want to trim your database by eliminating all orders before November 1, 1990, 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.
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.
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 |