Database Management Systems - May 2016
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) List all functional dependencies satisfied by the relation
(i) primary key
(ii) Group by clause
(iii) Lock point
(iv) Total participation
(v) Data independence(10 marks) 3(b) What is view? How it it created and stored?(10 marks) 4(a) What is JOIn? Explain different types of JOIn along with example.(10 marks) 4(b) Consider the following employee database
Employee ( emp_name.street,city,date_of_joining)
Works ( emp_name, company_name, salary) <br. company="" (company_name,="" city)="" <br=""> Manages ( emp_name,manager_name)
Write SQL queries for following
(i) Modify the database so that 'Deepa' lives in 'Pune'
(ii) Give all employees 'XYZ corporation' a 10% rise in salary
(iii) List all employees who lives in the same city as heir company city
(iv) Display all employees who joined in the month of 'March'
(v) Find all employees who earn more than average salary of all employees of their company.</br.><>(10 marks) 5(a) Define Normalization? Explain 1NF, 2NF and 3NF with example.(10 marks) 5(b) Consider the SQL query given below. Draw initial query tree and transform this initial query tree using heuristic query optimization.
FROM Flight as F, Passager as P, Crew as C
WHERE F.flightno=c.flightno AND
F.Date= '06-23-15' AND
F.to= 'Mumbai' AND
P.name=C.name(10 marks) 6(a) Define transaction? Explain transaction state diagram and properties of transaction(10 marks) 6(b) Explain Differed database modification and immediate data base modification and their difference in context of recovery.(10 marks)