Course contentsShow
← Computer Science
Lesson 1639 of 2,87237. Database Design and NormalizationFree lesson

Achieving Third Normal Form

Decomposing 2NF tables to eliminate transitive dependencies by separating dependent attributes into new tables.

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:

  1. Identifying transitive dependencies – Find non-key attributes that determine other non-key attributes
  2. Creating new tables – Move the transitively dependent attributes into their own table
  3. 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.