Exercise 7.5.2: Write the following as triggers. In each case, disallow or undo

Question

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 with the same speed. b) When inserting a new printer, check that the model number exists in Product.

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

• Browse (All)
• Browse E
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.