Normalization in SQL

Normalization is the processes of reducing the redundancy of data in the table and also improving the data integrity.

Normalization emphasizes how to organize a database’s columns and tables to ensure that their dependencies are properly enforced by database integrity constraints.

Normalization Types

First Normal Form (1NF) 

• It simplifies each attribute in a relation.
• In 1NF, there should not be any repeating group of data.
• Each set of column must have a unique value.
• It contains atomic values because the table cannot hold multiple values.

Second Normal Form (2NF) 

• In 2NF, the table is required in 1NF.
• The main rule of 2NF is, ‘No non-prime attribute is dependent on the proper subset of any candidate key of the table.’
• An attribute which is not part of candidate key is known as non-prime attribute.

Function in SQL

Third Normal Form (3NF)

• Third Normal Form (3NF) is used to minimize the transitive redundancy.
• In 3NF, the table is required in 2NF.
• While using the 2NF table, there should not be any transitive partial dependency.
• 3NF reduces the duplication of data and also achieves the data integrity.

BCNF (Boyce – Code Normal Form)

• BCNF which stands for Boyce – Code Normal From is developed by Raymond F. Boyce and E. F. Codd in 1974.
• BCNF is a higher version of 3NF.
• It deals with the certain type of anomaly which is not handled by 3NF.
• If every determinant is a candidate key, then it is said to be BCNF.
• Candidate key has the ability to become a primary key. It is a column in a table.

Data Definition Language (DDL)

Fourth Normal Form (4NF)

• Fourth Normal Form (4NF) does not have non-trivial multivalued dependencies other than a candidate key.
• 4NF builds on the first three normal forms (1NF, 2NF and 3NF) and the BCNF.
• It does not contain more than one multivalued dependency.
• This normal form is rarely used outside of academic circles.

Fifth Normal Form (5NF)

• 5NF is also knows as Project-Join Normal Form (PJ/NF).
• It is designed for reducing the redundancy in relational databases.
• 5NF requires semantically related multiple relationships, which are rare.
• In 5NF, if an attribute is multivalued attribute, then it must be taken out as a separate entity.
• While performing 5NF, the table must be in 4NF.

Normalization in SQL

Related posts:

SQL Interview Questions & Answer – Best TOP 10 (Day – 3) Advanced Routing in MVC JOIN in SQL with Top 10 Example SQL Interview Questions & Answer – Best TOP 10 (Day – 1)