· 8 years ago · Jul 04, 2018, 04:52 AM
1DROP DATABASE IF EXISTS Congrès;
2
3CREATE DATABASE IF NOT EXISTS Congrès;
4USE Congrès;
5# -----------------------------------------------------------------------------
6# TABLE : INSCRIPTION
7# -----------------------------------------------------------------------------
8
9CREATE TABLE IF NOT EXISTS INSCRIPTION
10 (
11 NUMINSCRIPTION DB_TEXT, 32 NOT NULL ,
12 NUMORGANISME DB_TEXT, 32 NOT NULL ,
13 NOMINSCRIT DB_TEXT, 32 NULL ,
14 PRÉNOMINSCRIT DB_TEXT, 32 NULL ,
15 ADRESSEINSCRIT DB_TEXT, 32 NULL ,
16 ACCOMPAGNATEUR DB_TEXT, 32 NULL ,
17 CATÉGORIEHOTEL DB_TEXT, 32 NULL ,
18 TÉLÉPHONE DB_TEXT, 32 NULL
19 , PRIMARY KEY (NUMINSCRIPTION)
20 )
21 comment = "";
22
23# -----------------------------------------------------------------------------
24# INDEX DE LA TABLE INSCRIPTION
25# -----------------------------------------------------------------------------
26
27
28CREATE INDEX I_FK_INSCRIPTION_ORGANISME
29 ON INSCRIPTION (NUMORGANISME ASC);
30
31# -----------------------------------------------------------------------------
32# TABLE : SESSIONS
33# -----------------------------------------------------------------------------
34
35CREATE TABLE IF NOT EXISTS SESSIONS
36 (
37 NUMSESSION DB_TEXT, 32 NOT NULL ,
38 TYPE DB_TEXT, 32 NULL ,
39 NOMPRESIDENT DB_TEXT, 32 NULL ,
40 NBPLACES DB_TEXT, 32 NULL ,
41 SALLE DB_TEXT, 32 NULL ,
42 DATESESSION DB_TEXT, 32 NULL ,
43 DÉBUTSESSION DB_TEXT, 32 NULL ,
44 TARIF DB_TEXT, 32 NULL
45 , PRIMARY KEY (NUMSESSION)
46 )
47 comment = "";
48
49# -----------------------------------------------------------------------------
50# TABLE : ACTIVITÉ
51# -----------------------------------------------------------------------------
52
53CREATE TABLE IF NOT EXISTS ACTIVITÉ
54 (
55 NUMACTIVITÉ DB_TEXT, 32 NOT NULL ,
56 DÉSIGNATION DB_TEXT, 32 NULL ,
57 DATEACTIVITE DB_TEXT, 32 NULL ,
58 DEBUTACTIVITÉ DB_TEXT, 32 NULL ,
59 NBMAXPARTICIPANT DB_TEXT, 32 NULL ,
60 PRIX DB_TEXT, 32 NULL
61 , PRIMARY KEY (NUMACTIVITÉ)
62 )
63 comment = "";
64
65# -----------------------------------------------------------------------------
66# TABLE : HOTEL
67# -----------------------------------------------------------------------------
68
69CREATE TABLE IF NOT EXISTS HOTEL
70 (
71 NUMHOTEL DB_TEXT, 32 NOT NULL ,
72 NOMHOTEL DB_TEXT, 32 NULL ,
73 TYPEHOTEL DB_TEXT, 32 NULL ,
74 ACCOMPAGNATEUR DB_TEXT, 32 NULL ,
75 PRIX DB_TEXT, 32 NULL
76 , PRIMARY KEY (NUMHOTEL)
77 )
78 comment = "";
79
80# -----------------------------------------------------------------------------
81# TABLE : ORGANISME
82# -----------------------------------------------------------------------------
83
84CREATE TABLE IF NOT EXISTS ORGANISME
85 (
86 NUMORGANISME DB_TEXT, 32 NOT NULL ,
87 NOMORGANISME DB_TEXT, 32 NULL ,
88 ADRESSEORGANISME DB_TEXT, 32 NULL ,
89 ACOMPTE DB_TEXT, 32 NULL ,
90 FACTURE DB_TEXT, 32 NULL
91 , PRIMARY KEY (NUMORGANISME)
92 )
93 comment = "";
94
95# -----------------------------------------------------------------------------
96# TABLE : LOGER
97# -----------------------------------------------------------------------------
98
99CREATE TABLE IF NOT EXISTS LOGER
100 (
101 NUMINSCRIPTION DB_TEXT, 32 NOT NULL ,
102 NUMHOTEL DB_TEXT, 32 NOT NULL
103 , PRIMARY KEY (NUMINSCRIPTION,NUMHOTEL)
104 )
105 comment = "";
106
107# -----------------------------------------------------------------------------
108# INDEX DE LA TABLE LOGER
109# -----------------------------------------------------------------------------
110
111
112CREATE INDEX I_FK_LOGER_INSCRIPTION
113 ON LOGER (NUMINSCRIPTION ASC);
114
115CREATE INDEX I_FK_LOGER_HOTEL
116 ON LOGER (NUMHOTEL ASC);
117
118# -----------------------------------------------------------------------------
119# TABLE : PARTICIPESESSION
120# -----------------------------------------------------------------------------
121
122CREATE TABLE IF NOT EXISTS PARTICIPESESSION
123 (
124 NUMSESSION DB_TEXT, 32 NOT NULL ,
125 NUMINSCRIPTION DB_TEXT, 32 NOT NULL
126 , PRIMARY KEY (NUMSESSION,NUMINSCRIPTION)
127 )
128 comment = "";
129
130# -----------------------------------------------------------------------------
131# INDEX DE LA TABLE PARTICIPESESSION
132# -----------------------------------------------------------------------------
133
134
135CREATE INDEX I_FK_PARTICIPESESSION_SESSIONS
136 ON PARTICIPESESSION (NUMSESSION ASC);
137
138CREATE INDEX I_FK_PARTICIPESESSION_INSCRIPTION
139 ON PARTICIPESESSION (NUMINSCRIPTION ASC);
140
141# -----------------------------------------------------------------------------
142# TABLE : PARTICIPEACTIVITÉ
143# -----------------------------------------------------------------------------
144
145CREATE TABLE IF NOT EXISTS PARTICIPEACTIVITÉ
146 (
147 NUMINSCRIPTION DB_TEXT, 32 NOT NULL ,
148 NUMACTIVITÉ DB_TEXT, 32 NOT NULL
149 , PRIMARY KEY (NUMINSCRIPTION,NUMACTIVITÉ)
150 )
151 comment = "";
152
153# -----------------------------------------------------------------------------
154# INDEX DE LA TABLE PARTICIPEACTIVITÉ
155# -----------------------------------------------------------------------------
156
157
158CREATE INDEX I_FK_PARTICIPEACTIVITÉ_INSCRIPTION
159 ON PARTICIPEACTIVITÉ (NUMINSCRIPTION ASC);
160
161CREATE INDEX I_FK_PARTICIPEACTIVITÉ_ACTIVITÉ
162 ON PARTICIPEACTIVITÉ (NUMACTIVITÉ ASC);
163
164
165# -----------------------------------------------------------------------------
166# CREATION DES REFERENCES DE TABLE
167# -----------------------------------------------------------------------------
168
169
170ALTER TABLE INSCRIPTION
171 ADD FOREIGN KEY FK_INSCRIPTION_ORGANISME (NUMORGANISME)
172 REFERENCES ORGANISME (NUMORGANISME) ;
173
174
175ALTER TABLE LOGER
176 ADD FOREIGN KEY FK_LOGER_INSCRIPTION (NUMINSCRIPTION)
177 REFERENCES INSCRIPTION (NUMINSCRIPTION) ;
178
179
180ALTER TABLE LOGER
181 ADD FOREIGN KEY FK_LOGER_HOTEL (NUMHOTEL)
182 REFERENCES HOTEL (NUMHOTEL) ;
183
184
185ALTER TABLE PARTICIPESESSION
186 ADD FOREIGN KEY FK_PARTICIPESESSION_SESSIONS (NUMSESSION)
187 REFERENCES SESSIONS (NUMSESSION) ;
188
189
190ALTER TABLE PARTICIPESESSION
191 ADD FOREIGN KEY FK_PARTICIPESESSION_INSCRIPTION (NUMINSCRIPTION)
192 REFERENCES INSCRIPTION (NUMINSCRIPTION) ;
193
194
195ALTER TABLE PARTICIPEACTIVITÉ
196 ADD FOREIGN KEY FK_PARTICIPEACTIVITÉ_INSCRIPTION (NUMINSCRIPTION)
197 REFERENCES INSCRIPTION (NUMINSCRIPTION) ;
198
199
200ALTER TABLE PARTICIPEACTIVITÉ
201 ADD FOREIGN KEY FK_PARTICIPEACTIVITÉ_ACTIVITÉ (NUMACTIVITÉ)
202 REFERENCES ACTIVITÉ (NUMACTIVITÉ) ;