Databases Reference
In-Depth Information
DELETE ( P1 ) FROM PL ;
DELETE ( P1 ) FROM PK ;
INSERT ( P1 ) INTO PK ;
INSERT ( P1 ) INTO PL ;
However, the solution to be discussed in the subsection immediately following is in my opinion
greatly to be preferred. Of course, it's essentially the same as the solution to the corresponding
problem in the intersection and union cases as discussed in the previous two chapters.
A Better Design
As you'll recall from those previous chapters, the solution I have in mind 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. 11.2 (a repeat of Fig. 9.3 from Chapter 9, also Fig. 10.4 from Chapter 10).
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. 11.2: Relvar POI—sample value
Now we define restriction views PL′ and PK′ and difference view DLK′ as indicated by the
following constraints:
CONSTRAINT ... PL′ = POI WHERE ON_SALE ;
CONSTRAINT ... PK′ = POI WHERE IN_STOCK ;
CONSTRAINT ... DLK′ = PL′ MINUS PK′ ;
Fig. 11.3 shows sample values corresponding to those in Fig. 11.2.
