A First Course in Database Systems

Solutions for Chapter 6

Solutions for Section 6.1

Solutions for Section 6.2

Solutions for Section 6.3

Solutions for Section 6.4

Solutions for Section 6.5

Solutions for Section 6.6

Solutions for Section 6.1

Exercise 6.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.

Return to Top

Solutions for Section 6.2

Exercise 6.2.2

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 6.2.3(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 6.3

Exercise 6.3.1(a)

CREATE TABLE Movie (
    ...
    year INTEGER CHECK (year >= 1895),
    ...
)



Exercise 6.3.1(c)


Revised 1/9/99.



CREATE TABLE Studio (
    name CHAR(30) CHECK (name IN ('Disney', 'Fox', 'MGM', 'Paramount')),
    ...
)




Return to Top





Solutions for Section 6.4



Exercise 6.4.2(a)


Revised 9/13/99.


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


CHECK (NOT inColor OR year >= 1939)




Exercise 6.4.2(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))




Exercise 6.4.3(a)



CREATE ASSERTION CHECK
    (NOT EXISTS
        (
             (SELECT maker FROM Product NATURAL JOIN PC)
         INTERSECT
             (SELECT maker FROM Product NATURAL JOIN Laptop)
        )
    );




Exercise 6.4.3(b)



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




Return to Top





Solutions for Section 6.5



Exercise 6.5.1(a)



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




Exercise 6.5.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 6.6


Updated 5/23/97.



Exercise 6.6.2(a)



CREATE TRIGGER LowerPriceTrigger
AFTER UPDATE OF price ON PC
REFERENCING OLD AS OldTuple
            NEW AS NewTuple

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

DELETE FROM PC
WHERE (model, speed, ram, hd, cd, price) IN NewTuple;

INSERT INTO PC (SELECT * FROM OldTuple);




Exercise 6.6.3(a)



CREATE TRIGGER NewClassTrigger
AFTER INSERT ON Classes
REFERENCING NEW AS NewClass

WHEN(TRUE)

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




Return to Top