Database Reference
In-Depth Information
computer is assigned to an employee, but an employee may not have a computer. Thus, ID
does not contain any nulls, but COMPUTER_NO may be null in certain tuples. For ASSIGN-
MENT_2, it is assumed that every computer is assigned to an employee, and every employee
has a computer. Hence, ID and COMUPTER_NO do not contain any nulls. In both relations,
if ID is selected as the primary key, then there is no insertion anomaly. The result of deleting
an employee's information also is the same in both relations. The corresponding computer
has to be re-assigned to another employee, since, by assumption, every computer is assigned
to an employee. If candidate keys are not allowed to have nulls, then COMPUTER_NO in
ASSIGNMENT_1 is not a candidate key, while it is a candidate key in ASSIGNMENT_2.
This means that ASSIGNMENT_1 is not in BCNF while ASSIGNMENT_2 is in BCNF,
though insertion and deletion are identical in both relations. Thus, requiring that candidate
keys should not have nulls, or that the left-hand side of the functional dependency should
not be null when the right hand side is not null, leads to inconsistent results in applying
BCNF. Hence, candidate keys may have nulls. This is in agreement with the popular view
in the literature that candidate keys can have nulls, as pointed out by Date (2000, p. 595):
“…alternate keys can apparently have nulls allowed”. This conclusion supports our earlier
determination that ID -> COMPUTR_NO, MODEL, RAM holds though ID may be null in
certain tuples where COMPUTR_NO, MODEL, and RAM are not null.
Incorporate Entity Integrity Rule into BCNF
A third and recommended option is to apply the requirement that at least one candidate
key of the relation should not have nulls, as part of checking whether a relation is in BCNF.
To help the designer do this, this requirement is incorporated into the defi nition of BCNF.
The modifi ed defi nition of BCNF is:
A relation is in BCNF if, and only if, 1) every determinant is a candidate key, and 2)
at least
one of the candidate keys does not have any nulls
.
The additional requirement that at least one of the candidate keys does not have any
nulls is an essential pre-requisite to satisfy the entity integrity rule. Incorporating this require-
ment into BCNF forces the designer to explicitly apply the essence of the entity integrity
rule without selecting a primary key.
Now we examine the effect of modifying the defi nition of BCNF on normalization
of different relations. Applying the modifi ed defi nition of BCNF leads to the conclusion
that ASSIGNMENT is not in BCNF, since both candidate keys, ID and COMPUTER_NO,
contain nulls. This conclusion is consistent with the fact that ASSIGNMENT suffers from
insertion/deletion anomalies that can be removed by decomposition. Under the new defi ni-
tion, both ASSIGNMENT_1 and ASSIGNMENT_2 are in BCNF since the candidate key
ID does not contain any nulls. This is consistent with the fact that both relations do not
suffer from insertion and deletion anomalies, as discussed earlier. The designs of ASSIGN-
MENT_1 and ASSIGNMENT_2, of course, may not be desirable. Combining two entities
into a single relation lacks intuitive appeal. Deleting an employee's information results in
the consistently cumbersome process of reassigning the computer to another employee.
These areas, however, are not meant to be covered by normalization.
Next, we examine whether the additional requirement incorporated into BCNF falsely
classifi es a relation as not in BCNF when it does not have any insertion, deletion, or update
