Re: The fable of DEMETRIUS, CONSTRAINTICUS, and AUTOMATICUS

"Tony Andrews" <andrewst_at_onetel.com> wrote:

>Kenneth Downs wrote:
>> I have just made a post where I show how to handle all of Tony's
>examples
>> structurally. Can you think of any that are even tougher? I would
>be
>> happy for the exercise in finding the limits of my hypothesis.
>
>How about this kind of real example from my memory of a timesheet
>system I once worked on.
>
>create table emp (empno primary key, ...)
>
>create table project (projno primary key, ...)
>
>create table emp_proj_assignment (empno references emp,
>start_date, end_date, projno references project,
>billable_flag varchar2(1) check (billable_flag in ('Y','N')
>primary key (empno, start_date));
>
>create table task (taskno primary key,
>billable_flag varchar2(1) check (billable_flag in ('Y','N'));
>
>create table timesheet_line
>(empno references emp,
>ts_date date,
>taskno references task
>hours, ...);
>
>The complex rule is:
>An employee may only book time (via a timesheet_line) to a task on a
>project to which the employee is assigned on ts_date, and where the
>billable_flag on the emp_proj_assignment matches that of the task.
>(The trouble with complex rule examples is they are so, well, complex!)

     I started a try on this, but:

1. You have no relationship between project and task. I assumed a 1:N relationship.
2. The PK for emp_proj_assignment should include projno! I assumed that. If the

          exists
           (
           select * from emp_proj_assignment as epa
           where
            timesheet_line.projno=epa.projno and
            timesheet_line.emp_no=epa.empno
           )
          and
          billable_flag=
           (
           select billable_flag from emp_proj_assignment as epa
           where
            timesheet_line.projno=epa.projno and
            timesheet_line.emp_no=epa.empno and
            ts_date between epa.start_date and epa.end_date
           )

     I assume short-circuit evaluation between the two subqueries.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:

     I have preferences.
     You have biases.
     He/She has prejudices.