Deemed University 2010 M.Tech Computer Science & Engineering University: Lingayas University Term: III Title of the : Advanced Database Management System - Question Paper
Roll No. ..
Lingayas University, Faridabad
M.Tech
(Part-Time)
Computer Science and Engineering
Term-III Examination May, 2010
Advanced Database Management System (CS -503)
[Time: 3 Hours] [Max. Marks: 100]
Before answering the question, candidate should ensure that they have been supplied the correct and complete question paper. No complaint in this regard, will be entertained after examination.
Note: Question No. 1 Section A is compulsory. Attempt any two questions from Section B and any two questions from Section C. In all attempt five questions.
Section A
Q-1 (i) Differentiate between logical & physical data independence. (10x2=20)
(ii) Explain multivalued dependency.
(iii) Differentiate between synchronous & asynchronous distributed database.
(iv) What is cascade-less schedule?
(v) List the ACID properties of a transaction.
(vi) Differentiate between strict two phase locking and rigorous two phase locking.
(vii) What factors made organizations adopt the concept of data warehouse?
(viii) List the steps involved in query processing.
(ix) Name any 2 object-relational Database Management- System (ORDBMS) products.
(x) Differentiate between dependent data mart & independent data mart.
Section B
Q-2. (a) How do you define class, operations, range for attributes, user structures, relationship & abstract class in object definition language (ODL)? Explain with example. (12)
(b) What is object relational database management system. (ORDBMS)? Explain features and advantages of ORDBMS. (8)
Q-3. (a) What is data
warehouse? Explain Generic two level and three level data warehouse
architecture. (10)
(b) Explain data reconciliation (ETL) process. Also explain the
characteristics of data after ETL process. (10)
Q-4. (a) Explain options for distributing a database among the sites of a network with their relative advantages & disadvantages. (10)
(b) What is distributed database? Explain the business conditions that are drivers for the use of distributed database. (5)
(c) Describe the salient characteristics of the variety of distributed database environments. (5)
Section C
Q-5. (a) Indicate if each of the following equivalences are valid, without any knowledge about the relation schemes of R and S. If valid, how could they be used in query modification to improve its evaluation? (10)
(i) sp (R-S) sp R - sp S
(ii) pP (R-S) pPR - pPS
(b) Given R ( A, B,
C) , S (B, C, D) and T ( C, D, E). Draw the query tree for following expression
and apply optimization procedure to it (10)
sB=b (pABC ( R S) pABC (R T)
Q-6. (a) Consider the Schema R = (A, B, C, D) with a set of functional dependencies ( A B, A D, D C).
Normalize the relation upto third normal form. (8)
(b) Consider the relational database of following Schema
Sailors ( Sailorid, Sailername, rating, age)
BOATS (boatid, boatname, color)
RESERVE ( Sailorid, boatid, day)
Give an expression in relational algebra and SQL for each queries (4x3=12)
(i) Find the names of sailors who have reserved boat 103.
(ii) Find the name of sailors who have reserved at least one boat.
(iii) Find the names of sailors who have reserved a red or a green boat.
Q-7. Consider the transaction given below:
READ (A)
A = A -200
WRITE (B)
READ (B)
B = B + 200
WRITE (B)
If initial value of A is 1500 and initial value of B is 1200.
(i) Create log entries corresponding to transactions in case of immediate update.
(ii) Create log entries corresponding to transaction in case of deferred update log.
(iii) If a failure occurs just after the transaction log record for WRITE (B) has been written, then in case of immediate update (a) what action is necessary and why? (b) What are the resulting value of A and B.
(iv) If a failure occurs just after the <T, COMMIT> record has been written, then in case of differed update (a) What action is necessary and why (b) What are the resulting value of A and B. (20)
Q-8. (a) Consider the following two transactions
T1: Read (A) T2: Read (B)
Read (b) Read (A)
If A = 0 then B=B+1 If B=0 then A = A+1
Write (B) Write (A) (10)
(i) Add lock and unlock instruction to transaction T1 and T2, so that they observe the two phase locking protocol.
(ii) Can the concurrent execution of this transaction result in a deadlock? Show a schedule resulting in a deadlock.
(b) We have two transaction T1 and T2 with timestamp value t1 and t2 respectively (t1<t2). The schedule for the concurrent execution of these transactions is given below. Assuming that initially A: 700 Wa, Ra and B: 300, Wb, Rb, Show these values after each step if the timestamp ordering scheme for concurrency control is used. (10)
P.T.O
Step |
Schedule |
Transaction T1 |
Transaction T2 |
1 |
Sum = 0 |
Sum = 0 |
|
2 |
Read (A) |
Read (A) |
|
3 |
Sum = sum + A |
Sum sum + A |
|
4 |
Sum = 0 |
|
Sum = 0 |
5 |
Read (A) |
|
Read (A) |
6 |
A = A 200 |
|
A = A 200 |
7 |
Write (A) |
|
Write (A) |
8 |
Read (B) |
Read (B) |
|
9 |
Sum = sum + B |
Sum = sum + B |
|
10 |
Show (sum) |
Show (sum) |
|
11 |
Sum = sum +A |
|
Sum = sum + A |
12 |
Read (B) |
|
Read (B) |
13 |
B = B + 100 |
|
B = B + 100 |
14 |
Write (B) |
|
Write (B) |
15 |
Sum = sum + B |
|
Sum = sum + B |
16 |
Show (sum) |
|
Show (sum) |
Earning: Approval pending. |