Question Paper: Database Management Systems : Question Paper May 2016 - Information Technology (Semester 3) | Mumbai University (MU)

Database Management Systems - May 2016

Information Technology (Semester 3)

(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

X1 Y1 Z1
X1 Y1 Z1
X2 Y2 Z1
X2 Y2 Z1
(5 marks) 1(b) Explain Generalization and Specialization.(5 marks) 1(c) Explain deadlock in brief.(5 marks) 1(d) Explain aggregate function with exmaple(5 marks) 2(a) Explain different data models with its advantages and disadvantages.(10 marks) 2(b) Draw E-R diagram for Car insurance company that has a set of customers. Each customer has one or more cars. Each car is zero or more accident records.(10 marks) 3(a) Explain following terms
(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.&gt;<>(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.
SELECT p.ticketno
FROM Flight as F, Passager as P, Crew as C
WHERE F.flightno=c.flightno AND
F.Date= '06-23-15' AND 'Mumbai' AND
(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)

Please log in to add an answer.