Achieving Second Normal Form
You'll learn how to eliminate partial dependencies by splitting one table into smaller, focused tables.
What Does "Achieving 2NF" Mean?
You've identified partial dependencies in your 1NF table—now it's time to fix them. Achieving Second Normal Form means decomposing (splitting) your table so that every non-key attribute depends on the entire primary key, not just part of it.
This only matters when you have a composite key (a primary key made of multiple columns). If a column depends on only one part of that composite key, it creates a partial dependency—and that's what we're eliminating.
The Decomposition Process
Step 1: Identify all partial dependencies in your 1NF table.
Step 2: For each part of the composite key that has attributes depending only on it, create a new table:
- That key component becomes the primary key of the new table
- Move all partially dependent attributes into this new table
Step 3: Keep a table with the original composite key, retaining only attributes that depend on the full key.
Example
Suppose you have:
OrderDetails (OrderID, ProductID, ProductName, Quantity)
- Primary key: (OrderID, ProductID)
- ProductName depends only on ProductID (partial dependency!)
Decompose into:
- Products (ProductID, ProductName)
- OrderDetails (OrderID, ProductID, Quantity)
Now ProductName lives where it belongs—with Product. OrderDetails references it via ProductID (which will become a foreign key).
Why This Matters
By moving partially-dependent data to its own table, you avoid redundancy and update anomalies. ProductName is stored once per product, not once per order line.
Key Takeaway: Achieving 2NF means creating separate tables for each component of a composite key, moving attributes that depend only on that component into their own table.