Relational Query Languages| Basic sql query language

BASIC SQL QUERY LANGUAGE

INTRODUCTION-

• SQL (Structured Query Language) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS).
• SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standards (ISO) in 1987. 

WHAT CAN SQL DO?

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database

• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

SQL LANGUAGES

1. Data Definition Language (DDL)

2. Data Manipulation Language (DML)

3. Data Control Language (DCL)

1) DATA DEFINITION LANGUAGE- The Data Definition Language (DDL) manages table and index structure.

COMMAND USED- CREATE, ALTER, DROP,RENAME

CREATE- creates an object (a table) in the database.

ALTER modifies the structure of an existing object in various ways, for  example, adding a column to an existing table or a constraint.

DROP deletes an object in the database, usually irretrievably, i.e., it cannot be rolled back.

RENAME rename scheme object

2) DATA MANIPULATION LANGUAGE- The Data Manipulation Language (DML) is the subset of SQL used to add, update,select and delete data.

COMMAND USED-INSERT, UPDATE, DELETE,SELECT

INSERT adds rows (formally tuples) to an existing table.

UPDATE modifies a set of existing table rows.

DELETE removes existing rows from a table.

SELECT Retrieve data from one or more table

3) DATA CONTROL LANGUAGE-The Data Control Language (DCL) authorizes users to access and manipulate data.          

COMMAND USED- GRANT, REVOKE, COMMENT

GRANT authorizes one or more users to perform an operation or a set of operations on an object.

REVOKE eliminates a grant, which may be the default grant.

COMMENT Adding the element into data dictionary

Read More

Relational Query Languages | How to create table in sql

CREATING TABLES AND VIEWS

• CREATING TABLES

CREATE: This command is used when we want to create a new table.

syntax: create table <tname>  (col.1 data type (size) constraint <constraint name> not null , col.2 data type (size));

E.g.: create table mydoc1 (username varchar (20) constraint name_mn not null, password number(10));

• VIEWS

We can use views to display the particular values from  a table as separate sub table. for example a table emp details has 8 fields and we can build a view with 3 fields from the table.

SYNTAX- 

• For creating a view -create or replace view <view name> as  <basic query for retrieval> with read only;
• For dropping a view- Drop view view name;

Read More

Database management softeware ORACLE | download oracle latest version

Oracle Database (commonly referred to as Oracle RDBMS or simply as Oracle) is an object-relational database management system  produced and marketed by Oracle Corporation.

Larry Ellison and two friends and former co-workers, Bob Miner and Ed Oates, started a consultancy called Software Development Laboratories (SDL) in 1977. SDL developed the original version of the Oracle software. The name Oracle comes from the code-name of a CIA-funded project Ellison had worked on while previously employed by Ampex.

Download : click here

Read More

Two phase locking protocol in concurrency control | Transaction management

TWO PHASE LOCKING PROTOCOL(PESSIMISTIC APPROACH)

A transaction follows the two phase locking protocol, if all the locking operations precede the first unlock operation in the transaction. There are two phases in the Schedule. These are:

1.) GROWING PHASE- During which all the locks are requested.
2.) SHRINKING PHASE- During which all the locks are released.

Initially a transaction is in the growing phase. The transaction acquires locks as needed. Once the transaction releases a lock, it enters the shrinking phase and it can issue no more lock requests.

For Example:

             Lock X(A) 
             Read(A)   
             A:= A-200    GROWING PHASE
             Write(A)
             Lock X(B)
             ---------
             Unlock(A)
             Read(B)
             B:=B+200     SHRINKING PHASE
             Write(B)
             Unlock(B)

PROBLEMS WITH TWO PHASE LOCKING PROTOCOL:-

1.) Deadlock
2.) Cascading Roll-Back

Read More

Concurrency Control algorithm in transaction management |Database management system

CONCURRENT CONTROL ALGORITHMS-

There are two approaches used in algorithms to deals with the problems of concurrency control.These are:-

1.) Pessimistic Approach

2.) Optimistic Approach

1.) PESSIMISTIC APPROACH- This approach causes transaction to be delayed in case they conflict with each other at the some time in future.

PESSIMISTIC EXECUTION- The validate operation is performed first,if there is a validation according to compatibility  of lock then only read,compute and write operation are performed.

                           VALIDATE | READ | COMPUTE | WRITE

There are two commonly used algorithms,which are based on pessimistic approach:

a.) Two phase locking protocol

b.) Time stamp ordering protocol

2.) OPTIMISTIC APPROACH- 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 transaction run to completion and only check for conflicts before they commit.An optimistic concurrency control method is also known as validation or certification methods.The Optimistic method does not require locking or time stamping techniques.Instead,a transaction is executed without restrictions until it is committed.It allows transactions to proceed unsynchronized and check conflicts at the end.This approach is based on the premise that conflicts are rare.

OPTIMISTIC EXECUTION- It perform read and compute operation without validation and perform validation just before write operation.

                           READ | COMPOSITE | VALIDATE | WRITE 

ADVANTAGES OF OPTIMISTIC METHODS FOR CONCURRENCY CONTROL:-

1.) This technique is very efficient when conflicts are rare. The occasional conflicts result in the transaction rollback.

2.) The roolback involves only the local copy of data, the database is not involved and thus there will not be any cascading rollbacks.

PROBLEMS OF OPTIMISTIC METHODS FOR CONCURRENCY CONTROL:

1.) Conflicts are expensive to deal with, since the conflicting transaction must be rolled back.

2.) Longer transactions are more likely to have conflicts and may be repeatedly rolled back because of conflicts with short transactions.

APPLICATIONS OF OPTIMISTIC METHODS FOR CONCURRENCY CONTROL:

1.) Only suitable for environment where there are few conflicts and no long transactions.

2.) Acceptable for mostly Read and Query database systems that requires very few update.

Read More

Lock in transaction management | concurrency control

WHAT IS LOCK?

A lock is a variable associated with a data item that describes the status of the item with respect to possible operations that can be applied to it.Generally,there is one lock for each data item in the database.Locks are used as a means of synchronizing the access by concurrent transactions to the database items.

WHY WE NEED LOCKING TECHNIQUES?

When the transactions are running concurrently the various locking methods are used to ensure serializability of the schedule i.e.to make the non-serial schedule produce the same result as that of a serial schedule.

LOCKING TECHNIQUES FOR CONCURRENCY CONTROL- The various techniques of concurrency control are:-

1.)Binary Locks-A Binary lock has two states or values:Locked and Unlocked.Represented as LOCK( ) and UNLOCK( ). 

                    EG-Suppose there is a data item A and there is two transaction T1 and T2.If transaction T1 locks data item A (LOCK(A))then it is set to 1 and the other transaction(T2) is forced to wait.When the transaction is through using the item,it issues an UNLOCK(A) operation ,which sets it to 0 so that (A) may be accessed by other transaction.Hence a binary lock enforces mutual exclusion on the data item.We use binary locks to avoid Lost Update Problem.

                           

                                    T1                                   T2

                                     

               (Set to 1)      LOCK(A)                       (wait)

                                    Read A

                                    A=A+200

                                    Write(A)

               (set to 0)      UNLOCK(A)

                                                                            LOCK(A)

                                                                            Read A

                                                                            A=A+300

                                                                            Write(A)

                                                                            UNLOCK(A)

DISADVANTAGES OF BINARY LOCK:- It is too restrictive for database items.So, binary locking system cannot be used for practical purpose.

2.)SHARE/EXCLUSIVE(READ/WRITE LOCK)-Share lock can be used for read purpose only.However,if a transaction is to write an item A,it must have exclusive access to A.Share lock is represented as (LOCK S(A)) and exclusive lock is represented as (LOCK X (A)).Also known as read/wrie lock. EG-

                        T1             
             

                         LOCK X(SUM)   (exclusive lock on data item SUM)

                         SUM=

                         LOCK S(A)     (Share lock on data item A)

                         Read A

                         SUM=SUM+A

                         UNLOCK(A)

                         LOCK S(B)     (Share lock on data item B)

                         Read B  

                         SUM=SUM+B

                         Write(SUM)

                         UNLOCK(B)

                         UNLOCK(SUM)

Read More

Concurrency Control in transaction management | Database management

CONCURRENT EXECUTION-

Transaction processing systems usually allow multiple transactions to run concurrently.By allowing multiple transactions to run concurrently will improve the performance of the system in terms of increased throughput or improved response time but this allows causes several complications with consistency of the data.

EXAMPLE-To illustrate the concept of concurrency control, consider two travelers who go to electronic kiosks at the same time to purchase a train ticket to the same destination on the same train. There's only one seat left in the coach, but without concurrency control, it's possible that both travelers will end up purchasing a ticket for that one seat. However, with concurrency control, the database wouldn't allow this to happen. Both travelers would still be able to access the train seating database, but concurrency control would preserve data accuracy and allow only one traveler to purchase the seat.

ADVANTAGES OF CONCURRENT EXECUTION-

1. Improved throughput

2. Reduced Waiting time

CONCURRENCY CONTROL-

Process of managing simultaneous execution of transactions in a shared database, to ensure the serializability of transactions, is known as concurrency control.

WHY WE NEED CONCURRENCY CONTROL?

Simultaneous execution of transactions over a shared database can create several data integrity and consistency problems:-

 1.Lost Updates(write-write conflict)

 2.Dirty Read Problem(write-read conflict)

 3.Unrepeatable reads(Read-Write conflict)

PROBLEMS OF CONCURRENCY CONTROL OR TYPES OF CONFLICT OPERATIONS-

1.Lost Updates(write-write conflict)-A lost update problem occurs when two transactions that access the same database items have their operations in a way that makes the value of some database item incorrect. E.G.- if Transaction T1 and T2 both read a record and then update it,the effects of the first update will be overwritten by the second update.It occurs in case of write-write conflict.

2.Dirty Read Problem(write-read conflict)-A dirty read problem occurs when one transaction updates a database item and then the transaction fails for some reason. The updated database item is read or accessed by another transaction before it is changed back to the original value. E.G.- a transaction T1 updates a record ,which is read by the transaction T2 then T1 aborts and T2 now has values which have never formed part of stable database or we can say T1 reads a dirty data.It occurs in case of write-read conflict.

3.Unrepeatable reads(Read-Write conflict)-Read-Write conflict occurs if a transaction writes a data object previously read by another transaction.It means a transaction reads several values from a database while second transaction updates some of them.

Read More

Scheduling of transaction | Transaction Management

SCHEDULING OF TRANSACTIONS

Scheduling of transaction represents the actual execution sequence. A schedule of a system is an abstract model to describe execution of transactions running in the system. A Schedule is a list of actions (reading, writing, aborting or committing) from a set of transaction must consist of all instructions of those transactions and must preserve the order in which the transaction appear in each individual transaction.

For Example- If in a transaction T1 the instruction write(A) appears before read(A) then in any valid schedule this sequence must also be preserved.

Let's discuss various types of scheduling-

 1. Complete Schedule- A  Schedule that contains either an abort or a commit for each transaction whose actions are listed in it is called a complete schedule. In simple words we can say after each transaction there is commit or abort command.A complete schedule must contain all the actions of every transaction that appears in it.

2. Serial Schedule- Serial Schedule is the schedule in which after completion of one transaction second take place.For Example- There are 2 transactions T1 and T2. 

                          T1                  T2

                          A=A+100

                          B=B-100

                                               A=A*7.06

                                               B=B*7.06

                                    (Schedule 1)

When T1 transaction completes its execution after that transaction T2 takes place.This is called Serial Schedule.

3. Non Serial Schedule- A Schedule in which the operations from a set of concurrent transaction are interleaved is called a non-serial Schedule.For Example- There are 2 transactions T1 and T2 and transaction takes place concurrently.This is called Non-serial transaction.

                          T1                      T2

                          A=A+100

                                                    A=A*7.06

                          B=B-100

                                                    B=B*7.06

                                    (Schedule 2)

                        

4. Equivalent Schedule- The effect of executing the first schedule is identical to the effect of executing the second schedule. For Example- (Schedule 2) is equivalent to (Schedule 1).

5. Serializiable schedule- A non-serial schedule is said to be serializiable if it produces the same result as that of some serial execution of  transaction running concurrently without interfering with each other.For Example- Output of (Schedule 1=400) and (Schedule 2=400) then it is called serializiable. In serializability the ordering of read and writes operations are important.                          

Read More