Database Management System Question - Pune University December 2011 / DBMS Computer Engineering Question of Pune University
[4063]
– 341
T.E.
(Computer Engineering) (Semester – I) Examination, 2011
DATABASE MANAGEMENT SYSTEMS
(2008
Pattern) (New)
Time : 3 Hours Total
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
1. a) Explain component and overall
structure of DBMS. [10]
b) Explain in detail the different
levels of abstraction. [4]
c) Discuss the entity integrity and
referential integrity constraints. [4]
OR
2. a) How following problems are
handled with DBMS
i) Data isolation
ii) Data redundancy and inconsistency
iii) Data integrity ? [6]
b) Explain significant difference
between File Processing and DBMS. [6]
c) Explain the different constraints
on specialization/generalization with suitable example. [4]
d) What is meant by mapping
cardinality? [2]
3. a) What are different types of
joins in SQL ? Explain with suitable example. [6]
b) Write short note on Dynamic SQL. [6]
c) Consider following database :
Student (Roll_no, Name, Address)
Subject (Sub_code, Sub_name)
Marks (Roll_no, Sub_code, marks)
Write following queries in SQL :
i) Find average marks of each student,
along with the name of student.
ii) Find how many students have failed
in the subject “DBMS”. [4]
OR
4. a) What is cursor ? Explain
explicit cursor and reference cursor in PL/SQL with suitable example. [6]
b) What is stored procedure and
function? [4]
c) Consider the relational database
dept (dept_no, dname, loc, mgrcode)
emp (emp_no, ename, designation)
project (proj_no, proj_name, status)
dept. and emp. are related as 1 to
many.
Project and emp are related as 1 to
many.
Write queries for the following:
i) Give the names of employees who are
working on ‘Blood Bank’ project.
ii) Give the name of managers from
‘MARKETING’ department.
iii) Give all the employees working
under status ‘INCOMPLETE’ projects. [6]
5. 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 lossless decomposition if the
following set F of functional dependencies
hold.
A → BC, CD→E, B→D, E→A. [8]
b) For the relation schema R = (A, B,
C, D, E). Compute the closure F+ and
canonical cover Fc of following set F
of functional dependencies.
A→BC, CD→E, B→D, E→A. [8]
OR
6. a) Describe the concept of
transitive dependency and explain how this concept is used to define 3 NF. [8]
b) Specify Amstrong’s axioms. Use
Amstrong’s axioms to prove the soundness of pseudo transitivity rule. [8]
SECTION
– II
7. a) What is ordered indices ?
Explain the types of ordered indices with suitable example. [10]
b) State the important of query
optimization. [4]
c) How cost of query is measured? [4]
OR
8. 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. [9]
b) What are the steps involved in
query processing? Explain in brief. [9]
9. a) Explain two phase locking
protocol. How does it insure serializabiltiy? [8]
b) Explain shadow paging recovery
scheme and log based recovery scheme. [8]
OR
10. a) When do deadlock happens ? How
to prevent them and how to recover if deadlock takes place? [8]
b) Explain the concept of transaction.
Describe ACID properties for transaction. [8]
11. a) Write a short note on any
two : [12]
i) Data warehouse
ii) Pointer swizzling techniques
iii) Centralized and Distributed
Database Systems.
b) Explain how objects are stored in
relational databases. [4]
OR
12. a) What is the difference between
persistent and transient objects ? How persistence handles in typical object
oriented database system? [8]
b) Explain two-tier and three-tier
architecture. [4]
c) Explain steps for data mining. [4]
——————