# GTU Last Year question papers

GUJARAT TECHNOLOGICAL UNIVERSITY
B.E. Sem-III Remedial Examination May 2011
Subject code: 130703

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) Consider following schema and write SQL for given statements.
Student(Rollno, Name, Age,Sex,City)
Student_marks(Rollno,Sub1,Sub2,Sub3,Total,Average)
Write query to
(i) Calculate and store total and average marks from Sub1, Sub2 & Sub3.
(ii) Display name of students who got more than 60 marks in
subject Sub1.
(iii) Display name of students with their total and average marks.
(iv) display name of students who got equal marks in subject Sub2

(b) Explain database system architecture with diagram in detail.
Q.2 (a) (i) List the benefits of database approach.
(ii) What is constraint in database? Explain types of constraints with suitable example.

(b) (i) Draw symbols for following in E-R diagram:
Weak Entity set, Derived attribute
(ii) List relational algebra operators and explain any two with example.

OR
(b) (i) List the major functions performed by DBA.
(ii) Draw E-R diagram for supplier who supplies different parts. The parts are used in different projects. Explain the mapping cardinality used. Assume suitable attributes.

Q.3 (a) (i) Explain generalization and specialization in ER diagram with
suitable example.
(ii) Explain 3NF with example.

(b) (i) Explain method of query optimization
(ii) Explain BCNF with example.

OR
Q.3 (a) (i) What problems can occur due to wrong database design? How
they can be solved?
(ii) Define functional dependency. Explain trivial and non-trivial FD with example.

(b) (i) Explain various steps of query evaluation.
(ii) Given relation R with attributes A,B, C,D,E,F and set of FDs
as A-> BC, E-> CF, B->E and CD-> EF. Find out closure {A,
B} + of the set of attributes.

Q.4 (a) What is concurrency? What are the three problems due to concurrency? How the problems can be avoided, explain for one of the three problems.
(b)(i) Explain Two-Phase Locking protocol.
(ii) What is the difference between security and integrity?

OR
Q.4 (a) Explain system recovery procedure with check point record concept. 07
(b)
(i) Explain Two-Phase commit protocol.
(ii) State and explain Heath’s Theorem.

Q.5 (a)
(i) Explain briefly the meaning of serializability of transactions.
(ii) Explain the difference between Discretionary access control
and mandatory access control.
(iii) What is deadlock? Explain Wait-For-Graph.

(b) (i) What are transaction control commands? Explain any two commands.
(ii) Write short note on database triggers in PL/SQL.

OR
Q.5 (a) (i) What is security of data? Explain data encryption.