Normalization In Database
This article explains database normalization terminology. A basic understanding of this terminology is helpful when discussing the design of a relational database.
Normalization is a database design technique that organizes tables in a manner that reduces or removes redundancy and dependency of data.
It divides the larger tables into smaller tables and links them using well-structured relations.
** However, in most practical applications, normalization achieves its best in the 3rd Normal form.
A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies.
The goal is to avoid (minimize) anomalies:
Insertion Anomaly: adding new rows forces the user to create duplicate data.
Deletion Anomaly: deleting a row may cause loss of other data representing completely different facts.
Modification Anomaly: changing data in a row forces changes to other rows because of duplication.
Step Of Normalization:
Step of normalization are divided into the following normal forms:
0. Unnormalized form
1. 1NF (First Normal Form)
2. 2ND (Second Normal Form)
3. 3NF (Third Normal Form)
4. BCNF (Boyce-Codd normal form)
5. 4NF (Fourth Normal Form)
6. ETNF (Essential tuple normal form)
7. 5NF (Fifth normal form)
8. DKNF (Domain-key normal form)
1. 1NF (First Normal Form):
If the table to be in the First Normal form,
1. It should only have single-valued attributes/columns.
2. Values stored in a column should be of the same domain
3. All the columns in a table should have unique names.
4. And the order in which data is stored, does not matter.
As you can see in the above example student abc has contained more than one subject value. But as per rule 1st Normal form, each column contains only a single value.
So here, I convert the above Student Table in 1st Normal form.
2. 2ND (Second Normal Form):
If the table to be in the second Normal form,
1. Table or relation must be in the First Normal Form.
2. It should not have a partial dependency.
So above example in 1NF, So below I convert 2nd Normal.
3. 3NF (Third Normal Form):
If the table to be in the Third Normal form,
1. Table or relation must be in the Second Normal Form.
2. There should be no transitive dependency in the table.
Decompose table in 3rd normal form.
Other normalization forms:
Boyce Codd Normal Form (BCNF), Fourth normal form, fifth normal form, and rest other who below 5NF are rarely a consideration in the practical design of Database. Neglect these rules may result in a less than perfect Database design. But should not affect functionality.
Conclusion:
In this article, we discussed the different terminology in Database normalization, Like 1st normal form, 2nd normal form, etc. And also gives some examples based on that concept. Database designing is critical to the successful implementation of a database management system that meets the data requirements of an enterprise system. Normalization Process in DBMS helps produce database systems that are cost-effective and have better security models. Functional dependencies are a very important component of the normalized data process. Most database systems are normalized databases up to the third normal form. A primary key uniquely identifies is the record in a Table and cannot be null. A foreign key helps connect the table and references a primary key.
Thanks.
