schema.sql

-- Create table to store Oscar nominations and wins data
CREATE TABLE oscars (
    year_film        INT                 NOT NULL,          -- film release year
    year_ceremony    INT                 NOT NULL,          -- ceremony year
    ceremony         INT                 NOT NULL,          -- ceremony number (ordinal)
    category         TEXT                NOT NULL,          -- category name
    canon_category   TEXT                NOT NULL,          -- canonical category name
    name         	   TEXT,                                  -- nominee name
    film             TEXT,                                  -- film title
    winner           BOOLEAN             NOT NULL,          -- TRUE if winner, FALSE if not winner

    -- Main constraints
    CONSTRAINT chk_year_film_range
        CHECK (year_film BETWEEN 1900 AND 2100), 			      -- Ensure film year is within a reasonable range
    CONSTRAINT chk_year_ceremony_range
        CHECK (year_ceremony BETWEEN 1900 AND 2100),		    -- Ensure ceremony year is within a reasonable range
    CONSTRAINT chk_ceremony_pos
        CHECK (ceremony > 0)								                -- Ceremony number must be positive
);