Why is normalization important and to upto which form should normalization be done and why? 

 

Answer:

To understand the importance of normalization first, we need to understand what normalization is. Normalization is a process, which is used to remove all the flaws of a database which consists of a bad design which can result in inconsistent data retrieval as well as data insertion. It can be very difficult to add, update or delete data in a database which is inconsistent. 

There are many. Forms of normalization. These are 1NF, 2NF, 3NF, BCNF, 4NF and 5NF.

With the help of these normalizations we can remove all the anomalies in the database which can occur due to updating deleting or inserting elements. 

There are two types of anomalies.

First is insertion anomaly, which occurred due to the insertion of data. Like inserting a record which does not exist on the database. 

Second is the deletion anomaly, in which the data which is to be deleted, due to poor design of database the other record gets deleted, which was not selected in the first place which results in loss of data. 

Next is eliminating the redundancy of data which occurs when a data is sorted multiple times. It is resolved by normalization. 

Data consistency while inserting or updating a table if a record is left, it can lead inconsistent data with a help of normalization we can resolve it and ensure that the data is consistent.

Isolation of data a database, which is designed well states that the changes in a particular table does not affect the other table. Which can be achieved through normalization.

Data dependency means when a data is stored, it is stored in a correct table to where it belongs. Which is achieves by normalization.

Now the types of Normalizations -

• First normal form (1NF)

Each table must have a primary key

• Second normal form (2NF)

Should have the requirements of 1NF along with if the table has a composite key.

• Third normal form (3NF)

Data in a table must be dependent only on the primary key and not on any other field in the table and the database also meets the requirements of the 2NF.

• Boyce-Codd normal form (BCNF)

Non-trivial functional dependencies of attributes should not be present in the table on something other than a superset of a candidate key.

• Fourth normal form (4NF)

A table is in 4NF if and only if it is in the BCNF and multi-valued dependencies are functional dependencies. 4NF also removes all the unwanted data structures which are the multi-valued dependencies.

• Fifth normal form (5NF)

There should be no non-trivial join dependencies which do not follow from the key constraints.

A table is 5NF when it is 4NF and also every join dependency in it is implied by the candidate key.

Up to which form should normalization be done and why?

This answer depends on the situation of the project or length of the database on which normalization is being applied as each progressive step that is each normalization make the table or the database easier to navigate, import and extract data but it significantly impacts on the overall performance of the database. So according to the situation striking a good balance between the normalization and the impact on performance is the most significant aspect.