Definition
A relation schema is in Boyce-Codd normal form(BCNF) with respect to a set of functional dependencies if:
For all functional dependencies , where , at least one of the following holds:
- is trivial. (i.e., )
- is a superkey of . (i.e., )
Example
Consider , and the set of functional dependencies .
- This is not in BCNF because
- holds in , but
- is not a superkey.
Testing for BCNF
Testing a functional dependency
To check if a nontrivial functional dependency causes a violation of BCNF:
- Compute .
- Verify that . i.e., is a superkey of .
Testing a relation schema decomposition
Let be a relation schema, and be a decomposition of . Let be the set of functional dependencies.
- For every set of attributes , compute .
- Verify that either or .
- If the condition is violated by some , the dependency . Hence violates BCNF.
Decomposition into BCNF
Let be a schema that is not in BCNF, and let be the functional dependency that causes a violation in BCNF.
Then, the following decomposition of is in BCNF.
In most cases, .
Example
Consider a schema with the functional dependencies and .
Here, the dependency is causing a violation of BCNF conditions.
The decomposition
is in BCNF.
However, any of the decomposed relation does not include all the attributes used in the dependency ; it is not dependency preserving.