Exercise 6.4.3

c) CREATE ASSERTION PriceCheck CHECK

(NOT EXISTS

(select model

from Laptop L, PC C

where L.ram>C.ram and L.price<=C.price))

d) CREATE ASSERTION ModelCheck CHECK

(NOT EXISTS

(select model

from Laptop L, PC C, Printer P

where L.model=P.model or P.model=C.model or

L.model=C.model))

e) CREATE ASSERTION DuplicateModel CHECK

(NOT EXISTS

(select PR.model

from Product PR

where (PR.model!=L.model PR.type=’laptop’ PR.model not in (select model from Laptop)

or (PR.type=’pc’ and PR.model not in (select model from PC)

or (PR.type=’printer’ and PR.model not in (select model Printer)

)

Exercise 6.4.6

Create Table Classes

(class char(15),

type int,

country char(15),

numGuns int,

bore int,

displacement int,

a) CHECK bore<=16);

b) CHECK (numGuns<=9 or bore<=14)

Create Table Ships

(name char(20),

class char(15),

launched char(1))

Create Table Battles(

name varchar(10),

date Date)

Create Table Outcomes

(ship char(20),

battle varchar(10),

result int

c) CHECK (NOT EXISTS (select name from Ships S, Battles B where ship=S.name and battle=B.name and S.launched>B.date)

Exercise 6.6.4

a) Create Table StarsIn(

MovieTitle varchar(15),

MovieYear int,

StarName varchar(15) references MovieStar(name));

b)

Create Assertion Exec CHECK (name in (select name from Studio) or

Name in (select producerC from Movie )

c)

create Assertion StarGender

CHECK (NOT EXISTS

Select Title

From Movie

Where Title Not IN

(select movieTitle

from MovieStar MS, StarsIn S

Where S.starName=MS.name and

MS.gender=’F’)

Or Title Not IN

(select StarName

from StarsIn S, MovieStar MS

Where S.starName=MS.name and

MS.gender=’M’)

)

d)Create Trigger NumMovies

Instead of Insert ON Movie

Refencing

NEW_TABLE as New

When (100>=ALL(select COUNT(*) from (Movie UNION New) group by StudioName, year))

Insert INTO Movie

(select * from New)

e) Create Assertion LenMovies

CHECK (120>=ALL(select AVG(length) from Movie group by year))

Exercise 7.4.3

9.1.1

R(A), R(B), W(B), R(C), W(C), R(D), W(D), R(E), W(E)

9.2.1

a)A=x;B=y

(T1,T2)

A: t:=x+2; B: t:=3*y, B: s:=6*y; A: s:=x+5

End result: A=x+5 B=6*y

(T2,T1)

B: s:=2*y; A: s:=x+3 A: t:=x+5; B: t:=6*y

End result: A=x+5 B=6*y

b)

Serializable : T1,T2

Non Serializable: T1<T2 and T2<T1

R1(A,t) R2(B,s) W2(B,s) R2(A,s) W1(A,t) W2(A,s) R1(B,t) W1(B,t)

c) (T1,T2), (T2,T1)

9.2.4

c) From ations on A: T3<T1

on B:T1<T2

on C: T2<T3

i)

ii)No

iii) no

d) From ations on B: T2<T1 and T1<T2

i)

ii)No

iii)no

e) From actions on A: T2<T1; T3<T1

on B: T1<T2; T4<T2

i)

ii) No

iii) No