Databases Reference
In-Depth Information
As for constraints, {PNO} is obviously the sole key for each of these three relvars, and of
course XLK is equal to the intersection of the other two:
CONSTRAINT ... XLK = PL INTERSECT PK ;
Updates on PL and/or PK are noncontroversial:
ON INSERT
i
INTO PL : INSERT (
i
INTERSECT PK ) INTO XLK ;
ON INSERT
i
INTO PK : INSERT (
i
INTERSECT PL ) INTO XLK ;
ON DELETE
d
FROM PL : DELETE
d
FROM XLK ;
ON DELETE
d
FROM PK : DELETE
d
FROM XLK ;
Now what about updates on XLK? Well, the insert rule is obvious:
ON INSERT
i
INTO XLK :
INSERT
i
INTO PL , INSERT
i
INTO PK ;
As for the delete rule, clearly it would be sufficient for deletes on XLK to cascade to just
one of PL and PK (it must cascade to at least one, of course). However, there's no good reason
for choosing either of these possibilities over the other; in fact, what we have here once again is
the ambiguity (?) issue that I discussed in detail in Chapter 6—at least, it's a special case of that
issue—and the discussions in that chapter apply here also, mutatis mutandis.
4
I therefore
propose the following as an appropriate delete rule (and observe that, not incidentally, it has the
same general form as its counterpart in Example 1 in the previous section):
ON DELETE
d
FROM XLK :
DELETE
d
FROM PL , DELETE
d
FROM PK ;
Of course, once again we're dealing with a situation in which information equivalence is
lost. To spell the point out, any information that can be represented by relvar XLK 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 XLK. An example of such an update is “Insert a new tuple into PL without
simultaneously inserting that same tuple into PK” (i.e., update the database to say some part is on
sale but, if it wasn't previously in stock, still isn't).
I'll leave it as an exercise to determine the implications of all of the foregoing for a user
who sees just relvar XLK. However, let me now point out that, sadly, there's another issue here
(I touched on this issue in Chapter 6, but I didn't elaborate on it in that chapter). Suppose we
4
I remind you that David McGoveran has a proposal for resolving the ambiguity, which I'll be discussing in Chapter 15. Do
note carefully, however, that the ambiguity problem didn't arise with Example 1, where the overlap was explicit.
