Data modification anomalies can be categorized into three types:

○​ Insertion Anomaly: Insertion Anomaly refers to when one cannot insert a new
tuple into a relationship due to lack of data.
○​ Deletion Anomaly: The delete anomaly refers to the situation where the deletion
of data results in the unintended loss of some other important data.
○​ Updation Anomaly: The update anomaly is when an update of a single data value
requires multiple rows of data to be updated.

Why Is Normalization Important?

Let's look at why proper normalization matters for real-world applications.

Data redundancy

Data integrity
Data anomalies

Performance and scalability

Security

Step-by-Step Normalization Process

First normal form (1NF)

The first normal form eliminates repeating groups and makes sure every column contains atomic
values. Learn more about it in the First Normal Form (1NF) in-depth guide.

Atomic values mean each cell holds exactly one piece of information - no lists, no
comma-separated values, no multiple data points crammed into a single field. This is the
foundation that makes everything else possible.

Here's what violates 1NF:

CREATE TABLE orders_bad (

order_id INT,
customer_name VARCHAR(100),
products VARCHAR(500),
 quantities VARCHAR(50)
);

The products and quantities columns contain multiple values separated by commas.
You can't easily query "all orders containing laptops" or calculate total quantities without string
parsing.

To convert this to 1NF, split the repeating groups into separate rows:

-- First normal form (1NF)

CREATE TABLE orders_1nf (
order_id INT,
customer_name VARCHAR(100),
product VARCHAR(100),
quantity INT
);

Now, each cell contains exactly one value. You can query, sort, and aggregate the data using
standard SQL operations.

Second normal form (2NF)

Second normal form removes partial dependencies - when non-key columns depend on only part
of a composite primary key.
There's more to the Second Normal Form (2NF) than meets the eye. Learn more in our in-depth
guide.

A table is in 2NF if it's in 1NF and every non-key column depends on the entire primary key, not
just part of it.

Our 1NF table has a problem. If we use order_id and product as a composite primary key,
customer_name depends only on order_id, not on the product. This creates redundancy - the
customer name repeats for every product in an order.

-- Still has partial dependencies

-- customer_name depends only on order_id, not on (order_id, product)
CREATE TABLE orders_1nf (
order_id INT,
customer_name VARCHAR(100), -- Partial dependency!
product VARCHAR(100),
quantity INT,
PRIMARY KEY (order_id, product)
);
To achieve 2NF, split the table based on dependencies:
-- Orders table (customer info depends on order_id)
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_name VARCHAR(100)
);

-- Order items table (quantity depends on both order_id and product)

CREATE TABLE order_items (
order_id INT,
product VARCHAR(100),
quantity INT,
PRIMARY KEY (order_id, product),
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

Now customer_name appears only once per order, eliminating redundancy. Each table has
columns that depend on the entire primary key.

Third normal form (3NF)

The third normal form eliminates transitive dependencies, which occur when non-key columns
depend on other non-key columns instead of the primary key. Dive into the Third Normal
Form (3NF) beyond the basics.

A transitive dependency exists when “Column A” determines “Column B”, and “Column B”
determines “Column C”, creating an indirect dependency from A to C.

Let's expand our orders table with customer address information:

-- Has transitive dependencies

CREATE TABLE orders_2nf (
order_id INT PRIMARY KEY,
customer_name VARCHAR(100),
customer_city VARCHAR(50),
customer_state VARCHAR(50),
customer_zip VARCHAR(10)
);

Here's the problem: customer_name → customer_city, and customer_city → customer_state.

The state depends on the city, not directly on the order. This creates redundancy - every order
from the same city repeats the state information.

To achieve 3NF, remove transitive dependencies by creating separate tables:

-- Customers table (removes transitive dependencies)

CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
city_id INT,
FOREIGN KEY (city_id) REFERENCES cities(city_id)
);

-- Cities table
CREATE TABLE cities (
city_id INT PRIMARY KEY,
city_name VARCHAR(50),
state VARCHAR(50),
zip VARCHAR(10)
);

-- Orders table (now references customer, not customer details)

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Now geographic information lives in one place. If a city changes states (rare but possible), you
update one row instead of hunting through every order from that city.

Each normal form solves specific redundancy problems while maintaining the ability to
reconstruct your original data through joins.

Boyce-Codd normal form (BCNF)

BCNF fixes a subtle problem that 3NF misses: when a table has overlapping candidate keys.

3NF allows non-key columns to depend on candidate keys, but BCNF is more strict. In BCNF,
every determinant (a column that determines another column) must be a superkey - either a
primary or a candidate key.

Here's where 3NF breaks down:

-- Table in 3NF but violates BCNF

CREATE TABLE course_instructors (
student_id INT,
course VARCHAR(50),
instructor VARCHAR(50),
PRIMARY KEY (student_id, course)
);

The business rules are:

●​ Each student can take multiple courses

●​ Each course has exactly one instructor
●​ Each instructor teaches exactly one course

This creates course → instructor and instructor → course dependencies. Both (student_id,
course) and (student_id, instructor) are candidate keys, but course and instructor determine each
other without being superkeys themselves.
The problem shows up when you try to add a new instructor without students. You can't insert
"Professor Smith teaches Database Design" without also adding a student to that course.

To achieve BCNF, decompose based on the problematic dependency:

-- BCNF solution
CREATE TABLE course_assignments (
course VARCHAR(50) PRIMARY KEY,
instructor VARCHAR(50) UNIQUE
);

CREATE TABLE student_enrollments (

student_id INT,
course VARCHAR(50),
PRIMARY KEY (student_id, course),
FOREIGN KEY (course) REFERENCES course_assignments(course)
);

Now you can add instructors without students, and the database structure matches the business
rules exactly.

Fourth normal form (4NF)

4NF eliminates multi-valued dependencies - when one column determines multiple independent
sets of values.

A multi-valued dependency exists when “Column A” determines multiple values in “Column B”,
and those values are independent of other columns in the table.

Consider this table tracking student skills and hobbies:

-- Violates 4NF due to multi-valued dependencies

CREATE TABLE student_info (
student_id INT,
skill VARCHAR(50),
hobby VARCHAR(50),
PRIMARY KEY (student_id, skill, hobby)
);
The problem: student_id determines both skills and hobbies, but skills and hobbies are
independent of each other. When student 1 learns a new skill, you need to create rows for every
hobby combination. When they pick up a new hobby, you need rows for every skill combination.

This creates explosive redundancy as the number of skills and hobbies grows.

To achieve 4NF, separate the independent multi-valued dependencies:

-- 4NF solution
CREATE TABLE student_skills (
student_id INT,
skill VARCHAR(50),
PRIMARY KEY (student_id, skill)
);

CREATE TABLE student_hobbies (

student_id INT,
hobby VARCHAR(50),
PRIMARY KEY (student_id, hobby)
);

Now you can add skills and hobbies independently without creating Cartesian product
explosions.

Fifth and sixth normal forms (5NF and 6NF)

5NF (Project-Join Normal Form) eliminates join dependencies: complex relationships that
require three or more tables to reconstruct data without loss.
A join dependency exists when you can't reconstruct the original table by joining two
decomposed tables, but you can reconstruct it by joining three or more tables.

Consider suppliers, parts, and projects with this rule: "A supplier can supply a part to a project
only if the supplier supplies that part AND works on that project."

-- Original table with join dependency

CREATE TABLE supplier_part_project (
supplier_id INT,
part_id INT,
project_id INT,
PRIMARY KEY (supplier_id, part_id, project_id)
);

To achieve 5NF, decompose into three binary relationships:

-- 5NF decomposition
CREATE TABLE supplier_parts (supplier_id INT, part_id INT);
CREATE TABLE supplier_projects (supplier_id INT, project_id INT);
CREATE TABLE project_parts (project_id INT, part_id INT)

6NF takes normalization to the extreme by putting each attribute in its own table with temporal
keys.

6NF is designed for data warehouses and temporal databases where you need to track how every
attribute changes over time independently.

-- 6NF example for temporal data

CREATE TABLE customer_names (
customer_id INT,
name VARCHAR(100),
valid_from DATE,
valid_to DATE
);

CREATE TABLE customer_addresses (

customer_id INT,
address VARCHAR(200),
valid_from DATE,
valid_to DATE
);

This allows you to track when each attribute changed without affecting others, but it makes
queries complex and is rarely used outside specialized temporal database systems.

Most applications stop at 3NF or BCNF. These advanced forms solve specific edge cases but add
complexity that isn't worth it for typical business applications.

Advantages of normalization

●​ Reduced redundancy means your database stores each fact exactly once, cutting storage
costs and eliminating sync issues. When customer data lives in a single table instead of
scattered across dozens, updating an address becomes a one-row operation. No hunting
through related tables, worrying about missed updates, or inconsistent data showing up in
reports.
●​ Data consistency becomes automatic when there's only one source of truth. Your
application can't display conflicting information because conflicting information can't
exist in the first place.
●​ Updates become fast and reliable because you're changing one row instead of dozens.
Insert a new customer once, reference them everywhere else with foreign keys. Delete an
order without worrying about orphaned data in related tables.
●​ Security controls get simpler when sensitive data has clear boundaries. Customer
payment information lives in a specific table with specific access controls. You don't need
to worry about credit card numbers hiding in unexpected places.
●​ Scalability improves because normalized tables are smaller and more focused. Indexes
work better on smaller tables. You can partition data logically without duplicating
information across shards.
●​ Team collaboration becomes smoother when everyone understands where the data
lives. New developers can navigate the schema faster. Database administrators can
optimize performance with confidence. Business analysts can write reliable queries
without second-guessing data quality.
●​ Backup and recovery strategies get cleaner because related data doesn't span multiple
disconnected tables. Foreign key constraints ensure you can't restore partial data that
breaks referential integrity.

Disadvantages and challenges of normalization

●​ Query complexity increases when simple questions require multiple joins to answer.
Want to see a customer's order history with product names? In a denormalized table,
that's one query. In a normalized database, you're joining the customers, orders, order
 items, and products tables. More joins mean more opportunities for mistakes and slower
query execution.
●​ Performance can suffer when you're constantly joining tables instead of reading from
single, wide tables. Each join adds overhead, especially when your database needs to
access data from different storage locations.
●​ Development time increases because developers need to understand table relationships
before writing queries. What used to be a simple SELECT becomes a multi-table JOIN
with proper foreign key handling.
●​ Over-normalization creates artificial complexity when you split data that naturally
belongs together. If you normalize a person's full name into separate first, middle, and
last name tables, you've probably gone too far.

Here's a real example: An e-commerce site normalized product categories into six levels of
hierarchy. Simple queries like "show all electronics" became seven-table joins that took seconds
instead of milliseconds. The theoretical purity wasn't worth the practical pain.

●​ Read-heavy applications suffer when normalization optimizes for writes, but most
operations are reads. Social media feeds, analytics dashboards, and reporting systems
often perform better with some strategic denormalization.
●​ Maintenance overhead grows as the number of tables increases. More tables mean more
indexes to maintain, more foreign key constraints to validate, and more complex backup
procedures.

The key is finding the right balance for your specific use case - normalize enough to prevent data
integrity problems, but not so much that you sacrifice performance and developer productivity.
SQL Normalization: A Beginner's Guide to 1NF, 2NF, 3NF, and BCNF – Dataquest