//
While Third Normal Form (3NF) eliminates most redundancies, it has a slight mathematical flaw if the table has multiple overlapping candidate keys. To fix this, Raymond Boyce and Edgar Codd introduced a stricter version of 3NF known as Boyce-Codd Normal Form (BCNF).
A relation schema $R$ is in BCNF with respect to a set $F$ of functional dependencies if, for all functional dependencies in $F$ of the form $\alpha \rightarrow \beta$ (where $\alpha \subseteq R$ and $\beta \subseteq R$), at least one of the following holds:
The Simple Rule: For every non-trivial functional dependency $X \rightarrow Y$, $X$ must be a superkey. There are absolutely no exceptions.
In 3NF, there is a third exception: If $\alpha$ is not a superkey, the dependency is still allowed if $\beta$ is a prime attribute (part of a candidate key). BCNF aggressively removes this exception. If $X$ determines $Y$, $X$ must be a unique key for the whole table.
When we normalize a table, we divide it (decompose it) into two or more smaller tables. A critical requirement of normalization is that this decomposition must be lossless.
A decomposition is lossless if we can rejoin the smaller tables (using a Natural Join) and get back exactly the original table—no missing rows, and no "spurious" (fake, extra) rows.
If a relation $R$ is decomposed into $R_1$ and $R_2$, the decomposition is lossless if and only if the common attributes of $R_1$ and $R_2$ form a superkey for at least one of the two tables.
Mathematically, $R_1 \cap R_2$ must be a superkey for $R_1$ OR $R_1 \cap R_2$ must be a superkey for $R_2$.
Employees (EmpID, Name, DeptID, DeptLocation) into Emp(EmpID, Name, DeptID) and Dept(DeptID, DeptLocation).DeptID.Dept table, DeptID is the primary key (a superkey).While we always want lossless decomposition, we also strive for Dependency Preservation.
A decomposition preserves dependencies if all the original functional dependencies can still be enforced by looking at the individual tables without having to join them back together.
The BCNF Trade-off: It is mathematically proven that we can always decompose a table into 3NF in a way that is both lossless and dependency-preserving. However, we cannot guarantee this for BCNF. Sometimes, achieving BCNF forces us to lose a dependency. This is why many DBAs stop at 3NF.