Question

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 Preview

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.

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 (*);...

This is only a preview of the solution. Please use the purchase button to see the entire solution

Assisting Tutor

Related Homework Solutions

E-R Diagram
Homework Solution
$25.00
Computer
Science
E-R Diagram
Database
Development
Customer
Service
Installation
SQL Server for Business Intelligence
Homework Solution
$20.00
SQL
Server
Business
Intelligence
Transactional
3rd
Normal
Form
Relational
Schema
Dimensional
Model
Fact
Table
OLAP
System
Warehouse
Database
Analysis
Services
Data
Tools
Management
Studio
Dimension
Database Management Questions
Homework Solution
$25.00
Database Management
MS Access
DB Programmer
DML
SQL
External Schema
Conceptual Schema
Logical Data Independence
Physical Data Independence
Client Server
Primary Key
Foreign Key
Integrity Rules
Traditional File System
ER Diagram
University D
Database Developments & SQL Queries
Homework Solution
$20.00
Computer Science
Database Development
Relationships
SQL Queries
MS Access
Relational Schema
Referential Integrity
Books
Authors
Orders
Sales
Data Sets
Tables
Get help from a qualified tutor
Live Chats