Relational database management systems mcq quiz with answers 21

MCQ on various aspects in DBMS including database disk access and querying, cost for reading disk block pages, count the number of disk pages required to store a table, serializability, serial schedules, RDBMS Multiple Choice Questions with Answers

Database management systems - MCQ exam questions

1. Which of the following is TRUE about serializable schedules?

a) Every serializable schedule is recoverable.

b) Every serializable schedule contains no conflicting actions.

c) Every 2PL schedule is serializable.

d) Every view serializable schedule is also a conflict serializable

Answer: (c) Every 2PL schedule is serializable Two-phase locking protocol ensures conflict serializability. Consider any transaction. If the transaction has acquired its final lock, we say that it has reached the end of growing phase. The point in the schedule is referred as lock point. Now, transactions can be ordered according to their lock points—this ordering is, in fact, a serializability ordering for the transactions. Every serializable schedule is recoverable – FALSE Every serializable schedule contains no conflicting actions – FALSE. View serializable schedules may have conflicting actions. Every view serializable schedule is also a conflict serializable – FALSE. Alternate of this statement is true.

 

2. Let us join two relations R(a, b) and S(a, c) on attribute a using a merge join. Suppose B(R)=10,000 and B(S)=5,000. Assume a memory size of 101 blocks. If both R and S are already sorted on a, how many disk IOs are needed for a merge join?

a) 10000

b) 15000

c) 5000

d) 150000000

Answer: (b) 15000 Cost of merge join for sorted relations = Sum of number of disk blocks occupied by the relations.                                                                         = B(R) + B(S) = 10000 + 5000 = 15000

 

3. Which of the following is TRUE about recoverable schedules?

a) In a recoverable schedule, if a transaction T commits, then any other transaction that T read from must also have committed.

b) In a recoverable schedule, no transaction will ever be aborted because a transaction that it read from has aborted.

c) Every recoverable schedule is serializable.

d) Every cascadeless schedule is also non-recoverable.

Answer: (a) In a recoverable schedule, if a transaction T commits, then any other transaction that T read from must also have committed. Let us suppose that there are two transactions T1 and T2 in a schedule. Also, let us suppose that T2 reads a data item (say A) which was written by T1. That is, WRITE(A) of T1 appears before READ(A) of T2. Then, the COMMIT instruction of T1 must appear before the COMMIT of T2.

 

************************

Related posts:

• Go to Multiple Choice Questions in DBMS home

• Go to Normalization - MCQs page

• Go to General Mixed Quiz in all topics of DBMS page

• Go to Advanced DBMS Concepts page

Quiz questions with answers on DBMS concepts

calculate blocking factor

cost for reading disk block pages using binary search

What is recoverable schedule?

What is serializable schedules?

Define recoverable and serializable schedule with examples  

 

Serializable Schedules and Transactions - solved examples

Given schedule is conflict serializable or not exercise, view serializable schedules, serializability exercises, how to find whether a schedule is conflict serializable or view serializable? view serializability example, non-conflict serializability example

 

View Serializable Schedule - Solved Exercise

Question:

Is the following schedule S is serializable? If yes, is it conflict serializable or view serializable?

R₁(C), W₁(B), R₂(B), W₂(A), W₁(A), W₂(A)

 

Solution:

Conflict serializable schedule: A schedule is conflict serializable if it can be transformed into an equivalent serial schedule by swapping pairs of non-conflicting instructions. Two instructions conflict if they involve the same data item and at least one of them is a WRITE.

 

View serializable schedule: A schedule is view serializable if it is view equivalent to a serial schedule. A schedule is said to be view equivalent if all of the following conditions met by the schedule; Consider two transactions T1 and T2 and two schedules S1 and S2. S1 and S2 are said to be view equivalent if the following conditions satisfied; If T1 reads a data item Q initially in S1 then T1 must read Q initially in S2. In S1, if T1 reads a data item Q which is resulted in the write operation of T2, then in S2 also T1 must read Q which is written by T2. If T1 writes the final value of a data item Q in S1, then T1 must write the final value of Q in S2 also.

 

Is the schedule S a conflict serializable schedule?

NO. The precedence graph of S consists of a cycle.         

The cycle is formed due to the conflicting instructions {W₁(B), R₂(B)} (resulted in an edge T1 to T2), and {W₂(A), W₁(A)} (resulted in an edge T2 to T1).

 

Is the schedule S a view serializable schedule?

YES.

For S, the possible serial schedules are <T1, T2> or <T2, T1>.

The conditions for view serializability are satisfied in the case of S for an equivalent schedule <T1, T2>. Let us consider the equivalent schedule as S’.

Schedule S		Schedule S’
T1	T2	T1	T2
R(C) W(B)     W(A)	R(B) W(A)   W(A)	R(C) W(B) W(A)	R(B) W(A) W(A)

 

Satisfaction of following conditions can be observed from the above table (refer the color code);

• In schedule S and S’, T1 reads C first.

• In schedule S and S’, T2 consumes B which is written by T1.

• In schedule S and S’, T2 writes final A.

The schedule S is view equivalent to serial schedule S1. Hence, the schedule S is view serializable.

Note 1: A conflict serializable schedule is always a view serializable schedule. But a view serializable schedule need not be conflict serializable. Note 2: The write operations in S (W(A), W(B) in T1 and T2) are called as Blind Writes because they have not read the values of data items A or B prior.

 

*****************************

 

Go to Define Serializability page

Go to Conflict Serializability explained with example page

Go to Solved exercises in DBMS page

Conflict serializability solved exercise

view serializability solved exercise

conflict and view serializable schedule example

how to find whether a schedule is view serializable or not?

how to check a schedule is serializable or not?

use precedence graph for checking serializability

serializability solved exercise

concurrency and serializability