Exercises

1
Exercises
2
Exercise (1.1)

• Suppose the AAA Automobile Co. builds a data
  warehouse to analyze sales of its cars.
• The measure - price of a car
• We would like to answer the following typical
  queries
• find total sales by day, week, month and year
• find total sales by week, month, ... for each
  dealer
• find total sales by week, month, ... for each car
  model
• find total sales by month for all dealers in a
  given city, region and state.

3
Exercise (1.2)

• Dimensions
• time (day, week, month, quarter, year)
• dealer (name, city, state, region, phone)
• cars (serialno, model, color, category , )
• Design the conceptual data warehouse schema

4
Exercise (2.1)

• Our chain of hotels operates 500 hotels in 40
  countries. In our chain, we have different types
  of hotels (5-stars, 4-stars, 3-stars, motels,
  etc.). We would like to store information about
  each hotel and perform analysis with regard a
  given hotel. For each hotel we store its name,
  type, address, country, region, zip code, phone,
  name of the manager. There are also different
  types of rooms like single, double, family,
  suits, etc. Each room may also incorporate
  certain optional features, such as refrigerator
  or kitchenette. So, each room may be described as
  foolws rooms type, size, number of beds,
  max-number_of_customers, refrigerator (Boolean),
  kitchenette (Boolean).

5
Exercise (2.2)

• The hotel chains capacity to accomodate
  customers is limited. Each hotel has a set number
  of rooms.
• Our primary ssource of revenue is accomodation in
  hotel rooms. The biggest challenge we face iss
  determining how to price our hotel rooms. If they
  are priced low, our hotels will be constantly
  booked, the people will be forced to try the
  competition out, and we wont any money. If rooms
  are priced too high, a lot of rooms will remain
  empty.
• To determine how to price hotel rooms our
  analysts need a look at the use of our capacity
  over time the occupancy rate (utilization) and
  the vacancy rate.

6
Exercise (2.3)

• We would like to perform the following analysis
• find total number of occupied rooms (occupaancy
  rate) by week, month, year for each type of a
  hotel and each type of a room.
• find total number of vacant rooms (vacancy rate)
  by week, month, year for each type of a hotel and
  each type of a room.
• rates are seasoned, so we are interested at
  utilization and vacancy rates at specific periods
  in time,
• find total number of guests staying in occupied
  rooms by week, month, year for each type of a
  hotel and each type of a room.
• we would like to analyze preferable locations of
  hotels.

7
Exercise (2.4)

• We would like to perform the following analysis
• Our guets prefer rooms woth additional features
  or they prefer standard rooms
• Shall we invest in 3-stars, 4-stars, or 5-stars
  hotels?
• Etc.
• Design the conceptual data warehouse schema

8
Exercise (3.1)

• We would like to design a data warehouse for a
  regional weather bureau. The weather bureau has
  about 1000 probes, which are scattered throughout
  various land and lake locations in the region to
  to collect basic weather data including air
  pressure, temperature, and precipitation at each
  hour. All data are sent to the central station,
  which has collected such data over 10 years.. The
  designn should facilitate efficient analysis and
  on-line query processing, and derive general
  weather pattterns.

9
Exercise (4.1)

• We would like to design a data warehouse for
  Primera Division (football leaque). We would like
  to analyze games, the number of spectators
  watching games, the number of tickets sold for
  spectators, and revenue got from the sale of
  tickets. Games are organized at different
  locations. For each location we would like to
  keep track of its name, address, capacity, and
  some additional features like closed or open.
  For each game we would like to store information
  about teams playing game, result of the game,
  etc. Spectators may be students, adults, or
  seniors, with each categgory having its own
  charge rate.

10
Exercise (4.2)

• We would like to answer the following typical
  queries
• find the total number of spectators watching
  games played by Real Madrid by years and by
  locations,
• find the total number of seniors watching games
  organized in Madrid by years,
• find the total charge that students paid for
  watching games in Klagenfurt by months and years,
• find the total charge that spectators paid in
  2001 by diffeerent locations and different
  categories of spectators.