Third Normal Form (3NF) — Database Management Systems

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:

Example

Student(StudentID, StudentName, AdvisorID) ← PK: StudentID Advisor(AdvisorID, AdvisorName, AdvisorOffice) ← PK: AdvisorID Now: - Advisor info stored once regardless of how many students they advise - Can add a new advisor without assigning students - Removing last student of an advisor does not delete advisor info

3NF Synthesis Algorithm

When you have multiple functional dependencies, the 3NF synthesis algorithm (as opposed to decomposition) guarantees both lossless join and dependency preservation:

Example

Algorithm: 1. Find minimal cover (canonical cover) of FDs 2. For each FD X → A in the minimal cover: Create a relation R(X, A) with X as primary key 3. If no relation contains a candidate key of the original relation: Add a relation containing a candidate key 4. Remove redundant relations (those whose attributes are a subset of another)

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

Definition

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)

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:

Example

3NF Synthesis Algorithm

When you have multiple functional dependencies, the 3NF synthesis algorithm (as opposed to decomposition) guarantees both lossless join and dependency preservation:

Example

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

Third Normal Form (3NF) — Database Management Systems

Definition

Formal Definition

Anomalies Caused by Transitive Dependencies

Decomposition to Achieve 3NF

Before (in 2NF but not 3NF)

Result (now in 3NF)

Another Detailed Example

3NF Synthesis Algorithm

Visual Comparison: Before and After 3NF

3NF vs BCNF: The Key Difference

Key Takeaways

Continue learning this concept

Third Normal Form (3NF) — Database Management Systems

Definition

Formal Definition

Anomalies Caused by Transitive Dependencies

Decomposition to Achieve 3NF

Before (in 2NF but not 3NF)

Result (now in 3NF)

Another Detailed Example

3NF Synthesis Algorithm

Visual Comparison: Before and After 3NF

3NF vs BCNF: The Key Difference

Key Takeaways

Continue learning this concept