Advanced Database Management Systems - May 2015

Information Technology (Semester 5)

TOTAL MARKS: 80
TOTAL TIME: 3 HOURS (1) Question 1 is compulsory.
(2) Attempt any three from the remaining questions.
(3) Assume data if required.
(4) Figures to the right indicate full marks.

1 (a) Explain the Triggers in SQL with two examples. (5 marks)

1 (b) What are the different characteristics of a Data Warehouse? (5 marks)

1 (c) What are ACID properties of a transaction? (5 marks)

1 (d) What is the role of meta data in a data warehouse? (5 marks)

2 (a) Design a schema in SQL for a Library System. Show one example each for PRIMARY KEY and FOREIG KEY constraint. Create one assertion for the following Constraint:
"No member can borrow more than three books at a time" (10 marks)

2 (b) You have to design and implement a database that manages information about publishers, authors, and books. Some information includes:
A publisher has a name and an address for the headquarters. Each publisher also has a set of branches, each branch having an address and two phone numbers.
An author has a name and an address.
A book is published by a publisher and has a list of authors associated with it. An author can publish several books and a book can be published by at most one publisher.
i) Design an ODL schema or the above database.
ii) Write in OQL the following query:

List the name of the author who has published the most books with publisher "McGraw Hill". (10 marks)

Explain the following concept with the help of example.

3 (a) SQL Injection. (5 marks)

3 (b) Access Control in a Database. (5 marks)

3 (c) Snowflask Schema. (5 marks)

3 (d) Factless Fact Table. (5 marks)

4 (a) Consider the following database that has to be distributed:
PATIENT (Number, Name, SSN, Amount_Due, Dept, Doctor, Med_treatment)
DEPARTMENT (Dept, Location, Director)
STAFF (Staffnum, Director, Task)
i) Show 2 examples of horizontal fragmentation.
ii) Show 2 examples of vertical fragmentaion.
iii) Show 2 examples of derived fragmentation. (10 marks)

4 (b) Consider a data warehouse storing sales details of various goods sold, and the time of the sale. Using this example describe the following OLAP operations
i) Slice iii) Dice iii) Rollup iv) Drill down. (10 marks)

5 (a) Clearly state the difference between OLTP and OLAP. (10 marks)

5 (b) With the help of a diagram explain the architecture of a Data Warehouse. (10 marks)

Write short notes on any two of the following:

6 (a) ETL Functions of a data warehouse. (10 marks)

6 (b) Advanced recovery techniques in a database. (10 marks)

6 (c) Indexing Techniques in a Database. (10 marks)