MU Information Technology (Semester 3)
Database Management Systems
May 2016
Total marks: --
Total time: --
(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) List all functional dependencies satisfied by the relation
X1 Y1 Z1
X1 Y1 Z1
X2 Y2 Z1
X2 Y2 Z1
5 M
1(b) Explain Generalization and Specialization.
5 M
1(c) Explain deadlock in brief.
5 M
1(d) Explain aggregate function with exmaple
5 M

2(a) Explain different data models with its advantages and disadvantages.
10 M
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 M

3(a) Explain following terms
(i) primary key
(ii) Group by clause
(iii) Lock point
(iv) Total participation
(v) Data independence
10 M
3(b) What is view? How it it created and stored?
10 M

4(a) What is JOIn? Explain different types of Join along with example.
10 M
4(b) Consider the following employee database
Employee ( emp_name.street,city,date_of_joining)
Works ( emp_name, company_name, salary) 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.
10 M

5(a) Define Normalization? Explain 1NF, 2NF and 3NF with example.
10 M
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 M

6(a) Define transaction? Explain transaction state diagram and properties of transaction
10 M
6(b) Explain Differed database modification and immediate data base modification and their difference in context of recovery.
10 M

More question papers from Database Management Systems