Achieving Third Normal Form
You'll learn: How to remove transitive dependencies from 2NF tables by breaking them into separate, more focused tables.
The Core Problem
You've reached Second Normal Form (2NF)—congratulations! Your tables no longer have partial dependencies. But there's still one sneaky data redundancy issue: transitive dependencies. These occur when a non-key attribute depends on another non-key attribute, which itself depends on the primary key.
Remember: In a transitive dependency, if A → B and B → C, then A → C indirectly. We want to remove that indirect path.
The Decomposition Process
To achieve Third Normal Form (3NF), you decompose 2NF tables by:
- Identifying transitive dependencies – Find non-key attributes that determine other non-key attributes
- Creating new tables – Move the transitively dependent attributes into their own table
- Establishing foreign keys – Keep the determining attribute in the original table as a foreign key reference
Example Walkthrough
Consider a 2NF table:
Employee(EmployeeID, Name, DeptID, DeptName, DeptLocation)
Here, DeptID → DeptName and DeptID → DeptLocation. The department attributes depend on DeptID, not directly on EmployeeID (our primary key). That's a transitive dependency!
Decompose into:
Employee(EmployeeID, Name, DeptID)
Department(DeptID, DeptName, DeptLocation)
Now each table has a clear, single focus. Department information appears exactly once, eliminating update anomalies.
The Analogy
Think of your contact list. If you store both a friend's phone number and their city, state, and country based on their area code, you're storing redundant geographic data. Instead, store just the area code, then look up location details in a separate area code reference table.
Key Takeaway: Achieving 3NF means extracting transitively dependent attributes into separate tables, leaving only direct dependencies on the primary key in each table.