Database Management System Question - Pune University December 2012

Database Management System Question - Pune University December 2012 / DBMS Computer Engineering Question of Pune University / DBMS for BE-BTech CSE and IT of Pune University

[4263] – 341

T.E. (Computer / IT Engg.)

DATABASE MANAGEMENT SYSTEMS

(2008 Pattern) (Semester - I)

Time : 3 Hours                                                             Max. 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

Q1) a) Explain various components and database system structure with the help of neat diagram. [10]

b) Explain multi-user DBMS architecture. [4]

c) Specify the CODD’s norms to be specified by RDBMS. [4]

OR

Q2) a) What are the various features of extended E-R diagrams [EER]. Explain in details with the help of example. [10]

b) What is a data abstraction? [4]

c) What is integrity constraint? Explain referencial integrity constraints. [4]

Q3) a) Consider the relational database: [8]

dept (dept_no, dname, LOC)

emp (emp_no, ename, designation)

project (proj_no, proj_name, status)

empproject (emp_no, proj_no)

dept and emp are related as 1 to many

proj and emp are related as 1 to many

Write relational algebra expressions for the following:

i) List all employees of ‘INVENTORY’ department of ‘PUNE’ location.

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

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

iv) Give all the employees working under status ‘INCOMPLETE’ projects.

b) Explain stored procedures and triggers with example. [8]

OR

Q4) a) Explain various join operations with example. [8]

b) What is cursor? Explain various types of cursor. [8]

Q5) a) State and prove Armstiong’s axioms for functional dependencies. [8]

 b) A set of FD’s for the relation R (A, B, C, D, E, F) is AB → C, C → A, BC → D, ACD → B, BE → C, EC → FA, CF → BD, D → E. Find the minimum canonical cover for this set of FD’s. [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 loseless decomposition if the following set F of functional dependencies holds:

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

b) Define Boyce-Codd normal form. How does it differ from 3NF? Why is it considered a stronger form of 3NF? [8]

SECTION – II

Q7) 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. [8]

b) Explain the role of “selection” operation in query processing. [8]

OR

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

b) Explain equivalence rules for query optimization. [8]

Q9) a) Which are different crash recovery methods? Explain any one in detail? [8]

b) What is meant by ‘Transaction’? Explain obstruct transaction model with state diagram. [8]

OR

Q10) a) Explain two-phase locking protocol. How does it ensure resializality? [8]

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

Q11) Write a short note on any three: [18]

a) Data warehouse.

b) Association rules for data mining.

c) Distributed database system.

d) Persistent programming languages.

OR

Q12) a) Compare relational model Vs object-oriented model. [6]

b) What is fragment of a relation? What are the main types of fragments? Why is fragmentation a useful concept in distributed database design? [8]

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

——————