Re: more on delete from join

From: paul c <>
Date: Sun, 30 Aug 2009 19:08:09 GMT
Message-ID: <t6Amm.41661$Db2.31934_at_edtnps83>

Mr. Scott wrote:
> The system having to guess what the user intended is a direct consequence of
> view updates being nondeterministic. For example, an insert into a union
> view involving two tables is not deterministic because there are three
> different combinations of values that could result in the same value for the
> view. The system has to guess whether the row must be inserted into one
> table or the other or both. The same can be said of a delete from a join
> view.

This the standard absolutist, self-serving complaint. Of course if you set up a system that defines certain view updates as being nondeterministic, certain view updates will be nondeterministic, but that is not a consequence of relational theory. I don't care whether the motive comes from wilfulness or a wish to have simplistic implementations or just confusion about the significance of atttribute names, instead I'll take a stab at discarding the non-essential stuff and stick to what I hope involves only predicates and a strict (TTM) algebra.

i) Suppose we have a relation S with predicate "s is a supplier" and value:


and a relation C with predicate "c is a customer" and value:


So Bill is a supplier, Bill is a customer and Bob is a customer.

ii) Supposing we wish to distinguish customers who are also suppliers and suppliers who are also customers, mere join (conventional) doesn't quite produce a relation that can be queried without qualification, ie

s c
Bill Bill
Bill Bob

This has the predicate "s is a supplier and c is a customer". In practice, it is more likely that 's' and 'c' would not be distinct attribute names, still this join does allow us to conclude that Bill is both a supplier and a customer, if the application language includes an "equals" operator and users remember to use it. .

iii) If we want the recording form itself to reflect the predicate we want without depending on implementation language, eg., a slightly different predicate, say "s is a supplier and c is a customer and s and c have the same name", we need a join definition that makes the equality explicit, by restricting the join to tuples where s = c, eg., :
R1 = ((S <RENAME> (s, c) ) <AND> C) <AND> S with value: s c
Bill Bill

In fact, S JOIN C, aka S <AND> C isn't quite what we want, rather what we want is equivalent to: S <AND> C <AND> R1.

R1's predicate could also be written as "s is a customer and s is a supplier and c is a customer and c is a supplier" which might seem a little "redundant" because it amounts to saying the same thing twice.

iv) We could say the same thing as R1 with two relations R2 and R3:

R2 = ((S <RENAME> (s, c) ) <AND> C) with value: c

R2 has the predicate "c is a customer and c is a supplier".

and with R3 = R2 <RENAME> (c,s) with value: s

R3 has the predicate "s is a customer and s is a supplier".

v) Note that R2 = R1{c} and R3 = R2{s} and R1 = R2 JOIN R3 = R2 <AND> R3.

vi ) Suppose we wish to retract the fact that "Bill is a customer and Bill is a supplier, ie.::

<NOT> R1 =
<NOT> (R2 <AND> R3) =
<NOT> R2 <OR> <NOT> R3

As many people point out, the 'disjoin' has three possible values. But if we assert one of them, say, that <NOT> R2 is true and <NOT> R3 is false, we are left with the truth of R3, ie., "Bill is a customer and Bill is a supplier", which contradicts what we intended. We can avoid that contradiction by asserting (<NOT> R2) <AND> (<NOT> R3).

I suggest that any join can be viewed this way so that it can be 'deleted from', no matter whether it is base or view..

(I'm not suggesting that an implementation language needs to rely on <RENAME> to form a join, rather that its join definition could take the above into account.) Received on Sun Aug 30 2009 - 21:08:09 CEST

Original text of this message