Database Systems: The Complete Book
Solutions for Chapter 9

Solutions for Section 9.1
Solutions for Section 9.2
Solutions for Section 9.3
Solutions for Section 9.4
Solutions for Section 9.5

Solutions for Section 9.1

Exercise 9.1.1(a)

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

Exercise 9.1.1(b)

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

Exercise 9.1.2(a)

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

Exercise 9.1.2(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 9.1.4(a)

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

Exercise 9.1.4(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.

Return to Top

Solutions for Section 9.2

Exercise 9.2.1(a)

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

Exercise 9.2.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 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 "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.

Return to Top

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: "Colorado",
                     launched: 1923,
                     classOf: marylandClass
                    )
Here, we assume that marylandClass is a variable holding the class object for the ship-class "Maryland".

Return to Top

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;

Return to Top

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;

Return to Top