TdE BD1 02 07 2021 ENG

Page 1

BASI DI DATI 1 – PROFF. S. CERI, S. COMAI, L. TANCA – A. A. 2020/21 APPELLO DEL 2 LUGLIO 2021 – DURATA DELLA PROVA: 90 m Write the solutions of the two parts on TWO DISTINCT SHEETS, both including your name and details Part 1: QUERY LANGUAGES (on a separate sheet / file w.r.t. Part 2) The following schema stores information about the main cycling tours (Giro d’Italia, Tour de France and Vuelta a España). Each year there is a different edition of the tours in the respective countries. TOUR (TourID, Country, Year, Logo, Description, NumberOfStages) STAGE (TourID, StageNumber, Date, Departure City, Arrival City, ElevationGain) CYCLIST (CyclistID, Name, Surname, Country, Team) % Country indicates the country the cyclist comes from PARTICIPATE (TourID, StageNumber, CyclistID, Time) A. SQL (14 points) 1. Specify the commands for creating the PARTECIPATE table; define the tuple and domain constraints deemed appropriate and express any referential integrity constraints towards the other tables. (2pts) 2. Extract CyclistID, name and surname of the cyclists who have never participated to tours with stages with an elevation gain greater than 5000 meters. (4pts) 3. For each edition of each Tour, extract CyclistID, name and surname of the cyclist who won it (i.e. who took the shortest time overall). Assume that the time is an integer (expressing the number of seconds of each participation) and that all the cyclists complete the last stage. (4pts) 4. Express the constraint that verifies that there are no two stages with the same starting city in the same edition of a Tour. (4pts) B. Formal languages (4 points) 5. Extract CyclistID, name and surname of the cyclists who have participated at least twice in a tour in their country (in two formal languages of your choice). Part 2: DB…