Re: more on delete from join

From: Mr. Scott <do_not_reply_at_noone.com>
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}).

I don't think this is right. It assumes that a delete from J translates into a delete from both A and B, when a delete from either A or B would suffice. The predicate of a join view is the conjunction of the predicates of the tables being joined. For there to be a row in J requires that there be a row in both A and B, so the fact represented by a row in J is the conjunction of the facts represented by the corresponding rows in A and B. Denial of at least one of the facts represented in A or B denies the fact represented in J; conversely, denial of the fact represented in J denies at least one of the facts represented in A or B, but not necessarily both.

Assuming that your interpretation is the right one, trying to delete from a join view would be like trying to delete from a denormalized table. One of the reasons to normalize is to eliminate so-called update anomalies. For example, a table that contains both customer information and order information exhibits a delete anomaly. When the last order for a customer is deleted, that customer's information is deleted. Decomposing the table into a customers table and an orders table with a foreign key from orders to customers eliminates that anomaly. If we accept your interpretation, then trying to delete from a view that joins orders and customers reintroduces the anomaly.

> 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

Original text of this message