Gujarat Technological University 2009-3rd Sem B.E Agricultural and Irrigation Engineering , , , Database Management System -emr- - Question Paper
GUJARAT TECHNOLOGICAL UNIVERSITY
B.E. Sem-III Examination December 2009
Seat No. Enrolment No.
Subject code: 130703 Subject Name: Database Management System
Date: 23 /12 /2009 Time: 11.00 am - 1.30 pm
Total Marks: 70
2. Make suitable assumptions wherever necessary.
3. Figures to the right indicate full marks.
(i) Explain the purpose of the database system. 04
Q.1 (a) (b)
Q.2 (a)
(b)
(ii) Explain different database users. 03
(i) What are the responsibilities of a DBA? 04
(iii) Explain three level architecture of database system 03
(i)Explain candidate key, primary key and foreign key 03
(ii)Explain following relational algebra operation
(ii) Selection and projection operation 02
(i) Explain specialization and generalization feature of ER 03 diagram with example.
(ii) Construct E-R diagram for a hospital with a set of patients and 04 medical doctors. Associate with each patient a log of various tests
and examinations conducted.
(b)
Q.3 (a) (b)
Q.3 (a) (b)
(i) Explain aggregation operation of ER diagram. 03
(ii) Construct E-R diagram of the bank. It provides different kinds of 04 bank accounts. And loans. It operates number of branches.
(i) 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
(i) What is normalization? What is the need for normalization? 04
(ii) Explain 3NF with example 03
(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
(i) Why concurrency control is needed?
(ii) Explain Two phase commit protocol
Q.4 (a) (b)
Q.4 (a) (b)
Q.5
03
04
04
03
04
03
04 03
(i) Explain shadow paging
(ii) Explain mandatory access control of database security.
OR
(i) Explain ACID properties of transaction.
(ii) Explain Two phase locking.
(i) Explain deadlock detection mechanism
(ii) Explain Data encryption in brief.
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)
07
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.
(b)
i) Delete records in supplier table whose status is 40.
01
01
02
03
07
ii) Add one field in supplier table.
iii) Explain commit command
iv) Explain Curser in PL/SQL.
Q.5 (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.
(b)
i) Delete records in parts table whose color is blue.
01
01
02
03
ii) Drop one field in parts table.
iii) Explain rollback command.
iv) Explain stored procedure in PL/SQL.
***********
2
|
Attachment: |
| Earning: Approval pending. |
