BCNF and Functional Dependencies

Concept

A functional dependency written X -> Y means: if you know X, then Y is determined. For any two rows with the same X, Y must be the same. X is called the determinant.

-- In the sample schema
employees.id      -> first_name, last_name, salary, dept_id
departments.id    -> name, location
(employee_id, project_id) -> hours

Normalisation is entirely a question of which determinants are allowed to exist. Every normal form is a rule about the left hand side of the arrow.

Boyce-Codd normal form

For every non trivial functional dependency X -> Y, X must be a superkey.

3NF has an escape clause: it allows X -> Y when Y is part of some candidate key, even if X is not a superkey. BCNF removes that exception. In practice, a table in 3NF is almost always in BCNF - the difference only appears when a table has several overlapping candidate keys.

Example

-- A course session is taught in one room; each room hosts one course.
CREATE TABLE schedule (
    course  VARCHAR(40),
    slot    VARCHAR(20),
    room    VARCHAR(20),
    PRIMARY KEY (course, slot)
);

-- Functional dependencies:
--   (course, slot) -> room     the key determines the room
--   room           -> course   a room is dedicated to one course

Candidate keys are (course, slot) and (room, slot) - they overlap on slot. The dependency room -> course has a determinant, room, that is not a superkey: knowing the room does not identify the row, because the same room appears in many slots.

The table is in 3NF - course is part of a candidate key, so the 3NF exception applies - but it is not in BCNF. And the anomaly is real: the fact room R101 hosts the maths course is repeated in every slot, so one row can be updated and the others left behind.

-- BCNF: split so every determinant is a key
CREATE TABLE room_course (
    room   VARCHAR(20) PRIMARY KEY,
    course VARCHAR(40) NOT NULL
);

CREATE TABLE room_slot (
    room VARCHAR(20),
    slot VARCHAR(20),
    PRIMARY KEY (room, slot),
    CONSTRAINT fk_rs_room FOREIGN KEY (room) REFERENCES room_course(room)
);

Explanation

The decomposition is not free. In the original table the constraint a course occupies one room per slot was enforced by the primary key. After the split, that rule spans two tables and a foreign key alone cannot express it. This is the known trade off: BCNF decomposition is always lossless, but it is not always dependency preserving. 3NF decomposition is always both.

That is why 3NF, not BCNF, is the practical target for most transactional systems - and why the exception is worth understanding rather than blindly maximising the normal form.

Higher normal forms, briefly

Important rules

• X -> Y is trivial when Y is a subset of X; trivial dependencies never violate anything.
• A superkey is any column set that uniquely identifies a row; a candidate key is a minimal superkey.
• BCNF is strictly stronger than 3NF. Every BCNF table is in 3NF; the reverse is not guaranteed.
• A table with no composite candidate keys and one candidate key is in BCNF as soon as it is in 3NF.
• Decomposition must be lossless: rejoining the pieces must give back exactly the original rows, no more.

Common mistakes

• Deriving dependencies from today's sample data rather than from the business rules. A dependency holds only if it must always hold.
• Chasing BCNF on a table with one candidate key, where it can add nothing.
• Decomposing to BCNF and silently losing a constraint the original key enforced.
• Confusing "the same value appears twice" with a functional dependency.

Best practices

• Write the functional dependencies down before designing tables; they are the design.
• Target 3NF, then check for overlapping candidate keys - that is the only place BCNF differs.
• If a BCNF split loses a constraint, either keep 3NF or enforce the rule with a trigger or application logic, and document the decision.
• Re examine the dependencies whenever a business rule changes; the schema follows from them.

Practice

1. List the functional dependencies of order_items(order_id, product_id, quantity, unit_price).
2. Show that a table keyed on (student, subject) with a dependency teacher -> subject is in 3NF but not BCNF.
3. Explain the difference between a superkey and a candidate key in one sentence.