First Course in Database Systems: Solutions for Chapter 5

A First Course in Database Systems

Revised 12/9/00.

Solutions for Chapter 5

Solutions for Section 5.1

Solutions for Section 5.2

Solutions for Section 5.3

Solutions for Section 5.4

Solutions for Section 5.5

Solutions for Section 5.6

Solutions for Section 5.7

Solutions for Section 5.8

Solutions for Section 5.9

Solutions for Section 5.10

Solutions for Section 5.1

Exercise 5.1.2(a)

SELECT address
FROM Studio
WHERE name = 'MGM';

Exercise 5.1.2(c)

Revised 11/1/98.

If you interpret the question as asking only that Love appear as a substring, then the following is OK:

SELECT starName
FROM StarsIn
WHERE movieYear = 1980 OR movieTitle LIKE '%Love%';

However, another reasonable interpretation is that we want the word Love as a word by itself. The query above returns stars of a movie like The Cook, the Thief, His Wife, and Her Lover. To identify only titles that have Love as a word by itself, either at the beginning, the middle, the endor as the entire title, we need to use four patterns. The following query works; notice the judiciously placed blanks in the patterns.

SELECT starName
FROM StarsIn
WHERE movieYear = 1980 OR
      movieTitle LIKE 'Love %' OR
      movieTitle LIKE '% Love %' OR
      movieTitle LIKE '% Love' OR
      movieTitle = 'Love';

Exercise 5.1.3(a)

SELECT model, speed, hd
FROM PC
WHERE price < 1600;

The result:

model	speed	hd
1001	133	1.6
1002	120	1.6
1010	160	1.2

Exercise 5.1.3(b)

SELECT model, speed AS megahertz, hd AS gigabytes
FROM PC
WHERE price < 1600;

The result:

model	megahertz	gigabytes
1001	133	1.6
1002	120	1.6
1010	160	1.2

Exercise 5.1.3(e)

SELECT *
FROM Printer
WHERE color;

The result:

model	color	type	price
3001	true	ink-jet	275
3002	true	ink-jet	269
3006	true	dry	470

Return to Top

Solutions for Section 5.2

Exercise 5.2.1(a)

(Revised 11/3/98)

SELECT name
FROM MovieStar, StarsIn
WHERE gender = 'M' AND
      name = starName AND
      movieTitle = 'Terms of Endearment';

Exercise 5.2.1(d)

SELECT M1.title
FROM Movie AS M1, Movie AS M2
WHERE M2.title = 'Gone With the Wind' AND
      M1.length > M2.length;

Exercise 5.2.2(a)

SELECT maker, speed
FROM Product, Laptop
WHERE hd >= 1.0 AND
      Product.model = Laptop.model;

Exercise 5.2.2(b)

We need to join Product with each of the other three relations and take the union.

    (SELECT Product.model, price
     FROM Product, PC
     WHERE Product.model = PC.model AND
           maker = 'B')
UNION
    (SELECT Product.model, price
     FROM Product, Laptop
     WHERE Product.model = Laptop.model AND
           maker = 'B')
UNION
    (SELECT Product.model, price
     FROM Product, Printer
     WHERE Product.model = Printer.model AND
           maker = 'B');

Exercise 5.2.4

A systematic way to handle this problem is to create a tuple variable for every Ri, i=1,2,...,n, whether we need to (because Ri appears more than once) or not. That is, the FROM clause is

FROM R1 AS T1, R2 AS T2,...,Rn AS Tn.

Now, build the WHERE clause from C by replacing every reference to some attribute A of Ri by Ti.A. Also, build the SELECT clause from list of attributes L by replacing every attribute A of Ri by Ti.A.

Return to Top

Solutions for Section 5.3

Exercise 5.3.1(a)

SELECT maker
FROM Product
WHERE model IN
    (SELECT model
     FROM PC
     WHERE speed >= 160);

SELECT maker
FROM Product
WHERE EXISTS
    (SELECT *
     FROM PC
     WHERE speed >= 160 AND
           Product.model = model);

Notice that the second solution uses a correlated subquery, and ``model'' refers to the more local PC.model unless we explicitly say that the ``model'' of the outer query is wanted by Product.model.

Exercise 5.3.2(b)

SELECT class
FROM Ships
WHERE name IN
    (SELECT ship
     FROM Outcomes
     WHERE result = 'sunk');

SELECT class
FROM Ships
WHERE EXISTS
    (SELECT *
     FROM Outcomes
     WHERE Ships.name = Outcomes.ship AND
           result = 'sunk');

Exercise 5.3.5(a)

SELECT name, address
FROM MovieStar
WHERE gender = 'F' AND
      (name, address) IN
          (SELECT name, address
           FROM MovieExec
           WHERE netWorth > 10000000);

Return to Top

Solutions for Section 5.4

Exercise 5.4.1(a)

SELECT model
FROM PC
WHERE speed >= 150;

As the model number is a key, we do not expect to find duplicates, so DISTINCT is not useful here and could slow down the query.

Exercise 5.4.1(f)

SELECT DISTINCT P1.hd
FROM PC AS P1, PC AS P2
WHERE P1.hd = P2.hd AND P1.model <> P2.model;

Exercise 5.4.1(h)

SELECT DISTINCT P1.maker
FROM Product AS P1, Product AS P2
WHERE P1.maker = P2.maker AND
      P1.model <> P2.model AND
      P1.model IN (
               (SELECT model FROM PC WHERE speed >= 133)
           UNION
               (SELECT model FROM Laptop WHERE speed >= 133)
      ) AND
      P2.model IN (
               (SELECT model FROM PC WHERE speed >= 133)
           UNION
               (SELECT model FROM Laptop WHERE speed >= 133)
      );

We look at all pairs of products, constraining them first to have the same manufacturer, different model numbers, and finally to be ``fast'' computers. The union of the PC's and laptops with speed at least 133 needs to be used twice, so we can constrain the two different model numbers to be ``fast'' computers. The DISTINCT in the outer query is essential.

Exercise 5.4.3(a)

Whichever query form we choose, there could be several products by the same manufacturer that meet the condition of the outer WHERE clause. Thus, DISTINCT in the outer query is advisable.

Exercise 5.4.4(b)

The same observation as for Exercise 5.4.3(a) applies here. There could be several sunken ships of the same class, so we advise DISTINCT in the outer query.

Return to Top

Solutions for Section 5.5

Exercise 5.5.1(a)

SELECT AVG(speed)
FROM PC;

Exercise 5.5.1(f)

SELECT maker, AVG(screen)
FROM Product, Laptop
WHERE Product.model = Laptop.model
GROUP BY maker;

Exercise 5.5.1(i)

SELECT speed, AVG(price)
FROM PC
WHERE speed > 150
GROUP BY speed;

Notice that the condition about speed is not a property of a group, so we do not need a HAVING clause.

Return to Top

Solutions for Section 5.6

Updates to Exercises 5.6.2(c) and (d) solutions 10/14/97.

Exercise 5.6.2(a)

INSERT INTO Classes VALUES('Nelson', 'bb', 'Gt. Britain', 9, 16, 34000);
INSERT INTO Ships VALUES('Nelson', 'Nelson', 1927);
INSERT INTO Ships VALUES('Rodney', 'Nelson', 1927);

Exercise 5.6.2(c)

DELETE FROM Ships
WHERE name IN
    (SELECT ship
     FROM Outcomes 
     WHERE result = 'sunk');

Exercise 5.6.2(d)

UPDATE Classes
SET bore = bore * 2.5,
    displacement = displacement/1.1;

Return to Top

Solutions for Section 5.7

Exercise 5.7.2(f) solution modified 10/14/97.

Exercise 5.7.1

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

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

CREATE TABLE MovieExec (
    name CHAR(30),
    address VARCHAR(255),
    cert# INTEGER,
    netWorth INTEGER
);

CREATE TABLE Studio (
    name CHAR(50),
    address VARCHAR(255),
    presC# INTEGER
);

Exercise 5.7.2(c)

CREATE TABLE Laptop (
    model INTEGER,
    speed INTEGER,
    ram INTEGER,
    hd FLOAT,
    screen FLOAT,
    price INTEGER
);

Exercise 5.7.2(f)

ALTER TABLE Laptop ADD cd CHAR(5) DEFAULT 'none';

Return to Top

Solutions for Section 5.8

Exercise 5.8.1(a)

CREATE VIEW RichExec AS
    SELECT *
    FROM MovieExec
    WHERE netWorth >= 10000000;

Exercise 5.8.3(b)

SELECT RichExec.name
FROM RichExec, StudioPres
WHERE RichExec,name = StudioPres.name;

Exercise 5.8.4

Here are the trees that are the answers to Part (a), Part (b), and Part (c). For part (d), we move the projection onto title and name up, until it is just before the projection onto name, whereupon it becomes useless. Then, we combine the two consecutive selections, for title = ``Gone With the Wind'' and for producerC# = cert#, into one selection.

Return to Top

Solutions for Section 5.9

Revised 6/8/97.

Exercise 5.9.2

Because of our assumption that model numbers are unique, even across different types of product, there are no tuples of PC, Laptop, and Printer that join with each other. Thus, if we take the full, natural outerjoin of these three relations, we shall get the tuples of each, padded out with nulls in the other attributes. This operation is sometimes called the outerunion.

Once we have this outerjoin, we can join it with Product. There are two problems.

The attributes named type from Product and Printer are different, and we need to rename the type from Product.
We want to record information about a model even if it doesn't join with a Product tuple. However, we do not want information about a model from Product if it does not join with a PC, Laptop, or Printer tuple. Thus, we need a left (or right) outerjoin.

Here is the solution:

    (SELECT maker, model, type AS productType FROM Product)
RIGHT NATURAL OUTER JOIN
    ((PC FULL NATURAL OUTER JOIN Laptop) FULL NATURAL OUTER JOIN Printer);

Exercise 5.9.6(a)

We would use a SELECT clause with a list of all the attributes of R followed by all the attributes of S. Then, the FROM clause would be

FROM R, S

Return to Top

Solutions for Section 5.10

Exercise 5.10.2(b)

WITH RECURSIVE Single(class, eclass) AS
        (SELECT class, eclass
         FROM Rel
         WHERE mult = 'single')
    UNION
        (SELECT Single.class, Rel.eclass
         FROM Single, Rel
         WHERE Single.eclass = Rel.class AND
               mult = 'single')

SELECT * FROM Single;

Exercise 5.10.2(c)

WITH RECURSIVE Multi(class, eclass) AS
        (SELECT class, eclass
         FROM Rel
         WHERE mult = 'multi')
    UNION
        (SELECT Multi.class, Rel.eclass
         FROM Multi, Rel
         WHERE Multi.eclass = Rel.class)
    UNION
        (SELECT Rel.class, Multi.eclass
         FROM Multi, Rel
         WHERE Rel.eclass = Multi.class)

SELECT * FROM Multi;

In the above, we start with a connection known to be ``multi'' as the basis. We then allow an arbitrary connection to be attached to the end (the middle term of the union) or the beginning (the last term of the union) of a connection known to have at least one ``multi'' connection. The reader may wish to compare this approach with the approach taken in Exercise 4.4.3(b). Either approach is appropriate for both Datalog and SQL3, except that SQL3 does not support nonlinear recursion as was used in Exercise 4.4.3(b).

Exercise 5.10.3

The nonlinear recursion allows us to double, at each round, the length of the paths used. Thus, after i rounds of recursion, we will have discovered connections due to paths of length 2^{i-1}.

Return to Top