A First Course in Database Systems

Solutions for Chapter 8

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

Solutions for Section 8.2

Solutions for Section 8.3

Solutions for Section 8.4

Solutions for Section 8.5

Solutions for Section 8.6

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;

Return to Top

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.

Return to Top

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.

Return to Top

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.

Return to Top

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;

Return to Top

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);

Return to Top