# GTU Exam Paper Database Managemement System May 2011

GUJARAT TECHNOLOGICAL UNIVERSITY

B.E. Sem-III Remedial Examination May 2011

Subject code: 130703  Subject    Name: Database Management System

Total Marks: 70

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

Student (Rollno, Name, Age,Sex,City)

Student marks (Rollno,Sub 1,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 Sub 1.

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

Q.2 (a)

(i)                 List the benefits of database approach.      02

(ii)               What is constraint in database? Explain types of constraints                                  05 with suitable example.

(i)        Draw symbols for following in E-R diagram:                                                    02 Weak Entity set, Derived attribute

(i)                 List relational algebra operators and explain any two with                                             05 example.

OR

(b)   (i) List the major functions performed by DBA. 02

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                       04 suitable example.

(ii)               Explain 3NF with example.                                                                      03

(i)                         Explain method of query optimization                                                 04

(ii)                       Explain BCNF with example.                                                               03

OR

(i)
What problems can occur due to wrong database design? How 04 they can be solved?

(ii)               Define functional dependency. Explain trivial and non-trivial  03 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.

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

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