Boyce-Codd Normal Form (BCNF)

The Boyce-Codd Normal Form (BCNF) is a stricter version of the Third Normal Form (3NF). It closes a loophole that 3NF leaves open: a table can satisfy 3NF and still carry redundancy when a non-key attribute determines part of a key. BCNF removes that loophole with a single, sharp rule.

The BCNF rule. A relation is in BCNF if, for every non-trivial functional dependency X → Y that holds on it, X is a superkey. Equivalently: every determinant must be a superkey. There are no exceptions for prime attributes, which is exactly where 3NF is more lenient.

Because the rule quantifies over all functional dependencies, the honest way to verify it is to (1) compute the attribute closures, (2) find the candidate keys, and (3) check each dependency's left-hand side against those keys. We will do exactly that below, rather than eyeballing the table.

The worked example: Student_Course_Instructor

Consider a table recording which students take which courses and who teaches each course. The business rules are:

• A Course_ID is taught by exactly one instructor.
• A Student_ID may enroll in many courses, and a course may have many students.

The functional dependencies that hold

1. {Student_ID, Course_ID} → Instructor — a (student, course) pair determines the instructor (trivially, since the course alone already does).
2. Course_ID → Instructor — the stated rule that one course has one instructor.

Finding the candidate key by closure

Notice that Instructor never appears on the left of any dependency, and nothing determines Student_ID. So Student_ID must be in every candidate key, and to reach Instructor we still need Course_ID. Compute the closure of the pair:

• {Student_ID, Course_ID}+ = {Student_ID, Course_ID, Instructor} — all attributes, so it is a superkey.
• {Student_ID}+ = {Student_ID} — not a superkey.
• {Course_ID}+ = {Course_ID, Instructor} — not a superkey (it misses Student_ID).

No proper subset of {Student_ID, Course_ID} is a superkey, so {Student_ID, Course_ID} is the sole candidate key. The prime attributes are Student_ID and Course_ID; Instructor is non-prime.

What normal form is this table actually in?

Now we check the forms in order, because the diagnosis hinges on getting this exactly right.

1NF

All cells hold single atomic values, so the table is in 1NF.

2NF — this is where it fails

2NF forbids any partial dependency: a non-prime attribute must not depend on a proper subset of a candidate key. Examine Course_ID → Instructor:

• Course_ID is a proper subset of the candidate key {Student_ID, Course_ID}.
• Instructor is non-prime.

So Course_ID → Instructor is a textbook partial dependency: a non-prime attribute determined by part of the key. A partial dependency violates 2NF. Therefore the table is in 1NF only — it is not even in 2NF, let alone 3NF or BCNF.

It is worth being precise about why this matters. A partial dependency is a stronger defect than a transitive dependency (the kind 3NF targets). If a table has a partial dependency it fails 2NF, and any table that fails 2NF automatically fails 3NF and BCNF as well, since each higher form presupposes the one below it. Saying such a table is “in 2NF but not 3NF” would be wrong: the partial dependency is precisely what 2NF exists to forbid.

Honest caveat about this example. This single table is convenient for showing a partial dependency, but it does not produce the situation BCNF is famous for — a relation that genuinely satisfies 3NF yet still violates BCNF. Because it already fails 2NF, it cannot illustrate a clean “3NF yes / BCNF no” case. The classic BCNF-specific example needs overlapping candidate keys; see the next section for one. The decomposition below still lands us in BCNF, but the violation it removes is a 2NF violation, not a 3NF-only one.

Decomposing to BCNF

The fix is the standard lossless decomposition: split off the violating dependency Course_ID → Instructor into its own table so its left-hand side becomes a key, and keep the rest in a second table. The same two tables that resolve the 2NF violation are already in BCNF.

Table 1 — Course_Instructor

Hold the Course_ID → Instructor dependency on its own, with Course_ID as the primary key.

Table 2 — Student_Course

Record enrollments, with the composite primary key (Student_ID, Course_ID).

Verifying both tables are in BCNF

• Course_Instructor: the only non-trivial FD is Course_ID → Instructor, and {Course_ID}+ is all attributes, so Course_ID is a superkey. Every determinant is a superkey → BCNF.
• Student_Course: no non-trivial FD holds among its attributes except those with the full key on the left; {Student_ID, Course_ID} is the only candidate key and is trivially a superkey → BCNF.

The decomposition is also lossless: joining the two tables on Course_ID reconstructs the original rows exactly, because the shared attribute Course_ID is a key of Course_Instructor.

The case BCNF was really built for: overlapping candidate keys

To see a relation that is genuinely in 3NF yet violates BCNF, you need a determinant that is a prime attribute but not a superkey — which can only happen with overlapping candidate keys. Classic example, a relation {Student, Subject, Teacher} with the rules:

• Each teacher teaches exactly one subject: Teacher → Subject.
• For each subject, a student is taught by one teacher: {Student, Subject} → Teacher.

Here there are two candidate keys: {Student, Subject} and {Student, Teacher}. Every attribute is prime, so there is no non-prime attribute to be partially or transitively dependent — the relation satisfies 3NF (and even 2NF). Yet Teacher → Subject has a determinant, Teacher, that is not a superkey. That is a BCNF violation with no 3NF violation. This is the situation the convenient single-table example above cannot reproduce, which is why we flagged it.

Why BCNF matters

• Removes the last update anomalies tied to functional dependencies: with every determinant a superkey, a fact is stored in exactly one place.
• Stronger guarantee than 3NF, at the occasional cost of dependency preservation — some BCNF decompositions cannot preserve every FD, which is the practical reason 3NF is sometimes chosen instead.
• Forces precise reasoning: you cannot certify BCNF without enumerating candidate keys, which is good discipline for any schema.

The next lesson covers Higher Normal Forms (4NF and 5NF), which move beyond functional dependencies to multi-valued and join dependencies.

Source

Adapted and corrected from the Knowledge Guide Normalization track, lesson 005 — Boyce-Codd Normal Form (BCNF) (site/databases/normalization/005-boyce-codd-normal-form-bcnf.html). Normal-form definitions and the closure-based verification method follow the standard treatment in Silberschatz, Korth & Sudarshan, Database System Concepts, and Elmasri & Navathe, Fundamentals of Database Systems.