12 02 2024 E T

Page 1

Check if 30% reduction: ◻ Last name: First name: Pers. code: Signature: Databases 2 - exam - February 12, 2024 - Dur. 2h S. Comai, P. Fraternali, D. Martinenghi Students with 30% reduction can omit: Question B.2. A. Concurrency control (8 points) Apply the distributed deadlock detection algorithm to the following wait-for conditions using two conven- tions: 1) a dependency ti → tj can be transmitted (forward) if i > jif i > jif i > jif i > jif i > jif i > jif i > jif i > jif i > jif i > jif i > jif i > jif i > jif i > jif i > jif i > jif i > j; 2) a dependency ti → tj can be transmitted (forward) if i < jif i < jif i < jif i < jif i < jif i < jif i < jif i < jif i < jif i < jif i < jif i < jif i < jif i < jif i < jif i < jif i < j; • Node A: EC → t4; t4 → t7; t7 → t5; t5 → EB; • Node B: EA → t5; t5 → t6; t6 → t1; t1 → EC; • Node C: EB → t1; t1 → t3; t3 → t2; t3 → t4; t4 → EA. For each convention, execute all the steps, reporting all the messages that are sent during the process. B. T riggers (12 points) Consider the following relational schema: PROJECT(PIDPIDPIDPIDPIDPIDPIDPIDPIDPIDPIDPIDPIDPIDPIDPIDPID, name, duration) EMP(EIDEIDEIDEIDEIDEIDEIDEIDEIDEIDEIDEIDEIDEIDEIDEIDEID, name, salary) ASSIGNMENT(PID, EIDPID, EIDPID, EIDPID, EIDPID, EIDPID, EIDPID, EIDPID, EIDPID, EIDPID, EIDPID, EIDPID, EIDPID, EIDPID, EIDPID, EIDPID, EIDPID, EID) where PID and EID in ASSIGNMENT are under foreign key constraints to PROJECT and EMP, respectively. Your task is to define a set of triggers that maintain a table BUDGET(PIDPIDPIDPIDPIDPIDPIDPIDPIDPIDPIDPIDPIDPIDPIDPIDPID, 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.PID LEFT JOIN emp E ON…