schema.sql

------------------------------------------------------
-- SCHEMA: olympics
------------------------------------------------------
DROP DATABASE IF EXISTS olympicDB;
CREATE DATABASE olympicDB;
\connect olympicDB;

DROP SCHEMA IF EXISTS olympics CASCADE;
CREATE SCHEMA olympics;
SET SCHEMA 'olympics';

DROP TABLE IF EXISTS OLYMPIAD CASCADE;
DROP TABLE IF EXISTS COUNTRY CASCADE;
DROP TABLE IF EXISTS SPORT CASCADE;
DROP TABLE IF EXISTS PARTICIPANT CASCADE;
DROP TABLE IF EXISTS ACCOUNT CASCADE;
DROP TABLE IF EXISTS TEAM_MEMBERS CASCADE;
DROP TABLE IF EXISTS TEAM CASCADE;
DROP TABLE IF EXISTS EVENT CASCADE;
DROP TABLE IF EXISTS VENUE CASCADE;
DROP TABLE IF EXISTS MEDAL CASCADE;
DROP TABLE IF EXISTS PLACEMENT CASCADE;


/*
Domains:
- athlete_gender: 'M' or 'F'
- team_gender: 'M', 'F', or 'X' (mixed)
- participant_role: 'Organizer', 'Participant', 'Guest'
- medal_type: 'Gold', 'Silver', 'Bronze'
*/

DROP DOMAIN IF EXISTS athlete_gender CASCADE;
DROP DOMAIN IF EXISTS team_gender CASCADE;
DROP DOMAIN IF EXISTS role CASCADE;
DROP DOMAIN IF EXISTS medal_type CASCADE;

CREATE DOMAIN athlete_gender AS VARCHAR(1) CHECK (VALUE IN ('M', 'F'));
CREATE DOMAIN team_gender AS VARCHAR(1) CHECK (VALUE IN ('M', 'F', 'X'));
CREATE DOMAIN role AS VARCHAR(12) CHECK (VALUE IN ('Organizer', 'Participant', 'Guest'));
CREATE DOMAIN medal_type AS VARCHAR(6) CHECK (VALUE IN ('Gold', 'Silver', 'Bronze'));



/*
TABLE: COUNTRY
Format: (country_code, country_name)
*/
CREATE TABLE COUNTRY (
    country_code CHAR(3),
    country_name VARCHAR(30) NOT NULL,
    CONSTRAINT country_pk PRIMARY KEY (country_code) --checks that the country code is unique and not null
);

/*
TABLE: OLYMPIAD
Format: (olympiad_num, city, country, opening_date, closing_date, website)
*/
CREATE TABLE OLYMPIAD (
    olympiad_num VARCHAR(50),
    city VARCHAR(30) NOT NULL,
    country CHAR(3) NOT NULL, --country code for reference to country table (identified by country_code)
    opening_date TIMESTAMP NOT NULL,
    closing_date TIMESTAMP NOT NULL,
    website VARCHAR(70) NOT NULL,
    CONSTRAINT olympiad_pk PRIMARY KEY (olympiad_num), --checks that the olympiad number is unique and not null
    CONSTRAINT olympiad_country_fk FOREIGN KEY (country) REFERENCES COUNTRY(country_code) ON DELETE CASCADE, --checks that the country code is a valid country code
    CONSTRAINT olympiad_opening_date_chk CHECK ((opening_date <= closing_date) OR (closing_date IS NULL)) --checks that the opening date is before the closing date
);


/*
TABLE: SPORT
Format: (sport_id, sport_name, sport_description, team_size, date_added)
*/
CREATE TABLE SPORT (
    sport_id SERIAL,
    sport_name VARCHAR(30) NOT NULL,
    description VARCHAR(240),
    team_size INT NOT NULL,
    date_added TIMESTAMP NOT NULL,
    CONSTRAINT sport_pk PRIMARY KEY (sport_id), --checks that the sport id is unique and not null
    CONSTRAINT sport_team_size_chk CHECK ((team_size > 0) AND (team_size < 100)) --checks that the team size is greater than 0 and less than ... idk maybe 100?
);

/*
TABLE: ACCOUNT
Format: (account_id, username, passkey, participant_role, last_login)
*/
CREATE TABLE ACCOUNT (
    account_id SERIAL,
    username VARCHAR(30) NOT NULL,
    passkey VARCHAR(30) NOT NULL,
    role role NOT NULL,
    last_login TIMESTAMP,
    CONSTRAINT account_pk PRIMARY KEY (account_id) --checks that the account id is unique and not null
);

/*
TABLE: PARTICIPANT
Format: (participant_id, acount, first_name, middle_name, last_name, birth_country, dob, gender)
*/
CREATE TABLE PARTICIPANT (
    participant_id SERIAL,
    account INT NOT NULL, --account username for reference to account table (identified by account_id)
    first VARCHAR(30),
    middle VARCHAR(30), --leaving these open bc some countries have different naming conventions (e.g. brazil can have many last names or only one name)
    last VARCHAR(30),
    birth_country CHAR(3) NOT NULL, --country code for reference to country table
    dob TIMESTAMP,
    gender athlete_gender NOT NULL,
    CONSTRAINT participant_pk PRIMARY KEY (participant_id), --checks that the participant id is unique and not null
    CONSTRAINT participant_account_fk FOREIGN KEY (account) REFERENCES ACCOUNT(account_id) ON DELETE CASCADE, --checks that the account username is a valid username
    CONSTRAINT participant_birth_country_fk FOREIGN KEY (birth_country) REFERENCES COUNTRY(country_code), --checks that the birth country code is a valid country code
    CONSTRAINT participant_dob_chk CHECK (dob <= CURRENT_DATE) --checks that the date of birth is not in the future
);

/*
TABLE: TEAM
Format: (team_id, OLYMPIAD, SPORT, coach (PARTICIPANT), COUNTRY, gender, eligible)
*/
CREATE TABLE TEAM (
    team_id SERIAL,
    olympiad VARCHAR(30) NOT NULL, --olympiad number for reference to olympiad table (identified by olympiad_num)
    sport INT NOT NULL, --sport id for reference to sport table (identified by sport_id)
    coach INT NOT NULL, --participant id for reference to participant table (identified by participant_id)
    country CHAR(3) NOT NULL, --country code for reference to country,
    gender team_gender,
    eligible BOOLEAN,
    CONSTRAINT team_pk PRIMARY KEY (team_id), --checks that the team id is unique and not null
    CONSTRAINT team_olympiad_fk FOREIGN KEY (olympiad) REFERENCES OLYMPIAD(olympiad_num) ON DELETE CASCADE, --checks that the olympiad number is a valid olympiad number
    CONSTRAINT team_sport_fk FOREIGN KEY (sport) REFERENCES SPORT(sport_id) ON DELETE CASCADE, --checks that the sport id is a valid sport id
    CONSTRAINT team_coach_fk FOREIGN KEY (coach) REFERENCES PARTICIPANT(participant_id) ON DELETE CASCADE, --checks that the coach id is a valid participant id
    CONSTRAINT team_country_fk FOREIGN KEY (country) REFERENCES COUNTRY(country_code) ON DELETE CASCADE--checks that the country code is a valid country code
);


/*
TABLE: TEAM_MEMBERS
Format: (TEAM, PARTICIPANT)
*/
CREATE TABLE TEAM_MEMBERS (
    team INT NOT NULL, --team id for reference to team table (identified by team_id)
    participant INT NOT NULL, --participant id for reference to participant table (identified by participant_id)
    CONSTRAINT team_members_pk PRIMARY KEY (team, participant), --checks that the team id and participant id are unique and not null
    CONSTRAINT team_members_team_fk FOREIGN KEY (team) REFERENCES TEAM(team_id) ON DELETE CASCADE, --checks that the team id is a valid team id
    CONSTRAINT team_members_participant_fk FOREIGN KEY (participant) REFERENCES PARTICIPANT(participant_id) ON DELETE CASCADE
);

/*
TABLE: VENUE
Format: (venue_name, capacity)
*/
CREATE TABLE VENUE (
    venue_name VARCHAR(50) NOT NULL,
    capacity INT NOT NULL,
    CONSTRAINT venue_pk PRIMARY KEY (venue_name), --checks that the venue name is unique and not null
    CONSTRAINT venue_capacity_chk CHECK (capacity > 0) --checks that the capacity is greater than 0
);

/*
TABLE: EVENTS
Format: (event_id, VENUE, OLYMPIAD, SPORT, gender, date)
*/
CREATE TABLE EVENT (
    event_id SERIAL,
    venue VARCHAR(50), --venue name for reference to venue table (identified by venue name)
    olympiad VARCHAR(30) NOT NULL, --olympiad number for reference to olympiad table (identified by olympiad_num)
    sport INT NOT NULL, --sport id for reference to sport table (identified by sport,
    gender team_gender,
    date TIMESTAMP,
    CONSTRAINT events_pk PRIMARY KEY (event_id), --checks that the event id is unique and not null
    CONSTRAINT events_venue_fk FOREIGN KEY (venue) REFERENCES VENUE(venue_name) ON DELETE CASCADE, --checks that the venue id is a valid venue id
    CONSTRAINT events_olympiad_fk FOREIGN KEY (olympiad) REFERENCES OLYMPIAD(olympiad_num) ON DELETE CASCADE,--checks that the olympiad number is a valid olympiad number
    CONSTRAINT events_sport_fk FOREIGN KEY (sport) REFERENCES SPORT(sport_id) ON DELETE CASCADE --checks that the sport id is a valid sport id
);



/*
TABLE: MEDAL
Format: (medal_id, type, points)
*/
CREATE TABLE MEDAL (
    medal_id SERIAL,
    type medal_type NOT NULL,
    points INT NOT NULL, --follows the olympic medal points system of 5:3:1
    CONSTRAINT medal_pk PRIMARY KEY (medal_id), --checks that the medal id is unique and not null
    CONSTRAINT medal_points_chk CHECK (points > 0) --checks that the points are greater than 0
);

/*
TABLE: PLACEMENT
Format: (event, team, medal, position)
*/
CREATE TABLE PLACEMENT (
    event INT NOT NULL, --event id for reference to event table (identified by event_id)
    team INT NOT NULL, --team id for reference to team table (identified by team_id)
    medal INT, --medal id for reference to medal table (identified by medal_id)
    position INT,
    CONSTRAINT placement_pk PRIMARY KEY (event, team), --checks that the event id and team id are unique and not null
    CONSTRAINT placement_event_fk FOREIGN KEY (event) REFERENCES EVENT(event_id) ON DELETE CASCADE, --checks that the event id is a valid event id
    CONSTRAINT placement_team_fk FOREIGN KEY (team) REFERENCES TEAM(team_id) ON DELETE CASCADE, --checks that the team id is a valid team id
    CONSTRAINT placement_medal_fk FOREIGN KEY (medal) REFERENCES MEDAL(medal_id) ON DELETE CASCADE, --checks that the medal id is a valid medal id
    --CONSTRAINT placement_position_chk CHECK (position > 0) --checks that the position is greater than 0
);