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 



Oracle SQL Functions

Oracle database supplied with many in built SQL functions. Oracle recommends to use in built SQL function rather than using a user defined functions. It perform better than user defined functions. So try to utilize in built SQL functions as much as you can. Some of the SQL functions really helps while writing complex SQLs.     

Most used SQL functions can be categorized as

1. Aggregate functions - Returns a single result from a  group of rows.
Examples :- AVG, MAX, MIN, COUNT,SUM etc.


2. Numeric functions - Accepts numeric input and returns numeric result. It is also called as Mathematical functions.
Examples :- SIN, COS, TAN,  MOD, POWER, EXP etc.


3. Character functions - Returns character result from character input.
Examples :- CHR, LOWER, UPPER, TRIM etc.


4. Date functions - To manipulate date data.
Examples :- SYSDATE, ADD_MONTHS, TRUNC etc.


5. Conversion functions - To convert one data type to another data type
Examples :- TO_CHAR, TO_DATE, TO_NUMBER etc.


6. Analytical functions - It computes aggregate based on group of data
Examples :- RANK, DENSE_RANK, LAG etc.


Apart from these Oracle has few more functions like Collection functions, Large Object functions, XML functions, Data Mining functions, Hierarchical functions etc.

Some of the above listed functions which falls multiple categories. For example TRUNC is a Numeric as well as Date function. TRUNC(date) returns date value while TRUNC(number) returns numeric value.

Each individual SQL function explained in separate sections with examples.


DUAL table in Oracle

DUAL table is a special single row table present by default in Oracle Databases.

Structure of DUAL table
DUAL table has only one column called DUMMY as Varchar2(1) data type and value as X.


Examples
1) select 1 from DUAL;
    returns 1

2) select 1+2 from DUAL;
    returns 3

3) select sysdate from DUAL;
    returns system date of oracle database.

4) select `sql` from DUAL;
    returns`sql`

5) select user from DUAL;
    returns oracle user logged in.

6) select 
   (select empno from emp where empno = 10)
   from DUAL;
    returns 10

DUAL table can be used to test the SQL functions ( Both in-built and user defined functions)

7) select func_salary(10) from DUAL;
where func_salary is a user defined function returns a value when argument is 10.

8) select LOWER(`ORAclE`) from DUAL;
 returns`oracle` where LOWER is a in-built SQL function.

 DUAL table can be used in SQL and PL/SQL.


SELECT statement

SELECT statement has five major sections

1. SELECT key word 
2. column specification 
3. from key word
4. table or list of tables
5. where clause


Section 1, 2,3 and 4 are mandatory. 5 is optional. 


Explanation with examples  
Consider the table EMP with below structure

Empno Name DOB Salary
10 Mark 12/31/1970 4000
20 Adam 11/20/1980 4500
30 Gary 02/03/1995 3500
40 Lisa 06/14/1960 6000

1) All records
Suppose user needs to see all records from EMP table. Below statement will pull all records from the table. 
select from EMP;

Note :- 1) In SQL `*` means all columns. Here table has four columns.
            2) `;` is the terminator for the SQL statement.
            3) SQL is not case sensitive.

2) Selected columns
select empno,salary from EMP;
The above statement fetches all empno and salary columns from EMP table

3)  All records with where condition
select empno,salary from EMP where empno = 10;
 The above statement will fetch a record with empno as value 10

4)  All records using where condition and order by
select empno,salary from EMP where empno > 10 order by salary asc;
 The above statement will fetch all record with empno as value > 10 in a ascending order of salary

Select SQL statement has lot of features, some of them are pretty straight forward and some of them are really complex. Normally SQL cannot be learnt by just reading. You just need to practice SQLs with real examples. For that we need a database installed in an operating system.
Each database will have a client utility to manage SQLs. In Oracle Database supplied with  rwo SQL client utilities, SQL Plus and Oracle SQL DeveloperSQL Plus is a command line utility and Oracle SQL Developer is a graphical user interface(GUI)

List of standalone tools available in the market.
1. TOAD - www.quest.com
2. Oracle SQL Developer - www.oracle.com
3. PL/SQL Developer - http://www.allroundautomations.com  
4. Advanced Query Tool(AQT) - www.querytool.com





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. Recently Oracle has acquired Java and MySQL were MySQL is a open source database.

Major mile stones

* 1977 – Oracle founded
* 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.

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

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 syntaxes 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 dependant 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.

What is SQL


SQL (pronouncing as 'sequel') means Structured Query Language. It is a programming language to manage data in a DBMS or RDBMS. SQL is the back bone of any modern RDBMS.

History
SQL developed by IBM in 1970. In 1986 ANSI standardized the SQL.

SQL Statements
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 first. Assume tat

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.
We will discuss each SQL statements in a very detail manner.




Difference between DBMS and RDBMS


RDBMS is a super set of DBMS. Consolidated few differences here.



Microsoft Access is an example of  DBMS.
Oracle, Microsoft SQL Server, DB2 are few examples for RDBMS.



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



Read Also Difference between DBMS and RDBMS