Database Systems: The Complete Book
Solutions for Chapter 6
Revised 11/3/01.

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

Solutions for Section 6.1

Exercise 6.1.1

If they are two different attributes, there will be a comma between them. Remember, in SQL, two names with no punctuation between them usually indicates that the second is an alias for the first.

Exercise 6.1.2(a)

SELECT address
FROM Studio
WHERE name = 'MGM';

Exercise 6.1.2(c)

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 6.1.3(a)

SELECT model, speed, hd
FROM PC
WHERE price < 1200;
The result:
modelspeedhd
100170010
100486610
100870030
101075030
10123507

Exercise 6.1.3(b)

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

Exercise 6.1.3(e)

SELECT *
FROM Printer
WHERE color;
The result:
modelcolortypeprice
3001trueink-jet231
3002trueink-jet267
3004trueink-jet439
3005truebubble200
3006truelaser1999

Exercise 6.1.5(a)

All tuples where either a is 10 or b is 20, or both. There is no restriction on where NULL may appear, except for the above condition.

Exercise 6.1.5(d)

All tuples where a and b have the same, nonnull value.

Return to Top

Solutions for Section 6.2

Exercise 6.2.1(a)

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

Exercise 6.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 6.2.2(a)

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

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

Exercise 6.3.1(a)

Revised 7/9/02. 

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

SELECT maker
FROM Product
WHERE EXISTS
    (SELECT *
     FROM PC
     WHERE speed >= 1200 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 6.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 6.3.5(a)

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

Exercise 6.3.8

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.

  1. The attributes named type from Product and Printer are different, and we need to rename the type from Product.
  2. 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 6.3.11(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 6.4

Exercise 6.4.5

Unfortunately, it might produce duplicates. If the same person has produced several of Harrison Ford's movies, then that person will appear several times in the termporary relation Prod defined in lines (2)--(7) of Fig. 6.11 (p. 270). Then, in the join of MovieExec with Prod, this person's name will appear once for each of those movies.

Exercise 6.4.6(a)

SELECT AVG(speed)
FROM PC;

Exercise 6.4.6(f)

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

Exercise 6.4.6(i)

SELECT speed, AVG(price)
FROM PC
WHERE speed > 800
GROUP BY speed;
Notice that the condition about speed is not a property of a group, so we do not need a HAVING clause.

Exercise 6.4.9

It is possible! In the gamma, we need to produce all the aggregations that the HAVING clause uses. Then, we can follow the gamma by a sigma that eliminated from the result of the gamma the tuples that correspond to the groups that the HAVING would eliminate. Finally, we use pi to get rid of the extra aggregations that were used only by the HAVING clause.

Return to Top

Solutions for Section 6.5

Exercise 6.5.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 6.5.2(c)

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

Exercise 6.5.2(d)

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

Return to Top

Solutions for Section 6.6

Exercise 6.6.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 6.6.2(c)

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

Exercise 6.6.2(f)

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

Return to Top

Solutions for Section 6.7

Exercise 6.7.1(a)

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

Exercise 6.7.3(b)

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

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