Database Management Systems - Dec 2012

Computer Engineering (Semester 4)

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) Construct an ER Diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examination conducted. Convert ER Diagram into tables.(10 marks) 1 (b) List significant differences between file processing system and Database Management System.(5 marks) 1 (c) What are the functions of Database administrator. (5 marks) 2 (a) For the given employee database given an expression in SQL for the following:
Employee (<u>empname</u>, street, city)
Work (<u>empname</u>, company-name, salary)
Company (<u>company-name</u>, city)
Managers (<u>empname</u>, manager-name)
(i) Modify database so that swapnil now lives in Navi Mumbai .
(ii) Give all employees of IBM a 40% noise.
(iii) List all the employees who lives in the same cities as their managers.(10 marks) 2 (b) Define serializability. Explain conflict and view serializability.(10 marks) 3 (a) Explain following Relational algebra operations.
i. Set Difference
ii. Generalized projection
iii. Natural Join
iv. Rename(12 marks) 3 (b) What is deadlock? Discuss different types of deadlock avoidance schemes.(8 marks) 4 (a) When it is preferable to use dense index rather than sparse index? Also explain hash indices.(10 marks) 4 (b) What is view in SQL? Discuss the problem that may arise when we attempt to update a view. How views are implemented?(10 marks) 5 (a) Discuss different security and authorization mechanism in Database Management system.(10 marks) 5 (b) Define Normalization? What is the importance of Normalization in database design? Explain 1NF, 3NF and BCNF with example.(10 marks) 6 (a) Explain Time stamp ordering protocol and Thomas write rule in detail.(10 marks) 6 (b) Explain Undo and Redo operations for log based recovery. Also explain shadow paging recovery technique.(10 marks)

Write short notes on (any four)

7 (a) Data dictionary storage(5 marks) 7 (b) Armstrong Axioms(5 marks) 7 (c) Triggers(5 marks) 7 (d) Buffer Management(5 marks) 7 (e) Generalization and Aggregation(5 marks)