DEV Community

Cover image for Database Normalization In DBMS
Victor Alando
Victor Alando

Posted on • Edited 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

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more →

Top comments (1)

Postgres on Neon - Get the Free Plan

No credit card required. The database you love, on a serverless platform designed to help you build faster.

Get Postgres on Neon

AWS GenAI Live!

GenAI LIVE! is a dynamic live-streamed show exploring how AWS and our partners are helping organizations unlock real value with generative AI.

Tune in to the full event

DEV is partnering to bring live events to the community. Join us or dismiss this billboard if you're not interested. ❤️