A First Course in Database Systems

Revised 12/9/00.

## Solutions for Chapter 5

Solutions for Section 5.1

## 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:
modelspeedhd
10011331.6
10021201.6
10101601.2

### Exercise 5.1.3(b)

```SELECT model, speed AS megahertz, hd AS gigabytes
FROM PC
WHERE price < 1600;
```
The result:
modelmegahertzgigabytes
10011331.6
10021201.6
10101601.2

### Exercise 5.1.3(e)

```SELECT *
FROM Printer
WHERE color;
```
The result:
modelcolortypeprice
3001trueink-jet275
3002trueink-jet269
3006truedry470

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

## 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
FROM MovieExec
WHERE netWorth > 10000000);
```

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

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

## 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;
```

## 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),
cert# INTEGER,
netWorth INTEGER
);

CREATE TABLE Studio (
name CHAR(50),
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';
```

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

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

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

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