Normalization (1NF-BCNF)

First Normal Form (1NF)

A table is in 1NF if every column contains only atomic (indivisible) values and there are no repeating groups. Each row must be unique, identified by a primary key.

Violations of 1NF:

-- BAD: repeating groups in a single column
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    products VARCHAR(500) -- Phone, Laptop, Tablet (comma-separated)
);

-- BAD: multiple columns for the same attribute
CREATE TABLE student_courses (
    student_id INT PRIMARY KEY,
    course1 VARCHAR(100),
    course2 VARCHAR(100),
    course3 VARCHAR(100)
);

1NF-compliant tables:

-- GOOD: atomic values in separate rows
CREATE TABLE order_items (
    order_id INT,
    product_name VARCHAR(100),
    quantity INT,
    PRIMARY KEY (order_id, product_name)
);

-- GOOD: one course per row
CREATE TABLE student_courses (
    student_id INT,
    course_name VARCHAR(100),
    PRIMARY KEY (student_id, course_name)
);

Benefits of 1NF:

• Enables proper indexing and searching
• Allows aggregate functions to work correctly
• Prevents data anomalies from string manipulation
• Makes relationships between data explicit

1NF is the foundation of normalization. All higher normal forms build upon it.

Second Normal Form (2NF)

A table is in 2NF if it is in 1NF and every non-key column is fully functionally dependent on the entire primary key. This eliminates partial dependencies where a non-key column depends on only part of a composite primary key.

Violation of 2NF:

-- BAD: product_name depends only on product_id, not on the full key (order_id, product_id)
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100), -- depends only on product_id
    product_category VARCHAR(50), -- depends only on product_id
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

The problem: product_name and product_category depend only on product_id, not on the full composite key (order_id, product_id). This causes update anomalies — if a product name changes, you must update every order item.

2NF-compliant tables:

-- GOOD: separate tables for orders and products
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    product_category VARCHAR(50)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

2NF is only relevant for tables with composite primary keys. If a table has a single-column primary key, it automatically satisfies 2NF (assuming it is in 1NF).

Third Normal Form (3NF)

A table is in 3NF if it is in 2NF and no non-key column depends on another non-key column. This eliminates transitive dependencies.

Violation of 3NF:

-- BAD: city depends on zip_code, which depends on employee_id
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    zip_code VARCHAR(10),
    city VARCHAR(100), -- depends on zip_code, not directly on employee_id
    state VARCHAR(50) -- depends on zip_code
);

The transitive dependency: employee_id → zip_code → city, state. If the city for a zip code changes, you must update every row.

3NF-compliant tables:

-- GOOD: separate the zip code lookup
CREATE TABLE zip_codes (
    zip_code VARCHAR(10) PRIMARY KEY,
    city VARCHAR(100),
    state VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    zip_code VARCHAR(10),
    FOREIGN KEY (zip_code) REFERENCES zip_codes(zip_code)
);

Another common 3NF violation:

-- BAD: department_name depends on department_id, not directly on employee_id
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    department_name VARCHAR(100) -- depends on department_id
);

-- GOOD: separate departments table
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

3NF reduces data redundancy and prevents update anomalies while maintaining good query performance.

Boyce-Codd Normal Form (BCNF)

BCNF is a stricter version of 3NF. A table is in BCNF if for every functional dependency X → Y, X is a superkey. This means every determinant must be a candidate key.

BCNF differs from 3NF when:

• A non-prime attribute determines part of a candidate key
• There are overlapping candidate keys

Example requiring BCNF:

-- A course can be taught by multiple instructors, but each instructor teaches one course
-- A student can enroll in multiple courses
CREATE TABLE course_instructors (
    student_id INT,
    course_name VARCHAR(100),
    instructor_name VARCHAR(100),
    PRIMARY KEY (student_id, course_name)
);

-- Functional dependencies:
-- student_id, course_name → instructor_name (primary key)
-- course_name → instructor_name (each course has one instructor)
-- instructor_name → course_name (each instructor teaches one course)

-- instructor_name is not a superkey but determines course_name
-- This violates BCNF

BCNF solution:

-- Split into two tables
CREATE TABLE course_assignments (
    course_name VARCHAR(100) PRIMARY KEY,
    instructor_name VARCHAR(100)
);

CREATE TABLE student_enrollments (
    student_id INT,
    course_name VARCHAR(100),
    PRIMARY KEY (student_id, course_name),
    FOREIGN KEY (course_name) REFERENCES course_assignments(course_name)
);

In practice, BCNF violations are rare in well-designed databases. Most 3NF designs are already in BCNF. BCNF is most relevant in complex schemas with multiple overlapping candidate keys.

Benefits of Normalization

Normalization provides several critical benefits for database systems:

Reduced Data Redundancy:
Normalized tables store each piece of data once. Without normalization, a customer's address might be stored in orders, invoices, and shipping tables. With normalization, it exists only in the customers table.

-- Without normalization: customer data duplicated
CREATE TABLE orders_unnormalized (
    order_id INT,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    customer_address VARCHAR(200),
    -- customer data repeated for every order
);

-- With normalization: customer data stored once
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    customer_address VARCHAR(200)
);

CREATE TABLE orders_normalized (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Improved Data Integrity:
Changes to customer information only need to happen in one place. This eliminates inconsistency where the same customer has different addresses in different tables.

Easier Maintenance:
Adding a new field (like a phone number) requires altering only one table instead of many.

Better Query Optimization:
Smaller, focused tables allow the database optimizer to make better choices about indexes and join strategies.

Trade-offs:
Normalization can require more JOINs to reconstruct full records, which may impact read performance. This is why denormalization is sometimes used for read-heavy systems.

Normalization Examples

Example: E-commerce Schema

Starting unnormalized table:

CREATE TABLE order_records (
    order_id INT,
    order_date DATE,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    product_name VARCHAR(100),
    product_price DECIMAL(10,2),
    product_category VARCHAR(50),
    quantity INT
);

1NF: Each value is atomic, no repeating groups.
2NF: Remove partial dependencies — product details depend only on product.
3NF: Remove transitive dependencies — no non-key determines another non-key.

-- Normalized schema
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100)
);

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    product_price DECIMAL(10,2),
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

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

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

This normalized schema eliminates redundancy and ensures data integrity. Each entity (customers, products, orders) is stored once and referenced by foreign keys.