Advanced Normalization (BCNF, 4NF, 5NF)

-- A quick recap of the first three normal forms.
-- Start with one fat table that holds an entire order on each row:
--   orders(order_id, customer_name, customer_city,
--          product, qty, price, line_total)

-- 1NF — every cell holds ONE value (no lists, no repeating groups).
-- 2NF — every non-key column depends on the WHOLE primary key,
--        not just part of a composite key.
-- 3NF — no non-key column depends on another non-key column
--        (no transitive dependencies).

-- Th
...

-- The three anomalies a badly-normalized table suffers from.
-- Picture the fat "enrollments" table further down this lesson:
--   enrollments(student_id, student_name, course, professor, prof_office)

-- 1) UPDATE anomaly
--    Professor "Adler" moves office. Her office is repeated on every
--    row she teaches, so you must update MANY rows. Miss one and the
--    database now disagrees with itself.

-- 2) INSERT anomaly
--    A brand-new professor is hired but has no students yet. You cannot
...

-- BCNF (Boyce-Codd Normal Form) — the "3.5NF" most schemas should hit.
-- Rule: for EVERY functional dependency X -> Y, X must be a candidate key
-- (something that could uniquely identify a row). 3NF allows a few leaks;
-- BCNF plugs them.

-- The classic trap: course scheduling.
--   teaching(student_id, course, professor)
--   * (student_id, course) -> professor   (the candidate key determines prof)
--   * professor -> course                 (each professor teaches ONE course)
-- The second 
...

-- 4NF — eliminate multi-valued dependencies (MVDs).
-- An MVD is when one key independently determines MULTIPLE values of
-- two unrelated attributes. Cramming both into one table multiplies rows.

-- Bad: student_id  ->>  hobby    AND   student_id  ->>  language
-- 2 hobbies and 2 languages explode into 4 meaningless combinations.

-- 4NF fix: one table per independent multi-valued fact.
CREATE TABLE student_hobbies (
    student_id INT,
    hobby      VARCHAR(50),
    PRIMARY KEY (student_id,
...

-- 5NF (PJ/NF) — eliminate join dependencies that aren't implied by keys.
-- It applies to a rare case: a table that genuinely must be rebuilt by
-- joining THREE or more tables, where no pair alone is enough.

-- Classic example: which agent sells which brand for which company,
-- where the three facts are independent two-at-a-time.
--   bad: sales(agent, company, brand)   -- can introduce false triples

-- 5NF fix: three binary relationship tables instead of one ternary table.
CREATE TABLE age
...

-- DENORMALIZATION — deliberately adding redundancy back, for speed.
-- After measuring (never before!), you may copy a value to avoid a JOIN
-- on a hot read path. Here we cache each order's total on the order row:

ALTER TABLE orders ADD COLUMN order_total DECIMAL(12,2);

-- The total still LIVES in order_lines (the source of truth). order_total
-- is a cached copy you must keep in sync on every write — usually with a
-- trigger or in application code:
UPDATE orders o
SET order_total = (
    S
...