Normalize E-commerce Orders from Unnormalized to 3NF

advanced

E-commerce site stores orders in single table with repeating groups. Problems: Cannot store more than 3 products per order, customer data duplicated, product price changes affect old orders. Requirements: Normalize to 3NF to eliminate redundancy and anomalies.

-- Unnormalized table (violates 1NF)

CREATE TABLE orders_unnormalized (

order_id INT PRIMARY KEY,

order_date DATE,

customer_name VARCHAR(100),

customer_email VARCHAR(100),

customer_phone VARCHAR(20),

product1_name VARCHAR(100),

product1_price DECIMAL(10,2),

product1_quantity INT,

product2_name VARCHAR(100),

product2_price DECIMAL(10,2),

product2_quantity INT,

product3_name VARCHAR(100),

product3_price DECIMAL(10,2),

product3_quantity INT

);

-- Sample data showing problems

INSERT INTO orders_unnormalized VALUES

(1, '2024-01-15', 'John Doe', 'john@email.com', '555-0001',

'Laptop', 1000.00, 1, 'Mouse', 20.00, 2, 'Keyboard', 50.00, 1),

(2, '2024-01-16', 'John Doe', 'john@email.com', '555-0001',

'Monitor', 300.00, 1, NULL, NULL, NULL, NULL, NULL, NULL);

-- Problems:

-- 1. Repeating groups (product1, product2, product3) - violates 1NF

-- 2. Cannot store order with 4+ products

-- 3. Wasted space (order 2 has NULL for product2, product3)

-- 4. Customer data duplicated (John Doe in 2 rows) - update anomaly

-- 5. Cannot add customer without order - insertion anomaly

-- Step 1: Convert to 1NF (atomic values, no repeating groups)

CREATE TABLE orders_1nf (

order_id INT,

order_date DATE,

customer_name VARCHAR(100),

customer_email VARCHAR(100),

customer_phone VARCHAR(20),

product_name VARCHAR(100),