· 8 years ago · Jan 24, 2018, 10:48 AM
1DROP DATABASE IF EXISTS MLR4;
2
3CREATE DATABASE IF NOT EXISTS MLR4;
4USE MLR4;
5# -----------------------------------------------------------------------------
6# TABLE : CONGÉ
7# -----------------------------------------------------------------------------
8
9CREATE TABLE IF NOT EXISTS CONGÉ
10 (
11 MATRICULESALARIÉ CHAR(32) NOT NULL ,
12 DATEDEBUTC CHAR(32) NOT NULL ,
13 CODMOTIFE CHAR(32) NOT NULL ,
14 DATEFINC CHAR(32) NULL
15 , PRIMARY KEY (MATRICULESALARIÉ,DATEDEBUTC)
16 )
17 comment = "";
18
19# -----------------------------------------------------------------------------
20# INDEX DE LA TABLE CONGÉ
21# -----------------------------------------------------------------------------
22
23
24CREATE INDEX I_FK_CONGÉ_TYPE_CONGÉ
25 ON CONGÉ (CODMOTIFE ASC);
26
27CREATE INDEX I_FK_CONGÉ_SALARIÉ
28 ON CONGÉ (MATRICULESALARIÉ ASC);
29
30# -----------------------------------------------------------------------------
31# TABLE : DIVISION
32# -----------------------------------------------------------------------------
33
34CREATE TABLE IF NOT EXISTS DIVISION
35 (
36 NUMDIV CHAR(32) NOT NULL ,
37 LIBELDIV CHAR(32) NULL
38 , PRIMARY KEY (NUMDIV)
39 )
40 comment = "";
41
42# -----------------------------------------------------------------------------
43# TABLE : CERTIFICATION
44# -----------------------------------------------------------------------------
45
46CREATE TABLE IF NOT EXISTS CERTIFICATION
47 (
48 CODEQUALIF CHAR(32) NOT NULL ,
49 CODESOCIÉTÉ CHAR(32) NOT NULL ,
50 TIREQUALIF CHAR(32) NULL
51 , PRIMARY KEY (CODEQUALIF)
52 )
53 comment = "";
54
55# -----------------------------------------------------------------------------
56# INDEX DE LA TABLE CERTIFICATION
57# -----------------------------------------------------------------------------
58
59
60CREATE INDEX I_FK_CERTIFICATION_SOCIÉTÉ
61 ON CERTIFICATION (CODESOCIÉTÉ ASC);
62
63# -----------------------------------------------------------------------------
64# TABLE : TYPE_CONGÉ
65# -----------------------------------------------------------------------------
66
67CREATE TABLE IF NOT EXISTS TYPE_CONGÉ
68 (
69 CODMOTIFE CHAR(32) NOT NULL ,
70 TYPEMOTIF CHAR(32) NULL
71 , PRIMARY KEY (CODMOTIFE)
72 )
73 comment = "";
74
75# -----------------------------------------------------------------------------
76# TABLE : SALARIÉ
77# -----------------------------------------------------------------------------
78
79CREATE TABLE IF NOT EXISTS SALARIÉ
80 (
81 MATRICULESALARIÉ CHAR(32) NOT NULL ,
82 NOMSALARIÉ CHAR(32) NULL ,
83 PRÉNOMSALARIÉ CHAR(32) NULL ,
84 ADRESSESALARIÉ CHAR(32) NULL ,
85 NATURECONTRAT CHAR(32) NULL ,
86 DATEEMBAUCHE CHAR(32) NULL ,
87 DATEVISITEM CHAR(32) NULL
88 , PRIMARY KEY (MATRICULESALARIÉ)
89 )
90 comment = "";
91
92# -----------------------------------------------------------------------------
93# TABLE : DIPLÔME
94# -----------------------------------------------------------------------------
95
96CREATE TABLE IF NOT EXISTS DIPLÔME
97 (
98 CODEQUALIF CHAR(32) NOT NULL ,
99 NIVEAU CHAR(32) NULL ,
100 TIREQUALIF CHAR(32) NULL
101 , PRIMARY KEY (CODEQUALIF)
102 )
103 comment = "";
104
105# -----------------------------------------------------------------------------
106# TABLE : CLIENT
107# -----------------------------------------------------------------------------
108
109CREATE TABLE IF NOT EXISTS CLIENT
110 (
111 NUMCLIENT CHAR(32) NOT NULL ,
112 NUMDIV CHAR(32) NOT NULL ,
113 RAISONSOCCLIENT CHAR(32) NULL ,
114 RUECLIENT CHAR(32) NULL ,
115 CODEPOSTCLIENT CHAR(32) NULL ,
116 VILLECLIENT CHAR(32) NULL ,
117 NOMCONTACT CHAR(32) NULL ,
118 NOTELCLIENT CHAR(32) NULL ,
119 NOFAXCLIENT CHAR(32) NULL
120 , PRIMARY KEY (NUMCLIENT)
121 )
122 comment = "";
123
124# -----------------------------------------------------------------------------
125# INDEX DE LA TABLE CLIENT
126# -----------------------------------------------------------------------------
127
128
129CREATE INDEX I_FK_CLIENT_DIVISION
130 ON CLIENT (NUMDIV ASC);
131
132# -----------------------------------------------------------------------------
133# TABLE : QUALIFICATION
134# -----------------------------------------------------------------------------
135
136CREATE TABLE IF NOT EXISTS QUALIFICATION
137 (
138 CODEQUALIF CHAR(32) NOT NULL ,
139 TIREQUALIF CHAR(32) NULL
140 , PRIMARY KEY (CODEQUALIF)
141 )
142 comment = "";
143
144# -----------------------------------------------------------------------------
145# TABLE : AFFECTATION
146# -----------------------------------------------------------------------------
147
148CREATE TABLE IF NOT EXISTS AFFECTATION
149 (
150 MATRICULESALARIÉ CHAR(32) NOT NULL ,
151 RANG CHAR(32) NOT NULL ,
152 NUMCOMPTE CHAR(32) NOT NULL ,
153 NUMTYPE CHAR(32) NOT NULL ,
154 NUMCLIENT CHAR(32) NOT NULL ,
155 DATEDEBUT CHAR(32) NULL ,
156 DATEFIN CHAR(32) NULL
157 , PRIMARY KEY (MATRICULESALARIÉ,RANG)
158 )
159 comment = "";
160
161# -----------------------------------------------------------------------------
162# INDEX DE LA TABLE AFFECTATION
163# -----------------------------------------------------------------------------
164
165
166CREATE INDEX I_FK_AFFECTATION_COMPTE
167 ON AFFECTATION (NUMCLIENT ASC,NUMCOMPTE ASC);
168
169CREATE INDEX I_FK_AFFECTATION_TYPE_POSTE
170 ON AFFECTATION (NUMTYPE ASC);
171
172CREATE INDEX I_FK_AFFECTATION_SALARIÉ
173 ON AFFECTATION (MATRICULESALARIÉ ASC);
174
175# -----------------------------------------------------------------------------
176# TABLE : SOCIÉTÉ
177# -----------------------------------------------------------------------------
178
179CREATE TABLE IF NOT EXISTS SOCIÉTÉ
180 (
181 CODESOCIÉTÉ CHAR(32) NOT NULL ,
182 NOMSOCIÉTÉ CHAR(32) NULL
183 , PRIMARY KEY (CODESOCIÉTÉ)
184 )
185 comment = "";
186
187# -----------------------------------------------------------------------------
188# TABLE : COMPTE
189# -----------------------------------------------------------------------------
190
191CREATE TABLE IF NOT EXISTS COMPTE
192 (
193 NUMCLIENT CHAR(32) NOT NULL ,
194 NUMCOMPTE CHAR(32) NOT NULL ,
195 RUECOMPTE CHAR(32) NULL ,
196 CODEPOSTCOMPTE CHAR(32) NULL ,
197 VILLECOMPTE CHAR(32) NULL ,
198 DIRECTEURSITE CHAR(32) NULL ,
199 NOTELCOMPTE CHAR(32) NULL ,
200 NOFAXCOMPTE CHAR(32) NULL
201 , PRIMARY KEY (NUMCLIENT,NUMCOMPTE)
202 )
203 comment = "";
204
205# -----------------------------------------------------------------------------
206# INDEX DE LA TABLE COMPTE
207# -----------------------------------------------------------------------------
208
209
210CREATE INDEX I_FK_COMPTE_CLIENT
211 ON COMPTE (NUMCLIENT ASC);
212
213# -----------------------------------------------------------------------------
214# TABLE : TYPE_POSTE
215# -----------------------------------------------------------------------------
216
217CREATE TABLE IF NOT EXISTS TYPE_POSTE
218 (
219 NUMTYPE CHAR(32) NOT NULL ,
220 LIBELTYPE CHAR(32) NULL
221 , PRIMARY KEY (NUMTYPE)
222 )
223 comment = "";
224
225# -----------------------------------------------------------------------------
226# TABLE : OBTENIR
227# -----------------------------------------------------------------------------
228
229CREATE TABLE IF NOT EXISTS OBTENIR
230 (
231 MATRICULESALARIÉ CHAR(32) NOT NULL ,
232 CODEQUALIF CHAR(32) NOT NULL ,
233 DATEOBTENTION CHAR(32) NULL
234 , PRIMARY KEY (MATRICULESALARIÉ,CODEQUALIF)
235 )
236 comment = "";
237
238# -----------------------------------------------------------------------------
239# INDEX DE LA TABLE OBTENIR
240# -----------------------------------------------------------------------------
241
242
243CREATE INDEX I_FK_OBTENIR_SALARIÉ
244 ON OBTENIR (MATRICULESALARIÉ ASC);
245
246CREATE INDEX I_FK_OBTENIR_QUALIFICATION
247 ON OBTENIR (CODEQUALIF ASC);
248
249
250# -----------------------------------------------------------------------------
251# CREATION DES REFERENCES DE TABLE
252# -----------------------------------------------------------------------------
253
254
255ALTER TABLE CONGÉ
256 ADD FOREIGN KEY FK_CONGÉ_TYPE_CONGÉ (CODMOTIFE)
257 REFERENCES TYPE_CONGÉ (CODMOTIFE) ;
258
259
260ALTER TABLE CONGÉ
261 ADD FOREIGN KEY FK_CONGÉ_SALARIÉ (MATRICULESALARIÉ)
262 REFERENCES SALARIÉ (MATRICULESALARIÉ) ;
263
264
265ALTER TABLE CERTIFICATION
266 ADD FOREIGN KEY FK_CERTIFICATION_SOCIÉTÉ (CODESOCIÉTÉ)
267 REFERENCES SOCIÉTÉ (CODESOCIÉTÉ) ;
268
269
270ALTER TABLE CERTIFICATION
271 ADD FOREIGN KEY FK_CERTIFICATION_QUALIFICATION (CODEQUALIF)
272 REFERENCES QUALIFICATION (CODEQUALIF) ;
273
274
275ALTER TABLE DIPLÔME
276 ADD FOREIGN KEY FK_DIPLÔME_QUALIFICATION (CODEQUALIF)
277 REFERENCES QUALIFICATION (CODEQUALIF) ;
278
279
280ALTER TABLE CLIENT
281 ADD FOREIGN KEY FK_CLIENT_DIVISION (NUMDIV)
282 REFERENCES DIVISION (NUMDIV) ;
283
284
285ALTER TABLE AFFECTATION
286 ADD FOREIGN KEY FK_AFFECTATION_COMPTE (NUMCLIENT,NUMCOMPTE)
287 REFERENCES COMPTE (NUMCLIENT,NUMCOMPTE) ;
288
289
290ALTER TABLE AFFECTATION
291 ADD FOREIGN KEY FK_AFFECTATION_TYPE_POSTE (NUMTYPE)
292 REFERENCES TYPE_POSTE (NUMTYPE) ;
293
294
295ALTER TABLE AFFECTATION
296 ADD FOREIGN KEY FK_AFFECTATION_SALARIÉ (MATRICULESALARIÉ)
297 REFERENCES SALARIÉ (MATRICULESALARIÉ) ;
298
299
300ALTER TABLE COMPTE
301 ADD FOREIGN KEY FK_COMPTE_CLIENT (NUMCLIENT)
302 REFERENCES CLIENT (NUMCLIENT) ;
303
304
305ALTER TABLE OBTENIR
306 ADD FOREIGN KEY FK_OBTENIR_SALARIÉ (MATRICULESALARIÉ)
307 REFERENCES SALARIÉ (MATRICULESALARIÉ) ;
308
309
310ALTER TABLE OBTENIR
311 ADD FOREIGN KEY FK_OBTENIR_QUALIFICATION (CODEQUALIF)
312 REFERENCES QUALIFICATION (CODEQUALIF) ;