This lecture covers Third Normal Form (3NF). We’ve covered 1NF (atomic values) and 2NF (no partial dependencies). The final normal form we will discuss in this series is Third Normal Form (3NF). The rule for 3NF is:
A table is in 3NF if it is in 2NF and it has no transitive dependencies.
A transitive dependency occurs when a non-key column depends on another non-key column, which in turn depends on the primary key. Think of it as an indirect dependency: PK -> Non-Key Column A -> Non-Key Column B.
An Example of a 3NF Violation
Let’s imagine a simple table of employees that includes their department and the department’s location.
Employees Table (Violates 3NF)
| EmployeeID | Name | Department | DepartmentLocation |
|---|---|---|---|
| 1 | Alice | Sales | New York |
| 2 | Bob | Engineering | San Francisco |
| 3 | Charlie | Sales | New York |
This table is in 1NF (atomic values, has a PK) and 2NF (no composite key, so no partial dependencies). But it violates 3NF. Let’s analyze the dependencies:
Namedepends on `EmployeeID` (the primary key). This is good.Departmentdepends on `EmployeeID`. This is also good.DepartmentLocationdepends on `Department`. This is a problem. `Department` is not the primary key.
We have a transitive dependency: `EmployeeID` -> `Department` -> `DepartmentLocation`. This causes data redundancy. The fact that the Sales department is in New York is repeated for every employee in that department. If the department moves, we have to update it in multiple places, which can lead to errors.
Making it 3NF
As with other normalization rules, the solution is to split the table to remove the transitive dependency. We create a separate table for the departments.
Employees Table
| EmployeeID (PK) | Name | DepartmentID (FK) |
|---|---|---|
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | 10 |
Departments Table
| DepartmentID (PK) | DepartmentName | Location |
|---|---|---|
| 10 | Sales | New York |
| 20 | Engineering | San Francisco |
Now, the `DepartmentLocation` (now just `Location`) depends only on its table’s primary key, `DepartmentID`. The `Employees` table only contains information about employees. The transitive dependency is gone, and our design is in 3NF.
The phrase “Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key” is a good way to remember the rules of 1NF, 2NF, and 3NF.
In the Next Lecture…
Normalization is great for data integrity, but sometimes it can make queries slower because you have to join many tables. We will now look at a key technique for speeding up data retrieval: creating an index.
Lecture 35: Second Normal Form 2NF
