Re: RM VERY STRONG SUGGESTION 4: TRANSITION CONSTRAINTS

From: Brian <brian_at_selzer-software.com>
Date: Fri, 3 Sep 2010 08:35:00 -0700 (PDT)
Message-ID: <01b3645a-db9c-4d9d-84b9-6eee11310ee8_at_x24g2000pro.googlegroups.com>



On Sep 3, 9:41 am, Erwin <e.sm..._at_myonline.be> wrote:
> You may be turning things upside down.
>
> It is crystal clear that in a D (which does not expose internal
> details such as ROWID), individual tuples can only be identified using
> a (/the) key value.  Identifying that individual tuples in two
> distinct database states are about the same real-life object, is then
> only achievable if the key value hasn't changed.
>
> Trying to overcome this, e.g. by introducing some kind of "objectid"
> in the data which is "eternally, once and for all" linked to the tuple
> in question has other serious ramifications.  

I'm not suggesting the introduction of an objectid. I don't think it's necessary to do that.

> For example, I suspect
> that UPDATE can no longer be seen as a shorthand for some particular
> combination of DELETE-then-INSERT, precisely because of this
> additional objectid.

UPDATE is not the same as a combination of DELETE-then-INSERT, but not because of some objectid.

There is a mapping from every tuple in a database to something in the microworld that is being modeled, but more importantly, every tuple maps to something that has a location in time if not also space. If they did not have a location in time, then there could never be any database updates. There wouldn't be any point in having relvars, because each could only ever have one value. Because they are located in time, the things that are mapped to are concrete: they come into existence, possibly change in appearance, and cease to exist. DELETE essentially informs the system that things in the microworld have ceased to exist, INSERT that things have come into existence; UPDATE that things appear different now than they did before the update. It is the generic assignment, :=, not UPDATE, that must therefore be a shortcut for DELETE-then-INSERT.

>
> Besides, transition constraints are, imo, nowhere near as useful as
> the literature sometimes makes them seem to be.  The examples I see of
> them are always of the ilk "salaries cannot decrease" and "married
> cannot change to single".  But what if "married" does indeed need to
> go back to "single" because it was the introduction of "married"
> itself that was wrong in the first place ?  What if salaries DO need
> to decrease because a zero too many had been typed ?  Sorry, you made
> this mistake and you can't correct it ?  Sorry, you'll have to keep
> paying this 10-times-too-big salary and go bankrupt or you'll have to
> fire your employee ?  Come on.

In the example I provided specific business rules that were to be implemented by the transition constraints. The example is from a realworld  application that has been in service for over seven years. The transition constraints were implemented on Microsoft Sql Server using surrogates and triggers. (I had to use surrogates due to a limitation in Sql Server: FOR EACH ROW triggers are not supported.) I agree with Date and Darwen: it should be possible to implement transition constraints declaratively. I just don't think the mechanism they suggest is sound.

> Or "I assume there is some kind of other machinery in place that
> allows supervisors to do all necessary corrections" ?  So the model
> deliberately does not aim to offer support for _ALL POSSIBLE_
> transitions ?  Come on.

The model does aim to offer support for _ALL POSSIBLE_ transitions, but I think that D needs a slight alteration in order to implement them.

I think the mechanism that Date and Darwen propose is not logically sound. Successive values for a relvar hold during adjacent, but not overlapping, intervals in time. What was the case before an assignment can't also be what is the case after (unless it's a null assignment, of course). It is therefore illogical to compare what was the case before an assignment to what is the case after.

From a logical standpoint, a database is in essence a statement that asserts what has up to now been the case. A transition is a statement that asserts, given what had until now been the case, what is different and how it is different--that is, have things come into existence, have things ceased to exist, or if things appear different, what are the differences? From what has until now been the case and what is now different, one can determine what is now the case. One cannot always determine what is different from what had until now been the case and what is now the case, as is illustrated by the example in the original post that is the result of a multiple assignment. Given just the before and after values, it can't be determined with certainty whether Transition Constraint #4 was violated.

Since a transition is a statement just like a database is a statement, it should be possible to represent the transition as a set of relations, three for each relation in the database. One could be adorned with a '-' postfix and would contain the result of DELETEs that target the corresponding relvar; one could have a '+' postfix and would contain the result of INSERTs; one could have a '~' postfix and would contain the result of UPDATEs, each tuple containing the before and after values for each attribute.

In the above example, Transition Constraint #1 would be

CONSTRAINT IS_EMPTY (LABOR~

                WHERE (STATUS = 'A' AND STATUS' = 'O') OR
                          (STATUS = 'O' AND STATUS' = 'C') OR
                          (STATUS' = 'A' AND STATUS <> 'A'))

Transition Constraint #2 would be

CONSTRAINT IS_EMPTY (LABOR- {STATUS} WHERE STATUS = 'A'

                                UNION LABOR~ {STATUS} WHERE STATUS =
'A'
                                 UNION LABOR+ {STATUS} WHERE STATUS =
'A')

Transition Constraint #3 would be
CONSTRAINT IS_EMPTY (LABOR~ WHERE STATUS = 'A' AND STATUS' = 'C' AND

                          (EMP# <> EMP#' OR WO# <> WO#' OR SEQ <> SEQ'
OR M# <> M#' OR
                           LBRTYPE <> LBRTYPE' OR LBRDATE <> LBRDATE'
OR TIMEON <> TIMEON' OR
                           TIMEOFF <> TIMEOFF' OR ELAPSED <> ELAPSED'
OR APPLIED <> APPLIED' OR
                           PRODUCED <> PRODUCED' OR REJECTED <>
REJECTED')) Transition Constraint #4 would be
CONSTRAINT IS_EMPTY (LABOR~ WHERE LBRDATE <> LBRDATE') Received on Fri Sep 03 2010 - 10:35:00 CDT

Original text of this message