Lesson 35: Second Normal Form (2NF)

To achieve 2NF, a table must already be in 1NF, and it must satisfy an additional condition regarding composite keys.

Rule for 2NF

No non-key attribute is partially dependent on any candidate key.

This rule applies only when a table has a Composite Primary Key (a PK made up of two or more columns).

• Non-Key Attribute: Any column that is not part of the primary key.
• Partial Dependency: When a non-key attribute is determined by only a part of the composite key, not the whole key.

Violation Example: Order Details

Assume a composite PK: (OrderID, ProductID).

• ProductName and Price only depend on ProductID (a part of the key).
• Quantity depends on the full key (OrderID + ProductID).

If the ProductName changes, we must update it for every order it appears in (redundancy).

Correction (Achieving 2NF)

We separate the partially dependent attributes into a new table:

1. Products Table: PK: ProductID. Contains ProductName and Price.
2. OrderDetails Table: Composite PK: (OrderID, ProductID). Contains Quantity.

Now, ProductName is dependent only on the key in its own table, removing partial dependency.