Database Management Systems - Dec 2016

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) Explain with a neat diagram, the component modules of DBMS.(10 marks) 1(b) Define DBMS. Discuss the advantages of DBMS over traditional file system.(6 marks) 1(c) Explain additional implications of using database approach.(4 marks) 2(a) Discuss the concepts related to structural constraints of relationship type with suitable examples.(10 marks) 2(b) Write an ER diagram for hospital management considering at least four entities.(10 marks) 3(a) Lis any five relational algebra operators along with their syntax and purpose.(10 marks) 3(b) Consider the following COMPANY database: EMP(Name, SSN, Salary, SuperSSN, Dno)
DEPT( DNUM, Dname, MgrSSN)
DEPT_LOC( Dnum, Dlocation)
Works_ON( ESSN, Pno, Hours)
Dependent( ESSN, Dep_name, Sex)
Write the relational algebra queries for the following: i) Retrieve the name of the employee who works in the same department as that of "Ravi".
ii) Retrieve the number of dependents for an employee named " Ravi"
iii) Retrieve the name of managers working in location " DELHI" who has no female dependents(10 marks) 4(a) Explain with suitable example, how can you retrieve information from multiple tables.(8 marks) 4(b) Referring to the COMPANY database above, write SQL queries for the following:
i) Retrieve the name of employees whose salary is greater than all employees working in department 3.
ii) For ach department that has more than four employees, retrieve the department number and the number of its employees who have more than 4000 salary.
iii) Retrieve name of an employee who gets second highest salary.(12 marks) 5(a) Explain with example, how assertions are defined.(5 marks) 5(b) What is a view? Explain how views are created and dropped.(5 marks) 5(c) What is a cursor? Explain with example retrieving multiple tuples with embedded SQL.(10 marks) 6(a) Explain update anomalies with examples.(5 marks) 6(b) What is a functional dependency? List the conditions for a set of functional dependencies to minimal.(5 marks) 6(c) Consider the relation R (A, B, C, D, E, F) and the functional dependencies A&rigtharrow;B, C&rigtharrow;DF, AC→E, D→F. What is the primary key of this relation R? What is the highest normal form? Preserving the dependency, decompose R into third normal form.(10 marks) 7(a) Explain properties of reational decomposition.(5 marks) 7(b) Which normal form specifies multivalued functional dependency? Explain it with examples.(10 marks) 7(c) Define inclusion dependency, and write the inference rules for it.(5 marks) 8(a) Explain transition diagram of a transaction.(6 marks) 8(b) Explain the principles used behind ARIES algorithm.(6 marks) 8(c) What is a schedule? Explain conflict serializable schedule with example.(8 marks)