Q3 [1 pt]: Write the following as triggers for the following schema. In each cas
ID: 3591738 • Letter: Q
Question
Q3 [1 pt]: Write the following as triggers for the following schema. In each case disallow or undo the modification if it does not satisfy the stated constraint. Product(maker, model, type) PC(model, speed, ram, hd, price) * hd: hard disk 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 product? c) When making any modification to the laptop relation, check that the average price of laptop for each manufacturer is at least S1,500? 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?Explanation / Answer
Below are the triggers. Since no data is given for the table contents hence its possible to provide sample output.
a)CREATE OR REPLACE TRIGGER TRG_PC
BEFORE UPDATE OF price ON PC
FOR EACH ROW
DECLARE
V_price INTEGER;
BEGIN
SELECT min(price) into v_price FROM PC WHERE speed = :new.speed;
IF v_price <= :new.price
THEN
RAISE_APPLICATION_ERROR(-20101, 'PC with lower price currently exists.');
ROLLBACK;
END IF;
END;
/
b)CREATE OR REPLACE TRIGGER TRG_PRINTER
BEFORE INSERT ON PRINTER
FOR EACH ROW
DECLARE
V_test INTEGER;
BEGIN
SELECT 1 into v_test FROM PRODUCT A JOIN PRINTER B ON A.model=B.model WHERE B.model = :new.model;
IF v_test <> 1
THEN
RAISE_APPLICATION_ERROR(-20101, 'Printer model currently does not exist in product table .');
ROLLBACK;
END IF;
END;
/
c.CREATE OR REPLACE TRIGGER TRG_LAPTOP
BEFORE INSERT or UPDATE or DELETE ON LAPTOP
FOR EACH ROW
DECLARE
V_price INTEGER;
BEGIN
SELECT avg(price) into v_price FROM LAPTOP where model= :new.model ;
IF v_price < 1500
THEN
RAISE_APPLICATION_ERROR(-20101, 'Price is less than 1500.');
ROLLBACK;
END IF;
END;
/
d.CREATE OR REPLACE TRIGGER TRG_RAM_HD
BEFORE UPDATE OF ram,hd ON PC
FOR EACH ROW
DECLARE
V_price INTEGER;
BEGIN
v_price := (100*new.ram);
IF new.hd < v_price
THEN
RAISE_APPLICATION_ERROR(-20101, 'HD space is less than 100 times of RAM');
ROLLBACK;
END IF;
END;
/
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.