Database Normalization

Normalization is the process of organizing tables to reduce redundancy and prevent data anomalies by decomposing tables according to functional dependencies.

Concept

Normalization is the process of organizing tables to reduce redundancy and prevent data anomalies by decomposing tables according to functional dependencies. Each "normal form" (NF) is a set of rules; higher forms build on lower ones.

A functional dependency A → B means: if you know A, you know exactly one B. Example: customer_id → customer_name. Normalization is about making sure every non-key fact depends on the key, the whole key, and nothing but the key.

Why It Matters

Redundant data causes:

Wasted storage. Update anomaly: the same fact stored in many rows must be updated everywhere; missing one creates inconsistency.

Insert anomaly: you cannot record one fact without inventing another (cannot add a new course until a student enrolls).

Delete anomaly: deleting one fact destroys an unrelated fact (deleting the last enrollment deletes the course's instructor information).

Syntax

Normalization is a design process rather than SQL syntax. The result is expressed through CREATE TABLE statements with primary and foreign keys.

Example

23.1 The unnormalized starting point A spreadsheet-style table of course enrollments:

Databases, Networks Dr. Rao, Dr. Sen Lists in cells (phone_numbers, course_codes) — cannot query or constrain individual values.

"Databases" and "Dr. Rao, B-201" are repeated for every enrolled student.

If Dr. Rao moves office, many rows must change (update anomaly).

A new course without students cannot be stored (insert anomaly).

If Meena drops CS101 and she was its only student, the course and instructor information vanish (delete anomaly).

23.2 First Normal Form (1NF) Each column holds atomic (indivisible) values — no lists, no sets.

No repeating groups of columns (phone1, phone2, phone3).

Each row is unique (there is a primary key).

Each column has a single data type/meaning.

1NF version — one row per student per course; phones moved to their own table:

enrollments_1nf — PK (student_id, course_code) student_phones — PK (student_id, phone)

Still redundant: student names and course details repeat.

23.3 Second Normal Form (2NF) Rules: in 1NF and no partial dependency — every non-key column must depend on the whole primary key, not just part of a composite key. (Tables with a single-column PK are automatically in 2NF.)

Dependencies in enrollments_1nf with key (student_id, course_code):

student_id → student_name (partial: depends on only part of the key)

course_code → course_title, instructor, instructor_office (partial)

(student_id, course_code) → grade (full) students — PK student_id courses — PK course_code

enrollments — PK (student_id, course_code) 23.4 Third Normal Form (3NF)

Rules: in 2NF and no transitive dependency — non-key columns must not depend on other non-key columns.

In courses: course_code → instructor and instructor → instructor_office. So instructor_office depends on the key only through instructor (transitive). If Dr. Rao teaches five courses, the office repeats five times.

instructors — PK instructor_id courses — PK course_code, FK instructor_id The 3NF schema in SQL:

Now: Dr. Rao's office is stored once (no update anomaly); courses can exist without students (no insert anomaly); removing an enrollment does not remove course data (no delete anomaly).

Mnemonic: *"Every non-key attribute must provide a fact about the key (1NF), the whole key (2NF), and nothing but the key (3NF), so help me Codd."* 23.5 Boyce–Codd Normal Form (BCNF)

Rule: for every non-trivial functional dependency X → Y, X must be a superkey. BCNF is a stricter 3NF; the difference appears when a table has overlapping composite candidate keys.

Example: a tutoring center where each tutor teaches exactly one subject, and a student has one tutor per subject.

(student, subject) → tutor — key.

tutor → subject — each tutor teaches one subject. tutor is not a superkey.

Candidate keys: (student, subject) and (student, tutor). Every attribute is part of some candidate key, so the table satisfies 3NF (3NF allows X → Y when Y is a prime attribute), but it violates BCNF because tutor → subject has a non-superkey determinant. Anomaly: the fact "Tutor-A teaches Math" is repeated for every student.

BCNF decomposition:

tutor_subjects (tutor PK → subject) student_tutors (student, tutor) PK

Trade-off: the rule "a student has only one tutor per subject" can no longer be enforced by a single table constraint (BCNF decomposition is not always dependency-preserving). In practice, 3NF is often the practical target and BCNF is applied when it does not lose important constraints.

23.6 Fourth Normal Form (4NF) Rule: in BCNF and no non-trivial multi-valued dependencies (MVDs) other than those implied by a key. An MVD A →→ B means A determines a set of B values independently of other attributes.

Example: an employee has multiple skills and speaks multiple languages, independently.

emp →→ skill and emp →→ language are independent. Every skill must be paired with every language — adding a third language requires adding a row for each skill (redundancy and insert anomaly).

4NF decomposition:

23.7 Fifth Normal Form (5NF, Project-Join Normal Form) Rule: in 4NF and every join dependency is implied by candidate keys — the table cannot be decomposed into smaller tables and rejoined without loss, unless the decomposition is based on keys.

Example: agents sell products for companies, with the business rule: *"If an agent sells a product type, and the agent represents a company, and the company makes that product type, then the agent sells that product for that company."*

Under that cyclic rule, the table can be decomposed into three binary tables — (agent, company), (agent, product), (company, product) — and their three-way join reproduces exactly the original rows. Storing the three smaller tables removes redundancy. Without the rule, the decomposition would create spurious rows, and the table is already in 5NF.

5NF situations are rare in practice; recognize the concept for interviews and academic exams.

23.8 Normal forms summary Atomic values, no repeating groups, unique rows

1NF + no partial dependency on part of a composite key Redundancy from partial keys

2NF + no transitive dependency (non-key → non-key) Redundancy through non-key attributes Every determinant is a superkey

Remaining anomalies with overlapping candidate keys BCNF + no independent multi-valued dependencies

Cartesian redundancy of independent lists 4NF + no non-trivial join dependencies

Redundancy that only lossless 3+-way decomposition removes Denormalization is the intentional introduction of redundancy to improve read performance or simplify queries, after a normalized design exists.

When denormalization is useful:

Read-heavy workloads where joins/aggregations are a measured bottleneck.

Data warehouses (star schemas with denormalized dimension tables).

Reporting and dashboards.

Caching computed values that are expensive to derive.

Capturing historical values (prices, addresses at order time).

Costs: extra storage, more complex writes, risk of inconsistency (must be maintained by triggers, application code, or batch jobs).

23.10 Normalization vs performance Highly normalized (OLTP) Denormalized (OLAP / reporting) Slower (more places to update) Fewer joins, faster aggregations Strong, enforced by keys Requires maintenance logic Transactions (banking, orders) Analytics (star/snowflake schemas)

Guideline: normalize to 3NF first, then denormalize selectively based on measured performance needs.

CREATE TABLE students (
    student_id    VARCHAR(10) PRIMARY KEY,
    student_name  VARCHAR(100) NOT NULL
);

CREATE TABLE student_phones (
    student_id  VARCHAR(10) REFERENCES students(student_id),
    phone       VARCHAR(15),
    PRIMARY KEY (student_id, phone)
);

CREATE TABLE instructors (
    instructor_id    VARCHAR(10) PRIMARY KEY,
    instructor_name  VARCHAR(100) NOT NULL,
    office           VARCHAR(20)
);

CREATE TABLE courses (
    course_code    VARCHAR(10) PRIMARY KEY,
    course_title   VARCHAR(100) NOT NULL,
    instructor_id  VARCHAR(10) REFERENCES instructors(instructor_id)
);

CREATE TABLE enrollments (
    student_id   VARCHAR(10) REFERENCES students(student_id),
    course_code  VARCHAR(10) REFERENCES courses(course_code),
    grade        CHAR(2),
    PRIMARY KEY (student_id, course_code)
);

-- 1. Stored derived value: order total instead of summing items every time
ALTER TABLE orders ADD total_amount DECIMAL(12,2);   -- kept in sync by application or trigger

-- 2. Copying a frequently displayed attribute
ALTER TABLE orders ADD customer_name VARCHAR(100);    -- avoids a join for order lists

-- 3. Summary tables for reporting
CREATE TABLE daily_sales_summary (
    sales_date  DATE PRIMARY KEY,
    orders      INT,
    revenue     DECIMAL(14,2)
);

-- 4. Historical snapshot (not really redundancy — capturing facts at a point in time)
-- order_items.unit_price records the price at purchase time, even though products.price changes later.

Expected Result

Query the normalized schema to rebuild the original report:

SELECT s.student_name, c.course_title, i.instructor_name, i.office, e.grade
FROM enrollments e
JOIN students s    ON s.student_id = e.student_id
JOIN courses c     ON c.course_code = e.course_code
JOIN instructors i ON i.instructor_id = c.instructor_id
ORDER BY s.student_name, c.course_title;

Common Mistakes

Storing comma-separated lists in a column ("tags = 'a,b,c'").

Repeating column groups (phone1, phone2, phone3).

Storing derived values without a strategy to keep them in sync.

Over-normalizing trivial attributes into separate tables (e.g., a table for every enum-like value that never changes), making queries needlessly complex.

Denormalizing prematurely without evidence of a performance problem.

Confusing "historical snapshot" data (price at purchase time) with redundancy.

Best Practices

Design to 3NF (or BCNF) for transactional systems.

Document every intentional denormalization and how it is maintained.

Use views or materialized views to present denormalized shapes without duplicating base data.

Use star schemas for analytics warehouses.

Interview Relevance

Very high in both academic and industry interviews: "What is normalization?", "Explain 1NF/2NF/3NF with examples", "3NF vs BCNF?", "What are update/insert/delete anomalies?", "When would you denormalize?".

Practice Questions

Normalize this table to 3NF: orders(order_id, order_date, customer_id, customer_name, customer_city, product_id, product_name, qty, unit_price).

Identify the partial and transitive dependencies in your answer to Q1.

Give an example of a table in 3NF but not BCNF.

Explain why order_items.unit_price is not a normalization violation.

Describe two situations where denormalization is justified.