Re: relationship in the database

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

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
 ;

Now the problem just becomes one of convenience.

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.

Sorted.

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Thu Sep 26 2002 - 13:13:33 CEST