# Re: more on delete from join

Date: Tue, 1 Sep 2009 09:40:20 -0400

Message-ID: <ap-dnY3aL9bYuQDXnZ2dnUVZ_u6dnZ2d_at_giganews.com>

"paul c" <toledobythesea_at_oohay.ac> wrote in message
news: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:*

*>*

*> S:*

*> s*

*> Bill*

*>*

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

*>*

*> C:*

*> c*

*> Bill*

*> Bob*

*>*

*> 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 JOIN C:*

*> 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*

*> Bill*

*>*

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

*>*

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

*> s*

*> Bill*

*>*

*> 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).*

An appeal to logic. Affirming the disjunction of two ground atoms,

Pabc OR Qabc

implies that either Pabc is true or Qabc is true or both are true. Similarly, denying the conjunction of two ground atoms,

NOT (Pabc AND Rabd),

is equivalent to affirming the disjunction

NOT Pabc OR NOT Rabd

which implies that either Pabc is false or Qabd is false or both are false. Either case involves affirming a disjunction, not a conjunction.

Now let's apply this logic to relational theory. Let there be tables with predicates P, Q and R, a view that is the union of the table with predicate P and the table with predicate Q, and a view that is the join of the table with predicate P and the table with predicate R. Inserting a row (a,b,c) into the union view affirms the disjunction Pabc OR Qabc; deleting a row (a,b,c,d) from the join view affirms the disjunction NOT Pabc OR NOT Rabd. Both inserts into the union view and deletes from the join view affirm disjunctions, and as a consequence, neither inserts into the union view nor deletes from the join view have unique results.

I can see the benefit of recording disjunctive information. If it is known that A OR B is true, then it should be possible to assert A OR B, even if it is not known which is true. I just don't think that an insert into a union view is the correct way to do it. A better way would be to use three tables, one for facts like 'It is known that A.' one for facts like 'It is known that B.' and one for facts like 'It is known that A OR B, but not which.'

<snip> Received on Tue Sep 01 2009 - 08:40:20 CDT