-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathPlongee_bd TABLE SQL.SQL
219 lines (165 loc) · 7.12 KB
/
Plongee_bd TABLE SQL.SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
drop table if exists PLO_CONCERNER;
drop table if exists PLO_PALANQUEE;
drop table if exists PLO_PLONGEE;
drop table if exists PLO_DIRECTEUR;
drop table if exists PLO_SECURITE_DE_SURFACE;
drop table if exists PLO_PLONGEUR;
drop table if exists SITE;
drop table if exists PLO_EMBARCATION;
drop table if exists PLO_PERSONNE;
drop table if exists PLO_APTITUDE;
CREATE TABLE PLO_DIRECTEUR
(
PER_NUM INT NOT NULL,
PRIMARY KEY (PER_NUM)
);
CREATE TABLE PLO_PERSONNE
(
PER_NUM INT NOT NULL AUTO_INCREMENT,
PER_NOM VARCHAR(30) NULL,
PER_PRENOM VARCHAR(30) NULL,
PRIMARY KEY (PER_NUM)
);
CREATE TABLE PLO_APTITUDE
(
APT_CODE VARCHAR(10) NOT NULL ,
APT_LIBELLE VARCHAR(100) NULL
, PRIMARY KEY (APT_CODE)
);
CREATE TABLE PLO_EMBARCATION
(
EMB_NUM CHAR(32) NOT NULL ,
EMB_NOM VARCHAR(64) NULL
, PRIMARY KEY (EMB_NUM)
);
CREATE TABLE PLO_PLONGEUR
(
PER_NUM INT NOT NULL ,
APT_CODE VARCHAR(10) NOT NULL
, PRIMARY KEY (PER_NUM)
);
CREATE INDEX I_FK_PLO_PLONGEUR_PLO_APTITUDE
ON PLO_PLONGEUR (APT_CODE ASC);
CREATE TABLE PLO_SECURITE_DE_SURFACE
(
PER_NUM INT NOT NULL
, PRIMARY KEY (PER_NUM)
);
CREATE TABLE PLO_PALANQUEE
(
PLO_DATE DATE NOT NULL ,
PLO_MATIN_APRESMIDI CHAR(1) NOT NULL ,
PAL_NUM INT NOT NULL ,
PAL_PROFONDEUR_MAX INT NULL ,
PAL_DUREE_MAX INT NULL ,
PAL_HEURE_IMMERSION DATE NULL ,
PAL_HEURE_SORTIE_EAU DATE NULL ,
PAL_PROFONDEUR_REELLE INT NULL ,
PAL_DUREE_FOND INT NULL
, PRIMARY KEY (PLO_DATE,PLO_MATIN_APRESMIDI,PAL_NUM)
);
CREATE INDEX I_FK_PLO_PALANQUEE_PLO_PLONGEE
ON PLO_PALANQUEE (PLO_DATE ASC,PLO_MATIN_APRESMIDI ASC);
CREATE TABLE PLO_PLONGEE
(
PLO_DATE DATE NOT NULL ,
PLO_MATIN_APRESMIDI CHAR(1) NOT NULL ,
SIT_NUM INT NOT NULL ,
EMB_NUM CHAR(32) NOT NULL ,
PER_NUM_DIR INT NOT NULL ,
PER_NUM_SECU INT NOT NULL ,
PLO_EFFECTIF_PLONGEURS INT NULL ,
PLO_EFFECTIF_BATEAU INT NULL ,
PLO_NB_PALANQUEES INT NULL
, PRIMARY KEY (PLO_DATE,PLO_MATIN_APRESMIDI)
);
CREATE INDEX I_FK_PLO_PLONGEE_PLO_DIRECTEUR
ON PLO_PLONGEE (PER_NUM_DIR ASC);
CREATE INDEX I_FK_PLO_PLONGEE_SITE
ON PLO_PLONGEE (SIT_NUM ASC);
CREATE INDEX I_FK_PLO_PLONGEE_PLO_SECURITE_DE_SURFACE
ON PLO_PLONGEE (PER_NUM_SECU ASC);
CREATE INDEX I_FK_PLO_PLONGEE_PLO_EMBARCATION
ON PLO_PLONGEE (EMB_NUM ASC);
CREATE TABLE SITE
(
SIT_NUM INT NOT NULL ,
SIT_NOM VARCHAR(128) NULL ,
SIT_LOCALISATION VARCHAR(64) NULL
, PRIMARY KEY (SIT_NUM)
);
CREATE TABLE PLO_CONCERNER
(
PLO_DATE DATE NOT NULL ,
PLO_MATIN_APRESMIDI CHAR(1) NOT NULL ,
PAL_NUM INT NOT NULL ,
PER_NUM INT NOT NULL
, PRIMARY KEY (PLO_DATE,PLO_MATIN_APRESMIDI,PAL_NUM,PER_NUM)
);
CREATE INDEX I_FK_PLO_CONCERNER_PLO_PALANQUEE
ON PLO_CONCERNER (PLO_DATE ASC,PLO_MATIN_APRESMIDI ASC,PAL_NUM ASC);
CREATE INDEX I_FK_PLO_CONCERNER_PLO_PLONGEUR
ON PLO_CONCERNER (PER_NUM ASC);
ALTER TABLE PLO_DIRECTEUR
ADD FOREIGN KEY FK_PLO_DIRECTEUR_PLO_PERSONNE (PER_NUM)
REFERENCES PLO_PERSONNE (PER_NUM) ;
ALTER TABLE PLO_PLONGEUR
ADD FOREIGN KEY FK_PLO_PLONGEUR_PLO_APTITUDE (APT_CODE)
REFERENCES PLO_APTITUDE (APT_CODE) ;
ALTER TABLE PLO_PLONGEUR
ADD FOREIGN KEY FK_PLO_PLONGEUR_PLO_PERSONNE (PER_NUM)
REFERENCES PLO_PERSONNE (PER_NUM) ;
ALTER TABLE PLO_SECURITE_DE_SURFACE
ADD FOREIGN KEY FK_PLO_SECURITE_DE_SURFACE_PLO_PERSONNE (PER_NUM)
REFERENCES PLO_PERSONNE (PER_NUM) ;
ALTER TABLE PLO_PALANQUEE
ADD FOREIGN KEY FK_PLO_PALANQUEE_PLO_PLONGEE (PLO_DATE,PLO_MATIN_APRESMIDI)
REFERENCES PLO_PLONGEE (PLO_DATE,PLO_MATIN_APRESMIDI) ;
ALTER TABLE PLO_PLONGEE
ADD FOREIGN KEY FK_PLO_PLONGEE_PLO_DIRECTEUR (PER_NUM_DIR)
REFERENCES PLO_DIRECTEUR (PER_NUM) ;
ALTER TABLE PLO_PLONGEE
ADD FOREIGN KEY FK_PLO_PLONGEE_SITE (SIT_NUM)
REFERENCES SITE (SIT_NUM) ;
ALTER TABLE PLO_PLONGEE
ADD FOREIGN KEY FK_PLO_PLONGEE_PLO_SECURITE_DE_SURFACE (PER_NUM_SECU)
REFERENCES PLO_SECURITE_DE_SURFACE (PER_NUM) ;
ALTER TABLE PLO_PLONGEE
ADD FOREIGN KEY FK_PLO_PLONGEE_PLO_EMBARCATION (EMB_NUM)
REFERENCES PLO_EMBARCATION (EMB_NUM) ;
ALTER TABLE PLO_CONCERNER
ADD FOREIGN KEY FK_PLO_CONCERNER_PLO_PALANQUEE (PLO_DATE,PLO_MATIN_APRESMIDI,PAL_NUM)
REFERENCES PLO_PALANQUEE (PLO_DATE,PLO_MATIN_APRESMIDI,PAL_NUM) ;
ALTER TABLE PLO_CONCERNER
ADD FOREIGN KEY FK_PLO_CONCERNER_PLO_PLONGEUR (PER_NUM)
REFERENCES PLO_PLONGEUR (PER_NUM) ;
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('B', 'Bapt�me');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('PPB', 'pr�paration plongeur Bronze');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('PPA', 'pr�paration plongeur Argent');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('PN1', 'pr�paration plongeur Niveau1');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('PN2', 'pr�paration plongeur Niveau 2');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('PN3', 'pr�paration plongeur Niveau 3');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('PN4', 'pr�paration plongeur Niveau 4');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('PE-6', 'plongeur encadr� jusqu�� 6 m.');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('PE-12', 'plongeur encadr� jusqu�� 12 m.');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('PE-20', 'plongeur encadr� jusqu�� 20 m.');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('PE-40', 'plongeur encadr� jusqu�� 40 m.');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('PA-20', 'plongeur autonome jusqu�� 20 m.');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('PA-40', 'plongeur autonome jusqu�� 40 m.');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('PA-60', 'plongeur autonome jusqu�� 60 m.');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('E1', 'encadrant jusqu�� 6 m.');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('E2', 'encadrant jusqu�� 40 m.');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('E3', 'encadrant moniteur f�d�ral 1');
INSERT INTO `PLO_APTITUDE` (`APT_CODE`,`APT_LIBELLE`) VALUES ('E4', 'encadrant moniteur f�d�ral 2');
INSERT INTO `PLO_PERSONNE`(`PER_NOM`, `PER_PRENOM`) VALUES ("Audabram","Luc");
INSERT INTO `PLO_PERSONNE`(`PER_NOM`, `PER_PRENOM`) VALUES ("Rivat","Matthis");
INSERT INTO `PLO_PERSONNE`(`PER_NOM`, `PER_PRENOM`) VALUES ("Chavas","Nathan");
INSERT INTO `PLO_PERSONNE`(`PER_NOM`, `PER_PRENOM`) VALUES ("Simonin","Melissande");
INSERT INTO `PLO_PERSONNE`(`PER_NOM`, `PER_PRENOM`) VALUES ("Geumeunoix","Khaled");
INSERT INTO `plo_plongeur`(`PER_NUM`,`APT_CODE`) VALUES (3,"B");
INSERT INTO `plo_plongeur`(`PER_NUM`,`APT_CODE`) VALUES (4,"E4");
INSERT INTO `plo_plongeur`(`PER_NUM`,`APT_CODE`) VALUES (5,"PPB");
INSERT INTO `plo_securite_de_surface`(`PER_NUM`) VALUES (1);
INSERT INTO `plo_directeur`(`PER_NUM`) VALUES (2);
INSERT INTO `PLO_DIRECTEUR`(`PER_NUM`) VALUES (1);
INSERT INTO `PLO_SECURITE_DE_SURFACE`(`PER_NUM`) VALUES (2);