E xercise 7.5.2: Write the following as triggers. In each case, disallow or undo
ID: 3708728 • Letter: E
Question
E xercise 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(m aker, model, type)
PC(model, speed, ram, hd, p ric e )
Laptop(model, speed, ram, hd, screen , p rice )
P rin ter(m o d el, co lo r, ty p e, p ric e )
a) When updating the price of a PC, check that there is no lower priced PC
with the same speed.
.
d) When updating the RAM or hard disk of any PC, check that the updated
PC has at least 100 times as much hard disk as RAM.
e) When inserting a new PC, laptop, or printer, make sure that the model
number did not previously appear in any of PC, Laptop, or Printer
Explanation / Answer
Solution:
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;
d)
CREATE TRIGGER RAMorHDupdate on PC
SELECT hd FROM PC WHERE (SELECT ram FROM PC where hd_Size > 100*ram_Size)
e)
CREATE TRIGGER Model Insert SELECT model FROM Product, PC, Printer WHERE model NOT IN (SELECT model FROM Product, PC AND Printer)
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.