DEV Community

Cover image for Database Normalization In DBMS
Victor Alando
Victor Alando

Posted on • Updated on

Database Normalization In DBMS

What is Normalization?

Normalization is the process of organizing the data and the attributes of a database.

  • It is performed to reduce the data redundancy in a database and ensure that data is stored logically.

  • Normalization is systematic approach of decomposing table to eliminate data redundancy and undesirable characteristics like insertion, update and delete.

  • Normalization is multi-step process that puts data in tabular form and remove duplicate data from relation tables.

Example Employees Table

Id Name Address Profession
101 Mary 1245 Developer
102 David 5234 Accountant
103 Juliet 1444 Salesperson
104 Elizabeth 8745 Manager
105 Haskell 3251 Operation

In this table, we have data of office employees.


1. Insertion Anomaly
An insertion anomaly occurs in the relation database when some attributes or data items are inserted into database without existence of other attributes.

2. Updation Anomaly
Updation Anomaly occurs when the same data item is repeated with the same values are not linked to each other.

3. Deletion Anomalies

Deletion Anomalies occurs when deleting one part of the data deletes the other necessary information from the database.


Types of Normalization

  • 1NF

  • 2NF

  • 3NF

  • BCNF

  • 4NF

  • 5NF

Diagram:

Image description

1. 1NF (First Normal Form)
In 1NF relation, each table cell should contain a single value. Each record looks like unique.

Example

CouserId Course Name Framework
JAV101 Java NetBeans
SQL102 SQL MySQL, PostgreSQL
PY214 Python Flask

Here in the Framework row, we stored two frameworks of course Name MySQL, PostgreSQL so it is *multi-valued attribute. * it is not 1NF relation. We need to convert it into 1NF.

Convert it into 1NF

CourseId Course Name Framework
JAV101 Java NetBeans
SQL102 SQL MySQL
SQL102 SQL PostgreSQL
PY214 Python Flask

It's a simple method to store Framework separately in 1NF. Now this is First Normal Form. 1NF wants to store unique information in table without data repetition.

2. 2NF (Second Normal Form)

In 2NF, relation must in 1NF. In the Second Normal Form, all non-key attributes are fully functionally dependent on the primary key.

StudentID Specialization Student Age
501 Data Analyst 22
501 Data Engineer 22
502 Full Stack Developer 24
503 Web Developer 23

Top comments (1)