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
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
AFTER
INSERT ON Printer
CHECK (EXISTS
(Select Product.model
FROM Product
WHERE model = Product.model))
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