Third Normal Form (3NF) in DBMS – Eliminate Transitive Dependencies

Third Normal Form (3NF) in DBMS – Eliminate Transitive Dependencies

Third Normal Form (3NF) is a critical step in the database normalization process. It builds on Second Normal Form (2NF) by removing transitive dependencies, ensuring that non-key attributes depend only on the primary key—and not indirectly through another non-key attribute.

✅ Requirements of 3NF

To satisfy 3NF, a table must:

• ✅ Already be in 2NF
• ❌ Have no transitive dependencies (i.e., non-key attributes must not depend on other non-key attributes)

🧪 Example: Student_Department Table (Not in 3NF)

Student_ID	Student_Name	Department_ID	Department_Name	Department_Location
101	Alice Smith	D01	Science	Building A
102	Bob Johnson	D02	Arts	Building B
103	Carol White	D03	Commerce	Building C

• Primary Key: Student_ID

• Problem:

  • Department_ID → Department_Name, Department_Location
  • Student_ID → Department_ID → Department_Name / Location ➤ This is a transitive dependency.

🔄 Converting to 3NF

To remove the transitive dependency, split the table:

✅ Step 1: Student Table

Student_ID	Student_Name	Department_ID
101	Alice Smith	D01
102	Bob Johnson	D02
103	Carol White	D03

• Primary Key: Student_ID

✅ Step 2: Department Table

Department_ID	Department_Name	Department_Location
D01	Science	Building A
D02	Arts	Building B
D03	Commerce	Building C

• Primary Key: Department_ID

✅ Result After Conversion to 3NF

• No attribute depends indirectly on Student_ID.
• Department_Name and Department_Location now depend only on Department_ID, not on Student_ID.

🎯 Why 3NF Matters

• 🧹 Reduces Redundancy: No repetition of department info across multiple student records.
• ✅ Improves Data Integrity: Easy to update department info in a single place.
• 🔧 Simplifies Maintenance: Less chance of anomalies during updates, insertions, or deletions.

🧠 Summary

Feature	Description
Normal Form	Third Normal Form (3NF)
Focus	Eliminate transitive dependencies
Requirements	Must be in 2NF, and all non-key attributes must depend only on the primary key
Result	More efficient, consistent, and easily maintainable data structures