TdE BD1 27 06 23 Eng

Page 1

DataBases 1 – proff. S. Ceri, S. Comai, L. Tanca – a. y. 2022/23 June, 27th, 2023 – Duration: 2h Write the solutions of the two parts on TWO separate sheets/files, both headed. Part 1: QUERY LANGUAGES (on a separate sheet/file from Part 2) Given the following relational schema, relating to a well-known chain of SPAs: CLIENT(CF, First Name, Last Name, DateBirth) CENTER(Code, Address, City, DailyCapacity) RESERVATION(CF, Code, Date, Price) A. SQL (13 points) 1. Define the RESERVATION schema, including referential integrity constraints. (2pt) 2. Write the query that finds, for each city, the centers with the highest number of reservations for dates after June 27. (4pt) 3. Write the query that finds the customers who, considering all reservations, spent more in Salerno than in Milan. (4pt) 4. Express the constraint that requires that the maximum daily capacity of each center should not be exceeded. (3pt) B. Formal Languages (6 points) ONLY FOR PROF. TANCA'S STUDENTS: Please write the solution of part 1B (Formal Languages) on the additional A4 sheet that has been especially given to you. This part will not be corrected if done on other sheets. 1. Express in optimized relational algebra and Datalog the following query: Find customers who have made at least two reservations in a center in Milan, but have never made reservations in centers in Salerno Parte 2: DATABASE DESIGN (on a sheet/file different from the sheet used for Part 1) C. Conceptual and Logical Design (13 points) • The design is evaluated for completeness, correctness, readability, minimality, and self-explanation. • Remember to specify identifiers of all entities and cardinality of all associations (i.e., relationships). • Arrange conceptual design and logical design side by side so that they can be observed…