Re: more on delete from join

Walter Mitty wrote:
,,,
> Now if we switch over to
>
> A JOIN B = C
>
> and we specify that
>
> C' = C MINUS D
>
> where D is some set of tuples to be deleted, if present
>
> Then our job becomes to find A' and B' such that
>
> A' JOIN B' = C'
>
> This is underconstrained, This kind of rambling, but I hope it suggests
> something.
>

I presume there is a "constraint" you didn't mention, that C is a view and so has a constraining expression.. Another might reasonably be that we expect whatever algebra is involved to produce consistent results for queries and updates, eg., so that "(R MINUS S) UNION S = R".

If A has the value:

a
1
2

and B has the value

b
3
4

C = A JOIN B has the value

a b
1 3
1 4
2 3
2 4

I would ask if there is a base relation that is equal to C, eg., is base relation

X:
a b
1 3
1 4
2 3
2 4

equal to C? Its extension is equal, but I would suggest that its extension is not an equivalent way of stating the relation C unless every operation we can apply to C produces the same resulting relation when it is applied to X.

If we delete the tuple <a 1, b 3> from C by deleting <a 1> from A, the result is:

C':
a b
2 3
2 4

If we delete the projection <a 1> from X, the result is the same:

X'
a b
2 3
2 4

We also get "equal" extension results if we delete from B instead of A, but note they aren't the same results!

The only other choice is use exactly the same delete statement for both C and X, say by specifying the tuple <a 1, b 3>, but delete one projection from A and the another from B to get:

C'
a b
2 4

and simply delete the tuple <a 1, b 3> from X to get:

X'
a b
1 4
2 3
2 4
.
we have different (overlapping, too!) resulting extensions making me conclude that even though the original extensions look the same, C and X are not really equivalent relations as far as deletion is concerned, unless we never delete from both of A and B and we always pick the same base base table/relvar/variable to delete from. This latest value of X' is not even a join. Clearly C has a constraint that X doesn't have, so I'd say that X and C are not really alternatives of each other..

We could constrain X so that it is a join, say named XC, and get equal result relations no matter which attributes we specify in a delete. Then we could say that the constrained XC and C are equivalent relations, ie., equal alternatives. Why should we do that? I'd say we should so that users will get the same results from a delete no matter whether the "target" is base or view. Otherwise we'd be saying that certain relations can only be expressed as extensions of views and certain other relations can only be expressed as base extensions. If we did that, I think users would go crazy trying to figure out why delete behaved one way yesterday and another way today. To avoid that, dba's would have to never change their minds and switch a table/relvar from base to view or vice versa, which I think would drive dba's crazy. Some dba's I've known thought changing their minds was essential to their job security and a few others were obsessed with normal forms, so that they desired to 'switch bases and views' regularly as new applications were added..

To constrain X so that it is equivalent under deletion to C, we would need two more relations, XA and XB, so that XC = XA JOIN XB. If both were views, eg., each a projection of X's attributes, so that XA = XC{a} and XB = XC{b}, then that last result value for X' would be impossible.   (If both were base, they would need their own constraints, such that XA = XC{a} and XB = XC{b}, and similarly if only one were base and the other a view.)

If XA and XB are views, then any delete from the constrained XC will always delete from both views. So for C to be equivalent to XC under delete, the dbms would need to always delete from both of A and B.

An alternative to all this, which I gather most current SQL products follow in some fashion or other, is to say that some tables can be deleted from and some can't. What that means to me is that those products cannot always logically decide the result of "select C where a not equal 1 or b not equal 3" because if they refuse to record the extension of the restriction, they will need to refuse the query of the extension of the restriction. If they do that, it must really drive users nuts. If they don't, they are allowing contradictions as far as their algebra is concerned. Received on Tue Sep 01 2009 - 02:16:42 CEST