RM VERY STRONG SUGGESTION 4: TRANSITION CONSTRAINTS

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

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

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:D, 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:D, 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:D, 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:D, 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:D, 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. Received on Fri Sep 03 2010 - 14:51:45 CEST