 Home  Coding Ground   Teach with us     

  Login

Normal Forms in DBMS

DBMS Database SQL

Normalization is a process of organizing data in a database to reduce redundancy and improve data consistency. Primary
keys are really important in organizing information in a database. They help to make sure that every row in a table has a
unique identification so that nothing gets mixed up or lost.

In this article, we will discuss the different normal forms of database normalization.
DBMS Tutorial
Normalization
DBMS - Home
Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update
anomaly & deletion anomaly.
DBMS - Overview

DBMS - Architecture

DBMS - Data Models

DBMS - Data Schemas

DBMS - Data Independence

Entity Relationship Model

DBMS - ER Model Basic Concepts

DBMS - ER Diagram Representation

DBMS - Generalization, Aggregation

Relational Model

DBMS - Codd's Rules

DBMS - Relational Data Model

DBMS - Relational Algebra

DBMS - ER to Relational Model

DBMS- SQL Overview Normalization is the process of organizing data in a database to minimize redundancy and dependency. In database
design, there are different normal forms based on the primary keys of a table. These include −
Relational Database Design

DBMS - Database Normalization

DBMS - Database Joins

Storage and File Structure

DBMS - Storage System

DBMS - File Structure

Indexing and Hashing

First Normal Form (1NF)
DBMS - Indexing
1NF requires that each column in a table contains atomic values and that each row is uniquely identified. This means that
a table cannot have repeating groups or arrays as columns, and each row must have a unique primary key.
DBMS - Hashing
Example
 A table is in 1NF if each column contains atomic values and each row is uniquely identified. For example, a table that lists
Transaction And Concurrency
customers and their phone numbers −

DBMS - Transaction Customer ID Name Phone Numbers

DBMS - Concurrency Control 1 John 555-1234, 555-5678

DBMS - Deadlock 2 Jane 555-9876

3 Michael 555-5555
Backup and Recovery

DBMS - Data Backup This violates 1NF because the Phone Numbers column contains repeating groups.

To normalize this table to 1NF, we can split the Phone Numbers column into separate rows and add a separate primary
DBMS - Data Recovery key column −

DBMS Useful Resources Customer ID Name Phone Number

1 John 555-1234
DBMS - Quick Guide

1 John 555-5678
DBMS - Useful Resources

2 Jane 555-9876
DBMS - Discussion

3 Michael 555-5555

Second Normal Form (2NF)

2NF builds on 1NF by requiring that each non-primary key column in a table is fully functionally dependent on the
primary key. This means that a table should not have partial dependencies, where a non-primary key column depends on
only part of the primary key.

Example

A table is in 2NF if each non-primary key column is fully functionally dependent on the primary key. For example, a table
that lists orders and their line items:

Order ID Customer ID Customer Name Item ID Item Name Quantity

1 1 John 1 Shirt 2

1 1 John 2 Pants 1

2 2 Jane 1 Shirt 1

2 2 Jane 3 Hat 3

This violates 2NF because the Customer Name column depends on only part of the primary key (Customer ID). To
normalize this table to 2NF, we can split it into two tables −

Order ID Customer ID Item ID Quantity

1 1 1 2

1 1 2 1

2 2 1 1

2 2 3 3

Customer ID Customer Name

1 John

2 Jane

Third Normal Form (3NF)

3NF builds on 2NF by requiring that each non-primary key column in a table is not transitively dependent on the primary
key. This means that a table should not have transitive dependencies, where a non-primary key column depends on
another non-primary key column.

Example
To explain 3NF further, let's consider an example of a table that lists customer orders −

Order ID Customer ID Customer Name Customer City Order Date Order Total

1 100 John Smith New York 2022-01-01 100

2 101 Jane Doe Los Angeles 2022-01-02 200

3 102 Bob Johnson San Francisco 2022-01-03 300

In this example, the non-primary key column "Customer City" is transitively dependent on the primary key. That is, it
depends on "Customer ID", which is not part of the primary key, instead of depending directly on the primary key "Order
ID". To bring this table to 3NF, we can split it into two tables −

Table 1: Customers

Customer ID Customer Name Customer City

100 John Smith New York

101 Jane Doe Los Angeles

102 Bob Johnson San Francisco

Table 2: Orders

Order ID Customer ID Order Date Order Total

1 100 2022-01-01 100

2 101 2022-01-02 200

3 102 2022-01-03 300

Now, the "Customer City" column is no longer transitively dependent on the primary key and is instead in a separate
table that has a direct relationship with the primary key. This makes the table 3NF-compliant.

Boyce-Codd Normal Form (BCNF)

BCNF is a stricter form of 3NF that applies to tables with more than one candidate key. BCNF requires that each non-
trivial dependency in a table is a dependency on a candidate key. This means that a table should not have non-trivial
dependencies, where a non-primary key column depends on another non-primary key column. BCNF ensures that each
table in a database is a separate entity and eliminates redundancies.

Example

A table is in BCNF if each determinant is a candidate key. In other words, every non-trivial functional dependency in the
table must be on a candidate key. For example, consider a table that lists information about books and their authors −

Table: Books

Book ID Title Author ID Author Name Author Nationality

1 Crime and Punishment 100 Fyodor Dostoevsky Russian

2 The Great Gatsby 101 F. Scott Fitzgerald American

3 Pride and Prejudice 102 Jane Austen British

In this example, the functional dependency between "Author ID" and "Author Name" violates BCNF because it is not on a
candidate key. To bring this table to BCNF, we can split it into two tables −

Table 1: Authors

Author ID Author Name Author Nationality

101 Fyodor Dostoevsky Russian

101 F. Scott Fitzgerald American

102 Jane Austen British

Table 2: Books

Book ID Title Author ID

 1 Crime and Punishment 100

2 The Great Gatsby 101

3 Pride and Prejudice 102

Now, the "Author Name" and "Author Nationality" columns are not transitively dependent on the primary key, and the
table is in BCNF.

Fourth Normal Form (4NF)

4NF builds on BCNF by requiring that a table should not have multi-valued dependencies. A multi-valued dependency
occurs when a non-primary key column depends on a combination of other non-primary key columns. For example, a
table that lists customer orders with a primary key of order ID and non-primary key columns for customer ID and order
items violates 4NF because order items depend on both order ID and customer ID.

For example, a table that lists orders and their products, with columns for order ID, product ID, and product details,
violates 4NF because the product details depend on the combination of order ID and product ID.

Example

Consider the following table of orders and products

Order ID Product ID Product Name Product Description

1 100 Widget Red Widget

1 200 Widget Blue Widget

2 100 Widget Red Widget

2 300 Thing Green Thing

3 200 Widget Blue Widget

3 300 Thing Green Thing

In this table, the product name and description depend on both the order ID and product ID, creating a multi-valued
dependency. To bring the table into 4NF, we can split it into three tables −

Order ID Product ID

1 100

1 200

2 100

2 300

3 200

3 300

Product ID Product Name

100 Widget

200 Widget

300 Thing

Product ID Product Description

100 Red Widget

200 Blue Widget

300 Green Thing

Advantages and Disadvantages of Normalization

Advantages of Normalization
Reduced Data Redundancy

Improved Data Consistency

Simplified Database Maintenance

Improved Query Performance

Disadvantages of Normalization
Increased Complexity

Decreased Read Performance

Increased Write Performance

Increased Storage Space

Over-Normalization

Conclusion
So, we have explained the concept of normalization in database management systems (DBMS) and its importance in data
management. We covered different normal forms of database normalization, including 1NF, 2NF, 3NF, BCNF, and 4NF.
We explained how normalization helps eliminate data redundancy, insertion, update, and deletion anomalies. We have
also provided examples of tables in different normal forms and how to normalize them to the desired normal form. We
have written about the importance of normalization in ensuring data consistency and accuracy in DBMS.

Mithlesh Upadhyay

Updated on 18-May-2023 18:30:51  8K+ Views  Print Article    

 Related Articles

Explain how normal forms can be transformed from lower to higher(DBMS)

Normal Forms Based on Primary Keys

Create an example table that is in its first normal form(DBMS)

What is query optimization and explain its two forms(DBMS)?

Which normal form is the highest that satisfies the functional dependencies(DBMS)?

Deadlocks in DBMS

Cascadeless in DBMS

Checkpoints in DBMS

Constraints in DBMS

Cursors in DBMS

Multivalued dependency in DBMS

Join dependency in DBMS

Aggregate Functions in DBMS

Data Independence in DBMS

Cursors in Oracle DBMS

 Previous Page Next Page 

Advertisements
Annual Membership Training for a Team
Enjoy unlimited access on 5500+ Hand Affordable solution to train a team and
Picked Quality Video Courses make them project ready.

 Subscribe Now  Request a Demo

 About us  Refund Policy  Terms of use  Privacy Policy  FAQ's  Contact

Copyright © Tutorials Point (India) Private Limited. All Rights Reserved.