Database Reference
In-Depth Information
With the above defi nition, it is easy to see that all three relations ASSIGNMENT,
VISITOR, and COURSE presented earlier do not meet the modifi ed requirement for DKNF.
This result is consistent with the fact that they are not good designs since they suffer from
at least insertion anomaly as defi ned by Codd (1972).
SUMMARY
This chapter has shown some of the problems in applying the normalization theory when
all the candidate keys of a relation have nulls, but in each tuple, at least one candidate key
has a unique value. Applying the current defi nitions of BCNF or DKNF to such relations may
not help the designer to detect insertion and/or deletion anomalies that are associated with
poor designs. A basic problem is that there is nothing in the defi nition of a relation, BCNF
or DKNF, that guarantees that a normalized relation satisfi es the entity integrity rule. Three
possible solutions to the problem were considered: 1) Modify the defi nition of candidate
key to include the requirement that a candidate key should not have nulls; 2) Incorporate
the essence of the entity integrity rule into the defi nition of a relation; 3) Incorporate the
essence of the entity integrity rule into the defi nitions of BCNF and DKNF. It is shown that
the fi rst two solutions have negative side effects. The third method provides a solution to the
problem without creating such side effects. In essence, the modifi ed defi nition guarantees
that a relation that is in BCNF or DKNF will have at least one candidate key that does not
have nulls. This, in turn, helps to eliminate the insertion/deletion anomalies caused by nulls
in the primary key.
REFERENCES
Codd, E.F. (1972). Further normalization of the database relational model.
Database sys-
tems, Courant Computer Science Symposia series, 6,
34-64. Englewood Cliffs, NJ:
Prentice Hall.
Codd, E.F. (1986). Missing information (applicable and inapplicable) in relational databases.
SIGMOD Record
,
SIGMOD Record 15
(4), 53-74.
Connolly, T., & Begg, C. (2002).
Database systems
. Reading, MA: Addison-Wesley.
Date, C.J. (1990). NOT is not “Not”! (Notes on three-valued logic and related matters.
In C.J. Date.
Relational database writings, 1985 - 1989
(427- 450). Reading, MA:
Addison-Wesley.
Date, C.J. (2000).
An introduction to database systems
. Reading, MA: Addison-Wesley.
Date, C.J., & Fagin, R. (1992). Simple conditions for guaranteeing higher normal forms
in relational database systems.
ACM Transactions on Database Systems
,
17
(3), 465-
476.
Dutka, A.F., & Hanson, H.H. (1989).
Fundamentals of data normalization
. Reading, MA:
Addison-Wesley.
Elmasri, R., & Navathe, S. (2000).
Fundamentals of database systems.
Reading, MA: Ad-
dison-Wesley.
Fagin, R. (1981). A normal form for relational databases that is based on domains and keys.
ACM Transactions on Database Systems
,
6
(3), 387-415.
Hoffer, J.A., Prescott, M.B., & McFadden, F.R. (2002).
Modern database management.
Reading, MA: Addison-Wesley.
