Saturday, December 5, 2015

GATE 2015 Questions - DBMS

GATE-2015
1. Consider the following transaction involving two bank accounts x and y.
read (x) ; x := x – 50; write (x); read (y); y := y + 50; write (y)
The constraint that the sum of the accounts x and y should remain constant isthat of
(a) Atomicity
(b) Consistency
(c) Isolation
(d) Durability

Answer: option (b)
Explanation:
Consistency assures that after any transaction, the state of the database will remain valid. Database state refers to all stored values within the database at a given instance. A valid database must satisfy all constraints specified in the schema and over the database.
In the above transaction, 50 has been deducted from x, and y  has been incremented by 50. For the database to be valid after transaction, net amount  x+y should remain constant before and after the transaction .This is ensured by property consistency.

GATE - 2015
2. With reference to the B+ tree index of order 1 shown below, the minimum number of nodes (including the root node) that must be fetched in order to satisfy the following query: “Get all records with a search key greater than or equal to 7 and less than 15” is ________.
Ans: 5
Explanation:
In a B+ tree, data pointers are stored only at the leaf nodes. The leaf nodes contain the value of the search field as well as a pointer to the record/block that contains the record.In order to provide ordered access, the leaf nodes have been linked.
In the above question, in order to fetch all records with key values greater than or equal to 7 and less than 15, we first need to locate the leaf node that stores 7.To reach the node with key 7,we start from root with key value 9,follow left pointer to arrive at a node with key 5 (since search key 7 is less than 9). From node with key 5, we follow the right pointer to reach the leaf node with key value 7. Now we move from 7 till 15 sequentially towards right, thus traversing another 2 nodes.
Thus total number of nodes fetched in order to satisfy the query is 5.

GATE-2015
3. Consider two relations R1 (A, B) with the tuples (1, 5) (3,7)and R2(A, C) = (1, 7), (4, 9). Assume that R(A,B,C) is the full natural outer join of R1 and R2. Consider the following tuples of the form (A,B,C)” a = (1, 5, null), b = (1, null, 7), c = (3, null, 9), d = (4, 7, null), e = (1, 5, 7), f = (3, 7, null), g = (4, null, 9). Which one of the following statements is correct?
(a) R contains a, b, e, f, g but not c, d
(b) R contains a, b, c, d, e, f, g
(c) R contains e, f, g but not a, b 
(d) R contains e but not f, g

Ans: option (c)
Explanation:
R1 :

A

B

1

5

3

7

R2:

A

C

1

7

4

9

A full outer join of R1and R2 returns all rows of R1 and R2. Thus resulting relation
R(A,B,C)  is as shown below :

A

B

C

1

5

7

3

7

null

4

null

9

Hence in the tuples correspond to e, f and g in the question.

GATE-2015
4. Consider a simple checkpointing protocol and the following set of operations in the log.
(start, T4); (write, T4, y, 2, 3); (start, T1); (commit, T4); (write, T1, z, 5, 7); (checkpoint);

(start, T2); (write, T2, x, 1, 9); (commit, T2); (start, T3); (write, T3, z, 7, 2);

If a crash happens now and the system tries to recover using both undo and redo operations, what are the contents of the undo list and the redo list
(a) Undo: T3, T1; Redo: T2
(b) Undo: T3, T1; Redo: T2, T4
(c) Undo: none; Redo: T2, T4, T3; T1 
(d) Undo: T3, T1, T4; Redo: T2

Answer: option (a)
Explanation:
The given set of operations can be rewritten as :
T1
T2
T3
T4



start



Write(y, 2, 3)
start






commit
write(z, 5, 7)



checkpoint
checkpoint
checkpoint
checkpoint

start



write( x, 1, 9)



commit




start



write( z, 7, 2)

crash
crash
crash
crash

Checkpoint is a mechanism where all the previous logs are removed from the system and stored permanently in a storage disk. Checkpoint declares a point before which the DBMS was in consistent state, and all the transactions were committed. During recovery the system reads the logs backwards from the end to the last checkpoint. 
(Reference :http://www.tutorialspoint.com/dbms/dbms_data_recovery.htm ) .
In the question, transactions T1 and T3 are uncommitted hence these transactions are undone during recovery. T4 is committed before check pointing. Since T2 is committed after the checkpoint, it has to be redone.


5. A file is organized so that the ordering of data records is the same as or close to the ordering of data entries in some index. Then that index is called
(a) Dense 
(b) Sparse
(c) Clustered 
(d) Unclustered

Ans: option (c)
Explanation:
With a clustered index the physical records (rows) are stored on the disk in the same order as that of index . Hence every table can have exactly one clustered index.

MORE QUESTIONS WILL BE UPDATED SOON

Contributed by: RADHIKA V.M., M.Tech, College of Engineering Trivandrum


No comments:

Post a Comment