17 01 19

Page 1

DATA BASES 2 – JANUARY 17TH, 2019 – DURATION: 2H 15M PROF. SARA COMAI, PROF. DANIELE M. BRAGA A. Active Databases (10 p.) ROUTE (ROUTEID, FROM, TO, TIME, TOTALNUMBEROFSEATS, COST) REQUEST (USEREMAIL, ROUTEID, DATE, NUMBEROFSEATS, USECANCELLATIONBONUS, ACCEPTED) BOOKING (USEREMAIL, ROUTEID, DATE, NUMBEROFSEATS, USECANCELLATIONBONUS, TOTALCOST) CANCELLATIONBONUS (USEREMAIL, STARTINGDATE, BONUSVALUE) The schema above describes the ticketing system of a low -cost bus company . Design a set of triggers to im plement the following behavior. Whenever a new request arrives, availability of seats for the requested trip is checked: if seats are sufficient, it is accepted, and the Accepted attribute (initially always equal to null) is set to true; otherwise, Accepted is set to false. If the request is accepted, then it is added to BOOKING, and the total cost is calculated, possibly using bonuses. If a booking is deleted, a cancellation bonus is emitted, for a value equal to 90% of the TotalCost and valid for one year from the current date [management of the bonus expiration can be omitted for brevity]. If a user gets more than one bonus in the same day, their values are summed up. The bonus can be used for other trips , if this is specified in the request. In such a case, the value of the bonus is deducted from the total cost and the bonus value is consequently updated. If the user has more than one bonus, the oldest one is used first, and if the bonus is smaller than the amount to be payed, other bonuses of that user can be used, until the total cost is reached or the bonuses end. If a bonus is completely used, it is deleted. B Concurrency Control (5 p.) Classify this schedule w.r.t. VSR, CSR, 2PL, 2PL-strict, TS-mono, TS-multi, giving terse but precise justifications: r1(A)…