Normalization of Relations with Nulls in Candidate Keys: Traditional and Domain Key Normal Forms - Advanced Topics in Database Research - page 132

Figure 1: A state of the relation ASSIGNMENT

ID NAME TITLE COMPUTER_NO MODEL RAM

E1 A. Adams Manager null null null

E2 B. Brown V.P. null null null

E3 C. Carlos Manager C1 Model1 128

E4 J. Jones Sales Rep C2 Model1 64

E5 J. Jones Accountant C3 Model2 64

null null null C4 Model2 128

Since some employees may not have a computer, the corresponding tuples in AS-
SIGNMENT do not have any value for COMPUTER_NO, MODEL, and RAM. Similarly,
since some computers do not have employees assigned to them, the corresponding tuples
do not have any value for ID, NAME and TITLE. Here, null may represent value that does
not exist or value that is not applicable. For example, one employee may not be eligible for
a computer (not applicable), while another employee may be eligible, but no computer was
assigned (does not exist).
The above design is not a good one. The relation suffers from insertion and deletion
anomalies. If ID is selected as the primary key, then information on a computer cannot
be inserted if the computer is not assigned to an employee, as in the case of the computer
identifi ed by C4. By assumption, if an employee leaves the organization, the employee's
computer may not be assigned to anyone. In such cases, deleting the tuple for an employee
will result in losing the information on the corresponding computer. However, deleting
tuples of employees who do not have a computer does not result in losing information on
any computer. Similar problems exist if COMPUTER_NO or COMPUTER_NO+ID is
selected as the primary key. Thus, ASSIGNMENT in its current form suffers from insertion
and deletion anomalies. These anomalies could be removed by decomposing the relation
into two relations by taking projections:

1) EMPLOYEE (ID, NAME, TITLE, COMPUTER_NO),
2) COMPUTER (COMPUTER_NO, MODEL, RAM).
Or,
1) EMPLOYEE (ID, NAME, TITLE),
2) COMPUTER (COMPUTER_NO, MODEL, RAM, ID).

Though ASSIGNMENT has insertion and deletion anomalies that can be removed by
decomposition, applying the current popularly-used defi nitions of relation, determinant,
candidate key, and BCNF leads to the conclusion that the above relation is in BCNF (and
also in 4 th and 5 th normal forms), as explained in the next section.
Before analyzing ASSIGNMENT further, we discuss the relevance of relations such as
ASSIGNMENT that combine information on multiple entities, and the practical importance
of one-to-one relationships. Database design based on the popular top-down approach uses
three steps (Elmasri & Navathe, 2000): 1) identify the entities and their relationships, 2)
apply the mapping rules to create relations from entities, and 3) perform the normalization
procedure to validate the design. Ideally, the designer should identify the entities and their