hw7_ddl

DROP TABLE "Countries";
DROP TABLE "Olympics";
DROP TABLE "Players";
DROP TABLE "Events";
DROP TABLE "Results";

CREATE TABLE "Countries" (
    name varchar(40),
    country_id varchar(3) UNIQUE,
    area_sqkm integer,
    population integer
);

CREATE TABLE "Olympics" (
    olympic_id char(7) unique,
    country_id char(3),
    city char(50),
    year integer,
    startdate date,
    enddate date,
    FOREIGN KEY (country_id) REFERENCES "Countries" (country_id)
);

CREATE TABLE "Players" (
    name char(40),
    player_id char(10) unique,
    country_id char(3),
    birthdate date,
    FOREIGN KEY (country_id) REFERENCES "Countries" (country_id)
);

CREATE TABLE "Events" (
    event_id char(7) unique,
    name char(40),
    eventtype char(20),
    olympic_id char(7),
    is_team_event integer check (is_team_event in (0, 1)),
    num_players_in_team integer,
    result_noted_in char(100),
    FOREIGN KEY (olympic_id) REFERENCES "Olympics" (olympic_id)
);

CREATE TABLE "Results" (
    event_id char(7),
    player_id char(10),
    medal char(7),
    result float,
    FOREIGN KEY (event_id) REFERENCES "Events" (event_id),
    FOREIGN KEY (player_id) REFERENCES "Players" (player_id)
)