Database Management Systems - Dec 2013
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) Define Data Independence and explain types of data Independence(5 marks) 1 (b) List all Function dependencies satisfied by the relation
(iv) Faculty and convert it to tables.
Employee (empname, street, city, date_of_joining)
Works (empname, company_name, salary)
Company (company_name, city)
Manages (empname, manager_name).
Write SQL queries for the following statements :-
(i) Modify the database so that 'John' now lives in 'Mumbai'
(ii) Give all employee of 'ABC Corporation' a 10% raise.
(iii) LIst all employee who live in the same cities as their managers.
(iv) Find all employees who more that avearage salary of all employees of their company.(10 marks) 4 (b) Explain Time-stamp ordering protocol.(10 marks) 5 (a) Consider a dependency diagram of relation R and normalization it up to third normal form. (10 marks) 5 (b) Explain log-based Recovery(10 marks) 6 (a) Draw a query tree for the following SQL query.
Select P.Pnumber, P.Dnum, E.Lname, E.Address, E.Bdate.
From Project as P, Department as D, Employee as E.
(10 marks) 6 (b) Explian following relational algebra operations with proper examples.
(ii) Left outer join
(v) Natural join