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

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

UNIVERSITY OF PUNE

[4363] – 251

T.E. (Computer & IT Semester – I) Examination, 2013

DATABASE MANAGEMENT SYSTEMS

 (2008 Pattern)

Time: 3 Hours                                           Max. Marks: 100

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

2) Black figures to the right indicate full marks.

3) Assume suitable data, if necessary.

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

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

SECTION – I

Q1) A) How following problems are handled with DBMS. [6]

i. Data Isolation

ii. Data Redundancy and Inconsistency

iii. Data Integrity

B) Explain with examples how ER diagrams are converted into tables. [6]

C) Explain the need of the following. [6]

i. View       ii) Foreign Key.

OR

Q2) A) Explain various database languages. [8]

B) Explain various data models used in DBMS. [10]

Q3) A) Given relation schema: R(A,B,C), S(D,E,F). Let the relation r® and s(S) be given. Convert the following SQL statements in relational algebra form.               [8]

1. select * from r where B = 17;

2. select A, F from r,s where r.C = s.D;

3. update r set B = B*15 where A = ‘aaa’;

4. select * from s where E<20;

B) Explain various operations in relational algebra. [8]

OR

Q4) A) What is Cursor? Explain various types of Cursor. [8]

B) Explain stored procedures and triggers. [8]

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

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

OR

Q6) A) Let R=(A,B,C,D,E) and let M be the following set of multi-valued dependencies. [8]

A →→ BC, B →→ CD, E →→ AD.

List the non-trivial dependencies in M+.

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

SECTION - II

Q7) A) What is ordered indices? Explain the types of ordered indices with suitable example. [9]

B) Explain detail use of B Tree as an indexing technique. Compare B tree and B+ tree. [9]

OR

Q8) A) Explain following: [9]

i. Dense index    ii. Sparse index          iii. Clustered index

B) Give the transformation rules for releational expressions. [9]

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

B) Show the two phase locking protocol ensures conflict serializability. [8]

OR

Q10) A) Explain timestamp based protocol. [8]

B) State and explain Thomas Write rule. [8]

Q11) A) How does the concept of an object in the object oriented model differ from the concept of an entity in the ER model. [8]

B) Explain the need of backup and replication. [8]

OR

Q12) A) What is fragment of a relation? What are main types of fragments? [8]

B) Write short note on:

i. Data warehouse manager

ii. Pointers swizzling techniques.

————————