A First Course in Database Systems

## Solutions for Chapter 8

Last change: 12/2/97 Solutions for Section 8.1

## Solutions for Section 8.1

### Exercise 8.1.1(a)

Add to the definition of `Product` the declaration
```     lowerPrice(inout real);
```

### Exercise 8.1.1(b)

Add to the definition of `Product` the declaration
```     real getSpeed() raises notComputer;
```

## Solutions for Section 8.2

### Exercise 8.2.1(a)

```     SELECT p.model
FROM PCs p
WHERE p.price < 2000
```

### Exercise 8.2.1(c)

```     SELECT DISTINCT lp1.manufacturer
FROM Printers lp1, Printers lp2
WHERE lp1.manufacturer = lp2.manufacturer AND
lp1.printerType = "laser" AND
lp2.printerType = "laser" AND
lp1.model != lp2.model
```

### Exercise 8.2.2(a)

```     SELECT p.model
FROM (SELECT x FROM PCs x WHERE x.price < 2000) p
```

### Exercise 8.2.2(c)

```     SELECT DISTINCT lp1.manufacturer
FROM (SELECT x FROM Printers x WHERE x.printerType = "laser") lp1,
(SELECT y FROM Printers y WHERE y.manufacturer = lp1.manufacturer
AND y.printerType = "laser"
AND y.model != lp1.model) lp2
```
Notice that the second collection in the `FROM` clause is empty unless there is a second laser printer by the manufacturer of `lp1`.

## Solutions for Section 8.3

### Exercise 8.3.1(a)

```     SELECT pc.manufacturer
FROM PCs pc
WHERE EXISTS pr IN Printers :
pr.manufacturer = pc.manufacturer
```

### Exercise 8.3.1(b)

```     SELECT pc1.manufacturer
FROM PCs pc1
WHERE FOR ALL pc2 IN PCs :
pc2.hd >= 2.0 OR
pc2.manufacturer != pc1.manufacturer
```
Notice that by requiring of every PC `pc2` that it either be by a different manufacturer or have the requisite hard disk, we are saying that every PC by the manufacturer of `pc1` has enough hard disk. The fact that `pc1` exists guarantees that its manufacturer manufactures at least one PC.

### Exercise 8.3.1(d)

```     AVG(SELECT p.speed FROM PCs p)
```

### Exercise 8.3.1(e)

```     SELECT cdSpeed, AVG(SELECT part.pc.ram
FROM partition part)
FROM PCs pc
GROUP BY cdSpeed: pc.cd
```

### Exercise 8.3.2(d)

```     SELECT class, COUNT(SELECT part.s
FROM partition part
WHERE EXISTS outcome IN Outcomes :
outcome.theShip = part.s AND
outcome.status = sunk)
FROM Ships s
GROUP BY class: s.classOf
HAVING MIN(SELECT part.s.launched FROM partition part) < 1919
```
Starting from the `FROM` clause, we look at the ships s, and group them by class. The `HAVING` clause eliminates those groups (which are the classes) whose minimum launch date is 1919 or later. The result, expressed by the `SELECT` clause is a table giving the class and a count of a certain set. This set is obtained by looking at a group of ships and determining for each ship s whether that ship appears in an Outcome object with the status "sunk." We do not bother with `DISTINCT` when counting these ships, because it should be impossible for a ship to be sunk twice and therefore no ship s appears in two Outcome objects.

## Solutions for Section 8.4

### Exercise 8.4.1(a)

```     x = Set(1,2,3);
```

### Exercise 8.4.2(a)

```     x = SELECT p
FROM PCs p
WHERE p.model = 1000;
```

### Exercise 8.4.3(a)

```     colorado = Ship(name: "Colorado",
launched: 1923,
classOf: marylandClass
)
```
Here, we assume that `marylandClass` is a variable holding the class object for the ship-class "Maryland".

Note: the question is actually wrong, and should have read ``ship Maryland of the Colorado class.'' We have, however, left the question in the book as is and changed the answer on 12/2/97.

## Solutions for Section 8.5

### Exercise 8.5.1(b)

```     CREATE ROW TYPE PersonType (
name    NameType,
mother  REF(PersonType),
father  REF(PersonType)
);
```
The type `NameType` is not defined here. Its definition is Exercise 8.5.1(a).

### Exercise 8.5.5

```     CREATE ROW TYPE ClassType (
class   CHAR(20),
type    CHAR(2),
country CHAR(20),
numGuns INT,
bore    REAL,
disp    INT
);

CREATE ROW TYPE ShipType (
name    CHAR(20),
class   REF(ClassType),
launch  INT
);

CREATE ROW TYPE BattleType (
name    CHAR(20),
theDate DATE
);

CREATE ROW TYPE OutcomeType (
ship    REF(ShipType),
battle  REF(BattleType),
result  CHAR(10)
);

CREATE TABLE Classes OF TYPE ClassType;

CREATE TABLE Ships OF TYPE ShipType
SCOPE FOR class IS Classes;

CREATE TABLE Battles OF TYPE BattleType;

CREATE TABLE Outcomes OF TYPE OutcomeType
SCOPE FOR ship IS Ships
SCOPE FOR battle IS Battles;
```

### Exercise 8.5.6(a)

```     SELECT name
FROM Ships
WHERE class->disp > 35000;
```

## Solutions for Section 8.6

### Exercise 8.6.1

```     CREATE TYPE PCType (
speed   INT,
ram     INT,
hd      REAL,
cd      CHAR(5),
price   REAL
EQUALS DEFAULT,
LESS THAN NONE
);
```

### Exercise 8.6.2(a)

(Solution revised 12/6/98)
```      FUNCTION newPC(:s INT, :r INT, :h REAL, :c CHAR(5), :p REAL)
RETURNS PCType;

:pc PCType;

BEGIN
:pc = PC();
:pc.speed = :s;
:pc.ram = :r;
:pc.hd = :h;
:pc.cd = :c;
:pc.price = :p;
RETURN(:pc);
END;
```

### Exercise 8.6.2(b)

```     FUNCTION value(:pc PCType) RETURNS REAL;

RETURN(:pc.speed + 5*:pc.ram + 50*:pc.hd + 10*:pc.cd);
```