more on delete from join
Date: Sat, 22 Aug 2009 18:31:04 GMT
Message-ID: <IPWjm.40275$Db2.30224_at_edtnps83>
Here's another kick at the old can. The language 'delete D from J' is usually taken to mean the facts represented by D are retracted to produce a new value for J. Expressed algebraically, the resulting value is usually taken to equal J MINUS D, but this expression is not very instructive when it comes to deleting from a view.
Given a view J = A JOIN B where J, A and B are relvars, and the headings hA and hB of A and B respectively, the equallity J = J{hA} JOIN J{hB} is always true. We can use this 'invariant' to recast a 'delete definition' in a way that helps instruct an implementation. Whatever else results, this equation must always be true.
The equalities A = J{hA} and B = J{hB} don't necessarily hold because A MINUS J{hA} and B MINUS J{hB} may contain tuples that don't match any projection of the join. Since A MINUS J{hA} and B MINUS J{hB} cannot represent any facts that could be retracted from J, we need to take care to include, ie., 'assert', their values in any result that replaces the values of A and B. (One might choose to view the tuples that represent those relations to be 'spurious', but such a characterization isn't necessary from a logical viewpoint, in fact it's as irrelevant as the colour of a n integer is to arithmetic.)
If we desire to define deletion from a join, the equation J = J{hA} JOIN J{hB} must be true both before and after any replacements of the values of A and B. We can take advantage of this to see, fairly easily, what changes to A and B MUST BE implied by a language's delete statement that is defined with the algebraic expresion J MINUS D.
Assuming that we wish to use the same language to retract a fact from a J that is a view as we would from a J that is base, we might express the retraction by J' = J MINUS D, where the primed J' stands for the resulting relation and D stands for the relation representing the fact(s) we wish to retract.
B
b
3
4
J = A JOIN B:
a b
1 3
1 4
2 3
2 4
D:
a b
1 3
Assume bJ has the same value as J but is base:
bJ' = bJ MINUS D:
a b
1 4
2 3
2 4
However, using the above definition of delete,
A' = (J MINUS D} {hA} UNION (A MINUS J{hA}):
a
1
2
and
B' = {J MINUS D} {hB} UNION (B MINUS J{hB}):
b
3
4
so J' = J. It appears nothing has changed, which is not the case when J is base. This may surprise a user, but there is nothing illogical about the result, which is obtained simply by following a TTM-like algebra without appeal to any kind of intuition. One outcome of this is that bJ and J aren't "interchangeable". But the basic reason for the result is that the relation that D represents has quite a different predicate from J's predicate because it is constrained to a single tuple, whereas J is not constrained that way. Just as the typical relational algebras don't
have any notion of delete, they don't have a notion of exception either. That is, the algebra operations being closed over relations and constraints being defineable only with the algebra, it is left up to the viewer to interpret certain relation values as 'exceptions' (if they wish to).When the headings of A and B overlap, which is much more common, such a definition gives results that have more practical use. Somebody who believes that delete from join is not always logically possible might object on the grounds that the logical complement of a join involves three possible combinations of the A and B result relations whereas relational closure requires us to represent all resulting relations as single relvars or single extensions or single 'tables'. This is a red herring because we don't need to record complements.
Of course, anybody, eg., the implementors of various SQL products, could claim this result isn't allowed by their product's definitions. But those are piecemeal definitions that are based on relational logic in some places but not in others. Received on Sat Aug 22 2009 - 20:31:04 CEST