# Re: more on delete from join

Date: Tue, 25 Aug 2009 04:59:49 -0400

Message-ID: <VqydnQmqmaKYNQ7XnZ2dnUVZ_uudnZ2d_at_giganews.com>

"paul c" <toledobythesea_at_oohay.ac> wrote in message
news: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.
**>
**> If A' and B' stand for the resulting values of the base relvars,
**>
**> A' = (J MINUS D} {hA} UNION (A MINUS J{hA})
**> B' = {J MINUS D} {hB} UNION (B MINUS J{hB}).
*

> The equations for A' and B' don't necessarily produce the same result when

*> J is a join view as they do when J is base, for example, if A and B have
**> distinct headings and D contains a single tuple, J' = J. Here are sample
**> values for that 'cartesian product':
**>
**> A:
**> a
**> 1
**> 2
**>
**> 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 Tue Aug 25 2009 - 10:59:49 CEST