Page 218 -

11-ch04-125-186-9780123814791
#57
3:17 Page 181
HAN
2011/6/1
4.7 Exercises 181

(c) 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 2010?
(d) To obtain the same list, write an SQL query assuming the data are stored in a rela-
tional database with the schema fee (day, month, year, doctor, hospital, patient, count,
charge).
4.4 Suppose that a data warehouse for Big University consists of the four dimensions stu-
dent, course, semester, and instructor, and two measures count and avg grade. At the
lowest conceptual level (e.g., for a given student, course, semester, and instructor com-
bination), the avg grade measure stores the actual course grade of the student. At higher
conceptual levels, avg grade stores the average grade for the given combination.
(a) Draw a snowflake schema diagram for the data warehouse.
(b) Starting with the base cuboid [student,course,semester,instructor], what specific
OLAP operations (e.g., roll-up from semester to year) should you perform in order
to list the average grade of CS courses for each Big University student.
(c) If each dimension has five levels (including all), such as “student < major < status <
university < all”, how many cuboids will this cube contain (including the base and
apex cuboids)?
4.5 Suppose that a data warehouse consists of the four dimensions date, spectator, location,
and game, and the two measures count and charge, where charge is the fare that a spec-
tator pays when watching a game on a given date. Spectators may be students, adults, or
seniors, with each category having its own charge rate.
(a) Draw a star schema diagram for the data warehouse.
(b) Starting with the base cuboid [date,spectator,location,game], what specific OLAP
operations should you perform in order to list the total charge paid by student
spectators at GM Place in 2010?
(c) Bitmap indexing is useful in data warehousing. Taking this cube as an example,
briefly discuss advantages and problems of using a bitmap index structure.
4.6 A data warehouse can be modeled by either a star schema or a snowflake schema. Briefly
describe the similarities and the differences of the two models, and then analyze their
advantages and disadvantages with regard to one another. Give your opinion of which
might be more empirically useful and state the reasons behind your answer.
4.7 Design a data warehouse for a regional weather bureau. The weather bureau has about
1000 probes, which are scattered throughout various land and ocean locations in the
region to collect basic weather data, including air pressure, temperature, and precipi-
tation at each hour. All data are sent to the central station, which has collected such
data for more than 10 years. Your design should facilitate efficient querying and online
analytical processing, and derive general weather patterns in multidimensional space.
4.8 A popular data warehouse implementation is to construct a multidimensional database,
known as a data cube. Unfortunately, this may often generate a huge, yet very sparse,
multidimensional matrix.