QuestionQuestion

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).

Solution PreviewSolution Preview

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:
Solution.docx.

$6.00
for this solution

or FREE if you
register a new account!

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats