RDBMS-Part 5

 

Q5Q5) Write a short note on ANY TWO of the following (Out of THREE) : [2 × 3 = 6]

●       Characteristics of RDBMS.

Security

This is one of the most important functions of RDBMS. Security management sets rules that allow accessing the database. This function also sets restraints on what specific data any user can see or write.

Accuracy

In relational database management systems multiple tables are related to one another with the use of primary key and foreign key concepts. This makes data non repetitive so there is no chance for duplication of data. Hence, accuracy of RDBMS is good.

Integrity

Data integrity enforces the three constraints. Entity integrity means a table should have the primary key.

Consistency

The data consistency in relational model is best in RDBMS for maintaining data across application and database copies.

 

  2.Control Statements in PLSQL.

PL/SQL Loop

● Loops are iterative control statements.

● They are used to repeat execution of one or more statements for defined number of times.

Syntax

LOOP
Sequence of statements;
END LOOP;

 

The four types of loops are:

 

1. PL/SQL Exit Loop

● It is used a set of statements is executed at least once before termination of loop.

● There should be an EXIT condition in the loop, otherwise the loop will get into an infinite number of iterations.

Syntax

LOOP  
Statements;  
EXIT;  
[or EXIT WHEN condition;]
END LOOP;

Example

DECLARE 
i NUMBER := 1; 
BEGIN 
LOOP 
EXIT WHEN i>5; 
dbms_output.put_line(i); 
i := i+1; 
END LOOP; 
END;

 

Output:

1

2

3

4

5

 

2. PL/SQL WHILE Loop

● It is used when a set of statements should be executed as long as condition is true.

● The condition is decided at the beginning of each iteration and continues until condition becomes false.

WHILE [condition]
LOOP Statements;
END LOOP;

Example

DECLARE 
i INTEGER := 1; 
BEGIN 
WHILE i <= 5 LOOP 
dbms_output.put_line(i); 
i := i+1; 
END LOOP; 
END;

 

Output:

1

2

3

4

5

 

3. PL/SQL FOR Loop

● It is used to execute a set of statements for a fixed number of times.

● It is iterated between the start and end integer values.

Example

FOR counter IN initial_value .. final_value LOOP 
LOOP statements;  
END LOOP;

Example

BEGIN 
FOR k IN 1..5 LOOP 
dbms_output.put_line(k)  
END LOOP; 
END;

 

Output:

1

2

3

4

5

 

IF-ELSE

   3.Transaction States

●       Active − Transaction is executing.

●       Failed − Transaction fails to complete successfully.

●       Abort − changes made by transaction are cancelled (roll back).

●       Partially commit − Final statement of transaction is executed.

●       Commit − Transaction completes its execution successfully.

●       Terminated − Transaction is finished.

4  Lock Based Protocol 

In this type of protocol, any transaction cannot read or write data until it acquires an appropriate lock on it. There are two types of lock:

1. Shared lock:

• It is also known as a Read-only lock. In a shared lock, the data item can only read by the transaction.
• It can be shared between the transactions because when the transaction holds a lock, then it can't update the data on the data item.

2. Exclusive lock:

• In the exclusive lock, the data item can be both reads as well as written by the transaction.
• This lock is exclusive, and in this lock, multiple transactions do not modify the same data simultaneously.

5. Concurrent Execution of Transaction

In the transaction process, a system usually allows executing more than one transaction simultaneously. This process is called a concurrent execution.

Advantages of concurrent execution of a transaction

1.      Decrease waiting time or turnaround time.

2.      Improve response time

3.      Increased throughput or resource utilization.

Dirty read problem (W-R conflict)

This type of problem occurs when one transaction T1 updates a data item of the database, and then that transaction fails due to some reason, but its updates are accessed by some other transaction.

Example: Let's take the value of A is 100

 

Time	Transaction T1	Transaction T2
t1	Read(A)
t2	A=A+20
t3	Write(A)
t4		Read(A)
t5		A=A+30
t6		Write(A)
t7	Write(B)

Here,

• At t1 time, T1 transaction reads the value of A i.e., 100.
• At t2 time, T1 transaction adds the value of A by 20.
• At t3 time, T1transaction writes the value of A (120) in the database.
• At t4 time, T2 transactions read the value of A data item i.e., 120.
• At t5 time, T2 transaction adds the value of A data item by 30.
• At t6 time, T2transaction writes the value of A (150) in the database.
• At t7 time, a T1 transaction fails due to power failure then it is rollback according to atomicity property of transaction (either all or none).