· 8 years ago · Apr 17, 2018, 06:58 AM
1
2USE 21701926_dev;
3# -----------------------------------------------------------------------------
4# TABLE : ENS_TEST
5# -----------------------------------------------------------------------------
6
7CREATE TABLE IF NOT EXISTS ENS_TEST
8 (
9 A INT NOT NULL ,
10 B INT NOT NULL ,
11 C INT NOT NULL
12 , PRIMARY KEY (A,B,C)
13 )
14 comment = "";
15
16# -----------------------------------------------------------------------------
17# TABLE : ENS_CHARGE_ADMIN
18# -----------------------------------------------------------------------------
19
20CREATE TABLE IF NOT EXISTS ENS_CHARGE_ADMIN
21 (
22 CHA_CODE DECIMAL(2) NOT NULL ,
23 CHA_LIBELLE VARCHAR(40) NULL
24 , PRIMARY KEY (CHA_CODE)
25 )
26 comment = "";
27
28# -----------------------------------------------------------------------------
29# TABLE : ENS_ETUDIANT
30# -----------------------------------------------------------------------------
31
32CREATE TABLE IF NOT EXISTS ENS_ETUDIANT
33 (
34 ETU_NUM DECIMAL(3) NOT NULL ,
35 ETU_NOM VARCHAR(20) NOT NULL ,
36 ETU_PRENOM VARCHAR(20) NOT NULL
37 , PRIMARY KEY (ETU_NUM)
38 )
39 comment = "";
40
41# -----------------------------------------------------------------------------
42# TABLE : ENS_FORMATION
43# -----------------------------------------------------------------------------
44
45CREATE TABLE IF NOT EXISTS ENS_FORMATION
46 (
47 FOR_NUM DECIMAL(3) NOT NULL ,
48 FOR_INTITULE VARCHAR(10) NOT NULL ,
49 FOR_DESCRIPTION VARCHAR(100) NULL
50 DEFAULT "null"
51 , PRIMARY KEY (FOR_NUM)
52 )
53 comment = "";
54
55# -----------------------------------------------------------------------------
56# TABLE : ENS_CAMPUS
57# -----------------------------------------------------------------------------
58
59CREATE TABLE IF NOT EXISTS ENS_CAMPUS
60 (
61 CAM_CODE CHAR(32) NOT NULL ,
62 CAM_VILLE VARCHAR(20) NOT NULL ,
63 CAM_ADRESSE VARCHAR(50) NULL
64 , PRIMARY KEY (CAM_CODE,CAM_VILLE)
65 )
66 comment = "";
67
68# -----------------------------------------------------------------------------
69# TABLE : ENS_ENSEIGNANT
70# -----------------------------------------------------------------------------
71
72CREATE TABLE IF NOT EXISTS ENS_ENSEIGNANT
73 (
74 ENS_CODE DECIMAL(3) NOT NULL ,
75 ENS_NOM VARCHAR(32) NULL ,
76 ENS_PRENOM VARCHAR(32) NULL ,
77 ENS_COURRIEL CHAR(32) NULL ,
78 ENS_CATEGORIE VARCHAR(20) NULL
79 , PRIMARY KEY (ENS_CODE)
80 )
81 comment = "";
82
83# -----------------------------------------------------------------------------
84# TABLE : ENS_COURS
85# -----------------------------------------------------------------------------
86
87CREATE TABLE IF NOT EXISTS ENS_COURS
88 (
89 COU_NUMERO DECIMAL(2) NOT NULL ,
90 COU_NOM VARCHAR(10) NULL ,
91 COU_DESCRIPTION VARCHAR(100) NULL ,
92 COU_TYPE VARCHAR(2) NULL
93 , PRIMARY KEY (COU_NUMERO)
94 )
95 comment = "";
96
97# -----------------------------------------------------------------------------
98# TABLE : ENS_INSCRIRE
99# -----------------------------------------------------------------------------
100
101CREATE TABLE IF NOT EXISTS ENS_INSCRIRE
102 (
103 ETU_NUM DECIMAL(3) NOT NULL ,
104 FOR_NUM DECIMAL(3) NOT NULL ,
105 INS_DATE_DEBUT DATE NULL ,
106 INS_DATE_FIN DATE NULL
107 , PRIMARY KEY (ETU_NUM,FOR_NUM)
108 )
109 comment = "";
110
111# -----------------------------------------------------------------------------
112# INDEX DE LA TABLE ENS_INSCRIRE
113# -----------------------------------------------------------------------------
114
115
116CREATE INDEX I_FK_ENS_INSCRIRE_ENS_ETUDIANT
117 ON ENS_INSCRIRE (ETU_NUM ASC);
118
119
120CREATE INDEX I_FK_ENS_INSCRIRE_ENS_FORMATIO
121 ON ENS_INSCRIRE (FOR_NUM ASC);
122
123
124# -----------------------------------------------------------------------------
125# TABLE : ENS_DISPENSER
126# -----------------------------------------------------------------------------
127
128CREATE TABLE IF NOT EXISTS ENS_DISPENSER
129 (
130 COU_NUMERO DECIMAL(2) NOT NULL ,
131 ENS_CODE DECIMAL(3) NOT NULL
132 , PRIMARY KEY (COU_NUMERO,ENS_CODE)
133 )
134 comment = "";
135
136# -----------------------------------------------------------------------------
137# INDEX DE LA TABLE ENS_DISPENSER
138# -----------------------------------------------------------------------------
139
140
141CREATE INDEX I_FK_ENS_DISPENSER_ENS_COURS
142 ON ENS_DISPENSER (COU_NUMERO ASC);
143
144
145CREATE INDEX I_FK_ENS_DISPENSER_ENS_ENSEIGN
146 ON ENS_DISPENSER (ENS_CODE ASC);
147
148
149# -----------------------------------------------------------------------------
150# TABLE : ENS_PROPOSER
151# -----------------------------------------------------------------------------
152
153CREATE TABLE IF NOT EXISTS ENS_PROPOSER
154 (
155 FOR_NUM DECIMAL(3) NOT NULL ,
156 COU_NUMERO DECIMAL(2) NOT NULL ,
157 PRO_COEF DECIMAL(10,2) NULL ,
158 PRO_DUREE DECIMAL(3) NULL
159 , PRIMARY KEY (FOR_NUM,COU_NUMERO)
160 )
161 comment = "";
162
163# -----------------------------------------------------------------------------
164# INDEX DE LA TABLE ENS_PROPOSER
165# -----------------------------------------------------------------------------
166
167
168CREATE INDEX I_FK_ENS_PROPOSER_ENS_FORMATIO
169 ON ENS_PROPOSER (FOR_NUM ASC);
170
171
172CREATE INDEX I_FK_ENS_PROPOSER_ENS_COURS
173 ON ENS_PROPOSER (COU_NUMERO ASC);
174
175
176# -----------------------------------------------------------------------------
177# TABLE : ENS_SITUER
178# -----------------------------------------------------------------------------
179
180CREATE TABLE IF NOT EXISTS ENS_SITUER
181 (
182 FOR_NUM DECIMAL(3) NOT NULL ,
183 CAM_CODE CHAR(32) NOT NULL ,
184 CAM_VILLE VARCHAR(20) NOT NULL
185 , PRIMARY KEY (FOR_NUM,CAM_CODE,CAM_VILLE)
186 )
187 comment = "";
188
189# -----------------------------------------------------------------------------
190# INDEX DE LA TABLE ENS_SITUER
191# -----------------------------------------------------------------------------
192
193
194CREATE INDEX I_FK_ENS_SITUER_ENS_FORMATION
195 ON ENS_SITUER (FOR_NUM ASC);
196
197
198CREATE INDEX I_FK_ENS_SITUER_ENS_CAMPUS
199 ON ENS_SITUER (CAM_CODE ASC,CAM_VILLE ASC);
200
201
202# -----------------------------------------------------------------------------
203# TABLE : ENS_AVOIR
204# -----------------------------------------------------------------------------
205
206CREATE TABLE IF NOT EXISTS ENS_AVOIR
207 (
208 ENS_CODE DECIMAL(3) NOT NULL ,
209 CHA_CODE DECIMAL(2) NOT NULL ,
210 AVO_DATE_DEBUT DATE NULL ,
211 AVO_DATE_FIN DATE NULL
212 , PRIMARY KEY (ENS_CODE,CHA_CODE)
213 )
214 comment = "";
215
216# -----------------------------------------------------------------------------
217# INDEX DE LA TABLE ENS_AVOIR
218# -----------------------------------------------------------------------------
219
220
221CREATE INDEX I_FK_ENS_AVOIR_ENS_ENSEIGNANT
222 ON ENS_AVOIR (ENS_CODE ASC);
223
224
225CREATE INDEX I_FK_ENS_AVOIR_ENS_CHARGE_ADMI
226 ON ENS_AVOIR (CHA_CODE ASC);
227
228
229# -----------------------------------------------------------------------------
230# TABLE : ENS_BINOME
231# -----------------------------------------------------------------------------
232
233CREATE TABLE IF NOT EXISTS ENS_BINOME
234 (
235 ETU_NUM_1 DECIMAL(3) NOT NULL ,
236 ETU_NUM_2 DECIMAL(3) NOT NULL ,
237 BIN_COMPTE_ORACLE VARCHAR(8) NULL
238 , PRIMARY KEY (ETU_NUM_2,ETU_NUM_1)
239 )
240 comment = "";
241
242# -----------------------------------------------------------------------------
243# INDEX DE LA TABLE ENS_BINOME
244# -----------------------------------------------------------------------------
245
246
247CREATE INDEX I_FK_ENS_BINOME_ENS_ETUDIANT
248 ON ENS_BINOME (ETU_NUM_2 ASC);
249
250
251CREATE INDEX I_FK_ENS_BINOME_ENS_ETUDIANT1
252 ON ENS_BINOME (ETU_NUM_1 ASC);
253
254
255
256# -----------------------------------------------------------------------------
257# CREATION DES REFERENCES DE TABLE
258# -----------------------------------------------------------------------------
259
260
261ALTER TABLE ENS_INSCRIRE
262 ADD FOREIGN KEY FK_ENS_INSCRIRE_ENS_ETUDIANT (ETU_NUM)
263 REFERENCES ENS_ETUDIANT (ETU_NUM) ;
264
265
266ALTER TABLE ENS_INSCRIRE
267 ADD FOREIGN KEY FK_ENS_INSCRIRE_ENS_FORMATION (FOR_NUM)
268 REFERENCES ENS_FORMATION (FOR_NUM) ;
269
270
271ALTER TABLE ENS_DISPENSER
272 ADD FOREIGN KEY FK_ENS_DISPENSER_ENS_COURS (COU_NUMERO)
273 REFERENCES ENS_COURS (COU_NUMERO) ;
274
275
276ALTER TABLE ENS_DISPENSER
277 ADD FOREIGN KEY FK_ENS_DISPENSER_ENS_ENSEIGNAN (ENS_CODE)
278 REFERENCES ENS_ENSEIGNANT (ENS_CODE) ;
279
280
281ALTER TABLE ENS_PROPOSER
282 ADD FOREIGN KEY FK_ENS_PROPOSER_ENS_FORMATION (FOR_NUM)
283 REFERENCES ENS_FORMATION (FOR_NUM) ;
284
285
286ALTER TABLE ENS_PROPOSER
287 ADD FOREIGN KEY FK_ENS_PROPOSER_ENS_COURS (COU_NUMERO)
288 REFERENCES ENS_COURS (COU_NUMERO) ;
289
290
291ALTER TABLE ENS_SITUER
292 ADD FOREIGN KEY FK_ENS_SITUER_ENS_FORMATION (FOR_NUM)
293 REFERENCES ENS_FORMATION (FOR_NUM) ;
294
295
296ALTER TABLE ENS_SITUER
297 ADD FOREIGN KEY FK_ENS_SITUER_ENS_CAMPUS (CAM_CODE,CAM_VILLE)
298 REFERENCES ENS_CAMPUS (CAM_CODE,CAM_VILLE) ;
299
300
301ALTER TABLE ENS_AVOIR
302 ADD FOREIGN KEY FK_ENS_AVOIR_ENS_ENSEIGNANT (ENS_CODE)
303 REFERENCES ENS_ENSEIGNANT (ENS_CODE) ;
304
305
306ALTER TABLE ENS_AVOIR
307 ADD FOREIGN KEY FK_ENS_AVOIR_ENS_CHARGE_ADMIN (CHA_CODE)
308 REFERENCES ENS_CHARGE_ADMIN (CHA_CODE) ;
309
310
311ALTER TABLE ENS_BINOME
312 ADD FOREIGN KEY FK_ENS_BINOME_ENS_ETUDIANT (ETU_NUM_2)
313 REFERENCES ENS_ETUDIANT (ETU_NUM) ;
314
315
316ALTER TABLE ENS_BINOME
317 ADD FOREIGN KEY FK_ENS_BINOME_ENS_ETUDIANT1 (ETU_NUM_1)
318 REFERENCES ENS_ETUDIANT (ETU_NUM) ;