Database Reference
In-Depth Information
anomalies, especially anomalies that require you to insert, modify, or delete different numbers
of rows to maintain integrity, check for multivalued dependencies.
By The WAy
You will sometimes hear people use the term
normalize
in phrases like,
“that table has been normalized” or “check to see if those tables are nor-
malized.” Unfortunately, not everyone means the same thing with these words. Some
people do not know about BCNF, and they will use it to mean tables in 3NF, which is a
lesser form of normalization, one that allows for anomalies from functional dependen-
cies that BCNF does not allow. Others use it to mean tables that are both BCNF and
4NF. Others may mean something else. The best choice is to use the term
normalize
to
mean tables that are in both BCNF and 4NF.
Fifth Normal Form
There is a fifth normal form (5NF), also known as
Project-Join Normal Form (PJ/NF)
, which
involves an anomaly where a table can be split apart but not correctly joined back together.
However, the conditions under which this happens are complex, and generally if a relation is in
4NF it is in 5NF. We will not deal with 5NF in this topic. For more information about 5NF, start
with the works cited earlier in this chapter and the Wikipedia article at
http://en.wikipedia.org/
Domain/Key Normal Form
As discussed earlier in this chapter, in 1982 R. Fagin published a paper that defined domain/
key normal form (DK/NF). Fagin asked, “What conditions need to exist for a relation to have
no anomalies?” He showed that a relation in DK/NF has no modification anomalies and, fur-
ther, that a relation that has no modification anomalies is in DK/NF.
But what does this mean? Basically, DK/NF requires that all the constraints on the data
values be logical implications of the definitions of domains and keys. To the level of detail in
this text, and to the level of detail experienced by 99 percent of all database practitioners, this
can be restated as follows: Every determinant of a functional dependency must be a candidate
key. This, of course, is simply our definition of BCNF, and, for practical purposes, relations in
BCNF are in DK/NF as well.
Databases arise from three sources: from existing data,
from new systems development, and from the redesign
of existing databases. This chapter and the next are con-
cerned with databases that arise from existing data. Even
though a table is a simple concept, certain tables can lead
to surprisingly difficult processing problems. This chap-
ter uses the concept of normalization to understand and
possibly solve those problems. Figure 3-3 lists terms you
should be familiar with.
A relation is a special case of a table; all relations are
tables, but not all tables are relations. Relations are tables
that have the properties listed in Figure 3-4. Three sets of
terms are used to describe relation structure: (relation, attri-
bute, tuple); (table, column, row); and ( file, field, and record).
Sometimes these terms are mixed and matched. In practice,
the terms
table
and
relation
are commonly used synony-
mously, and we will do so for the balance of this text.
In a functional dependency, the value of one attribute, or
attributes, determines the value of another. In the functional
dependency A
S
B, attribute A is called the determinant.
Some functional dependencies arise from equations, but
many others do not. The purpose of a database is, in fact, to
store instances of functional dependencies that do not arise
from equations.
Determinants that have more than one attribute are
called composite determinants. If A
S
(B, C), then A
S
B
and A
S
C. However, if (A, B)
S
C, then, in general, neither
A
S
C nor B
S
C.
Search WWH ::
Custom Search