16-schema-design-normalization.sql

-- ============================================================
-- SQL Masterclass
-- Chapter 16: Schema Design & Normalization
-- ============================================================
-- Level: Expert (Data Architect)
-- Dependencies: PostgreSQL (Requires ecommerce DB from setup)
-- 
-- Concepts Covered:
-- 1. Normal Forms (1NF, 2NF, 3NF)
-- 2. Denormalization (Flattening data for analytics)
-- 3. Star Schema vs Snowflake Schema concepts
-- 4. Creating and populating Fact and Dimension tables
-- ============================================================

-- ============================================================
-- 1. The Denormalized Flat Table Problem
-- ============================================================
-- In CSV files or basic analytics dumps, data is often "denormalized."
-- This means data is duplicated across rows, causing slow writes,
-- high storage costs, and anomaly risks when updating.

-- Let's simulate a flat 'Orders Dump' to see what it looks like:
DROP TABLE IF EXISTS flat_orders_dump;

CREATE TABLE flat_orders_dump AS
SELECT 
    o.order_id,
    o.order_status,
    o.order_purchase_timestamp,
    c.customer_id,
    c.customer_unique_id,
    c.customer_city,
    c.customer_state,
    p.product_id,
    p.product_category_name,
    oi.price,
    oi.freight_value
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

-- If you query this table, notice how `customer_city` and `product_category_name`
-- are repeated millions of times if a customer buys repeatedly or a product is popular.
SELECT * FROM flat_orders_dump LIMIT 5;


-- ============================================================
-- 2. Normalization (OLTP Design - 3NF)
-- ============================================================
-- The Olist database provided to us is actually well-normalized into 3NF.
-- 1NF: Every column holds atomic data (no comma-separated lists), tables have primary keys.
-- 2NF: No partial dependencies (all non-key columns depend on the FULL primary key).
-- 3NF: No transitive dependencies (non-key columns don't depend on other non-key columns).

-- Example: Why do we have a separate `customers` table and `orders` table?
-- Because an order's status depends on the `order_id`, but the `customer_city` 
-- depends on the `customer_id`. Kept together, `customer_city` is transitively 
-- dependent on `order_id` through `customer_id`. That violates 3NF!

-- Let's query the normalized tables to prove they contain no duplicated conceptual records:
SELECT * FROM customers WHERE customer_id = '06b8999e2fba1a1fbc88';
SELECT * FROM orders WHERE customer_id = '06b8999e2fba1a1fbc88';


-- ============================================================
-- 3. The Star Schema (OLAP Design)
-- ============================================================
-- While 3NF is great for Application Databases (fast inserts/updates),
-- Data Warehouses prefer "Star Schemas" for fast reads.
-- Star Schemas have a central "Fact Table" (metrics/events) surrounded
-- by "Dimension Tables" (descriptive attributes).

-- Let's build a Data Warehouse Star Schema for Olist!

-- 3a. Create Dimension: Date (Calendar Table)
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_date AS
SELECT
    datum AS date_key,
    EXTRACT(YEAR FROM datum) AS year,
    EXTRACT(MONTH FROM datum) AS month_num,
    TO_CHAR(datum, 'Month') AS month_name,
    EXTRACT(QUARTER FROM datum) AS quarter,
    EXTRACT(DOW FROM datum) AS day_of_week
FROM GENERATE_SERIES('2016-01-01'::DATE, '2019-12-31'::DATE, '1 day') AS datum;

-- 3b. Create Dimension: Customer
DROP TABLE IF EXISTS dim_customer;
CREATE TABLE dim_customer AS
SELECT
    customer_id AS customer_key, -- Usually a surrogate integer key in real DW
    customer_unique_id,
    customer_city,
    customer_state
FROM customers;

-- 3c. Create Dimension: Product
DROP TABLE IF EXISTS dim_product;
CREATE TABLE dim_product AS
SELECT 
    p.product_id AS product_key,
    COALESCE(t.product_category_name_english, p.product_category_name, 'Unknown') AS category_name,
    p.product_weight_g
FROM products p
LEFT JOIN product_category_name_translation t 
    ON p.product_category_name = t.product_category_name;

-- 3d. Create Fact: Order Sales
-- Fact tables contain Foreign Keys to Dimensions, and numeric Measurements.
DROP TABLE IF EXISTS fact_order_sales;
CREATE TABLE fact_order_sales AS
SELECT
    oi.order_id,
    oi.order_item_id,
    CAST(o.order_purchase_timestamp AS DATE) AS date_key,
    o.customer_id AS customer_key,
    oi.product_id AS product_key,
    oi.seller_id AS seller_key,
    oi.price AS revenue_amount,
    oi.freight_value AS shipping_cost
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_status = 'delivered';


-- ============================================================
-- 4. Querying the Star Schema
-- ============================================================
-- Notice how clean and intuitive analytics queries become when 
-- querying the Star Schema compared to highly normalized 3NF structures.

-- Q: Total Revenue by Year and Product Category?
SELECT
    d.year,
    p.category_name,
    SUM(f.revenue_amount) AS total_revenue
FROM fact_order_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
WHERE p.category_name IN ('health_beauty', 'sports_leisure')
GROUP BY d.year, p.category_name
ORDER BY d.year, total_revenue DESC;


-- ============================================================
-- 5. Exercises
-- ============================================================

-- Exercise 1: Create a `dim_seller` table containing the seller_id, city, and state.
-- Write your query below:
DROP TABLE IF EXISTS dim_seller;
-- CREATE TABLE dim_seller AS ...
-- SELECT ...


-- Exercise 2: Using the Fact and Dimension tables you've created, 
-- write a query to find the Total Revenue generated by Sellers in "sao paulo" during 2018.
-- Write your query below:
-- SELECT ...


-- ============================================================
-- Solutions
-- ============================================================
/*
-- Solution 1:
CREATE TABLE dim_seller AS
SELECT
    seller_id AS seller_key,
    seller_city,
    seller_state
FROM sellers;

-- Solution 2:
SELECT
    SUM(f.revenue_amount) AS sp_seller_revenue_2018
FROM fact_order_sales f
JOIN dim_seller s ON f.seller_key = s.seller_key
JOIN dim_date d ON f.date_key = d.date_key
WHERE d.year = 2018
  AND s.seller_city = 'sao paulo';
*/