BCNF

Definition

Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF: for every non-trivial functional dependency X -> Y in the relation, X must be a superkey — full stop, no exceptions. It removes the "or Y is prime" escape clause that 3NF allows (21.14). Every relation in BCNF is automatically in 3NF, but not every 3NF relation is in BCNF.

Why the Main Schema Doesn't Show the Difference

The clean 3NF schema from 21.14 — Student, Instructor, Course(CourseID, CourseName, InstructorID), Enrollment — happens to already be in BCNF too, because each relation has a single, simple candidate key and every determinant is that key. To see the 3NF/BCNF gap, we need a relation with overlapping candidate keys, so zoom into a closely related scenario.

The BCNF-Violating Example

Suppose the department also tracks, for auditing, which specific instructor's section each student attends per course — some large courses run multiple sections, each with a different instructor, but as a firm departmental policy, each instructor is assigned to teach sections of only one course (no instructor splits time across courses). Model this as:

SectionAssignment(StudentID, CourseID, InstructorID)

with these FDs:

• {StudentID, CourseID} -> InstructorID (a student in a course attends exactly one section/instructor)
• InstructorID -> CourseID (by policy, each instructor belongs to exactly one course)

Candidate keys: {StudentID, CourseID} (closure reaches InstructorID, hence everything) and also {StudentID, InstructorID} (since InstructorID -> CourseID, its closure also reaches everything). Both CourseID and InstructorID are therefore prime attributes — each belongs to some candidate key.

Is this in 3NF? Check InstructorID -> CourseID: is InstructorID a superkey? No — its closure alone only reaches {InstructorID, CourseID}, missing StudentID. But is CourseID (the right-hand side) prime? Yes — it's part of the candidate key {StudentID, CourseID}. So condition (b) of 3NF is satisfied, and this relation IS in 3NF.

Is this in BCNF? Check the same FD, InstructorID -> CourseID, against BCNF's rule: InstructorID must be a superkey. It is not (shown above). BCNF has no "prime attribute" escape clause, so this relation VIOLATES BCNF.

This is the concrete gap: a schema can be in 3NF but not BCNF exactly when a non-superkey determinant happens to determine a prime attribute.

The BCNF Fix

Decompose along the violating FD:

InstructorCourse(InstructorID, CourseID)   -- InstructorID is now a proper key
StudentInstructor(StudentID, InstructorID)  -- records which section a student attends

Now every determinant (InstructorID in the first, {StudentID, InstructorID} in the second) is a superkey of its own relation.

Edge Cases

• BCNF decomposition can occasionally not preserve all original functional dependencies without an extra join to re-check them (a known trade-off called "dependency preservation" loss) — this is a rare, advanced caveat, unlike simple 3NF decompositions which usually preserve dependencies cleanly.
• Most everyday schemas (like the chapter's main Student/Instructor/Course/Enrollment design) reach BCNF "for free" once they reach 3NF — the 3NF/BCNF gap specifically requires overlapping candidate keys, which is a comparatively rare situation.

Key Takeaways / Interview Angle

• Q: State the one-sentence difference between 3NF and BCNF. 3NF allows a non-superkey determinant if what it determines is a prime attribute; BCNF forbids this entirely — every determinant must be a superkey, no exceptions.
• Q: Give the concrete violating FD from this topic. InstructorID -> CourseID in SectionAssignment(StudentID, CourseID, InstructorID) — InstructorID is not a superkey, yet it determines CourseID.
• Q: Why doesn't the chapter's main 4-table schema need a further BCNF step? Because none of its relations have overlapping candidate keys — each already has every determinant as a superkey once 3NF is reached.