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 9.2.1(d)

AVG(SELECT p.speed FROM PCs p)

Exercise 9.2.1(e)

SELECT cdSpeed, AVG(SELECT part.pc.ram FROM partition part) FROM PCs pc

GROUP BY cdSpeed: pc.cd

Exercise 9.2.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 \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 9.3

Exercise 9.3.1(a)

x = Set(1,2,3);

Exercise 9.3.2(a)

x = SELECT p FROM PCs p

WHERE p.model = 1000;

Exercise 9.3.3(a)

colorado = Ship(name: \ launched: 1923,

classOf: marylandClass )

Here, we assume that marylandClass is a variable holding the class object for the

ship-class \

Solutions for Section 9.4

Exercise 9.4.1(b)

CREATE TYPE PersonType AS ( name NameType,

mother REF(PersonType), father REF(PersonType) );

The type NameType is not defined here. Its definition is Exercise 9.4.1(a).

Exercise 9.4.5

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

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

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

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

CREATE TABLE Classes OF ClassType ( REF IS classID SYSTEM GENERATED );

CREATE TABLE Ships OF ShipType

REF IS shipID SYSTEM GENERATED );

CREATE TABLE Battles OF TYPE BattleType REF IS battleID SYSTEM GENERATED );

CREATE TABLE Outcomes OF TYPE OutcomeType;

Solutions for Section 9.5

Exercise 9.5.1(a)

SELECT star->name FROM StarsIn

WHERE movie->title = 'Ishtar';

Exercise 9.5.1(b)

Revised 6/6/03.

SELECT DISTINCT movie->title, movie->year FROM StarsIn

WHERE star->address.city() = 'Malibu';

Exercise 9.5.3(a)

SELECT s.name() FROM Ships s

WHERE s.class() -> disp > 35000;

Exercise 9.5.5

CREATE PROCEDURE DeleteStar(IN n CHAR(30))

BEGIN

DELETE FROM StarsIn WHERE star->name = n;

DELETE FROM MovieStar m WHERE m.name() = n; END;

Database Systems: The Complete Book

Solutions for Chapter 10

Solutions for Section 10.1

Exercise 10.1.1(a)

Answer(model) <- PC(model,speed,_,_,_,_) AND speed >= 1000

Exercise 10.1.1(f)

Answer(hd) <- PC(m1,_,_,hd,_,_) AND PC(m2,_,_,hd,_,_) AND m1 <> m2 Notice how Datalog allows us to express equality conditions by using the same

variable, e.g., hd, twice.

Exercise 10.1.1(h)

FastComputers(M) <- PC(M,S,_,_,_,_) AND S >= 700 FastComputers(M) <- Laptop(M,S,_,_,_,_) AND S >= 700

Answer(maker) <- Product(maker,m1,_) AND Product(maker,m2,_) AND

FastComputers(m1) AND FastComputers(m2) AND m1 <> m2

Solutions for Section 10.2

Exercise 10.2.1(d)

The following uses X as a temporary predicate to represent R UNION S. X(a,b,c) <- R(a,b,c) X(a,b,c) <- S(a,b,c)

Answer(a,b,c) <- X(a,b,c) AND NOT T(a,b,c)

Exercise 10.2.1(g)

This expression asks for those (a,b) pairs that are both the first two components of a tuple from R and the last two components of a tuple from S. Thus, the following rule works.

Answer(a,b) <- R(a,b,_) AND S(_,a,b)

A more mechanical solution would be something like: X(a,b) <- R(a,b,_) Y(a,b) <- S(_,a,b)

Answer(a,b) <- X(a,b) AND Y(a,b)

Exercise 10.2.2(b)

Answer(x,y,z) <- R(x,y,z) AND x < y AND y < z

Exercise 10.2.2(e)

We need to work on this using DeMorgan's laws to put the expression into conjunctive normal form. First, we push the NOT through the AND, which ``flips'' the AND to an OR, giving us:

(NOT(xy)) OR (NOT(y<>

The second NOT flips < to >=.

The first NOT is pushed through the first OR, flipping it to AND:

((NOT(xy))) OR y>=z

Finally, push the remaining NOT's into the literals:

(x>=y AND y>=x) OR y>=z

In this case, we can observ esomething special about the left side of the OR. The only way for x>=y and y>=x both to hold is if x=y. Thus, our final form is:

x=y OR y>=z

This condition can be expressed by two rules:

Answer(x,x,z) <- R(x,x,z)

Answer(x,y,z) <- R(x,y,z) AND y >= z

Notice that the first rule is a more succinct way of writing

Answer(x,y,z) <- R(x,y,z) AND x = y

Exercise 10.2.4(b)

Answer(rx,ry,rz,sx,sy,sz) <- R(rx,ry,rz) AND S(sx,sy,sz) AND rx < sy AND ry < sz

Exercise 10.2.4(e)

Taking advantage of the expression simplification we did for Exercise 4.3.2(e):

Product(rx,ry,rz,sx,sy,sz) <- R(rx,ry,rz) AND S(sx,sy,sz)

Answer(rx,ry,rz,sx,rx,sz) <- Product(rx,ry,rz,sx,rx,sz)

Answer(rx,ry,rz,sx,sy,sz) <- Product(rx,ry,rz,sx,sy,sz) AND ry >= sz

Notice that rx appears four times in the first rule above, thus