BITS WILP Data Warehousing End-Sem Exam 2017-H1

BITS WILP Data Warehousing End-Sem Exam 2017-H1 (Open Book Exam)

Birla Institute of Technology & Science, Pilani

Work-Integrated Learning Programmes Division

Second Semester 2016-2017

Comprehensive Examination  (EC-3 Regular)

Course No.                  : SS ZG515 

Course Title                 : DATA WAREHOUSING 

Nature of Exam           : Open Book

Weightage                    : 50%

Duration                      : 3 Hours 

Date of Exam              : 09/04/2017    (AN)

No. of Pages        = 1

No. of Questions = 6

1.       Please follow all the Instructions to Candidates given on the cover page of the answer book.

2.       Assumptions made if any, should be stated clearly at the beginning of your answer.

Q.1.        A university wants to analyze the attendance of students in various courses. The attendance fact table has the schema:

Attendance_Fact_Table (Student_key, Faculty_key, Course_key, Campus_Key,  Room_key, Day_hour_key)

(a)             What is the granularity of the above fact table?

(b)             Assuming that all the dimension keys are surrogate keys, estimate the size of the above fact table (in GB), given that the fact table contains data for 10 semesters. In each semester, 200 courses are offered at each campus (assume there are 4 campuses) and each course has 40 lecture hours. Each course has an average strength of 100 students. It is also given that the average attendance in courses is 60%.

(c)             Each faculty belongs to a department and each student has a major discipline (like CS). Create appropriate aggregates so that they facilitate attendance analysis wrt departments and disciplines. Suggest a suitable values for sparsity as we aggregate attendance over departments and disciplines.

(d)            When you create aggregates in part c, would sparsity failure happen? Justify your answer.

(e)             Design the Time dimension for the Attendance fact table given and give the aggregated star schema for semester-wise attendance of courses.

[1 + 2 + 3 + 2 + 2 =10]

Q.2.        Why it is recommended that the users should not be aware of the partitions and aggregates that are present in the DW? In the absence of information about partitions and aggregates, how the user queries leverage them?                                                                    [8]

Q.3.        Compare the immediate and the three deferred view maintenance schemes in terms of processing delay (attach a suitable probability) & currency of results (guaranteed or not guaranteed).  Give your answer in tabular form with schema (scheme, delay, currency).     [8]

Q.4.        Explain in detail, the kind of features and functionalities RDBMS vendors are introducing  to make their product more suitable for data warehousing & OLAP. List the features and briefly describe them and their usefulness.                                                                                            [8]

Q.5.        Discuss the advantages and disadvantages of using Multidimensional Databases (MDDBs)  in DW/OLAP. Also explain why MDDBs are used extensively in DW/OLAP systems despite their disadvantages.                                                                                                 [8]

Q.6.        Suppose you have implemented a DW on top of an RDBMS that does not support aggregates. That is, there is no Aggregate Navigator. In such a situation, how would you ensure that queries are able to leverage the aggregates that you have created and also that every query is answered. Create all necessary database objects that you might need to do this and give steps involved.                                                                                                  [8]

***********

Tags: BITS Pilani, Work Integrated Learning Program (WILP), Previous Year Question Papers, Data Warehousing