Database Management Systems (DBMS) Question - Pune University May 2012

Database Management Systems (DBMS) Question - Pune University May 2012 / Pune University BE (CSE) and BTech (IT) DBMS Question - May 2012

[4163] – 341

T.E. (Computer Engg.) (Semester – I) Examination, 2012

DATABASE MANAGEMENT SYSTEMS

(Common to IT)

(2008 Pattern) (New)

Time: 3 Hours                                           Max. Marks: 100

Instructions : 1) Answers to the two Sections should be written in separate books.

2) Neat diagrams must be drawn wherever necessary.

3) Assume suitable data, if necessary.

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

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

SECTION – I

Q1) a) Compare various data models. [10]

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

c) Compare DBMS and file processing system with following points. [4]

i) Redundancy. ii) Access Control.

OR

Q2) a) What is difference between specialization and generalization? Why do we not display this difference in schema diagram? [6]

b) Specify the CODD’s norms to be specified by RDBMS. [6]

c) What are the enhancements that distinguish the EER model from the ER Model? Explain with example. [6]

Q3) a) Consider following relational database;             [8]

Employee (emp_name, street, city).                                                     

Works (emp_name, company_name, salary).

Company (company_name, city).

Manages (emp_name, manager_name).

For each of the given query, give expression in relational algebra;

i) Find emp _ name, street and cities of residence whose salary exists in between 30,000 to 40,000 and work for XYZ Ltd.

ii) Find the name, street and cities of employees who live in the same city as the company they work for.

b) Write a short note on dynamic and embedded SQL. [8]

OR

Q4) a) Explain stored procedures and triggers. [8]

b) Explain create, Insert, update and delete operations with respective to views. [8]

Q5) a) Let R = (A, B, C, D, E) and let M be the following set of multi-valued dependencies A BC, B CD, E AD.

List the non-trivial dependencies in M⁺. [8]

b) Explain why 4NF is more desirable than BCNF. Rewrite the definition of 4NF and BCNF using the notions of domain constraints. [8]

OR

Q6) 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 a lossless decomposition if the following set F of functional dependencies holds. A → BC, CD → E, B → D, E → A. [8]

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

SECTION - II

Q7) a) Explain in detail use of B-Tree as an indexing technique. Compare B-Tree and B+-Tree. [8]

b) Explain roll of “selection” operation in query processing. [6]

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

OR

Q8) a) What are the steps involved in query Processing? Explain each in brief. [8]

b) What are the various techniques to handle variable length records? Explain any one in details. [8]

c) Define dense index. [2]

Q9) a) Explain the concept of ‘Transaction’. Describe ACID properties for transaction. [8]

b) Explain deferred database modifications and immediate database modifications and their difference in the context of recovery. [8]

OR

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

b) Explain recoverable and cascadeless schedules. [8]

Q11) a) Explain how persistant pointer is implemented, compare this implementation with that of pointers as they exists in general purpose language such as ‘C’. [8]

b) Specify advantages and disadvantages of distributed database system. [8]

OR

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

i) Pointers swizzling techniques.

ii) Persistent programming language.

iii) Association rules for data mining.

b) Explain the need of backup and replication. [4]

————————