Advanced Normalization (BCNF, 4NF, 5NF)

-- UNNORMALIZED: everything in one table (messy!)
-- orders: order_id, customer_name, customer_email,
--         product1_name, product1_qty, product2_name, product2_qty

-- 1NF: Eliminate repeating groups
-- Each cell has one value, no arrays or lists
CREATE TABLE order_items_1nf (
    order_id INT,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    product_name VARCHAR(100),
    quantity INT,
    price DECIMAL(10,2)
);
-- Problem: customer data repeated on every row

-- 2NF: 
...

-- 3NF: Remove transitive dependencies
-- Bad: employee table has department_name AND department_location
-- department_location depends on department_name, not employee

-- Before 3NF:
-- employees(id, name, dept_name, dept_location, dept_manager)

-- After 3NF: separate the transitive dependency
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    name VARCHAR(50),
    location VARCHAR(100),
    manager_id INT
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100
...

-- BCNF (Boyce-Codd Normal Form)
-- Stricter than 3NF: every determinant is a candidate key

-- Problem scenario: course scheduling
-- (student, course) -> professor
-- professor -> course (each prof teaches one course)

-- This violates BCNF because professor -> course
-- but professor is NOT a candidate key

-- Fix: split into two tables
CREATE TABLE professor_courses (
    professor_id INT PRIMARY KEY,
    professor_name VARCHAR(100),
    course_id INT UNIQUE  -- each prof teaches one course

...

-- 4NF: Eliminate multi-valued dependencies
-- A student can have multiple hobbies AND multiple languages
-- These are independent of each other

-- Bad (violates 4NF):
-- student_info(student_id, hobby, language)
-- Creates cartesian product: 3 hobbies × 2 languages = 6 rows!

-- Good (4NF):
CREATE TABLE student_hobbies (
    student_id INT,
    hobby VARCHAR(50),
    PRIMARY KEY (student_id, hobby)
);

CREATE TABLE student_languages (
    student_id INT,
    language VARCHAR(50),
    PRIMARY K
...