Boyce-Codd Normal Form (BCNF)

Boyce-Codd Normal Form (BCNF), sometimes called 3.5NF, is a stricter version of Third Normal Form. It closes a narrow gap that 3NF leaves open: tables that technically satisfy 3NF but still have a non-obvious redundancy caused by overlapping candidate keys. A table is in BCNF when, for every functional dependency X → Y in the table, X is a superkey — meaning every determinant (every column or set of columns that determines another column's value) is itself capable of uniquely identifying a row.

Why 3NF is not always enough

3NF only requires that non-key columns depend directly on the key, with no transitive dependency through another non-key column. It says nothing about dependencies where the determinant is part of a key but not a whole key by itself, in a table that has more than one candidate key overlapping on some columns. BCNF is the fix for that specific, fairly rare situation.

A conceptual example: 3NF but not BCNF

Picture a table that tracks which instructor teaches which subject in which student's schedule, with the rule that each instructor teaches exactly one subject, but a given subject can be taught by several instructors:

Satisfies 3NF, but still violates BCNF

SQL

CREATE TABLE student_subject_instructor (
  student_id INT,
  subject    VARCHAR(100),
  instructor VARCHAR(100),
  PRIMARY KEY (student_id, subject)
);

INSERT INTO student_subject_instructor (student_id, subject, instructor) VALUES
  (1, 'Mathematics', 'Dr. Patel'),
  (2, 'Mathematics', 'Dr. Patel'),
  (3, 'Physics',      'Dr. Osei'),
  (1, 'Physics',      'Dr. Osei');

Here the primary key is (student_id, subject), and instructor depends on the full key — no partial or transitive dependency in the 3NF sense, so the table is in 3NF. But there is a second functional dependency hiding in the business rule: instructor → subject, because each instructor only ever teaches one subject. instructor is not a superkey by itself (many rows can share the same instructor across different students), yet it determines subject. That violates BCNF, and the practical symptom is the same kind of redundancy seen in earlier normal forms: the fact "Dr. Patel teaches Mathematics" is repeated once per enrolled student, and if Dr. Patel were reassigned to teach Physics, every one of those rows would need to be updated in lockstep.

The BCNF fix

The fix follows the same pattern as every normal form so far: pull the dependency out into its own table, keyed by its actual determinant.

BCNF-compliant: the instructor-subject fact is stated once

SQL

CREATE TABLE instructors (
  instructor VARCHAR(100) PRIMARY KEY,
  subject    VARCHAR(100) NOT NULL
);

CREATE TABLE student_enrollments (
  student_id INT,
  instructor VARCHAR(100),
  PRIMARY KEY (student_id, instructor),
  FOREIGN KEY (instructor) REFERENCES instructors(instructor)
);

INSERT INTO instructors (instructor, subject) VALUES
  ('Dr. Patel', 'Mathematics'),
  ('Dr. Osei',  'Physics');

INSERT INTO student_enrollments (student_id, instructor) VALUES
  (1, 'Dr. Patel'),
  (2, 'Dr. Patel'),
  (3, 'Dr. Osei'),
  (1, 'Dr. Osei');

Form	Requirement	What it catches
1NF	Atomic columns, no repeating groups	Lists or delimited values in one column
2NF	1NF + no partial dependency on a composite key	Non-key columns depending on only part of the key
3NF	2NF + no transitive dependency	Non-key columns depending on other non-key columns
BCNF	3NF + every determinant is a superkey	Dependencies from a non-superkey column, usually with overlapping candidate keys

BCNF is relatively rare in practice

Situations that satisfy 3NF but not BCNF require overlapping composite candidate keys with a specific pattern of functional dependencies, which is uncommon in typical application schemas. BCNF shows up far more often in database theory courses and interview questions than in day-to-day schema design — most real systems that reach 3NF are already in BCNF as a side effect of how naturally they were modeled. It is still worth recognizing the pattern, both for interviews and for the rare production case where it genuinely applies.

BCNF requires that every functional dependency in a table has a superkey on the left-hand side.
A table can satisfy 3NF and still violate BCNF when candidate keys overlap in a specific way.
The fix is the same as other normal forms: move the offending dependency into a table keyed by its true determinant.
BCNF violations are uncommon in everyday schema design — treat this mainly as a theory and interview-prep topic.