Database Management Systems - May 2015

Information Technology (Semester 3)

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 terms total participation and partial participation with example. (5 marks)

1 (b) List four significant differences between file processing and database management system. (5 marks)

1 (c) Explain the steps in query processing. (5 marks)

1 (d) List all functional dependencies satisfied by the relation.

2 (a) Construct an E-R diagram for a car-insurance company that has a set customer each of whom owns one or more cars. Each car has associated with it zero to any number of recorded accidents. (8 marks)

2 (b) We can convert any either set to a strong entity set by simply adding appropriates attributes. Why then, do we have weak entity sets? (12 marks)

3 (a) Consider the following employee database
Employee (empname, street, city, data of join)
Works (empname, company, name, salary)
Manages (empname, manager-name)
Write SQL queries for the following statements:-

i) Find all employees who joined in the month of October.
ii) Modify the database so that 'Peter' now lives in 'Newton'.
iii) List all employees who live in the same cities as their managers.
iv) Find all employees who earn more than average salary of all employees of their company.
v) Give all employees of XYZ corporation a 15 percent raise. (10 marks)

3 (b) Draw and Explain Database system structure. (10 marks)

4 (a) Explain conflict and view serialiazability. (10 marks)

4 (b) What is the view in SQL, how it is defined? Discuss the problem that may arise when we attempt to update a view. How views are implemented? (10 marks)

5 (a) Consider a dependency diagram of relation R and normalize it up to 3^rd Normal form. (10 marks)

5 (b) Compare the shadow paging recovery scheme with the log-based recovery schemes in terms of ease of implementation and overhead cost. (10 marks)

6 (a) Explain the following relational algebra operations with proper examples.
i) Set intersection
ii) Natural Join
iii) Division
iv) Generalized Projection. (10 marks)

6 (b) Explain time stamp ordering protocol and Thomas Write rule. (10 marks)