What does Boyce-Codd Normal Form (BCNF) require of a table's functional dependencies, and what is a determinant?

## The vocabulary the rule is built from **Functional dependency (FD).** Written X -> Y. It is a constraint, not an observation about today's rows: in every legal state of the table, any two rows that agree on all columns of X must also agree on all columns of Y. `employee_id -> email` says an employee has exactly one email. FDs come from business rules; sample data can disprove an FD but never establish one. **Determinant.** The left-hand side X. Read X -> Y as "X determines Y", so X is the determining column set. **Superkey.** A column set that functionally determines every column of the table — i.e. no two distinct rows may share its value. A **candidate key** is a minimal superkey (drop any column and it stops being one). A **prime attribute** is a column that belongs to at least one candidate key. **Trivial FD.** One where the right side is already inside the left side, such as (a, b) -> a. These hold automatically and are exempt from every normal form. ## The rule itself A relation is in BCNF if and only if, for every non-trivial FD X -> A that holds on it, X is a superkey. The slogan is *every determinant is a superkey*. There is no second clause, no exception for special columns — that austerity is exactly what distinguishes BCNF from Third Normal Form. ## Why non-superkey determinants create redundancy Suppose the table stores employees and their departments, with the FD `dept_id -> dept_location`. The key is `employee_id`, so `dept_id` is not a superkey: it repeats once per employee in the department. Because the FD forces every row with that `dept_id` to carry the same `dept_location`, the single fact "department 7 is in Berlin" is physically stored once per employee. That duplication is the root of the three classic anomalies. **Update anomaly:** relocating department 7 requires touching every one of its employee rows, and a partial update leaves two contradictory locations. **Insertion anomaly:** you cannot record a new department's location until someone is hired into it, because there is no row to put it in. **Deletion anomaly:** removing the last employee of a department erases the department's location entirely. BCNF removes these by construction: if only superkeys determine things, then every determinant value appears at most once in the table, so no determined fact can be stored twice. ## Checking a table List the FDs the business rules imply. For each non-trivial one, compute the **attribute closure** of its left side — start with X, and repeatedly add the right side of any FD whose left side you already have. If the closure contains every column, X is a superkey and that FD is fine. If any FD's left side closes to less than the full column list, the table violates BCNF. Two shortcuts are worth memorising. Any table with only two columns is automatically in BCNF. And a table with exactly one candidate key is in BCNF precisely when it is in 3NF — the two forms can only diverge when candidate keys overlap. ## Decomposing a violation Given a violating FD X -> Y, split R into R1 = X together with the closure of X, and R2 = X together with the columns not in that closure. X is a key of R1, and X is the shared column set, so joining R1 and R2 on X reconstructs R exactly — the decomposition is **lossless**. Repeat on any sub-table that still violates. The algorithm always terminates in BCNF, and always losslessly; the price, discussed under dependency preservation, is that some original FD may no longer be checkable inside a single table. ## In practice Most OLTP schemas designed the ordinary way — one entity per table, a surrogate primary key, real facts hung off foreign keys — land in BCNF without anyone invoking the theory. BCNF earns its keep as a diagnostic: when you notice the same value being typed into many rows, name the FD, check whether its determinant is a superkey, and if it is not, you have found a table that wants splitting.