11 09 19

Pagina 1

DATA BASES 2 – SEPTEMBER 11TH, 2019 – DURATION: 2H 15M PROF. SARA COMAI, PROF. DANIELE M. BRAGA A. Active Databases (10 p.) Consider the following database for a bike-sharing service (some data are replicated to simplify triggers/queries). Each rental requires a pick up request and a return operation, identified by the same RentalID. BIKE (BikeNumber, Type, CostPerHour, CurrentStationID) USER (UserID, Name, Address, ChargedAmount) PICKUPREQUEST (RentalID, UserID, BikeNumber, PickUpStationID) RETURN (RentalID, BikeNumber, ReturnStationID) RENTAL (RentalID, UserID, BikeNumber, StartingTimeStamp, ReturnTimeStamp) A) Users can pick up bikes at any station , but they can have at most 3 active rentals at the same time . If a bike request violates this rule, an application error is reported. Otherwise, the rental starts: a new record is inserted in to the RENTAL table with the pickup data and the CurrentStationID for that bike is set to null. B) When the user returns the bike, the RENTAL record is completed with the re stitution timestamp; also the CurrentStationID of the bike is updated . C) The current amount of the user is decreased for the hours of use, according to the cost per hour of the rented bicycle. However, if the bicycle presents some problems and is replaced in the same station within 3 minutes, no charges apply. Design a set of triggers implementing the above rules. Suppose the existence of a function INT Difference(TS Time1, TS Time2) returning the number of minutes between two timestamps. SYSDATE() returns the current timestamp. B. Reliability Control of Distributed Systems (5 p.) Given the following log found on a node of a distributed transactional system (with the following abbreviations for the records relative to the distributed recovery protocol: p =…