Re: RM VERY STRONG SUGGESTION 4: TRANSITION CONSTRAINTS

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

On 03/09/2010 5:51 AM, Brian wrote:
> 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 - 22:50:58 CEST