Solutions HW4

6.4.3

a. CREATE ASSERTION NoLaptop CHECK

(NOT EXISTS

(SELECT *

FROM Product, PC

WHERE Product.model = PC.model and maker IN

(SELECT maker

FROM Product, Laptop

WHERE Product.model = Laptop.model)))

 

b. CREATE ASSERTION speeds CHECK

(NOT EXISTS

(SELECT *

FROM Product P1, PC

WHERE Product.model = PC.model and speed > ALL

(SELECT speed

FROM Product, Laptop

WHERE Product.model = Laptop.model and Product.maker = P1.maker)))

 

6.6.2

  1. CREATE TRIGGER noLowerPrice
  2. AFTER UPDATE OF price ON PC

    REFERENCING

    OLD AS OldTuple

    NEW AS NewTuple

    WHEN (OldTuple.price < NewTuple.price)

    UPDATE PC

    SET price = OldTuple.price

    WHERE speed = OldTuple.speed

    FOR EACH ROW

     

  3. CREATE ASSERTION modelPresent
  4. AFTER

    INSERT ON Printer

    CHECK (EXISTS

    (Select Product.model

    FROM Product

    WHERE model = Product.model))

  5. CREATE TRIGGER averagePrice

INSTEAD OF

UPDATE OF price OF Laptop

INSERT ON Laptop

DELETE ON Laptop

REFERENCING

OLD_TABLE AS OldStuff

NEW_TABLE AS NewStuff

WHEN( 2000 <= ALL

(SELECT AVG(price)

FROM (Laptop EXCEPT OldStuff) UNION NewStuff, Product

WHERE Laptop.model = Product.model

GROUP BY maker ))

DELETE FROM Laptop

WHERE (model, speed, ram, hd, screen, price) IN OldStuff

INSERT INTO Laptop

(SELECT * FROM NewStuff);

 

3.1 sorted file directly- it's already sorted in exactly the order we want

3.2 hashed index- hashed index expects to find the value in 1 lookup

3.3 B+ index- we'd waste accesses between 50000 and 50010 with accessing the file directly

3.4 B+ index, although sorted would work as well