1) A flight data warehouse for a travel agent consists of six dimensions: traveler, departure (city), departure time, arrival, arrival time, and flight; and two measures: count, and avg fare, where avg fare stores the concrete fare at the lowest level but average fare at other levels.
(a) Suppose the cube is fully materialized. Starting with the base cuboid [traveller, departure, departure time, arrival, arrival time, flight], what specific OLAP operations (e.g., roll-up flight to airline) should one perform in order to list the average fare per month for each business traveler who flies Emirates Airlines from Abu Dhabi in the year 2004?
(b) Suppose we want to compute a data cube where the condition is that the minimum number of records is10 and the average fare is over $500. Outline an efficient cube computation method (based on common sense about flight data distribution).
2) Suppose that a data warehouse consists of the three dimensions time, doctor, and patient, and the two measures count and charge, where charge is the fee that a doctor charges a patient for a visit.
(a) Draw a schema diagram for the above data warehouse using one of the schema classes listed (e.g. star schema).
(b) Starting with the base cuboid [day; doctor; patient], what specific OLAP operations should be performed in order to list the total fee collected by each doctor in 2004?
(c) To obtain the same list, write an SQL query assuming the data is stored in a relational database with the schema fee (day, month, year, doctor, hospital, patient, count, charge).
This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.
a) The possible sequence of OLAP operations can look like below:
- Roll-up on TRAVELLER until the CATEGORY layer and then dice on BUSINESS;
- Roll-up on DEPARTURE until the CITY level and dice on Abu Dhabi;
- Roll-up on DEPARTURE_TIME to ANY level (*);
- Then Roll-up on ARRIVAL to ANY level (*);
- Then Roll-up on ARRIVAL_TIME also to ANY level (*);...
This is only a preview of the solution. Please use the purchase button to see the entire solution