Database Management Systems - Dec 2015

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) Draw E-R diagram for Hospital management System: Convert E-R diagram into tables.(10 marks) 1 (b) Explain authorization in SQL.(5 marks) 1 (c) List four significant differences between file processing system and database management system.(5 marks) 2 (a) What is deadlock? How is it detected? Discuss different types of deadlock prevention scheme.(10 marks) 2 (b) Explain following terms with suitable example.
i) Weak entity set
ii) Data manipulation language.
iii) Foreign key
iv) Super key.(10 marks) 3 (a) When a transaction is rolled back under timestamp ordering, it is assigned a new timestamp, Why can it not simply keep its old timestamp?(10 marks) 3 (b) What is normalization? Explain 1NF, 2NF, 3NF and BCNF with examples.(10 marks) 4 (a) For the following given database, write SQL queries - employee (eid, employee_name, street, city)
works (eid, cid, salaray)
Company (cid, company_name, city)
Manager (eid, manager_name)
i) Find the names, street and city of all employees who work for 'AZT' and earn more than Rs. 30,000.
ii) Find the names of all employees having 'K' as the first letter in their names.
ii) Display the annual salary of all employees.(10 marks) 4 (b) Describe overall architecture of DBMS with diagram.(10 marks) 5 (a) Discuss the different security and authorization mechanism in database management system.(10 marks) 5 (b) Explain lock based and validation based protocol with example.(10 marks)

Write short notes on any four.

6 (a) Specialization and Aggregation.(5 marks) 6 (b) Referential Integrity(5 marks) 6 (c) Assignment(5 marks) 6 (d) Log based recovery.(5 marks) 6 (e) Cost based query optimization.(5 marks)