Untitled

-- Create soccer_team table
CREATE TABLE soccer_team (
    team_id NUMERIC(10, 0) PRIMARY KEY,
    team_group CHAR(1),
    match_played NUMERIC(3, 0),
    won NUMERIC(3, 0),
    draw NUMERIC(3, 0),
    lost NUMERIC(3, 0),
    goal_for NUMERIC(3, 0),
    goal_against NUMERIC(3, 0),
    goal_diff NUMERIC(3, 0),
    points NUMERIC(3, 0),
    group_position NUMERIC(3, 0) NOT NULL
);

-- Create soccer_country table
CREATE TABLE soccer_country (
    country_id NUMERIC(10, 0) PRIMARY KEY,
    country_abbr CHAR(2),
    country_name VARCHAR(40)
);

-- Create soccer_city table
CREATE TABLE soccer_city (
    city_id NUMERIC(10, 0) PRIMARY KEY,
    city_name VARCHAR(25),
    country_id NUMERIC(10, 0),
    FOREIGN KEY (country_id) REFERENCES soccer_country(country_id)
);

-- Create soccer_venue table
CREATE TABLE soccer_venue (
    venue_id NUMERIC(10, 0) PRIMARY KEY,
    venue_name VARCHAR(30),
    city_id NUMERIC(10, 0),
    aud_capacity NUMERIC(10, 0),
    FOREIGN KEY (city_id) REFERENCES soccer_city(city_id)
);

-- Create referee_mast table
CREATE TABLE referee_mast (
    referee_id NUMERIC(10, 0) PRIMARY KEY,
    referee_name VARCHAR(40),
    country_id NUMERIC(10, 0),
    FOREIGN KEY (country_id) REFERENCES soccer_country(country_id)
);

-- Create coach_mast table
CREATE TABLE coach_mast (
    coach_id NUMERIC(10, 0) PRIMARY KEY,
    coach_name VARCHAR(40)
);

-- Create team_coaches table
CREATE TABLE team_coaches (
    team_id NUMERIC(10, 0),
    coach_id NUMERIC(10, 0),
    PRIMARY KEY (team_id, coach_id),
    FOREIGN KEY (team_id) REFERENCES soccer_team(team_id),
    FOREIGN KEY (coach_id) REFERENCES coach_mast(coach_id)
);

-- Create player_mast table
CREATE TABLE player_mast (
    player_id NUMERIC(10, 0) PRIMARY KEY,
    team_id NUMERIC(10, 0),
    jersey_no NUMERIC(3, 0),
    player_name VARCHAR(40),
    pos_to_play CHAR(2),
    dt_of_birth DATE,
    age NUMERIC(3, 0),
    playing_club VARCHAR(40),
    FOREIGN KEY (team_id) REFERENCES soccer_team(team_id)
);

-- Create match_mast table
CREATE TABLE match_mast (
    match_no NUMERIC(10, 0) PRIMARY KEY,
    play_stage CHAR(1),
    play_date DATE,
    results CHAR(5),
    decided_by CHAR(1),
    goal_score CHAR(5),
    venue_id NUMERIC(10, 0),
    referee_id NUMERIC(10, 0),
    audience NUMERIC(10, 0),
    plr_of_match NUMERIC(10, 0),
    stop1_sec NUMERIC(3, 0),
    stop2_sec NUMERIC(3, 0),
    FOREIGN KEY (venue_id) REFERENCES soccer_venue(venue_id),
    FOREIGN KEY (referee_id) REFERENCES referee_mast(referee_id)
);

-- Create match_details table
CREATE TABLE match_details (
    match_no NUMERIC(10, 0),
    play_stage CHAR(1),
    team_id NUMERIC(10, 0),
    win_loss CHAR(1),
    decided_by CHAR(1),
    goal_score NUMERIC(3, 0),
    penalty_score NUMERIC(3, 0),
    ass_ref NUMERIC(3, 0),
    player_gk NUMERIC(10, 0),
    PRIMARY KEY (match_no, team_id),
    FOREIGN KEY (match_no) REFERENCES match_mast(match_no),
    FOREIGN KEY (team_id) REFERENCES soccer_team(team_id)
);

-- Create player_booked table
CREATE TABLE player_booked (
    match_no NUMERIC(10, 0),
    team_id NUMERIC(10, 0),
    player_id NUMERIC(10, 0),
    booking_time VARCHAR(40),
    sent_off CHAR(1),
    play_schedule CHAR(2),
    play_half NUMERIC(3, 0),
    PRIMARY KEY (match_no, team_id, player_id),
    FOREIGN KEY (match_no) REFERENCES match_mast(match_no),
    FOREIGN KEY (team_id) REFERENCES soccer_team(team_id),
    FOREIGN KEY (player_id) REFERENCES player_mast(player_id)
);

-- Create penalty_shootout table
CREATE TABLE penalty_shootout (
    kick_id NUMERIC(10, 0),
    match_no NUMERIC(10, 0),
    team_id NUMERIC(10, 0),
    player_id NUMERIC(10, 0),
    score_goal CHAR(1),
    PRIMARY KEY (kick_id, match_no, team_id, player_id),
    FOREIGN KEY (match_no) REFERENCES match_mast(match_no),
    FOREIGN KEY (team_id) REFERENCES soccer_team(team_id),
    FOREIGN KEY (player_id) REFERENCES player_mast(player_id)
);

-- Create playing_position table
CREATE TABLE playing_position (
    position_id CHAR(2) PRIMARY KEY,
    position_desc VARCHAR(15)
);

-- Create match_captain table
CREATE TABLE match_captain (
    match_no NUMERIC(10, 0),
    team_id NUMERIC(10, 0),
    player_captain NUMERIC(10, 0),
    PRIMARY KEY (match_no, team_id, player_captain),
    FOREIGN KEY (match_no) REFERENCES match_mast(match_no),
    FOREIGN KEY (team_id) REFERENCES soccer_team(team_id),
    FOREIGN KEY (player_captain) REFERENCES player_mast(player_id)
);

-- Create penalty_gk table
CREATE TABLE penalty_gk (
    match_no NUMERIC(10, 0),
    team_id NUMERIC(10, 0),
    player_gk NUMERIC(10, 0),
    PRIMARY KEY (match_no, team_id, player_gk),
    FOREIGN KEY (match_no) REFERENCES match_mast(match_no),
    FOREIGN KEY (team_id) REFERENCES soccer_team(team_id),
    FOREIGN KEY (player_gk) REFERENCES player_mast(player_id)
);

-- Create goal_details table
CREATE TABLE goal_details (
    goal_id NUMERIC(10, 0),
    match_no NUMERIC(10, 0),
    player_id NUMERIC(10, 0),
    team_id NUMERIC(10, 0),
    goal_type CHAR(1),
    play_stage CHAR(1),
    goal_schedule CHAR(2),
    goal_half NUMERIC(3, 0),
    PRIMARY KEY (goal_id, match_no),
    FOREIGN KEY (match_no) REFERENCES match_mast(match_no),
    FOREIGN KEY (player_id) REFERENCES player_mast(player_id),
    FOREIGN KEY (team_id) REFERENCES soccer_team(team_id)
);

-- Create asst_referee_mast table
CREATE TABLE asst_referee_mast (
    ass_ref_id NUMERIC(10, 0) PRIMARY KEY,
    ass_ref_name VARCHAR(40),
    country_id NUMERIC(10, 0),
    FOREIGN KEY (country_id) REFERENCES soccer_country(country_id)
);