Normalization SQL Examples:

Unnormalized Table:

CREATE TABLE orders_bad (

order_id INT PRIMARY KEY,

customer_name VARCHAR(100),

customer_email VARCHAR(100),

product1 VARCHAR(100),

product2 VARCHAR(100),

product3 VARCHAR(100),

price1 DECIMAL(10,2),

price2 DECIMAL(10,2),

price3 DECIMAL(10,2)

);

-- Problems: Repeating groups (product1, product2, product3)

-- Cannot store more than 3 products per order

-- Wasted space if order has only 1 product

1NF (Atomic Values):

CREATE TABLE orders_1nf (

order_id INT,

customer_name VARCHAR(100),

customer_email VARCHAR(100),

product VARCHAR(100),

price DECIMAL(10,2),

PRIMARY KEY (order_id, product)

);

-- Each cell has single value

-- Can store unlimited products per order

-- Still has redundancy (customer_name repeated)

2NF (No Partial Dependencies):

CREATE TABLE customers_2nf (

customer_id INT PRIMARY KEY,

customer_name VARCHAR(100),

customer_email VARCHAR(100)

);

CREATE TABLE products_2nf (

product_id INT PRIMARY KEY,

product_name VARCHAR(100),

price DECIMAL(10,2)

);

CREATE TABLE orders_2nf (