Re: relationship in the database

From: Peter Koch Larsen <pkl_at_mailme.dk>
Date: Thu, 26 Sep 2002 21:45:07 +0200
Message-ID: <3d9363c3$0$18141$edfadb0f_at_dspool01.news.tele.dk>


"Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> skrev i en meddelelse news:amuqai$de0$1_at_sp15at20.hursley.ibm.com...
> >> No, the consequence is that some assignments are recognized as a single
> >> tuple update.
>
> >This is an unwanted and unwarranted limitations.
>
> Agreed
>
> >For a (very practical
> >example) I have phone numbers in a country comprising of ten digits.
> >Of course, the phone number is the natural key for the "phone line" (or
> >whatever is the proper denomination), and I'd argue that no other key is
> >really necessary, so we can imagine that a phoen number is foreign key
> >in _lots_ of places.
>
> >Then a need is recognized to have 11 digits in order to make room for
> >more numbers. The existing numbers will be recognized by prefixing them
> >with, let's say, the digit "5". Quite easy, right ?
> >UPDATE ........ SET number= '5' || number.
>
> >No big deal so far, the changes *should* be propagated in all places.
>
> >Relational assignment doesn't support this. Therefore, I couldn't care
> >less if (some) relational model is well defined and consistent and sound
> >with only relational assignment as the one and only mean to change the
> >database state.
>
> Single relvar assignment indeed does not support the above.
>
> The two traditional (i.e. SQL) answers have been:
>
> a). Transactions. With constraint checking deferred to the finial COMMIT
> b). Referential actions attached to foreign keys. Including an implicit
> tuple-level update semantics for ON UPDATE CASCADE
>
> On a), I see transactions as orthogonal to the model, and in fact would
> argue that they are a bad idea to expose them at the logical level, but
> that is a discussion for another day.

Okay - let us do that. I just remember a transaction should have ACID properties, where the "C" stands for consistency. A good solution in my humble opinion.

> On b). This is obviously an ad-hoc solution that can only cover some cases
> (i.e. those with FKs), irrespective of the implicit tuple assumption
>
I am not sure I follow you here. Surely it must be possible and feasible to have some integrity constraints checked at commit-time and others immediately?
>
> To support the above in the RM, we simply require multiple relvar
> assignemt, and indeed Date&Darwen include this in their model.

>
> [To be slightly critical of D&D for a moment, I would prefer to see them
> talk of dbvar assignment. I.e. the only mutaion operation in the RM is the
> relacing of the current value of the database varable with a new value.
> Single relvar assignement is then just a special, limited case of dbvar
> assignement.]
>
> So, instead of relying on cascaded actions, we simply do the comensating
> actions ourselves in ONE multiple assignent statment
> UPDATE PHONE_NOS SET number= '5' || number
> ,UPDATE PEOPLE SET number= '5' || number
> ,UPDATE COMPANIES SET number= '5' || number
> etc
> ;

Yes - and this is what we (at least I) criticise. It is to easy for the user to forget one of the relations while doing the update. Also, even if the user should remember all the foreign keys (knowing myself, I would probably forget at least one of them!), some day a new FK-dependency is added and the above code is not corrected.

>
> Now the problem just becomes one of convenience.
Convenience matters!
>
> To keep the browsers on board, I'll restate the problem.
>
> I need my example back (slightly altered)
>
> T1(_A_, B)
> T2(_A_, C) REFRENCES T1(A)
>
> INSERT INTO T1 {(1,5),(2,5)}, INSERT INTO T2 ({1,8), (2,9)}
>
> Now,
> UPDATE T1 { A := A + 2};
> results in the same relational assignment to T1 as
> UPDATE T1 { A := 4 - A };
> namely
> T1 := {(3,5), (4,5)}
>
> if we add the SQL style referential action
> ON UPDATE CASCADE ON DELETE CASCADE to the FK,
> we want to get
>
> UPDATE T1 { A := 4 - A };
> resulting in the multiple relational assignment:
> T1 := {(3,5), (4,5)}, T2 := {(3,8), (4,9)};
>
> whereby
> UPDATE T1 { A := A + 2};
> resulting in the multiple relational assignment:
> T1 := {(3,5), (4,5)}, T2 := {(3,9), (4,8)}
>
> so if we translate the update to T1 to an assignment to T1, and _then_
> attempt to apply the referential actions, then the RM cannot perform the
> same semantics as the tuple level semantic of SQL.
> BTW this same problem is also true of SQL 'FOR EACH ROW' triggers.
>
> So the question is, can we 'rescue' these tuple level semantics in the RM?
>
> I say yes and the answer is to simply extend the Macro expression that
> UPDATE consists of in the RM, to consider any referential actions.
>
> E.g.
> Folowing page 167 of the 2nd ed of the Manifesto, we can extand the
> expanded UPDATE macro includes our required referential actions
> (please try to excuse the hacked pseudo SQL/Tutorial D syntax - I'll
> repost if people can't see the idea from the example below..)
>
> for the first update we would get
> WITH ( T1 ) AS old{old_A, old_B},
> ( EXTEND old ADD ( 4 - old_A AS A, old_B as B) ) as upd,
> ( upd { ALL BUT old_A, old_B } as new:
> T1 := ( T1 MINUS old ) UNION new
> ,T2 := ( T2 JOIN old ON A = old_A ) {old.A as A, T2.B}
>
> which would perform the first multiple assignment
>
> and for the second update we would get
> WITH ( T1 ) AS old{old_A, old_B},
> ( EXTEND old ADD ( old_A + 2 AS A, old_B as B) ) as upd,
> ( upd { ALL BUT old_A, old_B } as new:
> T1 := ( T1 MINUS old ) UNION new
> ,T2 := ( T2 JOIN old ON A = old_A ) {old.A as A, T2.B}
>
> which would perform the second multiple assignment
>
> So if you want the convenience of SQL style referential actions in the RM,
> all we need do is realise that they need to be considered while an UPDATE
> is been translated into a relational assignment.

Ahhh... some comments.
1) I hope D-language syntax will not be transferred to any language, I should use. It is not quite as nice as (Costins syntax) update(T1, A := 4 - A) (first example) or update(T1, A := A + 2) (second example) is it?
2) I doubt how easy it is to to automate these examples. 3) Even if you can automate examples such as the above, this only means that the D-language should be the "assembly-language level" of relations. This is not what Date & Darwen intended - is it? 4) I do believe that you could create a model with an SQL-like update (e.g. using Costins syntax, which feels very nice) without (otherwise) violating the relational model in TTM. Perhaps we could even get rid of that ugly-looking comma-operator (which on my dirty monitor might be indescernible from a semicolon anyway ;-)?

>
> Sorted.

Phew!
>
> Regards
> Paul Vernon
> Business Intelligence, IBM Global Services

Kind regards
Peter Koch Larsen Received on Thu Sep 26 2002 - 21:45:07 CEST

Original text of this message