What is ACID property in transactions.

 

In computer science ACID properties stands for below :

1. A : Atomicity
2. C : Consistency
3. I : Isolation
4. D : Durability

Atomicity:

Transactions are mostly composed of multiple statements. Atomicity guarantees that each transaction is treated as single unit, which either succeeds completely or fails completely.

Atomic system must guarantee the atomicity in each and every situation, including power failures, errors and crashes.

Example: Suppose a person is trying to do a transaction in which person is trying to transfer money from A's account to B's account. So in this case amount will be debited from A and same amount will be credited to B. But if this transaction fails at any point, then the balances of A and B will be remain unchanged.

1. A-------------------------------->B (10$)
2. A-10 ----------------------------> B
3. Transactions fails 
4. B wasn't updated
5. Rollback
6. A remain unchanged (No deduction)  

Consistency:

Consistency insures that transaction can bring database from one valid state to another. This prevents data corruption by illegal transaction. Means transaction should follow all rules to bring database from one valid state to other. Here rules could be any check constraints, triggers, foreign keys etc.  

Example:

1. Transferring 10$ from A to B
2. So A-10 -------------------> B+10
3. It should be not be doing A-5 and adding B+10  

Isolation:

Isolation means all transactions running in parallel should be isolated from each other.Isolation is the main goal of concurrency control.

Consider two transactions: T₁ transfers 10 from A to B. T₂ transfers 20 from B to A.

Combined, there are four actions:

1. T1 subtracts 10 from A.

2. T1 adds 10 to B.

3. T2 subtracts 20 from B.

4. T2 adds 20 to A.

If these operations are performed in order, isolation is maintained, although T₂ must wait. Consider what happens if T₁ fails halfway through. The database eliminates T₁'s effects, and T₂ sees only valid data.

By interleaving the transactions, the actual order of actions might be:

1. T1 subtracts 10 from A.

2. T2 subtracts 20 from B.

3. T2 adds 20 to A.

4. T1 adds 10 to B.

Durability:

Durability ensures that changes made to the database (transactions) that are successfully committed will survive permanently, even in the case of system failures. This ensures that the data within the database will not be corrupted by:

• Service outages
• Crashes
• Other cases of failure

Example:

1. Transfer 10$ from A to B.
2. Valid state: A-10 to B+10
3. Transaction committed  successfully. 
4. Power failure occurred 
5. Checked database after system is up
6. All changes done by transaction are there. 

What is SQL and the history of SQL?

What is SQL

SQL stands for structured query language. It is used to query the relational databases. Since the name is saying the structured means it handles structured data. 

What is structured data

Here structured data means a data which is in tabular format. And the data that have some relations between data is called relational data. 

You can query structured data residing inside relational database management system. Relational database management system is a management system which stores and handles the data. Data volume could be too much like in GB, TB,PB. RDMS(Relational database management system) can handle this much of volume.

There are lot of RDBMS in the market like Oracle, SQL server, MYSQL and Postgres etc. Here MYSQL and Postgres both are open source.

Previously SQL was called structured english query language (SEQUEL) . Later it was renamed as structured query language(SQL). Many people call it SEQUEL and many call it SQL. So it's up to you what you want to call it.

A brief History of SQL

• • 1970 − Dr. E. F. Codd of IBM is known as the father of relational databases. He described a relational model for databases.

  • 1974 − Structured Query Language appeared.

  • 1978 − IBM worked to develop Codd's ideas and released a product named System/R.

  • 1986 − IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software which later came to be known as Oracle.

How to write SQL and how to fetch data

First you need to decide which RDBMS you are going to install on your system. I would recommend using postgres which is open source or SQL server which is not open source but you can download it's free version. Microsoft is developer of SQL server.

Now I am assuming that you have downloaded the RDBMS. If not then you can practice on any online platform which provide you sql editor.

Suppose I have table EMPLOYEE, which has three columns name, age, city. 

Name	Age	City
Suraj	25	Banglore
Mohit	26	Chennai
Rahul	27	Lucknow

I want to fetch all records inside this table, so I will write below query:

SELECT * FROM EMPLOYEE;

Here you are query data then you need to write SELECT , * means all columns (Name,Age,City) ,write from keyword then table name. In this case table name is EMPLOYEE.

So above query will give you all records from the table. If I want to select only name then I need write only Name column in my query like Below:

SELECT Name FROM EMPLOYEE;

Similar for Age and City as well:

SELECT Age FROM EMPLOYEE;

SELECT City FROM EMPLOYEE; 

Suppose I want to display all details about Suraj then I'll write below query:

SELECT * FROM EMPLOYEE WHERE NAME = 'Suraj' 

Name	Age	City
Suraj	25	Banglore

 You can use a wild card for search but there is a lot to cover on SQL. I suggest to read more on basics of SQL to get started.

•