Normalization and Denormalization are the processes which are being used to optimize the performance of the database. The difference between them is explained with the help of examples.
Normalization:
Normalization is a process which is carried out to minimize/remove the anomalies in order to maintain the database’s consistency. Example of normalization is given below: –
| Company ID | Company Name | Product ID | Product Name | Product Quantity |
| 1
|
Iqra Enterprises | 101
102 |
Beauty Soap
Tooth Brush |
120
100 |
| 2 | Shahid Enterprises | 103 | Polish | 140 |
| 3 | Khalid Enterprises | 103 | Polish | 250 |
Normalization or normalisation is carried out in following forms:
- First Normal Form (1NF)
- Second Normal Form(2NF)
- Third Normal Form(3NF)
- Boyce & Codd Normal Form(BCNF)
First Normal Form:
In first normal form, the duplicate columns are removed.
| Company ID | Company Name | Product ID | Product Name | Product Quantity |
| 1 | Iqra Enterprises | 101 | Beauty Soap | 120 |
| 1 | Iqra Enterprises | 102 | Tooth Brush | 100 |
| 2 | Shahid Enterprises | 103 | Polish | 140 |
| 3 | Khalid Enterprises | 103 | Polish | 250 |
Second Normal Form:
In case of second normal form, it contains step of first normal form in addition to removal of duplicate data which is placed in a child table.
Company Table:
| Company ID | Company Name |
| 1 | Iqra Enterprises |
| 1 | Iqra Enterprises |
| 2 | Shahid Enterprises |
| 3 | Khalid Enterprises |
Product Table:
| Product ID | Product Name | Product Quantity |
| 101 | Beauty Soap | 120 |
| 102 | Tooth Brush | 100 |
| 103 | Polish | 140 |
| 103 | Polish | 250 |
Company – Product Table
| Company ID | Product ID |
| 1 | 101 |
| 1 | 102 |
| 2 | 103 |
| 3 | 103 |
Third Normal Form:
The third normal form include 2nd normal form and further steps are carried out. In this form the columns are removed which are not dependent on primary key columns.
Company Table:
| Company ID | Company Name |
| 1 | Iqra Enterprises |
| 2 | Shahid Enterprises |
| 3 | Khalid Enterprises |
Product Table:
| Product ID | Product Name | Product Quantity |
| 101 | Beauty Soap | 120 |
| 102 | Tooth Brush | 100 |
| 103 | Polish | 140 |
| 103 | Polish | 250 |
Company – Product Table
| Company ID | Product ID |
| 1 | 101 |
| 1 | 102 |
| 2 | 103 |
| 3 | 103 |
Denormalization:
De-normalization is a reverse process of Normalization. It reduces the work load and optimizes the performance. Example of de-normalization is given below: –
Employee Table:
| Employee ID | Employee Name |
| 1 | Shahid |
| 2 | Rizwan |
| 3 | Khalid |
Course Table:
| Employee ID | Course Qualified |
| 1 | MS Office |
| 2 | Web Designing |
| 3 | C++ programming |
After De-normalization,
| Employee ID | Employee Name | Course Qualified |
| 1 | Shahid | MS Office |
| 2 | Rizwan | Web Designing |
| 3 | Khalid | C++ programming |
IMPLEMENT THE DE-NORMALIZATION IN RDBMS:
Denormalization is a reverse process of normalization. It reduces the work load and optimizes the performance in order to access the database. Denormalization is essential where repeating tables exist which requires to be processed in a single table.
Relational Database Management System (RDBMS) is a Database Management System (DBMS) that implements a relational data model. RDBMS accelerate the performance in accessing the required data.
In fact, RDBMS product requires de-normalized data structures to resolve the performance constraints. A fully normalized database has excessive table join operations which decreases the reading performance of the database, so, this issue will be resolved by De-normalization. In De-normalization, table relations must be merged for better efficiency and to produce a result with a single query instead of a certain number of queries.