Chapter 18: Database Normalization Exercises

What you'll learn in this chapter

Decompose a given unnormalized table up through third normal form on your own
Reliably apply the normal-form judgments covered in Chapter 11 across multiple cases
Recognize situations where you shouldn't normalize

Prerequisites

You should understand Chapter 11: Database Normalization. This chapter is exercise-focused, with a sample answer attached to every problem. Try working through each one on your own first, then check the answer.

Exercises

Exercise 1: Normalizing Children's Information (a Repeating Group)

The following table holds an employee's and their children's information in a single row. Decompose it up through first, second, and third normal form.

employees_with_children
+------------+---------------+------------+-------+------------+-------+------------+-------+
| employee_id| employee_name | child_name1| age1  | child_name2| age2  | child_name3| age3  |
+------------+---------------+------------+-------+------------+-------+------------+-------+
| 1001       | Taro Yamada   | Ichiro     | 8     | Jiro       | 5     | NULL       | NULL  |
| 1002       | Hanako Suzuki | Misaki     | 3     | NULL       | NULL  | NULL       | NULL  |
| 1003       | Jiro Sato     | Kenta      | 12    | Miho       | 10    | Shota      | 7     |
+------------+---------------+------------+-------+------------+-------+------------+-------+

Sample answer

Problem: "child" is a repeating group (child_name1, age1, child_name2, age2, ...) → violates first normal form.

Decompose into first normal form:

CREATE TABLE employee_children_1nf (
    employee_id    INTEGER NOT NULL,
    employee_name  VARCHAR(100) NOT NULL,
    child_name     VARCHAR(100) NOT NULL,
    child_age      INTEGER NOT NULL,
    PRIMARY KEY (employee_id, child_name)
);

Put each child on a separate row, with the primary key (employee_id, child_name).

Second normal form: the primary key is the composite (employee_id, child_name), and employee_name is determined by employee_id alone (a partial functional dependency) → a split is needed.

CREATE TABLE employees_2nf (
    id    INTEGER PRIMARY KEY,
    name  VARCHAR(100) NOT NULL
);

CREATE TABLE children_2nf (
    employee_id  INTEGER NOT NULL REFERENCES employees_2nf(id),
    name         VARCHAR(100) NOT NULL,
    age          INTEGER NOT NULL CHECK (age >= 0),
    PRIMARY KEY (employee_id, name)
);

Third normal form: there's no transitive functional dependency (neither employees_2nf nor children_2nf has a dependency on anything other than the primary key) → it already satisfies third normal form as-is.

Common mistakes:

Including "child's name" in the primary key means you can't register children who share a name (e.g., twins with the same name): in practice, make children.id SERIAL PRIMARY KEY the surrogate key, and don't put a UNIQUE constraint on (employee_id, name) (or add one depending on business requirements). This exercise adopts a composite primary key for teaching purposes
Storing "age": as touched on in Chapter 1, age changes over time. In practice, store birth_date and compute it on demand
Adding a constraint like "up to 3 children": don't add a headcount limit that isn't in the business requirements

Exercise 2: Normalizing Multiple Categories (Many-to-Many)

The following table holds the relationship between products and categories in a single row. Some products belong to multiple categories.

products_with_categories
+------------+-----------+----------------------------+
| product_id | name      | category                    |
+------------+-----------+----------------------------+
| 1          | Laptop    | Electronics,PC              |
| 2          | Coffee    | Food,Beverages               |
| 3          | Mouse     | Electronics,PC,Peripherals   |
+------------+-----------+----------------------------+

Sample answer

Problem: the category is stored as CSV (comma-separated) in a single cell → violates first normal form. Even trying to search for "Electronics" leaves you with only a poorly performing query like LIKE '%Electronics%'.

After normalizing (a junction table is needed, since it's many-to-many):

CREATE TABLE products (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(200) NOT NULL
);

CREATE TABLE categories (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE product_categories (
    product_id   INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    category_id  INTEGER NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
    PRIMARY KEY (product_id, category_id)
);

Common mistakes:

Searching the comma-separated string as-is with LIKE '%Electronics%': poor performance, and it also picks up partial matches like "Home Electronics"
Adding a category_id column to the products table (turning it into one-to-many): this can't express multiple categories, like "a mouse is Electronics + PC + Peripherals"
Not creating a categories table, and storing the string directly in product_categories.category_name: this causes inconsistent spelling of category names (e.g., "Electronics" vs. "Home Electronics"), breaking consistency

Alternative approach: you could also use PostgreSQL's text[] array type (products.categories text[]), but strictly speaking this violates first normal form. Searching and joining become complicated, so the standard junction-table approach is recommended.

Exercise 3: Normalizing a Derived Value (Something Computable)

The following table holds a value that "shouldn't be stored." Point out the problem and fix it.

orders_with_calc
+------------+-------------+------------+----------+------------+-----------+------------+
| order_id   | customer_id | product_id | quantity | unit_price | subtotal  | order_date |
+------------+-------------+------------+----------+------------+-----------+------------+
| 1          | 100         | 5          | 3        | 1500       | 4500      | 2026-01-15 |
| 2          | 101         | 8          | 2        | 800        | 1600      | 2026-01-16 |
+------------+-------------+------------+----------+------------+-----------+------------+

Sample answer

Problem: storing subtotal = quantity × unit_price, a derived attribute that can be computed.

If you update quantity but forget to update subtotal, you get an inconsistency
The extra work and risk of mistakes from computing quantity × unit_price at the application layer before inserting

After the fix: drop the subtotal column, and compute it with a SELECT when needed.

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    product_id  INTEGER NOT NULL,
    quantity    INTEGER NOT NULL CHECK (quantity > 0),
    unit_price  NUMERIC(10, 2) NOT NULL,                -- a snapshot
    order_date  DATE NOT NULL DEFAULT CURRENT_DATE
);

-- A query that retrieves the subtotal
SELECT id, quantity, unit_price, (quantity * unit_price) AS subtotal
FROM   orders;

Exceptions (cases where storing it is fine):

An aggregation table for reports: when performance requirements are clear and it can be recomputed from the source data (e.g., synced daily via a batch job)
A historical snapshot: when you want to see a past point-in-time total later, freeze the computed result and store it
A data warehouse: denormalization is standard for BI / OLAP use cases

For a business system's production tables, the safe default is to not hold derived attributes at all.

Common mistakes:

Prematurely optimizing by "storing subtotal for speed": in most business systems, computing quantity * unit_price is plenty fast. Measure before deciding
Holding subtotal as a generated (computed) column: using PostgreSQL's GENERATED ALWAYS AS (quantity * unit_price) STORED gives you a stored-yet-automatically-computed option. This is a reasonable compromise

Exercise 4: A Comprehensive Exercise on Employee Information

Decompose the following table up through first, second, and third normal form.

employee_full
+------------+---------------+---------------+-----------------+---------------------+-------------+
| employee_id| employee_name | department_id | department_name | department_location | hire_date   |
+------------+---------------+---------------+-----------------+---------------------+-------------+
| 1          | Yamada        | 10            | Sales           | Tokyo               | 2020-04-01  |
| 2          | Suzuki        | 20            | Engineering     | Osaka               | 2021-04-01  |
| 3          | Sato          | 10            | Sales           | Tokyo               | 2022-04-01  |
+------------+---------------+---------------+-----------------+---------------------+-------------+

Sample answer

First normal form ✓ (no repeating groups, each cell holds an atomic value, has a primary key)

Second normal form ✓ (since the primary key is the single column employee_id, no partial functional dependency can occur)

Third normal form ✕ — there's a transitive functional dependency: employee_id → department_id → department_name and employee_id → department_id → department_location

After decomposing:

CREATE TABLE departments (
    id        SERIAL PRIMARY KEY,
    name      VARCHAR(50) NOT NULL,
    location  VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    id             SERIAL PRIMARY KEY,
    name           VARCHAR(100) NOT NULL,
    department_id  INTEGER NOT NULL REFERENCES departments(id),
    hire_date      DATE NOT NULL
);

Move "department name" and "department location" to departments. Leave only the foreign key department_id on employees.

Common mistakes:

"Splitting the department name and location into separate tables" (over-normalization): "location" is an attribute of "department," so consolidating it into the departments table is the natural choice. A separate, independent locations table is only needed when multiple departments share the same site, or when a site has its own attributes
"Holding a transfer history on employees to handle personnel transfers": if "transfer history" is actually a requirement, the right call is to create a separate employee_assignments table. It isn't in this exercise's requirements, so don't add it (the YAGNI principle)

Exercise 5: Recognizing When You Shouldn't Normalize

Which of the following are cases where denormalization is acceptable or recommended? Answer with your reasoning.

A blog post's published_at (publish date/time), alongside separate published_year and published_month columns
An order line item's unit_price (a snapshot of the price at order time)
An e-commerce site's users.full_address column, holding the concatenation of users.postal_code + users.address_line1 + users.address_line2
The product master holding both products.category_name (category name) and products.category_id (category ID)

Sample answer

#	Normalize / denormalize	Reason
1. Publish year/month	Denormalization NG (should be removed)	Can be computed on demand with `EXTRACT(YEAR FROM published_at)` / `EXTRACT(MONTH FROM published_at)`. An index can be handled with an expression index
2. Unit-price snapshot	Denormalization OK (recommended)	Protects past order amounts from future price changes in the product master. This isn't a normalization violation — it's the concept of a snapshot
3. full_address	NG	Can be concatenated from `postal_code` / `address_line1` / `address_line2`. Redundant, and requires managing consistency on update
4. Duplicating category_name	NG (a classic denormalization anti-pattern)	Updating the category master requires updating every row on the `products` side too (the textbook "update anomaly" from Chapter 11)

Distinguishing normalization from denormalization:

A value derivable by computation (publish year/month, a total, a full address) → don't store it, compute it
A foreign-key value that changes over time (the price at order time, the address at order time) → store it as a snapshot

The decision axis is "do you want to see the current value, or the value as of that point in time?"

Common mistakes:

"Holding the category name on products because joining it is a hassle": the cost of maintaining consistency grows over time. A JOIN is usually plenty fast
"Snapshotting everything is safest": master updates don't get reflected (e.g., if a customer's name is snapshotted, it stays the old name even after the customer changes their legal name)

Summary

Here's a recap of what this chapter covered.

Confirmed the normalization theory from Chapter 11 across five hands-on exercises
Repeating groups and comma-separated CSV violate first normal form
A transitive functional dependency (employee → department → department name) is resolved by third normal form
Don't store a value derivable by computation — compute it at SELECT time
A snapshot is a deliberate denormalization that trades for "chronological accuracy"

That's the end of the comprehensive exercises. Move on to the advanced chapter (index design), or make use of the following references throughout the guide.

Next chapter

Chapter 19: Introduction to Index Design — how to place indexes that speed up searches, and how to read EXPLAIN

Related references

Glossary — a quick-reference dictionary of the technical terms that appear throughout this guide
Design Template Collection — checklists for naming conventions, normalization, and reviews

Back to the top of the guide

Introduction to Database Design — Getting Started

What you'll learn in this chapter​

Exercises​

Exercise 1: Normalizing Children's Information (a Repeating Group)​

Exercise 2: Normalizing Multiple Categories (Many-to-Many)​

Exercise 3: Normalizing a Derived Value (Something Computable)​

Exercise 4: A Comprehensive Exercise on Employee Information​

Exercise 5: Recognizing When You Shouldn't Normalize​

Summary​

What you'll learn in this chapter

Exercises

Exercise 1: Normalizing Children's Information (a Repeating Group)

Exercise 2: Normalizing Multiple Categories (Many-to-Many)

Exercise 3: Normalizing a Derived Value (Something Computable)

Exercise 4: A Comprehensive Exercise on Employee Information

Exercise 5: Recognizing When You Shouldn't Normalize

Summary