21 02 20

Page 1

DATA BASES 2 – FEBRUARY 21ST, 2020 – DURATION: 2H PROF. SARA COMAI, PROF. DANIELE M. BRAGA A. Active Databases (8 p.) Consider the following schema: STUDENT (StudID, Campus GroupAorB, TotalCFU) EXAM (StudID, CourseID, Date, Grade) COURSE (CourseID, Professor, CFU) Write a set of triggers that every time that an exam is inserted or deleted, update the total number of CFUs acquired by the student and classifies him/her as “A” if the weighted average grade of the student is greater than 27, as “B” o therwise. Assume grades in the interval [18, 30]; in the weighted average, the number of CFUs is taken into account, so that 10 CFU exams weigh twice as much as 5 CFU exams. Write the rules both with a) the “naïve” and b) the “incremental” approach, possibly extending the schema, if needed. B Concurrency Control (7 p.) Consider the following waiting conditions on the nodes of a distributed database system: Node A: T3  T2 ; EC  T1 ; T1  T3 ; T2  EB ; T1  T7 ; ED  T7 ; Node B: EA  T2 ; T2  T6 ; T6  T4 ; T4  EC ; T6  T5 ; T5  EC ; Node C: EB  T4 ; T4  ED ; EB  T5 ; T5  T1 ; T1  EA ; Node D: EC  T4 ; T4  T7 ; T7  EA Simulate the Obermarck algorithm (with the situation at the four nodes) and indicate whether there is a distributed deadlock. List the messages transmitted between nodes. C. XML (9 p.) <!ELEMENT Polifly (Line+ )> <!ELEMENT Line (Company, DepAirport, ArrAirport, DepTime, ArrTime, Flight* )> <!ATTLIST Line id ID #REQUIRED> <!ELEMENT Flight (Date, PlaneInfo, Ticket*, ActualDepTime?, ActualArrTime? )> <!ELEMENT Ticket ( PassengerName, Price, SeatRow, SeatColumn )> This DTD describes international flights and their tickets. An Id identifies a specific line operated at a certain time – as example consider AZ120, the daily MilanAmsterdam (LINAMS) flight…