05 02 15

Page 1

DATA BASES 2 – FEBRUARY 5TH, 2015 – TIME: 2H 30M PROF. DANIELE M. BRAGA, PROF. STEFANO PARABOSCHI A. Active Databases (9 p.) The cargo ships of a freight ship company transport containers over many routes across the oceans . Customers book their shipments with constraints on the maximum delivery date: SHIP( ShipId, ShipName ) ROUTE( ShipId, DepartureDate, ArrivalDate, DepPort, ArrPort ) SHIPPINGORDER( CustomerId, NumContainers, DeparturePort, ArrivalPort, OrderDate, MaxDeliveryDate) Design a set of triggers that react to the insertion of a new ShippingOrder by accepting or rejecting it, based on an auxiliary data structure, also managed by the set of triggers: REACHABILITY( ArrivalPort, EarliestDeliveryDate ) The auxiliary table, that is populated for each new order, indicates the earliest date for each destination that can be reached using all planned routes, possibly “chaining” two itineraries and transferring the load from one ship to the other under the condition that the arrival date at the port where the transfer takes place must be at least one day before the next departure date. Only itineraries that are able to deliver within the deadline can be considered. The population of the table should then allow the application to decide whether the order has to be accepted or not. B. XML (10 p.) <!ELEMENT Catalogue ( Ad*, VisitRequest* )> <!ELEMENT Ad ( Apartment, PublishedPrice, Owners, MinimumAcceptablePrice?, MortgageLoan?, … )> <!ATTLIST Ad code ID #REQUIRED PublicationDate CDATA #REQUIRED > <!ELEMENT Owners ( Person+ )> <!ELEMENT Person ( FirstName, LastName, Email, Telephone )> <!ELEMENT VisitRequest ( Person, DateOfRequest, ScheduledDateForTheVisit?, OfferedPriceAfterVisit?, … )> <!ATTLIST VisitRequest AdRef IDREF #REQUIRED > The DTD above describes apartments…