Saturday, April 18, 2015

Database Management System Question - Pune University December 2011

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


(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.


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]
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]
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
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]
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]


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]
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]
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]
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]


Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery