Database Reference
In-Depth Information
Thus, a somewhat harmless and potentially benefi cial side effect of the additional re-
quirement in the modifi ed BCNF is that it identifi es certain insertion anomalies that cannot
be removed by decomposition. The major benefi t of incorporating the additional requirement
is that in relations that represent two entities with a one-to-one relationship between them,
the modifi ed defi nition helps to identify insertion/deletion anomalies that can be eliminated
by decomposition, while the current defi nition may not help to identify them.
THIRD NORMAL FORM
A relation that is in BCNF also should be in third normal form (3NF). Using the current
defi nition of BCNF, ASSIGNMENT is in BCNF. Is it also in 3NF? One group of defi nitions
uses the term primary key. An example is:
A relation is in 3NF if it is in second normal and
no nonprime attribute is transitively dependent on the primary key
(Elmasri & Navathe,
2000). Applying this group of defi nitions leads to the conclusion that ASSIGNMENT is not
in 3NF, or at least it is not possible to determine whether it is in 3NF, since it does not have
a valid primary key. Thus, this defi nition of 3NF is able to identify the insertion/deletion
anomaly problems that are not detected by BCNF. Hence, these defi nitions of 3NF do not
require any change.
A more general defi nition that does not use the term primary key is: A relation is in 3NF
if whenever a nontrivial functional dependency X -> A holds, then either X is a superkey
of R, or A is a prime attribute (Elmasri & Navathe, 2000). An attribute is a prime attribute
if it is part of a candidate key. Thus, the only difference between 3NF and BCNF is that in
3NF, the right hand side of the functional dependency is allowed to be a prime attribute.
Since ASSIGNMENT is in BCNF, it is also in 3NF under the relaxed requirements. Hence,
this defi nition of 3NF needs to be modifi ed:
A relation is in 3NF if whenever a nontrivial functional dependency X -> A holds, then 1)
either X is a super key of R, or A is a prime attribute, and
2) at least one of the candidate
keys does not have any nulls.
DOMAIN KEY NORMAL FORM
An alternative or a supplement to using the traditional normal forms is the Domain-Key
Normal Form (DKNF) proposed by Fagin (1981) as the ideal or ultimate normal form. A
relation is in DKNF if every constraint (including dependencies) can be inferred by simply
knowing the attributes and their domains, and the set of keys. Thus, if a relation schema is
in DKNF, then the DBMS should be able to enforce all constraints of the relation schema
by enforcing the domain and key constraints. A relation schema is defi ned to be a set of at-
tributes, along with their constraints. DKNF has the conceptual superiority that it is based
on the primitive concepts of domains and keys, whereas traditional normal forms are based
on functional, multivalued, or join dependencies. However, DKNF is not popularly used by
practitioners due to practical limitations, including the lack of simple well-defi ned methods
to achieve DKNF. Hence, it is important that the traditional normal forms be able to cor-
rectly identify design problems, irrespective of whether DKNF can identify them. Next, we
examine whether DKNF identifi es the problems with the design of ASSIGNMENT.
