Databases Reference
In-Depth Information
exp1
≝
SP
/* defining view VSP1 */
exp2
≝
S JOIN SP
/* defining view VSP2 */
Observe here that (per the foregoing discussion)
exp2
can be “semantically transformed” into
exp1
.
4
It follows that, at any given time, VSP1 and VSP2 both have the same value; in fact, they
both have the same value as the relvar SP does at the time in question. However, now consider
the following deletes:
DELETE ( S2 , P2 ) FROM VSP1 ;
DELETE ( S2 , P2 ) FROM VSP2 ;
Once again the two deletes both have the same effect on the views as such. But it's easy to
see that the first causes the tuple (S2,P2) to be deleted from relvar SP and has no effect on relvar
S, while the second causes that same tuple to be deleted from relvar SP and—at least according
to the rules given in Chapter 8 for deleting through a one to many join—also causes the tuple for
supplier S2 to be deleted from relvar S.
5
Next, consider the following inserts:
INSERT ( S4 , P1 ) INTO VSP1 ;
INSERT ( S4 , P1 ) INTO VSP2 ;
The first of these inserts fails on a
Golden Rule
violation (specifically, a violation of the
foreign key constraint from SP to S). The second, however, causes the specified tuple to be
inserted into relvar SP and—again, at least according to the rules given in Chapter 8 for inserting
through a one to many join—also causes a tuple for supplier S4 to be inserted into relvar S. So
this time, not only do the updates have different effects on the underlying relvars, they also have
different effects on the views as such! In other words, to spell the point out, here we have a
situation in which “the same” update doesn't even have the same effect on its target relvars, even
though those target relvars do happen to be “equivalent.”
To all of the above let me add that the problems are compounded by (a) the fact that no
possible update on VSP1 has the same effect on the underlying suppliers relvar S as the
4
Or the other way around, of course, a state of affairs that I think bolsters the argument that follows.
5
It's true that the join of S and SP loses information (unless every supplier is required to supply at least one part), and you might
therefore be thinking that the culprit here is the pragma involved in updating through such a join. But it's not. Rather, the
problem seems to be intrinsic. In fact, I'd like to elaborate briefly on that pragma issue. We've seen that deleting the tuple
(S2,P2) from views VSP1 and VSP2 has different effects on relvar S, depending on which view the delete is aimed at. But at
least that delete has the desired effect on the view as such in both cases. By contrast, the position espoused by certain critics,
according to which deletes on such views like VSP2 must be rejected entirely (see Chapter 6), would cause the delete on view
VSP1 to succeed but the one on view VSP2 to fail! In my opinion, therefore, that position involves just as much pragma as, and
has no more claim to “respectability” than, the approach described in Chapter 6 does.
