GTU Database Management System Question Paper Dec 2009

GTU Database Management System Question Paper Dec 2009

GUJARAT TECHNOLOGICAL UNIVERSITY

B.E. Sem-III Examination December 2009

 Subject     Name: Database Management System

Instructions:

  1. Attempt all questions.
  2. Make suitable assumptions wherever necessary.
  3. Figures to the right indicate full marks.

 Q.1 (a)      (i)   Explain the purpose of the database system.                                       04

(ii)   Explain different database users.                                                       03

(b)   (i) What are the responsibilities of a DBA?                                 04

(iii) Explain three level architecture of database system                           03

Q.2 (a) (i) Explain candidate key, primary key and foreign key              03

(i)     Explain following relational algebra operation

(i)           Natural join operation                                                              02

(ii)   Selection and projection operation                                                     02

(b)  (i) Explain specialization and generalization feature of ER       diagram with example.

(ii)  Construct E-R diagram for a hospital with a set of patients and   medical doctors. Associate with each patient a log of various tests and examinations conducted.

OR

(b) (i) Explain aggregation operation of ER diagram.                               03

(i)     Construct E-R diagram of the bank. It provides different kinds of 04 bank accounts. And loans. It operates number of branches.

Q.3 (a)   What are anomalies in database design? How can we solve it?  04

(ii) Explain BCNF with example.                                                             03

(i)   Explain how to find closure of a set of attributes?                              03

(ii)   Explain query optimization process.                                                  04

OR

Q.3 (a) What is normalization? What is the need for normalization?       04

(b) Explain 3NF with example                                                                 03

(b) (i)  What is non-loss decomposition in database? How it is useful in 03 database?

(ii) Explain evaluation of expression process in query optimization. 04

Q.4 (a) Why concurrency control is needed?

(ii) Explain Two phase commit protocol

(b) (i) Explain shadow paging

(ii) Explain mandatory access control of database security.

OR

Q.4 (a) Explain ACID properties of transaction.

(ii) Explain Two phase locking.

(b) (i)  Explain deadlock detection mechanism

(ii) Explain Data encryption in brief.

Q.5 (a) we have following relations: Supplier(S #,sname,status,city)

Parts(P#,pname,col or,weight, city)

SP(S#,P#,quantity)

Answer the following queries in SQL.

(a)

i)   Find name of supplier for city = ‘Delhi’.

ii)   Find suppliers whose name start with ‘AB’

iii)   Find all suppliers whose status is 10, 20 or 30.

iv)   Find total number of city of all suppliers.

v)  Find s# of supplier who supplies ‘red’ part.

vi)   Count number of supplier who supplies ‘red’ part.

vii)    Sort the supplier table by sname.

i)   Delete records in supplier table whose status is 40.

ii)   Add one field in supplier table.

iii)   Explain commit command

iv)   Explain Curser in PL/SQL.

OR

(a)

i)   Find name of parts whose color is ‘red’

ii)   Find parts name whose weight less than 10 kg.

iii)   Find all parts whose weight from 10 to 20 kg.

iv)                  Find    average weight of all parts.

v)  Find S# of supplier who supply part ‘p2’

vi)   Find name of supplier who supply maximum parts.

vii)    Sort the parts table by pname.

i)   Delete records in parts table whose color is ‘blue’.

ii)   Drop one field in parts table.

iii)   Explain rollback command.

iv)   Explain stored procedure in PL/SQL.

Leave a Comment