Quick Contact

    What is Denormalization?

    Denormalization is a database optimization process where we add redundant data in the database to get rid of the difficult join operations. This is complete with speeding up database access speed. Denormalization is done after normalization for improving the performance of the database. The data from one table is included in another table to reduce the number of joins in the query and hence helps in speeding up the performance.

    Example

    Suppose after normalization we have two tables first, Student table and second, Department table. The student has the attributes as STUDENT-ID, STUDENT-NAME, AGE, and DEPT-ID.

    STUDENT
    STUDENT-ID STUDENT-NAME AGE DEPT-ID
    121 Rahul 18 D-01
    122 Vijay 19 D-01
    123 Raman 18 D-02
    124 Rohan 20 D-03

    The Department table is related to the Student table with DEPT-ID as the foreign key in the Student table.

    DEPARTMENT
    DEPT-ID DEPT-NAME DEPT-LOC
    D-01 B.Tech Delhi
    D-02 MCA Delhi
    D-03 BCA Noida

    If we need the name of students along with the name of the DEPT-NAME, then we need to implement a join operation. The problem is that if the table is vast, we need a lot of time to implement the join operations. So, we can add the data of DEPT-NAME from the DEPARTMENT table to the STUDENT table, and this will help in reducing the time that would have been used in join operation and thus optimize the database.

    Advantages of Denormalization

    Denormalization assists in the minimization of joins and foreign keys and help in resolving aggregates. Since storing values can require to be retrieved (repeatedly), it can be possible to minimize the number of indexes. Even tables are needed to process queries.

    Disadvantages of Denormalization
    • As data redundancy is there, updation and insertion operations are cheaper and take higher time. Since we are not implementing normalization, so this will result in redundant data.
    • Data Integrity is not maintained in denormalization. As there is redundancy, so that data can be inconsistent
    Technique of Denormalization

    It is to be noted that denormalization is a method for tuning a database for a particular application. Attaching indexes to a table is a tuning technique. This technique makes it translucent to the applications and the end-users. Altering the database schema is not translucent. If the database design is changed, the application code also needs to be modified since it accesses that database.

    The following denormalization techniques are applied to alter the physical database design in an attempt to enhance performance.

    • Modifying the definitions of columns by bringing together columns or cutting columns that are existing.
    • Redefining the tables by bringing them together, reproducing complete tables or parts of the table, and dividing tables into many tables.
    • Attaching unnecessary data by copying columns or defining data summary.
    Difference between Normalization and Denormalization
    What is Denormalization?
    Normalization Denormalization
    Normalization is a technique of minimizing the insertion, deletion, and update anomalies through eliminating the redundant data. Denormalization is the reverse process of normalization, where the redundancy is added to the data to improve the performance of the specific application and data integrity.
    Normalization is the process of making a set schema to save non-redundant and consistent information. Denormalization is the process of combining the record so that it can be queried speedily.
    Normalization mainly focuses on clearing the database from unused data and on reducing the data redundancy and inconsistency. Denormalization, on the other hand, focuses on achieving the faster execution of the queries by introducing redundancy.
    Normalization maintains data integrity, i.e., any addition or deletion of data from the table will not create any mismatch in the relationship of the tables. Denormalization does not support any data integrity.
    Normalization is used in the OLTP system where the emphasis is on making the insert, delete and update anomalies faster, and storing the quality data. Denormalization is used in the OLAP system, where the importance is on making the search and analysis more quickly.
    Normalization optimizes the uses of disk spaces. Denormalization does not optimize the disk spaces.

    Enroll Yourself in Live Training: DBMS Training

    Copyright 1999- Ducat Creative, All rights reserved.