120 likes | 413 Views
UCLA Computer Science Dept. CS240A Fall 2014. Solutions for third Assignment Carlo Zaniolo. 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.
E N D
UCLA Computer Science Dept. CS240A Fall 2014 Solutions for third Assignment Carlo Zaniolo
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.
Oracle The before semantics cannot be used here: it only modifies the original update. After—for each row should be fine.
DB2 The before semantics cannot be used here: it only modifies the original update. After—for each row should be fine.
Check Salary in Oracle and DB2 This is only correct for single tuple updates
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).
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.
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
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)
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;