RDBMS



Unit 1:-Introduction TO Rdbms
1)Give any two differences between DBMS and RDBMS.
2)Explain any four objects of oracle.
3)Write any 2 distinguishing characteristics of RDBMS
4)Explain any four features of oracle
5)Explain relation between application program and RDBMS
6)What is RDBMS? Explain any two features of RDBMS
7)Any 2 popular products of RDBMS
8)What is RDBMS ? List any four characteristics of RDBMS.
9)Explain advantages and disadvantages of RDBMS.


Unit 2:- PLSQL
1)What is trigger ? List types of trigger.
2)Write syntax of nested if statement in PL/SQL with example.
3)Explain the following predefined exception :no-data-found, zero-divide, two-many-rows, duplicate-val-on-index.
4)What is PL/SQL ? Explain different data types in PL/SQL
5)What is cursor ? List attributes of cursor with an example.
6)Explain any four features of PL/SQL
7)List the iterative statements in PL/SQL
8)Explain procedures syntax and example
9)What is difference between %type and % rowtype
10)Explain for loop statements in PL/SQL
11)What is PL/SQL ?Explain block of PL/SQL
12)What is trigger ?Explain syntax with example
13)What is PL/SQL ? List the sections of a PL/SQL block.
14)Write a note on package in PL/SQL.
15)What is exception handling ? explain user defined exception
16)Explain for while statements in PL/SQL
17)Difference between procedure and function with example
18) What is procedure in PL/SQL ?Give syntax of procedure.
19)What is exception handling ? explain system defined exception

Unit 3:-Transaction Management
1)What is transaction? List properties of transaction.
2)Define cascade less schedule.
3)Explain states of transaction with the help of suitable diagram.
4)What is serializability? Explain view serializability with example.
5)Which are the conflict operations of transaction?
6)What is conflict serilizability
7)Explain the different types of schedule related to recovery with example.
8)What is serializability?List the type of serializability?
9)Define Recoverable Schedule
10)What is transaction? Explain ACID Properties of transaction
11)What is Schedule ? List the types of Schedule
12)Explain recoverable schedule and cascadeless schedule with example.

Unit 4:-Concurrency Control
1)Define :(i)    growing phase (ii) shrinking phase.
2)Give Lock-compatibility matrix.
3)What is deadlock ? Explain deadlock prevention methods.
4)Explain Thomas write rule.
5)Define lock ?Explain types of Lock?
6)Define physical block ,Buffer block
7)What is dirty read problem?
8)Write a note on Deadlock
9)Write a note on Granting of locks
10)Define upgrading and downgrading
11)What is deadlock ? Explain how deadlock is recovered
12)Explain Timestamp ordering Protocol
13)What is Deadlock ? Explain how deadlock is hundled.
14)Explain two-phase locking protocol with example.
15)What is block? Explain their types
16)Explian validation based protocol
17)What is precedence graph
18)Define W-Timestamp ,R-Timestamp

Unit 5:-Recovery System
1)List different types of failures.
2)Write a short note on storage type.
3)Explain remote backup system with the help of a diagram
4)Explain designing issues of remote backup system
5)Define commit and rollback
6)What is checkpoint
7)Write a note on recovery from Concurrent transaction
8)What are the various problems that occur in Concurrent transaction
9)Explain Deferred Database modification with example.
10)Define :(i ) Redo (ii ) Undo
11)What are the different types of storage ? Give example
12)Define Logical error  ,Syntax error
13)Explain immediate database modification technique in detail with example.
14)What is Log ? Explain log-based recovery.
15)Explain advantages of concurrent execution

Case study
1)Consider the following transactions :

T1        T2
Read(z)           Read(x)
z = z + 100      Read(y)
Write(z)           y = y – x
Read(y)           Write(y)
y = y – 100     
Write(y)          
Give two non-serial schedules that are serializable.

2) Consider the following transactions :
T1                    T2
Read(A)           Read(A)
A = A + 1000  A = A – 1000
Write(A)           Write(A)
                         Read(B)
                         B = B + 1000
                        Write(B)
Give two non-serial schedules that are serializable.

 3)The following is the list of events in an interleaved execution of set T1, T2, T3 and T4 assuming 2PL protocol. Is there a deadlock ? If yes, which transactions are involved in deadlock ?

Time    Transaction      Code

t1         T1        Lock(A, X)
t2         T2        Lock(B, X)
t3         T3        Lock(C, X)
t4         T4        Lock(A, S)
t5         T1        Lock(C, S)
t6         T2        Lock(D, S)
t7         T3        Lock(D, X)
t8         T4        Lock(B, X)

 4) The following is the list representing the sequence of events in an interleaved execution of set transactions T1, T2, T3 and T4 assuming 2PL protocol. Construct a wait for graph according
to request. Is there deadlock at any instance. Justify.


Time    Transaction      Code

t1         T1        Lock(A, X)
t2         T2        Lock(C,S)
t3         T3        Lock(A,S)
t4         T4        Lock(C, S)
t5         T1        Lock(B,X)
t6         T2        Lock(B, S)
t7         T3        Lock(D, S)
t8         T4        Lock(D, X)


5)Consider the following transactions :

T1                     T2
Read(p)           Read(p)
p= p+ 100        P=p-100
Write(p)           Write(p)
Read(Q)          Read(Q)
Read(R)          Q=Q-200
Q=Q+200        Write(Q)
Write(Q)         
R=R+300       
Write(R)         
Give two non-serial schedules that are serializable.

6) Consider the following transactions :
T1                  T2
Read(X)
Read(M)       
X=X+M         
                    Read(N)
                    Read(X)
                    X=X+N
Write(x)          
                      Write(x)
Read(y)          
Y=y+m           
Write(y)          
Give two non-serial schedules that are serializable.


7)The following is the list of events in an interleaved execution of set T1, T2, T3 and T4 assuming 2PL protocol. Is there a deadlock ? If yes, which transactions are involved in
deadlock ?

Time    Transaction      Code

t1         T1        Lock(A, X)
t2         T2        Lock(B, S)
t3         T3        Lock(A, S)
t4         T4        Lock(C, S)
t5         T1        Lock(B, X)
t6         T2        Lock(C, X)
t7         T3        Lock(D, S)
t8         T4        Lock(D, X)

 8) The following is the list representing the sequence of events in an interleaved execution of set transactions T1, T2, T3 and T4 assuming 2PL protocol. Construct a wait for graph according to request. Is there deadlock at any instance. Justify.
 
Time    Transaction      Code

t1         T1        Lock(A, X)
t2         T2        Lock(B,X)
t3         T3        Lock(C,X)
t4         T4        Lock(A, S)
t5         T1        Lock(C,S)
t6         T2        Lock(D, S)
t7         T3        Lock(D, S)
t8         T4        Lock(B,S)


9) The following are the log entries at the time of system crash :
[start-transaction, T2]
[write-item, T1, B, 200]
[commits, T1]
[checkpoint]
[start-transaction, T2]
[write-item, T2, C, 250]
[commit, T2]
[start-transaction, T3]
[write-item, T3, C, 300]
[start-transaction, T4]
[write-item, T4, A, 400]
[write-item, T3, D, 250] _ system crash
If deferred update technique with checkpoint is used, what will be recovery procedure ?

10) The following are the log entries at the time of system crash :
[start-transaction, T1]
[write-item, T1, P,10,20]
[commits, T1]
[start-transaction, T2]
[write-item, T2, Q,30,20]
[write-item, T2, R,20,30]
[commit, T2]
[checkpoint]
 [start-transaction, T4]
[write-item, T4, S, 20,10]
 [start-transaction, T3]
[write-item, T3, T1, 10,30]_ system crash
If Immediate update technique with checkpoint is used, what will be recovery procedure ?

11) The following are the log entries at the time of system crash :
[start-transaction, T1]
[write-item, T1, A,100]
[commits, T1]
[start-transaction, T3]
[write-item, T3,B,200]
[checkpoint]
[commit, T3]
[start-transaction, T2]
[write-item, T2, B,300]
[start-transaction, T4]
 [write-item, T4, D, 200]
[write-item, T2, C, 300] _ system crash
If deferred update technique with checkpoint is used, what will be recovery procedure ?

12)The following is the list of events in an interleaved execution of set T1, T2, T3 and T4 assuming 2PL protocol. Is there a deadlock ? If yes, which transactions are involved in
deadlock ?

Time    Transaction      Code

t1         T1        Lock(B, S)
t2         T2        Lock(A,X)
t3         T3        Lock(C, S)
t4         T4        Lock(B, S)
t5         T1        Lock(A,S)
t6         T2        Lock(C, X)
t7         T3        Lock(A,X)
t8         T4        Lock(C,S)

 13) The following is the list representing the sequence of events in an interleaved execution of set transactions T1, T2, T3 and T4 assuming 2PL protocol. Construct a wait for graph according
to request. Is there deadlock at any instance. Justify.


Time    Transaction      Code

t1         T1        Lock(A, X)
t2         T2        Lock(B,S)
t3         T3        Lock(A,S)
t4         T4        Lock(C, S)
t5         T1        Lock(B,X)
t6         T2        Lock(C,X)
t7         T3        Lock(D, S)
t8         T4        Lock(D,X)


14)Consider the following transactions. Find out two non-serial schedules that are  serializable :
T1                    T2
Read (P)          Read (Q)
P = P * 10       Q = Q + 10
Write (P)         Write (Q)
Read (Q)         Read (R)
Q = Q/10         R = R * 10
Write (Q          Write (Q

15)Consider the following transactions. Find out two non-serial schedules that are serializable :

T1                    T2
Read (x)          Read (x)
x = x + 1000    x = x – 1000
Write (x)          Write (x)
Read (y)          Read (y)
Read (z)          y = y – 2000
y = y + 2000    Write (y)
Write (y)         
z = z + 3000   
Write (z)         


 16) The following is the list representing the sequence of events in an interleaved execution of set T1, T2, T3 and T4 assuming two-phase locking protocol.Is there a deadlock ? If yes, which transactions are involved in deadlock ?


Time    Transaction      Code

t1         T1        Lock(B, S)
t2         T2        Lock(A,X)
t3         T3        Lock(C, S)
t4         T4        Lock(B, X)
t5         T1        Lock(D,S)
t6         T2        Lock(C, S)
t7         T3        Lock(A,S)
t8         T4        Lock(D,S)

 17) The following is the list representing the sequence of events in an interleaved execution of set T1, T2, T3 and T4 assuming two-phase locking protocol. Is there a deadlock ? If yes, which transactions are involved in deadlock ?

Time    Transaction      Code

t1         T1        Lock(A,X)
t2         T2        Lock (B,S)
t3         T3        Lock(A, S)
t4         T4        Lock(C, S)
t5         T1        Lock (C,X)
t6         T2        Lock (B,X)
t7         T3        Lock (D,X)
t8         T4        Lock(D,S)

18) The following are the log entries at the time of system crash :
[Start-Transaction, T1]
[Write-item, T1, X, 2000]
[Commits, T1]
[Check point]
[Start-Transaction, T4]
[Write-item, T4, X, 3000]
[Write-item, T4, Y, 2000]
[Commit, T4]
[Start-transaction, T2]
[Write-item, T2, Z, 2000]
[Start-transaction, T3]
[Write-item, T3, X, 3000] _ System crash
If deferred update technique with check point is used, what will be the recovery procedure ?

19)Consider the following transactions :

T1                    T2
Read(z)           Read(x)
z = z * 10        Read(z)
Write(z)           X=x+z
Read(y)           Write(x)
Read(z)          
Y=y+z
Write(y)          
Give two non-serial schedules that are serializable.

20) Consider the following transactions :
T1        T2
Read(A)          Read(B)
A = A *5         B = B*5
Write(A)          Write(B)
Read(B)          Read(A)
Read(C)          A=A*10
B=B*10          Write(A)
Write(B)         
C=C*5           
Write(C)         
Give two non-serial schedules that are serializable.


21) The following is the list representing the sequence of events in an interleaved execution of set T1, T2, T3 and T4 assuming two-phase locking protocol.Is there a deadlock ? If yes, which transactions are involved in deadlock ?


Time    Transaction      Code

t1         T1        Lock(A, X)
t2         T2        Lock(B,S)
t3         T3        Lock(A, S)
t4         T4        Lock(C,S)
t5         T1        Lock(B,S)
t6         T2        Lock(C, X)
t7         T3        Lock(D,X)
t8         T4        Lock(D,S)

 22) The following is the list representing the sequence of events in an interleaved execution of set T1, T2, T3 and T4 assuming two-phase locking protocol. Is there a deadlock ? If yes, which
transactions are involved in deadlock ?

Time    Transaction      Code

t1         T1        Lock(L,X)
t2         T2        Lock (M,X)
t3         T3        Lock(N, S)
t4         T4        Lock(L, S)
t5         T1        Lock (N,X)
t6         T2        Lock (L,S)
t7         T3        Lock (O,X)
t8         T4        Lock(M,S)

23) The following are the log entries at the time of system crash :
[Start-Transaction, T1]
[Write-item, T1, C, 200]
[Commits, T1]
[Check point]
[Start-Transaction, T2]
[Write-item, T2, D, 100]
[Commits, T2]
[Start-transaction, T3]
[Write-item, T3, D, 150]
[Write-item, T3, C, 250]_ System crash
If deferred update technique with check point is used, what will be the recovery procedure ?


24)Consider the following transactions :

T1                     T2
Read(x)           Read(y)
x = x + 1000    Y=y-500
Write(x)           Write(y)
Read(y)           Read(z)
y = y +1000     Z=z-500
Write(y)           Write(z)
Give two non-serial schedules that are serializable.

25) Consider the following transactions :
T1                   T2
Read(x)           Read(z)
Read(z)           Z=z+100
X=x+z            Write(z)
Write(x)           Read(y)
                        Y=y+200
                      Write(y)
Give two non-serial schedules that are serializable.

26) The following is the list representing the sequence of events in an interleaved execution of set T1, T2, T3 and T4 assuming two-phase locking protocol.Is there a deadlock ? If yes, which transactions are involved in deadlock ?


Time    Transaction      Code

t1         T1        Lock(A, X)
t2         T2        Lock(B,S)
t3         T3        Lock(A, S)
t4         T4        Lock(D,S)
t5         T1        Lock(B,X)
t6         T2        Lock(C, X)
t7         T3        Lock(D,S)
t8         T4        Lock(C,X)

 27) The following is the list representing the sequence of events in an interleaved execution of set T1, T2, T3 and T4 assuming two-phase locking protocol. Is there a deadlock ? If yes, which
transactions are involved in deadlock ?

Time    Transaction      Code

t1         T1        Lock(A,X)
t2         T2        Lock (A,S)
t3         T3        Lock(A, S)
t4         T4        Lock(B, S)
t5         T1        Lock (B,X)
t6         T2        Lock (C,X)
t7         T3        Lock (D,S)
t8         T4        Lock(D,X)

28) The following are the log entries at the time of system crash :
[Start-Transaction, T1]
[Write-item, T1, A,10, 20]
[Commits, T1]
[Check point]
[Start-Transaction, T2]
[Start-Transaction, T2,B,10,15]
[Start-transaction, T3]
[Start-Transaction, T3,C,10,25]
[Commits, T2]
[Write-item, T3, D, 10,30]-System crash
If immediate  update technique with check point is used, what will be the recovery procedure ?
 
29) Consider the following transactions. Give two non-serial schedules that are serializable :
 T1                     T2
Read (X)         Read (Z)
X = x + 100     Read (X)
Write (X          X = x – z
Read (Y)         Write (X)
Read (Z)          Read (Y)
Y = y + z         Y = y – 100
Write (Y)         Write (Y)


 30) Consider the following transaction. Give two non-serial schedules that are serializable :
T1                    T2
 Read (Y)         Read (X)
Read (a)          Read (a)
Y = y + a         X = x + a
Write (Y)         Write (X)
                        Read (Y)
                       Y = y + a
                      Write (Y)
 
 31) Following is the list of events in an interleaved execution of set T1, T2, T3 and T4 assuming 2PL (Two Phase Lock). Is there a Deadlock ? If yes, which transactions are involved in Deadlock ?
Time    Transaction      Code

t1         T1        Lock(A,X)
t2         T2        Lock (B,X)
t3         T3        Lock(A, S)
t4         T4        Lock(B, S)
t5         T1        Lock (B,S)
t6         T2        Lock (D,X)
t7         T3        Lock (D,S)
t8         T4        Lock(C,X)


 32) Following is the list of events in an interleaved execution of set T1 , T2 , T3 and T4, assuming 2PL. Is there a Deadlock ? If yes, which transactions are involved in Deadlock ?

Time    Transaction      Code

t1         T1        Lock(A,X)
t2         T2        Lock (B,S)
t3         T3        Lock(A, S)
t4         T4        Lock(B, S)
t5         T1        Lock (B,X)
t6         T2        Lock (C,S)
t7         T3        Lock (D,S)
t8         T4        Lock(D,X)
 
 33) Following are the log entries at the time of system crash :
[start_transaction, T1]
[write_item, T1, A, 10]
[commit T1]
[start_transaction, T3]
[write_item T3, B, 15]
[checkpoint]
[commit T3]
[start_transaction, T2]
[write_item T2, B, 20]
[start_transaction, T4]
[write_item, T4, D, 25]
[write_item T2, C, 30] ←System Crash
If deferred update technique with checkpoint is used, what will be the recovery procedure ?

34)Consider the following transactions :

T1                     T2
Read(x)           Read(x)
x = x + 1000    Y=y-1000
Write(x)           Write(x)
Read(y)           Read(y)
y = y +2000     Z=z-2000
Write(y)          Write(y)
z=z+3000
write(z)
Give two non-serial schedules that are serializable.


35)Consider the following transactions :

T1                     T2
Read(P)           Read(Q)
P= P* 10         Q=Q+10
Write(P)           Write(Q)
Read(Q)           Read(R)
Q = Q /10          R=R*10
Write(Q)           Write(R)
Give two non-serial schedules that are serializable.
36)The following is the list representing the sequence of events in an interleaved execution of set T1, T2, T3 and T4 assuming two-phase locking protocol. Is there a deadlock ? If yes, which
transactions are involved in deadlock ?

Time    Transaction      Code

t1         T1        Lock(B,S)
t2         T2        Lock (A,X)
t3         T3        Lock(C, S)
t4         T4        Lock(B, X)
t5         T1        Lock (D,S)
t6         T2        Lock (C,S)
t7         T3        Lock (A,S)
t8         T4        Lock(D,S)


37)The following is the list representing the sequence of events in an interleaved execution of set T1, T2, T3 and T4 assuming two-phase locking protocol. Is there a deadlock ? If yes, which

transactions are involved in deadlock ?



Time    Transaction      Code



t1         T1        Lock(A,X)

t2         T2        Lock (B,S)

t3         T3        Lock(A, S)
t4         T4        Lock(C,S)
t5         T1        Lock (C,X)
t6         T2        Lock (B,X)
t7         T3        Lock (D,X)
t8         T4        Lock(D,S)

38)Following are the log entries at the time of system crash :
[start_transaction, T1]
[write_item, T1, X, 2000]
[commit T1]
[checkpoint]
[start_transaction, T4]
[write_item T4, X, 3000]
[write_item T4, Y, 2000]
[commit, T4]
[start_transaction, T2]
[write_item T2, Z, 2000]
[start_transaction, T3]
 [write_item T3, X, 3000] ← System Crash
If deferred update technique with checkpoint is used, what will be the recovery procedure ?



39)The following is the list representing the sequence of events in an interleaved execution of set T1, T2, T3 and T4 assuming two-phase locking protocol. Is there a deadlock ? If yes, which
transactions are involved in deadlock ?

Time    Transaction      Code

t1         T1        Lock(A,X)
t2         T2        Lock (B,S)
t3         T3        Lock(C, S)
t4         T4        Lock(A,S)
t5         T1        Lock (C,S)
t6         T2        Lock (B,X)
t7         T3        Lock (D,X)
t8         T4        Lock(D,S)

40)Following are the log entries at the time of system crash :
[start_transaction, T1]
[write_item, T1, A]
[write_item, T1, X, 200]
[commit T1]
[start_transaction, T2]
[Read_item,T2,B]
[write_item T2, 400]
[checkpoint]
[start_transaction, T3]
[Read_item,T3,A]
[write_item T3, 600]
 [commit]
 [write_item T3, B, 600] ← System Crash
If immediate update technique with checkpoint is used, what will be the recovery procedure ?

41] Following are the log entries at the time of system crash :
[start_transaction, T1]
[write_item, T1, A,500]
 [commit T1]
[start_transaction, T2]
[write_item, T2, B,200]
[commit,T2]
[checkpoint]
[start_transaction, T3,C,100]
[write_item T3, C,200] ← System Crash
If deferred update technique with checkpoint is used, what will be the recovery procedure ?

42) Consider the following transactions :Give two non serial schedule that are serializable

T1                     T2
Read(Q)           Read(R)
Q= Q* 100        R=R+200
Write(Q)           Write(R)
Read(R)           Write(P)
R = R+250        
Write(R) 
         
43) The following is the list representing the sequence of events in an interleaved execution of set T1, T2, T3 and T4 assuming two-phase locking protocol. Is there a deadlock ? If yes, which
transactions are involved in deadlock ?

Time    Transaction      Code

t1         T1        Lock(B,X)
t2         T2        Lock (A,X)
t3         T3        Lock(C, S)
t4         T4        Lock(B,X)
t5         T1        Lock (A-C)
t6         T2        Lock (C,X)
t7         T3        Lock (A,X)
t8         T4        Lock(C,S)



44)Consider the following transactions :

T1                     T2
Read(X)           Read(X)
X=X+1000       X=X-1000
Write(X)          Write(X)
                          Read(Y)
Read(Y)            Y=Y-2000
Read(z)             Write(y)
Y=y+2000         
Write(Y)    
Z=Z+3000
Write(z)         
Give two non-serial schedules that are serializable.

45) The following is the list representing the sequence of events in an interleaved execution of set T1, T2, T3 and T4 assuming two-phase locking protocol. Is there a deadlock ? If yes, which
transactions are involved in deadlock ?

Time    Transaction      Code

t1         T1        Lock(P,X)
t2         T2        Lock (Q,S)
t3         T3        Lock(P, S)
t4         T4        Lock(Q,S)
t5         T1        Lock (Q,X)
t6         T2        Lock (R,X)
t7         T3        Lock (U,S)
t8         T4        Lock(D,X)

46) The following is the list representing the sequence of events in an interleaved execution of set T1, T2, T3 and T4 assuming two-phase locking protocol. Is there a deadlock ? If yes, which
transactions are involved in deadlock ?

Time    Transaction      Code

t1         T1        Lock(A,S)
t2         T2        Lock (B,X)
t3         T3        Lock(C,X)
t4         T4        Lock(A,S)
t5         T1        Lock (C,X)
t6         T2        Lock (A,S)
t7         T3        Lock (D,X)
t8         T4        Lock(B,S)


47] Following are the log entries at the time of system crash :
[start_transaction, T1]
[Read item,T1,U]
 [write_item, T1, U,40]
 [commit T1]
[checkpoint]
[start_transaction, T2]
[Read-item,T2,Q]
[write_item, T2, Q,32]
[start_transaction, T3]
[write_item, T3, P,40]
[Read-item,T2,U]
 [write_item T3, U,45] ← System Crash
If deferred update technique with checkpoint is used, what will be the recovery procedure
 

48) Consider the following transactions :Give two non serial schedule that are serializable
T1
T2
T3
Read(A)
A=A+500
Write(A)
Read(B)
B=B-500
Write(B)
Read(C)
Read(B)
B=B+C
Write(B)
Read(A)
A=N-C
Write(A)
Read(B)
Read(C)
B=B+450
Write(B)
C=C+B
Write(B)

Subscribe to: Posts (Atom)