Boyce-Codd Normal Form or BCNF is an extension to the third normal form, and is also known as 3.5 Normal Form.
For a table to satisfy the Boyce-Codd Normal Form, it should satisfy the following two conditions:
- It should be in the Third Normal Form.
- And, for any dependency A → B, A should be a super key.
The second point means, that for a dependency A → B, A cannot be a non-prime attribute
is a prime attribute
This will be explained using BCNF in terms of relations.
Consider the following relationship: R(A,B,C,D) and following dependencies:
A -> BCD
BC -> AD
D -> B
Above the relationship is already in 3rd normal form
where the keys are A and BC.
Hence, in the functional dependency, A -> BCD, is the super key. In the second relation BC->AD, BC is also a key.
However, in the relation, D -> B, D is not a key. Therefore, we can only break our relationship R into two relationships
A superkey is a set of fields that contain unique values. You can use a superkey to uniquely identify the records in a table and recall that a candidate key is a minimal superkey.
In other words, if you remove any of the fields from the candidate key
, it will not be a superkey anymore. A determinant is a field that at least partly determines the value in another field.
Note that the definition of 3NF deals with fields that are dependent on another field that is not part of the primary key. Now we are talking about fields that might be dependent on fields that are part of the primary key (or any candidate key).
A table is in BCNF if:
- It is in 3NF.
- Every determinant is a candidate key.