Normalization in DBMS

Normalization is the process of minimizing redundancy from a relation or set of relations. Redundancy in relation may cause insertion, deletion, and update anomalies. So, it helps to minimize the redundancy in relations. Normal forms are used to eliminate or reduce redundancy in database tables.

Levels of Normalization

There are various levels of normalization. These are some of them:

• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Boyce-Codd Normal Form (BCNF)
• Fourth Normal Form (4NF)
• Fifth Normal Form (5NF)

First Normal Form

If a relation contains a composite or multi-valued attribute, it violates the first normal form, or the relation is in the first normal form if it does not contain any composite or multi-valued attribute. A relation is in first normal form if every attribute in that relation is a single-valued attribute.

A table is in 1 NF if:

• There are only Single Valued Attributes.
• Attribute Domain does not change.
• There is a unique name for every Attribute/Column.
• The order in which data is stored does not matter

Second Normal Form

• In the 2NF, relational must be in 1NF.
• In the second normal form, all non-key attributes are fully functional and dependent on the primary key

Example-1: Consider the table as follows below.

STUD_NO            COURSE_NO        COURSE_FEE
1                     C1                  1000
2                     C2                  1500
1                     C4                  2000
4                     C3                  1000
4                     C1                  1000
2                     C5                  2000

Table 1                                    Table 2
STUD_NO            COURSE_NO          COURSE_NO                COURSE_FEE     
1                 C1                  C1                       1000
2                 C2                  C2                       1500
1                 C4                  C3                       1000
4                 C3                  C4                       2000
4                 C1                  C5                       2000        
2                 C5

Note — 2NF tries to reduce the redundant data getting stored in memory. For instance, if 100 students are taking the C1 course, we don't need to store its Fee as 1000 for all the 100 records, instead once we can store it in the second table as the course fee for C1 is 1000.