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.
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 |
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:
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:
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.
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 */
This section provides formal definitions of the ten table universes of our sample database. For each table, you’ll find four subsections:
chr_<table alias>
.tup_<table alias>
.tab_<table alias>
.Note 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:
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.
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) }
External Predicate: The employee with employee number
EMPNO
has nameENAME
, jobJOB
, was born atBORN
, is hired atHIRED
, has a monthly salary ofMSAL
dollars within theSGRADE
salary grade, is assigned to accountUSERNAME
, and works for the department with department numberDEPTNO
.
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.
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 )
}
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
}
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' )
)
}
External Predicate: The sales representative with employee number
EMPNO
has an annual sales target ofTARGET
dollars and a yearly commission ofCOMM
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.
chr_SREP :=
{ ( EMPNO; EMPNO_TYP )
, ( TARGET; { n | n∈number(5,0) ∧ n > 9999 } )
, ( COMM; SALARY_TYP )
}
tup_SREP :=
{ s | s∈Π(chr_SREP) }
tab_SREP :=
{ S | S∈℘(tup_SREP) ∧
/* EMPNO uniquely identifies a tuple r7 */
( ∀s1,s2∈S: s1(EMPNO) = s2(EMPNO) ⇒ s1 = s2 )
}
External Predicate: The employee with employee number
EMPNO
is managed by the employee with employee numberMGR
.
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.
chr_MEMP :=
{ ( EMPNO; EMPNO_TYP )
, ( MGR; EMPNO_TYP )
}
tup_MEMP :=
{ m | m∈Π(chr_MEMP) ∧
/* You cannot manage yourself r8 */
m(EMPNO) ≠ m(MGR)
}
tab_MEMP :=
{ M | M∈℘(tup_MEMP) ∧
/* EMPNO uniquely identifies a tuple r9 */
( ∀m1,m2∈S: m1(EMPNO) = m2(EMPNO) ⇒ m1 = m2 )
}
External Predicate: The employee with number
EMPNO
has resigned or was fired at dateLEFT
due to reasonCOMMENTS
.
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.
chr_TERM :=
{ ( EMPNO; EMPNO_TYP )
, ( LEFT; date )
, ( COMMENTS; varchar(60) )
}
tup_TERM :=
{ t | t∈Π(chr_TERM) }
tab_TERM :=
{ T | T∈℘(tup_TERM) ∧
/* EMPNO uniquely identifies a tuple r10 */
( ∀t1,t2∈T: t1(EMPNO) = t2(EMPNO) ⇒ t1 = t2 )
}
External Predicate: The department with department number
DEPTNO
has nameDNAME
, is located atLOC
, and is managed by the employee with employee numberMGR
.
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.
chr_DEPT :=
{ ( DEPTNO; DEPTNO_TYP )
, ( DNAME; { s | s∈varchar(12) ∧ upper(DNAME) = DNAME } )
, ( LOC; { s | s∈varchar(14) ∧ upper(LOC) = LOC } )
, ( MGR; EMPNO_TYP )
}
tup_DEPT :=
{ d | d∈Π(chr_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 )
}
External Predicate: The salary grade with ID
GRADE
has a lower monthly salary limit ofLLIMIT
dollars, an upper monthly salary limit ofULIMIT
dollars, and a maximum net monthly bonus ofBONUS
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.
chr_GRD :=
{ ( GRADE; { n | n∈ number(2,0) ∧ n > 0 } )
, ( LLIMIT; SALARY_TYP )
, ( ULIMIT; SALARY_TYP )
, ( BONUS; SALARY_TYP )
}
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)
}
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
)
}
External Predicate: The course with code
CODE
has descriptionDESCR
, falls in course categoryCAT
, and has duration ofDUR
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.
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 } )
}
tup_CRS :=
{ c | c∈Π(chr_CRS) ∧
/* Build courses never take more than 5 days r21 */
c(CAT) = 'BLD' ⇒ t(DUR) ≤ 5
}
tab_CRS :=
{ C | C∈℘(tup_CRS) ∧
/* Course code uniquely identifies a tuple r22 */
( ∀c1,c2∈C: c1(CODE) = c2(CODE) ⇒ c1 = c2 )
}
External Predicate: The course offering for the course with code
COURSE
that starts atSTARTS
, has statusSTATUS
, has a maximum capacity ofMAXCAP
attendees, is taught by the employee with employee numberTRAINER
, and is offered at locationLOC
.
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.
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) )
}
tup_OFFR :=
{ o | o∈Π(chr_OFFR) ∧
/* Unassigned TRAINER allowed only for certain STATUS values r23 */
o(TRAINER) = -1 ⇒ o(STATUS)∈{'CANC','SCHD'}
}
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 )
}
External Predicate: The employee whose employee number is
STUD
has registered for the course offering of courseCOURSE
that starts atSTARTS
, and has rated the course with an evaluation score ofEVAL
.
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.
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 } )
}
tup_REG :=
{ r | r∈Π(chr_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 )
) )
}
External Predicate: At date
UNTIL
, for employee whose employee number isEMPNO
, either the department or the monthly salary (or both) have changed. Prior to dateUNTIL
, the department for that employee wasDEPTNO
and the monthly salary wasMSAL
.
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.
chr_HIST :=
{( EMPNO; EMPNO_TYP )
,( UNTIL; date )
,( DEPTNO; DEPTNO_TYP )
,( MSAL; SALARY_TYP )
}
tup_HIST :=
{ h | h∈Π(chr_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) )
)
}
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.
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 )
}
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.
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 */
/* ==================================================================== */
}
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.
Note In this section we use sysdate
to denote the moving point “now.”
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} )
) )
}
18.118.186.202