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