Databases Reference
In-Depth Information
Now we define restriction views PL′ and PK′ of relvar POI as indicated by the following
constraints (I use the primed names PL′ and PK′ in order to avoid confusion with relvars PL and
PK as previously defined, but of course they—i.e., relvars PL′ and PK′—serve essentially the
same purpose as those earlier relvars did):
6
CONSTRAINT ... PL′ = POI WHERE ON_SALE ;
CONSTRAINT ... PK′ = POI WHERE IN_STOCK ;
And now we can define XLK′ as the intersection of PL′ and PK′, and we have:
CONSTRAINT ... XLK′ = PL′ INTERSECT PK′ ;
(Of course, we could also define XLK′ as a direct restriction of POI, thus: POI WHERE
ON_SALE AND IN_STOCK. But let's ignore this possibility until further notice.) See Fig. 9.4
for some sample values, corresponding of course to the sample value of relvar POI as shown in
Fig. 9.3.
PL′ XLK′
┌─────┬─────────┬──────────┐ ┌─────┬─────────┬──────────┐
│ PNO │ ON_SALE │ IN_STOCK │ │ PNO │ ON_SALE │ IN_STOCK │
├═════┼─────────┼──────────┤ ├═════┼─────────┼──────────┤
│ P1 │ TRUE │ FALSE │ │ P2 │ TRUE │ TRUE │
│ P2 │ TRUE │ TRUE │ └─────┴─────────┴──────────┘
│ P3 │ TRUE │ FALSE │
│ P6 │ TRUE │ FALSE │
└─────┴─────────┴──────────┘
PK′
┌─────┬─────────┬──────────┐
│ PNO │ ON_SALE │ IN_STOCK │
├═════┼─────────┼──────────┤
│ P2 │ TRUE │ TRUE │
│ P5 │ FALSE │ TRUE │
└─────┴─────────┴──────────┘
Fig. 9.4: Relvars PL′, PK′, and XLK′—sample values
The predicates (deliberately spelled out in somewhat excruciating detail) are as follows:
PL′:
Part PNO is on sale if and only if ON_SALE is TRUE and in stock if and only if
IN_STOCK is TRUE (and ON_SALE is TRUE).
PK′:
Part PNO is on sale if and only if ON_SALE is TRUE and in stock if and only if
IN_STOCK is TRUE (and IN_STOCK is TRUE).
6
Here's a question for you: Is the design consisting of relvars PL′ and PK′ information equivalent to the one consisting of relvars
PL and PK?
