Computer Engineering - Data Bases 2

Full exam

Databases 2 - exam - February 12, 2024 -Dur. 2h S. Comai, P. Fraternali, D. Martinenghi A. Triggers (12 points) Consider the following relational schema: PROJECT(PID PID PID PID PID PID PID PID PID PID PID PID PIDPID PID PID PID, name, duration) EMP(EID EID EID EID EID EID EID EID EID EID EID EID EIDEID EID EID EID, name, salary) ASSIGNMENT(PID, EID PID, EID PID, EID PID, EID PID, EID PID, EID PID, EID PID, EID PID, EID PID, EID PID, EID PID, EID PID, EIDPID, EID PID, EID PID, EID PID, EID) wherePIDandEIDinASSIGNMENTare under foreign key constraints toPROJECTandEMP, respectively. Your task is to dene a set of triggers that maintain a tableBUDGET(PID PID PID PID PID PID PID PID PID PID PID PID PIDPID PID PID PID, cost)with the same content as would be maintained by the following view: CREATE VIEW budget AS SELECT P.PID, COALESCE (SUM(salary), 0) AS cost FROM project P LEFT JOIN assignment A ON P.PID = A.PIDLEFT JOIN emp E ON E.EID = A.EID GROUP BY P.PID; where theCOALESCE()function returns the rst non-null value in its list of comma-separated arguments. Assume that the primary key values are not updated and that each employee is assigned to no more than one pro ject. 1.Complete the table by indicating, for each event and table, whether the maintenance of the content oftheBUDGETtable requires the implementation of a trigger. If so, write the name of the trigger (e.g., T1,T2, etc.), otherwise provide a justication (3 points). 2.Dene the code of such triggers (9 points).PROJECTEMPASSIGNMENT INSERT◻ Yes { No◻Name:◻ Yes { No◻Name:◻ Yes { No◻Name:UPDATE◻ Yes { No◻Name:◻ Yes { No◻Name:◻ Yes { No◻Name:DELETE◻ Yes { No◻Name:◻ Yes { No◻Name:◻ Yes { No◻Name:Solution. PROJECTEMPASSIGNMENT INSERTYes { Name: T1NoYes { Name: T4 UPDATENoYes { Name: T3No DELETEYes { Name: T2NoYes { Name: T5 � There is no need of triggers for the updates onPROJECTandASSIGNMENTtables because primary keys are not updated (see assumptions in the text). �No trigger is required for insertions and deletions into theEMPtable, as only assignments aect pro ject costs. 1.CREATE TRIGGER T1 AFTER INSERT ON PROJECT FOR EACH ROW INSERT INTO BUDGET VALUES(NEW.PID, 0); 2.CREATE TRIGGER T2 AFTER DELETE ON PROJECT FOR EACH ROW DELETE FROM BUDGET WHERE PID = OLD.PID; 3. CREATE TRIGGER T3 AFTER UPDATE OF SALARY ON EMP FOR EACH ROW UPDATE BUDGET SET COST = COST + NEW.SALARY - OLD.SALARY WHERE PID = (SELECT PID FROM ASSIGNMENT WHERE EID = NEW.EID); 4.CREATE TRIGGER T4 AFTER INSERT ON ASSIGNMENT FOR EACH ROW UPDATE BUDGET SET COST = COST + (SELECT SALARY FROM EMP WHERE EID = NEW.EID) WHERE PID = NEW.PID; 5.CREATE TRIGGER T5 AFTER DELETE ON ASSIGNMENT FOR EACH ROW UPDATE BUDGET SET COST = COST - (SELECT SALARY FROM EMP WHERE EID = OLD.EID) WHERE PID = OLD.PID; B. Concurrency control (8 points) Apply the distributed deadlock detection algorithm to the following wait-for conditions using two conven- tions:1) a dependencyt i→ t jcan be transmitted (forward)if i>j ifi>j ifi>j ifi>j ifi>j ifi>j ifi>j ifi>j ifi>j if i>j if i>j if i>j if i>j if i>j if i>j if i>j ifi>j; 2) a dependencyt i→ t jcan be transmitted (forward)if i