I'm wondering if it is even possible to define the transition
constraints below in D using Date and Darwen's mechanism described in
TTM. What follows is a typical relvar that is used to record labor
activities performed by employees in a manufacturing facility. (I've
taken liberties with the D syntax for brevity.)

LABOR {EMP#, WO#, SEQ, M#, LBRTYPE, LBRDATE, TIMEON, TIMEOFF, ELAPSED,
APPLIED, PRODUCED, REJECTED, STATUS},
KEY {EMP#, WO#, SEQ, M#, LBRTYPE, LBRDATE, TIMEON},
KEY {EMP#, WO#, SEQ, M#, LBRTYPE, LBRDATE, TIMEOFF}
CONSTRAINT IS_EMPTY ( LABOR WHERE
( STATUS = ‘O’ AND
( TIMEON <> TIMEOFF OR
ELAPSED <> 0 OR APPLIED <> 0.0 OR
PRODUCED <> 0 OR REJECTED <> 0 ) ) )

EMP# identifies the employee who performed the activity.

WO# identifies the work order to which the activity applies.

SEQ is the sequence in the work order.

M# identifies the machine on which the activity was performed.

OP identifies the operation performed.

LBRTYPE identifies the type of labor: S[etup], D[irect], R[ework].

LBRDATE identifies the date on which the labor activity was
performed. Activities performed after midnight on a shift that starts
before midnight are applied to the previous day.

TIMEON and TIMEOFF are the times the labor activity began and
ended respectively. Labor activities that are currently being
performed have the same TIMEON and TIMEOFF. TIMEOFF can be less than
TIMEON when an activity starts before and ends after midnight.

ELAPSED is the elapsed working time in minutes. Working time does
not include breaks or lunch. ELAPSED is always less than 1440
minutes. ELAPSED is zero when TIMEON and TIMEOFF are the same.

APPLIED is the working time in hours that is to be applied to the
work order. (This is not necessarily the same as ELAPSED/60. An
employee may have been on more than one job at the same time, for
example, monitoring several different machines at the same time.)
Like ELAPSED, APPLIED is zero when TIMEON and TIMEOFF are the same.

PRODUCED and REJECTED are the quantities of the part for which the
work order was cut that were produced and rejected as a result of the
labor activity. It is possible for these to be zero—there are
operations that can take more than one shift to complete, or the
activity may have begun late in the shift. These are also zero when
TIMEON and TIMEOFF are the same.

STATUS is one of O[pen], C[losed], or A[pproved]. STATUS is
O[pen] for labor activities that are currently being performed; STATUS
is C[losed] for activities that are no longer being performed; STATUS
is A[pproved] for activities that have been approved by management.


Here are four transition constraints that implement specific business
rules.

Transition constraint #1:
STATUS can only transition from O[pen] to C[losed] or from C[losed]
to A[pproved].

Closed activities cannot be reopened; instead, a new activity is
opened.

Labor activities can't be approved while they’re being performed.

Transition constraint #2:
Labor activities with STATUS A[pproved] cannot be INSERTed, UPDATEd
or DELETEd.

Approved labor activities cannot be INSERTed because they have to
have been reviewed before they can be approved. They can't have been
reviewed if they hadn't already been in the database with STATUS
C[losed].

Labor activities that have been approved affect WIP (work in
progress inventory); as a consequence, once an activity has been
approved, it cannot be altered or eliminated. Corrections to offset
activities approved in error are accomplished through auditable
journal entries recorded somewhere else in the database.

Transition constraint #3:
When STATUS transitions from C[losed] to A[pproved],
only STATUS transitions from C[losed] to A[pproved].

A labor activity that is being approved has to have been reviewed
before it can be approved. That can't have happened if any component
other than STATUS differs.

Transition constraint #4:
LBRDATE can't be different.
The date that a labor activity was performed doesn't change.


Is it possible to implement the above transition constraints using the
mechanism described by Date and Darwen on page 220 of TTM, Third
Edition, RM VERY STRONG SUGGESTION 4: TRANSITION CONSTRAINTS? In this
section they suggest using a primed relvar name to refer to the
corresponding relvar as it was prior to the update under
consideration.

The keys are compound, and it's possible for both keys to change. For
example, Joe entered the wrong work order when he clocked onto labor.
When he tried to clock off, the system couldn't find the work order,
so the next day an entry showed up on his supervisor’s error log. The
labor activity from yesterday remained open. After determining what
actually occurred, the supervisor specifies the correct work order
number along with the time off, the quantities produced and rejected,
the elapsed time and the hours to be applied, setting STATUS to
C[losed].

Before:
{EMP#:22, WO#:2343, SEQ:12, M#:M3, LBRTYPE, LBRDATE:2010-06-30,
TIMEON:0800, TIMEOFF:0800, ELAPSED:0, APPLIED:0.0, PRODUCED:0,
REJECTED:0, STATUS:O}

After:
{EMP#:22, WO#:2334, SEQ:12, M#:M3, LBRTYPE, LBRDATE:2010-06-30,
TIMEON:0800, TIMEOFF:1500, ELAPSED:330, APPLIED:5.5, PRODUCED:33,
REJECTED:2, STATUS:C}

The work order number is an element of both candidate keys, so both
key values differ as a result of the supervisor's update. How would
it be possible to match the tuple in the relvar as it was prior to the
update under consideration to the tuple in the relvar as it would be
after the update to verify that LBRDATE isn't different?

Just in case you're tempted, you can't assume that the update under
consideration involves only one tuple. There may have been a multiple
assignment, and constraints are checked at statement boundaries--that
is, after the entire multiple assignment, not between its component
assignments. In this case, when the employee tried to log on today,
an error occurred because he was already logged on, so the supervisor
had to manually enter the activity.

Before:
{EMP#:22, WO#:2343, SEQ:12, M#:M3, LBRTYPE, LBRDATE:2010-06-30,
TIMEON:0800, TIMEOFF:0800, ELAPSED:0, APPLIED:0.0, PRODUCED:0,
REJECTED:0, STATUS:O}

After:
{EMP#:22, WO#:2334, SEQ:12, M#:M3, LBRTYPE, LBRDATE:2010-06-30,
TIMEON:0800, TIMEOFF:1500, ELAPSED:330, APPLIED:5.5, PRODUCED:33,
REJECTED:2, STATUS:C}
{EMP#:22, WO#:2343, SEQ:12, M#:M3, LBRTYPE, LBRDATE:2010-07-01,
TIMEON:0800, TIMEOFF:0800, ELAPSED:0, APPLIED:0.0, PRODUCED:0,
REJECTED:0, STATUS:O}

At first glance, this appears to be a clear violation of Transition
Constraint #4 (Everything is identical except LBRDATE!), but it
isn’t. The tuple with WO#:2343 before matches the tuple with WO#2334
after, and the tuple with WO#2343 represents a new labor activity that
began on 2010-07-01. How can that be determined, given just the
relvar as it was prior to the update under consideration and the
relvar as it is after the update.

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

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.

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.

On Sep 3, 9:41*am, Erwin <e.sm... (AT) myonline (DOT) be> wrote:
I'm not suggesting the introduction of an objectid. I don't think
it's necessary to do that.

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.

In the example I provided specific business rules that were to be
implemented by the transition constraints. The example is from a real-
world 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.

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')

On 3 sep, 17:35, Brian <br... (AT) selzer-software (DOT) com> wrote:
It is the same. Always. Before some update, relvar R has value
{t1}. After that update, relvar R has the value {t2}.

Please explain what the difference is between this update and a
multiple assignment consisting of the delete of t1 and the insert of
t2.

My claim is furthermore supported by, e.g. (but not limited to) the
UPDATE example in The Third Manifesto, page 154, and Date's treatment
of view updating in Appendix E, in which he expresses all updates as
consisting merely of an insert portion and a delete portion. In the
beginning of that Appendix, Date even provides actual proof to what I
claim here !



There is no mapping. A database is a collection of assertions of
fact. Insertions are assertions of fact that were previously not
believed to be true, but now do, and deletions are assertions of fact
that were indeed believed to be true, but no longer are.

A database records our beliefs of what the real world is like. Why is
it that this leads so many into the flawed belief that as a
consequence of that, our databases should mimick exactly the same
transition path as the world it models ? There is a difference
between the world itself that might be constrained in how it
transitions from one state to another, and our set of beliefs that had
better not at all be constrained in what transitions it can undergo.
Well, certainly not in the sense that "transition constraints" usually
imply.



My recollection of The third Manifesto is that it only says an
extremely minimal bit about "transition constraints". I hesitate to
call that minimal bit a "proposed mechanism", and certainly do not
find the grounds on which to dismiss it as "logically unsound". Maybe
you can enlighten me with page references.



Depends on what precise meaning you attach here to "compare". Compare
for equality (database equality) ? Presumably not.

A database variable has, at any moment in time, a database value D.
Database updating is the process of replacing that value D with a new
value D'.
The "delta" between D and D' can be modeled as a tuple (called 'S' for
Statement) holding all the deletions and all the insertions of all
(base) relvars.

The following equivalences hold:

D' = D dbadd S /* dbadd is the operator that "applies S to D" */
D = D' dbundo S /* dbundo is the operator that "undoes an update" :
instead of adding the insertions in S and removing the deletions in S,
it adds the deletions in S and removes the insertions in S */
S = D' dbdelta D /* should be obvious */



It can. What isn't certain is that it will always produce the outcome
you would want to see produced.

What you then need to do is reformulate your transition constraints in
such a way that they do.

You can try out SIRA_PRISE if you want. It supports declarative
transition constraints (they're baptised differently, though, for
reasons I documented in the new implementer's comments on TTM).



As I indicated with my D',D and S stuff : you have said nothing
incorrect here, except that a statement is a set of TWO relations per
relvar, not three.

If it were my question I'd pose it to Hugh Darwen's discussion list.

On 03/09/2010 5:51 AM, Brian wrote:

On Sep 3, 2:29*pm, Erwin <e.sm... (AT) myonline (DOT) be> wrote:
<snip>

In the update, the referent of t1 is the referent of t2, but in the
multiple assignment, the referent of t1 ceased to exist and the
referent of t2 came into existence. The "meaning" of the fact "t2" is
therefore different. For example,

before: The person at the front of the line is wearing a red hat.
after: The person at the front of the line is wearing a blue hat.

Did the person who has up to now been at the front of the line don a
blue hat, or has the person who has until now been at the front of the
line been replaced by someone else?


Date oversimplifies. Again, I ask, did the person who has up to now
been at the front of the line don a blue hat, or has the person who
has until now been at the front of the line been replaced by someone
else?

There is definitely a mapping. In his book, Codd wrote, "keys in the
relational model act as surrogates for the objects being
modeled." (Page 25) By definition, every relation has at least one
candidate key, so every tuple has at least one key, and therefore
every tuple maps to something in the microworld that is being
modeled.

<snip>

Page 220-221.

You are committing the same oversimplification as Date. The objects
that are the referents of the facts represented in a database are
concrete: they have a location in time if not also space. They are
not abstract mathematical objects which are independent of time, and
it is a mistake to treat them as such. Concrete objects can come into
existence, they can cease to exist, and they can differ in appearance
at different times during their lifetime.

Please enlighten me: how can it be determined which tuple in the after
value matches the tuple in the before value?

I'll look into it.

Brian wrote:

I disagree. At the logical level of discourse, both assignments are the
same.

On 4 sep, 05:21, Brian <br... (AT) selzer-software (DOT) com> wrote:
So the meaning of t2 does not depend exclusively on the attribute
values within it, but also on its history of how it came into
existence ?

The fact documented by a tuple t2 in a relvar R is nothing more and
nothing less than the instantiation of the relvar's external predicate
with the attribute values of t2. You claim to the contrary, that it
also includes "something" to do with the history of how t2 came to
be. You are free to think as you like. But my position (which is
also Date's) allows me to use the CWA, and the CWA alone, to determine
whether or not some tuple should or should not appear in some relvar.
With your position, that simple decision rule is impossible to follow,
because you also need to somehow inspect the history of the currently
existing tuples, just in order to decide which particular update you
should be issuing.



So predictable. Arguing with examples while leaving out the actual
business requirement.

Which is of course intended to leave yourself the freedom to invent
the business requirements depending on my response, and claim "those
were obvious".

But anyway. I'll assume that your business requirement is about
keeping track of the color of the hat of the person at the front of
the line. That requires a unary relvar with attribute COLOR of some
type, and with the external predicate "The person at the front of the
line is wearing a §COLOR§ hat.". If that really is your business
requirement, then it should be obvious that your example question is
unanswerable from that. More to the point, if it is required that
such questions must be answerable, THEN YOU SHOULD PROVIDE THE RELVARS
TO DO SO. And since the question obviously includes a temporal aspect
("that was up until now at the front of the line"), it might help to
design those relvars according to the proposals laid out in "Temporal
Data and the Relational Model". (Incidentally, you can use SIRA_PRISE
to toy around with temporal data too).



If you say so. I say it is oversimplifying to expect the system to be
able to answer temporal queries from a single unary relvar.



That is not entirely untrue, because of the CWA. A tuple represents a
proposition that is believed to be true in the microworld modeled by
the database in which the tuple appears. Applying the CWA to
determine whether some tuple should or should not appear in the
database, cannot possibly be done by the DBMS, and is therefore the
exclusive responsibility of the user. So while such a mapping can
indeed be perceived to exist, it is by definition of no use to the
DBMS and it is therefore a mistake to let the DBMS be bothered with
such mapping.



I'll refresh my memory.



If you say so.



You are committing the same mistake as all those other, eurhm, people
whose "thinking" has gotten too biased by object-oriented mysticism.

That is, to think that the database must somehow keep track of the
physical identity(/location/...) of the objects they describe.

They must not. Databases are mere collections of assertions of fact,
and there is no legitimate reason to constrain those collections of
assertions of fact to "transition" exclusively along exactly the same
paths as the reality those facts describe. A DBMS cannot itself
observe the microworld to verify that some tuple truly represents a
true fact. It cannot itself observe the objects in that microworld,
and that is why it shouldn't be bothered with such stuff in any way.
Seeing to it that the database accurately describes the reality it is
to model, is the user's responisibility.



If an identifying value has not changed, then by identifying value.
If an identifying value has changed, then it cannot.

Therefore in general, it cannot.

And as I argued before : that shouldn't be a problem because if you
accept that databases are merely collections of assertions of fact,
then there should never be any need to actually do such matching.

To which I should add that, since this is a forum where database
therory, thus DBMS technology, is discussed, it is indeed valid to say
that such a mapping does not exist, because its existence does not
make any difference to the DBMS anyhow.

On Sep 4, 6:10*am, Erwin <e.sm... (AT) myonline (DOT) be> wrote:
I do not claim to the contrary. I claim that it can be determined
whether t1 and t2 refer to the same thing at different times or to
different things at different times. That distinction is critical to
the definition of transition constraints. Again, unlike abstract
objects that are independent of time, concrete objects have lifetimes,
so what t1 refers to at one time may be different than what t1 refers
to at another, and what t1 refers to at one time may be what t2 refers
to at another. The transition marks the boundary between the interval
during which the referent of t1 exemplifies the relvar's predicate and
the interval during which the referent of t2 does. If the transition
is UPDATE, then the thing that both t1 and t2 refer to has a lifetime
that spans both intervals; if the transition is DELETE-then-INSERT,
then the thing that t1 refers to has a lifetime that ends at the point
in time of the transition, and the thing that t2 refers to has a
lifetime that begins at the point in time of the transition.

The CWA has absolutely nothing to do with whether or not some tuple
should or should not appear in some relvar. The CWA applies to how
the tuples that don't appear in a relvar should be interpreted: false,
or unknown.