Database Management System - Dec 2014
Computer Engineering (Semester 3)
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) Write queries for the following tables:
T1 ( Empno, Ename , Salary, Designation)
T2 (Empno, Deptno.)
(1) Display all the details of the employee whose salary is lesser than 10K.
(2) Display the Deptno in which Employee Seeta is working.
(3) Add a new column Deptname in table T2.
(4) Change the designation of Geeta from 'Manager' to 'senior Manager'.
(5) Find the total salary of all the employees.
(6) Display Empno, Ename, Deptno and Deptname.
(7) Drop the table T1.(7 marks) 1 (b) (i) What is deadlock? Illustrate the same using the Wait-For-Graph.(4 marks) 1 (b) (ii) Explain the different types of database users.(3 marks) 2 (a) Draw an Entity Relation diagram for the Hospital Management System. Consider the different types of Patients with respect to Disease and In-Patient and Out-Patient Department in the design. Consider the availability of all well qualified Doctors. Consider various types of tests and operations to be conducted. Explain the mapping cardinality used. Assume suitable attributes. Use generalization and Specialization.(7 marks)
Answer any one question from Q2 (b) & Q2 (c)
2 (b) Explain time stamp based protocols in detail .(7 marks) 2 (c) Explain the three level architecture of DBMS.(7 marks)
Answer any two question from Q3 (a), (b) & Q3 (c), (d)
3 (a) (i) What is normalization? Explain the need for normalization.(4 marks) 3 (a) (ii) What are Multi-valued dependencies? Explain with an appropriate example.(3 marks) 3 (b) Write a PL/SQL block to print the sum of even numbers from 1 to 100.(7 marks) 3 (c) (i) Explain advantages and disadvantages of Conventional File-based system over Database management system.(5 marks) 3 (c) (ii) What is Functional Dependency? Explain.(2 marks) 3 (d) Write a PL/SQL block to print the sum of odd numbers from 1 to 100.(7 marks)
Answer any two question from Q4 (a), (b) & Q4 (c), (b)
4 (a) (i) What is a Transaction? Explain the properties of the transaction. Explain the States of the transaction with a neat sketch.(4 marks) 4 (a) (ii) Explain the differences between Discretionary access control and mandatory access control.(3 marks) 4 (b) What is a view? Explain the syntax. Explain the different types of views.(7 marks) 4 (c) (i) What is a constraint in database? Explain types of constraints with a suitable example.(4 marks) 4 (c) (ii) List all the Relational algebra operators. Explain the working of Cartesian product Operation and the Division Operation with an appropriate example.(3 marks) 4 (d) What are triggers? Explain the advantages and the needs.(7 marks)
Answer any two question from Q5(a), (b) & Q5 (c), (d)
5 (a) (i) Explain the Immediate Database modification Log Based Recovery method with an appropriate example. Explain role of check point in Log base.(5 marks) 5 (a) (ii) What is Query Optimization Process? Explain.(2 marks) 5 (b) (i) What is a Join? Explain different types of outer join with appropriate example.(5 marks) 5 (b) (ii) Explain the Rollback and commit commands.(2 marks) 5 (c) (i) What problems can occur due to the wrong database design? How they can be solved?(4 marks) 5 (c) (ii) What is security of data? Illustrate Data encryption with an appropriate example.(3 marks) 5 (d) (i) Explain two phase locking.(5 marks) 5 (d) (ii) Explain DDL statements.(2 marks)