Normalize a Flat E-Commerce Table

Problem Statement

You are given a flat (badly designed) table with all order data in one place. Your job is to normalize it into proper 3NF tables. Flat table (orders_flat): order_id, order_date, customer_name, customer_email, customer_city, product_name, product_category, product_price, quantity, total_amount Tasks: 1. Identify what's wrong with this flat table (write as SQL comments) 2. Design normalized tables (customers, products, orders, order_items) 3. Write CREATE TABLE statements for all 4 tables 4. Write a query to show: order_id, customer_name, product_name, quantity, total_amount

Sample Data

Sample flat data:
1, '2024-01-10', 'Alice', 'alice@x.com', 'Mumbai', 'Laptop', 'Electronics', 50000, 1, 50000
2, '2024-01-10', 'Alice', 'alice@x.com', 'Mumbai', 'Mouse', 'Electronics', 1500, 2, 3000
3, '2024-01-11', 'Bob',   'bob@x.com',   'Delhi',  'Desk',  'Furniture',   8000, 1, 8000
Notice: Alice's details repeat across rows — that's the problem.

Expected Output

Problems identified: customer data repeats, product data repeats, no separation of concerns
4 normalized tables with PKs and FKs
JOIN query across all 4 tables