Database Systems: The Complete Book
Solutions for Chapter 7

Solutions for Section 7.1
Solutions for Section 7.2
Solutions for Section 7.3
Solutions for Section 7.4

Solutions for Section 7.1

Exercise 7.1.1

CREATE TABLE Movie (
    title VARCHAR(255),
    year INTEGER,
    length INTEGER,
    inColor BIT(1),
    studioName CHAR(50),
    producerC# INTEGER,
    PRIMARY KEY (title, year)
);

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.

Return to Top

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))





Return to Top





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));





Return to Top





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
    );





Return to Top