Database develop. life cycle - Third Normal Form (3NF)

Third Normal Form (3NF)

1. Definition

A table is in 3NF if:

  1. It is already in 2NF.

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

In simple words:

  • A non-key attribute should depend only on the primary key, not on another non-key attribute.

2. Example – Table in 2NF

From 2NF, we had:

Student Table

StudentID Name Phone Department
101 Alice 555-1234 CS
102 Bob 555-8765 IT

Enrollment Table

StudentID Course
101 DBMS
101 Networks
102 DBMS

3. Problem (Transitive Dependency)

  • Suppose Department depends on StudentID through Name or just depends on Name instead of the key.

  • Or in another example: If we had a Course table like this:

CourseID CourseName Instructor InstructorPhone

  • InstructorPhone depends on Instructor, not directly on CourseID.

  • This is a transitive dependency (CourseID → Instructor → InstructorPhone).

4. Transforming to 3NF

We remove transitive dependencies by creating separate tables:

Course Table

CourseID CourseName Instructor

Instructor Table

Instructor InstructorPhone

Now:

  • InstructorPhone depends directly on Instructor, not CourseID.

  • All tables have attributes depending only on their primary key.

Student Table remains the same if no transitive dependency exists.

5. Why 3NF Matters

  • Eliminates redundant data caused by indirect dependencies.

  • Reduces anomalies for insert, update, and delete operations.

  • Ensures a clean, efficient database design.

6. Summary

  • 1NF: No repeating groups; atomic values.

  • 2NF: No partial dependency on a composite key.

  • 3NF: No transitive dependency on the primary key.