Normalization in DBMS (Database Management Systems) is the systematic process of
organizing data in a database to:
Reduce redundancy
Avoid anomalies
Improve consistency
Ensure data integrity
Formal Definition
Normalization is the process of structuring a relational database in a way that minimizes data
redundancy and eliminates undesirable characteristics such as insertion, update, and deletion
anomalies.
Why Normalization Is Needed
Without normalization, a database may have:
Redundant (duplicate) data
Update Anomalies – Need to update the same data in many places
Insert Anomalies – Cannot add data because other data is missing
Delete Anomalies – Deleting one fact accidentally deletes others
Example of Problem Without Normalization
Student Course Instructor
John DBMS Dr. Rao
John AI Dr. Kumar
Mary DBMS Dr. Rao
Problems:
• "Dr. Rao" is repeated → redundancy
• If Dr. Rao leaves and you delete a DBMS row → lose instructor info → deletion anomaly
• If adding a new instructor without a student → cannot insert → insertion anomaly
How Normalization Fixes This
We split the table into smaller logical tables:
STUDENT(student_id, student_name)
COURSE(course_id, course_name)
INSTRUCTOR(instructor_id, instructor_name)
STUDENT_COURSE(student_id, course_id, instructor_id)
Now:
• No repeated data
• Easy to update
• No anomalies
Levels of Normalization
Normalization uses Normal Forms:
1NF — First Normal Form
• Atomic values
• No repeating groups
2NF — Second Normal Form
• No partial dependency (in composite keys)
3NF — Third Normal Form
• No transitive dependency
BCNF
• Every determinant must be a candidate key
4NF
• No multi-valued dependencies
5NF
• No join dependencies
Summary
Normalization = organizing data properly to make a clean, efficient, reliable database.
It:
✔ Reduces redundancy
✔ Avoids anomalies
✔ Makes the database scalable and easy to maintain
ILLUSTRATIVE EXAMPLES
Below are illustrative SQL examples using the Sakila database to explain how each normal form
works.
Important: Normalization is about table design, not specific queries.
But we can use SELECT queries to demonstrate violations and show how the Sakila schema
follows the rules.
1. FIRST NORMAL FORM (1NF)
Rule: No repeating groups, no multi-valued attributes. Every field should contain atomic values.
Example: Detecting non-atomic values (if they existed)
(Sakila is already in 1NF, but inventory > inventory_id might appear multiple copies)
SELECT film_id, COUNT(*) AS copies
FROM inventory
GROUP BY film_id
HAVING COUNT(*) > 1;
This shows multiple physical copies of the same film but stored atomically across rows → valid
1NF.
Example Showing Atomic Fields
SELECT film_id, title, rental_duration, rental_rate
FROM film;
Each column has atomic values → 1NF satisfied.
2. SECOND NORMAL FORM (2NF)
Rule: No partial dependency on a composite primary key.
Example table: film_category → PK = (film_id, category_id)
Query to Look for Attributes Dependent Only on One Key
(There are no such extra attributes, but query is illustrative.)
SELECT fc.film_id, [Link], fc.category_id
FROM film_category fc
JOIN film f ON fc.film_id = f.film_id;
This shows:
If title existed inside film_category, that would violate 2NF.
Sakila avoids this: title correctly stays in film table.
Example of 2NF Compliance
DESCRIBE film_category;
You will see the table contains only the two key fields → 2NF satisfied.
3. THIRD NORMAL FORM (3NF)
Rule: No transitive dependencies (non-key → non-key → key).
Example: Detecting Potential Transitive Dependencies
(Sakila is clean, but an example check)
SELECT c.customer_id, a.address_id, a.city_id, [Link],
[Link]
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
JOIN country co ON ci.country_id = co.country_id;
If country were stored directly inside customer, that would be a 3NF violation.
Example of 3NF Compliance
“Customer’s country” comes indirectly via address → city → country.
No redundant fields → 3NF satisfied.
BOYCE-CODD NORMAL FORM (BCNF)
Rule: Every determinant must be a candidate key.
Query: Check Functional Dependency film_id → title
SELECT film_id, COUNT(DISTINCT title)
FROM film
GROUP BY film_id
HAVING COUNT(DISTINCT title) > 1;
If this returned rows, it would violate BCNF.
Since film_id uniquely determines title → BCNF satisfied.
Query: Check country → country_id
SELECT country, COUNT(DISTINCT country_id)
FROM country
GROUP BY country
HAVING COUNT(DISTINCT country_id) > 1;
No duplicates → BCNF holds.
FOURTH NORMAL FORM (4NF)
Rule: No multi-valued dependencies (table shouldn’t store two independent 1-to-many
attributes for same key).
Example good design:
film_actor stores a single relationship: film ↔ actor.
Another: film_category stores film ↔ category.
These are two separate tables, so Sakila avoids 4NF violations.
Query to demonstrate multi-valued independence
SELECT fa.film_id, COUNT(DISTINCT actor_id) AS actors,
fc.film_id, COUNT(DISTINCT category_id) AS categories
FROM film_actor fa
JOIN film_category fc ON fa.film_id = fc.film_id
GROUP BY fa.film_id;
If both actors and categories were stored in one table, it would violate 4NF.
Sakila splits them → 4NF satisfied.
FIFTH NORMAL FORM (5NF)
Rule: No join dependencies — a table shouldn’t depend on complex joins of other tables.
Sakila handles complex relationships correctly:
• film ↔ actor (film_actor)
• film ↔ category (film_category)
If both were in one table like:
film_id | actor_id | category_id
That would require a join-dependency and violate 5NF.
Query to show independent relationships
SELECT f.film_id, COUNT(DISTINCT fa.actor_id) actors,
COUNT(DISTINCT fc.category_id) categories
FROM film f
LEFT JOIN film_actor fa ON f.film_id = fa.film_id
LEFT JOIN film_category fc ON f.film_id = fc.film_id
GROUP BY f.film_id;
Sakila uses separate relationship tables, satisfying 5NF.
Summary Table: Queries and What They Show
Normal Form Purpose Sakila Query Demonstrating It
1NF Atomic values Check atomic columns in film
2NF No partial dependency Inspect film_category composite key
3NF No transitive dependency customer → address → city → country
BCNF Every determinant is key Check uniqueness of title per film_id
4NF No independent multi-valued attributes separate film_actor & film_category
5NF No join dependency show independent relationships
Great! Here is Option E — the complete study package:
Printable Notes
20 Practice SQL Questions
ER Diagram Explanation
Denormalized → Normalized SQL Examples
Everything is based on MySQL Sakila Database.
PART A — PRINTABLE NOTES (1NF → 5NF with Sakila Examples)
1NF — First Normal Form (Atomicity)
A table is in 1NF when:
No repeating groups
No multi-valued attributes
Every cell contains atomic values
✔ Sakila Example
film table:
title, rental_rate, length are atomic
No lists inside a single field
Demonstration Query
SELECT film_id, title, rental_duration, rental_rate
FROM film;
2NF — Second Normal Form (No Partial Dependencies)
A table is in 2NF when:
• It is in 1NF
• No non-key attribute depends on part of a composite key
Sakila Example
film_category has composite PK (film_id, category_id)
→ No extra columns → No partial dependency.
Demonstration Query
DESCRIBE film_category;
3NF — Third Normal Form (No Transitive Dependencies)
A table is in 3NF when:
It is in 2NF
No non-key attribute depends on another non-key attribute
Sakila Example
customer does not store city or country.
Instead:
customer → address → city → country
avoids redundancy.
Demonstration Query
SELECT c.customer_id, [Link], [Link], [Link]
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
JOIN country co ON ci.country_id = co.country_id;
BCNF — Boyce-Codd Normal Form
A stronger 3NF:
Every determinant must be a candidate key.
Sakila Example
In film table:
film_id → title, description, length, rating...
Film ID uniquely determines all fields → BCNF satisfied.
Demonstration Query
SELECT film_id, COUNT(DISTINCT title)
FROM film
GROUP BY film_id
HAVING COUNT(DISTINCT title) > 1;
4NF — Fourth Normal Form (No Multi-valued Dependencies)
A table is in 4NF when:
It is in BCNF
It has no independent multi-valued attributes
Sakila Example
Film has multiple:
Actors (film_actor)
Categories (film_category)
These are stored in separate tables, avoiding 4NF violation.
Demonstration Query
SELECT f.film_id,
COUNT(DISTINCT fa.actor_id) AS actors,
COUNT(DISTINCT fc.category_id) AS categories
FROM film f
LEFT JOIN film_actor fa ON f.film_id = fa.film_id
LEFT JOIN film_category fc ON f.film_id = fc.film_id
GROUP BY f.film_id;
5NF — Fifth Normal Form (Eliminate Join Dependencies)
A table is in 5NF when:
No table can be decomposed further without losing information
All join dependencies are implied by candidate keys
Sakila Example
Two separate tables exist:
film_actor
film_category
They should NOT be merged.
Demonstration Query
SELECT [Link], COUNT(DISTINCT fa.actor_id), COUNT(DISTINCT
fc.category_id)
FROM film f
LEFT JOIN film_actor fa ON f.film_id = fa.film_id
LEFT JOIN film_category fc ON f.film_id = fc.film_id
GROUP BY f.film_id;
PART B — 20 SQL Practice Questions (with answers)
1NF QUESTIONS
List all films with non-null length values.
SELECT film_id, title, length FROM film WHERE length IS NOT NULL;
Check if any field holds more than one value.
SELECT film_id, title FROM film WHERE title LIKE '%,%';
2NF QUESTIONS
Demonstrate composite key usage in film_actor.
DESCRIBE film_actor;
Show all films belonging to more than one category.
SELECT film_id, COUNT(*)
FROM film_category
GROUP BY film_id
HAVING COUNT(*) > 1;
3NF QUESTIONS
Prove city depends on address, not customer.
SELECT customer_id, city FROM customer
JOIN address USING (address_id)
JOIN city USING (city_id);
List customers by country—without redundancy.
SELECT c.customer_id, [Link]
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
JOIN country co ON ci.country_id = co.country_id;
BCNF QUESTIONS
Check if country uniquely identifies country_id.
SELECT country, COUNT(DISTINCT country_id)
FROM country
GROUP BY country;
Verify unique film titles per film_id.
SELECT film_id, COUNT(DISTINCT title)
FROM film
GROUP BY film_id;
4NF QUESTIONS
Count actors and categories per film.
SELECT film_id,
COUNT(DISTINCT actor_id) AS actors,
COUNT(DISTINCT category_id) AS categories
FROM film
LEFT JOIN film_actor USING (film_id)
LEFT JOIN film_category USING (film_id)
GROUP BY film_id;
Identify films with both >5 actors and >3 categories.
SELECT film_id
FROM film_actor
GROUP BY film_id
HAVING COUNT(actor_id) > 5
INTERSECT
SELECT film_id
FROM film_category
GROUP BY film_id
HAVING COUNT(category_id) > 3;
5NF QUESTIONS
Identify if recomposing actor + category lists produces all combinations.
SELECT fa.film_id, fa.actor_id, fc.category_id
FROM film_actor fa
JOIN film_category fc ON fa.film_id = fc.film_id;
Check if merging film_actor & film_category introduces redundancies.
SELECT [Link], COUNT(DISTINCT actor_id), COUNT(DISTINCT
category_id)
FROM film f
LEFT JOIN film_actor fa USING (film_id)
LEFT JOIN film_category fc USING (film_id)
GROUP BY film_id;
More Practice (13–20)
List the number of distinct ratings.
SELECT rating, COUNT(*) FROM film GROUP BY rating;
Show customers living in the same city.
SELECT city_id, COUNT(*) FROM address GROUP BY city_id HAVING
COUNT(*) > 1;
Show films rented multiple times by same customer.
SELECT customer_id, film_id, COUNT(*) FROM rental
JOIN inventory USING (inventory_id)
JOIN film USING (film_id)
GROUP BY customer_id, film_id HAVING COUNT(*) > 1;
Detect redundant storage in payment table.
DESCRIBE payment;
Test if staff → store is functional dependency.
SELECT staff_id, COUNT(DISTINCT store_id) FROM staff GROUP BY
staff_id;
Find countries having multiple cities.
SELECT country_id, COUNT(*) FROM city GROUP BY country_id;
Show all actor–film combinations (join dependency test).
SELECT fa.actor_id, fa.film_id FROM film_actor fa;
Identify films with no multi-valued dependencies.
SELECT film_id FROM film
WHERE film_id NOT IN (SELECT film_id FROM film_actor)
OR film_id NOT IN (SELECT film_id FROM film_category);
PART C — ER DIAGRAM EXPLANATION (TEXT-BASED)
FILM ─────< FILM_ACTOR >──── ACTOR
└──────< FILM_CATEGORY >──── CATEGORY
CUSTOMER ────> ADDRESS ────> CITY ────> COUNTRY
STORE ────> ADDRESS
RENTAL ───> INVENTORY ───> FILM
Normalization Highlights
1NF: No repeating groups in any table.
2NF: Relationship tables have only composite keys.
3NF: Customer does not store city/country directly.
BCNF: All identifiers (film_id, actor_id) uniquely determine attributes.
4NF: Actor list and category list are split.
5NF: Relationships decomposed into minimal join tables.
PART D — DENORMALIZED → NORMALIZED EXAMPLE
Denormalized Table (Bad Design)
film_info(
film_id,
title,
actor_list, -- "Tom Hanks, Brad Pitt"
category_list, -- "Drama, Action"
Violates: 1NF, 4NF, 5NF.
Step 1 — Convert to 1NF
Split actor_list and category_list into separate rows.
CREATE TABLE film_actor_nf1 AS
SELECT film_id, TRIM(SUBSTRING_INDEX(actor, ',', 1)) AS actor
FROM (
SELECT film_id, title,
SUBSTRING_INDEX(actor_list, ',', 1) AS actor
FROM film_info
) AS t;
(Simplified example — real process requires SPLIT_STRING logic)
Step 2 — 2NF / 3NF / BCNF
Move actors, categories to their own lookup tables.
CREATE TABLE actor(
actor_id INT AUTO_INCREMENT PRIMARY KEY,
actor_name VARCHAR(50)
);
CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);
Step 3 — 4NF / 5NF
• Create proper relationship tables.
CREATE TABLE film_actor(
film_id INT,
actor_id INT,
PRIMARY KEY (film_id, actor_id)
);
CREATE TABLE film_category(
film_id INT,
category_id INT,
PRIMARY KEY (film_id, category_id)
);
Now:
• No multi-valued dependencies
• No join dependencies
• Fully normalized to 5NF