Database Management Systems - May 2017

MU Computer Engineering (Semester 4)

Total marks: --
Total time: -- INSTRUCTIONS
(1) Assume appropriate data and state your reasons
(2) Marks are given to the right of every question
(3) Draw neat diagrams wherever necessary 1(a) Construct an ER diagram for hospital with a set of patient and a set of medical doctors, Associated with each patient a log of various tests and examination conducted. 10 marks

1(b) Explain lossless join decomposition and dependency preserving decomposition. 10 marks

1(c) List four significant differences between file processing system and database management system 10 marks

2(a) What is deadlock? How is it detected? Discuss different types of deadlock prevention scheme. 10 marks

2(b) Write SQL queries for th given database
Employee (eid, ename, street , city)
Works (eid, cid, salary)
Company (cid, cname, city) i) Modify the database so that Jack now lives in 'Mumbai'
ii) Give all employees of 'ANZ Corporation' a 10% raise in salary
iii) Find all employee id who live in same cities as the company for which they work
iv) Give total number of employees
v) Find the highest paid employee 10 marks

3(a) What is attribute? Explain different types of attributesn with examples. 10 marks

3(b) Companies manufacture ranges of products which are purchased by customers. The relation schema for this operation is given as :- Company (company _ code, Company _name, director# director_ director _ name, {product name, cost, {cust#, customer _ name, address}}) where { } represents a repeating groups and company _ code, director# and cust# contains unique values. Normalize this relation to third normal form. 10 marks

4(a) Explain following relational algebra operations with examples
i) set intersection ii) Generalized projection iii) Natural Join
iv) Aggregation operator 10 marks

4(b) Explain nested loop join and block nested loop join algorithm in query processing. 10 marks

5(a) Explain Timestamp ordering protocol and Thomas write rule. 10 marks

5(b) Describe the three level schema architecture architecture of DBMS. State different level of dependencies in this architecture. 10 marks

6(a) Explain log based recovery 10 marks

6(b) Explain Hash join algorithm in query 10 marks