Databases Reference
In-Depth Information
that reason, I'm not even going to try to define it here; I'm just going to assume until further notice that we can at
least recognize it when we see it (though even that's a pretty big assumption, actually). Chapter 15 examines the
concept in depth.
NORMALIZATION AND CONSTRAINTS
There's another issue that arises in connection with normalization, one that's often overlooked. Again consider the
example of decomposing relvar S into its projections SNC on {SNO,SNAME,CITY} and CT on {CITY,STATUS}.
Then there are three cases to consider:
1.
Suppose the original design, consisting of just relvar S, was at least logically correct (i.e., it merely suffered
from redundancy). As I pointed out in the section “Normalization Serves Two Purposes,” then, there's a
certain constraint (an “equality dependency”) that holds between the two projections:
CONSTRAINT ... SNC { CITY } = CT { CITY } ;
(“every city in SNC appears in CT and vice versa”).
2.
Alternatively, suppose as we did earlier that it's possible for CT to contain a tuple such as (Rome,10) that has
no counterpart in SNC. Moreover, suppose it's not possible for the converse to be true—SNC can never
contain a tuple that has no counterpart in CT. In that case, a
foreign key constraint
holds between those two
projections (from SNC to CT):
FOREIGN KEY { CITY } REFERENCES CT
3.
The third possibility (perhaps less likely than the first two) is that CT and SNC might both be allowed to
contain tuples with no counterpart in the other. For example, it might be the case that—let's say—supplier
S6, with name Lopez, is located in Madrid but Madrid has no status. In this case a perfectly reasonable
design would involve the appearance of the tuple (S6,Lopez,Madrid) in SNC without the appearance of a
tuple for Madrid in CT; clearly, therefore, no constraint involving cities holds between the two relvars at all
(at least, let's agree not for the sake of the example).
Now, simplifying somewhat, I've said that a relvar
R
in
n
th normal form can always be nonloss decomposed
into projections in (
n
+1)st normal form. As the foregoing discussion indicates, however,
such decomposition
usually means there's at least one new constraint that now needs to be maintained
. What makes matters worse is
that the constraint in question is a
multirelvar
constraint (i.e., it spans two relvars, or possibly more than two). So
there's a tradeoff: Do we want the benefits of decomposition, or do we want to avoid that multirelvar constraint?
11
11
Of course, maintaining that constraint, if it has to be done, should be done by the system and not the user─but the constraint will at least have
to be defined, and users will have to be aware of it.
