Databases Reference
In-Depth Information
Is ASSIGNMENT in DKNF? It is assumed that the relation schema of ASSIGNMENT
has only simple constraints: Attribute RAM must be an integer, and all other attributes are
character strings with a specifi ed limit on length. These constraints can be enforced by en-
forcing the domain constraints that can be imposed by the DBMS on individual attributes.
The schema of ASSIGNMENT also includes the constraints represented by the two
functional dependencies:
ID -> {NAME, TITLE, COMPUTER_NO, MODEL, RAM} and
COMPUTER_NO -> {ID, NAME, TITLE, MODEL, RAM}.
These constraints can be enforced by the DBMS by enforcing the key constraints on
ID and COMPUTER_NO. A key is defi ned as an attribute such that no two tuples have the
same value for the attribute. Thus, if there are no other constraints, all constraints can be
enforced by domains and keys, implying that ASSIGNMENT is in DKNF.
An additional real-world constraint that may not be evident could be that, for each
tuple, either ID or COMPTER_NO must not be null. Since each employee has an ID and
each computer has a COMPUTER_NO, it would be unrealistic to have a tuple that has nulls
in ID and COMPUTER_NO. Furthermore, if ID and COMPUTER_NO can be null in the
same tuple, then there could be two or more such tuples with identical values for the rest
of the fi elds which are not required to be unique. Under such conditions, it is not clear that
ASSIGNMENT meets the requirement for a relation that there are no duplicate tuples, since
comparison of two nulls evaluate to the “unknown” truth value (Date, 2000). The constraint
that either ID or COMPUTER_NO must not be null cannot be enforced by specifying the
domain for individual attributes, or, by enforcing key constraints on ID, COMPUTER_NO,
or ID+COMPUTER_NO, implying that ASSIGNMENT is not in DKNF.
The reason why ASSIGNMENT appears to be not in BKNF is the existence of a
single constraint involving two attributes (ID and COMPUTER_NO cannot be null in the
same tuple). It is not the existence of any functional dependency that cannot be implied by
a key. The constraint involving ID and COMPUTER_NO may not be readily evident to the
designer, making it diffi cult to identify the design problem in ASSIGNMENT by applying
DKNF. If the DBMS can enforce the constraint that either ID or COMPUTER_NO must not
be null in each tuple, then ASSIGNMENT would be in a weaker normal form that is based
on the concept of DKNF, though ASSIGNMENT is not a good design.
Applying DKNF to the relation COURSE (COURSE_NAME, DATE, INSTRUCTOR)
presented earlier yields the result that COURSE is in DKNF, though COURSE has insertion
problems. Here, the only constraint is the functional dependency, COURSE_NAMEA+DATE
-> INSTRUCTOR. This constraint can be enforced by specifying COURSE_NAMEA+DATE as
a key.
In order to make it easy to identify design problems in relations like ASSIGNEMNT
and COURSE it would be desirable to incorporate into the defi nition of DKNF the principle
implied by the entity integrity rule that at least one candidate key must not have nulls:
A relation is in DKNF if 1) every constraint (including dependencies) can be inferred by
simply knowing the attributes and their domains and the set of keys,
and 2) at least one of
the keys does not have any nulls
.
