· 6 years ago · Mar 24, 2019, 11:00 AM
1-- MySQL Script generated by MySQL Workbench
2-- mar. 19 mars 2019 10:30:09 CET
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema mydb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`CLUB`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`CLUB` (
24 `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
25 `NOM` VARCHAR(200) NOT NULL,
26 `HORAIRES` VARCHAR(1000) NOT NULL,
27 `TEL` CHAR(10) NOT NULL,
28 `EMAIL` VARCHAR(400) NOT NULL,
29 `ADRESSE` VARCHAR(45) NOT NULL,
30 PRIMARY KEY (`ID`))
31ENGINE = InnoDB;
32
33
34-- -----------------------------------------------------
35-- Table `mydb`.`ENTRAINEMENT`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `mydb`.`ENTRAINEMENT` (
38 `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
39 `HEURE_DEBUT` DATETIME NOT NULL,
40 `HEURE_FIN` DATETIME NOT NULL,
41 `NIVEAU` ENUM('D', 'I', 'A') NOT NULL,
42 PRIMARY KEY (`ID`))
43ENGINE = InnoDB;
44
45
46-- -----------------------------------------------------
47-- Table `mydb`.`AVATAR`
48-- -----------------------------------------------------
49CREATE TABLE IF NOT EXISTS `mydb`.`AVATAR` (
50 `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
51 `CONTENT` BLOB NULL,
52 PRIMARY KEY (`ID`))
53ENGINE = InnoDB;
54
55
56-- -----------------------------------------------------
57-- Table `mydb`.`ABONNE`
58-- -----------------------------------------------------
59CREATE TABLE IF NOT EXISTS `mydb`.`ABONNE` (
60 `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
61 `PRENOM` VARCHAR(100) NOT NULL,
62 `NOM` VARCHAR(100) NOT NULL,
63 `PSEUDO` VARCHAR(100) NULL,
64 `EMAIL` VARCHAR(200) NOT NULL,
65 `MDP` CHAR(16) NOT NULL,
66 `REDUCTION` TINYINT(1) NOT NULL DEFAULT 0,
67 `DESCRIPTION` VARCHAR(2000) NULL,
68 `NIVEAU` ENUM('D', 'I', 'A') NOT NULL,
69 `CLUB_ID` INT UNSIGNED NOT NULL,
70 `ENTRAINEMENT_ID` INT UNSIGNED NOT NULL,
71 `AVATAR_ID` INT UNSIGNED NOT NULL,
72 PRIMARY KEY (`ID`),
73 INDEX `fk_ABONNE_CLUB1_idx` (`CLUB_ID` ASC),
74 INDEX `fk_ABONNE_ENTRAINEMENT1_idx` (`ENTRAINEMENT_ID` ASC),
75 INDEX `fk_ABONNE_AVATAR1_idx` (`AVATAR_ID` ASC),
76 CONSTRAINT `fk_ABONNE_CLUB1`
77 FOREIGN KEY (`CLUB_ID`)
78 REFERENCES `mydb`.`CLUB` (`ID`)
79 ON DELETE NO ACTION
80 ON UPDATE NO ACTION,
81 CONSTRAINT `fk_ABONNE_ENTRAINEMENT1`
82 FOREIGN KEY (`ENTRAINEMENT_ID`)
83 REFERENCES `mydb`.`ENTRAINEMENT` (`ID`)
84 ON DELETE NO ACTION
85 ON UPDATE NO ACTION,
86 CONSTRAINT `fk_ABONNE_AVATAR1`
87 FOREIGN KEY (`AVATAR_ID`)
88 REFERENCES `mydb`.`AVATAR` (`ID`)
89 ON DELETE NO ACTION
90 ON UPDATE NO ACTION)
91ENGINE = InnoDB;
92
93
94-- -----------------------------------------------------
95-- Table `mydb`.`TERRAIN`
96-- -----------------------------------------------------
97CREATE TABLE IF NOT EXISTS `mydb`.`TERRAIN` (
98 `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
99 `COUVERT` TINYINT(1) NOT NULL DEFAULT 0,
100 `TYPE` ENUM('TERRE BATTUE', 'GAZON', 'SYNTHETIQUE') NOT NULL,
101 `CLUB_ID` INT UNSIGNED NOT NULL,
102 PRIMARY KEY (`ID`),
103 INDEX `fk_TERRAIN_CLUB1_idx` (`CLUB_ID` ASC),
104 CONSTRAINT `fk_TERRAIN_CLUB1`
105 FOREIGN KEY (`CLUB_ID`)
106 REFERENCES `mydb`.`CLUB` (`ID`)
107 ON DELETE NO ACTION
108 ON UPDATE NO ACTION)
109ENGINE = InnoDB;
110
111
112-- -----------------------------------------------------
113-- Table `mydb`.`MATCH`
114-- -----------------------------------------------------
115CREATE TABLE IF NOT EXISTS `mydb`.`MATCH` (
116 `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
117 `HEURE_DEBUT` DATETIME NOT NULL,
118 `HEURE_FIN` DATETIME NOT NULL,
119 `COMPETITION` TINYINT(1) NOT NULL DEFAULT 0,
120 `ABONNE_ID_GAGNANT` INT UNSIGNED NOT NULL,
121 `ABONNE_ID_PERDANT` INT UNSIGNED NOT NULL,
122 `TERRAIN_ID` INT UNSIGNED NOT NULL,
123 PRIMARY KEY (`ID`),
124 INDEX `fk_MATCH_ABONNE1_idx` (`ABONNE_ID_GAGNANT` ASC),
125 INDEX `fk_MATCH_ABONNE2_idx` (`ABONNE_ID_PERDANT` ASC),
126 INDEX `fk_MATCH_TERRAIN1_idx` (`TERRAIN_ID` ASC),
127 CONSTRAINT `fk_MATCH_ABONNE1`
128 FOREIGN KEY (`ABONNE_ID_GAGNANT`)
129 REFERENCES `mydb`.`ABONNE` (`ID`)
130 ON DELETE NO ACTION
131 ON UPDATE NO ACTION,
132 CONSTRAINT `fk_MATCH_ABONNE2`
133 FOREIGN KEY (`ABONNE_ID_PERDANT`)
134 REFERENCES `mydb`.`ABONNE` (`ID`)
135 ON DELETE NO ACTION
136 ON UPDATE NO ACTION,
137 CONSTRAINT `fk_MATCH_TERRAIN1`
138 FOREIGN KEY (`TERRAIN_ID`)
139 REFERENCES `mydb`.`TERRAIN` (`ID`)
140 ON DELETE NO ACTION
141 ON UPDATE NO ACTION)
142ENGINE = InnoDB;
143
144
145-- -----------------------------------------------------
146-- Table `mydb`.`ENTRAINEUR`
147-- -----------------------------------------------------
148CREATE TABLE IF NOT EXISTS `mydb`.`ENTRAINEUR` (
149 `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
150 `PRENOM` VARCHAR(100) NOT NULL,
151 `NOM` VARCHAR(100) NOT NULL,
152 `DESCRIPTION` VARCHAR(2000) NULL,
153 `AVATAR` VARCHAR(45) NULL,
154 PRIMARY KEY (`ID`))
155ENGINE = InnoDB;
156
157
158-- -----------------------------------------------------
159-- Table `mydb`.`CLUB_has_ABONNE`
160-- -----------------------------------------------------
161CREATE TABLE IF NOT EXISTS `mydb`.`CLUB_has_ABONNE` (
162 `CLUB_ID` INT UNSIGNED NOT NULL,
163 `ABONNE_ID` INT UNSIGNED NULL,
164 PRIMARY KEY (`CLUB_ID`, `ABONNE_ID`),
165 INDEX `fk_CLUB_has_ABONNE_ABONNE1_idx` (`ABONNE_ID` ASC),
166 INDEX `fk_CLUB_has_ABONNE_CLUB_idx` (`CLUB_ID` ASC),
167 CONSTRAINT `fk_CLUB_has_ABONNE_CLUB`
168 FOREIGN KEY (`CLUB_ID`)
169 REFERENCES `mydb`.`CLUB` (`ID`)
170 ON DELETE NO ACTION
171 ON UPDATE NO ACTION,
172 CONSTRAINT `fk_CLUB_has_ABONNE_ABONNE1`
173 FOREIGN KEY (`ABONNE_ID`)
174 REFERENCES `mydb`.`ABONNE` (`ID`)
175 ON DELETE NO ACTION
176 ON UPDATE NO ACTION)
177ENGINE = InnoDB;
178
179
180-- -----------------------------------------------------
181-- Table `mydb`.`CLUB_has_ABONNE1`
182-- -----------------------------------------------------
183CREATE TABLE IF NOT EXISTS `mydb`.`CLUB_has_ABONNE1` (
184 `CLUB_ID` INT UNSIGNED NOT NULL,
185 `ABONNE_ID` INT UNSIGNED NOT NULL,
186 PRIMARY KEY (`CLUB_ID`, `ABONNE_ID`),
187 INDEX `fk_CLUB_has_ABONNE1_ABONNE1_idx` (`ABONNE_ID` ASC),
188 INDEX `fk_CLUB_has_ABONNE1_CLUB1_idx` (`CLUB_ID` ASC),
189 CONSTRAINT `fk_CLUB_has_ABONNE1_CLUB1`
190 FOREIGN KEY (`CLUB_ID`)
191 REFERENCES `mydb`.`CLUB` (`ID`)
192 ON DELETE NO ACTION
193 ON UPDATE NO ACTION,
194 CONSTRAINT `fk_CLUB_has_ABONNE1_ABONNE1`
195 FOREIGN KEY (`ABONNE_ID`)
196 REFERENCES `mydb`.`ABONNE` (`ID`)
197 ON DELETE NO ACTION
198 ON UPDATE NO ACTION)
199ENGINE = InnoDB;
200
201
202-- -----------------------------------------------------
203-- Table `mydb`.`GAGNE`
204-- -----------------------------------------------------
205CREATE TABLE IF NOT EXISTS `mydb`.`GAGNE` (
206 `ABONNE_ID` INT UNSIGNED NOT NULL,
207 `MATCH_ID` INT UNSIGNED NOT NULL,
208 PRIMARY KEY (`ABONNE_ID`, `MATCH_ID`),
209 INDEX `fk_ABONNE_has_MATCH_MATCH1_idx` (`MATCH_ID` ASC),
210 INDEX `fk_ABONNE_has_MATCH_ABONNE1_idx` (`ABONNE_ID` ASC),
211 CONSTRAINT `fk_ABONNE_has_MATCH_ABONNE1`
212 FOREIGN KEY (`ABONNE_ID`)
213 REFERENCES `mydb`.`ABONNE` (`ID`)
214 ON DELETE NO ACTION
215 ON UPDATE NO ACTION,
216 CONSTRAINT `fk_ABONNE_has_MATCH_MATCH1`
217 FOREIGN KEY (`MATCH_ID`)
218 REFERENCES `mydb`.`MATCH` (`ID`)
219 ON DELETE NO ACTION
220 ON UPDATE NO ACTION)
221ENGINE = InnoDB;
222
223
224-- -----------------------------------------------------
225-- Table `mydb`.`ABONNE_has_MATCH`
226-- -----------------------------------------------------
227CREATE TABLE IF NOT EXISTS `mydb`.`ABONNE_has_MATCH` (
228 `ABONNE_ID` INT UNSIGNED NOT NULL,
229 `MATCH_ID` INT UNSIGNED NOT NULL,
230 `MATCH_ABONNE_ID_GAGNANT` INT UNSIGNED NOT NULL,
231 `MATCH_ABONNE_ID_PERDANT` INT UNSIGNED NOT NULL,
232 `MATCH_TERRAIN_ID` INT UNSIGNED NOT NULL,
233 PRIMARY KEY (`ABONNE_ID`, `MATCH_ID`, `MATCH_ABONNE_ID_GAGNANT`, `MATCH_ABONNE_ID_PERDANT`, `MATCH_TERRAIN_ID`),
234 INDEX `fk_ABONNE_has_MATCH_MATCH2_idx` (`MATCH_ID` ASC, `MATCH_ABONNE_ID_GAGNANT` ASC, `MATCH_ABONNE_ID_PERDANT` ASC, `MATCH_TERRAIN_ID` ASC),
235 INDEX `fk_ABONNE_has_MATCH_ABONNE2_idx` (`ABONNE_ID` ASC),
236 CONSTRAINT `fk_ABONNE_has_MATCH_ABONNE2`
237 FOREIGN KEY (`ABONNE_ID`)
238 REFERENCES `mydb`.`ABONNE` (`ID`)
239 ON DELETE NO ACTION
240 ON UPDATE NO ACTION,
241 CONSTRAINT `fk_ABONNE_has_MATCH_MATCH2`
242 FOREIGN KEY (`MATCH_ID` , `MATCH_ABONNE_ID_GAGNANT` , `MATCH_ABONNE_ID_PERDANT` , `MATCH_TERRAIN_ID`)
243 REFERENCES `mydb`.`MATCH` (`ID` , `ABONNE_ID_GAGNANT` , `ABONNE_ID_PERDANT` , `TERRAIN_ID`)
244 ON DELETE NO ACTION
245 ON UPDATE NO ACTION)
246ENGINE = InnoDB;
247
248
249-- -----------------------------------------------------
250-- Table `mydb`.`MATCH_has_ABONNE`
251-- -----------------------------------------------------
252CREATE TABLE IF NOT EXISTS `mydb`.`MATCH_has_ABONNE` (
253 `ABONNE_ID` INT UNSIGNED NOT NULL,
254 `MATCH_ID` INT UNSIGNED NOT NULL,
255 PRIMARY KEY (`ABONNE_ID`, `MATCH_ID`),
256 INDEX `fk_ABONNE_has_MATCH1_MATCH1_idx` (`MATCH_ID` ASC),
257 INDEX `fk_ABONNE_has_MATCH1_ABONNE1_idx` (`ABONNE_ID` ASC),
258 CONSTRAINT `fk_ABONNE_has_MATCH1_ABONNE1`
259 FOREIGN KEY (`ABONNE_ID`)
260 REFERENCES `mydb`.`ABONNE` (`ID`)
261 ON DELETE NO ACTION
262 ON UPDATE NO ACTION,
263 CONSTRAINT `fk_ABONNE_has_MATCH1_MATCH1`
264 FOREIGN KEY (`MATCH_ID`)
265 REFERENCES `mydb`.`MATCH` (`ID`)
266 ON DELETE NO ACTION
267 ON UPDATE NO ACTION)
268ENGINE = InnoDB;
269
270
271-- -----------------------------------------------------
272-- Table `mydb`.`ENTRAINEMENT_has_TERRAIN`
273-- -----------------------------------------------------
274CREATE TABLE IF NOT EXISTS `mydb`.`ENTRAINEMENT_has_TERRAIN` (
275 `ENTRAINEMENT_ID` INT UNSIGNED NOT NULL,
276 `TERRAIN_ID` INT UNSIGNED NOT NULL,
277 PRIMARY KEY (`ENTRAINEMENT_ID`, `TERRAIN_ID`),
278 INDEX `fk_ENTRAINEMENT_has_TERRAIN_TERRAIN1_idx` (`TERRAIN_ID` ASC),
279 INDEX `fk_ENTRAINEMENT_has_TERRAIN_ENTRAINEMENT1_idx` (`ENTRAINEMENT_ID` ASC),
280 CONSTRAINT `fk_ENTRAINEMENT_has_TERRAIN_ENTRAINEMENT1`
281 FOREIGN KEY (`ENTRAINEMENT_ID`)
282 REFERENCES `mydb`.`ENTRAINEMENT` (`ID`)
283 ON DELETE NO ACTION
284 ON UPDATE NO ACTION,
285 CONSTRAINT `fk_ENTRAINEMENT_has_TERRAIN_TERRAIN1`
286 FOREIGN KEY (`TERRAIN_ID`)
287 REFERENCES `mydb`.`TERRAIN` (`ID`)
288 ON DELETE NO ACTION
289 ON UPDATE NO ACTION)
290ENGINE = InnoDB;
291
292
293-- -----------------------------------------------------
294-- Table `mydb`.`ENTRAINEUR_has_ENTRAINEMENT`
295-- -----------------------------------------------------
296CREATE TABLE IF NOT EXISTS `mydb`.`ENTRAINEUR_has_ENTRAINEMENT` (
297 `ENTRAINEUR_ID` INT UNSIGNED NOT NULL,
298 `ENTRAINEMENT_ID` INT UNSIGNED NOT NULL,
299 PRIMARY KEY (`ENTRAINEUR_ID`, `ENTRAINEMENT_ID`),
300 INDEX `fk_ENTRAINEUR_has_ENTRAINEMENT_ENTRAINEMENT1_idx` (`ENTRAINEMENT_ID` ASC),
301 INDEX `fk_ENTRAINEUR_has_ENTRAINEMENT_ENTRAINEUR1_idx` (`ENTRAINEUR_ID` ASC),
302 CONSTRAINT `fk_ENTRAINEUR_has_ENTRAINEMENT_ENTRAINEUR1`
303 FOREIGN KEY (`ENTRAINEUR_ID`)
304 REFERENCES `mydb`.`ENTRAINEUR` (`ID`)
305 ON DELETE NO ACTION
306 ON UPDATE NO ACTION,
307 CONSTRAINT `fk_ENTRAINEUR_has_ENTRAINEMENT_ENTRAINEMENT1`
308 FOREIGN KEY (`ENTRAINEMENT_ID`)
309 REFERENCES `mydb`.`ENTRAINEMENT` (`ID`)
310 ON DELETE NO ACTION
311 ON UPDATE NO ACTION)
312ENGINE = InnoDB;
313
314
315-- -----------------------------------------------------
316-- Table `mydb`.`ENTRAINEMENT_has_ENTRAINEUR`
317-- -----------------------------------------------------
318CREATE TABLE IF NOT EXISTS `mydb`.`ENTRAINEMENT_has_ENTRAINEUR` (
319 `ENTRAINEMENT_ID` INT UNSIGNED NOT NULL,
320 `ENTRAINEUR_ID` INT UNSIGNED NOT NULL,
321 PRIMARY KEY (`ENTRAINEMENT_ID`, `ENTRAINEUR_ID`),
322 INDEX `fk_ENTRAINEMENT_has_ENTRAINEUR_ENTRAINEUR1_idx` (`ENTRAINEUR_ID` ASC),
323 INDEX `fk_ENTRAINEMENT_has_ENTRAINEUR_ENTRAINEMENT1_idx` (`ENTRAINEMENT_ID` ASC),
324 CONSTRAINT `fk_ENTRAINEMENT_has_ENTRAINEUR_ENTRAINEMENT1`
325 FOREIGN KEY (`ENTRAINEMENT_ID`)
326 REFERENCES `mydb`.`ENTRAINEMENT` (`ID`)
327 ON DELETE NO ACTION
328 ON UPDATE NO ACTION,
329 CONSTRAINT `fk_ENTRAINEMENT_has_ENTRAINEUR_ENTRAINEUR1`
330 FOREIGN KEY (`ENTRAINEUR_ID`)
331 REFERENCES `mydb`.`ENTRAINEUR` (`ID`)
332 ON DELETE NO ACTION
333 ON UPDATE NO ACTION)
334ENGINE = InnoDB;
335
336
337-- -----------------------------------------------------
338-- Table `mydb`.`AVATAR`
339-- -----------------------------------------------------
340CREATE TABLE IF NOT EXISTS `mydb`.`AVATAR` (
341 `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
342 `CONTENT` BLOB NULL,
343 PRIMARY KEY (`ID`))
344ENGINE = InnoDB;
345
346
347-- -----------------------------------------------------
348-- Table `mydb`.`IMAGE`
349-- -----------------------------------------------------
350CREATE TABLE IF NOT EXISTS `mydb`.`IMAGE` (
351 `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
352 `CONTENT` BLOB NULL,
353 PRIMARY KEY (`ID`))
354ENGINE = InnoDB;
355
356
357SET SQL_MODE=@OLD_SQL_MODE;
358SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
359SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;