Database transaction and ACID properties

Database Transaction
A transaction is logical unit of work which has a complete status performed within a database management system. It should not be in an inconsistent status.Example :-
Assume a user has a savings account having 4000 unit of currency. He withdraws 100 unit of currency from an ATM and this withdrawal is referred as a transaction.1. User/Card/Account authentication.
2. Withdrawal request for 100 unit of currency.
3. Balance verification.
4. Dispenses the currency to user and update the balance to 3900.
5. Print receipt and exit.

Assume that a network error happened after Step 3. The transaction will not be completed.

ACID properties
A database transaction must follow ACID properties.

A – Atomic
C – Consistent
I –  Isolated
D – Durable

Atomic :-
This property ensures the transaction either in complete status or nothing has started. There must not be any scenario like user received some currency amount and balance remains same or balance updated and failed to dispense the amount.

Consistent:-
Every transaction must be consistent.

Isolated:-
Every transaction must be isolated to avoid the locks or deadlocks. Deadlock is a condition when two or more users or processes waiting for the other to release a resource.

Durable:-
A transaction which has committed or saved must be there for permanently.

Read more on how to install Oracle 11g on Ubuntu Linux here 

Advertisements

Difference between DBMS and RDBMS

RDBMS is a super set of DBMS plus some more features.

Consolidated few differences here.


DBMS RDBMS
Organization, storage, management, and retrieval of data Yes Yes
Flat file design Yes No
Relational design No Yes
Password protection Yes Yes
Enforce referential integrity No Yes
Null or blank data management Yes Yes
Meta data support Yes Yes
Support relatively large set of data No Yes
Support complex business applications No Yes

Microsoft Access is an example of  DBMS.

Oracle, Microsoft SQL Server, DB2 are few examples for RDBMS.

What is ANSI SQL

ANSI means American National Standards Institute (http://www.ansi.org)

Evolution of ANSI SQL

SQL is an integral part of many modern RDBMS like Oracle, DB2, Microsoft SQL Server, MySQL etc. Each vendor developed their own SQL syntax for their own products. All over the world many government, public and private agencies use different database products from different vendors and each develop system based on vendor dependent SQLs. So ANSI developed a common standard for SQL which runs in all modern vendor databases.

Advantages

  • Work in all modern databases.
  • Easy to integrate different systems.
  • Easy to migrate from one vendor database to another.
  • Less maintenance cost.

Disadvantages

  • Relatively complex syntax compared to vendor SQLs.
  • Higher development cost.

Explanation with example

SQL to find all employees from Marketing Department. I have written two queries one in Oracle SQL format and another is ANSI SQL format

1) Standard SQL style

select e.empno,e.empname,e.salary,d.deptname
from emp e,dept d 
where e.deptno = d.deptno
and d.deptno = 2;

2) ANSI Style

select e.empno,e.empname,e.salary,d.deptname
from emp e join dept d
on (e.deptno = d.deptno)
where d.deptno = 2;

Assume a company using Db2 database and writing all their SQL in non-ANSI standard, and one fine morning management decide to port some applications from Db2 to Oracle or Microsoft SQL Server, most of the SQLs need to re-write with respect to the new database standard. If all SQL follows ANSI standard it would be quiet easy to migrate from any vendor database to another.

Also ANSI SQL maintenance is easy because a lot of employees might come from different platforms and every developer can easily follow if every body adhere to the standard.

As you know that ANSI SQL syntax is little difficult to follow initially. But once every body used to it, it is quite easy. Though it is considered as more complex than non-ANSI SQL standard. If you see the above queries both looks almost similar, but for larger queries having too many joins and where conditions it would be little complex as you think.

Another disadvantage is ANSI SQL will not have all features of vendor database SQLs because ANSI is a common standard and it is very difficult to integrate all features of modern database SQLs.

See the below Oracle SQL to print 1 to 10 using only SQL.

select level from dual connect by level <= 10; 

The same feature is not available in ANSI SQL ( As of today ) and in another vendor databases corresponding SQL will be much complex than above.

Finally choosing and adhering ANSI SQL standard is an important factor you need to decide before you start writing the SQLs. Personally I would say it is very ideal for small companies and websites to follow ANSI SQL because you cannot predict when your base database vendor ( Actually Open Source databases ) giving you some trouble.

Related Posts
What is SQL
What is Oracle Database

Introduction – DBMS and RDBMS

What is DBMS ?

A DBMS is a set of software programs that controls the system organization, storage, management, and retrieval of data in a database. The DBMS determines how data are stored and retrieved.

Data is the back bone of any organization. So it is very essential to manage it in a efficient way. DBMS accepts request from application and processes in the operating system and vice verse.

Examples :- Oracle, Microsoft SQL Server, DB2, MySQL etc.

Features of DBMS

1. Data Security –

DBMS is capable of protecting the data stored inside the database. Examples are database passwords, schema management etc.

2. Data Integrity –

DBMS ensures the data integrity by maintaining the transcriptional and user level access. It eliminates the unwanted duplicity.

3. Data Access –

DBMS provides an efficient way to access and manage the data called SQL (Structured Query Language). All modern databases support SQL.

4. Data Audit –

DBMS should allow to audit and manage the data stored inside the database.

What is RDBMS ?

Any DBMS which has the ability to represent the data in set of tuples (related set of data) which share the same type is called RDBMS. In another way it organizes data into related rows and columns. All RDBMS terms can be defined in mathematical terms. In RDBMS all tables will have a relationship among.

Relational Model

The fundamental assumption of the relational model is that all data is represented as mathematical n-tuples relations. The relational model of data permits the database designer to create a consistent, logical representation of information.

Comparing Relational and Mathematical models

Relational Model Mathematical Model
Column Attribute
Row Tuple
Table Relation
Data Set Element

 

What is Oracle Database ?

Oracle is the leading RDBMS (Relational Data Base Management System) available in the modern market. Oracle Software is produced and marketed by Oracle Corporation (www.oracle.com). Oracle Corporation has many other products based on Applications, Middleware, Server and Storage Systems etc. In 2011 Oracle has acquired Java and MySQL where MySQL is a open source database.

Major mile stones
* 1977 – Oracle founded ( Formerly Software Development Laboratories )

* 1979
* 1983 – Oracle database 3
* 1992 – Oracle database 7
* 1997 – Oracle database 8
* 1999 – Oracle database 8i
* 2001 – Oracle database 9i
* 2003 – Oracle database 10g
* 2007 – Oracle database 11g

Oracle database 11g release 2 is the latest version. Oracle is planning to release latest database release Oracle 12c. Read more about Oracle 12c here.

Oracle Database

Oracle Database is suitable for all types of small and large applications including enterprise applications, data warehouses, websites, big data analysis projects etc. Oracle is considered to be fast, reliable, secure and easy to manage for all types of database.

Database Market Share 

Without argument Oracle is the best database available in the market as of today and holds  majority of the database market share. Microsoft SQL Server and IBM DB2 are the nearest rival for Oracle nowadays.

What is SQL

SQL (pronouncing as ‘sequel’) means Structured Query Language. It is a programming language to manage data which is stored in an organized manner.

Why SQL ?

How SQL originated ? Every body know that data is the back bone of any organization or company. So cost effective data management is vital. So there should be a common tool to handle and this leads to development of SQL.

History

SQL developed by IBM in 1970. In 1986 ANSI (American National Standards Institute) standardized the SQL and it is referred as ANSI SQL.

Basic Components of SQL 

SELECT, INSERT, UPDATE and DELETE are the primary components of basis SQL.

SELECT – Uses to fetches and displaying the data in a structured manner.

INSERT –  Uses to store data into the database.

UPDATE – Update or modify the stored data.

DELETE –  Removing the stored data.

SELECT statement is the most widely and frequently used one.

To explain the above statements we need to define a table ( This is called the basic data storage unit in database ) first. Assume that

EMP table stores employee details like Empno, Name, Age,

Structure of EMP Table

Empno Name Age
10 Mark 26
20 Adam 30
30 Gary 33
40 Lisa 27
There are 4 records in EMP table.

SELECT statement

Example :-  select * from EMP;

Explanation :-

The above statement will fetch all records from a table called EMP.

INSERT statement

Example :-  insert into EMP values(50,‘Lo‘,33);
Explanation :-
The above statement will insert one records into EMP table.

UPDATE statement

Example :-  update EMP set name = ‘Lopez‘ where Empno = 50;

Explanation :-

The above statement will update name column in EMP table.

Delete statement

Example :-  delete from EMP where Empno = 50;

Explanation :-

The above statement will update name column in EMP table.

Apart from above four components there are many complex components are available in modern SQL.

Learn SQL

Generally speaking SQL is a complex programming language. Mastering SQL need many years of learning and experience, even though you can easily learn basic SQL in few days.  To learn initially we need a database management system. In market there are many database products are available. Oracle, Microsoft SQL Server, DB2, MySQL, PostgreSQL etc. For educational purpose most of them are free. You can download and install in your desktop or laptop and start learning SQL today itself.