Advanced Database Management Systems - Dec 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) Justify Write-Ahead Logging (WAL) protocol for a recovery algorithm that requires both UNDO and REDO.(5 marks) 1 (b) What is a view? Discuss the difference between a view and a base relation.(5 marks) 1 (c) Explain Factless Fact Table.(5 marks) 1 (d) What is role of metadata in data warehouse?(5 marks) 2 (a) What are the different types of SQL injection attacks? What risks are associated with it? Explain any one attack in detail.(10 marks) 2 (b) Vaidehi Foods & Beverage is a new company which produces Dairy, Bread and Cookies products with production unit located at Kharghar, Navi Mumbai. These products are sold in selected regions of India. They have sales units at Mumbai, Pune, Satara, Sangli and Kolhapur. The President of the company wants sales information.
1) Find the dimensions of data warehouse for given problem statement and justify your answer.
2) State and represent the concept hierarchy for each dimension.
3) If the president of company wants the report: The number of items sold and income in each region for each product with time.
3.1) Explain at which concept hierarchy level the query will be?
3.2) Write ROLA/MOLAP query.
4) Describe the Rollup or Drilldown OLAP operations.(10 marks) 3 (a) What are the roles of the Analysis, Redo and Undo phase in ARIES? Explain with suitable example.(10 marks) 3 (b) Consider the following database that has to be distributed:
PROJ (PNO, PNAME, BUDGET)
EMP (ENO, ENAME, TITLE)
ASG (ENO, PNO, RESPONSIBiLITY, DURATION)
1. Show 2 example of horizontal fragmentation
2. Show 1 example of derived fragmentation
3. Show 1 example of vertical fragmentation.(10 marks) 4 (a) Design a schema in SQL for a Library System. Show one example each for Primary key and Foreign Key constraint. Create one suitable ECA example to enforce the Library constraint.(10 marks) 4 (b) Explain different types of transparencies in distributed database in detail.(10 marks) 5 (a) Explain with suitable example object identity, object structure and type constructors in OOODB's.(10 marks) 5 (b) PERSON
Query 1: SELECT COUNT (*) FROM PERSON WHERE <condition>;
Query 2: SELECT AVG (Income) FROM PERSON WHERE <condition>;
Consider the PERSON relation and two queries as shown:
1) Give <condition> so it is possible to infer the values of individual tuples.
2) State Clearly the remedies by which the possibility of inferring individual information from statistical queries is reduced.
3) Explain in short Statistical Database Security with above relation and queries.</condition></condition></condition>(10 marks) 6 (a) With suitable relational schema give at least two queries of
1) Simple Query,
2) Complex Retrieval Queries using Group By,
Recursive Queries and
4) Nested Queries.(10 marks)
Attempt any Two of the following.
6 (b) (i) What is meant by granting and revoking a privilege?(5 marks) 6 (b) (ii) Explain in short the concurrency control in distributed database.(5 marks) 6 (b) (iii) Explain in detail the information Delivery Component of data warehouse architecture.(5 marks)