Normalization in DBMS: Definition, Example, and When to Use

Normalization in DBMS is a process of organizing data in tables to reduce redundancy and improve data integrity. It involves dividing large tables into smaller related tables and defining relationships between them using keys.

How It Works

Normalization works by breaking down a large table with repeated or duplicated data into smaller tables that each focus on a single topic. Imagine you have a messy closet where clothes are all mixed up; normalization is like sorting clothes into separate drawers for shirts, pants, and socks so you can find things easily and avoid duplicates.

In databases, this means each table stores data about one thing only, and tables connect through keys. This reduces errors and saves space because you don’t store the same information multiple times. It also makes updating data easier since you only change it in one place.

Example

This example shows a simple table before and after normalization. The original table mixes customer and order details, causing repeated customer info.

sql

/* Original table: Orders */
OrderID | CustomerName | CustomerPhone | Product
1       | Alice        | 123-456       | Book
2       | Alice        | 123-456       | Pen
3       | Bob          | 789-012       | Notebook

/* After normalization: Separate tables */

/* Customers table */
CustomerID | CustomerName | CustomerPhone
1          | Alice        | 123-456
2          | Bob          | 789-012

/* Orders table */
OrderID | CustomerID | Product
1       | 1          | Book
2       | 1          | Pen
3       | 2          | Notebook

When to Use

Normalization is used when designing databases to keep data clean, consistent, and easy to maintain. It is especially helpful in systems where data changes often, like customer records, sales, or inventory.

For example, an online store uses normalization to avoid storing the same customer address multiple times for each order. This saves space and prevents mistakes when a customer updates their address.

Key Points

• Normalization reduces duplicate data by splitting tables.
• It improves data accuracy and consistency.
• Tables are linked using keys to keep related data connected.
• It makes updating and managing data easier.

Key Takeaways

Normalization organizes data into smaller, related tables to reduce duplication.

It improves data integrity by ensuring each piece of data is stored only once.

Using keys connects tables and keeps data linked correctly.

Normalization helps maintain and update data efficiently in databases.