Re: delete from jon

Walter Mitty wrote:
....
> Part of the problem with regard to sloppy language is that the term "view
> update" is misleading. If view C is defined as A join B and one were to
> apply an update, let's say MINUS D, what gets updated? If we were, in
> reality, updating view C, then the update would be really simple. We would
> update view C by changing its definition. The new definition of C is (A
> join B) minus D. This might require making D be persistent, so that the
> view can be applied later. Hey, presto! View C has just been updated!
>
> But that's not what we really mean when we say "update view C". What we
> mean is "leave view C defined exactly as before, but update A and B such
> that the effect on C's apparent extension is the same as if the update had
> been applied to a base relvar whose extension is the same as C's apparent
> extension." Under this meaning of "update view C" the operation is
> underconstrained, as has already been noted.

I changed the subject back to delete from join because the above is not about insert to projection. They are very different problems (unlike delete from join, insert to projection is not just a matter of constraints).

The "effect on C's apparent extension" after a delete from an unconstrained base is not "the same as" as a delete from a constrained view. To get the same effect, the base needs to have constraints equivalent to the view's constraints.

One of those constraints in this case is that the result satisfies A' JOIN B'. Now ask: when C is base, what are the values of A' and B'?

For example, take the simplest case where A and B have the same heading.   Using Walter's notation, if A' = B' (ie., the values after the delete), A must have been equal to B before the delete, ie., A = B, because any tuple in A that isn't in B couldn't have been deleted from A because it wasn't in the join in the first place, likewise for a tuple in B that isn't in A. So if A' = B' any tuple that was deleted from A must have been deleted from B as well.

If A' and B' aren't equal, suppose A' has a tuple that isn't in B'. If that tuple isn't in A, it must have been inserted to give A'. So the statement 'delete D from C' seems to have had the effect of inserting a tuple to A. But if that tuple is in D and C, it must already have been in A so the tuple wasn't actually inserted. So any definition of delete that presumes that D is in C can't have the effect of inserting a tuple, only delete. So the tuple must have been in A, is still in A' and it must have been deleted from B. The effect is that D was inserted to A MINUS B. But A MINUS B is not in A JOIN B!

When the definition of delete is C = C MINUS D, ambiguous choices are possible but they are logically possible only because of simplistic definition. If we want a language definition for delete from join that results only in deletes of tuples that are in the join and not inserts of tuples that aren't in the join, then C' = C MINUS D isn't good enough. For that we need another constraint to prevent deleting from only one of the join operand values, namely the constraint A' = B'. Such a definition is not 'underconstrained'. because it requires that D be deleted from both A and B.

This doesn't prevent a db designer from requiring that C' = C' MINUS D and A' = A, ie., forcing deletes from C to delete only from B. To do this, he would define C as B MINUS (A MINUS B) instead of A JOIN B. This is actually two joins. It might seem that the "right-hand" join which is equivalent to A AND NOT B must delete from A but the right-hand join is an intermediate result, not the final result.

Such a definition does allow changes to relvars that aren't referenced by the join, eg., the database might have a relvar Z that happens to be defined as A MINUS C and has no mutual constraints with C, but for any relvar, preventing certain changes depends on the constraints supplied by the db designer.

Delete from join where A and B have different headings is more complicated because other constraints as well as intermediate projections are involved. Regardless, the basic ideas are two: i) delete means delete, ie., D is asserted false and if a result that makes D false and violates no constraint is possible then that is the result and ii) unlike when A and B have equal headings, when the headings are unequal a delete from one doesn't necessarily result in a delete from the other. If the join has attributes {a, b} and A has attributes {a}, and we try to delete the tuple <a 1, b 2>, the deletion of <a 1> from A doesn't imply deleting <a 1, b 2> from C and <a 1, b 2> must remain true unless there is a constraint that both tuples must be in C or both not be in C. Ie., <a 1, b 2> being false doesn't mean <a 1> is false.

Some people will argue that we can't delete <a 1, b 2> from B either, because we don't know that B has the same predicate as C. If that is the correct way to see things, I'd ask how do we know that B has the same predicate as C when C is base, ie., how can we ever delete from any base relvar, let alone from a view? Predicates aren't recorded! That would lead me to conclude that their view of delete is that it means C and D have the same predicate when C is base but not necessarily the same when C is a view. They may be right logically, but the practical result would be useless, we could never insert or delete at all. Since predicates aren't recorded, a practical dbms must "take the user's word for it" that the predicates are compatible.

If NOT D is impossible a language might require an exception or it might simply not delete and leave detection up to the user.

If a language designer chooses an ambiguous definition for delete from join, I can't stop him, but it seems silly to me and rather self-serving to set the stage that way and then turn around and say certain results are always undecideable. Received on Sat Sep 05 2009 - 23:17:46 CEST