Visvesvaraya Technological University (VTU) 2006 B.E Fifth Semester , uary/ruary , Database Management Systems - Question Paper

Fifth Semester B.E. Degree Examination, January/February 2006
Computer Science/Information Science and Engineering
Database Management Systems
Time: three hrs. Max.Marks: 100
Note: ans any 5 full ques..
1. (a) A bank has many branches the bank has many customers. A customer can open
many various types of accounts with the bank. Any customer of the bank can
take loan from the bank. All branches can provide loans, Banks have also installed
automatic teller machines, from which a customer can withdraw from his/her bank.
Draw the ER diagram for the bank. Create three NF tables of your design. Make
suitable assumptions, if any. (10 Marks)
(b) define the different functions which are needed to be performed by the
database administrator. (6Marks)
(c) elaborate the disadvantages of database system? discuss them briefly.
(5 Marks)
2. (a) What is a participator role? When is it necessary to use role names in the
description of relationship types? (8 Marks)
(b) explain the naming convention used for ER schema diagram. (7 Marks)
(c) What is the FUNCTION operation? What is it used for? (5 Marks)
3. (a) In relational algebra, explain a few kinds of quenes for which renaming is
necessary in order to specify the query unambiguously. (5 Marks)
(b) Consider the 2 tables T1 and T2 show the outcome of the subsequent operations:
i) T1 ¥ T1.P=T2.A T2
ii) T1 ¥ T1.Q=T2.B T2
iii) T1 = ¥ T1..P=T2.A T2
iv) T1 ¥=T1.Q=T2.R T2
v) T1 È T2
vi) T1 ¥ (T1..P=T2.A and T1.R=T2.C) T2
Table T1
Table T2
4. (a) Consider the subsequent relations for a database that keeps track of business trips of
sales persons in a sales office.
SAILOR (SID, SNAME, RATING, AGE)
BOATS (BID, BNAME, COLOR)
RESERVES (SID, BID, DAY)
Specify the subsequent queries in SQL and in relational algebra
i) obtain the names of sailors who have reserved a red boat.
ii) obtain the names of sailors who have reserved a red or a green boat.
iii) obtain the names of sailors who have reserved all boats called ‘Interlake'
(12 Marks)
(b) How do the relations (tables) in SQL differ from the relations described formally?
explain the differences in terminology. Why does SQL allow duplicate tuples in a
table or in a query result? (8 Marks)
5. (a) explain insertion, deletion and modification anomalies. Why are they considered
bad? Illustrate with examples. (9 marks)
(b) Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J} and the set of
functional dependencies
F = {{A, B} ® {C}, {A} ® {D, E} {B}® {F}, {F} ® {G, H}, {D} ® {I, J}.
What is the key for R? Decompose R into 2NF, then 3NF relations (8 Marks)
(c) explain the issue of spurious tuples and how we may prevent it. (3 Marks)
6. (a) What is serialisablility? How can serialisability be ensure? Do you need to
restrict concurrent execution of transaction to ensure serialisability? Justify your
answer. provide an example of transactions and how you can force serialisability
in those transactions. (10 Marks)
(b) elaborate the steps 1 must take with its database management system, in order
to ensure disaster recovery? describe the process of recovery in case of disaster.
(10 Marks)
7. (a) What is 2 phase locking? define with help of an example. Will 2 phase
locking outcome in serialisable schedule? Will 2 phase locking outcome in
deadlock? Justify your ans with the help of an example. (10 Marks)
(b) What is shadow paging scheme? Where is it used? (5 Marks)
(c) What is the multiversion
technique of concurrency control? define with the
help of an example. Will this scheme outcome in rollback and /or deadlock? Justify
your ans.
8. (a) What is time stamping? discuss a mechanism of concurrency control that uses
time stamping with the help of an example. (10 Marks)
(b) What is intention made locking? define the different intention mode locks with
the help of an example. (10 Marks)

Earning:   Approval pending.