Databases Reference
In-Depth Information
Supplier S1 supplies part P1 in quantity 300.
In fact, either of the two relvars SP and PS can be defined in terms of the other, as the following constraints
(actually EQDs once again) both show:
CONSTRAINT ...
PS = SP RENAME { SNO AS SNR , PNO AS PNR , QTY AS AMT } ;
CONSTRAINT ...
SP = PS RENAME { SNR AS SNO , PNR AS PNO , AMT AS QTY } ;
A database that contained both relvars would thus clearly involve some redundancy.
7
The net of the foregoing discussion is this: There's a many to many relationship between tuples and
propositions─any number of tuples can represent the same proposition, any number of propositions can be
represented by the same tuple. Given this state of affairs, then, here's an attempt at stating the orthogonality priniple
a little more precisely:
Definition
(
second attempt
): Let relvars
R
and
R2
be distinct, and let them have headings {
A1
,...,
An
} and
{
B1
,...,
Bn
}, respectively. Let relvar
R1
be defined as follows:
R1
=
R
RENAME {
A1
AS
B1′
, ... ,
An
AS
Bn′
}
where
B1′
, ...,
Bn′
is some permutation of
B1
, ...,
Bn
. (Observe that
R1
and
R2
thus have the same heading.)
Then
The Principle of Orthogonal Design
says there must not exist restriction conditions
c1
and
c2
, neither
of which is identically false, such that the equality dependency (
R1
WHERE
c1
) = (
R2
WHERE
c2
) holds.
Points arising from this second attempt:
This version of the principle certainly solves the problem with the design of Fig. 14.3: First, take
R
and
R2
to
be LP and HP, respectively, and define
R1
thus:
R1
= LP RENAME { PNO AS PNO , ... , CITY AS CITY }
(In other words, take
R1
to be identically equal to
R
.) Second, take both
c1
and
c2
to be the restriction
condition WEIGHT = 17.0. Then the equality dependency (
R1
WHERE
c1
) = (
R2
WHERE
c2
) holds, and
the design thus violates
The Principle of Orthogonal Design
.
Note:
As this example demonstrates, so long
as
c1
and
c2
aren't identically false, then certain tuples exist that, if and when they represent “true facts,”
must appear in both
R1
and
R2
─and, in essence, that's the situation we want to outlaw. (By contrast, if
c1
and
c2
were identically false, the restrictions
R1
WHERE
c1
and
R2
WHERE
c2
would both be empty, and
there wouldn't be any orthogonality violation.)
In fact, this version of the principle subsumes the previous version, because (a) we can make
R1
identical to
R
(by effectively making the renaming a “no op,” as in the previous bullet item) and (b) we can take each of
c1
and
c2
to be simply TRUE. (As I pointed out earlier, the previous version of the principle did assume the
7
The example thus suggests an obvious rule of thumb: When you start the design process─which as far as I'm concerned means when you write
down the predicates and other business rules─
always use the same name for the same property
; don't “play games” by using, e.g., both SNO and
SNR to refer to supplier numbers, both QTY and AMT to refer to quantities, and so on. Following this rule will (among other things) make it less
likely that two distinct tuples will represent the same proposition.
