Only need parts d & e for Exercise 7.52 Exercise 7.5.2: Write the following as t
ID: 3699641 • Letter: O
Question
Only need parts d & e for Exercise 7.52
Exercise 7.5.2: Write the following as triggers. In each case, disallow or undo the modification if it does not satisfy the stated constraint. The database schema is from the "PC" example of Exercise 2.4.1: Product (maker, model, type) PC (model, speed, ram, hd, price) Laptop (model, speed, ram, hd, screen, price) Printer (model, color, type, price) a) When updating the price of a PC, check that there is no lower priced PC b) When inserting a new printer, check that the model number exists in ! c) When making any modification to the Laptop relation, check that the ! d) When updating the RAM or hard disk of any PC, check that the updated ! e) When inserting a new PC, laptop, or printer, make sure that the model with the same speed Product average price of laptops for each manufacturer is at least $1500 PC has at least 100 times as much hard disk as RAM. number did not previously appear in any of PC, Laptop, or Printer.Explanation / Answer
a) CREATE TRIGGER LowerPriceCheck
AFTER UPDATE OF price ON PC
REFERENCING OLD ROW AS OldTuple
NEW ROW AS NewTuple
FOR EACH ROW
WHEN(NOT(NewTuple.price <= ALL(SELECT priceFROM PCWHERE speed = NewTuple.speed)))
BEGIN
DELETE FROM PC
WHERE (model, speed, ram, hd, rd, price) = NewTuple;
INSERT INTO PC VALUES(OldTuple.model, OldTuple.speed, OldTuple.ram,OldTuple.hd, OldTuple.rd, OldTuple.price);
END;
b) CREATE ASSERTION ModelChecking
AFTER INSERT ON Printer
CHECK (EXISTS
(Select Product.model
FROM Product
WHERE model = Product.model))
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.