Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

III.1.3. Second Normal Form: Dependent on the Whole Key

The second normal form states that the table must be in the first normal form and each column must be dependent on the entire primary key. The second normal form comes into play only on tables that have more than one column designated as the primary key. Having more than one column as the primary key is also known as a composite key.

If a table doesn't have a composite key (only one column is designated as the primary key), and the table is in the first normal form, then it's automatically considered to be in the second normal form.

Comparatively, if a table has a composite primary key, is in the first normal form, and has columns that aren't dependent on each of the columns in the primary key, then it's not in the second normal form.

For example, consider Figure 1-4. Only the OrderDetails table has a composite key (composed of the OrderID and ProductID columns). Because the Orders and Products table both have only one column in the primary key, they're automatically considered compliant with the second normal form as long as they're in the first normal form.

Figure III.1-4. OrderDetails isn't compliant with the second normal form.

For the OrderDetails table to be compliant with the second normal form, each of the non-primary key columns (ItemQty and Manufacturer) must be fully dependent on the entire primary key (both OrderID ...