· 6 years ago · Jan 15, 2020, 09:50 PM
1/*==============================================================*/
2/* Nom de SGBD : Microsoft SQL Server 2016 */
3/* Date de création : 15/01/2020 20:41:47 */
4/*==============================================================*/
5
6
7if exists (select 1
8 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
9 where r.fkeyid = object_id('AnneeScolaire') and o.name = 'FK_ANNEESCO_ASSOCIATI_INCRIPTI')
10alter table AnneeScolaire
11 drop constraint FK_ANNEESCO_ASSOCIATI_INCRIPTI
12go
13
14if exists (select 1
15 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
16 where r.fkeyid = object_id('Cycle') and o.name = 'FK_CYCLE_ASSOCIATI_NIVEAU')
17alter table Cycle
18 drop constraint FK_CYCLE_ASSOCIATI_NIVEAU
19go
20
21if exists (select 1
22 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
23 where r.fkeyid = object_id('Ecole') and o.name = 'FK_ECOLE_AVOIRTRAN_TRANSPOR')
24alter table Ecole
25 drop constraint FK_ECOLE_AVOIRTRAN_TRANSPOR
26go
27
28if exists (select 1
29 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
30 where r.fkeyid = object_id('Eleve') and o.name = 'FK_ELEVE_ASSOCIATI_DECLARAT')
31alter table Eleve
32 drop constraint FK_ELEVE_ASSOCIATI_DECLARAT
33go
34
35if exists (select 1
36 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
37 where r.fkeyid = object_id('Eleve') and o.name = 'FK_ELEVE_ASSOCIATI_ABSENCES')
38alter table Eleve
39 drop constraint FK_ELEVE_ASSOCIATI_ABSENCES
40go
41
42if exists (select 1
43 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
44 where r.fkeyid = object_id('Eleve') and o.name = 'FK_ELEVE_ASSOCIATI_INCRIPTI')
45alter table Eleve
46 drop constraint FK_ELEVE_ASSOCIATI_INCRIPTI
47go
48
49if exists (select 1
50 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
51 where r.fkeyid = object_id('GpsTracking') and o.name = 'FK_GPSTRACK_ASSOCIATI_HISTORIQ')
52alter table GpsTracking
53 drop constraint FK_GPSTRACK_ASSOCIATI_HISTORIQ
54go
55
56if exists (select 1
57 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
58 where r.fkeyid = object_id('Groupe') and o.name = 'FK_GROUPE_ASSOCIATI_INCRIPTI')
59alter table Groupe
60 drop constraint FK_GROUPE_ASSOCIATI_INCRIPTI
61go
62
63if exists (select 1
64 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
65 where r.fkeyid = object_id('Matiere') and o.name = 'FK_MATIERE_ASSOCIATI_ABSENCES')
66alter table Matiere
67 drop constraint FK_MATIERE_ASSOCIATI_ABSENCES
68go
69
70if exists (select 1
71 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
72 where r.fkeyid = object_id('Niveau') and o.name = 'FK_NIVEAU_ASSOCIATI_INCRIPTI')
73alter table Niveau
74 drop constraint FK_NIVEAU_ASSOCIATI_INCRIPTI
75go
76
77if exists (select 1
78 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
79 where r.fkeyid = object_id('Personnes') and o.name = 'FK_PERSONNE_ASSOCIATI_TRANSPOR')
80alter table Personnes
81 drop constraint FK_PERSONNE_ASSOCIATI_TRANSPOR
82go
83
84if exists (select 1
85 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
86 where r.fkeyid = object_id('Personnes') and o.name = 'FK_PERSONNE_RECEPTEUR_DECLARAT')
87alter table Personnes
88 drop constraint FK_PERSONNE_RECEPTEUR_DECLARAT
89go
90
91if exists (select 1
92 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
93 where r.fkeyid = object_id('Profil') and o.name = 'FK_PROFIL_ASSOCIATI_PERSONNE')
94alter table Profil
95 drop constraint FK_PROFIL_ASSOCIATI_PERSONNE
96go
97
98if exists (select 1
99 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
100 where r.fkeyid = object_id('Quartier') and o.name = 'FK_QUARTIER_HABITER_ELEVE')
101alter table Quartier
102 drop constraint FK_QUARTIER_HABITER_ELEVE
103go
104
105if exists (select 1
106 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
107 where r.fkeyid = object_id('TransportScolaire') and o.name = 'FK_TRANSPOR_ASSOCIATI_GPSTRACK')
108alter table TransportScolaire
109 drop constraint FK_TRANSPOR_ASSOCIATI_GPSTRACK
110go
111
112if exists (select 1
113 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
114 where r.fkeyid = object_id('cours') and o.name = 'FK_COURS_COURS_MATIERE')
115alter table cours
116 drop constraint FK_COURS_COURS_MATIERE
117go
118
119if exists (select 1
120 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
121 where r.fkeyid = object_id('cours') and o.name = 'FK_COURS_COURS_NIVEAU')
122alter table cours
123 drop constraint FK_COURS_COURS_NIVEAU
124go
125
126if exists (select 1
127 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
128 where r.fkeyid = object_id('garant') and o.name = 'FK_GARANT_GARANT_ELEVE')
129alter table garant
130 drop constraint FK_GARANT_GARANT_ELEVE
131go
132
133if exists (select 1
134 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
135 where r.fkeyid = object_id('garant') and o.name = 'FK_GARANT_GARANT_PERSONNE')
136alter table garant
137 drop constraint FK_GARANT_GARANT_PERSONNE
138go
139
140if exists (select 1
141 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
142 where r.fkeyid = object_id('participer') and o.name = 'FK_PARTICIP_PARTICIPE_ACTIVITE')
143alter table participer
144 drop constraint FK_PARTICIP_PARTICIPE_ACTIVITE
145go
146
147if exists (select 1
148 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
149 where r.fkeyid = object_id('participer') and o.name = 'FK_PARTICIP_PARTICIPE_ELEVE')
150alter table participer
151 drop constraint FK_PARTICIP_PARTICIPE_ELEVE
152go
153
154if exists (select 1
155 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
156 where r.fkeyid = object_id('transporter') and o.name = 'FK_TRANSPOR_TRANSPORT_QUARTIER')
157alter table transporter
158 drop constraint FK_TRANSPOR_TRANSPORT_QUARTIER
159go
160
161if exists (select 1
162 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
163 where r.fkeyid = object_id('transporter') and o.name = 'FK_TRANSPOR_TRANSPORT_TRANSPOR')
164alter table transporter
165 drop constraint FK_TRANSPOR_TRANSPORT_TRANSPOR
166go
167
168if exists (select 1
169 from sysobjects
170 where id = object_id('Absences')
171 and type = 'U')
172 drop table Absences
173go
174
175if exists (select 1
176 from sysobjects
177 where id = object_id('Activites')
178 and type = 'U')
179 drop table Activites
180go
181
182if exists (select 1
183 from sysindexes
184 where id = object_id('AnneeScolaire')
185 and name = 'ASSOCIATION9_FK'
186 and indid > 0
187 and indid < 255)
188 drop index AnneeScolaire.ASSOCIATION9_FK
189go
190
191if exists (select 1
192 from sysobjects
193 where id = object_id('AnneeScolaire')
194 and type = 'U')
195 drop table AnneeScolaire
196go
197
198if exists (select 1
199 from sysindexes
200 where id = object_id('Cycle')
201 and name = 'ASSOCIATION5_FK'
202 and indid > 0
203 and indid < 255)
204 drop index Cycle.ASSOCIATION5_FK
205go
206
207if exists (select 1
208 from sysobjects
209 where id = object_id('Cycle')
210 and type = 'U')
211 drop table Cycle
212go
213
214if exists (select 1
215 from sysobjects
216 where id = object_id('Declaration')
217 and type = 'U')
218 drop table Declaration
219go
220
221if exists (select 1
222 from sysindexes
223 where id = object_id('Ecole')
224 and name = 'AVOIRTRANSPORT_FK'
225 and indid > 0
226 and indid < 255)
227 drop index Ecole.AVOIRTRANSPORT_FK
228go
229
230if exists (select 1
231 from sysobjects
232 where id = object_id('Ecole')
233 and type = 'U')
234 drop table Ecole
235go
236
237if exists (select 1
238 from sysindexes
239 where id = object_id('Eleve')
240 and name = 'ASSOCIATION17_FK'
241 and indid > 0
242 and indid < 255)
243 drop index Eleve.ASSOCIATION17_FK
244go
245
246if exists (select 1
247 from sysindexes
248 where id = object_id('Eleve')
249 and name = 'ASSOCIATION15_FK'
250 and indid > 0
251 and indid < 255)
252 drop index Eleve.ASSOCIATION15_FK
253go
254
255if exists (select 1
256 from sysindexes
257 where id = object_id('Eleve')
258 and name = 'ASSOCIATION6_FK'
259 and indid > 0
260 and indid < 255)
261 drop index Eleve.ASSOCIATION6_FK
262go
263
264if exists (select 1
265 from sysobjects
266 where id = object_id('Eleve')
267 and type = 'U')
268 drop table Eleve
269go
270
271if exists (select 1
272 from sysindexes
273 where id = object_id('GpsTracking')
274 and name = 'ASSOCIATION19_FK'
275 and indid > 0
276 and indid < 255)
277 drop index GpsTracking.ASSOCIATION19_FK
278go
279
280if exists (select 1
281 from sysobjects
282 where id = object_id('GpsTracking')
283 and type = 'U')
284 drop table GpsTracking
285go
286
287if exists (select 1
288 from sysindexes
289 where id = object_id('Groupe')
290 and name = 'ASSOCIATION8_FK'
291 and indid > 0
292 and indid < 255)
293 drop index Groupe.ASSOCIATION8_FK
294go
295
296if exists (select 1
297 from sysobjects
298 where id = object_id('Groupe')
299 and type = 'U')
300 drop table Groupe
301go
302
303if exists (select 1
304 from sysobjects
305 where id = object_id('HistoriqueGPS')
306 and type = 'U')
307 drop table HistoriqueGPS
308go
309
310if exists (select 1
311 from sysobjects
312 where id = object_id('Incription')
313 and type = 'U')
314 drop table Incription
315go
316
317if exists (select 1
318 from sysindexes
319 where id = object_id('Matiere')
320 and name = 'ASSOCIATION18_FK'
321 and indid > 0
322 and indid < 255)
323 drop index Matiere.ASSOCIATION18_FK
324go
325
326if exists (select 1
327 from sysobjects
328 where id = object_id('Matiere')
329 and type = 'U')
330 drop table Matiere
331go
332
333if exists (select 1
334 from sysindexes
335 where id = object_id('Niveau')
336 and name = 'ASSOCIATION7_FK'
337 and indid > 0
338 and indid < 255)
339 drop index Niveau.ASSOCIATION7_FK
340go
341
342if exists (select 1
343 from sysobjects
344 where id = object_id('Niveau')
345 and type = 'U')
346 drop table Niveau
347go
348
349if exists (select 1
350 from sysindexes
351 where id = object_id('Personnes')
352 and name = 'RECEPTEUR_FK'
353 and indid > 0
354 and indid < 255)
355 drop index Personnes.RECEPTEUR_FK
356go
357
358if exists (select 1
359 from sysindexes
360 where id = object_id('Personnes')
361 and name = 'ASSOCIATION11_FK'
362 and indid > 0
363 and indid < 255)
364 drop index Personnes.ASSOCIATION11_FK
365go
366
367if exists (select 1
368 from sysobjects
369 where id = object_id('Personnes')
370 and type = 'U')
371 drop table Personnes
372go
373
374if exists (select 1
375 from sysindexes
376 where id = object_id('Profil')
377 and name = 'ASSOCIATION3_FK'
378 and indid > 0
379 and indid < 255)
380 drop index Profil.ASSOCIATION3_FK
381go
382
383if exists (select 1
384 from sysobjects
385 where id = object_id('Profil')
386 and type = 'U')
387 drop table Profil
388go
389
390if exists (select 1
391 from sysindexes
392 where id = object_id('Quartier')
393 and name = 'HABITER_FK'
394 and indid > 0
395 and indid < 255)
396 drop index Quartier.HABITER_FK
397go
398
399if exists (select 1
400 from sysobjects
401 where id = object_id('Quartier')
402 and type = 'U')
403 drop table Quartier
404go
405
406if exists (select 1
407 from sysindexes
408 where id = object_id('TransportScolaire')
409 and name = 'ASSOCIATION16_FK'
410 and indid > 0
411 and indid < 255)
412 drop index TransportScolaire.ASSOCIATION16_FK
413go
414
415if exists (select 1
416 from sysobjects
417 where id = object_id('TransportScolaire')
418 and type = 'U')
419 drop table TransportScolaire
420go
421
422if exists (select 1
423 from sysindexes
424 where id = object_id('cours')
425 and name = 'COURS_FK2'
426 and indid > 0
427 and indid < 255)
428 drop index cours.COURS_FK2
429go
430
431if exists (select 1
432 from sysindexes
433 where id = object_id('cours')
434 and name = 'COURS_FK'
435 and indid > 0
436 and indid < 255)
437 drop index cours.COURS_FK
438go
439
440if exists (select 1
441 from sysobjects
442 where id = object_id('cours')
443 and type = 'U')
444 drop table cours
445go
446
447if exists (select 1
448 from sysindexes
449 where id = object_id('garant')
450 and name = 'GARANT_FK2'
451 and indid > 0
452 and indid < 255)
453 drop index garant.GARANT_FK2
454go
455
456if exists (select 1
457 from sysindexes
458 where id = object_id('garant')
459 and name = 'GARANT_FK'
460 and indid > 0
461 and indid < 255)
462 drop index garant.GARANT_FK
463go
464
465if exists (select 1
466 from sysobjects
467 where id = object_id('garant')
468 and type = 'U')
469 drop table garant
470go
471
472if exists (select 1
473 from sysindexes
474 where id = object_id('participer')
475 and name = 'PARTICIPER_FK2'
476 and indid > 0
477 and indid < 255)
478 drop index participer.PARTICIPER_FK2
479go
480
481if exists (select 1
482 from sysindexes
483 where id = object_id('participer')
484 and name = 'PARTICIPER_FK'
485 and indid > 0
486 and indid < 255)
487 drop index participer.PARTICIPER_FK
488go
489
490if exists (select 1
491 from sysobjects
492 where id = object_id('participer')
493 and type = 'U')
494 drop table participer
495go
496
497if exists (select 1
498 from sysindexes
499 where id = object_id('transporter')
500 and name = 'TRANSPORTER_FK2'
501 and indid > 0
502 and indid < 255)
503 drop index transporter.TRANSPORTER_FK2
504go
505
506if exists (select 1
507 from sysindexes
508 where id = object_id('transporter')
509 and name = 'TRANSPORTER_FK'
510 and indid > 0
511 and indid < 255)
512 drop index transporter.TRANSPORTER_FK
513go
514
515if exists (select 1
516 from sysobjects
517 where id = object_id('transporter')
518 and type = 'U')
519 drop table transporter
520go
521
522/*==============================================================*/
523/* Table : Absences */
524/*==============================================================*/
525create table Absences (
526 id int not null,
527 date datetime null,
528 justif bit null,
529 observation varchar(254) null,
530 constraint PK_ABSENCES primary key (id)
531)
532go
533
534/*==============================================================*/
535/* Table : Activites */
536/*==============================================================*/
537create table Activites (
538 id int not null,
539 date datetime null,
540 description varchar(254) null,
541 montant int null,
542 constraint PK_ACTIVITES primary key (id)
543)
544go
545
546/*==============================================================*/
547/* Table : AnneeScolaire */
548/*==============================================================*/
549create table AnneeScolaire (
550 id int not null,
551 Inc_id int not null,
552 anneeScolaire int null,
553 dateDebut datetime null,
554 dateFin datetime null,
555 constraint PK_ANNEESCOLAIRE primary key (id),
556 constraint AK_IDENTIFIANT_1_ANNEESCO unique (id),
557 constraint AK_IDENTIFIANT_2_ANNEESCO unique (id),
558 constraint AK_IDENTIFIANT_3_ANNEESCO unique (id)
559)
560go
561
562/*==============================================================*/
563/* Index : ASSOCIATION9_FK */
564/*==============================================================*/
565
566
567
568
569create nonclustered index ASSOCIATION9_FK on AnneeScolaire (Inc_id ASC)
570go
571
572/*==============================================================*/
573/* Table : Cycle */
574/*==============================================================*/
575create table Cycle (
576 id int not null,
577 Niv_id int not null,
578 description int null,
579 constraint PK_CYCLE primary key (id)
580)
581go
582
583/*==============================================================*/
584/* Index : ASSOCIATION5_FK */
585/*==============================================================*/
586
587
588
589
590create nonclustered index ASSOCIATION5_FK on Cycle (Niv_id ASC)
591go
592
593/*==============================================================*/
594/* Table : Declaration */
595/*==============================================================*/
596create table Declaration (
597 id int not null,
598 date datetime null,
599 declaration varchar(254) null,
600 valider bit null,
601 reponse varchar(254) null,
602 constraint PK_DECLARATION primary key (id)
603)
604go
605
606/*==============================================================*/
607/* Table : Ecole */
608/*==============================================================*/
609create table Ecole (
610 id int not null,
611 matricule varchar(254) not null,
612 raisonSocial varchar(254) null,
613 adresse varchar(254) null,
614 telethone varchar(254) null,
615 email varchar(254) null,
616 localisationGPS varchar(254) null,
617 fax varchar(254) null,
618 logo varchar(254) null,
619 constraint PK_ECOLE primary key (id)
620)
621go
622
623/*==============================================================*/
624/* Index : AVOIRTRANSPORT_FK */
625/*==============================================================*/
626
627
628
629
630create nonclustered index AVOIRTRANSPORT_FK on Ecole (matricule ASC)
631go
632
633/*==============================================================*/
634/* Table : Eleve */
635/*==============================================================*/
636create table Eleve (
637 num varchar(254) not null,
638 id int not null,
639 Inc_id int not null,
640 Dec_id int not null,
641 nom varchar(254) null,
642 prenom varchar(254) null,
643 dateNaissance datetime null,
644 adresse varchar(254) null,
645 sex varchar(254) null,
646 lieuNaissance varchar(254) null,
647 telephone varchar(254) null,
648 constraint PK_ELEVE primary key (num),
649 constraint AK_IDENTIFIANT_1_ELEVE unique (num)
650)
651go
652
653/*==============================================================*/
654/* Index : ASSOCIATION6_FK */
655/*==============================================================*/
656
657
658
659
660create nonclustered index ASSOCIATION6_FK on Eleve (Inc_id ASC)
661go
662
663/*==============================================================*/
664/* Index : ASSOCIATION15_FK */
665/*==============================================================*/
666
667
668
669
670create nonclustered index ASSOCIATION15_FK on Eleve (Dec_id ASC)
671go
672
673/*==============================================================*/
674/* Index : ASSOCIATION17_FK */
675/*==============================================================*/
676
677
678
679
680create nonclustered index ASSOCIATION17_FK on Eleve (id ASC)
681go
682
683/*==============================================================*/
684/* Table : GpsTracking */
685/*==============================================================*/
686create table GpsTracking (
687 id int not null,
688 His_id int not null,
689 numSerie varchar(254) null,
690 marque varchar(254) null,
691 gspNumero varchar(254) null,
692 constraint PK_GPSTRACKING primary key (id)
693)
694go
695
696/*==============================================================*/
697/* Index : ASSOCIATION19_FK */
698/*==============================================================*/
699
700
701
702
703create nonclustered index ASSOCIATION19_FK on GpsTracking (His_id ASC)
704go
705
706/*==============================================================*/
707/* Table : Groupe */
708/*==============================================================*/
709create table Groupe (
710 id int not null,
711 Inc_id int not null,
712 description varchar(254) null,
713 constraint PK_GROUPE primary key (id),
714 constraint AK_IDENTIFIANT_1_GROUPE unique (id)
715)
716go
717
718/*==============================================================*/
719/* Index : ASSOCIATION8_FK */
720/*==============================================================*/
721
722
723
724
725create nonclustered index ASSOCIATION8_FK on Groupe (Inc_id ASC)
726go
727
728/*==============================================================*/
729/* Table : HistoriqueGPS */
730/*==============================================================*/
731create table HistoriqueGPS (
732 id int not null,
733 date datetime null,
734 heure bigint null,
735 latitude numeric null,
736 longitude numeric null,
737 constraint PK_HISTORIQUEGPS primary key (id)
738)
739go
740
741/*==============================================================*/
742/* Table : Incription */
743/*==============================================================*/
744create table Incription (
745 id int not null,
746 observation int null,
747 constraint PK_INCRIPTION primary key (id),
748 constraint AK_IDENTIFIANT_1_INCRIPTI unique (id)
749)
750go
751
752/*==============================================================*/
753/* Table : Matiere */
754/*==============================================================*/
755create table Matiere (
756 id int not null,
757 Abs_id int not null,
758 description varchar(254) null,
759 constraint PK_MATIERE primary key (id)
760)
761go
762
763/*==============================================================*/
764/* Index : ASSOCIATION18_FK */
765/*==============================================================*/
766
767
768
769
770create nonclustered index ASSOCIATION18_FK on Matiere (Abs_id ASC)
771go
772
773/*==============================================================*/
774/* Table : Niveau */
775/*==============================================================*/
776create table Niveau (
777 id int not null,
778 Inc_id int not null,
779 description varchar(254) null,
780 constraint PK_NIVEAU primary key (id)
781)
782go
783
784/*==============================================================*/
785/* Index : ASSOCIATION7_FK */
786/*==============================================================*/
787
788
789
790
791create nonclustered index ASSOCIATION7_FK on Niveau (Inc_id ASC)
792go
793
794/*==============================================================*/
795/* Table : Personnes */
796/*==============================================================*/
797create table Personnes (
798 cin varchar(254) not null,
799 id int not null,
800 matricule varchar(254) not null,
801 nom varchar(254) null,
802 prenom varchar(254) null,
803 dateNaissance datetime null,
804 lieuNaissance varchar(254) null,
805 sex varchar(254) null,
806 primaryAdresse varchar(254) null,
807 secondaryAddress varchar(254) null,
808 telephone varchar(254) null,
809 email varchar(254) null,
810 login varchar(254) null,
811 password varchar(254) null,
812 localisationGPS varchar(254) null,
813 constraint PK_PERSONNES primary key (cin),
814 constraint AK_IDENTIFIANT_1_PERSONNE unique (cin),
815 constraint AK_IDENTIFIANT_2_PERSONNE unique (cin)
816)
817go
818
819/*==============================================================*/
820/* Index : ASSOCIATION11_FK */
821/*==============================================================*/
822
823
824
825
826create nonclustered index ASSOCIATION11_FK on Personnes (matricule ASC)
827go
828
829/*==============================================================*/
830/* Index : RECEPTEUR_FK */
831/*==============================================================*/
832
833
834
835
836create nonclustered index RECEPTEUR_FK on Personnes (id ASC)
837go
838
839/*==============================================================*/
840/* Table : Profil */
841/*==============================================================*/
842create table Profil (
843 code varchar(254) not null,
844 cin varchar(254) not null,
845 designation varchar(254) null,
846 isAdmin bit null,
847 isTransporteur bit null,
848 isEneignant bit null,
849 isParent bit null,
850 constraint PK_PROFIL primary key (code),
851 constraint AK_IDENTIFIANT_1_PROFIL unique (code)
852)
853go
854
855/*==============================================================*/
856/* Index : ASSOCIATION3_FK */
857/*==============================================================*/
858
859
860
861
862create nonclustered index ASSOCIATION3_FK on Profil (cin ASC)
863go
864
865/*==============================================================*/
866/* Table : Quartier */
867/*==============================================================*/
868create table Quartier (
869 id int not null,
870 num varchar(254) not null,
871 designation varchar(254) null,
872 constraint PK_QUARTIER primary key (id),
873 constraint AK_IDENTIFIANT_1_QUARTIER unique (id)
874)
875go
876
877/*==============================================================*/
878/* Index : HABITER_FK */
879/*==============================================================*/
880
881
882
883
884create nonclustered index HABITER_FK on Quartier (num ASC)
885go
886
887/*==============================================================*/
888/* Table : TransportScolaire */
889/*==============================================================*/
890create table TransportScolaire (
891 matricule varchar(254) not null,
892 id int null,
893 modele varchar(254) null,
894 marque varchar(254) null,
895 nbrPers int null,
896 constraint PK_TRANSPORTSCOLAIRE primary key (matricule)
897)
898go
899
900/*==============================================================*/
901/* Index : ASSOCIATION16_FK */
902/*==============================================================*/
903
904
905
906
907create nonclustered index ASSOCIATION16_FK on TransportScolaire (id ASC)
908go
909
910/*==============================================================*/
911/* Table : cours */
912/*==============================================================*/
913create table cours (
914 Mat_id int not null,
915 id int not null,
916 constraint PK_COURS primary key (Mat_id, id)
917)
918go
919
920/*==============================================================*/
921/* Index : COURS_FK */
922/*==============================================================*/
923
924
925
926
927create nonclustered index COURS_FK on cours (Mat_id ASC)
928go
929
930/*==============================================================*/
931/* Index : COURS_FK2 */
932/*==============================================================*/
933
934
935
936
937create nonclustered index COURS_FK2 on cours (id ASC)
938go
939
940/*==============================================================*/
941/* Table : garant */
942/*==============================================================*/
943create table garant (
944 cin varchar(254) not null,
945 num varchar(254) not null,
946 constraint PK_GARANT primary key (cin, num)
947)
948go
949
950/*==============================================================*/
951/* Index : GARANT_FK */
952/*==============================================================*/
953
954
955
956
957create nonclustered index GARANT_FK on garant (cin ASC)
958go
959
960/*==============================================================*/
961/* Index : GARANT_FK2 */
962/*==============================================================*/
963
964
965
966
967create nonclustered index GARANT_FK2 on garant (num ASC)
968go
969
970/*==============================================================*/
971/* Table : participer */
972/*==============================================================*/
973create table participer (
974 num varchar(254) not null,
975 id int not null,
976 constraint PK_PARTICIPER primary key (num, id)
977)
978go
979
980/*==============================================================*/
981/* Index : PARTICIPER_FK */
982/*==============================================================*/
983
984
985
986
987create nonclustered index PARTICIPER_FK on participer (num ASC)
988go
989
990/*==============================================================*/
991/* Index : PARTICIPER_FK2 */
992/*==============================================================*/
993
994
995
996
997create nonclustered index PARTICIPER_FK2 on participer (id ASC)
998go
999
1000/*==============================================================*/
1001/* Table : transporter */
1002/*==============================================================*/
1003create table transporter (
1004 matricule varchar(254) not null,
1005 id int not null,
1006 constraint PK_TRANSPORTER primary key (matricule, id)
1007)
1008go
1009
1010/*==============================================================*/
1011/* Index : TRANSPORTER_FK */
1012/*==============================================================*/
1013
1014
1015
1016
1017create nonclustered index TRANSPORTER_FK on transporter (matricule ASC)
1018go
1019
1020/*==============================================================*/
1021/* Index : TRANSPORTER_FK2 */
1022/*==============================================================*/
1023
1024
1025
1026
1027create nonclustered index TRANSPORTER_FK2 on transporter (id ASC)
1028go
1029
1030alter table AnneeScolaire
1031 add constraint FK_ANNEESCO_ASSOCIATI_INCRIPTI foreign key (Inc_id)
1032 references Incription (id)
1033go
1034
1035alter table Cycle
1036 add constraint FK_CYCLE_ASSOCIATI_NIVEAU foreign key (Niv_id)
1037 references Niveau (id)
1038go
1039
1040alter table Ecole
1041 add constraint FK_ECOLE_AVOIRTRAN_TRANSPOR foreign key (matricule)
1042 references TransportScolaire (matricule)
1043go
1044
1045alter table Eleve
1046 add constraint FK_ELEVE_ASSOCIATI_DECLARAT foreign key (Dec_id)
1047 references Declaration (id)
1048go
1049
1050alter table Eleve
1051 add constraint FK_ELEVE_ASSOCIATI_ABSENCES foreign key (id)
1052 references Absences (id)
1053go
1054
1055alter table Eleve
1056 add constraint FK_ELEVE_ASSOCIATI_INCRIPTI foreign key (Inc_id)
1057 references Incription (id)
1058go
1059
1060alter table GpsTracking
1061 add constraint FK_GPSTRACK_ASSOCIATI_HISTORIQ foreign key (His_id)
1062 references HistoriqueGPS (id)
1063go
1064
1065alter table Groupe
1066 add constraint FK_GROUPE_ASSOCIATI_INCRIPTI foreign key (Inc_id)
1067 references Incription (id)
1068go
1069
1070alter table Matiere
1071 add constraint FK_MATIERE_ASSOCIATI_ABSENCES foreign key (Abs_id)
1072 references Absences (id)
1073go
1074
1075alter table Niveau
1076 add constraint FK_NIVEAU_ASSOCIATI_INCRIPTI foreign key (Inc_id)
1077 references Incription (id)
1078go
1079
1080alter table Personnes
1081 add constraint FK_PERSONNE_ASSOCIATI_TRANSPOR foreign key (matricule)
1082 references TransportScolaire (matricule)
1083go
1084
1085alter table Personnes
1086 add constraint FK_PERSONNE_RECEPTEUR_DECLARAT foreign key (id)
1087 references Declaration (id)
1088go
1089
1090alter table Profil
1091 add constraint FK_PROFIL_ASSOCIATI_PERSONNE foreign key (cin)
1092 references Personnes (cin)
1093go
1094
1095alter table Quartier
1096 add constraint FK_QUARTIER_HABITER_ELEVE foreign key (num)
1097 references Eleve (num)
1098go
1099
1100alter table TransportScolaire
1101 add constraint FK_TRANSPOR_ASSOCIATI_GPSTRACK foreign key (id)
1102 references GpsTracking (id)
1103go
1104
1105alter table cours
1106 add constraint FK_COURS_COURS_MATIERE foreign key (Mat_id)
1107 references Matiere (id)
1108go
1109
1110alter table cours
1111 add constraint FK_COURS_COURS_NIVEAU foreign key (id)
1112 references Niveau (id)
1113go
1114
1115alter table garant
1116 add constraint FK_GARANT_GARANT_ELEVE foreign key (num)
1117 references Eleve (num)
1118go
1119
1120alter table garant
1121 add constraint FK_GARANT_GARANT_PERSONNE foreign key (cin)
1122 references Personnes (cin)
1123go
1124
1125alter table participer
1126 add constraint FK_PARTICIP_PARTICIPE_ACTIVITE foreign key (id)
1127 references Activites (id)
1128go
1129
1130alter table participer
1131 add constraint FK_PARTICIP_PARTICIPE_ELEVE foreign key (num)
1132 references Eleve (num)
1133go
1134
1135alter table transporter
1136 add constraint FK_TRANSPOR_TRANSPORT_QUARTIER foreign key (id)
1137 references Quartier (id)
1138go
1139
1140alter table transporter
1141 add constraint FK_TRANSPOR_TRANSPORT_TRANSPOR foreign key (matricule)
1142 references TransportScolaire (matricule)
1143go