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