Full exam for Basi di Dati in the Computer Engineering degree programme at Politecnico di Milano. The document covers: 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
Full exam for Basi di Dati in the Computer Engineering degree programme at Politecnico di Milano. The document covers: 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
Import quality: text was extracted directly from the original document.
Representative passages recognised in different parts of the material. The full extracted text remains available to search, while this compact preview makes the page easier to read.
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…
First page of the document.
