Database Management System (DBMS) / Normalization in DBMS

3NF

📘 What is 3NF (Third Normal Form)?

A table is in 3NF if:

  1. It is already in 2NF, and

  2. No non-prime attribute is transitively dependent on the primary key

🔁 What is Transitive Dependency?

A transitive dependency exists when:

A → B  and  B → C  ⇒  A → C

📌 In database terms: A non-key attribute depends on another non-key attribute, instead of depending directly on the primary key.

🧾 Example (Table in 2NF, but not in 3NF):

🎓 Student_Detail Table:

Student_ID

Name

Dept_ID

Dept_Name

101

Swati

D1

CSE

102

Ravi

D2

IT

✅ Primary Key = Student_ID

❌ Dept_Name depends on Dept_ID

❌ Dept_ID depends on Student_ID

➡️ So, Dept_Name depends transitively on Student_ID

➡️ Violates 3NF

🎯 Why 3NF is Important?

  • Removes transitive dependencies

  • Prevents update anomalies

  • Makes schema cleaner and easier to maintain

🧠 Key Concepts Recap:

Concept

In 3NF?

Atomic values

✅ Yes (from 1NF)

No partial dependencies

✅ Yes (from 2NF)

No transitive dependencies

✅ Yes

All attributes depend only on PK

✅ Yes

⚠️ When is 3NF Not Enough?

If multiple candidate keys exist or non-trivial FDs don’t originate from candidate keys — you may need to apply BCNF (Boyce-Codd Normal Form).