schema.sql

-- =============================================================
-- Section 15 — Views & Materialised Views
-- =============================================================

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

CREATE TABLE customer (
    id        SERIAL PRIMARY KEY,
    full_name VARCHAR(200) NOT NULL,
    country   CHAR(2)      NOT NULL
);

CREATE TABLE product (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    category    VARCHAR(50)  NOT NULL,
    price_cents INTEGER      NOT NULL
);

CREATE TABLE "order" (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customer(id),
    placed_at   DATE NOT NULL
);

CREATE TABLE order_item (
    id         SERIAL PRIMARY KEY,
    order_id   INTEGER NOT NULL REFERENCES "order"(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES product(id),
    quantity   INTEGER NOT NULL CHECK (quantity > 0)
);