2014-15 (SEM I) BE IT DMDW Assignment I

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