ACID Properties

ACID Property is the most important part of the database. ACID stands for Atomicity Consistency Isolation Durability.

Atomicity:

This means that “all or nothing”. When an update occurs to a database either all or none of the update will become available to anyone beyond the user. This update to the database is called a transaction and it either commits or aborts.

Consistency: 

It ensures that any changes to values in an instance are consistent with changes to other values in the same instance.

Enroll in the Database Courses to get yourself industry ready.

Isolation:  

Isolation is is needed when there are concurrent transactions. Concurrent transactions are transactions that occur at the same time, such as shared multiple users accessing shared objects.

An important concept to understanding isolation through transactions is serializability. Transactions are serializable when the effect on the database is the same whether the transactions are executed in serial order or in an interleaved fashion.

Durability:

Maintaining updates of committed transactions is important. These updates must never be lost. The ACID property of durability addresses this need. Durability refers to the ability of the system to recover committed transaction updates if either the system or the storage media fails.

We have the perfect professional SQL Training Course for you!

Become a Database Architect

  • Normalization:

Normalization is a technique which is used to organize the data in the database. It is a systematic approach to remove the data redundancy. Normalization is mainly used for two purpose,

  • To remove data redundancy.
  • Ensuring data dependencies is proper.

Without normalization 3 anomalies occurred and it becomes difficult to handle and update data. To understand these anomalies let’s take an Student table

ID Name Address Subject
201 Akshay Jaipur Maths
202 Charu Bombay Bio
203 Disha Banglore Physics
204 Eva Noida Maths

 

  • Updation Anamoly – To update address of a student who occurs twice or more than twice in a table, we will have to updateAddress column in all the rows, else data will become inconsistent.
  • Insertion Anamoly – Suppose for a new admission, we have a Student id, name and address of a student but if student has not opted for any subject yet then we have to insertNULL there, leading to Insertion Anamoly.
  • Deletion Anamoly – If id 401 has only one subject and temporarily he drops it, when we delete that row, entire student record will be deleted along with id.

Get 100% Hike!

Master Most in Demand Skills Now !

Normalization Form:

Normalization Rules are divided into 4 normal forms.

  • First Normal Form
  • Second Normal Form
  • Third Normal Form
  • BCNF

Wish to get certified in SQL! Learn SQL from top SQL experts and excel in your career with Intellipaat’s SQL certification.

First Normal Form:

As per First Normal Form, no two rows of data must contain repeating data i.e., whenever we search for a particular result the multiple columns cannot be used to fetch the same row.

Each table should be organized into rows, and each row should have a primary key that distinguishes it as unique.

For example, consider a table not in first normal form

Student Age Subject
Akshay 15 Maths, Physics
Charu 14 Biology
Disha 17 Maths

Student table in 1NF will be:

Student Age Subject
Akshay 15 Maths
Akshay 15 Physics
Charu 14 Biology
Disha 17 Maths

Using the First Normal Form, data redundancy increases, as there will be many columns with the same data in multiple rows but each row as a whole will be unique.

Second Normal Form:

As per the Second Normal Form there must not be any partial dependency of any column on primary key. It means that for a table that has concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence.

  • Meet all the requirements of the first normal form.
  • Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
  • Create relationships between these new tables and their predecessors through the use of foreign keys.

For example:

New student table following 2NF will be:

Student Age
Akshay 15
Charu 14
Disha 17

In Student Table the candidate key will be Student column, because all other column i.e Age is dependent on it.

Student Subject
Akshay Maths
Akshay Physics
Charu Biology
Disha Maths

In Subject Table the candidate key will be {Student, Subject} column. Now, both the above tables qualifies for Second Normal Form and will never suffer from Update Anomalies.

Prepare yourself for the industry by going through this Top SQL Interview Questions and Answers!

Become a SQL Developer

Third Normal Form:

  • A relation is in third normal form (3NF) if it is in second normal form and it contains no transitive dependencies.
  • Consider relation R containing attributes A, B and C. R(A, B, C)
  • If A → B and B → C then A → C
  • Transitive Dependency: Three attributes with the above dependencies.

For example:
Student_details table

ID Name Subject DOB Address Mobile No. City

New Student_detail table:

ID Name Subject

 
Address Table:

ID Address DOB Mobile No. City

The advantage of removing transtive dependency is,

  • Amount of data duplication is reduced.
  • Data integrity achieved.

Career Transition

Got Job With Salary Hike | Process Developer to Sr. Software Engineer Career Transition
Got Multiple Job Offers with 100% Salary Hike | Best SQL Course | Intellipaat Career Transition
How To Switch Career From A Support Role To Oracle DBA Engineer | Got Job With 80% Salary Hike
How To Become A Database Expert From A Non Tech Background | Got Job With Salary Hike | Intellipaat
Got Job as Cloud DBA Just After completion of the Course | Intellipaat Career Transition
SQL Developer to Software Engineer Career Transition | Got Job with 120% Salary Hike | Intellipaat

Boyce and Codd Normal Form (BCNF):

This is a higher version of third normal form. This form deals with certain type of anamoly that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF, following conditions must be satisfied:

  • R must be in 3rd Normal Form
  • and, for each functional dependency ( X -> Y ), X should be a super Key.

Come to Intellipaat’s SQL Community if you have more queries on SQL!

Course Schedule

Name Date Details
SQL Training 23 Mar 2024(Sat-Sun) Weekend Batch
View Details
SQL Training 30 Mar 2024(Sat-Sun) Weekend Batch
View Details
SQL Training 06 Apr 2024(Sat-Sun) Weekend Batch
View Details