UCLA Computer Science Dept. CS240A Fall 2014

1. UCLA Computer Science Dept. CS240A Fall 2014 Solutions for third Assignment Carlo Zaniolo

2. Question from notes The following question was left open in the notes: What happens if we change the FK corrective action to: FOREIGN KEY (Supplier) REFERENCES Distributor ON DELETE SET NULL Answer: Signal stops the chain of actions but let the transaction go. Thus it the original deletion is voided, and the transaction completes, with only a warning message sent to user.

4. Starbust

5. Oracle The before semantics cannot be used here: it only modifies the original update. After—for each row should be fine.

6. DB2 The before semantics cannot be used here: it only modifies the original update. After—for each row should be fine.

7. Check Salary in Oracle and DB2 This is only correct for single tuple updates

8. Salary-control: multiple updates Assume three employees: Mary, managed by Pam who reports to Tom. Update statements: All females receive a 10K salary raise. The current salaries are: Mary 60k, Pam 65k, Tom 68k. For alternative semantics: (statement|row)& (before|after) Which is correct (i.e., Preserves the IC) which also achieves confluence? Four activation semantics • Statement&before: Mary 65k, Pam 68k. IC preserved. • Row&before: same as 1 if Mary is processed first. But if Pam is done first both Pam and Mary get 68k. (IC ok, but no confluence) • Statement&after: Violation is only detected for Pam who gets 68k. No action on Mary who keeps 75k: the IC is violated. • Row&after: Same as 3 if Mary is processed first (thus IC is violated) but same as 2 if Pam is processed first (Pam and Mary both get 68k).

9. DELETE FROM Distributor WHERE State =`CA’ FOREIGN KEY (Supplier) REFERENCES DistributorON DELETE SET DEFAULT HDD NULL CREATE TRIGGER OneSupplier BEFORE UPDATE OF Supplier ON Part REFERENCING NEW AS N FOR EACH ROW WHEN (N.Supplier IS NULL) SIGNAL SQLSTATE … CREATE TRIGGER Audit AFTER UPDATE ON Part REFERENCING OLDTABLE AS OT FOR EACH STATEMENT INSERT INTO AuditSupplier VALUES(USER, CURRENTDATE, (SELECT COUNT(*) FROM OT)) Answer: Signal stops the chain of actions but let the transaction go. Thus, the original deletion is voided, and the transaction completes with a warning message sent to user.

10. Example Trigger in Oracle:Reorder Rule CREATE TRIGGER Reorder AFTER UPDATE OF PartOnHand ON Inventory WHEN (New.PartOnHand < New.ReorderPoint FOR EACH ROWDECLARE NUMBER X BEGIN SELECT COUNT(*) INTO X FROM PendingOrders WHERE Part = New.Part; IF X=0 THEN INSERT INTO PendingOrders VALUES (New.Part, New.OrderQuantity, SYSDATE) END IF; END; PL/SQL

11. DB2 Rules on PendingOrders instead of PL/SQL Original rule revised: CREATE TRIGGER Reorder AFTER UPDATE OF PartOnHand ON Inventory WHEN (New.PartOnHand < New.ReorderPoint FOR EACH ROW INSERT INTO PendingOrders VALUES (New.Part, New.OrderQuantity, SYSDATE)

12. Revised DB2 rules on PendingOrders: an order for 1/2 of the requested OrderQuantity whenever there is already a pending order for the part. CREATE TRIGGER nodup BEFORE INSERT ON PendingOrders REFERENCING NEW AS N FOR EACH ROW WHEN (N.Part in (SELECT Part FROM PendingOrders)) SIGNAL SQLSTATE '70001' (’Duplicate Pending Order '); CREATE TRIGGER halfquantity BEFORE INSERT ON PendingOrders REFERENCING NEW AS N FOR EACH ROW WHEN(N.Part in (SELECT Part FROM PendingOrders)) SET N.OrderQuantity = 0.5 * N.OrderQuantity;