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) );
Leave a Comment