# Re: more on delete from join

From: Kevin Kirkpatrick <kvnkrkptrck_at_gmail.com>

Date: Tue, 1 Sep 2009 08:12:00 -0700 (PDT)

Message-ID: <d8e37957-af0e-4ae8-98dd-a6e20200fe09_at_z34g2000vbl.googlegroups.com>

On Sep 1, 8:40 am, "Mr. Scott" <do_not_re..._at_noone.com> wrote:

Date: Tue, 1 Sep 2009 08:12:00 -0700 (PDT)

Message-ID: <d8e37957-af0e-4ae8-98dd-a6e20200fe09_at_z34g2000vbl.googlegroups.com>

On Sep 1, 8:40 am, "Mr. Scott" <do_not_re..._at_noone.com> wrote:

*> "paul c" <toledobythe..._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>- Hide quoted text -**>**> - Show quoted text -*Yes - the "sound" approach would seem to be: the DBMS should only accept propositions which exactly conform to the predicates of the underlying base relvars. If there is a need within the user community to record propositions which do not exactly fit the existing base relvars, then the data model does not properly reflect the Universe Of Discourse, and needs to be modified accordingly. If there is a desire within the user communit to record propositions which do exactly fit existing base relvars, then the end-user (or the application they are using) should do so with precise assignments to the appropriate relvars. Received on Tue Sep 01 2009 - 10:12:00 CDT