GTU Database Management System Question Paper Dec 2009
GUJARAT TECHNOLOGICAL UNIVERSITY
B.E. Sem-III Examination December 2009
Subject Name: Database Management System
- Attempt all questions.
- Make suitable assumptions wherever necessary.
- 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.
(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
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.
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)
Answer the following queries in SQL.
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.
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.