images

In this appendix, we deliver the formal specification of the example database design used throughout this book.

The section “Bird’s Eye Overview” provides a bird’s-eye overview of the example database; it shows a picture of the ten tables with their relationships, and it provides brief informal descriptions of those tables and relationships.

We then present you with a definition of the database skeleton DB_S. In the section “Database Skeleton DB_S” you can find the involved attributes for each table, including a brief description for each attribute.

The biggest section of this appendix is the section “Table Universe Definitions,” providing the ten table universe definitions. For each table, you’ll find the table universe specification that formally defines all attribute, tuple, and table constraints that are applicable for that table, alongside the external predicate (describing the meaning of the table to the user).

The appendix ends with the definition of the static database constraints through a specification of the database universe DB_UEX in the section “Database Universe DB_UEX,” followed by the dynamic constraints through a specification of the state transition universe TX_UEX in the section “State Transition Universe TX_UEX.” The following table summarizes the structure of this appendix and shows how the specification of the example database design is built up, starting from a skeleton all the way up to a database and state transition universe.

Table A-1. Appendix Structure

Section Specifies Description
“Database Skeleton DB_S” DB_S Lists the table and column names
“Table Universe Definitions” tab_XXX Provides the characterization, tuple universe, and table universe for each of the ten tables
“Database Characterization DBCH” DBCH Maps each table to its table universe
“Database Universe DB_UEX” DB_UEX Lists all static database constraints
“State Transition Universe TX_UEX” TX_UEX Lists all dynamic database constraints

Bird’s Eye Overview

Figure A-1 shows a diagram of the ten tables (represented by rounded-corner boxes) that make up our sample database design, and their mutual relationships (represented by arrows).

Each of these arrows indicates a subset requirement that is applicable between a pair of tables. These subset requirements indicate that some projection of the table at the beginning of the arrow should always be a subset of some projection of the table to which the arrow is pointing. The majority of these arrows represent what is often called many-to-one relationships, and will eventually end up as foreign key constraints during the implementation phase. However, this is not always the case, as you have seen in Chapter 11.

We’ll give the exact meaning of each arrow in the database universe specification DB_UEX in the fifth section of this appendix.

Our database holds employees (EMP) and departments (DEPT) of a company. Some of the arrows indicate the following:

  • An employee is working for a department.
  • A department is managed by an employee.
  • An employee is assigned to a salary grade (GRD).

Employee history (HIST) records are maintained for all salary and/or “works-for-department” changes; every history record describes a period during which one employee was assigned to one department with a specific salary.

We hold additional information for all sales representatives in a separate table (SREP). We hold additional information for employees who no longer work for the company (that is, they have been terminated or they resigned) in a table TERM. We hold additional information for all managed employees (MEMP); that is, employees who have a manager assigned to them.

The database further holds information about courses (CRS), offerings (OFFR) of those courses, and registrations (REG) for those course offerings. Some more arrows show the following:

  • An offering must be taught by a trainer who works for the company.
  • An offering is of an existing course.
  • A registration records one employee as an attendee for one course offering.

images

Figure A-1. Picture of example database

Database Skeleton DB_S

In this section, you’ll find a specification of the skeleton DB_S for the sample database.

A database skeleton defines our vocabulary; for each table we introduce a table alias, and for each table we introduce the names of the involved attributes for that table. We won’t give the external predicates for the tables here; you can find these in the definition of the table universes in the next section of this appendix.

A database skeleton is a set-valued function; for each table this function yields the set of attributes (heading) of that table. Our database skeleton DB_S for the sample database is defined in Listing A-1.

Listing A-1. Database Skeleton Definition

DB_S = { (EMP;                   -- Employees
                { EMPNO          /* Employee number               */
                , ENAME          /* Employee name                 */
                , JOB            /* Employee job                  */
                , BORN           /* Date of birth                 */
                , HIRED          /* Date hired                    */
                , SGRADE         /* Salary grade                  */
                , MSAL           /* Monthly salary                */
                , USERNAME       /* Username                      */
                , DEPTNO   } )   /* Department number             */
       , (SREP;                  -- Sales Representatives
                { EMPNO          /* Employee number               */
                , TARGET         /* Sales target                  */
                , COMM     } )   /* Commission                    */
       , (MEMP;                  -- Managed Employees
                { EMPNO          /* Employee number               */
                , MGR      } )   /* Manager: employee number      */
       , (TERM;                  -- Terminated Employees
                { EMPNO          /* Employee number               */
                , LEFT           /* Date of leave                 */
                , COMMENTS } )   /* Termination comments          */
       , (DEPT;                  -- Departments
                { DEPTNO         /* Department number             */
                , DNAME          /* Department name               */
                , LOC            /* Location                      */
                , MGR      } )   /* Manager: employee number      */
       , (GRD;                   -- Salary Grades
                { GRADE          /* Grade code                    */
                , LLIMIT         /* Lower salary limit            */
                , ULIMIT         /* Upper salary limit            */
                , BONUS    } )   /* Yearly bonus                  */
       , (CRS;                   -- Courses
                { CODE           /* Course code                   */
                , DESCR          /* Course description            */
                , CAT            /* Course category               */

                , DUR      } )   /* Duration of course in days    */
       , (OFFR;                  -- Course Offerings
                { COURSE         /* Code of course                */
                , STARTS         /* Begin date of this offering   */
                , STATUS         /* Scheduled, confirmed, ...     */
                , MAXCAP         /* Max participants capacity     */
                , TRAINER        /* Trainer: employee number      */
                , LOC      } )   /* Location                      */
       , (REG;                   -- Course Registrations
                { STUD           /* Student: employee number      */
                , COURSE         /* Course code                   */
                , STARTS         /* Begin date course offering    */
                , EVAL     } )   /* Evaluation                    */
       , (HIST;                  -- Employee History Records
                { EMPNO          /* Employee number               */
                , UNTIL          /* History record end date       */
                , DEPTNO         /* Department number             */
                , MSAL     } ) } /* Monthly salary                */

Table Universe Definitions

This section provides formal definitions of the ten table universes of our sample database. For each table, you’ll find four subsections:

  • The external predicate for the table, describing the meaning of the attributes of the table to the database users.
  • The characterization, attaching attribute-value sets to each attribute.
    The naming convention is chr_<table alias>.
  • The tuple universe, defining the tuple constraints (if any) for the table.
    The naming convention is tup_<table alias>.
  • The table universe, defining the table constraints for the table.
    The naming convention is tab_<table alias>.

imagesNote The tuple universe specifications build on the characterization specifications; the table universe specifications, in turn, build on the tuple universe specifications.

Chapter 7 explained the following:

  • An attribute-value set acts as the data type for the corresponding attribute.
  • A tuple universe acts as the data type for a tuple variable of the table at hand.
  • A table universe acts as the data type for a corresponding table variable.

You’ll notice that the attribute-value sets are always defined by first drawing values from the base data types that are available in current SQL database management systems (that is, NUMBER, VARCHAR, and DATE) and then narrowing down these sets by specifying attribute constraints. In doing so, the attribute constraint expression to implement with the SQL CHECK clause is made explicit.

Where deemed necessary, you’ll find embedded comments (/* ... */) explaining the formal definition.

All tuple, table, database, and dynamic constraints are sequentially numbered for easy reference.

Some Convenient Sets

In our database definition, four sets occur frequently: employee numbers, department numbers, salary-related amounts, and course codes. Therefore, we define them here so we can refer to them by name. You could consider them user-defined data types:

EMPNO_TYP   = { n | n∈number(4,0) ∧ n > 999      }
DEPTNO_TYP  = { n | n∈number(2,0) ∧ n > 0        }
SALARY_TYP  = { n | n∈number(7,2) ∧ n > 0        }
CRSCODE_TYP = { s | s∈varchar(6)  ∧ s = upper(s) }

Table Universe for EMP

External Predicate: The employee with employee number EMPNO has name ENAME, job JOB, was born at BORN, is hired at HIRED, has a monthly salary of MSAL dollars within the SGRADE salary grade, is assigned to account USERNAME, and works for the department with department number DEPTNO.

The following three listings (A-2, A-3, and A-4) show the attribute-value sets, the tuple universe, and the table universe for EMP, respectively.

Listing A-2. Characterization chr_EMP

chr_EMP :=
{ ( EMPNO;  EMPNO_TYP                               )
, ( ENAME;  varchar(9)                              )
, ( JOB;    { s | s∈varchar(8) ∧
                  s∈{'PRESIDENT','MANAGER'
                     ,'SALESREP','TRAINER','ADMIN'} )
, ( BORN;   date                                    )
 , ( HIRED;  date                                    )
, ( SGRADE; { n | n∈number(2,0) ∧ n > 0 }          )
, ( MSAL;   SALARY_TYP                              )
, ( USERNAME; { s | s∈varchar(15) ∧
                    upper(USERNAME) = USERNAME }    )
, ( DEPTNO; DEPTNO_TYP                              )
}

Listing A-3. Tuple Universe tup_EMP

tup_EMP :=
{ e | e∈Π(chr_EMP) ∧
      /* We hire adult employees only                                   r1 */
      e(BORN) + 18 ≤ e(HIRED)
      ∧
      /* A president earns more than 10K monthly                        r2 */
      e(JOB) = 'PRESIDENT' ⇒ e(MSAL) > 10000
      Ÿ
      /* Administrators earn less than 5K monthly                       r3 */
      e(JOB) = 'ADMIN' ⇒ e(MSAL) < 5000
}

Listing A-4. Table Universe tab_EMP

tab_EMP :=
{ E | E∈℘ (tup_EMP) ∧
      /* EMPNO uniquely identifies an employee tuple                    r4 */
      ( ∀e1,e2∈E: e1(EMPNO) = e2(EMPNO) ⇒ e1 = e2 )
      ∧
      /* USERNAME uniquely identifies an employee tuple                 r4 */
      ( ∀e1,e2∈E: e1(USERNAME) = e2(USERNAME) ⇒ e1 = e2 )
      ∧
      /* At most one president allowed                                  r5 */
      #{ e | e∈E ∧ e(JOB) = 'PRESIDENT' } ≤ 1
      ∧
      /* A department that employs the president or a manager           r6 */
      /* should also employ at least one administrator                     */
      ( ∀d∈{ e1(DEPTNO) | e1∈E }:
        ( ∃e2∈E: e2(DEPTNO) = d ∧ e2(JOB) ∈ {'PRESIDENT','MANAGER'} )
          ⇒
        ( ∃e2∈E: e2(DEPTNO) = d ∧ e2(JOB) = 'ADMIN' )
      )
}

Table Universe for SREP

External Predicate: The sales representative with employee number EMPNO has an annual sales target of TARGET dollars and a yearly commission of COMM dollars.

The following three listings (A-5, A-6, and A-7) show the attribute-value sets, the tuple universe, and the table universe for SREP, respectively.

Listing A-5. Characterization chr_SREP

chr_SREP :=
{ ( EMPNO;  EMPNO_TYP                        )
, ( TARGET; { n | n∈number(5,0) ∧ n > 9999 } )
, ( COMM;   SALARY_TYP                       )
}

Listing A-6. Tuple Universe tup_SREP

tup_SREP :=
{ s | s∈Π(chr_SREP) }

Listing A-7. Table Universe tab_SREP

tab_SREP :=
{ S | S∈℘(tup_SREP) ∧
      /* EMPNO uniquely identifies a tuple                              r7 */
      ( ∀s1,s2∈S: s1(EMPNO) = s2(EMPNO) ⇒ s1 = s2 )
}

Table Universe for MEMP

External Predicate: The employee with employee number EMPNO is managed by the employee with employee number MGR.

The following three listings (A-8, A-9, and A-10) show the attribute-value sets, the tuple universe, and the table universe for MEMP, respectively.

Listing A-8. Characterization chr_MEMP

chr_MEMP :=
{ ( EMPNO;  EMPNO_TYP                  )
, ( MGR;    EMPNO_TYP                  )
}

Listing A-9. Tuple Universe tup_MEMP

tup_MEMP :=
{ m | m∈Π(chr_MEMP) ∧
      /* You cannot manage yourself                                     r8 */
      m(EMPNO) ≠ m(MGR)
}

Listing A-10. Table Universe tab_MEMP

tab_MEMP :=
{ M | M∈℘(tup_MEMP) ∧
      /* EMPNO uniquely identifies a tuple                              r9 */
      ( ∀m1,m2∈S: m1(EMPNO) = m2(EMPNO) ⇒ m1 = m2 )
}

Table Universe for TERM

External Predicate: The employee with number EMPNO has resigned or was fired at date LEFT due to reason COMMENTS.

The following three listings (A-11, A-12, and A-13) show the attribute-value sets, the tuple universe, and the table universe for TERM, respectively.

Listing A-11. Characterization chr_TERM

chr_TERM :=
{ ( EMPNO;    EMPNO_TYP   )
, ( LEFT;     date        )
, ( COMMENTS; varchar(60) )
}

Listing A-12. Tuple Universe tup_TERM

tup_TERM :=
{ t | t∈Π(chr_TERM) }

Listing A-13. Table Universe tab_TERM

tab_TERM :=
{ T | T∈℘(tup_TERM) ∧
      /* EMPNO uniquely identifies a tuple                              r10 */
      ( ∀t1,t2∈T: t1(EMPNO) = t2(EMPNO) ⇒ t1 = t2 )
}

Table Universe for DEPT

External Predicate: The department with department number DEPTNO has name DNAME, is located at LOC, and is managed by the employee with employee number MGR.

The following three listings (A-14, A-15, and A-16) show the attribute-value sets, the tuple universe, and the table universe for DEPT, respectively.

Listing A-14. Characterization chr_DEPT

chr_DEPT :=
{ ( DEPTNO; DEPTNO_TYP                                   )
, ( DNAME;  { s | s∈varchar(12) ∧ upper(DNAME) = DNAME } )
, ( LOC;    { s | s∈varchar(14) ∧ upper(LOC) = LOC }      )
, ( MGR;    EMPNO_TYP                                     )
}

Listing A-15. Tuple Universe tup_DEPT

tup_DEPT :=
{ d | d∈Π(chr_DEPT) }

Listing A-16. Table Universe tab_DEPT

tab_DEPT :=
{ D | D∈℘(tab_DEPT) ∧
      /* Department number uniquely identifies a tuple                  r11 */
      ( ∀d1,d2∈D: d1(DEPTNO) = d2(DEPTNO) ⇒ d1 = d2 )
      ∧
      /* Department name and location uniquely identify a tuple         r12 */
      ( ∀d1,d2∈D:
        d1↓{DNAME,LOC} = d2↓{DNAME,LOC} ⇒ d1 = d2 )
      ∧
      /* You cannot manage more than two departments                    r13 */
      ( ∀m∈{ d(MGR) | d∈D }: #{ d | d∈D ∧ d(MGR) = m } ≤ 2 )
}

Table Universe for GRD

External Predicate: The salary grade with ID GRADE has a lower monthly salary limit of LLIMIT dollars, an upper monthly salary limit of ULIMIT dollars, and a maximum net monthly bonus of BONUS dollars.

The following three listings (A-17, A-18, and A-19) show the attribute-value sets, the tuple universe, and the table universe for GRD, respectively.

Listing A-17. Characterization chr_GRD

chr_GRD :=
{ ( GRADE;  { n | n∈ number(2,0) ∧ n > 0 } )
, ( LLIMIT; SALARY_TYP                     )
, ( ULIMIT; SALARY_TYP                     )
, ( BONUS;  SALARY_TYP                     )
}

Listing A-18. Tuple Universe tup_GRD

tup_GRD :=
{ g | g∈Π(chr_GRD) ∧
      /* Salary grades have a bandwidth of at least 500 dollars         r14 */
      g(LLIMIT) ≤ g(ULIMIT) - 500
      ∧
      /* Bonus must be less than lower limit                            r15 */
      g(BONUS) < g(LLIMIT)
}

Listing A-19. Table Universe tab_GRD

tab_GRD :=
{ G | G∈℘(tup_GRD) ∧
      /* Salary grade code uniquely identifies a tuple                  r16 */
      ( ∀g1,g2∈G: g1(GRADE) = g2(GRADE) ⇒ g1 = g2 )
      ∧
      /* Salary grade lower limit uniquely identifies a tuple           r17 */
      ( ∀g1,g2∈G: g1(LLIMIT) = g2(LLIMIT) ⇒ g1 = g2 )
      ∧
      /* Salary grade upper limit uniquely identifies a tuple           r18 */
      ( ∀g1,g2∈G: g1(ULIMIT) = g2(ULIMIT) ⇒ g1 = g2 )
      /* A salary grade overlaps with at most one (lower) grade         r20 */
      ( ∀g1∈G:
        ( ∃g2∈G: g2(LLIMIT) < g1(LLIMIT) )
          ⇒
        #{ g3 | g3∈G ∧ g3(LLIMIT) < g1(LLIMIT) ∧
                        g3(ULIMIT) ≥ g1(LLIMIT) ∧
                        g3(ULIMIT) < g1(ULIMIT) } = 1
      )
}

Table Universe for CRS

External Predicate: The course with code CODE has description DESCR, falls in course category CAT, and has duration of DUR days.

The following three listings (A-20, A-21, and A-22) show the attribute-value sets, the tuple universe, and the table universe for CRS, respectively.

Listing A-20. Characterization chr_CRS

chr_CRS :=
{ ( CODE;  CRSCODE_TYP                        )
, ( DESCR; varchar(40)                        )
           /* Course category values: Design, Generate, Build               */
, ( CAT;   { s | s∈varchar(3) ∧
                 s∈{'DSG','GEN','BLD'} }      )
           /* Course duration must be between 1 and 15 days                 */
, ( DUR;   { n | n∈number(2,0) ∧ 1 ≤ n ≤ 15 } )
}

Listing A-21. Tuple Universe tup_CRS

tup_CRS :=
{ c | c∈Π(chr_CRS) ∧
      /* Build courses never take more than 5 days                      r21 */
      c(CAT) = 'BLD' ⇒ t(DUR) ≤ 5
}

Listing A-22. Table Universe tab_CRS

tab_CRS :=
{ C | C∈℘(tup_CRS) ∧
      /* Course code uniquely identifies a tuple                        r22 */
      ( ∀c1,c2∈C: c1(CODE) = c2(CODE) ⇒ c1 = c2 )
}

Table Universe for OFFR

External Predicate: The course offering for the course with code COURSE that starts at STARTS, has status STATUS, has a maximum capacity of MAXCAP attendees, is taught by the employee with employee number TRAINER, and is offered at location LOC.

The following three listings (A-23, A-24, and A-25) show the attribute-value sets, the tuple universe, and the table universe for OFFR, respectively.

Listing A-23. Characterization chr_OFFR

chr_OFFR :=
{ ( COURSE; CRSCODE_TYP                     )
, ( STARTS; date                            )
, ( STATUS; { s | s∈varchar(4) ∧
            /* Three STATUS values allowed:                                 */
                  s∈{'SCHD','CONF','CANC'} } )
            /* Maximum course offering capacity; minimum = 6                */
, ( MAXCAP; { n | n∈number(2,0) ∧ n ≥ 6 }  )
            /* TRAINER = -1 means "no trainer assigned" (see r23)           */
, ( TRAINER; EMPNO_TYP ∪ { -1 }            )
, ( LOC;     varchar(14)                   )
}

Listing A-24. Tuple Universe tup_OFFR

tup_OFFR :=
{ o | o∈Π(chr_OFFR) ∧
      /* Unassigned TRAINER allowed only for certain STATUS values      r23 */
      o(TRAINER) = -1  ⇒ o(STATUS)∈{'CANC','SCHD'}
}

Listing A-25. Table Universe tab_OFFR

tab_OFFR :=
{ O | O∈℘(tup_OFFR) ∧
      /* Course code and begin date uniquely identify a tuple           r24 */
      ( ∀o1,o2∈O:
        o1↓{COURSE,STARTS} = o2↓{COURSE,STARTS} ⇒ o1 = o2 )
      ∧
      /* Begin date and (known) trainer uniquely identify a tuple       r25 */
      ( ∀o1,o2∈{ o | o∈O ∧ o(TRAINER) ≠ -1 }:
        o1↓{STARTS,TRAINER} = o2↓{STARTS,TRAINER} ⇒ o1 = o2 )
}

Table Universe for REG

External Predicate: The employee whose employee number is STUD has registered for the course offering of course COURSE that starts at STARTS, and has rated the course with an evaluation score of EVAL.

The following three listings (A-26, A-27, and A-28) show the attribute-value sets, the tuple universe, and the table universe for REG, respectively.

Listing A-26. Characterization chr_REG

chr_REG :=
{ ( STUD;   EMPNO_TYP             )
, ( COURSE; CRSCODE_TYP           )
, ( STARTS; date                  )
  /* -1: too early to evaluate; 0: not (yet) evaluated;                     */
  /* 1-5: regular evaluation values (from 1=bad to 5=excellent)             */
, ( EVAL;   { n | n∈number(1,0)
                  ∧ -1 ≤ n ≤ 5  } )
}

Listing A-27. Tuple Universe tup_REG

tup_REG :=
{ r | r∈Π(chr_REG) }

Listing A-28. Table Universe tab_REG

tab_REG :=
{ R | R∈℘(tup_REG) ∧
      /* Attendee and begin date(!) uniquely identify a tuple           r26 */
      ( ∀r1,r2∈R:
        r1↓{STUD,STARTS} = r2↓{STUD,STARTS} ⇒ r1 = r2 )
      ∧
      /* Offering is evaluated,                                             */
      /* or it is too early to evaluate the offering                    r27 */
      ( ∀r1,r2∈R:
        ( r1↓{COURSE,STARTS} = r2↓{COURSE,STARTS} )
          ⇒
        ( ( r1(EVAL) = -1 ∧ r2(EVAL) = -1 ) ∨
          ( r1(EVAL) ≠ -1 ∧ r2(EVAL) ≠ -1 )
      ) )
}

Table Universe for HIST

External Predicate: At date UNTIL, for employee whose employee number is EMPNO, either the department or the monthly salary (or both) have changed. Prior to date UNTIL, the department for that employee was DEPTNO and the monthly salary was MSAL.

The following three listings (A-29, A-30, and A-31) show the attribute-value sets, the tuple universe, and the table universe for HIST, respectively.

Listing A-29. Characterization chr_HIST

chr_HIST :=
{( EMPNO;  EMPNO_TYP  )
,( UNTIL;  date       )
,( DEPTNO; DEPTNO_TYP )
,( MSAL;   SALARY_TYP )
}

Listing A-30. Tuple Universe tup_HIST

tup_HIST :=
{ h | h∈Π(chr_HIST) }

Listing A-31. Table Universe tab_HIST

tab_HIST :=
{ H | H∈℘(tup_HIST) ∧
      /* Employee number and end date uniquely identify a tuple         r28 */
      ( ∀h1,h2∈H: h1↓{EMPNO,UNTIL} = h2↓{EMPNO,UNTIL} ⇒ h1 = h2 )
      ∧
      /* Either department number or monthly salary (or both)           r29 */
      /* must have changed between two consecutive history records          */
      ( ∀h1,h2∈H:
        ( h1(EMPNO) = h2(EMPNO) ∧
          h1(UNTIL) < h2(UNTIL) ∧
          ¬ ( ∃h3∈T:  h3(EMPNO) = h1(EMPNO) ∧
                      h3(UNTIL) > h1(UNTIL) ∧
                      h3(UNTIL) < h2(UNTIL) )
        ) ⇒
        ( h1(MSAL) ≠ h2(MSAL) ∨ h1(DEPTNO) ≠ h2(DEPTNO) )
      )
}

Database Characterization DBCH

The database characterization DBCH attaches the ten table universes (as defined in the previous section of this appendix) to their corresponding table aliases; see Listing A-32. As such, the database characterization “revisits” the database skeleton and provides many more details.

Listing A-32. Database Characterization DBCH

DBCH :=
{ ( EMP;  tab_EMP  )
, ( SREP; tab_SREP )
 , ( MEMP; tab_MEMP )
, ( TERM; tab_TERM )
, ( DEPT; tab_DEPT )
, ( GRD;  tab_GRD  )
, ( CRS;  tab_CRS  )
, ( OFFR; tab_OFFR )
, ( REG;  tab_REG  )
, ( HIST; tab_HIST )
}

Database Universe DB_UEX

As you can see in Listing A-33, the database universe DB_UEX is built on top of the database characterization DBCH. The specification of DB_UEX contains all static database constraints.

Listing A-33. Database Universe DB_UEX

DB_UEX :=
{ v | v∈Π(DBCH) ∧
   /* ====================================================================  */
   /* Start of Subset Requirements                                          */
   /* ====================================================================  */
   /* Employee works for a known department                             r30 */
   { e(DEPTNO) | e∈v(EMP) } ⊆ { d(DEPTNO) | d∈v(DEPT) }
   ∧
   /* Dept mgr is a known employee, excluding admins and president      r31 */
   { d(MGR)   | d∈v(DEPT) } ⊆
   { e(EMPNO) | e∈v(EMP) ∧ e(JOB) ∉ {'ADMIN','PRESIDENT'} }
   ∧
   /* Employees can report to the president or a manager only           r32 */
   { m(MGR)   | m∈v(MEMP) } ⊆
   { e(EMPNO) | e∈v(EMP) ∧ e(JOB) ∈ {'PRESIDENT','MANAGER' } }
   ∧
   /* A termination is for a known employee; not everyone has left      r33 */
   { t(EMPNO) | t∈v(TERM) } ⊂ { e(EMPNO) | e∈v(EMP) }
   ∧
   /* Employee has a known salary grade                                 r34 */
   { e(SGRADE) | e∈v(EMP) } ⊆ { g(GRADE) | g∈v(GRD) }
   ∧
   /* Course offering is for a known course                             r35 */
   { o(COURSE) | o∈v(OFFR) } ⊆ { c(CODE) | c∈v(CRS) }
   ∧
   /* Courses take place in locations where we have a department        r36 */
   { o(LOC) | o∈v(OFFR) } ⊆ { d(LOC) | d∈v(DEPT) }
   ∧
   /* Trainer of course offering is a known trainer                     r37 */
    { o(TRAINER) | o∈v(OFFR) ∧ o(TRAINER) ≠ -1 } ⊆
   { e(EMPNO) | e∈v(EMP) ∧ e(JOB) = 'TRAINER' }
   ∧
   /* Course registration is for a known employee                       r38 */
   { r(STUD) | r∈v(REG) } ⊆ { e(EMPNO) | e∈v(EMP) }
   ∧
   /* Course registration is for a known course offering                r39 */
   { r↓{COURSE,STARTS} | r∈v(REG) } ⊆
   { o↓{COURSE,STARTS} | o∈v(OFFR) }
   ∧
   /* History record is for a known employee                            r40 */
   { h(EMPNO) | h∈v(HIST) } ⊆ { e(EMPNO)} | e∈v(EMP) }
   ∧
   /* History record is for a known department                          r41 */
   { h(DEPTNO) | h∈v(HIST) } ⊆ { d(DEPTNO)} | d∈v(DEPT) }
   ∧
   /* ====================================================================  */
   /* End of Subset Requirements; Start of Specialization Rules             */
   /* ====================================================================  */
   /* Sales reps have a target and a commission                         r42 */
   { e(EMPNO) | e∈v(EMP) ∧ e(JOB) = 'SALESREP' } =
   { s(EMPNO) | s∈v(SREP) }
   ∧
   /* Everybody, excluding the president, is a managed employee         r43 */
   { e(EMPNO) | e∈v(EMP) ∧ e(JOB) ≠ 'PRESIDENT' } =
   { m(EMPNO) | m∈v(MEMP) }
   ∧
   /* ====================================================================  */
   /* End of Specializations; Start of Tuple-in-Join Rules                  */
   /* ====================================================================  */
   /* Monthly salary must fall within assigned salary grade             r44 */
   ( ∀e∈v(EMP), g∈v(GRD):
       e(SGRADE) = g(GRADE) ⇒ g(LLIMIT) ≤ e(MSAL) ≤ g(ULIMIT)
   ) ∧
   /* Leave date must fall after hire date                              r45 */
   ( ∀e∈v(EMP), t∈v(TERM):
       e(EMPNO) = t(EMPNO) ⇒ e(HIRED) < t(LEFT)
   ) ∧
   /* Sales reps cannot earn more than the employee they report to      r46 */
   ( ∀s∈v(SREP), es,em∈v(EMP), m∈v(MEMP):
     ( s(EMPNO)=es(EMPNO) ∧ es(EMPNO)=m(EMPNO)∧ m(MGR) = em(EMPNO))
       ⇒
     ( es(MSAL) + s(COMM)/12 < em(MSAL)     )
   ) ∧
   /* Non-sales reps cannot earn more than the employee they report to  r47 */
   ( ∀e,em∈v(EMP), m∈v(MEMP):
     (e(EMPNO)=m(EMPNO) ∧ m(MGR) = em(EMPNO) ∧ e(JOB) ≠ 'SALESREP')
        ⇒
     ( e(MSAL) < em(MSAL)      )
   ) ∧
   /* No history records allowed before hire date                       r48 */
   ( ∀e∈v(EMP), h∈v(HIST):
       e(EMPNO) = h(EMPNO) ⇒ e(HIRED) < h(UNTIL)
   ) ∧
   /* No history records allowed after leave date                       r49 */
   ( ∀t∈v(TERM), h∈v(HIST):
       t(EMPNO) = h(EMPNO) ⇒ t(LEFT) > h(UNTIL)
   ) ∧
   /* You cannot register for offerings in 1st four weeks on the job    r50 */
   ( ∀e∈v(EMP), r∈v(REG):
       e(EMPNO) = r(STUD) ⇒ e(HIRED) + 28 ≤ r(STARTS)
   ) ∧
   /* You cannot register for offerings given at or after leave date    r51 */
   ( ∀t∈v(TERM), r∈v(REG), c∈v(CRS):
     ( t(EMPNO) = r(STUD) ∧ r(COURSE) = c(CODE) )
       ⇒
     ( t(LEFT) ≥ r(STARTS) + c(DUR)             )
   ) ∧
   /* You cannot register for overlapping course offerings              r52 */
   ( ∀e∈v(EMP), r1,r2∈v(REG), o1,o2∈v(OFFR), c1,c2∈v(CRS):
     ( e(EMPNO) = r1(STUD)                     ∧
       r1↓{COURSE,STARTS} = o1↓{COURSE,STARTS} ∧
       o1(COURSE)         = c1(COURSE)         ∧
       e(EMPNO)           = r2(STUD)           ∧
       r2↓{COURSE,STARTS} = o2↓{COURSE,STARTS} ∧
       o2(COURSE) = c2(COURSE)
     ) ⇒
     ( o1↓{COURSE,STARTS} = o2↓{COURSE,STARTS} ∨
       o1(STARTS) ≥ o2(STARTS) + c2(DUR)       ∨
       o2(STARTS) ≥ o1(STARTS) + c1(DUR)
   ) ) ∧
   /* Trainer cannot teach courses before hire date                     r53 */
   ( ∀e∈v(EMP), o∈v(OFFR):
       e(EMPNO) = o(TRAINER) ⇒ e(HIRED) ≤ o(STARTS)
   ) ∧
   /* Trainer cannot teach courses at or after leave date               r54 */
   ( ∀t∈v(TERM), o∈v(OFFR), c∈v(CRS):
     ( t(EMPNO) = o(TRAINER) ∧ o(COURSE) = c(CODE) )
       ⇒
     ( t(LEFT) ≥ o(STARTS) + c(DUR)                )
   ) ∧
   /* Trainer cannot register for offerings taught by him/herself       r55 */
   ( ∀r∈v(REG), o∈v(OFFR):
       r↓{COURSE,STARTS} = o↓{COURSE,STARTS} ⇒
        r(STUD) ≠ o(TRAINER)
   ) ∧
   /* Trainer cannot teach different courses simultaneously             r56 */
   ( ∀o1,o2∈v(OFFR), c1,c2∈v(CRS):
     ( o1(TRAINER) = o2(TRAINER) ∧
       o1(COURSE)  = c1(CODE)    ∧
       o2(COURSE)  = c2(CODE)
     ) ⇒
     ( o1↓{COURSE,STARTS} = o2↓{COURSE,STARTS} ∨
       o1(STARTS) ≥ o2(STARTS) + c2(DUR)       ∨
       o2(STARTS) ≥ o1(STARTS) + c1(DUR)
   ) ) ∧
   /* Employee cannot register for course offerings that overlap        r57 */
   /* with another course offering where he/she is the trainer              */
   ( ∀e∈v(EMP), r∈v(REG), o1,o2∈v(OFFR), c1,c2∈v(CRS):
     ( e(EMPNO) = r(STUD)                      ∧
       r↓{COURSE,STARTS} = o1↓{COURSE,STARTS}  ∧
       o1(COURSE) = c1(CODE)                   ∧
       e(EMPNO)   = o2(TRAINER)                ∧
       o2(COURSE) = c2(CODE)
     ) ⇒
     ( o1↓{COURSE,STARTS} = o2↓{COURSE,STARTS} ∨
       o1(STARTS) ≥ o2(STARTS) + c2(DUR)       ∨
       o2(STARTS) ≥ o1(STARTS) + c1(DUR)
   ) ) ∧
   /* ====================================================================  */
   /* End of Tuple-in-Join Rules; Start of Other Database Rules             */
   /* ====================================================================  */
   /* Department manager must work for a department he/she manages      r58 */
   ( ∀d1∈v(DEPT): { e(DEPTNO)| e∈v(EMP) ∧ e(EMPNO)=d1(MGR)} ⊆
                   { d2(DEPTNO)| d2∈v(DEPT) ∧ d2(mgr)=d1(mgr) } )
   /* Active employee cannot be managed by terminated employee          r59 */
   { t1(EMPNO) | t1∈v(TERM) } ∩
   { m(MGR) | m∈v(MEMP) ∧
              ¬ ( ∃t2∈v(TERM): t2(EMPNO) = m(EMPNO) ) } = ∅
   ∧
   /* Department cannot be managed by a terminated employee             r60 */
   { t(EMPNO) | t∈v(TERM) } ∩ { d(MGR) | d∈v(DEPT) } = ∅
   ∧
   /* At least half of the course offerings (measured by duration)      r61 */
   /* taught by a trainer must be 'at home base'                            */
   ( ∀e1∈{ o1(TRAINER) | o1∈v(OFFR) ∧ o1(STATUS) ≠ 'CANC' }:
     ( Σt∈{ o2∪c2| d2∈v(DEPT) ∧ e2∈v(EMP) ∧ o2∈v(OFFR) ∧ c2∈v(CRS) ∧
                    e2(EMPNO)  = e1          ∧
                    e2(EMPNO)  = o2(TRAINER) ∧
                    e2(DEPTNO) = d2(DEPTNO)  ∧
                    o2(COURSE) = c2(CODE)    ∧
                     o2(STATUS) ≠ 'CANC'      ∧
                    c2(LOC)    = d2(LOC)
          } : t(DUR)
     ) ≥
     ( Σt∈{ o3∪c3| d3∈v(DEPT) ∧ e3∈v(EMP) ∧ o3∈v(OFFR) ∧ c3∈v(CRS) ∧
                    e3(EMPNO)  = e1          ∧
                    e3(EMPNO)  = o3(TRAINER) ∧
                    e3(DEPTNO) = d3(DEPTNO)  ∧
                    o3(COURSE) = c3(CODE)    ∧
                    o3(STATUS) ≠ 'CANC'      ∧
                    c3(LOC)    ≠ d3(LOC)
           } : t(DUR)
   ) ) ∧
   /* Offerings with 6+ registrations must have status confirmed        r62 */
   ( ∀o∈v(OFFR):
     #{ r | r∈v(REG) ∧
            r↓{COURSE,STARTS} = o↓{COURSE,STARTS} } ≥ 6
     ⇒
     o(STATUS) = 'CONF'
   ) ∧
   /* Number of registrations cannot exceed maximum capacity of offering r63 */
   ( ∀o∈v(OFFR):
     #{ r | r∈v(REG) ∧
            r↓{COURSE,STARTS} = o↓{COURSE,STARTS} } ≤ o(MAXCAP)
   ) ∧
   /* Canceled offerings cannot have registrations                      r64 */
   ( ∀o∈v(OFFR): o(STATUS) = 'CANC'
                  ⇒
                  ¬( ∃r∈v(REG): r↓{COURSE,STARTS} = o↓{COURSE,STARTS} )
   ) ∧
   /* You are allowed to teach a certain course only if:                r65 */
   /* 1. You have been employed for at least one year, or                   */
   /* 2. You have attended that course first and the trainer of that        */
   /*    course offering attends your first teach as participant            */
   ( ∀o1∈v(OFFR):
     /* If this is the 1st time this trainer gives this course ...          */
     ( ¬∃o2∈v(OFFR):
        o1↓{COURSE,TRAINER} = o2↓{COURSE,TRAINER} ∧
        o2(STARTS) < o1(STARTS)
     ) ⇒
     ( /* then there should be an attendee in the classroom ...             */
       ( ∃r1∈v(REG):
           r1↓{COURSE,STARTS} = o1↓{COURSE,STARTS} ∧
           /* who has given this course at an earlier date ...              */
           ( ∃o3∈v(OFFR):
             o3(TRAINER) = r1(STUD)  ∧
             o3(COURSE) = o1(COURSE) ∧
              o3(STARTS) < o1(STARTS) ∧
     /* and *that* course was attended by the current trainer               */
             ( ∃r2∈v(REG):
               o3↓{COURSE,STARTS} = r2↓{COURSE,STARTS} ∧
               r2(STUD) = o1(TRAINER)
       ) ) ) ∨
   /* or, this trainer has been employed for at least one year              */
      ( ↵{ e(HIRED) | e∈v(EMP) ∧ e(EMPNO) = o1(TRAINER) } <
        o1(STARTS)  - 365
      )
    )
   )
   /* ====================================================================  */
   /* End of Other Database Rules                                           */
   /* ====================================================================  */
}

State Transition Universe TX_UEX

The state transition universe TX_UEX is defined by first generating the Cartesian product of the database universe DB_UEX (as defined in the section “Database Universe DB_UEX” of this appendix) with itself. You can view the result of this Cartesian product as the set of all possible transactions. In this state transition universe TX_UEX, every transaction is depicted as an ordered pair (b;e), where b stands for the database state in which the transaction began and e stands for the database state in which the transaction ends. As you can see in Listing A-34, the definition of TX_UEX restricts this set to hold only valid transactions, through specifying the dynamic (also called state transition or transaction) constraints.

imagesNote In this section we use sysdate to denote the moving point “now.”

Listing A-34. Transaction Universe TX_UEX

TX_UEX :=
{ (b;e) | b∈DB_UEX ∧ e∈DB_UEX ∧
  /* Monthly salary can only increase                                   r66 */
  ( ∀e1∈b(EMP), e2∈e(EMP):
      e1(EMPNO) = e2(EMPNO) ⇒ e1(MSAL) ≤ e2(MSAL)
  ) ∧
  /* New offerings must start with status SCHED                         r67 */
  ( ∀o1∈e(OFFR)⇓{COURSE,STARTS} − b(OFFR)⇓{COURSE,STARTS}:
     ↵{ o2(STATUS) | o2∈e(OFFR) ∧ o2↓{COURSE,STARTS} = o1↓{COURSE,STARTS} }
     = 'SCH'
   ) ∧
  /* Valid offering status transitions are:                             r68 */
  /* SCHED -> CONF, SCHED -> CANC, CONF -> CANC                             */
  ( ∀o1∈b(OFFR), o2∈e(OFFR):
    o1↓{COURSE,STARTS} = o2↓{COURSE,STARTS}
      ⇒
    ( o1(STATUS) = o2(STATUS) ∨
      ( o1(STATUS) = 'SCH'  ∧ o2(STATUS) = 'CONF' ) ∨
      ( o1(STATUS) = 'SCH'  ∧ o2(STATUS) = 'CANC' ) ∨
      ( o1(STATUS) = 'CONF' ∧ o2(STATUS) = 'CANC' )
  ) ) ∧
  /* No updates allowed to history records                              r69 */
  ( ∀h1∈b(HIST), h2∈e(HIST):
    h1↓{EMPNO,UNTIL} = h2↓{EMPNO,UNTIL}
    ⇒
    ( h1(DEPTNO) = h2(DEPTNO) ∧ h1(MSAL) = h2(MSAL) )
  ) ∧
  /* New history records must accurately reflect employee updates       r70 */
  ( ∀h1∈e(HIST)⇓{EMPNO,UNTIL} − b(HIST)⇓{EMPNO,UNTIL}:
    ( ∃h2∈e(HIST):
      h2↓{EMPNO,UNTIL} = h1↓{EMPNO,UNTIL} ∧
      h2(UNTIL) = sysdate ∧
      ( ∃e1∈b(EMP), e2∈e(EMP):
        e1↓{EMPNO,MSAL,DEPTNO} = h2↓{EMPNO,MSAL,DEPTNO} ∧
        e2(EMPNO) = h2(EMPNO) ∧
        ( e2(MSAL) ≠ e1(MSAL) ∨ e2(DEPTNO) ≠ e1(DEPTNO) )
  ) ) ) ∧
  /* New registration tuples must start with EVAL = -1                  r71 */
  ( ∀r1∈e(REG)⇓{STUD,STARTS} − b(REG)⇓{STUD,STARTS}:
    ( ∃r2∈e(REG):
      r2↓{STUD,STARTS} = r1↓{STUD,STARTS} ∧ r2(EVAL) = -1
  ) ) ∧
  /* Transitions for evaluation must be valid                           r72 */
  /* and cannot occur before start date of offering                         */
  ( ∀r1∈b(REG), r2∈e(REG):
    r1↓{STUD,STARTS} = r2↓{STUD,STARTS} ∧ r1(EVAL) ≠ r2(EVAL)
    ⇒
    ( ( r1(EVAL) = -1 ∧ r2(EVAL) = 0 ∧ r2(STARTS) ≥ sysdate ) ∨
      ( r1(EVAL) = 0 ∧ r2(EVAL) ∈{1,2,3,4,5} )
  ) )
}

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.145.109.8