/*================================================================================*/
/* SQL CREATE SCRIPT                                                              */
/*================================================================================*/
/* Title:      XTOSS                                                              */
/* Filename:   xtoss1.hf                                                          */
/* Platform:   MySQL 3                                                            */
/* Generated:  Thursday, April 14, 2005                                           */
/*================================================================================*/

/*================================================================================*/
/* Tables                                                                         */
/*================================================================================*/

CREATE TABLE calibers (
    caliber_key INTEGER NOT NULL,
    caliber_name VARCHAR(40) NOT NULL,
    PRIMARY KEY (caliber_key)
);

CREATE TABLE conferences (
    conference_key INTEGER NOT NULL,
    conference_name VARCHAR(40),
    league_key INTEGER NOT NULL,
    PRIMARY KEY (conference_key)
);

CREATE TABLE divisions (
    division_key INTEGER NOT NULL,
    division_name VARCHAR(40),
    conference_key INTEGER NOT NULL,
    league_key INTEGER NOT NULL,
    PRIMARY KEY (division_key)
);

CREATE TABLE event_affiliations (
    event_id INTEGER NOT NULL,
    event_key VARCHAR(40),
    affiliation_type VARCHAR(40),
    affiliation_key INTEGER NOT NULL,
    PRIMARY KEY (event_id)
);

CREATE TABLE event_weather (
    event_id VARCHAR(20),
    event_key INTEGER AUTO_INCREMENT NOT NULL,
    temperature VARCHAR(20),
    humidity VARCHAR(20),
    clouds VARCHAR(20),
    wind_direction VARCHAR(20),
    wind_velocity VARCHAR(20)
);

CREATE TABLE events (
    id INTEGER AUTO_INCREMENT NOT NULL,
    event_key VARCHAR(20),
    publisher_key VARCHAR(40),
    site_key INTEGER NOT NULL,
    start_date_time VARCHAR(20),
    site_alignment VARCHAR(20),
    event_status VARCHAR(20),
    season_key INTEGER NOT NULL,
    season_type VARCHAR(40),
    duration VARCHAR(20),
    attendance VARCHAR(20),
    PRIMARY KEY (id)
);

CREATE TABLE events_players (
    event_id INTEGER NOT NULL,
    event_key INTEGER AUTO_INCREMENT NOT NULL,
    player_key VARCHAR(20) NOT NULL,
    score VARCHAR(20)
);

CREATE TABLE events_teams (
    event_id VARCHAR(20) NOT NULL,
    event_key INTEGER AUTO_INCREMENT NOT NULL,
    team_key VARCHAR(20) NOT NULL,
    alignment VARCHAR(20),
    score VARCHAR(20)
);

CREATE TABLE leagues (
    league_key INTEGER NOT NULL,
    league_name VARCHAR(40),
    league_alias VARCHAR(40),
    caliber_key INTEGER NOT NULL,
    organization_key INTEGER NOT NULL,
    sport_key INTEGER NOT NULL,
    PRIMARY KEY (league_key)
);

CREATE TABLE organizations (
    organization_key INTEGER NOT NULL,
    organization_name VARCHAR(40),
    PRIMARY KEY (organization_key)
);

CREATE TABLE player_career_history (
    player_key VARCHAR(20) NOT NULL,
    team_key INTEGER NOT NULL,
    uniform_number VARCHAR(20),
    regular_position VARCHAR(20),
    start_date_time VARCHAR(20),
    end_date_time VARCHAR(20),
    start_season_key INTEGER NOT NULL,
    start_season_type VARCHAR(20),
    end_season_key VARCHAR(40),
    end_season_type VARCHAR(40),
    height VARCHAR(40),
    weight VARCHAR(40)
);

CREATE TABLE player_injury_history (
    player_key VARCHAR(20) NOT NULL,
    injury_status VARCHAR(20),
    injury_type VARCHAR(20),
    injury_comment VARCHAR(20),
    disabled_list VARCHAR(20),
    start_date_time VARCHAR(20),
    end_date_time VARCHAR(20),
    season_key INTEGER NOT NULL,
    season_type VARCHAR(20)
);

CREATE TABLE players (
    player_key VARCHAR(20) NOT NULL,
    given_name VARCHAR(20),
    middle_name VARCHAR(20),
    family_name VARCHAR(20),
    nickname VARCHAR(20),
    full_name VARCHAR(20),
    birth_date VARCHAR(20),
    gender VARCHAR(20),
    birth_city VARCHAR(20),
    birth_state VARCHAR(20),
    birth_country VARCHAR(20),
    death_date VARCHAR(20),
    PRIMARY KEY (player_key)
);

CREATE TABLE seasons (
    season_key INTEGER NOT NULL,
    league_key INTEGER NOT NULL,
    preseason_start_date_time VARCHAR(40),
    regular_season_start_date_time VARCHAR(40),
    postseason_start_date_time VARCHAR(40),
    postseason_end_date_time VARCHAR(40),
    PRIMARY KEY (season_key)
);

CREATE TABLE sites (
    site_key INTEGER NOT NULL,
    site_name VARCHAR(40),
    site_country INTEGER NOT NULL,
    site_state_province INTEGER NOT NULL,
    site_city VARCHAR(40),
    site_postal_code VARCHAR(40),
    site_postal_code_extension VARCHAR(40),
    site_timezone VARCHAR(40),
    site_address_1 VARCHAR(40),
    site_address_2 VARCHAR(40),
    PRIMARY KEY (site_key)
);

CREATE TABLE sports (
    sport_key INTEGER NOT NULL,
    sport_name VARCHAR(40),
    sport_alias VARCHAR(40),
    PRIMARY KEY (sport_key)
);

CREATE TABLE stats (
    stat_key INTEGER NOT NULL,
    name VARCHAR(40),
    stat_holder_type VARCHAR(40),
    stat_holder_key VARCHAR(40),
    stat_coverage_type VARCHAR(40),
    stat_coverage_key VARCHAR(40),
    stat_table_name VARCHAR(40),
    PRIMARY KEY (stat_key)
);

CREATE TABLE stats_baseball_defensive (
    stat_key INTEGER NOT NULL,
    double_plays VARCHAR(40),
    triple_plays VARCHAR(40),
    putouts VARCHAR(40),
    assists VARCHAR(40),
    errors VARCHAR(40),
    fielding_percentage VARCHAR(40),
    defensive_average VARCHAR(40),
    errors_passed_ball VARCHAR(40),
    errors_catchers_interference VARCHAR(40)
);

CREATE TABLE stats_baseball_offensive (
    stat_key INTEGER NOT NULL,
    average VARCHAR(40),
    runs_scored VARCHAR(40),
    at_bats VARCHAR(40),
    hits VARCHAR(40),
    rbi VARCHAR(40),
    total_bases VARCHAR(40),
    slugging_percentage VARCHAR(40),
    bases_on_balls VARCHAR(40),
    strikeouts VARCHAR(40),
    left_on_base VARCHAR(40),
    left_in_scoring_position VARCHAR(40),
    singles VARCHAR(40),
    doubles VARCHAR(40),
    triples VARCHAR(40),
    home_runs VARCHAR(40),
    grand_slams VARCHAR(40),
    at_bats_per_rbi VARCHAR(40),
    plate_appearances_per_rbi VARCHAR(40),
    at_bats_per_home_run VARCHAR(40),
    plate_appearances_per_home_run VARCHAR(40),
    sac_flies VARCHAR(40),
    sac_bunts VARCHAR(40),
    grounded_into_double_play VARCHAR(40),
    moved_up VARCHAR(40),
    on_base_percentage VARCHAR(40),
    stolen_bases VARCHAR(40),
    stolen_bases_caught VARCHAR(40),
    stolen_bases_average VARCHAR(40),
    hit_by_pitch VARCHAR(40),
    defensive_interferance_reaches VARCHAR(40),
    on_base_plus_slugging VARCHAR(40),
    plate_appearances VARCHAR(40),
    hits_extra_base VARCHAR(40)
);

CREATE TABLE stats_baseball_pitching (
    stat_key INTEGER NOT NULL,
    runs_allowed VARCHAR(40),
    home_runs_allowed VARCHAR(40),
    innings_pitched VARCHAR(40),
    hits VARCHAR(40),
    earned_runs VARCHAR(40),
    unearned_runs VARCHAR(40),
    bases_on_balls VARCHAR(40),
    bases_on_balls_intentional VARCHAR(40),
    strikeouts VARCHAR(40),
    strikeout_to_bb_ratio VARCHAR(40),
    number_of_pitches VARCHAR(40),
    era VARCHAR(40),
    inherited_runners_scored VARCHAR(40),
    pick_offs VARCHAR(40),
    errors_hit_with_pitch VARCHAR(40),
    errors_wild_pitch VARCHAR(40),
    balks VARCHAR(40),
    wins VARCHAR(40),
    losses VARCHAR(40),
    saves VARCHAR(40),
    shutouts VARCHAR(40),
    games_complete VARCHAR(40),
    games_finished VARCHAR(40),
    winning_percentage VARCHAR(40),
    event_credit VARCHAR(40),
    save_credit VARCHAR(40)
);

CREATE TABLE subscores (
    event_id VARCHAR(40),
    event_key INTEGER AUTO_INCREMENT NOT NULL,
    period VARCHAR(40),
    score VARCHAR(40)
);

CREATE TABLE teams (
    id INTEGER NOT NULL,
    team_key VARCHAR(20),
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    abbreviation VARCHAR(20),
    short_name VARCHAR(20),
    home_site_key INTEGER NOT NULL,
    division_key INTEGER NOT NULL,
    conference_key INTEGER NOT NULL,
    league_key INTEGER NOT NULL,
    PRIMARY KEY (id)
);

/*================================================================================*/
/* Foreignkeys                                                                    */
/*================================================================================*/

ALTER TABLE leagues
    ADD FOREIGN KEY (caliber_key) REFERENCES calibers (caliber_key);

ALTER TABLE divisions
    ADD FOREIGN KEY (conference_key) REFERENCES conferences (conference_key);

ALTER TABLE teams
    ADD FOREIGN KEY (conference_key) REFERENCES conferences (conference_key);

ALTER TABLE teams
    ADD FOREIGN KEY (division_key) REFERENCES divisions (division_key);

ALTER TABLE events_players
    ADD FOREIGN KEY (event_id) REFERENCES events (id);

ALTER TABLE events_teams
    ADD FOREIGN KEY (event_id) REFERENCES events (id);

ALTER TABLE event_weather
    ADD FOREIGN KEY (event_id) REFERENCES events (id);

ALTER TABLE subscores
    ADD FOREIGN KEY (event_id) REFERENCES events (id);

ALTER TABLE event_affiliations
    ADD FOREIGN KEY (event_id) REFERENCES events (id);

ALTER TABLE event_affiliations
    ADD FOREIGN KEY (event_key) REFERENCES events (event_key);

ALTER TABLE event_weather
    ADD FOREIGN KEY (event_key) REFERENCES events (event_key);

ALTER TABLE subscores
    ADD FOREIGN KEY (event_key) REFERENCES events (event_key);

ALTER TABLE events_players
    ADD FOREIGN KEY (event_key) REFERENCES events (event_key);

ALTER TABLE events_teams
    ADD FOREIGN KEY (event_key) REFERENCES events (event_key);

ALTER TABLE seasons
    ADD FOREIGN KEY (league_key) REFERENCES leagues (league_key);

ALTER TABLE conferences
    ADD FOREIGN KEY (league_key) REFERENCES leagues (league_key);

ALTER TABLE divisions
    ADD FOREIGN KEY (league_key) REFERENCES leagues (league_key);

ALTER TABLE teams
    ADD FOREIGN KEY (league_key) REFERENCES leagues (league_key);

ALTER TABLE leagues
    ADD FOREIGN KEY (organization_key) REFERENCES organizations (organization_key);

ALTER TABLE events_players
    ADD FOREIGN KEY (player_key) REFERENCES players (player_key);

ALTER TABLE player_injury_history
    ADD FOREIGN KEY (player_key) REFERENCES players (player_key);

ALTER TABLE player_career_history
    ADD FOREIGN KEY (player_key) REFERENCES players (player_key);

ALTER TABLE events
    ADD FOREIGN KEY (season_key) REFERENCES seasons (season_key);

ALTER TABLE player_injury_history
    ADD FOREIGN KEY (season_key) REFERENCES seasons (season_key);

ALTER TABLE teams
    ADD FOREIGN KEY (home_site_key) REFERENCES sites (site_key);

ALTER TABLE events
    ADD FOREIGN KEY (site_key) REFERENCES sites (site_key);

ALTER TABLE leagues
    ADD FOREIGN KEY (sport_key) REFERENCES sports (sport_key);

ALTER TABLE stats_baseball_offensive
    ADD FOREIGN KEY (stat_key) REFERENCES stats (stat_key);

ALTER TABLE stats_baseball_defensive
    ADD FOREIGN KEY (stat_key) REFERENCES stats (stat_key);

ALTER TABLE stats_baseball_pitching
    ADD FOREIGN KEY (stat_key) REFERENCES stats (stat_key);

ALTER TABLE events_teams
    ADD FOREIGN KEY (team_key) REFERENCES teams (team_key);

ALTER TABLE player_career_history
    ADD FOREIGN KEY (team_key) REFERENCES teams (team_key);

/*================================================================================*/
/* Indexes                                                                        */
/*================================================================================*/



/* -- END OF SCRIPT -- */