Normalization in the database, a huge amount of data becomes saved in multiple tables. There can be the chance of redundancy to be existing in the data. So Normalization in DBMS can be described as the process in which reduces the redundancy from the data and assures data integrity. Also, the normalization of data assists in removing the insert, update and delete exceptions.
How Does Normalization in Database?
The normalization in Database Management System can be specified as a technique to compose the schema of a database and this is done by transforming the existing schema which also reduces the redundancy and dependency of the data. So with Normalization, the unwanted duplication in data is eliminated along with the exceptions. In insert exception, the values such as null are not provided to be inserted for a column.
Types of Normalization in DBMS
The normal forms which are used most commonly in Database are as below:
- (1F) First Normal Form
- (2F) Second Normal Form
- (3F) Third Normal Form
- (BCNF) Boyce-Codd Normal Form
First Normal Form
The table or relation is assumed to be in First Normal Form if it does not carry any multi-valued or composite attributes. So the table or relation should include only single-valued attributes for fulfilling the condition for the First Normal Form.
Let us take the example of the STUDENTS with Subjects table as below:
| Roll | Name | Subject |
| 9 | Rauf | Computer, Science |
| 3 | Ali | History, Urdu |
| 2 | Ahmad | Biology |
The above-mentioned table is not in First Normal Form as this carries the multi-valued attribute. The below table is transformed into the First Normal Form as it contains only atomic values.
| Roll | Name | Subject |
| 9 | Rauf | Computer |
| 9 | Rauf | Science |
| 3 | Ali | History |
| 3 | Ali | Urdu |
| 2 | Ahmad | Biology |
Second Normal Form
A relation or table to be in Second Normal Form should be in First Normal Form and it should not contain any partial dependency. So in Second Normal Form, the table should not hold any non-prime attribute depending upon the proper subset of any candidate key.
Let us consider the STUDENTS table as cited previously as below:
| Roll | Name | Subject |
| 1 | Rauf | Computer |
| 1 | Rauf | Science |
| 3 | Ali | History |
| 3 | Ali | Urdu |
| 2 | Ahmad | Biology |
The above table needs to be broken into two tables as below to make it Second Normal Form compliant.
STUDENT
| Roll | Name |
| 1 | Rauf |
| 2 | Ali |
| 3 | Ahmad |
SUBJECT_DETAIL
| Roll | Subject |
| 1 | Computer |
| 1 | Science |
| 2 | History |
| 2 | Urdu |
| 3 | Biology |
The functional dominion from the table ‘STUDENT’ is transferred and the column Subject in ‘SUBJECT_DETAIL’ is fully conditional on the primary key ‘Roll’.
Third Normal Form
A table is in Third Normal Form if it is in Second Normal Form and there should not be any transitive territory for the non-prime attributes. So for every non-trivial functional Dependency A->B, if any of the two conditions is true from the below, the relation is said to be in Third Normal Form.
- X is a super key.
- Y is a prime attribute where each element of B is part of any candidate key.
Let us consider the table ‘EMPLOYEE’ as below:
| EMP_ID | EMP_NAME | EMP_DEPT | EMP_STATE | EMP_ COUNTRY |
| 29 | Rauf | Sales | Florida | U.S. |
| 38 | Usman | Finance | Maharashtra | India |
| 99 | Asim | Marketing | Texas | U.S. |
4. Boyce-Codd Normal Form
For a table to be in Boyce-Codd Normal Form, it should be in Third Normal Form, and for every functional dependency A->B, A is the super key in the table.
EMP_DEPT table:
| ID | COUNTRY | DEPARTMENT | DEPT_TYPE | DEPT_NO |
| 990 | Pakistan | Marketing | M08 | 05 |
| 190 | US | Finance | F057 | 02 |
| 190 | India | Sales | S102 | 02 |
The functional mandate for the above table is: ID -> COUNTRY, DEPARTMENT -> {DEPT_TYPE, DEPT_NO}. {ID, DEPARTMENT} is the candidate key. To modify the above table to BCNF, we have to break it into three tables as below:
EMP_COUNTRY:
| ID | COUNTRY |
| 990 | Pakistan |
| 190 | US |
| 190 | Pakistan |
DEPT_DETAILS:
| DEPARTMENT | DEPT_TYPE | DEPT_NO |
| Marketing | M98 | 04 |
| Finance | F567 | 02 |
| Sales | S002 | 01 |
EMP_DEPARTMENT_MAP:
| ID | DEPARTMENT |
| 990 | Marketing |
| 190 | Finance |
| 190 | Sales |
The functional dependency for the above is ID -> EMP_COUNTRY, DEPARTMENT-> {DEPT_TYPE, DEPT_NO}. The candidate keys for the tables EMP_COUNTRY, DEPT_DETAILS and EMP_DEPARTMENT_MAP are ID, DEPARTMENT and {ID, DEPARTMENT}.
Advantages
Below are the advantages of Normalization:
- Redundant data gets eliminated efficiently.
- Enhanced data excellence and flexibility in database designing.
- The revised overall organization of data in the database.
- Data is constant and logically saved in the database.
Conclusion
Normalization performs a vital role in producing the database. It assures data integrity and the reduction of undesired data. With the powers to offer, Normalization in database also comes with certain drawbacks which should be stored in the notice. A fully normalized data may present complications in understanding the complex business logic which in turn will increase the time to develop and implement. So the designer should have a sharp understanding of normalization to use it efficiently.