Database develop. life cycle - BCNF (Boyce–Codd Normal Form)

What is BCNF?

  • BCNF is an advanced version of 3rd Normal Form (3NF) in database normalization.

  • A relation is in BCNF if:

    • It is in 3NF, and

    • For every functional dependency (X → Y), the determinant (X) is a candidate key.

In other words, no non-prime attribute should depend on anything other than a super key.

Why BCNF?

  • Even in 3NF, sometimes anomalies (redundancy, update issues) still exist if a dependency’s determinant is not a candidate key.

  • BCNF fixes this stricter condition.

Example (Not in BCNF)

Consider a relation:

R(StudentID, Course, Instructor)

Functional dependencies (FDs):

  1. StudentID, Course → Instructor (because a student in a course has a specific instructor)

  2. Instructor → Course (each instructor teaches only one course)

Candidate key: (StudentID, Course)

Check dependencies:

  • For FD1: (StudentID, Course) is a candidate key → OK.

  • For FD2: Instructor → Course

    • Determinant = Instructor.

    • But Instructor is not a candidate key.

    • This violates BCNF.

So relation is not in BCNF.

Decomposition to BCNF

We decompose into:

  1. R1(Instructor, Course)

    • Here, Instructor → Course holds.

    • Instructor is a key in this table → satisfies BCNF.

  2. R2(StudentID, Instructor)

    • Here, candidate key = (StudentID, Instructor).

    • All FDs satisfied → BCNF.

Now, both relations are in BCNF.

Key Idea

  • 3NF allows: a non-prime attribute to depend on another non-prime, if it’s part of a transitive dependency.

  • BCNF does not allow this. Every determinant must be a candidate key.