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

• Third Normal Form (3NF) in DBMS – Eliminate Transitive Dependencies
  • ✅ Requirements of 3NF
  • 🧪 Example: Student_Department Table (Not in 3NF)
  • 🔄 Converting to 3NF
  • ✅ Result After Conversion to 3NF
  • 🎯 Why 3NF Matters
  • 🧠 Summary

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)

• 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

• Primary Key: Student_ID

✅ Step 2: Department Table

• 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