MENU


Boyce Codd Normal Form

Boyce-Codd Normal Form (BCNF), sometimes called 3.5NF, is a stricter version of Third Normal Form. A table is in BCNF when:

It is already in 3NF.
For every non-trivial functional dependency X → Y, X is a candidate key of the table – not just part of one, not just a column that happens to determine another.

In practice almost every table that satisfies 3NF also satisfies BCNF. The two diverge only in one specific situation: a table with two or more candidate keys that overlap (share at least one column), where a non-key part of one candidate key determines a non-key part of another. 3NF tolerates this because the determinant, though not a candidate key itself, is a prime attribute (part of some candidate key) – and 3NF only forbids a non-key column depending on another non-key column. BCNF forbids it regardless.

When 3NF Isn't Enough

The classic case: a table recording which teacher teaches which subject to which student, where each teacher teaches exactly one subject, but a subject can be taught by several teachers, and a student can study several subjects (with different teachers).

Worked Example

(student_id, subject) → teacherA student studies each subject with one specific teacher.
teacher → subjectBusiness rule: each teacher teaches only one subject.

This table has two overlapping candidate keys: (student_id, subject) and (student_id, teacher) – either one, together with student_id, pins down the whole row. Since subject is part of a candidate key, it is a prime attribute, so teacher → subject is not a transitive dependency by the 3NF definition and the table is technically in 3NF.

It still has a real redundancy problem: the teacher's subject is repeated on every row for every student that teacher has. If a teacher switches subjects, every one of their rows must be updated, and nothing stops two rows from recording the same teacher against two different subjects by mistake – the FD teacher → subject is a real business rule that this table cannot enforce or store without duplication, because teacher (the determinant) is not itself a candidate key. That is exactly what BCNF forbids.

The fix is to decompose the table so the offending determinant becomes a key in its own table: a teacher_subjects table keyed on teacher (satisfying teacher → subject as a proper key-based fact), and a student_teachers table recording which students study with which teachers. See the worked CREATE TABLE statements below.

BCNF decomposition occasionally loses the ability to enforce every original constraint with plain FOREIGN KEY/UNIQUE clauses alone (dependency-preservation is not guaranteed the way it is for 3NF decompositions); in practice this is rare enough, and the redundancy saved is significant enough, that most schemas apply BCNF wherever the overlapping-candidate-key situation actually shows up, and accept 3NF elsewhere. 

-- BEFORE: satisfies 3NF but violates BCNF.
-- Candidate keys: (student_id, subject) and (student_id, teacher).
-- teacher -> subject holds (each teacher teaches one subject) but teacher
-- alone is not a candidate key, so this functional dependency is a BCNF
-- violation even though it isn't a 3NF violation.
CREATE TABLE student_teacher_subject (
    student_id INT UNSIGNED NOT NULL,
    subject    VARCHAR(50) NOT NULL,
    teacher    VARCHAR(80) NOT NULL,
    PRIMARY KEY (student_id, subject),
    UNIQUE KEY uq_student_teacher (student_id, teacher)
) ENGINE=InnoDB;

-- Nothing in this schema stops the same teacher being recorded against
-- two different subjects by mistake:
INSERT INTO student_teacher_subject VALUES (1, 'Algebra', 'Ms. Rivera');
INSERT INTO student_teacher_subject VALUES (2, 'Biology', 'Ms. Rivera'); -- inconsistent, not prevented

-- AFTER: BCNF-compliant. Every determinant is now a candidate key.
CREATE TABLE teacher_subjects (
    teacher VARCHAR(80) NOT NULL PRIMARY KEY,
    subject VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE student_teachers (
    student_id INT UNSIGNED NOT NULL,
    teacher    VARCHAR(80)  NOT NULL,
    PRIMARY KEY (student_id, teacher),
    CONSTRAINT fk_student_teachers_teacher
        FOREIGN KEY (teacher) REFERENCES teacher_subjects (teacher)
) ENGINE=InnoDB;

-- Now the business rule is enforced structurally: a teacher can appear in
-- teacher_subjects with only one subject, full stop.
INSERT INTO teacher_subjects VALUES ('Ms. Rivera', 'Algebra');
-- INSERT INTO teacher_subjects VALUES ('Ms. Rivera', 'Biology');  -- rejected: duplicate PK

INSERT INTO student_teachers VALUES (1, 'Ms. Rivera');

-- Reconstructing the original flattened view is a JOIN:
SELECT st.student_id, ts.teacher, ts.subject
FROM student_teachers st
JOIN teacher_subjects ts ON ts.teacher = st.teacher;