Database normalization, Advantages of Normalization, Disadvantages of Normalization.
What is database normalization?
Database normalization is a process in which we modify the complex database into a simpler database.
What are the Advantages of normalization?
Data consistency
Data consistency means that the data is always real and it is not ambiguous.
Data becomes non-redundant
Non-redundant means that only copy original copy of data is available for each user and for every time. There are no multiple copies of the same data for different persons. So when data is changed in one file and stay in one file. Then of course data is consistent and non-redundant. Here redundant is not the same as a backup of data, both are different things.
Reduce insertion, deletion and updating anomalies
-
- Insertion anomaly is an anomaly that occurs when we want to insert data into the database but the data is not completely or correctly inserted in the target attributes. If completely inserted in the database then not correctly entered.
- Deletion anomaly is an anomaly that occurs when we want to delete data in the database but the data is not completely or correctly deleted in the target attributes.
- Update anomaly is an anomaly that occurs when we want to update data in the database but the data is not completely or correctly updated in the target attributes.
Database table compaction
-
-
- When we normalize the database, we convert the large table into a smaller table that leads to data and table compaction. Compaction means to have the least and required size.
-
Better performance
Fast queries
What are the disadvantages of normalization?
- There is a requirement for an experienced database designer.
- Difficult and expansive
- Requires detailed database design
Difference between normalization and denormalization
Here, i am sharing with you some common difference between normalization and denormalization.
| Normalization | De-normalization | |
| Focus | on clearing the database from unused data and to increase the data consistency by reducing the data redundancy. | to achieve the speedy execution of the queries. |
| Memory consumption | Uses optimized memory and that’s why faster in performance. | Can leads to wastage of memory. |
| Data integrity | Cares the data integrity | Don’t care about data integrity. |
| Number of Tables | a number of tables are deleted from the database and that’s why tables are lesser in number. | After normalization database tables are divided into smallest tables and in de-normalization, data is integrated into the same database and that’s why a number of tables for data storage increases in number. |
| Where to use | joins of those tables are not expensive. | where joins are expensive and we need to execute the frequent query on the database tables. |
| Implementation | to remove redundant data from the database and to store non-redundant and consistent data into it. | combine multiple table data into one table and the reason behind this is to solve the quick queries. |
Frequently Asked Questions (FAQ)
What are the advantages of 1NF?
First normal form helps us to remove the repeating groups.
What are the advantages of 2NF?
Second normal form helps us to remove the partial functional dependencies and focus on full functional dependencies.
What are the advantages of 3NF?
Third normal form helps us to remove the transitive dependencies.
What are the advantages of 3.5NF?
3.5NF ensures that Non-prime attribute never determines prime attribute.
What are the advantages of 4NF?
4NF ensures that there should be no more than one multi-valued dependencies.
What is de-normalization in DBMS?
De-normalization means to convert the database tables into non-normalized form