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.

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

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.

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;

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

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

Database Systems: The Book

Solutions for Chapter 7

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)

Complete