An Introduction to Database Systems 8Ed - C J Date - Solutions Manual Episode 1 Part 9 pot

Copyright (c) 2003 C. J. Date page 9.23

unknown, and the constraint will not be regarded as violated.

See Chapter 19 for further explanation.

l. CREATE ASSERTION SQL_L CHECK

( NOT EXISTS ( SELECT * FROM S

WHERE S.CITY = 'London'

AND NOT EXISTS

( SELECT * FROM SPJ

WHERE SPJ.S# = S.S#

AND SPJ.P# = P# ( 'P2' ) ) ) ) ;

m. CREATE ASSERTION SQL_M CHECK

( NOT EXISTS ( SELECT * FROM P

WHERE P.COLOR = COLOR ( 'Red' ) )

OR EXISTS ( SELECT * FROM P

WHERE P.COLOR = COLOR ( 'Red' )

AND P.WEIGHT < WEIGHT ( 50.0 ) ) ) ;

n. CREATE ASSERTION SQL_N CHECK

( ( SELECT COUNT ( DISTINCT P# ) FROM SPJ

WHERE EXISTS ( SELECT * FROM S WHERE

( S.S# = SPJ.S# AND

S.CITY = 'London' ) ) ) >

( SELECT COUNT ( DISTINCT P# ) FROM SPJ

WHERE EXISTS ( SELECT * FROM S WHERE

( S.S# = SPJ.S# AND

S.CITY = 'Paris' ) ) ) ) ;

o. CREATE ASSERTION SQL_O CHECK

( ( SELECT SUM ( SPJ.QTY ) FROM SPJ

WHERE ( SELECT S.CITY FROM S

WHERE S.S# = SPJ.S# ) = 'London' ) >

( SELECT SUM ( SPJ.QTY ) FROM SPJ

WHERE ( SELECT S.CITY FROM S

WHERE S.S# = SPJ.S# ) = 'Paris' ) ) ;

Note the use of two scalar subqueries in this example.

p. Can't be done directly (SQL doesn't support transition

constraints). We could write a trigger, though. No further

answer provided.

q. Same as p.

9.15 The answers are trivial syntactic variations on those already

given for Exercises 9.7-9.9. No further answer provided.

9.16 No. An important exception is predicates of the form

illustrated by this example: "i is an integer." This is a

membership predicate; in fact, it's the type constraint for the

Copyright (c) 2003 C. J. Date page 9.24

type INTEGER.* Note: If instead of treating INTEGER as a type we

tried to define a relvar INTEGER to list all possible integers,

what type would the single attribute I of that relvar be?

──────────

* At least, for an ideal type INTEGER; we ignore the fact that no

real computer system is capable of directly representing all

possible integers.

──────────

9.17 Suppose we were to define a relvar SC{S#,CITY} with predicate

"Supplier S# does not have an office in city CITY." Suppose

further that supplier S1 has an office in just ten cities; then

the Closed World Assumption would imply that relvar SC must have

N-10 tuples for supplier S1, where N is the total number of cities

in the world!

*** End of Chapter 9 ***