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