Database Reference
In-Depth Information
For these situations, the Boyce-Codd normal form (BCNF) provides the perfect
solution. As you shall soon see, the BCNF is really a refinement of 3NF. In fact, where the
above-mentioned conditions do not hold, BCNF reduces to 3NF.
4.8 The Boyce-Codd Normal Form
Simply, Boyce-Codd normal form (BCNF) requirement states:
A determinant is an attribute (or group of attributes) on which some other attribute(s)
is (are) fully functionally dependent. Examination of
R2
,
R3
,
R4
, and
R5
above will quickly
reveal that they are in BCNF (hence 3NF). We therefore need to find a different example
that illustrates the importance of BCNF.
Consider the situation where it is desirous to keep track of animals in various zoos,
and the assigned keepers for these animals. Let us tentatively construct the relation
R6
as
shown below:
R6
{Zoo, Animal, Keeper}
Assume further that that a keeper works at one and only one zoo. We can therefore
identify the following FDs:
•
[Zoo, Animal] → Keeper
•
Keeper → Zoo
Given the above, we conclude that [Zoo, Animal] is the primary key. Observe
that
R6
is in 3NF but not in BCNF, since
Keeper
is not a candidate key but is clearly a
determinant. Using Heath's theorem, we may decompose
R6
as follows:
R7
{Animal, Keeper} PK[Animal]
R8
{Keeper, Zoo} PK[Keeper]
As on previous occasions, let us examine the consequences of
our action:
1.
By achieving BCNF, we benefit from further reduction in data
duplication, and modification anomalies.
2.
A further advantage is that we can now store
dangling records
.
In our example, a keeper can be assigned to a zoo even before
he/she is assigned an animal.
3.
One possible drawback with BCNF is that more relations have
to be accessed (joined) in order to obtain useful information.
Again referring to the example,
R7
must be joined with
R8
in
order to derive Zoo-Animal pairs.
