3NF

Definition

A relation is in Third Normal Form (3NF) if it is already in 2NF, and it has no transitive dependency (21.8) — every non-key attribute must depend directly on a candidate key, not indirectly through another non-key attribute.

Formally, for every non-trivial FD X -> Y in the relation, at least one of these must hold: (a) X is a superkey, or (b) Y consists only of prime attributes (attributes belonging to some candidate key). This "(b)" escape clause is the exact detail that separates 3NF from the stricter BCNF (21.15).

Diagnosing the Violation

After the 2NF decomposition (21.13), Course(CourseID, CourseName, InstructorID, InstructorName, InstructorOffice) is in 2NF (its key, CourseID, is single-attribute, so no partial dependency is possible) but not in 3NF, because of the transitive chain:

CourseID -> InstructorID -> InstructorName
CourseID -> InstructorID -> InstructorOffice

InstructorID is not a superkey of Course (it doesn't determine CourseName, since one instructor can — in general — teach multiple courses), and InstructorName/InstructorOffice are not prime attributes. So condition (a) and (b) both fail: this is a genuine 3NF violation.

The Decomposition

Split Instructor out of Course:

Instructor(InstructorID, InstructorName, InstructorOffice)
Course(CourseID, CourseName, InstructorID)

with Course.InstructorID now a foreign key into Instructor.

The Final Schema

Combining this with the 2NF split from 21.13, the fully 3NF-normalized schema is:

Student(StudentID, StudentName)
Instructor(InstructorID, InstructorName, InstructorOffice)
Course(CourseID, CourseName, InstructorID)
Enrollment(StudentID, CourseID, EnrollmentDate)

Now: Dr. Fernandez (I4, Room 402) can be inserted into Instructor with zero dependency on any course or enrollment — the insertion anomaly is gone. Changing Dr. Rao's office is a single UPDATE on one row of Instructor — the update anomaly is gone. Dropping the sole enrollment in a course no longer erases the course's or instructor's row, since they live independently — the deletion anomaly is gone. All three anomalies from 21.2-21.4, and the redundancy from 21.1, are now fully resolved for this schema.

Why 3NF Is Usually "Enough" in Practice

3NF is the normal form most real-world production schemas target, because it eliminates all three classic anomalies while remaining simple to reason about and query (a handful of clean joins rather than a large number of hyper-fragmented tables). BCNF, 4NF, and 5NF close increasingly rare, subtler loopholes that most everyday schemas never encounter.

Edge Cases

• 3NF's "prime attribute" escape clause (condition b) is deliberate — it exists so that certain schemas with overlapping candidate keys don't get needlessly over-decomposed. This is exactly the case explored in the next topic, BCNF.
• Check 3NF against every candidate key of the relation, exactly as with 2NF.

Key Takeaways / Interview Angle

• Q: State 3NF in one sentence. 2NF, plus no transitive dependency — every non-key attribute must depend directly on a candidate key, not through another non-key attribute.
• Q: What decomposition achieves 3NF here? Splitting Instructor(InstructorID, InstructorName, InstructorOffice) out of Course, leaving Course(CourseID, CourseName, InstructorID).
• Q: Why does 3NF include the "or Y is prime" escape clause that BCNF removes? It's a deliberate relaxation that avoids over-decomposing schemas with overlapping candidate keys — a nuance made concrete in the BCNF discussion (21.15).