Databases Reference
In-Depth Information
ON INSERT
i
INTO ULK :
INSERT
i
INTO PL , INSERT
i
INTO PK ;
Of course, as indicated earlier, we're dealing here once again with a situation in which
information equivalence is lost. To be specific, any information that can be represented by relvar
ULK alone can certainly be represented by relvars PL and PK taken in combination, but the
converse is false. (Here's an example of a query on the latter that has no exact counterpart on the
former: “Get part numbers for parts that are on sale and not in stock.”) As a consequence, it
should be obvious that there'll be certain updates that can be done on PL and/or PK that have no
exact counterpart on ULK. An example of such an update is “Delete some existing tuple from
PL without simultaneously deleting that same tuple from PK” (i.e., update the database to say
some part is no longer on sale but, assuming it was previously in stock, still is).
I'll leave it as an exercise to determine the implications of all of the foregoing for a user
who sees just relvar ULK. However, let me now point out that (as in the case of the intersection
analog of this example in the previous chapter) there's unfortunately another issue here.
Suppose we start off with just relvars PL and PK, without the union view. Suppose too, just to
be definite, that part P4 is represented in neither PL nor PK. Then the following INSERT
operation is clearly legitimate:
INSERT ( P4 ) INTO PL ;
Observe in particular that there's no question of this insert cascading to relvar PK. But
now suppose we introduce the union view ULK. Given the rules defined above, then, this insert
will
now cascade to relvar PK!—and it'll do so, moreover, even if view ULK isn't visible to the
user issuing the insert on relvar PL. Or to put the point another way: Introducing that view
apparently requires the simultaneous introduction of a cascade insert rule from PL to PK and
vice versa.
1
Now, one possible fix for this problem, in the particular case at hand, is to execute an
appropriate delete immediately after the insert, thus:
INSERT ( P4 ) INTO PL ;
DELETE ( P4 ) FROM PK ;
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 case, as discussed in the previous chapter).
1
A related issue is the following. Under the stated rules for updates on ULK, inserting a tuple into ULK and then deleting it
again will preserve the status quo, but deleting a tuple from ULK and then inserting it again might not. The reason is that
deleting a tuple from ULK might actually cause a tuple to be deleted from just one of PL and PK, whereas inserting a tuple into
ULK will always cause a tuple to be inserted into both. Of course, the status quo with respect to relvar ULK as such is always
preserved.
