Databases Reference
In-Depth Information
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:
a.
The database now contains two distinct relvars, SNC and STC.
b.
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.
c.
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:
b.
Thanks to Heath's Theorem and the fact that the FD {CITY} → {STATUS} holds in relvar STC, the JD
{{CITY,STATUS},{CITY,SNO}} holds in, and in fact is irreducible with respect to, that relvar STC.
c.
Thus, the projection of relvar STC on {CITY,SNO} is part of a valid nonloss decomposition of STC. But an
equality dependency holds between that projection and the projection of SNC on those same attributes.
Thus, the design violates the orthogonality principle as just articulated (the “third attempt”).
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:
{ { SNO , SNAME , CITY } , { CITY , STATUS , SNAME } }
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:
a.
The database now contains two distinct relvars─I'll call them SNC and CTN─with headings
{SNO,SNAME,CITY} and {CITY,STATUS,SNAME}, respectively.
b.
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.
c.
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
 
Search WWH ::




Custom Search