);

CREATE TABLE StarsIn (

movieTitle VARCHAR(255), movieYear INTEGER, starName CHAR(30) );

CREATE TABLE MovieExec ( name CHAR(30),

address VARCHAR(255),

cert# INTEGER PRIMARY KEY, netWorth INTEGER );

CREATE TABLE Studio (

name CHAR(50) PRIMARY KEY, address VARCHAR(255), presC# INTEGER );

We have chosen not to add

PRIMARY KEY (movieTitle, movieYear, starName)

to the declaration of StarsIn, although we could. Since that relation is likely to be used for going between movies and stars or vice versa, we think an index, which the DBMS might create for us were we to declare a primary key, will be of no use and may waste time.

Exercise 7.1.2(a)

We may add to the declaration of attribute producerC# the clause: REFERENCES MovieExec(cert#)

No policy needs to be specified, because rejecting violating modifications is the default action.

Exercise 7.1.3

Nope. A foreign key must refer to the primary key in some relation. But movieTitle and movieYear are not a key for StarsIn, even though title and year are the key for Movie.

Exercise 7.1.6(a)

CREATE TABLE Ships ( name CHAR(30),

class CHAR(30) REFERENCES Classes(class), launched INTEGER );

In addition to the above declaration, class must be declared the primary key for Classes.

Solutions for Section 7.2

Exercise 7.2.1(a)

CREATE TABLE Movie ( ...

year INTEGER CHECK (year >= 1895), ... )

Exercise 7.2.1(c)

revised 6/26/03

CREATE TABLE Movie ( ...

studioName CHAR(30) CHECK (studioName IN ('Disney', 'Fox', 'MGM', 'Paramount')), ... )

Exercise 7.2.4(a)

Add to the declaration of Movie the tuple-based check constraint:

CHECK (NOT inColor OR year >= 1939)

Exercise 7.2.4(d)

Add to the declaration of MovieStar:

CHECK (name NOT IN (SELECT name FROM MovieExec))

Also, add to the declaration of MovieExec:

CHECK (name NOT IN (SELECT name FROM MovieStar))

Solutions for Section 7.3

Exercise 7.3.1(a)

ALTER TABLE Movie ADD CONSTRAINT MovieKey PRIMARY KEY (title, year);

Exercise 7.3.1(d)

ALTER TABLE MovieStar ADD CONSTRAINT c1

CHECK (name NOT IN (SELECT name FROM MovieExec)); ALTER TABLE MovieExec ADD CONSTRAINT c2

CHECK (name NOT IN (SELECT name FROM MovieStar));

Solutions for Section 7.4

Exercise 7.4.2(a)

CREATE TRIGGER LowerPriceTrigger AFTER UPDATE OF price ON PC

REFERENCING OLD ROW AS OldTuple NEW ROW AS NewTuple FOR EACH ROW

WHEN(NOT(NewTuple.price <= ALL (SELECT price FROM PC

WHERE speed = NewTuple.speed ) ) ) BEGIN

DELETE FROM PC

WHERE (model, speed, ram, hd, rd, price) = NewTuple;

INSERT INTO PC VALUES(OldTuple.model, OldTuple.speed, OldTuple.ram, OldTuple.hd, OldTuple.rd, OldTuple.price); END;

Exercise 7.4.2(b)

CREATE ASSERTION CHECK (NOT EXISTS

(

(SELECT maker FROM Product NATURAL JOIN PC) INTERSECT

(SELECT maker FROM Product NATURAL JOIN Laptop) ) );

Exercise 7.4.2(c)

CREATE ASSERTION CHECK (NOT EXISTS

(SELECT maker

FROM Product NATURAL JOIN PC WHERE speed > ALL (SELECT L2.speed

FROM Product P2, Laptop L2 WHERE P2.maker = maker AND P2.model = L2.model ) ) );

Exercise 7.4.3(a)

CREATE TRIGGER NewClassTrigger AFTER INSERT ON Classes

REFERENCING NEW TABLE AS NewStuff FOR EACH STATEMENT WHEN(TRUE)

INSERT INTO Ships(name, class) (SELECT class, class FROM NewStuff );

Database Systems: Book

The Complete Solutions for Chapter 8

Revised 11/4/01.

Solutions for Section 8.1

Exercise 8.1.1(a)

In the following, we use macro NO_MORE_TUPLES as defined in the section. void closestMatchPC() {

EXEC SQL BEGIN DECLARE SECTION; char manf[30], SQLSTATE[6];

int targetPrice, /* holds price given by user */

speedOfClosest, modelOfClosest, priceOfClosest, /* for closest price found so far */ tempSpeed, tempModel, tempPrice;

/* for tuple just read from PC */ EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE pcCursor CURSOR FOR SELECT model, price, speed FROM PC;

EXEC SQL OPEN pcCursor;

/* ask user for target price and read the answer into variable targetPrice */

/* Initially, the first PC is the closest to the target price.

If PC is empty, we cannot answer the question, and so abort. */

EXEC SQL FETCH FROM pcCursor INTO :modelOfClosest, :priceOfClosest, :speedOfClosest; if(NO_MORE_TUPLES) /* print message and exit */ ;

while(1) {

EXEC SQL FETCH pcCursor INTO :tempModel, :tempPrice, :tempSpeed; if(NO_MORE_TUPLES) break;

if( /* tempPrice closer to targetPrice than is priceOfClosest */) { modelOfClosest = tempModel; priceOfClosest = tempPrice; speedOfClosest = tempSpeed; } }

/* Now, modelOfClosest is the model whose price is closest to