Boyce-Codd Normal Form

Learning outcomes

Boyce-Codd Normal Form (BCNF) is a stricter refinement of Third Normal Form. 3NF allows a narrow class of dependencies where the dependent attribute is prime. BCNF removes that exception: every non-trivial determinant must be a superkey.

The BCNF rule

For every non-trivial functional dependency:

\[ X \rightarrow Y \]

BCNF requires X to be a superkey.

Compare 3NF and BCNF

3NF permits \(X \rightarrow A\) when either:

1. X is a superkey; or
2. A is a prime attribute.

BCNF permits only the first condition.

A classic 3NF-but-not-BCNF pattern

Suppose:

Teaching(  student,  course,  instructor)

Business rules:

• Each instructor teaches exactly one course.
• For a given student and course, there is one instructor.

Dependencies:

\[ (student,course) \rightarrow instructor \]

\[ instructor \rightarrow course \]

Candidate keys

One candidate key is:

\[ (student,course) \]

Another is:

\[ (student,instructor) \]

Therefore all three attributes are prime.

Why 3NF permits it

The dependency:

\[ instructor \rightarrow course \]

has a determinant that is not a superkey, but course is prime. Therefore the relation can satisfy 3NF.

Why BCNF rejects it

Instructor is not a superkey, so:

\[ instructor \rightarrow course \]

violates BCNF.

BCNF decomposition

Decompose around the violating dependency:

InstructorCourse(  instructor,  course)StudentInstructor(  student,  instructor)

The decomposition is lossless because instructor is the common attribute and determines all attributes of InstructorCourse.

Dependency preservation problem

The original dependency:

\[ (student,course) \rightarrow instructor \]

is no longer contained entirely within either decomposed relation. Enforcing it may require a join.

BCNF versus dependency preservation

This is one reason designers sometimes choose 3NF rather than BCNF: 3NF synthesis can preserve dependencies while removing most harmful redundancy.

WorkshopHub-style example

Suppose a specialized training relation records:

TechnicianCertification(  technician_id,  certification_code,  certifying_body)

and the business says each certification code is issued by exactly one body:

\[ certification\_code \rightarrow certifying\_body \]

If the row key is (technician_id, certification_code), this determinant is not the whole key. The certification metadata belongs in a Certification relation.

BCNF decomposition procedure

1. Find a dependency \(X \rightarrow Y\) where X is not a superkey.
2. Create one relation containing \(X \cup Y\).
3. Create another relation containing the remaining attributes plus X.
4. Repeat until every relation satisfies BCNF.

Losslessness of the standard decomposition

The standard BCNF decomposition around \(X \rightarrow Y\) is lossless because X becomes the common determinant of the decomposed relation containing Y.

Do not apply BCNF mechanically

BCNF is powerful, but the final design must still consider:

• dependency preservation;
• constraint enforceability;
• transaction boundaries;
• query complexity;
• business semantics.

Candidate keys still matter

BCNF reasoning depends on knowing all candidate keys. A determinant may look non-key only because an alternate candidate key was overlooked.

Practice: 3NF or BCNF?

Schedule(student, course, room)

(student, course) -> roomroom -> course

Summary and next lesson

BCNF strengthens 3NF by requiring every non-trivial determinant to be a superkey. It removes subtle redundancy that 3NF can permit, but BCNF decomposition may lose dependency preservation. The next lesson moves beyond ordinary functional dependencies to multivalued dependencies and Fourth Normal Form.

References

• Raymond F. Boyce and E. F. Codd, foundational work on normalization.
• C. J. Date, Database Design and Relational Theory.
• Jeffrey Ullman and Jennifer Widom, A First Course in Database Systems.