Databases Reference
In-Depth Information
(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
<
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.
university
<
