TdE BD1 27 01 2022 ENG

Page 1

BASI DI DATI 1 – PROFF. S. CERI, S. COMAI, L. TANCA – A. A. 2021/22 EXAM HELD ON 27TH JANUARY 2022 – TIME DURATION: 90 minutes Please write the solutions of the two parts in TWO SEPARATE SHEETS/FILES, both of which should contain your name, surname and student code Part 1: QUERY LANGUAGES (on separate sheet/file with respect to Part 2) Consider the following schema, describing a chain of public swimming pools. CUSTOMER (Customer_ID, Name, Surname, City, Birth_Date) SWIMMINGPOOL (Pool_ID, Name, Address, City, Opening_Date) TICKET (Customer_ID, Pool_ID, Entrance_Date) MAINTENANCE (Pool_ID, Maintenance_Date, Activity) A. SQL (15 points) 1. Specify the command to create the TICKET table, defining the tuple and domain constraints deemed ap-propriate and expressing all the referential integrity constraints towards the other tables of the schema. (2pt) 2. Find the Customer_ID, Name, and Surname of those customers that are 65 years or older and that have been at least once in all the swimming pools. (4pt) 3. Show, for each day of the year 2020, the most popular pool (i.e., the most attended). (5pt) 4. Express the constraint that verifies that no tickets have been sold for those days when the swimming pool is closed for maintenance or for those preceding the opening date. (4pt) B. Formal languages (4 points) 5. Solve the query A.2 using two formal languages of your choice. Part 2: CONCEPTUAL MODELING (on separate sheet/file with respect to Part 1) C. Conceptual Model and Logical Schema (13 points) § The project will be evaluated for its completeness, correctness, readability, minimality, and self-explanatoriness. § Remember to specify the identifiers of all the entities and the cardinality of all relationships. FOR PROF. CERI’s students: § Remember that also the logical schema…