12 02 2024 E TS

Page 1

Databases 2 - exam - February 12, 2024 - Dur. 2h S. Comai, P. Fraternali, D. Martinenghi A. 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 E.EID = A.EID GROUP BY P.PID; where the COALESCE() function returns the first 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 project. 1. Complete the table by indicating, for each event and table, whether the maintenance of the content of the BUDGET table requires the implementation of a trigger. If so, write the name of the trigger (e.g., T1, T2, etc.), otherwise provide a justification (3 points). 2. Define the code of such triggers (9 points). PROJECT EMP ASSIGNMENT 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. PROJECT EMP ASSIGNMENT INSERT Yes – Name: T1 No Yes – Name: T4 UPDATE No Yes – Name: T3 No DELETE Yes – Name: T2…