Databases Reference
In-Depth Information
start off with just relvars PL and PK, without the intersection view, and suppose too that part P2
is represented in both relvars. Then the following update is clearly legitimate:
DELETE ( P2 ) FROM PL ;
Observe in particular that there's no question of this delete cascading to relvar PK. But
now suppose we introduce the intersection view XLK. Given the rules defined above, then, this
delete
will
now cascade to relvar PK!—and it'll do so, moreover, even if view XLK isn't visible
to the user issuing the delete on relvar PL. Or to put the point another way: Introducing that
view apparently requires the simultaneous introduction of a cascade delete rule from PL to PK
and vice versa.
5
Such a state of affairs doesn't seem very desirable, to say the least. Can we do anything
about it? Well, one possible (though annoying) pragmatic fix, in the particular case at hand, is to
execute an appropriate insert immediately after the delete, thus:
DELETE ( P2 ) FROM PL ;
INSERT ( P2 ) INTO PK ;
To my mind, however, the solution to be discussed in the subsection immediately following is
greatly to be preferred.
A Better Design
Suppose we replace relvars PL and PK in their entirety by a single relvar—let's call it 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. 9.3 (note the tuple for part P4 in particular).
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. 9.3: Relvar POI—sample value
5
A related issue is the following. Under the stated rules for updates on XLK, deleting a tuple from XLK and then inserting it
again will preserve the status quo, but inserting a tuple into XLK and then deleting it again might not. The reason is that inserting
a tuple into XLK might actually cause a tuple to be inserted into just one of PL and PK, whereas deleting a tuple from XLK will
always cause a tuple to be deleted from both. Of course, the status quo with respect to relvar XLK as such is always preserved.
