Esercitazione DW 1

Page 1

EXERCISE (Travel agency) A travel agency organizes guided trips for tourists of different nationalities. The agency wants to design a data warehouse in order to know the main trends about the participation in trips with respect to the characteristics of trips and participants. The following is the relational schema of the operational database. TRIP (CodeT, DestinationName, CategoryName, CodeGuide, DepartureDate) // Examples of categories: business trip, adventure travel, … DESTINATION (DestinationName, Description, TypeName, NationName) // Examples of destination types: seaside, mountain, art city, … TRAVELING (CodeTrip, CodeParticipant, PaidPrice, Discount) // PaidPrice is the price paid by a specific participant for the trip; it may differ for each participant. Discount is the percentage of discount obtained by the participant. PARTICIPANT (CodeP, Name, Surname, Age, NationName) NATION (NationName, Continent) GUIDE (CodeGuide, Name, Surname, Age, Gender, NationName) TYPE (TypeName, Description) CATEGORY (CategoryName, Description) 1. Perform the reverse engineering of the given logical schema into a conceptual schema (Entity-Relationship model). 2. With respect to the produced ER diagram, discover the fact(s) that are useful specifically to answer the queries reported below. For each of these facts: a. Produce the attribute tree (with pruning and grafting). b. Produce the conceptual schema (fact schema). c. Produce the glossary. 3. Produce a logical schema consistent with the conceptual schema and such that it allows performing the queries below. 4. Write in SQL the following queries against the designed logical schema: a. Total income and total number of participations with respect to departure date, type and nation of the destination, and age and continent of the…