· 6 years ago · Nov 05, 2019, 05:18 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema mlb_juiced_balls
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema mlb_juiced_balls
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mlb_juiced_balls` DEFAULT CHARACTER SET utf8 ;
15USE `mlb_juiced_balls` ;
16
17-- -----------------------------------------------------
18-- Table `mlb_juiced_balls`.`season_2019`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mlb_juiced_balls`.`season_2019` (
21 `Team_ID` VARCHAR(3) NOT NULL,
22 `runs_per_game19` DECIMAL(10,2) NULL,
23 `at_bats19` DECIMAL(10,2) NULL,
24 `runs19` DECIMAL(10,2) NULL,
25 `hits19` DECIMAL(10,2) NULL,
26 `d0uble19` DECIMAL(10,2) NULL,
27 `triple19` DECIMAL(10,2) NULL,
28 `home_runs19` DECIMAL(10,2) NULL,
29 `runs_batted_in19` DECIMAL(10,2) NULL,
30 `bases_on_balls19` DECIMAL(10,2) NULL,
31 `batting_avg19` DECIMAL(10,3) NULL,
32 `on_base_percentage19` DECIMAL(10,3) NULL,
33 `slugging_percentage19` DECIMAL(10,3) NULL,
34 `total_bases19` DECIMAL(10,2) NULL,
35 `season_2019col` INT NULL,
36 PRIMARY KEY (`Team_ID`))
37ENGINE = InnoDB;
38
39
40-- -----------------------------------------------------
41-- Table `mlb_juiced_balls`.`season_2018`
42-- -----------------------------------------------------
43CREATE TABLE IF NOT EXISTS `mlb_juiced_balls`.`season_2018` (
44 `Team_ID` VARCHAR(3) NOT NULL,
45 `runs_per_game18` DECIMAL(10,2) NULL,
46 `at_bats18` DECIMAL(10,2) NULL,
47 `runs18` DECIMAL(10,2) NULL,
48 `hits18` DECIMAL(10,2) NULL,
49 `d0uble18` DECIMAL(10,2) NULL,
50 `triple18` DECIMAL(10,2) NULL,
51 `home_runs18` DECIMAL(10,2) NULL,
52 `runs_batted_in18` DECIMAL(10,2) NULL,
53 `bases_on_balls18` DECIMAL(10,2) NULL,
54 `batting_avg18` DECIMAL(10,3) NULL,
55 `on_base_percentage18` DECIMAL(10,3) NULL,
56 `slugging_percentage18` DECIMAL(10,3) NULL,
57 `total_bases18` DECIMAL(10,2) NULL,
58 PRIMARY KEY (`Team_ID`))
59ENGINE = InnoDB;
60
61
62-- -----------------------------------------------------
63-- Table `mlb_juiced_balls`.`season_2017`
64-- -----------------------------------------------------
65CREATE TABLE IF NOT EXISTS `mlb_juiced_balls`.`season_2017` (
66 `Team_ID` VARCHAR(3) NOT NULL,
67 `runs_per_game17` DECIMAL(10,2) NULL,
68 `at_bats17` DECIMAL(10,2) NULL,
69 `runs17` DECIMAL(10,2) NULL,
70 `hits17` DECIMAL(10,2) NULL,
71 `d0uble17` DECIMAL(10,2) NULL,
72 `triple17` DECIMAL(10,2) NULL,
73 `home_runs17` DECIMAL(10,2) NULL,
74 `runs_batted_in17` DECIMAL(10,2) NULL,
75 `bases_on_balls17` DECIMAL(10,2) NULL,
76 `batting_avg17` DECIMAL(10,3) NULL,
77 `on_base_percentage17` DECIMAL(10,3) NULL,
78 `slugging_percentage17` DECIMAL(10,3) NULL,
79 `total_bases17` DECIMAL(10,2) NULL,
80 PRIMARY KEY (`Team_ID`))
81ENGINE = InnoDB;
82
83
84-- -----------------------------------------------------
85-- Table `mlb_juiced_balls`.`season_2016`
86-- -----------------------------------------------------
87CREATE TABLE IF NOT EXISTS `mlb_juiced_balls`.`season_2016` (
88 `Team_ID` VARCHAR(3) NOT NULL,
89 `runs_per_game16` DECIMAL(10,2) NULL,
90 `at_bats16` DECIMAL(10,2) NULL,
91 `runs16` DECIMAL(10,2) NULL,
92 `hits16` DECIMAL(10,2) NULL,
93 `d0uble16` DECIMAL(10,2) NULL,
94 `triple16` DECIMAL(10,2) NULL,
95 `home_runs16` DECIMAL(10,2) NULL,
96 `runs_batted_in16` DECIMAL(10,2) NULL,
97 `bases_on_balls16` DECIMAL(10,2) NULL,
98 `batting_avg16` DECIMAL(10,3) NULL,
99 `on_base_percentage16` DECIMAL(10,3) NULL,
100 `slugging_percentage16` DECIMAL(10,3) NULL,
101 `total_bases16` DECIMAL(10,2) NULL,
102 PRIMARY KEY (`Team_ID`))
103ENGINE = InnoDB;
104
105
106-- -----------------------------------------------------
107-- Table `mlb_juiced_balls`.`season_2015`
108-- -----------------------------------------------------
109CREATE TABLE IF NOT EXISTS `mlb_juiced_balls`.`season_2015` (
110 `Team_ID` VARCHAR(3) NOT NULL,
111 `runs_per_game15` DECIMAL(10,2) NULL,
112 `at_bats15` DECIMAL(10,2) NULL,
113 `runs15` DECIMAL(10,2) NULL,
114 `hits15` DECIMAL(10,2) NULL,
115 `d0uble15` DECIMAL(10,2) NULL,
116 `triple15` DECIMAL(10,2) NULL,
117 `home_runs15` DECIMAL(10,2) NULL,
118 `runs_batted_in15` DECIMAL(10,2) NULL,
119 `bases_on_balls15` DECIMAL(10,2) NULL,
120 `batting_avg15` DECIMAL(10,3) NULL,
121 `on_base_percentage15` DECIMAL(10,3) NULL,
122 `slugging_percentage15` DECIMAL(10,3) NULL,
123 `total_bases15` DECIMAL(10,2) NULL,
124 PRIMARY KEY (`Team_ID`))
125ENGINE = InnoDB;
126
127
128-- -----------------------------------------------------
129-- Table `mlb_juiced_balls`.`MLB_Teams`
130-- -----------------------------------------------------
131CREATE TABLE IF NOT EXISTS `mlb_juiced_balls`.`MLB_Teams` (
132 `Team_ID` VARCHAR(3) NOT NULL,
133 `Team_Name` VARCHAR(100) NULL,
134 `season_2019_Team_ID` VARCHAR(3) NULL,
135 `season_2018_Team_ID` VARCHAR(3) NULL,
136 `season_2017_Team_ID` VARCHAR(3) NULL,
137 `season_2016_Team_ID` VARCHAR(3) NULL,
138 `season_2015_Team_ID` VARCHAR(3) NULL,
139 PRIMARY KEY (`Team_ID`),
140 INDEX `fk_MLB_Teams_season_2019_idx` (`season_2019_Team_ID` ASC) INVISIBLE,
141 INDEX `fk_MLB_Teams_season_20181_idx` (`season_2018_Team_ID` ASC) INVISIBLE,
142 INDEX `fk_MLB_Teams_season_20171_idx` (`season_2017_Team_ID` ASC) INVISIBLE,
143 INDEX `fk_MLB_Teams_season_20161_idx` (`season_2016_Team_ID` ASC) INVISIBLE,
144 INDEX `fk_MLB_Teams_season_20151_idx` (`season_2015_Team_ID` ASC) INVISIBLE,
145 CONSTRAINT `fk_MLB_Teams_season_2019`
146 FOREIGN KEY (`season_2019_Team_ID`)
147 REFERENCES `mlb_juiced_balls`.`season_2019` (`Team_ID`)
148 ON DELETE NO ACTION
149 ON UPDATE NO ACTION,
150 CONSTRAINT `fk_MLB_Teams_season_20181`
151 FOREIGN KEY (`season_2018_Team_ID`)
152 REFERENCES `mlb_juiced_balls`.`season_2018` (`Team_ID`)
153 ON DELETE NO ACTION
154 ON UPDATE NO ACTION,
155 CONSTRAINT `fk_MLB_Teams_season_20171`
156 FOREIGN KEY (`season_2017_Team_ID`)
157 REFERENCES `mlb_juiced_balls`.`season_2017` (`Team_ID`)
158 ON DELETE NO ACTION
159 ON UPDATE NO ACTION,
160 CONSTRAINT `fk_MLB_Teams_season_20161`
161 FOREIGN KEY (`season_2016_Team_ID`)
162 REFERENCES `mlb_juiced_balls`.`season_2016` (`Team_ID`)
163 ON DELETE NO ACTION
164 ON UPDATE NO ACTION,
165 CONSTRAINT `fk_MLB_Teams_season_20151`
166 FOREIGN KEY (`season_2015_Team_ID`)
167 REFERENCES `mlb_juiced_balls`.`season_2015` (`Team_ID`)
168 ON DELETE NO ACTION
169 ON UPDATE NO ACTION)
170ENGINE = InnoDB;
171
172
173SET SQL_MODE=@OLD_SQL_MODE;
174SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
175SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;