The following lines of text are the SQL statements used to create the physical design of the volleyball database and add players to the database. These statements are executed with MySQL and may not work for other databases.
CREATE DATABASE volleyball; USE volleyball; CREATE TABLE season ( season_year INT(4) NOT NULL, season_location VARCHAR(45) NOT NULL, team_name VARCHAR(45) NOT NULL, player_num INT(2) NOT NULL, first_name VARCHAR(20) NOT NULL, last_name VARCHAR (20) NOT NULL, position VARCHAR (20) NOT NULL, PRIMARY KEY(player_num, team_name, season_year, season_location) ); Player tables are created with each added player CREATE TABLE SEASONYEAR_SEASONLOCATION_TEAMNAME_PLAYERNUM ( match_date VARCHAR(8) NOT NULL, opponent VARCHAR(45) NOT NULL, match_score VARCHAR(7), game1_score VARCHAR(7), game2_score VARCHAR(7), game3_score VARCHAR(7), game4_score VARCHAR(7), stats_gp INT(1), stats_attack_attempt INT(2) NOT NULL, stats_overhand_kill INT(2) NOT NULL, stats_overpass INT(2) NOT NULL, stats_attack_error INT(2) NOT NULL, stats_pct FLOAT(5,3) NOT NULL, stats_a INT(2) NOT NULL, stats_sa INT(2) NOT NULL, stats_se INT(2) NOT NULL, stats_sr3 INT(2) NOT NULL, stats_sr2 INT(2) NOT NULL, stats_sr1 INT(2) NOT NULL, stats_srn INT(2) NOT NULL, stats_re INT(2) NOT NULL, stats_digs INT(2) NOT NULL, stats_bs INT(2) NOT NULL, stats_ba INT(2) NOT NULL, stats_be INT(2) NOT NULL, stats_bhe INT(2) NOT NULL, PRIMARY KEY(match_date) ); |