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 )

c) When making any modification to the Laptop relation, check that the average price of laptops for each manufacturer is at least $1500.

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

C)

CREATE TRIGGER Laptop

SELECT avg(price), manufacturer FROM Laptop

Declare avg = SELECT price FROM Laptop if(avg<1500)

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)

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

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