Database Management System Question - Pune University December 2011

Database Management System Question - Pune University December 2011 / DBMS Computer Engineering Question of Pune University

[4063] – 341

T.E. (Computer Engineering) (Semester – I) Examination, 2011

DATABASE MANAGEMENT SYSTEMS

(2008 Pattern) (New)

Time : 3 Hours                                                             Total Marks : 100

Instructions : i) Answers to the two Sections must be written in separate books.

ii) Neat diagrams must be drawn whenever necessary.

iii) Assume suitable data if necessary.

iv) Solve Section I : Q.1 or Q.2, Q.3 or Q.4, Q.5 or Q.6.

v) Solve Section II : Q.7 or Q.8, Q.9 or 10, Q.11 or Q.12.

SECTION – I

1. a) Explain component and overall structure of DBMS. [10]

b) Explain in detail the different levels of abstraction. [4]

c) Discuss the entity integrity and referential integrity constraints. [4]

OR

2. a) How following problems are handled with DBMS

i) Data isolation

ii) Data redundancy and inconsistency

iii) Data integrity ? [6]

b) Explain significant difference between File Processing and DBMS. [6]

c) Explain the different constraints on specialization/generalization with suitable example. [4]

d) What is meant by mapping cardinality? [2]

3. a) What are different types of joins in SQL ? Explain with suitable example. [6]

b) Write short note on Dynamic SQL. [6]

c) Consider following database :

Student (Roll_no, Name, Address)

Subject (Sub_code, Sub_name)

Marks (Roll_no, Sub_code, marks)

Write following queries in SQL :

i) Find average marks of each student, along with the name of student.

ii) Find how many students have failed in the subject “DBMS”. [4]

OR

4. a) What is cursor ? Explain explicit cursor and reference cursor in PL/SQL with suitable example. [6]

b) What is stored procedure and function? [4]

c) Consider the relational database

dept (dept_no, dname, loc, mgrcode)

emp (emp_no, ename, designation)

project (proj_no, proj_name, status)

dept. and emp. are related as 1 to many.

Project and emp are related as 1 to many.

Write queries for the following:

i) Give the names of employees who are working on ‘Blood Bank’ project.

ii) Give the name of managers from ‘MARKETING’ department.

iii) Give all the employees working under status ‘INCOMPLETE’ projects. [6]

5. a) What is decomposition ? Suppose that we decompose the schema R = (A, B, C, D, E) into (A, B, C) and (A, D, E). Show that this decomposition

is lossless decomposition if the following set F of functional dependencies

hold.

A → BC, CD→E, B→D, E→A.          [8]

b) For the relation schema R = (A, B, C, D, E). Compute the closure F+ and

canonical cover Fc of following set F of functional dependencies.

A→BC, CD→E, B→D, E→A.             [8]

OR

6. a) Describe the concept of transitive dependency and explain how this concept is used to define 3 NF. [8]

b) Specify Amstrong’s axioms. Use Amstrong’s axioms to prove the soundness of pseudo transitivity rule. [8]

SECTION – II

7. a) What is ordered indices ? Explain the types of ordered indices with suitable example. [10]

b) State the important of query optimization. [4]

c) How cost of query is measured? [4]

OR

8. a) Construct a B+ tree for the following set of key values :

(2, 3, 5, 7, 11, 17, 19, 23, 29, 31). Assume order of tree is 4.       [9]

b) What are the steps involved in query processing? Explain in brief. [9]

9. a) Explain two phase locking protocol. How does it insure serializabiltiy? [8]

b) Explain shadow paging recovery scheme and log based recovery scheme. [8]

OR

10. a) When do deadlock happens ? How to prevent them and how to recover if deadlock takes place? [8]

b) Explain the concept of transaction. Describe ACID properties for transaction. [8]

11. a) Write a short note on any two :                      [12]

i) Data warehouse

ii) Pointer swizzling techniques

iii) Centralized and Distributed Database Systems.

b) Explain how objects are stored in relational databases. [4]

OR

12. a) What is the difference between persistent and transient objects ? How persistence handles in typical object oriented database system? [8]

b) Explain two-tier and three-tier architecture. [4]

c) Explain steps for data mining. [4]

——————