Database Management Systems - Dec 2011

Computer Science Engg. (Semester 5)

TOTAL MARKS: 100
TOTAL TIME: 3 HOURS (1) Question 1 is compulsory.
(2) Attempt any four from the remaining questions.
(3) Assume data wherever required.
(4) Figures to the right indicate full marks. 1 (a) Discuss the various component modules of a DBMS with a neat diagram.(8 marks) 1 (b) Briefly explain the advantages of object oriented systems.(5 marks) 1 (c) List and explain the main characteristics of database approach.(7 marks) 2 (a) Define and explain a partial key, with an example.(4 marks) 2 (b) What is meant by recursive relationship? Bring out the importance of role names in recursive relationship, with an example.(6 marks) 2 (c) Design an ER diagram for maintaining a movie database taking into account atleast four entities.(10 marks) 3 (a) Explain foreign key and its importance. Can a foreign key exist, only for a single table? Explain.(5 marks) 3 (b) How can an intersection operator be implemented using union and minus operator?(3 marks) 3 (c) Write queries in relational algebra for the following:
(i) Retrieve the number of dependents for an employee named "Ram"
(ii) Retrieve the name of managers working in location named "XYZ" who has no female dependents.
(iii) Retrieve the name of employee who works in the same department as that of "Raj".(12 marks) 4 (a) Explain all possible options that can be specified when a referential integrity constraint is violated using suitable example for all options.(8 marks) 4 (b) Write a queries in SQL for the following. Refer the relations at the end of the question paper:
(i) Retrieve the name of the employee who gets second highest salary
(ii) For each department that has more than five employees, retrieve the department number and the number of its employees who have salary more than Rs.5000.
(iii) Retrieve the name of employees who salary is greater than all the employees working in either department 5 or 6.(12 marks) 5 (a) Discuss the significance of an assertion. Write an assertion to specify a constraint such that the salary of an employee must no be greater than the salary of the manager of the department that the employee works for in SQL.(8 marks) 5 (b) What is meant by impedance mismatch? Explain.(6 marks) 5 (c) Create a view which will display the department name, number of employees working and total salary for each department.(6 marks) 6 (a) Suggest and explain three different techniques to achieve INF using a suitable example.(8 marks) 6 (b) Different between prime and non-prime attribute, with an example. (4 marks) 6 (c) Consider the relation R(A, B, C, D, E, F) and the FD A?B, C?DF, AC?E, D?F.
What is the key and highest normal form of R? if it is not in 3NF find a decomposition that is lossless and dependency preserving?(8 marks) 7 (a) Which normal form is based on the concept of multi valued functional dependency? Explain the same with an example.(10 marks) 7 (b) Explain two phase locking protocol and its disadvantages.(10 marks)

Write short notes on:

8 (a) Tune stamp ordering algorithm (5 marks) 8 (b) ARIES algorithm (5 marks) 8 (c) Embedded SQL(5 marks) 8 (d) Fifth normal form(5 marks) Data Tables/Relations:
Employee (Name, SSN, Salary, SuperSSN, Dno)
Department (Dnum, Dname, MgrSSN)
Dept-Locations (Dnum, Dlocation)
Work ON (ESSN, Pno, Hours)
Dependent (ESSN, Dep Name, Sex)(0 marks)