DBMS Notes
A relation is in Third Normal Form (3NF) if it satisfies two conditions: first, it must be in Second Normal Form (2NF), and second, no non-prime attribute...
Definition
A relation is in Third Normal Form (3NF) if it satisfies two conditions: first, it must be in Second Normal Form (2NF), and second, no non-prime attribute should be transitively dependent on the primary key. In simpler terms, every non-key attribute must depend directly on the primary key — not through another non-key attribute.
A transitive dependency occurs when we have a chain like PK → A → B, where A is a non-key attribute and B depends on A rather than directly on the primary key. The attribute B is said to be transitively dependent on the primary key through A.
Formal Definition
For every non-trivial functional dependency X → A in relation R, at least one of the following must hold:
- X is a superkey of R, or
- A is a prime attribute (part of some candidate key)
This formal definition is slightly more general than the transitive dependency explanation. It covers all cases where a non-superkey determines a non-prime attribute, which is the fundamental violation 3NF prohibits.
Anomalies Caused by Transitive Dependencies
| Anomaly | Example |
|---|---|
| Update Anomaly | Changing the CS department location requires updating all 50 employee rows |
| Insertion Anomaly | Cannot add a new department until at least one employee is assigned to it |
| Deletion Anomaly | If the last employee in a department leaves, the department information is lost |
These anomalies exist because we are storing information about two distinct entities (employees and departments) in a single table, linked by a transitive dependency.
Decomposition to Achieve 3NF
The remedy is to decompose the relation by extracting the transitive dependency into a separate relation:
Before (in 2NF but not 3NF)
Employee(EmpID, EmpName, DeptID, DeptName, DeptLocation)
Step 1: Identify transitive FDs:
DeptID → DeptName, DeptLocation
Step 2: Create a new relation with the transitive determinant as PK:
Department(DeptID, DeptName, DeptLocation)
Step 3: Remove transitively dependent attributes from original (keep FK):
Employee(EmpID, EmpName, DeptID)
Result (now in 3NF)
Employee(EmpID, EmpName, DeptID) ← PK: EmpID, FK: DeptID → Department
Department(DeptID, DeptName, DeptLocation) ← PK: DeptID
Now department information is stored exactly once, and the Employee table references it through a foreign key.
Another Detailed Example
Consider a student advisory system:
| Primary Key | StudentID |
| StudentID | StudentName, AdvisorID (direct — each student has one advisor) |
| AdvisorID | AdvisorName, AdvisorOffice (transitive — advisor info depends on AdvisorID) |
| StudentID | AdvisorID → AdvisorName |
| StudentID | AdvisorID → AdvisorOffice |
Decomposition:
3NF Synthesis Algorithm
When you have multiple functional dependencies, the 3NF synthesis algorithm (as opposed to decomposition) guarantees both lossless join and dependency preservation:
This algorithm is particularly useful when the decomposition approach might lose some functional dependencies.
Visual Comparison: Before and After 3NF
| Employee | Department | |
|---|---|---|
| ──────── | ────────── | |
| EmpID (PK) | DeptID (PK) | |
| EmpName | ───► | DeptName |
| DeptID (FK) | DeptLocation |
3NF vs BCNF: The Key Difference
| Aspect | 3NF | BCNF |
|---|---|---|
| Rule | Every non-trivial FD X→A: X is superkey OR A is prime | Every non-trivial FD X→A: X must be superkey |
| Allows | Prime attribute determined by non-superkey | No exceptions |
| Dependency Preservation | Always achievable | May not be achievable |
| Lossless Decomposition | Always achievable | Always achievable |
The exception in 3NF (allowing prime attributes to be determined by non-superkeys) is what makes 3NF less strict than BCNF but guarantees dependency preservation.
Key Takeaways
- 3NF eliminates transitive dependencies — no non-key attribute should depend on another non-key attribute
- If a relation is in 3NF, it is automatically in 2NF and 1NF
- Most practical database designs target 3NF as the minimum acceptable normal form
- The 3NF synthesis algorithm guarantees both lossless join and dependency preservation — a property BCNF decomposition cannot always provide
- A quick test: if every non-trivial determinant is either a superkey or the dependent is a prime attribute, the relation is in 3NF
Continue the lesson path