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.

————————

 

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]

——————

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]

————————