Esercitazione DW 2

Page 1

EXERCISE (Insurance company) An insurance company requires the data warehouse design for accident analysis of its customers. In particular, the company requires to: x Evaluate the history of the accidents with respect to the date, the motivation and the policies stipulated by the insurance company, considering the number of accidents and the total cost. x Evaluate the history of the policies with respect to the class, the start and end dates, the customers of the insurance company and the policy type, considering the number of policies, the total amount and the average policy duration. Relational schema of the operational database: CUSTOMER (IdCustomer, Name, Surname, Address, City, Gender, BirthDate) POLICY (IdPolicy, StartDate, EndDate, Amount, Class, Owner, TypeName) // The class of the policy is an integer that depends on the past history of accidents associated with this policy. The type of the policy indicates the kind of accidents that are covered; e.g., the ‘Comprehensive’ policy type covers all the accidents. POLICYTYPE (TypeName, Description) ACCIDENT (IdAccident, Date, Cost, Description, Motivation*, IdPolicy) // Sample motivations: rain, drunk driving, speeding, … 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 for answering 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. 4. Write in SQL the following queries against the designed logical schema: a. Considering only the accidents happened on…