Database Reference
In-Depth Information
anomalies. The additional requirement that at least one candidate key of a relation should not
have nulls affects the normalization of only those relations that have nulls in all candidate
keys. Such relations will have nulls in the primary key, resulting in problems in inserting
and possibly in deleting certain tuples. Thus the additional requirement affects only those
relations that have at least insertion problems.
It should be noted that current decomposition algorithms that split relations into BCNF
may not handle nulls adequately. Dealing with such cases is an area of further research.
Other Relations with Nulls in Candidate Keys
Relations that represent only one entity also may have nulls in all candidate keys and
suffer from insertion problems. These relations will be classifi ed as not in BCNF if the new
defi nition of BCNF is applied. Two examples illustrate the two cases: 1) A relation has
multiple candidate keys; 2) A relation has a composite candidate key.
The fi rst example involves a relation that represents a single entity, and has multiple
candidate keys. A relation, VISITOR, represents visitors to a country from a neighboring
country:
VISITOR(PASSPORT_NO, NATIONAL_ID, NAME, ADDRESS).
Each visitor is required to have a unique passport number or a unique national iden-
tifi cation number. Thus, a visitor may have a PASSPORT_NO, or a NATIONAL_ID, or
both. NAME, ADDRESS, or a combination of the two, is not unique. VISITOR qualifi es
as a relation, since no two tuples can be identical. However, in its current form, VISITOR
is not a good design. If PASSPORT_NO is selected as the primary key, then information
on visitors without PASSPORT_NO cannot be inserted. Selecting NATIONAL_ID or the
combination PASSPORT_NO + NATIONAL_ID as the primary key also has similar insertion
problems. However, unlike ASSIGNMENT, this insertion problem cannot be eliminated by
decomposition of VISITOR by taking projections. Hence, BCNF is not expected to iden-
tify this problem, but there is no harm if it does. Applying the current defi nition of BCNF,
VISITOR is in BCNF. Under the modifi ed defi nition of BCNF, VISITOR is not in BCNF.
Thus, in such relations, the modifi ed defi nition would result in identifying some insertion
anomalies that cannot be removed by decomposition. Once the insertion problem is identi-
fi ed, the problem may be fi xed by adding a new attribute VISITOR_ID, for example, as a
surrogate key. A second option that merits further investigation is preventing the creation
of relations like VISITOR by modifying the mapping rules.
The second example uses a relation that represents a single entity, and has a single
composite candidate key. The relation, COURSE, represents different courses offered by
an organization: COURSE (COURSE_NAME, DATE, INSTRUCTOR).
There are two types of courses: 1) one-day traditional classroom courses, and 2) Internet
courses. COURSE_NAME represents the unique name for a course, and DATE represents
offering date of the course. Internet courses are available all the time. Hence, the attribute,
DATE, is not applicable for Internet courses. By assumption, COURSE_NAME + DATE
is the only candidate key. VISITOR is not a good design. If COURSE_NAME + DATE
is selected as the primary key, then information on Internet courses, which do not have a
value for DATE, cannot be inserted. Again, this insertion problem cannot be removed by
decomposition of COURSE by taking projections. Under the modifi ed defi nition of BCNF,
COURSE is not in BCNF since it does not have a candidate key without nulls.
