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

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

[3963] – 351

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

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

1. a) Compare Relational data model, Hierarchical Data Model and Network Data Model. [6]

b) Design an E-R diagram with EER features which will model all the entities and relationships among them for the Airline Reservation System Database. [6]

c) Explain Multi-user DBMS Architectures in details. [5]

OR

2. a) Design an E-R diagram with EER features which will model all the entities and relationships among them for the Hospital Management System Database. [9]

b) Explain Overall Structure of DBMS. [8]

3. a) List difference between embedded SQL and Dynamic SQL. [6]

b) Explain the different operations of Relational Algebra. [5]

Consider the following Relations. It defines the schema of the database application for a bank. It manages the branches and customers of the bank.

Customers take loans (borrow money) or open accounts (deposit money) at one or more branches.                  [6]

Branch (B_No, B_name, B_city, asset), Customer (C_No,C_Name, C_citystreet), Loan(Loan_no, B_name, amount), Account (Acc_No, B_name, Balance), Borrower (C_No, Loan_No), Depositor (C_No, Acc_No)

Answer the following queries in each of the query languages that you know:

1) Find the names and address of customers who have a loan.

2) Find loan data, ordered by decreasing amounts, then increasing loan numbers.

3) Find the pairs of names of different customers who live at the same address but have accounts at different branches.

OR

4. a) Explain Assertion and Triggers with suitable example. [6]

b) Explain stored procedure and stored function.

Consider the following Relations. It defines the schema of the database application for a library.             [5]

Book (Book_ISBN [pk], Title, Publisher_Name [fk])                     [6]

BOOK_AUTHORS (Book_ISBN [pk, fk], Author_Name [pk])

PUBLISHER (Name [pk], Address, Phone)

BOOK_COPIES (Book_ISBN [pk, fk], Branch_ID [pk, fk], Num_Copies)

BOOK_LOANS (Book_ISBN [pk,fk], Branch_ID [pk, fk], Card_Num [pk, fk], Date_Out, Date_Due)

LIBRARY_BANCH (Branch_ID[pk], Branch_Name, Address)

BORROWER (Card_Num [pk], Name, Address, Phone)

Answer the following queries in each of the SQL query languages that you know:

1) List the ISBN and title of all books written by “John Smith”.

2) List the ISBN and title of all books written by “John Smith” as the only author.

3) List the Card number and name of all borrowers who checked out two or more books on 10/16/2003.

4) List the branch ID and name of all library branches that have at least one copy of all the books.

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

b) Write a short note on view. Define Multi-valued dependency. List all the non-trivial Multi-valued dependency satisfied by the relation given below:                   [8]

A	B	C
a1	b1	c1
a1	b1	c2
a2	b1	c1
a2	b1	c3

OR

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

b) 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 holds :                [8]

A→BC CD→E B→D E→A

SECTION – II

7. a) Discuss the techniques for allowing hash file to expand and shrink dynamically. What are the advantages and disadvantages of each? [9]

b) What are the advantages and disadvantages of hash indices relative to B-tree indices? How might the type of index available influence the choice of a query processing strategy? [8]

OR

8. a) Explain insertion operation on B+ tree with suitable example. [9]

b) Construct a B + tree for following set of the key values.          [8]

(2, 3, 5, 7, 11, 17, 19, 23, 29, 31)

Assume the order of tree is 4

9. a) Define serializability. Give test for conflict serializability. Check whether following schedule is conflict serializable.   [9]

T1	T2
Read(A) Write (A) Read(B) Write(B)	Read(A) Write(A) Read(B) Write(B)

b) How does the granularity of data items affect the performance of concurrency control ? What factors affect the selection of granularity size of data items?    [8]

OR

10. a) Show that the two phase locking protocol ensures conflict serializability.    [9]

b) What is Concurrency control? Explain time stamp based protocol. Compare the differed and immediate versions of the log based recovery scheme.     [8]

11. a) What is the difference between Persistent and Transient objects? How is persistence handled in the typical object oriented database system?   [8]

b) What are the various issues that decide the time cost communication between client and server?      [8]

OR

12. a) Write a short note on :                     [12]

i) Data Warehouse Manager

ii) Pointer Swizzling Techniques.

b) Specify the steps in accessing the data object in Conventional DBMS and OODBMS.                 [4]

————————