(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).
These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.Problem 1
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 (*);...
By purchasing this solution you'll be able to access the following files: