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:
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”).
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 CTThe 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 nth 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?[31]
Aside: It might be argued, at least in the SNC and CT example, that the decomposition also means there’s a constraint that now doesn’t have to be maintained: viz., the FD {CITY} → {STATUS}. But this argument isn’t entirely valid—all the decomposition does, in this respect, is move that constraint from one relvar to another (actually from relvar S to relvar CT, where it’s maintained as a side effect of maintaining the constraint that {CITY} is a key). End of aside.
Now, in the simple example under discussion, the benefits of doing the decomposition almost certainly outweigh the benefits of not doing so. But such is not always the case; indeed, the question of whether or not to decompose, in more complicated situations, can be a fairly vexing one. In what follows, in order to avoid a lot of repetitive text, I’ll tend to assume we do always want to do the decomposition—but please don’t forget there can sometimes be persuasive arguments for not doing so, especially in examples more complex than the one at hand, such as are discussed in Part III of this book.
[31] 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.