image

Chapter 1 Answers

Exercise 1

  1. This is a FALSE proposition.
  2. Predicate. It is equivalent to the predicate x > 0.
  3. This is a FALSE proposition.
  4. This is a TRUE proposition.
  5. This is a FALSE proposition.

Exercise 2

If you compare the truth tables for A ∧ B and A | B, you’ll notice a pattern:

A B A ∧ B A | B
T T T F
T F F T
F T F T
F F F T

Whenever expression A ∧ B is TRUE , expression A | B is FALSE, and vice versa. In other words, the first expression is the negation of the second expression. This should bring you straight to the solution for expressing the AND in terms of the NAND:

(A ∧ B) ⇔ ¬ (A | B)

If you compare the truth tables for A ∨ B and A | B , you’ll again notice a pattern:

A B A ∨ B A | B
T T T F
T F T T
F T T T
F F F T

The truth values for A ∨ B read downwards in the truth table, and equal the truth values for A | B, if read upwards in the truth table. So, if you were first to generate the four combinations of values for A and B the other way around and then computed the truth values for the NAND on those, you should find the solution. Generating “the other way around” is the same as negating the original input values of A and B. Extending the preceding truth table with the negations of A and B will give you the following truth table:

image

The third column now equals the last column, so you can conclude the following:

(A ∨ B) ⇔ (( ¬A) | ( ¬B))

Exercise 3

Truth Table for (P ∧ P) ⇔ P

P P P ∧ P
T T T
F F F

Truth Table for (¬¬P) ⇔ P

P ¬P ¬¬P
T F T
F T F

Truth Table for (P ∨ Q) ⇔ (Q ∨ P)

image

Truth Table for ((P ∧ Q) ∧ R) ⇔ (P ∧ (Q ∧ R))

image

Truth Table for ((P ∧ Q) ∨ R) ⇔ ((P ∨ R) ∧ (Q ∨ R))

image

Truth Table for ¬(P ∨ Q) ⇔ (¬P ∧ ¬Q)

image

The following table proves the second De Morgan law ¬(P ∧ Q) ⇔ ( ¬P ∨ ¬Q) by using available rewrite rules.

Derivation Comments
¬(P ∧ Q) ⇔ Double negation (twice)
¬(¬¬P ∧¬¬Q) ⇔ First law of De Morgan (right to left)
¬¬(¬P ∨¬Q) ⇔ Double negation
(¬P ∨¬Q)

Exercise 4

b. Using available rewrite rules.

Derivation Comments
P ⇒ Q ⇔ Rewrite implication into disjunction
¬P ∨ Q ⇔ Commutativity
Q ∨ ¬P ⇔ Double negation
¬¬Q ∨ ¬P ⇔ Rewrite disjunction into implication
¬Q ⇒ ¬P

d. ¬(P ⇒ Q) ⇔ (P ∧ ¬Q), using available rewrite rules.

Derivation Comments
¬(P ⇒ Q) ⇔ Rewrite implication into disjunction
¬(¬P ∨ Q) ⇔ De Morgan
¬¬P ∧ ¬Q ⇔ Double negation
P ∧ ¬Q

f. ( ( P ⇒ Q ) ∧( P ⇒ ¬Q ) ) ⇔ ¬P, using truth table.

image

Exercise 5

You can use truth tables or existing rewrite rules. Alternatively, you can bring up a valuation for the involved variables for which the predicate evaluates to FALSE (that is, you give a counter example).

a. P ⇒ (P ∧ Q)

Counter Example: P = TRUE and Q = FALSE

Derivation
TRUE ⇒ (TRUE ∧ FALSE) ⇔
TRUE ⇒ FALSE ⇔
FALSE

Therefore P ⇒ (P ∧ Q) is not a tautology.

b. P ⇒ (P ∨ Q)

Using Existing Rewrite Rules

Derivation Comments
P ⇒ (P ∨ Q) ⇔ Rewrite the implication into a disjunction
¬P ∨ (P ∨ Q) ⇔ Associativity
(¬P ∨ P) ∨Q ⇔ Special case
TRUE ∨ Q ⇔ Special case
TRUE

Therefore P ⇒ (P ∨ Q) is a tautology.

f. (P ⇒ Q) ⇒ (P ∧ Q) is not a tautology.

Counter Example: P = FALSE and Q = TRUE

Derivation
(FALSE ⇒ TRUE) ⇒ (FALSE ∧ TRUE) ⇔
(TRUE) ⇒ (FALSE) ⇔
FALSE

Note that the value of Q did not really matter: P = FALSE and Q = FALSE is a second counter example.

Chapter 2 Answers

Exercise 1

  1. TRUE; 3 is an element of set A.
  2. Meaningless; the left operand should be a set.
  3. TRUE; the empty set is a subset of every set.
  4. FALSE; there are only five elements in A, none of which is the empty set.
  5. TRUE.
  6. FALSE; again there are only five elements in A , none of which is the set {3,4}.

Exercise 2

  1. {3, 5, 7, 9}
  2. Ø
  3. {0}

Exercise 3

  1. { z ∈ N | sqrt(z) ∈ N }
  2. { z ∈ N | mod(z,2) = 0 }
  3. { p ∈ N×N | π1(p)+π2(p) < 11 }

Exercise 4

a. {3,4,5,7,9}

c. {1,8}

f. Depends on the operator precedence. If A − (B ∩ C) is meant, then {1,2,8}. If (A − B) ∩ C is meant, then {2}.

Exercise 6

a. TRUE.

b. TRUE.

f. TRUE.

g. FALSE .

n. There are ten distinct subsets of S that have two elements. You can construct these subsets as follows. Pick a first element from S . You have five possible choices for this. Then pick a second element from S , excluding the first-picked element. You have four possible choices for this. This gives a total of five times four, which equals twenty choices of two elements from S . However, because the order of the choices does not matter, we have to divide twenty by two, resulting in ten possible subsets of S with only two elements.

Exercise 7

image

Chapter 3 Answers

Exercise 2

  1. TRUE; all elements in set A are greater than 1.
  2. TRUE; 5 is an element that exists in B for which, when chosen for variable x, mod(x,5) = 0.
  3. FALSE; if you choose element 2 in A for x and choose 1 in B for y, then x + y = 3, which is not greater than or equal to 4. Because there exists such a combination for x and y, the given predicate does not hold for all values x and y, and is therefore FALSE. The predicate would be TRUE if you redefine set B to {3,5,7,9}.
  4. TRUE; for each element that you can choose from A, there is always an element in y that can be picked such that x + y = 11. For x = 2, 4, 6, 8, pick y = 9, 7, 5, 3, respectively.
  5. FALSE; there is no value in set B such that if you add that value to every available value in set A , the result of every such addition would always be 11. The predicate would be TRUE if you redefined set A such that it only holds one element, which effectively downgrades the inner universal quantification to an existential quantification. For A = {2} you can choose y = 9. For A = {4} you can choose y = 7. For A = {6} you can choose y = 5. For A = {8} you can choose y = 3 . Another option would be to redefine set A to the empty set. The inner universal quantification will then be TRUE, irrespective of the expression (x + y = 11). As long as B has at least one element, the full predicate will then always be TRUE.
  6. TRUE; choose 2 in A for both x and y. You might think that it is not allowed to choose the same value twice, but it is in this case. Variables x and y are bound independently to set A. The binding of y to (the inner) A is not influenced by the value you choose for x that is bound to the outer A.

Exercise 3

  1. ∀x∈A: div(x,2) = 0
  2. ∀x∈B: x < 9
  3. ∃x∈A: ∃y∈A: ∃z∈A: x ≠ y ∧ y ≠ z ∧ z ≠ x ∧ x + y + z = 18

Exercise 4

  1. ∀x∈A: x ≥ 5
  2. ∃x∈B: mod(y,2) = 0

Exercise 7

To prove this equivalence you start with either the left or right side of the equivalence and apply a series of rewrite rules such that you end up with the other side. Let’s start with the left side:

¬( ∃x∈S: ∀y∈T: P(x,y) )

Note that you can view this expression as follows: ¬(∃x∈S: R(x) ) , where R(x) =( ∀y∈T: P(x,y) ). Now you can apply the third rewrite rule of Listing 2-15, which has the following result:

( ∀x∈S: ¬R(x) )

Substituting the expression for R(x) back into this gives the following result:

( ∀x∈S: ¬( ∀y∈T: P(x,y) ) )

Now you can apply the fourth rewrite rule of Listing 2-15 to the inner (negated) universal quantification, which has the following result:

( ∀x∈S: ∃y∈T: ¬P(x,y) )

This ends the proof of the given equivalence.

Chapter 4 Answers

Exercise 3

  1. Function.
  2. Function.
  3. Function.
  4. Function.
  5. Specified in the enumerative method, the set is { (a;f), (b;e), (b;f) }. This is not a function.
  6. This is not a function. The first pair holds a second coordinate that is not in A. The second pair holds a first coordinate that is not in B.

Exercise 6

  1. The expression evaluates to { (X;1), (Y;3), (Z;2), (R;1) }. This is a function.
  2. The expression evaluates to { (X;1) }. This is a function.
  3. The expression evaluates to { (X;1), (Y;3), (X;2) }. This is not a function; the first coordinate X appears twice.

Exercise 7

  1. { { (a;1), (b;1) }, { (a;1), (b;2) } }
  2. Ø

Exercise 8

a. { (ean;9786012), (price;24.99) }

c. { (descr;'A very nice book') }

Exercise 11

  1. The left side evaluates to { {(empno;104)}, {(empno;106)}, {(empno;102)} }: a set of three functions. The right side evaluates to { {(empno;103)}, {(empno;104)}, {(empno;105)}, {(empno;106)} } ; this is a set of four functions. The proposition is FALSE.
  2. This expression evaluates to { {(deptno;10)}, {(deptno;20)} } ⊂ { {(deptno;10)}, {(deptno;20)} , {(deptno;30)} }. This proposition is TRUE; the set of two functions at the left is indeed a proper subset of the set of three functions at the right.

Chapter 5 Answers

Exercise 1

  1. This is not a table; not all tuples have the same domain.
  2. This is a table; in fact it is the empty table.
  3. This is a table over {partno}.
  4. This is a table over {suppno,sname,location}.
  5. This is a table over {partno,pname,price}.

Exercise 2

  1. { p | p∈Π(chr_PART) ∧ p(name)='hammer' ⇒ p(price)>250 }
  2. { p | p∈Π(chr_PART) ∧ p(price)<400 ⇒ p(name)≠'drill'} , or
    { p | p∈Π(chr_PART) ∧ p(name)='drill' ⇒ p(price)≥400 }
  3. { p | p∈Π(chr_PART) ∧ p(partno) ∈ {10,15,20} ⇒ p(instock)≤42 }

Exercise 3

E1 holds five tuples: the four tuples from table P and tuple {(partno;201)} . Because they do not all share the same domain, E1 is not a table.

E2 holds five tuples that all share the domain {partno,pname,price}; it is therefore a table.

Exercise 4

E6, the join of S and SP, is a table over {partno,suppno,available,reserved,sname,location}. It holds the six tuples from SP that have been extended with the supplier name and location.

E8 represents the Cartesian join of S and P. It is a table over {partno,pname,price,suppno, sname,location}, and holds eight tuples.

Exercise 5

P1 states that for all pairs of parts that can be chosen from table P, the two parts have different part numbers. This is a FALSE proposition because the formal specification allows a pair to hold the same part twice.

P2 states that for all pairs of different parts (chosen from table P), the two parts have different part numbers. This is a TRUE proposition.

Chapter 6 Answers

Exercise 1

  1. This proposition states that all even-numbered parts (in table PAR1) have a price of less than or equal to 15 . This is a TRUE proposition.
  2. If you rewrite this proposition into a universal quantification, you’ll see that it states that all parts are priced 5 and are currently in stock. Obviously this is a FALSE proposition.
  3. If you rewrite the implication into a disjunction, you’ll see that this proposition states that there are six parts in PAR1 for which we either have 10 or less items in stock, or that cost 10 or less. This is a TRUE proposition; all six parts in PAR1 satisfy the implication.

Exercise 4

This involves specifying three subset requirements and one additional constraint to state that every tuple in EMP1 has a corresponding tuple in one of the specializations.

TRN1⇓{empno} ⊆ EMP1⇓{empno} ∧
MAN1⇓{empno} ⊆ EMP1⇓{empno} ∧
CLK1⇓{empno} ⊆ EMP1⇓{empno} ∧
(∀ e∈EMP1: e(job)='TRAINER' ⇒(∃ t∈TRN1: t(empno)=e(empno))∧
            e(job)='MANAGER' ⇒(∃ m∈MAN1: m(empno)=e(empno))∧
            e(job)='CLERK'   ⇒(∃ c∈CLK1: c(empno)=e(empno)))

Exercise 5

This involves specifying three subset requirements and a few additional constraints stating that all tuples in EMP1 are covered by exactly one tuple in one of the specializations.

TRN1⇓{empno} ⊆ EMP1⇓{empno} ∧
MAN1⇓{empno} ⊆ EMP1⇓{empno} ∧
CLK1⇓{empno} ⊆ EMP1⇓{empno} ∧
TRN1⇓{empno} ∩ MAN1⇓{empno} = Ø∧
TRN1⇓{empno} ∩ CLK1⇓{empno} = Ø∧
MAN1⇓{empno} ∩ CLK1⇓{empno} = Ø∧
#EMP1 = #TRN1 + #MGR1 + #CLK1

Exercise 7

This is a tuple-in-join predicate. We join EMP1 with CLK1 on the empno attribute, and then join back to EMP1 on the manager attribute (which requires attribute renaming).

(∀ e∈(EMP1⊗CLK1)⊗(EMP1⋄⋄{(manager;empno),(m_deptno;deptno)}):
      e(deptno)=e(m_deptno))

This is a FALSE proposition; the managers of clerks 105 and 107 work in a different department.

Chapter 7 Answers

Exercise 2

Predicate o(STATUS)='CONF' ⇒ o(TRAINER)≠-1 is equivalent to predicate o(TRAINER)=-1 ⇒ o(STATUS)∈{'CANC','SCHD'}. This is a manifestation of the following rewrite rule:

(A ⇒ B) ⇔ ( ¬B ⇒¬A)

Your response should therefore be that adding that tuple constraint does not add anything.

Exercise 3

tab_MEMP :=
   { M | M∈ image(tup_MEMP) ∧
         /* EMPNO uniquely identifies a tuple */
         ( ∀m1,m2∈M: m1(EMPNO) = m2(EMPNO) ⇒ m1 = m2 ) ∧
         ( ∀m∈M:  |{ e| e∈M ∧ e(MGR)=m(MGR) }| ≤ 10 )
   }

When designing this constraint, you might want to check with the users whether the TERM table structure should play a role in this constraint.

Exercise 6

Of the thirteen elements that are in tab_RESULT (see Listing 7-25), only the following two can be combined with the given LIMIT table:

{ Ø
, { { (POPULATION;'DP'),     (COURSE;'set theory'), (AVG_SCORE;'C') }
  , { (POPULATION;'DP'),     (COURSE;'logic'),      (AVG_SCORE;'B') }
  , { (POPULATION;'NON-DP'), (COURSE;'set theory'), (AVG_SCORE;'E') }
  , { (POPULATION;'NON-DP'), (COURSE;'logic'),      (AVG_SCORE;'D') } }
}

All other result tables either have an average score of A for database pros, or an average score of F for non-database pros; these are prohibited by the database constraint given in DB_U2.

Exercise 7

You can express this by stating that for every department manager, the department number of the department that employs the manager must be an element of the set of department numbers of departments managed by this manager.

PTIJ5(EMP,DEPT) :=
  ( ∀d1∈DEPT⇓{MGR}: image{ e(DEPTNO)| e∈EMP ∧ e(EMPNO) =d1(MGR)}∈
                       { d2(DEPTNO)| d2∈DEPT ∧ d2(mgr)=d1(mgr) } )

Note that this is now no longer a tuple-in-join predicate.

Exercise 9

Constraints PTIJ3 and PTIJ4 prevent these cycles.

Exercise 11

The given constraint is abstractly of the following form:

(∀o∈OFFR: (P(o) ⇒ (Q(o,REG) ∧ R(o,OFFR)))

Here P, Q, and R are predicates with free variables o, o plus REG, and o plus OFFR, respectively. You can rewrite this predicate form into this:

(∀o∈OFFR: (P(o) ⇒ Q(o,REG)) ∧ (P(o) ⇒ R(o,OFFR)))

You can rewrite this, in turn, into the following conjunction:

(∀o∈OFFR: P(o) ⇒ Q(o,REG)) ∧ (∀o∈OFFR: P(o) ⇒ R(o,OFFR))

Note that the second conjunct now only involves the OFFR table structure and therefore is a table predicate.

Chapter 8 Answers

Exercise 3

STC(EMPB,EMPE) :=
  (∀e1∈EMPB, e2∈EMPE: (e1(EMPNO) = e2(EMPNO) ∧ e1(MSAL) > e2(MSAL))
                        ⇒ e1(SGRADE) < e2(SGRADE))

Exercise 4

STC2(OFFRB,OFFRE) :=
  /* New offerings must start with status SCHED */
  ( ∀o∈(OFFRE⇓{COURSE,STARTS}-OFFRB⇓{COURSE,STARTS})⊗OFFRE: o(STATUS)='SCHD' )

Exercise 5

We assume that addition has been defined on the date data type. By adding 31 days we formally specify the “one month.”

STC(EMPB,EMPE) :=
  ( ∀e∈(EMPE⇓{EMPNO}-EMPB⇓{EMPNO})⊗EMPE: e(HIRED)<=sysdate+31)

Exercise 6

If you rewrite the conclusion of STC7 ’s quantified implication into conjunctive normal form, you’ll end up with six conjuncts. In the same way as exercise 11 in Chapter 7, you can then rewrite the implication into a conjunction of six implications. From that, you can rewrite the universal quantification into a conjunction of six quantifications.

You can rewrite STC3 into a conjunction as follows:

( ∀o1∈OFFRB, o2∈OFFRE:
   (o1↓{COURSE,STARTS} = o2↓{COURSE,STARTS} ∧ o1(STATUS) ≠ o2(STATUS))
    ⇒ (o1(STATUS)='SCHD' ⇒ (o2(STATUS)='CONF' ∨ o2(STATUS)='CANC') )
∧
( ∀o1∈OFFRB, o2∈OFFRE:
   (o1↓{COURSE,STARTS} = o2↓{COURSE,STARTS} ∧ o1(STATUS) ≠ o2(STATUS))
    ⇒ (o1(STATUS)='CONF' ⇒ o2(STATUS)='CANC') )

Exercise 9

In the following specification, CRSE represents the CRS table in the end state. You’ll need to join to CRS to determine the last day of the offering.

STC7(REGB,REGE) :=
  ( ∀r1∈REGB, r2∈REGE⊗(CRSE⋄⋄{(COURSE;CODE),(DUR;DUR)}):
    (r1↓{STUD,STARTS} = r2↓{STUD,STARTS}∧r1(EVAL) ≠r2(EVAL))
    ⇒
    ( ( r1(EVAL) = -1 ∧ r2(EVAL) = 0 ∧ r2(STARTS) ≤ sysdate ∧
                                        r2(STARTS)+r2(DUR) ≥ sysdate )∨
      ( r1(EVAL) = 0 ∧ r2(EVAL) ∈ {1,2,3,4,5} ) ) )

Chapter 9 Answers

Exercise 1

{ t↓{empno,name} | t∈dbs(EMP) ∧ t(deptno)=10 }

select e.EMPNO, e.NAME
from EMP e
where e.deptno=10

Exercise 3

Note that an employee belongs to exactly one department. You can try to retrieve an answer for this question but it will always be the empty set (table).

{ t↓{empno,name} | t∈dbs(EMP) ∧ t(deptno)=10 ∧ t(deptno)=20}

select e.EMPNO, e.NAME
from EMP e
where e.DEPTNO=10 and e.DEPTNO=20

Exercise 5

{ (message;'Constraint is violated') | x∈{1} ∧
                             #{ e | e∈dbs(EMP) ∧ e(job)='PRESIDENT' } > 1 }
∪
{ (message;'Constraint is satisfied') | x∈{1} ∧
                             #{ e | e∈dbs(EMP) ∧ e(job)='PRESIDENT' } ≤ 1 }
select 'Constraint is violated'
from DUAL
where 1 < (select count(*)
           from EMP e
           where e.job='PRESIDENT')
union
select 'Constraint is satisfied'
from DUAL
where 1 >= (select count(*)
            from EMP e
            where e.job='PRESIDENT')

Exercise 7

There are a few ways to interpret “managers.” A manager is either an employee whose job equals MANAGER , or an employee who is managing other employees, or an employee who is managing a department, or any combination of these. We’ll give queries for the first two interpretations.

{ e↓{empno,name} | e∈dbs(EMP)⊗(dbs(GRD)⋄⋄{(sgrade;grade),(ulimit;ulimit)}) ∧
                   e(job)='MANAGER' ∧ e(msal)=e(ulimit) }
{ e↓{empno,name} | e∈dbs(EMP) ∧ e(empno) ∈ { m(mgr) | m∈dbs(MEMP) } ∧
                   e(msal)= image{ g(ulimit) | g∈dbs(GRD) ∧g(grade)=e(sgrade) }
select e.EMPNO, e.NAME
from EMP e
where e.JOB='MANAGER'
  and e.MSAL = (select g.ULIMIT
                from GRD g
                where g.GRADE = e.SGRADE)
select e.EMPNO, e.NAME
from EMP e
where e.EMPNO in (select m.MGR
                  from MEMP m)
  and e.MSAL = (select g.ULIMIT
                from GRD g
                where g.GRADE = e.SGRADE)

Exercise 10

The way this question has been expressed suggests the meaning of “manager” as an employee who is managing other employees (MEMP table structure). In this case the answer to the question is easy: Ø. No such manager exists, because there are two tuple-in-join constraints that state that managers always earn more than the employees they manage.

You could also interpret “his employees” as the employees who work in the department(s) that the “manager” manages. We’ll provide the query for this case:

{ em↓{empno,name,msal} | em∈dbs(EMP)⊗(dbs(DEPT)⋄⋄{(empno;mgr),(mdeptno;deptno)})
                         ∧((∃e∈dbs(EMP): e(job) ≠'SALESREP' ∧
                                          e(deptno)=em(mdeptno) ∧
                                          e(msal)>em(msal))∨
                            (∃e∈dbs(EMP)⊗dbs(SREP):
                                          e(job) ≠'SALESREP' ∧
                                          e(deptno)=em(mdeptno) ∧
                                          e(msal)+e(comm)/12 >em(msal))) }
select distinct /* Must use distinct! */
       em.EMPNO, em.NAME, em.MSAL
from EMP em
    ,DEPT d
where d.MGR = em.EMPNO
  and (exists (select 'One of "his employees" earns more (non salesrep case)'
               from EMP e
               where e.job <> 'SALESREP'
                 and e.DEPTNO = d.DEPTNO
                 and e.MSAL > em.MSAL)
       or
       exists (select 'One of "his employees" earns more (salesrep case)'
               from EMP e
                   ,SREP s
               where e.EMPNO = s.EMPNO
                 and e.DEPTNO = d.DEPTNO
                 and e.MSAL + s.COMM/12 > em.MSAL))

Exercise 13

{ {(empno;e(empno)),(name;e(name)),(smempno;sm(empno)),(smname;sm(name))}
   | e∈dbs(EMP) ∧ m1∈dbs(MEMP) ∧ m2∈dbs(MEMP) ∧ sm∈dbs(EMP) ∧
     e(empno)=m1(empno) ∧ m1(mgr)=m2(empno) ∧ m2(mgr)=sm(empno) }

select e.EMPNO, e.NAME, sm.EMPNO as SMEMPNO, sm.NAME as SMNAME
from EMP e
    ,MEMP m1
    ,MEMP m2
    ,EMP sm
where e.EMPNO = m1.EMPNO
  and m1.MGR = m2.EMPNO
  and m2.MGR = sm.EMPNO

Exercise 21

{ c↓{code,dur} ∪
  {(2006_offerings; { o↓{starts,status} ∪
                      {(students; #{ r| r in dbs(REG) ∧
                                        r↓{course,starts}=o↓{course,starts} })}
                      | o∈dbs(OFFR) ∧ o(course)=c(code) ∧ year(o(starts))=2006 }
  )}
  | c∈dbs(CRS) ∧ c(code) ∈ {'DB1','DB2','DB3'} }

select c.CODE, c.DUR, o.STARTS, o.STATUS
      ,(select count(*)
        from REG e
        where e.COURSE = o.COURSE
          and e.STARTS = o.STARTS) as students
from CRS c
    ,OFFR o
where c.CODE in ('DB1','DB2','DB3')
  and c.CODE = o.COURSE
  and to_char(o.STARTS,'YYYY') = '2006'

Note that the SQL expression repeats CRS information.

Chapter 10 Answers

Exercise 1

First, the two table constraints are involved:

/* Attendee and begin date uniquely identify a tuple */
( ∀r1,r2∈R:
  r1↓{STARTS,STUD} = r2↓{STARTS,STUD} ⇒ r1 = r2 )

/* Offering is evaluated by all attendees, or it is too early to */
/* evaluate the offering */
( ∀r1,r2∈R:
  ( r1↓{COURSE,STARTS} = r2↓{COURSE,STARTS} )
    ⇒
  ( ( r1(EVAL) = -1 ∧r2(EVAL) = -1 ) ∨
    ( r1(EVAL) ≠ -1 ∧r2(EVAL) π -1 )
) )

The first constraint can be violated if one of the administrators has already been registered for the offering.

The second constraint cannot be violated by transaction ETX1 irrespective of the begin state (assuming March 1 is still in the future; that is, other registrations already present still reflect EVAL=-1).

PTIJ8 (you cannot register for offerings in the first four weeks on the job) is involved and cannot be violated by ETX1 given the e.HIRED between sysdate - 91 and sysdate predicate in the WHERE clause.

PTIJ9 (you cannot register for offerings given at or after leave date) is involved. For ETX1 the begin state should reflect that all administrators who have been hired in the past three months are still working for the company.

PTIJ10 (you cannot register for overlapping courses) is involved. For ETX1 the begin state should reflect that none of these administrators are already registered for another offering that overlaps with the March 1 AM4DP offering.

PTIJ13 (trainer cannot register for offerings taught by him/herself) is involved. This one cannot be violated because administrators cannot act as the trainer for an offering.

PTIJ15 (you cannot register for offering that overlaps with another one where you are the trainer) is involved. For the same reason mentioned with PTIJ13 , this one cannot be violated either.

PODC4 (offerings with more than six registrations must have status confirmed) is involved. Depending upon the current number of registrations, the current status for the offering, and the number of administrators that get registered for the AM4DP offering, this constraint might well get violated by ETX1.

PODC5 (number of registrations cannot exceed maximum capacity of offering) is involved. This one too will get violated by ETX1, depending on the current number of registrations and the number added by ETX1.

PODC6 (canceled offerings cannot have registrations) is involved; it will get violated if the current status of the offering equals 'canceled'.

Exercise 3

The following integrity constraints are involved in the UPDATE statement of transaction ETX4: PTIJ1, PTIJ3, PTIJ4, PTIJ6, and PTIJ7. All of these except PTIJ3 run the risk of being violated by the statement.

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

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