TdE BD1 12 01 2022 ENG

Page 1

DATABASE 1 – PROFF. S. CERI, S. COMAI, L. TANCA – A. A. 2021/22 EXAM SESSION OF JANUARY 12TH 2022 – DURATION: 90 min Write the solutions of the two parts on two separate sheets/fils both named Part 1: QUERY LANGUAGES (on a separate sheet / file from Part 2) The following schema stores information about the Winter Olympic Games. ATHLETE (AthleteFiscalCode, Name, Surname, Nationality, Birthdate) COMPETITION (CompetitionID, Sport, Date, IsItTheFinal) PARTICIPATION (AthleteFiscalCode, CompetitionID, Time) A. SQL (15 points) 1. Specify the commands for creating the PARTICIPATION table, defining the tuple and domain constraints deemed appropriate and expressing any referential integrity constraints towards the other tables. (2pts) 2. Find the FiscalCode, Name and Surname of the athletes that have taken part in at least two competitions, but never in a Final. (4pts) 3. For each Nation, show the number of golden medals that have been obtained by the athletes of that nation. Note that an athlete obtains a golden medal when he or she achieves the best (shortest) time in a final competition (5pts) 4. Express the constraint that verifies that the DB does not contain two different final races that are associated with the same sport (4pts) B. Formal languages (4 points) 1. Write query n. 2 in two formal languages of your choice. Part 2: Database Design (on a separate sheet / file from Part 1) C. Conceptual and Logical Design (13 points) § The project is evaluated for completeness, correctness, legibility, minimality and self-explanatory nature. § Remember to specify the identifiers of all entities and the cardinalities of all associations. FOR THE STUDENTS OF PROF. CERI: § Remember that also the logical project is a graph whose nodes and arcs must be arranged coherently with those…