Now let’s return to our motivating example, in which relvar S was decomposed “vertically” into its projections SNC and STC on {SNO,SNAME,CITY} and {SNO,STATUS,CITY}, respectively. (The example of light vs. heavy parts involved horizontal decomposition, of course.) Observe now that although SNC and STC are certainly of the same degree, there’s no way any given tuple can appear in both: Tuples in SNC have an SNAME attribute, while tuples in STC have a STATUS attribute instead. What’s more, there’s no way we can simply rename (say) the SNAME attribute in SNC to STATUS and thereby produce a relvar with the same heading as STC, because SNAME in SNC is of type CHAR and STATUS in STC is of type INTEGER. (Renaming attributes changes names, not types.) It follows that our second attempt at defining the orthogonality principle is still inadequate; in the case at hand, in fact, it simply doesn’t apply.
Recall now what the problem was with the foregoing design: The tuple (s,c) appears in the projection of SNC on SNO and CITY if and only if that very same tuple (s,c) appears in the projection of STC on SNO and CITY. That is, the following EQD holds:
CONSTRAINT ... SNC { SNO , CITY } = STC { SNO , CITY } ;Let’s agree to ignore the question of attribute renaming for the moment, since it isn’t relevant to this example. Then the crucial point is that this EQD holds, not between distinct database relvars as such, but rather between distinct projections of the same database relvar: to be specific, projections arising from “vertical” decomposition of that database relvar. But such doesn’t have to be the case, of course—I mean, SNC and STC might have been defined independently, as two completely distinct relvars, without there ever having existed (in the designer’s mind, so to speak) a relvar equal to their join. They might even be, not relvars in their own right, but projections of two such distinct relvars. All of which leads to a third attempt at defining the orthogonality principle:
Definition (third attempt): Let relvars R1 and R2 be distinct, and let the JD
{X1,...,Xn} be irreducible with respect to R1.[147] Let there exist some Xi (1 ≤ i ≤ n) and some possibly empty set of attribute renamings on the projection, R1X say, of R1 on Xi that maps R1X into R1Y, say, where R1Y has the same heading as some subset Y of the heading of R2. Further, let the projection of R2 on Y be R2Y. Then The Principle of Orthogonal Design is violated by R1 and R2 if the equality dependency R1Y = R2Y holds.
Now, this looks a little complicated, but basically all it says is that no projection in any nonloss decomposition of R1 can be information equivalent to any projection of R2. Indeed, as you can probably see, much of the complexity in the definition (what complexity there is) arises from the need to deal with the renaming issue. For interest, here’s a slightly simpler version of the definition that ignores that complication:
Definition (third attempt, ignoring renaming): Let relvars R1 and R2 be distinct, and let the JD
{X1,...,Xn} be irreducible with respect to R1. Let some Xi (1 ≤ i ≤ n) be identical to some subset Y of the heading of R2; further, let the projections of R1 on Xi and R2 on Y be R1X and R2Y, respectively. Then The Principle of Orthogonal Design is violated by R1 and R2 if the equality dependency R1X = R2Y holds.
Observe now how adherence to this third version of the principle resolves the problem with our motivating example, in which relvar S was decomposed into its projections SNC and STC on {SNO,SNAME,CITY}) and STC {SNO,STATUS,CITY}, respectively. Suppose that decomposition is done. Then:
The database now contains two distinct relvars, SNC and STC.
Thanks to Heath’s Theorem and the fact that the FD {SNO} → {SNAME} holds in relvar SNC, the JD
{{SNO,SNAME},{SNO,CITY}} holds in, and in fact is irreducible with respect to, that relvar SNC.Thus, the projection of relvar SNC on {SNO,CITY} is part of a valid nonloss decomposition of SNC. But an equality dependency holds between that projection and the projection of STC on those same attributes. Thus, the design violates the orthogonality principle as just articulated (the “third attempt”).
Note: Points b. and c. here could be replaced by the following without changing the overall message:
I now observe that this third version of the orthogonality principle also lets me take care of a piece of unfinished business from Chapter 11. As you might recall, I pointed out in that chapter that the following JD held in relvar S, and in fact was irreducible with respect to that relvar:
I also said that decomposing relvar S on the basis of this JD wouldn’t be a good idea (and Exercise 11.4 asked why not). Well, now we can see that if that decomposition is done:
The database now contains two distinct relvars—I’ll call them SNC and CTN—with headings {SNO,SNAME,CITY} and {CITY,STATUS,SNAME}, respectively.
Thanks to Heath’s Theorem and the fact that the FD {CITY} → {STATUS} holds in relvar CTN, the JD
{{CITY,STATUS},{CITY,SNAME}} holds in, and in fact is irreducible with respect to, that relvar CTN.Thus, the projection of relvar CTN on {CITY,SNAME} is part of a valid nonloss decomposition of CTN. But an equality dependency holds between that projection and the projection of SNC on those same attributes. In other words, the design violates the orthogonality principle once again.
The net of the example is this: Doing a decomposition on the basis of a “bad” JD is contraindicated by virtue of The Principle of Orthogonal Design. (The JD in the example is “bad” because attribute SNAME can be dropped from the {CITY,STATUS,SNAME} component without significant loss.) What’s more, one consequence of abiding by orthogonality is that the fourth of the normalization principles as given at the beginning of the chapter—viz., that every projection should be needed in the reconstruction process—will automatically be satisfied (and so there’s a logical connection, of a kind, between orthogonality and normalization after all).
[147] Note that this JD certainly holds in R1, by the definition of JD irreducibility (see Chapter 11).