MGM’s College of Engineering, Nanded.
Department of IT
Semester I (2014-15)
Class: BE(IT) Subject: DMDW Assignment I
1.What are the differences between OLTP and OLAP? Elaborate
2. Enlist
and explain the properties of DW.
3.Why
a DW should be integrated and time-variant?
4.Briefly
compare snowflake schema, fact constellation schema, star schema, and starnet
query model with appropriate example.
5. List
the major steps involved in ETL process.
6.Explain
the differences between ROLAP AND MOLAP.
7. Describe
the operations roll-up, drill-down, slice and dice, and pivot with proper
example.
8. Draw
and explain the three-tier data warehousing architecture.
9. What
back-end tools and utilities are used in DW? Why?
10. What are the contents of DW metadata? Why
metadata is important in DW?
11.What is bitmap indexing? What are the
benefits of bitmap indexing? Create bitmap index for item and city for the
following table:
TID
|
Item
|
City
|
T1
|
Hotdog
|
Mumbai
|
T2
|
Sauce
|
Pune
|
T3
|
Burger
|
Mumbai
|
T5
|
Hotdog
|
Nagpur
|
T6
|
Sauce
|
Nagpur
|
12. How are measures categorized in DW schema?
13. What are the usages of DW? Explain.
14.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) Enumerate three classes of schemas that are
popularly used for modeling data warehouses.
(b) Draw a schema diagram for the above data warehouse
using one of the schema classes listed in (a).
15. For the above DW in Q14 , perform the following:
(a) 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?
(b) To obtain the same list, write an SQL query assuming the data are stored in a relational database with the schema fee (day, month, year, doctor, hospital, patient,count, charge).
16. Suppose that a data warehouse for Big
University consists of
the following four dimensions: student,
course, semester, and
instructor, and two measures count and
avg grade.When at the
lowest conceptual level (e.g., for a given
student, course,
semester, and instructor
combination), 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 one perform in order to list the
average grade of CS courses
for each Big University student.
17. 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 spectator 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 one perform in order to list
the total charge paid by
student spectators at GM_Place in 2004?
(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.
18.
A company is using OLAP to provide monthly summary
information about its products and branch
sales to the company
managers. How many different aggregates would
be required to
fill data cube on product, branches and dates
if there are 20
products, 10 branches and five
years of monthly data?
Faculty Incharge: Hashmi S A
