· 9 years ago · Oct 15, 2016, 05:44 PM
1DROP DATABASE IF EXISTS MLR2;
2
3CREATE DATABASE IF NOT EXISTS MLR2;
4USE MLR2;
5# -----------------------------------------------------------------------------
6# TABLE : MARQUES
7# -----------------------------------------------------------------------------
8
9CREATE TABLE IF NOT EXISTS MARQUES
10 (
11 ID INTEGER NOT NULL ,
12 NOM VARCHAR(50) NULL
13 , PRIMARY KEY (ID)
14 )
15 comment = "";
16
17# -----------------------------------------------------------------------------
18# TABLE : DESTRUCTIONS
19# -----------------------------------------------------------------------------
20
21CREATE TABLE IF NOT EXISTS DESTRUCTIONS
22 (
23 ID INTEGER NOT NULL ,
24 EST_TOTAL BIT(1) NULL ,
25 DESCRIPTION VARCHAR(100) NULL ,
26 MONTANT REAL(5,2) NULL
27 , PRIMARY KEY (ID)
28 )
29 comment = "";
30
31# -----------------------------------------------------------------------------
32# TABLE : ASSURES
33# -----------------------------------------------------------------------------
34
35CREATE TABLE IF NOT EXISTS ASSURES
36 (
37 ID INTEGER NOT NULL ,
38 BONUS_MALUS REAL(5,2) NULL ,
39 NOM VARCHAR(25) NULL ,
40 PRENOM VARCHAR(25) NULL ,
41 ADRESSE VARCHAR(25) NULL ,
42 VILLE VARCHAR(25) NULL ,
43 CODE_POSTAL BIGINT(5) NULL ,
44 TEL VARCHAR(13) NULL
45 , PRIMARY KEY (ID)
46 )
47 comment = "";
48
49# -----------------------------------------------------------------------------
50# TABLE : GARAGISTES
51# -----------------------------------------------------------------------------
52
53CREATE TABLE IF NOT EXISTS GARAGISTES
54 (
55 ID INTEGER NOT NULL ,
56 NOM VARCHAR(128) NULL ,
57 AGREE BIT(1) NULL
58 , PRIMARY KEY (ID)
59 )
60 comment = "";
61
62# -----------------------------------------------------------------------------
63# TABLE : ADMINISTRATEURS
64# -----------------------------------------------------------------------------
65
66CREATE TABLE IF NOT EXISTS ADMINISTRATEURS
67 (
68 ID INTEGER NOT NULL ,
69 ROLE VARCHAR(128) NULL ,
70 EMAIL VARCHAR(50) NOT NULL ,
71 HASH CHAR(40) NULL ,
72 NOM VARCHAR(25) NULL ,
73 PRENOM VARCHAR(25) NULL ,
74 ADRESSE VARCHAR(25) NULL ,
75 VILLE VARCHAR(25) NULL ,
76 CODE_POSTAL BIGINT(5) NULL ,
77 TEL VARCHAR(13) NULL
78 , PRIMARY KEY (ID)
79 )
80 comment = "";
81
82# -----------------------------------------------------------------------------
83# TABLE : ACCIDENTS
84# -----------------------------------------------------------------------------
85
86CREATE TABLE IF NOT EXISTS ACCIDENTS
87 (
88 ID INTEGER NOT NULL ,
89 NB_PERTES_H BIGINT(4) NULL ,
90 LIBELLE VARCHAR(100) NULL ,
91 DATE_SINISTRE DATE NULL ,
92 HEURE_SINISTRE TIME NULL ,
93 CONSTAT VARCHAR(100) NULL ,
94 MAJ DATETIME NOT NULL
95 , PRIMARY KEY (ID)
96 )
97 comment = "";
98
99# -----------------------------------------------------------------------------
100# TABLE : TYPES_GARANTIE
101# -----------------------------------------------------------------------------
102
103CREATE TABLE IF NOT EXISTS TYPES_GARANTIE
104 (
105 CODE BIGINT(4) NOT NULL ,
106 LIBELLE VARCHAR(100) NULL ,
107 POLICE DOUBLE PRECISION(13,2) NULL ,
108 FRANCHISE_MIN DOUBLE PRECISION(13,2) NULL ,
109 FRANCHISE_MAX DOUBLE PRECISION(13,2) NULL
110 , PRIMARY KEY (CODE)
111 )
112 comment = "";
113
114# -----------------------------------------------------------------------------
115# TABLE : SINISTRES
116# -----------------------------------------------------------------------------
117
118CREATE TABLE IF NOT EXISTS SINISTRES
119 (
120 ID INTEGER NOT NULL ,
121 ID_1 INTEGER NULL ,
122 LIBELLE VARCHAR(100) NULL ,
123 DATE_SINISTRE DATE NULL ,
124 HEURE_SINISTRE TIME NULL ,
125 CONSTAT VARCHAR(100) NULL ,
126 MAJ DATETIME NOT NULL
127 , PRIMARY KEY (ID)
128 )
129 comment = "";
130
131# -----------------------------------------------------------------------------
132# INDEX DE LA TABLE SINISTRES
133# -----------------------------------------------------------------------------
134
135
136CREATE INDEX I_FK_SINISTRES_EXPERTS
137 ON SINISTRES (ID_1 ASC);
138
139# -----------------------------------------------------------------------------
140# TABLE : DETERIORATIONS
141# -----------------------------------------------------------------------------
142
143CREATE TABLE IF NOT EXISTS DETERIORATIONS
144 (
145 ID_HER_5 INTEGER NOT NULL ,
146 ID INTEGER NOT NULL ,
147 TAUX DECIMAL(10,2) NULL ,
148 DESCRIPTION VARCHAR(100) NULL ,
149 MONTANT REAL(5,2) NULL
150 , PRIMARY KEY (ID_HER_5)
151 )
152 comment = "";
153
154# -----------------------------------------------------------------------------
155# INDEX DE LA TABLE DETERIORATIONS
156# -----------------------------------------------------------------------------
157
158
159CREATE INDEX I_FK_DETERIORATIONS_GARAGISTES
160 ON DETERIORATIONS (ID ASC);
161
162# -----------------------------------------------------------------------------
163# TABLE : TIERS
164# -----------------------------------------------------------------------------
165
166CREATE TABLE IF NOT EXISTS TIERS
167 (
168 ID INTEGER NOT NULL ,
169 COMPAGNIE VARCHAR(128) NULL ,
170 BONUS_MALUS REAL(5,2) NULL ,
171 NOM VARCHAR(25) NULL ,
172 PRENOM VARCHAR(25) NULL ,
173 ADRESSE VARCHAR(25) NULL ,
174 VILLE VARCHAR(25) NULL ,
175 CODE_POSTAL BIGINT(5) NULL ,
176 TEL VARCHAR(13) NULL
177 , PRIMARY KEY (ID)
178 )
179 comment = "";
180
181# -----------------------------------------------------------------------------
182# TABLE : INDEMNISATIONS
183# -----------------------------------------------------------------------------
184
185CREATE TABLE IF NOT EXISTS INDEMNISATIONS
186 (
187 ID BIGINT(32) NOT NULL ,
188 ID_DEBOUCHER INTEGER NOT NULL ,
189 ID_1 INTEGER NOT NULL ,
190 ID_2 INTEGER NOT NULL ,
191 MONTANT REAL(5,2) NULL ,
192 FRANCHISE DECIMAL(10,2) NULL
193 , PRIMARY KEY (ID)
194 )
195 comment = "";
196
197# -----------------------------------------------------------------------------
198# INDEX DE LA TABLE INDEMNISATIONS
199# -----------------------------------------------------------------------------
200
201
202CREATE INDEX I_FK_INDEMNISATIONS_ASSURES_2
203 ON INDEMNISATIONS (ID_2 ASC,ID_1 ASC);
204
205CREATE UNIQUE INDEX I_FK_INDEMNISATIONS_SINISTRES
206 ON INDEMNISATIONS (ID_DEBOUCHER ASC);
207
208# -----------------------------------------------------------------------------
209# TABLE : DOMMAGES
210# -----------------------------------------------------------------------------
211
212CREATE TABLE IF NOT EXISTS DOMMAGES
213 (
214 ID INTEGER NOT NULL ,
215 ID_CAUSER INTEGER NOT NULL ,
216 VIN CHAR(17) NOT NULL ,
217 DESCRIPTION VARCHAR(100) NULL ,
218 MONTANT REAL(5,2) NULL
219 , PRIMARY KEY (ID)
220 )
221 comment = "";
222
223# -----------------------------------------------------------------------------
224# INDEX DE LA TABLE DOMMAGES
225# -----------------------------------------------------------------------------
226
227
228CREATE INDEX I_FK_DOMMAGES_SINISTRES
229 ON DOMMAGES (ID_CAUSER ASC);
230
231CREATE INDEX I_FK_DOMMAGES_VEHICULES
232 ON DOMMAGES (VIN ASC);
233
234# -----------------------------------------------------------------------------
235# TABLE : ASSURES_2
236# -----------------------------------------------------------------------------
237
238CREATE TABLE IF NOT EXISTS ASSURES_2
239 (
240 ID_2 INTEGER NOT NULL ,
241 ID_1 INTEGER NOT NULL ,
242 ID INTEGER NOT NULL ,
243 CARTE_IDENTITE VARCHAR(100) NULL ,
244 ETAT BIGINT(4) NOT NULL ,
245 SEPA VARCHAR(100) NULL ,
246 MAJ DATETIME NOT NULL ,
247 BONUS_MALUS REAL(5,2) NULL ,
248 NOM VARCHAR(25) NULL ,
249 PRENOM VARCHAR(25) NULL ,
250 ADRESSE VARCHAR(25) NULL ,
251 VILLE VARCHAR(25) NULL ,
252 CODE_POSTAL BIGINT(5) NULL ,
253 TEL VARCHAR(13) NULL ,
254 EMAIL VARCHAR(50) NOT NULL ,
255 HASH CHAR(40) NULL
256 , PRIMARY KEY (ID_2,ID_1)
257 )
258 comment = "";
259
260# -----------------------------------------------------------------------------
261# INDEX DE LA TABLE ASSURES_2
262# -----------------------------------------------------------------------------
263
264
265CREATE INDEX I_FK_ASSURES_2_ASSURES
266 ON ASSURES_2 (ID_1 ASC);
267
268CREATE INDEX I_FK_ASSURES_2_UTILISATEURS
269 ON ASSURES_2 (ID_2 ASC);
270
271# -----------------------------------------------------------------------------
272# TABLE : MODELES
273# -----------------------------------------------------------------------------
274
275CREATE TABLE IF NOT EXISTS MODELES
276 (
277 ID INTEGER NOT NULL ,
278 ID_FABRIQUER INTEGER NOT NULL ,
279 NOM VARCHAR(50) NULL ,
280 ANNEE BIGINT(4) NULL
281 , PRIMARY KEY (ID)
282 )
283 comment = "";
284
285# -----------------------------------------------------------------------------
286# INDEX DE LA TABLE MODELES
287# -----------------------------------------------------------------------------
288
289
290CREATE INDEX I_FK_MODELES_MARQUES
291 ON MODELES (ID_FABRIQUER ASC);
292
293# -----------------------------------------------------------------------------
294# TABLE : VEHICULES
295# -----------------------------------------------------------------------------
296
297CREATE TABLE IF NOT EXISTS VEHICULES
298 (
299 VIN CHAR(17) NOT NULL ,
300 ID INTEGER NOT NULL ,
301 IMMATRICULATION CHAR(9) NULL ,
302 CARTE_GRISE VARCHAR(100) NULL ,
303 MAJ DATETIME NOT NULL
304 , PRIMARY KEY (VIN)
305 )
306 comment = "";
307
308# -----------------------------------------------------------------------------
309# INDEX DE LA TABLE VEHICULES
310# -----------------------------------------------------------------------------
311
312
313CREATE INDEX I_FK_VEHICULES_MODELES
314 ON VEHICULES (ID ASC);
315
316# -----------------------------------------------------------------------------
317# TABLE : CONTRATS
318# -----------------------------------------------------------------------------
319
320CREATE TABLE IF NOT EXISTS CONTRATS
321 (
322 ID INTEGER NOT NULL ,
323 VIN CHAR(17) NOT NULL ,
324 CODE_ETRE BIGINT(4) NOT NULL ,
325 ID_1 INTEGER NOT NULL ,
326 ID_2 INTEGER NOT NULL ,
327 MONTANT REAL(5,2) NULL ,
328 DATE_SOUSCRIPTION DATE NULL ,
329 ETAT BIGINT(4) NULL ,
330 CONTRAT VARCHAR(100) NULL ,
331 MAJ DATETIME NOT NULL ,
332 CODE BIGINT(4) NOT NULL
333 , PRIMARY KEY (ID)
334 )
335 comment = "";
336
337# -----------------------------------------------------------------------------
338# INDEX DE LA TABLE CONTRATS
339# -----------------------------------------------------------------------------
340
341
342CREATE INDEX I_FK_CONTRATS_VEHICULES
343 ON CONTRATS (VIN ASC);
344
345CREATE INDEX I_FK_CONTRATS_ASSURES_2
346 ON CONTRATS (ID_2 ASC,ID_1 ASC);
347
348CREATE INDEX I_FK_CONTRATS_TYPES_GARANTIE
349 ON CONTRATS (CODE_ETRE ASC);
350
351# -----------------------------------------------------------------------------
352# TABLE : SINISTRES_SANS_TIER
353# -----------------------------------------------------------------------------
354
355CREATE TABLE IF NOT EXISTS SINISTRES_SANS_TIER
356 (
357 ID INTEGER NOT NULL ,
358 TYPE BIGINT(4) NULL ,
359 LIBELLE VARCHAR(100) NULL ,
360 DATE_SINISTRE DATE NULL ,
361 HEURE_SINISTRE TIME NULL ,
362 CONSTAT VARCHAR(100) NULL ,
363 MAJ DATETIME NOT NULL
364 , PRIMARY KEY (ID)
365 )
366 comment = "";
367
368# -----------------------------------------------------------------------------
369# TABLE : EXPERTS
370# -----------------------------------------------------------------------------
371
372CREATE TABLE IF NOT EXISTS EXPERTS
373 (
374 ID INTEGER NOT NULL ,
375 RANG BIGINT(4) NULL ,
376 EMAIL VARCHAR(50) NOT NULL ,
377 HASH CHAR(40) NULL ,
378 NOM VARCHAR(25) NULL ,
379 PRENOM VARCHAR(25) NULL ,
380 ADRESSE VARCHAR(25) NULL ,
381 VILLE VARCHAR(25) NULL ,
382 CODE_POSTAL BIGINT(5) NULL ,
383 TEL VARCHAR(13) NULL
384 , PRIMARY KEY (ID)
385 )
386 comment = "";
387
388# -----------------------------------------------------------------------------
389# TABLE : UTILISATEURS
390# -----------------------------------------------------------------------------
391
392CREATE TABLE IF NOT EXISTS UTILISATEURS
393 (
394 ID INTEGER NOT NULL ,
395 EMAIL VARCHAR(50) NOT NULL ,
396 HASH CHAR(40) NULL ,
397 NOM VARCHAR(25) NULL ,
398 PRENOM VARCHAR(25) NULL ,
399 ADRESSE VARCHAR(25) NULL ,
400 VILLE VARCHAR(25) NULL ,
401 CODE_POSTAL BIGINT(5) NULL ,
402 TEL VARCHAR(13) NULL
403 , PRIMARY KEY (ID)
404 )
405 comment = "";
406
407# -----------------------------------------------------------------------------
408# TABLE : PERSONNES
409# -----------------------------------------------------------------------------
410
411CREATE TABLE IF NOT EXISTS PERSONNES
412 (
413 ID INTEGER NOT NULL ,
414 NOM VARCHAR(25) NULL ,
415 PRENOM VARCHAR(25) NULL ,
416 ADRESSE VARCHAR(25) NULL ,
417 VILLE VARCHAR(25) NULL ,
418 CODE_POSTAL BIGINT(5) NULL ,
419 TEL VARCHAR(13) NULL
420 , PRIMARY KEY (ID)
421 )
422 comment = "";
423
424# -----------------------------------------------------------------------------
425# TABLE : IMPLICATIONS
426# -----------------------------------------------------------------------------
427
428CREATE TABLE IF NOT EXISTS IMPLICATIONS
429 (
430 ID_1 INTEGER NOT NULL ,
431 ID INTEGER NOT NULL ,
432 TAUX_RESP REAL(5,2) NULL
433 , PRIMARY KEY (ID_1,ID)
434 )
435 comment = "";
436
437# -----------------------------------------------------------------------------
438# INDEX DE LA TABLE IMPLICATIONS
439# -----------------------------------------------------------------------------
440
441
442CREATE INDEX I_FK_IMPLICATIONS_ACCIDENTS
443 ON IMPLICATIONS (ID_1 ASC);
444
445CREATE INDEX I_FK_IMPLICATIONS_ASSURES
446 ON IMPLICATIONS (ID ASC);
447
448# -----------------------------------------------------------------------------
449# TABLE : POSSESSIONS
450# -----------------------------------------------------------------------------
451
452CREATE TABLE IF NOT EXISTS POSSESSIONS
453 (
454 ID INTEGER NOT NULL ,
455 VIN CHAR(17) NOT NULL ,
456 DATE_DEBUT DATE NOT NULL
457 , PRIMARY KEY (ID,VIN,DATE_DEBUT)
458 )
459 comment = "";
460
461# -----------------------------------------------------------------------------
462# INDEX DE LA TABLE POSSESSIONS
463# -----------------------------------------------------------------------------
464
465
466CREATE INDEX I_FK_POSSESSIONS_ASSURES
467 ON POSSESSIONS (ID ASC);
468
469CREATE INDEX I_FK_POSSESSIONS_VEHICULES
470 ON POSSESSIONS (VIN ASC);
471
472# -----------------------------------------------------------------------------
473# TABLE : COUVERTURE
474# -----------------------------------------------------------------------------
475
476CREATE TABLE IF NOT EXISTS COUVERTURE
477 (
478 ID INTEGER NOT NULL ,
479 CODE BIGINT(4) NOT NULL
480 , PRIMARY KEY (ID,CODE)
481 )
482 comment = "";
483
484# -----------------------------------------------------------------------------
485# INDEX DE LA TABLE COUVERTURE
486# -----------------------------------------------------------------------------
487
488
489CREATE INDEX I_FK_COUVERTURE_SINISTRES
490 ON COUVERTURE (ID ASC);
491
492CREATE INDEX I_FK_COUVERTURE_TYPES_GARANTIE
493 ON COUVERTURE (CODE ASC);
494
495
496# -----------------------------------------------------------------------------
497# CREATION DES REFERENCES DE TABLE
498# -----------------------------------------------------------------------------
499
500
501ALTER TABLE DESTRUCTIONS
502 ADD FOREIGN KEY FK_DESTRUCTIONS_DOMMAGES (ID)
503 REFERENCES DOMMAGES (ID) ;
504
505
506ALTER TABLE ASSURES
507 ADD FOREIGN KEY FK_ASSURES_PERSONNES (ID)
508 REFERENCES PERSONNES (ID) ;
509
510
511ALTER TABLE ADMINISTRATEURS
512 ADD FOREIGN KEY FK_ADMINISTRATEURS_UTILISATEURS (ID)
513 REFERENCES UTILISATEURS (ID) ;
514
515
516ALTER TABLE ACCIDENTS
517 ADD FOREIGN KEY FK_ACCIDENTS_SINISTRES (ID)
518 REFERENCES SINISTRES (ID) ;
519
520
521ALTER TABLE SINISTRES
522 ADD FOREIGN KEY FK_SINISTRES_EXPERTS (ID_1)
523 REFERENCES EXPERTS (ID) ;
524
525
526ALTER TABLE DETERIORATIONS
527 ADD FOREIGN KEY FK_DETERIORATIONS_GARAGISTES (ID)
528 REFERENCES GARAGISTES (ID) ;
529
530
531ALTER TABLE DETERIORATIONS
532 ADD FOREIGN KEY FK_DETERIORATIONS_DOMMAGES (ID_HER_5)
533 REFERENCES DOMMAGES (ID) ;
534
535
536ALTER TABLE TIERS
537 ADD FOREIGN KEY FK_TIERS_ASSURES (ID)
538 REFERENCES ASSURES (ID) ;
539
540
541ALTER TABLE INDEMNISATIONS
542 ADD FOREIGN KEY FK_INDEMNISATIONS_ASSURES_2 (ID_2,ID_1)
543 REFERENCES ASSURES_2 (ID_2,ID_1) ;
544
545
546ALTER TABLE INDEMNISATIONS
547 ADD FOREIGN KEY FK_INDEMNISATIONS_SINISTRES (ID_DEBOUCHER)
548 REFERENCES SINISTRES (ID) ;
549
550
551ALTER TABLE DOMMAGES
552 ADD FOREIGN KEY FK_DOMMAGES_SINISTRES (ID_CAUSER)
553 REFERENCES SINISTRES (ID) ;
554
555
556ALTER TABLE DOMMAGES
557 ADD FOREIGN KEY FK_DOMMAGES_VEHICULES (VIN)
558 REFERENCES VEHICULES (VIN) ;
559
560
561ALTER TABLE ASSURES_2
562 ADD FOREIGN KEY FK_ASSURES_2_ASSURES (ID_1)
563 REFERENCES ASSURES (ID) ;
564
565
566ALTER TABLE ASSURES_2
567 ADD FOREIGN KEY FK_ASSURES_2_UTILISATEURS (ID_2)
568 REFERENCES UTILISATEURS (ID) ;
569
570
571ALTER TABLE MODELES
572 ADD FOREIGN KEY FK_MODELES_MARQUES (ID_FABRIQUER)
573 REFERENCES MARQUES (ID) ;
574
575
576ALTER TABLE VEHICULES
577 ADD FOREIGN KEY FK_VEHICULES_MODELES (ID)
578 REFERENCES MODELES (ID) ;
579
580
581ALTER TABLE CONTRATS
582 ADD FOREIGN KEY FK_CONTRATS_VEHICULES (VIN)
583 REFERENCES VEHICULES (VIN) ;
584
585
586ALTER TABLE CONTRATS
587 ADD FOREIGN KEY FK_CONTRATS_ASSURES_2 (ID_2,ID_1)
588 REFERENCES ASSURES_2 (ID_2,ID_1) ;
589
590
591ALTER TABLE CONTRATS
592 ADD FOREIGN KEY FK_CONTRATS_TYPES_GARANTIE (CODE_ETRE)
593 REFERENCES TYPES_GARANTIE (CODE) ;
594
595
596ALTER TABLE SINISTRES_SANS_TIER
597 ADD FOREIGN KEY FK_SINISTRES_SANS_TIER_SINISTRES (ID)
598 REFERENCES SINISTRES (ID) ;
599
600
601ALTER TABLE EXPERTS
602 ADD FOREIGN KEY FK_EXPERTS_UTILISATEURS (ID)
603 REFERENCES UTILISATEURS (ID) ;
604
605
606ALTER TABLE UTILISATEURS
607 ADD FOREIGN KEY FK_UTILISATEURS_PERSONNES (ID)
608 REFERENCES PERSONNES (ID) ;
609
610
611ALTER TABLE IMPLICATIONS
612 ADD FOREIGN KEY FK_IMPLICATIONS_ACCIDENTS (ID_1)
613 REFERENCES ACCIDENTS (ID) ;
614
615
616ALTER TABLE IMPLICATIONS
617 ADD FOREIGN KEY FK_IMPLICATIONS_ASSURES (ID)
618 REFERENCES ASSURES (ID) ;
619
620
621ALTER TABLE POSSESSIONS
622 ADD FOREIGN KEY FK_POSSESSIONS_ASSURES (ID)
623 REFERENCES ASSURES (ID) ;
624
625
626ALTER TABLE POSSESSIONS
627 ADD FOREIGN KEY FK_POSSESSIONS_VEHICULES (VIN)
628 REFERENCES VEHICULES (VIN) ;
629
630
631ALTER TABLE COUVERTURE
632 ADD FOREIGN KEY FK_COUVERTURE_SINISTRES (ID)
633 REFERENCES SINISTRES (ID) ;
634
635
636ALTER TABLE COUVERTURE
637 ADD FOREIGN KEY FK_COUVERTURE_TYPES_GARANTIE (CODE)
638 REFERENCES TYPES_GARANTIE (CODE) ;