RM VERY STRONG SUGGESTION 4: TRANSITION CONSTRAINTS

From: Brian <brian_at_selzer-software.com>
Date: Fri, 3 Sep 2010 05:51:45 -0700 (PDT)
Message-ID: <caa226c8-ee20-4799-af65-2e126cc2d07f_at_z28g2000yqh.googlegroups.com>



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: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 - 07:51:45 CDT

Original text of this message