Question: -8(a) Differentiate between conflict and view serializability. How both of those are tested for a schedule?

Answer: – Serializibility is the process which prevent inconsistency ans aim to find a non serial schedule that allows transaction to execute concurrently without being interfered with one another and produce a database state that could be produced by a serial schedule.

THE DIFFERENCE BETWEEN CONFLICT AND VIEW SERIALIZABILITY

1.) Conflict serializability is easy to achieve but view serializability is hard to achieve

2.) Every conflict serializable is view serializable but the reverse is not true.

3.) It is much easy to test conflict serializability but expensive to test view serializability.

4.) Most of the concurrency control schemes used in practice are based on conflict serializability.

• Schedule− A chronological execution sequence of a transaction is called a schedule. A schedule can have many transactions in it, each comprising of a number of instructions/tasks.

Testing for conflict serializability

Method:-

• First write the given schedule in a linear way.
• Find the conflict pairs (RW, WR, WW) on same variable by different transactions.
• Whenever conflict pairs are find, write the dependency relation like Ti → Tj, if conflict pair is from Ti to Tj. For example, (W1(A), R2(A)) ⇒ T1 → T2
• Check to see if there is a cycle formed,
  • If yes= not conflict serializable
  • No= we get a sequence and hence are conflict serializable.

---

---

---

Questions: – 8(b) What is cursor? Discuss its different types. Name the commands used to control the cursor with their syntax.

Answer: – A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.
There are two types of cursors in PL/SQL  :

1. Implicit cursors.
2. Explicit cursors.

Implicit Cursors

These are the cursors which are automatically created and used by the DBMS. These cursors are created when we use DML statements like INSERT, DELETE or UPDATE or when we use SELECT query. Even for single row SELECT statement DBMS creates implicit cursors. It allocates some space in the memory to hold the data. Even when we fire DML statements, the implicit cursor is fired to select the matching records and the space is reserved for those records in the memory.

Even though these implicit cursors are internal and users/developers cannot control their execution, developers can view the details of these cursors using its attributes. We can access this implicit cursor using ‘SQL’ which refers to the cursor SQL. Its attributes are described below:

• %ISOPEN– It returns TRUE or FALSE depending on the cursor is still open for execution or not. It returns always FALSE for implicit cursor because DBMS would have executed and closed the cursor automatically.
• %FOUND– It returns TRUE if the DML statement affects any of the rows or the SELECT statement retrieves one or more row. Otherwise it returns FALSE. If the statements are not executed then it returns NULL.
• %NOTFOUND– It is opposite of %FOUND. It returns TRUE if the DML statement does not affect any of the rows or the SELECT statement does not retrieve any row.  Otherwise it returns FALSE.
• %ROWCOUNT– It returns the number of rows affected by the DML statements or the number of rows retrieved by the SELECT statement.

These implicit cursor attributes are called as below:

SQL%ISOPEN

SQL%ROWCOUNT

SQL%FOUND

SQL%NOTFOUND

One of the examples of implicit cursor

DECLARE

n_count NUMBER;

BEGIN

UPDATE EMPLOYEE

SET SALARY = SALARY + (SALARY*0.1) – 10% increment in salary for DEPT_ID = 10 employees

WHERE DEPT_ID = 10;

IF SQL%FOUND THEN – checks if any row is updated

n_count: = SQL%ROWCOUNT; — Number of rows updated are counted

DBMS_OUTPUT.PUT_LINE (‘Total Number of employees who got increment: ‘|| n_count);

ELSE

DBMS_OUTPUT.PUT_LINE (‘None of the employees got increment’);

END IF;

END;

Explicit Cursors

These cursors are created by the developer in his program. In this type of cursors, developer will have the control on it. Explicit cursors have to be declared in the DECLARE section and can be used as any other variable, but it will hold one or more rows of data.

Any explicit cursor created in the PL/SQL block will have four basic steps:

• DECLARE the Cursor– This step declares the cursor with its name and defines the SELECT statement for the cursor. The general syntax for declaring explicit cursor is as below:

CURSOR cursor_name IS

SELECT STATEMENT;

E.g.:

CURSOR cursor_Dept10Emp IS

SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE DEPT_ID = 10;

• OPEN the Cursor– Cursor is opened for reading the records from it. When we open the cursor it gets executed and memory space is allocated for the records.

OPEN cursor_name

 

OPEN cursor_Dept10Emp;

• FETCH the records– This step will start traversing the records in the cursor. Rows will be fetched one at a time from this cursor. We can perform any calculation or manipulation on these fetched records.

FETCH cursor_Dept10Emp INTO n_empID, v_empName;

• CLOSE the Cursor– Once the cursor is used and no more required in the program, close the cursor. This will release the allocated memory for this cursor.

CLOSE cursor_Dept10Emp;

Simple program with above 4 steps would be like below

DECLARE

CURSOR cursor_Dept10Emp IS   — Declare the cursor

SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE DEPT_ID = 10;

 

n_empID EMPLOYEE.EMP_ID%TYPE;

v_empName EMPLOYEE.EMP_NAME%TYPE;

BEGIN

OPEN cursor_Dept10Emp; — Open the cursor

 

LOOP

FETCH cursor_Dept10Emp INTO n_empID, v_empName; — Fetch cursor records

 

EXIT WHEN cursor_Dept10Em%NOTFOUND;

DBMS_OUTPUT.PUT_LINE (‘Employee ID: ‘|| n_empID|| ‘Employee Name:’|| v_empName);

END LOOP;

 

CLOSE cursor_Dept10Emp; — Close the Cursor

END;

Cursor For Loop

In the above four steps, FETCH step will retrieve only one record at a time and it will assign individual column values into respective variables. Also a separate loop has to be created to iterate through all the records. If we use For loop for cursors, then we can iterate and traverse the whole records (all the columns) of cursor query using the for loop variable. This is useful when all the records of a table need to be used in the program. The cursor will automatically be opened when it is called in the FOR loop and closed when for loop ends. No need to explicitly OPEN or CLOSE the cursor. Below example shows how cursor for loop is different from above four steps.

DECLARE

CURSOR cursor_Dept10Emp IS   — Declare the cursor

SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE DEPT_ID = 10;

 

rec_emp EMPLOYEE%ROWTYPE

BEGIN

FOR rec_emp IN cursor_Dept10Emp — implicitly opens cursor and assigns cursor variable rec_emp

LOOP

DBMS_OUTPUT.PUT_LINE (‘Employee ID: ‘||rec_emp.EMP_ID|| ‘Employee Name:’|| rec_emp.EMP_NAME);

END LOOP;

END;

Cursor Parameter

We can even pass parameters to the cursors like we do in procedures and functions. These parameters are used in the cursor query to retrieve the data. The scope of the cursor parameter is localized to cursor alone.

DECLARE

CURSOR cursor_Dept10Emp (deptID EMPLOYEE.DEPT_ID%TYPE) IS

SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE DEPT_ID = deptID; — deptID is the cursor parameter

rec_emp EMPLOYEE%ROWTYPE

 

BEGIN

FOR rec_emp IN cursor_Dept10Emp (10) – Pass the cursor parameter as 10

LOOP

DBMS_OUTPUT.PUT_LINE (‘Employee ID: ‘||rec_emp.EMP_ID|| ‘Employee Name:’|| rec_emp.EMP_NAME);

END LOOP;

END;

 

Ref Cursors

These are the cursor variables used to reference and access the static cursor. We can pass this variable to procedures / functions and get the values from function as a refcursor. In short it acts a variable, but reference to the query defined at the runtime.

We can see the difference between cursor and refcursor in below program.

DECLARE

TYPE rc_cursor is ref cursor;

CURSOR c_course IS

SELECT * FROM COURSE;

l_cursor rc_cursor;

n_ID NUMBER;

BEGIN

IF n_ID = 10 THEN

— Dynamically opens the cursor for student ids less than 10

Open l_cursor FOR ‘SELECT * FROM STUDENT WHERE STD_ID<= 10’;

ELSE

— Dynamically opens the cursor for student ids greater than 10

OPEN l_cursor FOR ‘SELECT * FROM STUDENT WHERE STD_ID > 10’;

END IF;

— Opens static cursor c_course

OPEN c_course;

END;

---

---

---

Question: -9(a) Discuss the optimistic concurrency control technique. Name its phases. How is minimum overhead reached?

Answer: – Concurrency control is a database management systems (DBMS) concept that is used to address conflicts with the simultaneous accessing or altering of data that can occur with a multi-user system. concurrency control, when applied to a DBMS, is meant to coordinate simultaneous transactions while preserving data integrity. [1] The Concurrency is about to control the multi-user access of Database

Locking Methods of Concurrency Control :

“A lock is a variable, associated with the data item, which controls the access of that data item.”
Locking is the most widely used form of  the concurrency control. Locks are further divided into three fields:

1. Lock Granularity
2. Lock Types
3. Deadlocks

 Lock Granularity :

A database is basically represented as a collection of named data items.  The size of the data item chosen as the unit of protection by a concurrency control program is

called GRANULARITY.  Locking can take place at the following level :

• Database level.
• Table level.
• Page level.
• Row (Tuple) level.
• Attributes (fields) level.

Lock Types :

The DBMS mailnly uses following types of locking techniques.

1. Binary Locking
2. Shared / Exclusive Locking
3. Two – Phase Locking (2PL)

Deadlocks :

A deadlock is a condition in which two (or more) transactions in a set are waiting  simultaneously for locks held by some other transaction in the set.

Neither transaction can continue because each transaction in the set is on a waiting queue,  waiting for one of the other transactions in the set to release the lock on an item.  Thus, a deadlock is an impasse that may result when two or more transactions are each  waiting for locks to be released that are held by the other.  Transactions whose lock requests have been refused are queued until the lock can be  granted.
A deadlock is also called a circular waiting condition where two transactions are waiting  (directly or indirectly) for each other.  Thus in a deadlock, two transactions are mutually excluded from accessing the next record  required to complete their transactions, also called a deadly embrace.

Time-Stamp Methods for Concurrency control : 

Timestamp is a unique identifier created by the DBMS to identify the relative starting time of a transaction.
Typically, timestamp values are assigned in the order in which the transactions are submitted to the system. So, a timestamp can be thought of as the transaction start time.  Therefore, time stamping is a method of concurrency control in which each transaction is  assigned a transaction timestamp.  Timestamps must have two properties namely

1. Uniqueness :  The uniqueness property assures that no equal timestamp values can exist.
2. monotonicity  :  monotonicity assures that timestamp values always increase.

Timestamp are divided into further fields :

1. Granule Timestamps
2.  Timestamp Ordering
3. Conflict Resolution in Timestamps

Optimistic Methods of Concurrency Control :

The optimistic method of concurrency control is based on the assumption that conflicts of database operations are rare and that it is better to let transactions run to completion and only check for conflicts before they commit.
An optimistic concurrency control method is also known as validation or certification methods. No checking is done while the transaction is executing.  The optimistic method does not require locking or timestamping techniques. Instead, a transaction is executed without restrictions until it is committed. In optimistic methods, each transaction moves through the following phases:

1. Read phase.
2. Validation or certification phase.
3. Write phase.

---

---

---

Question: – 9(b) Where to stored procedure and function reside? Also discuss the steps to execute a procedure or function.

Answer: – stored procedure

A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in a relational database management system as a group, so it can be reused and shared by multiple programs. Stored procedures can access or modify data in a database, but it is not tied to a specific database or object, which offers a number of advantages.

Benefits of using stored procedures

A stored procedure provides an important layer of security between the user interface and the database. It supports security through data access controls because end users may enter or change data, but do not write procedures. A stored procedure preserves data integrity because information is entered in a consistent manner. It improves productivity because statements in a stored procedure only must be written once.

Stored procedures offer advantages over embedding queries in a graphical user interface (GUI). Since stored procedures are modular, it is easier to troubleshoot when a problem arises in an application. Stored procedures are also tunable, which eliminates the need to modify the GUI source code to improve its performance. It’s easier to code stored procedures than to build a query through a GUI.

Use of stored procedures can reduce network traffic between clients and servers, because the commands are executed as a single batch of code. This means only the call to execute the procedure is sent over a network, instead of every single line of code being sent individually.

	Stored Procedure	Functions
Compilation	Stored in database in compiled format. Note: Compiled indicates, Execution plan will be made by sql at the time it created and stored in DB.	Will compiled at run time
Return type	It can directly return only integers   Return type is not must	It can return any scalar or table   Return type is must
Multiple return values	It can also return more than one values (of any data type) indirectly with the help of out parameters	It won’t support out parameters
DML Statements	Can have DML statements.	Cannot have DML statements. Note: In case of multi-table valued functions it can contain DML statements affecting Table Variables.
Execution	Stored procedure can execute function.   Cannot be the part of Select query as a column.   Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT	Function cannot execute stored procedure.   Can be the part of select query as a column.     Functions be used in the SQL statements anywhere in the WHERE/HAVING/SELECT
Exception handling	Can have Try….Catch	Cannot have Try….Catch

 

Function Reside:-

 

1. A FUNCTION always returns a value using the return statement. – Practical scenarios, when expecting a value to be returned which in turn helps for computation in rest of code

PROCEDURE may return one or more values through parameters or may not return any at all.

IN,OUT,INOUT parameters are different types. IN will be the input to the procedure. OUT will be the output from the procedure and this helps to get the output from the procedure. INOUT usually a same parameter behaves as input as well as output.

2. Functions are normally used for computations where as procedures are normally used for executing business logic.
3. A Function returns 1 value only. Procedure can return multiple values (max 1024).
4. Stored procedure always returns an integer value of zero by default. Whereas function return types could be scalar or table or table values.- This is because Functions mainly meant for computation
5. Stored procedures have a precompiled execution plan, where as functions are not. – Because of precompiled plan, for routines, stored procedure is preferred a lot.
6. A function can be called directly by SQL statement like select func_name from dual while procedures cannot.
7. Stored procedure has the security and reduces the network traffic and also we can call stored procedure in any no. of applications at a time.
8. A Function can be used in the SQL Queries while a procedure cannot be used in SQL queries .that cause a major difference b/w function and procedures.

---

---

---

This article is contributed by Tarun Jangra. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.