schema.sql

-- =============================================================
-- Section 03 — Normalization: STARTING SCHEMA
-- =============================================================
-- Unlike most schema.sql files in this repo, this one is NOT a
-- reference. It is the STARTING POINT.
--
-- It contains three deliberately BAD, denormalized tables. You
-- will spend this section critiquing them, spotting anomalies,
-- and rewriting them into 1NF, then 2NF, then 3NF.
--
-- Load this file first, then work through exercises.md:
--
--     psql -U postgres -d sql_exercise \
--          -f 03-database-design/03-normalization/schema.sql
--     psql -U postgres -d sql_exercise \
--          -f 03-database-design/03-normalization/seed.sql
--
-- The normalized reference schemas live in:
--     solutions/03-database-design/03-normalization/solutions.sql
--
-- It drops the schema first, so it's safe to re-run.
-- =============================================================

DROP SCHEMA IF EXISTS dd_normalization CASCADE;
CREATE SCHEMA dd_normalization;
SET search_path TO dd_normalization;


-- -------------------------------------------------------------
-- bad_student_courses
-- -------------------------------------------------------------
-- A single table tracking which students are enrolled in which
-- courses, plus the instructor, and the student's phone numbers.
--
-- Look at everything wrong with this:
--   * student_phones holds a COMMA-SEPARATED LIST of phone numbers
--   * Student info (name, major) repeats for every enrollment
--   * Course info (title, credits) repeats for every enrollment
--   * Instructor info (name, dept) repeats for every enrollment
--   * No primary key
--   * Everything is TEXT, including credits
-- -------------------------------------------------------------
CREATE TABLE bad_student_courses (
    student_id       TEXT,
    student_name     TEXT,
    student_major    TEXT,
    student_phones   TEXT,      -- e.g. '555-0100, 555-0200'
    course_code      TEXT,
    course_title     TEXT,
    course_credits   TEXT,
    instructor_name  TEXT,
    instructor_dept  TEXT,
    grade            TEXT
);


-- -------------------------------------------------------------
-- bad_orders
-- -------------------------------------------------------------
-- Every line item of every order is one row, but the row also
-- carries customer info and product info. Classic 2NF violation:
-- customer_name depends only on customer_id (part of the PK),
-- and product_name/price depend only on product_id.
-- -------------------------------------------------------------
CREATE TABLE bad_orders (
    order_id          INTEGER,
    product_id        INTEGER,
    -- order-level (depends on order_id only)
    order_date        DATE,
    customer_id       INTEGER,
    customer_name     TEXT,
    customer_city     TEXT,
    customer_zip      TEXT,
    -- product-level (depends on product_id only)
    product_name      TEXT,
    product_category  TEXT,
    product_unit_price NUMERIC(10, 2),
    -- line-level (depends on the full PK)
    quantity          INTEGER,
    line_total        NUMERIC(10, 2)
);


-- -------------------------------------------------------------
-- bad_employees
-- -------------------------------------------------------------
-- One row per employee. Each employee has a department, and each
-- department has a location and a manager. That's a transitive
-- dependency: employee_id -> dept_code -> dept_location.
-- -------------------------------------------------------------
CREATE TABLE bad_employees (
    employee_id     INTEGER PRIMARY KEY,
    first_name      TEXT NOT NULL,
    last_name       TEXT NOT NULL,
    email           TEXT NOT NULL,
    salary          NUMERIC(10, 2) NOT NULL,
    dept_code       TEXT NOT NULL,
    dept_name       TEXT NOT NULL,   -- depends on dept_code, not employee_id
    dept_location   TEXT NOT NULL,   -- depends on dept_code, not employee_id
    dept_manager    TEXT NOT NULL    -- depends on dept_code, not employee_id
);