Database Reference
In-Depth Information
THE SOLUTION
To help detect the violation of the requirement that at least one candidate key should not
have nulls, the database designer needs to consider this requirement explicitly in applying the
defi nition of BCNF. It is tempting to suggest that the entity integrity rule be incorporated into
the defi nition of either a relation or a candidate key. We fi rst consider these two cases.
Entity Integrity Rule, Relations, and Candidate Keys
One way to ensure that a relation that is in BCNF will have at least one candidate key
without nulls is to modify the properties of a relation by adding a new requirement that a
relation should have at least one candidate key that does not have nulls. Based on this require-
ment, ASSIGNMENT and similar “relations” would not qualify as relations, indicating that
they are not good designs. However, in many cases, only relations that are in 3NF or BCNF
will meet the new requirement for a relation. For example, ASSIGNMENT would have to
be decomposed to two normalized relations (EMPLOYEE, COMPUTER) before it meets
the new requirement. As a second example, consider a relation, CUST_ORDER:
CUST_ORDER (ORDER_ID, ORDER_DATE, CUST_ID, CUST_NAME, CUST_PHONE)
A customer may have zero, one, or many orders. An order belongs to exactly one cus-
tomer. Here, ORDER_ID is the only candidate key. Since a customer may not have an order,
the candidate key may have nulls in some tuples. Thus, CUST_ORDER is not a relation,
based on the new requirement that at least one candidate key of a relation must not have
nulls. If CUST_ORDER is not a relation, then the designer cannot apply the defi nition of
BCNF to normalize it. Obviously, decomposing CUST_ORDER into two normalized rela-
tions, ORDER_HEADER (ORDER_ID, ORDER_DATE, CUSTR_ID) and CUSTOMER
(CUSTR_ID, CUST_NAME, CUST_PHONE), would make both relations meet the new
requirement for a relation. But that would mean normalizing the database before applying
the normalization rules. Hence, incorporating the entity integrity rule into the defi nition of
a relation is not a satisfactory solution.
A second way to ensure that a relation that is in BCNF will have at least one candidate
key without nulls is to modify the defi nition of candidate key by adding the requirement that
no component of a candidate key should have nulls. To be consistent, this requirement also
should apply to determinants; that is, no component of a determinant should have nulls. This
would mean that in the functional dependency X->Y, required for X to be a determinant or
candidate key, no component of X can be null, except in the trivial case when Y is null. That
is, for every valid instance of X, the value of X uniquely determines the value of Y,
and for
every valid instance of Y, there is a corresponding value of X.
Under this requirement, ID
and COMPUTER_NO still are determinants. However, ID is not a candidate key. In some
tuples, ID may be null when {COMPUTER_NO, MODEL, RAM} is not null. Hence, under
the new defi nition of candidate key, ASSIGNMENT is not in BCNF, consistent with the
fact that it suffers from insertion/deletion anomalies. However, the additional requirement
for candidate keys results in inconsistencies in applying BCNF in certain cases as discussed
below.
Consider two relations, ASSIGNMENT_1, and ASSIGNMENT_2, both of which have
the same attributes as ASSIGNMENT, but differ in a basic assumption regarding the rela-
tionship between employees and computers. For ASSIGNMENT_1, it is assumed that every
