Why can decomposing a relation into Boyce-Codd Normal Form lose a functional dependency, and what do you do about it in a real schema?

## Two independent properties of a decomposition When you replace one table R with tables R1 and R2, you care about two different guarantees. **Lossless join (non-additive join).** Joining R1 and R2 on their shared columns must yield exactly the rows of R — no fewer, and crucially no spurious extras. The standard sufficient condition: the shared column set must be a superkey of at least one of the two parts. The BCNF algorithm satisfies this by construction, because it splits on a violating dependency X -> Y and makes X the key of the part holding Y. Losslessness is never the thing you sacrifice. **Dependency preservation.** Every functional dependency that held on R must be checkable by looking at a single resulting table, without a join. This matters because the database enforces dependencies with keys and unique constraints, and a constraint can only read one table. If a dependency's columns end up scattered across two tables, the engine has no cheap way to enforce it, and the rule quietly becomes advisory. 3NF can always be achieved both losslessly and with dependency preservation — that is a theorem, and it is the main reason 3NF survives as the practical default. BCNF can always be achieved losslessly, but there are relations for which **no** BCNF decomposition preserves all dependencies. It is an impossibility result, not a defect of a particular algorithm. ## The standard example `enrollment(student, course, teacher)` with `course -> teacher` and `(student, teacher) -> course`. The BCNF fix splits out `course_teacher(course PRIMARY KEY, teacher)` and leaves `enrollment(student, course)` keyed on both columns. `course -> teacher` is now enforced by a primary key — perfect. But `(student, teacher) -> course` mentions `student` (only in the second table) and `teacher` (only in the first). No key on either table forbids Ada from being enrolled in two courses that happen to share teacher Kim, which the original rule outlawed. The dependency has been lost. Note what is *not* lost: the data. You can still compute the join and detect the violation after the fact. What you lost is the engine's ability to reject the bad write at the moment it happens. ## What you actually do **Option 1 — stop at 3NF.** Keep the single table, accept that the course-teacher fact repeats per enrolled student, and enforce both dependencies with constraints the engine can express. You are trading storage redundancy and multi-row updates for guaranteed rule enforcement. When the redundant fact changes rarely (a teacher assignment) and the rule is a hard business invariant, this is often the right call. **Option 2 — decompose and enforce out of band.** Go to BCNF and reintroduce the lost rule with a mechanism outside the key system: a trigger that runs the check on insert and update, a periodic reconciliation job that reports violations, or a deliberately redundant column plus a unique index. For instance, carry `teacher` in `enrollment` as a maintained column kept in step with `course_teacher`, and put a unique index on `(student, teacher)`. That trades the redundancy you removed for a smaller, index-shaped one — sometimes a good deal, sometimes the original problem wearing a hat. **Option 3 — interrogate the dependency.** In practice the lost dependency is often over-specified. "A student may not take two courses from the same teacher" is a scheduling artefact, not a law. If the business will not defend it, drop it and the conflict evaporates. A senior answer includes this move, because arguing the requirement is cheaper than engineering around it. **Application-level enforcement is the weakest option.** A check in service code is only as good as the last code path that writes the table — backfills, admin scripts and concurrent transactions all route around it. If you choose it, say so explicitly and pair it with a reconciliation query, rather than presenting it as equivalent to a constraint. ## The concurrency footnote Even a trigger-based check is not automatically safe: two concurrent transactions can each read a state where the rule holds and each insert a row that jointly breaks it. Under snapshot isolation this write skew is not prevented by the trigger alone. Enforcing a cross-table dependency correctly needs either serializable isolation, an explicit lock on a common row, or — best — a real unique constraint on some single table. That is precisely why losing the dependency is a genuine cost and not bookkeeping.