· 7 years ago · Feb 21, 2019, 11:38 PM
1
2-- -----------------------------------------------------
3-- Table `work_rate`
4-- -----------------------------------------------------
5CREATE TABLE IF NOT EXISTS `work_rate` (
6 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
7 `workRate` VARCHAR(45) NOT NULL,
8 PRIMARY KEY (`id`),
9 UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
10 UNIQUE INDEX `workRate_UNIQUE` (`workRate` ASC) )
11ENGINE = InnoDB;
12
13
14-- -----------------------------------------------------
15-- Table `position`
16-- -----------------------------------------------------
17CREATE TABLE IF NOT EXISTS `position` (
18 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
19 `position` VARCHAR(45) NULL,
20 PRIMARY KEY (`id`))
21ENGINE = InnoDB;
22
23
24-- -----------------------------------------------------
25-- Table `s1133750_fifa`.`body_type`
26-- -----------------------------------------------------
27CREATE TABLE IF NOT EXISTS `body_type` (
28 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
29 `name` VARCHAR(20) NOT NULL,
30 PRIMARY KEY (`id`),
31 UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
32 UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
33ENGINE = InnoDB
34DEFAULT CHARACTER SET = utf8;
35
36
37-- -----------------------------------------------------
38-- Table `s1133750_fifa`.`club`
39-- -----------------------------------------------------
40CREATE TABLE IF NOT EXISTS `club` (
41 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
42 `name` VARCHAR(139) NULL DEFAULT NULL,
43 `logo` VARCHAR(50) NULL DEFAULT NULL,
44 PRIMARY KEY (`id`),
45 UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
46 UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
47ENGINE = InnoDB
48DEFAULT CHARACTER SET = utf8;
49
50
51-- -----------------------------------------------------
52-- Table `s1133750_fifa`.`country`
53-- -----------------------------------------------------
54CREATE TABLE IF NOT EXISTS `country` (
55 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
56 `name` VARCHAR(25) NOT NULL,
57 `flag_url` VARCHAR(40) NOT NULL,
58 PRIMARY KEY (`id`),
59 UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
60 UNIQUE INDEX `name_UNIQUE` (`name` ASC) ,
61 UNIQUE INDEX `flag_url_UNIQUE` (`flag_url` ASC) )
62ENGINE = InnoDB
63DEFAULT CHARACTER SET = utf8;
64
65
66-- -----------------------------------------------------
67-- Table `s1133750_fifa`.`prefered_foot`
68-- -----------------------------------------------------
69CREATE TABLE IF NOT EXISTS `prefered_foot` (
70 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
71 `name` VARCHAR(10) NOT NULL,
72 PRIMARY KEY (`id`),
73 UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
74 UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
75ENGINE = InnoDB
76DEFAULT CHARACTER SET = utf8;
77
78
79-- -----------------------------------------------------
80-- Table `s1133750_fifa`.`player`
81-- -----------------------------------------------------
82CREATE TABLE IF NOT EXISTS `player` (
83 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
84 `name` VARCHAR(45) NOT NULL,
85 `age` INT(10) UNSIGNED NOT NULL,
86 `realFace` VARCHAR(3) NULL,
87 `photo_url` VARCHAR(50) NOT NULL,
88 `club_id` INT(10) UNSIGNED NULL DEFAULT NULL,
89 `position_id` INT UNSIGNED NULL,
90 `jerseyNumber` INT UNSIGNED NULL,
91 `country_id` INT(10) UNSIGNED NOT NULL,
92 `prefered_foot_id` INT(10) UNSIGNED NULL DEFAULT NULL,
93 `body_type_id` INT(10) UNSIGNED NULL DEFAULT NULL,
94 `work_rate_id` INT UNSIGNED NULL,
95 `special` INT NULL,
96 `internationalReputation` INT NULL,
97 `overall` INT NULL,
98 `potential` INT NULL,
99 PRIMARY KEY (`id`),
100 UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
101 INDEX `fk_player_club_idx` (`club_id` ASC) ,
102 INDEX `fk_player_country1_idx` (`country_id` ASC) ,
103 INDEX `fk_player_body_type1_idx` (`body_type_id` ASC) ,
104 INDEX `fk_player_prefered_foot1_idx` (`prefered_foot_id` ASC) ,
105 INDEX `fk_player_position1_idx` (`position_id` ASC) ,
106 INDEX `fk_player_work_rate1_idx` (`work_rate_id` ASC) ,
107 CONSTRAINT `fk_player_body_type1`
108 FOREIGN KEY (`body_type_id`)
109 REFERENCES `body_type` (`id`)
110 ON DELETE CASCADE
111 ON UPDATE CASCADE,
112 CONSTRAINT `fk_player_club`
113 FOREIGN KEY (`club_id`)
114 REFERENCES `club` (`id`)
115 ON DELETE CASCADE
116 ON UPDATE CASCADE,
117 CONSTRAINT `fk_player_country1`
118 FOREIGN KEY (`country_id`)
119 REFERENCES `country` (`id`)
120 ON DELETE CASCADE
121 ON UPDATE CASCADE,
122 CONSTRAINT `fk_player_prefered_foot1`
123 FOREIGN KEY (`prefered_foot_id`)
124 REFERENCES `prefered_foot` (`id`)
125 ON DELETE NO ACTION
126 ON UPDATE NO ACTION,
127 CONSTRAINT `fk_player_position1`
128 FOREIGN KEY (`position_id`)
129 REFERENCES `position` (`id`)
130 ON DELETE NO ACTION
131 ON UPDATE NO ACTION,
132 CONSTRAINT `fk_player_work_rate1`
133 FOREIGN KEY (`work_rate_id`)
134 REFERENCES `work_rate` (`id`)
135 ON DELETE NO ACTION
136 ON UPDATE NO ACTION)
137ENGINE = InnoDB
138DEFAULT CHARACTER SET = utf8;
139
140
141-- -----------------------------------------------------
142-- Table `s1133750_fifa`.`player_contract`
143-- -----------------------------------------------------
144CREATE TABLE IF NOT EXISTS `player_contract` (
145 `player_id` INT(10) UNSIGNED NOT NULL,
146 `joined_date` INT(11) NULL DEFAULT NULL,
147 `value` INT(10) NULL,
148 `wage` INT(10) NULL DEFAULT NULL,
149 `contract_valid_untill` VARCHAR(10) NULL DEFAULT NULL,
150 `release_clause` INT(10) NULL DEFAULT NULL,
151 PRIMARY KEY (`player_id`),
152 CONSTRAINT `fk_player_contract_player1`
153 FOREIGN KEY (`player_id`)
154 REFERENCES `player` (`id`)
155 ON DELETE CASCADE
156 ON UPDATE CASCADE)
157ENGINE = InnoDB
158DEFAULT CHARACTER SET = utf8;
159
160
161-- -----------------------------------------------------
162-- Table `s1133750_fifa`.`player_stats`
163-- -----------------------------------------------------
164CREATE TABLE IF NOT EXISTS `player_stats` (
165 `player_id` INT(10) UNSIGNED NOT NULL,
166 `height` VARCHAR(4) NULL,
167 `weight` INT(3) NULL,
168 `weakFoot` INT NULL,
169 `skillMoves` INT NULL,
170 `ls` VARCHAR(5) NULL,
171 `st` VARCHAR(5) NULL,
172 `rs` VARCHAR(5) NULL,
173 `lw` VARCHAR(5) NULL,
174 `lf` VARCHAR(5) NULL,
175 `cf` VARCHAR(5) NULL,
176 `rf` VARCHAR(5) NULL,
177 `rw` VARCHAR(5) NULL,
178 `lam` VARCHAR(5) NULL,
179 `cam` VARCHAR(5) NULL,
180 `ram` VARCHAR(5) NULL,
181 `lm` VARCHAR(5) NULL,
182 `lcm` VARCHAR(5) NULL,
183 `cm` VARCHAR(5) NULL,
184 `rcm` VARCHAR(5) NULL,
185 `rm` VARCHAR(5) NULL,
186 `lwb` VARCHAR(5) NULL,
187 `ldm` VARCHAR(5) NULL,
188 `cdm` VARCHAR(5) NULL,
189 `rdm` VARCHAR(5) NULL,
190 `rwb` VARCHAR(5) NULL,
191 `lb` VARCHAR(5) NULL,
192 `lcb` VARCHAR(5) NULL,
193 `cb` VARCHAR(5) NULL,
194 `rcb` VARCHAR(5) NULL,
195 `rb` VARCHAR(5) NULL,
196 `crossing` INT(3) NULL,
197 `finishing` INT(3) NULL,
198 `headingAccuracy` INT(3) NULL,
199 `shortPassing` INT(3) NULL,
200 `volleys` INT(3) NULL,
201 `dribbling` INT(3) NULL,
202 `curve` INT(3) NULL,
203 `fKAccuracy` INT(3) NULL,
204 `longPassing` INT(3) NULL,
205 `ballControl` INT(3) NULL,
206 `acceleration` INT(3) NULL,
207 `sprintSpeed` INT(3) NULL,
208 `agility` INT(3) NULL,
209 `reactions` INT(3) NULL,
210 `balance` INT(3) NULL,
211 `shotPower` INT(3) NULL,
212 `jumping` INT(3) NULL,
213 `stamina` INT(3) NULL,
214 `strenght` INT(3) NULL,
215 `longShots` INT(3) NULL,
216 `aggresssion` INT(3) NULL,
217 `interception` INT(3) NULL,
218 `positioning` INT(3) NULL,
219 `vision` INT(3) NULL,
220 `penalties` INT(3) NULL,
221 `composure` INT(3) NULL,
222 `marking` INT(3) NULL,
223 `standingTackle` INT(3) NULL,
224 `slidingTackle` INT(3) NULL,
225 `GKDiving` INT(3) NULL,
226 `GKHandling` INT(3) NULL,
227 `GKKicking` INT(3) NULL,
228 `GKPositioning` INT(3) NULL,
229 `GKReflexes` INT(3) NULL,
230 PRIMARY KEY (`player_id`),
231 CONSTRAINT `fk_player_stats_player1`
232 FOREIGN KEY (`player_id`)
233 REFERENCES `player` (`id`)
234 ON DELETE CASCADE
235 ON UPDATE CASCADE)
236ENGINE = InnoDB
237DEFAULT CHARACTER SET = utf8;