Databases Reference
In-Depth Information
A Better Design
As you'll recall from the previous chapter, the solution I have in mind here involves replacing
relvars PL and PK in their entirety by a single relvar, POI, with attributes PNO, ON_SALE, and
IN_STOCK (where attributes ON_SALE and IN_STOCK are of type BOOLEAN and have the
obvious interpretations, and {PNO} is the sole key). A possible value for such a relvar is shown
in Fig. 10.4 (a repeat of Fig. 9.3 from the previous chapter).
POI
┌─────┬─────────┬──────────┐
│ PNO │ ON_SALE │ IN_STOCK │
├═════┼─────────┼──────────┤
│ P1 │ TRUE │ FALSE │
│ P2 │ TRUE │ TRUE │
│ P3 │ TRUE │ FALSE │
│ P4 │ FALSE │ FALSE │
│ P5 │ FALSE │ TRUE │
│ P6 │ TRUE │ FALSE │
└─────┴─────────┴──────────┘
Fig. 10.4: Relvar POI—sample value
Now we define restriction views PL′ and PK′ and union view ULK′ as indicated by the
following constraints:
CONSTRAINT ... PL′ = POI WHERE ON_SALE ;
CONSTRAINT ... PK′ = POI WHERE IN_STOCK ;
CONSTRAINT ... ULK′ = PL′ UNION PK′ ;
Fig. 10.5 shows sample values corresponding to those in Fig. 10.4.
PL′ ULK′
┌─────┬─────────┬──────────┐ ┌─────┬─────────┬──────────┐
│ PNO │ ON_SALE │ IN_STOCK │ │ PNO │ ON_SALE │ IN_STOCK │
├═════┼─────────┼──────────┤ ├═════┼─────────┼──────────┤
│ P1 │ TRUE │ FALSE │ │ P1 │ TRUE │ FALSE │
│ P2 │ TRUE │ TRUE │ │ P2 │ TRUE │ TRUE │
│ P3 │ TRUE │ FALSE │ │ P3 │ TRUE │ FALSE │
│ P6 │ TRUE │ FALSE │ │ P5 │ FALSE │ TRUE │
└─────┴─────────┴──────────┘ │ P6 │ TRUE │ FALSE │
PK′ └─────┴─────────┴──────────┘
┌─────┬─────────┬──────────┐
│ PNO │ ON_SALE │ IN_STOCK │
├═════┼─────────┼──────────┤
│ P2 │ TRUE │ TRUE │
│ P5 │ FALSE │ TRUE │
└─────┴─────────┴──────────┘
Fig. 10.5: Relvars PL′, PK′, and ULK′—sample values
