Back to course

Third Normal Form (3NF)

Database Fundamentals: From Zero to Hero

Lesson 36: Third Normal Form (3NF)

3NF is the most commonly desired level of normalization for transactional databases. A table in 3NF is in 2NF and meets one additional criterion.

Rule for 3NF

No non-key attribute is transitively dependent on the Primary Key.

  • Transitive Dependency: This occurs when a non-key attribute determines another non-key attribute.

PK -> NonKeyAttribute1 and NonKeyAttribute1 -> NonKeyAttribute2

Violation Example: Employee Location Data

Assume PK: EmployeeID.

EmployeeID (PK)EmployeeNameDeptIDDeptNameDeptManager
101AliceD01SalesJohn
102BobD01SalesJohn
  • EmployeeID -> DeptID (Normal dependence)
  • DeptID -> DeptName (Transitive dependence, as DeptName is dependent on the non-key attribute DeptID).

If we change the name of Department D01, we must update multiple employee records (Update Anomaly).

Correction (Achieving 3NF)

We separate the transitively dependent attributes (DeptName, DeptManager) into a new table (Departments).

  1. Employees Table: PK: EmployeeID. Contains FK: DeptID.
  2. Departments Table: PK: DeptID. Contains DeptName, DeptManager.

Now, data about the Department only appears once, and the dependency is direct and managed via the Foreign Key.