· 4 years ago · Jun 18, 2021, 11:30 AM
1--Création des tables--
2CREATE TABLE IF NOT EXISTS personne (
3 personne_id SERIAL,
4 personne_nom varchar(256) NOT NULL,
5 personne_prenom varchar(256) NOT NULL,
6 PRIMARY KEY (personne_id)
7);
8CREATE TABLE IF NOT EXISTS membresGroupe (
9 personne_id integer NOT NULL,
10 groupe_id integer NOT NULL,
11 UNIQUE (personne_id, groupe_id)
12);
13CREATE TABLE IF NOT EXISTS groupe (
14 groupe_id SERIAL,
15 nomDeScene varchar(256) NOT NULL,
16 PRIMARY KEY (groupe_id)
17);
18CREATE TABLE IF NOT EXISTS typeSalle (
19 typeSalle_id SERIAL,
20 typeSalle varchar(256) NOT NULL,
21 placesMax integer,
22 placesMin integer,
23 nbSecurite integer,
24 PRIMARY KEY (typeSalle_id)
25);
26CREATE TABLE IF NOT EXISTS agentSecurite (
27 personne_id integer NOT NULL,
28 concert_id integer NOT NULL,
29 groupe_id integer NOT NULL,
30 UNIQUE (personne_id, concert_id, groupe_id)
31);
32CREATE TABLE IF NOT EXISTS salle (
33 salle_id SERIAL,
34 salle_nom varchar(256),
35 typeSalle_id integer NOT NULL,
36 coutParConcert real,
37 nbPlaces integer,
38 PRIMARY KEY (salle_id)
39);
40CREATE TABLE IF NOT EXISTS concert (
41 concert_id SERIAL,
42 cachet real,
43 capaciteDemandee integer,
44 salle_id integer NOT NULL,
45 nbSecuritePermanent integer DEFAULT 0,
46 nbSecuriteAux integer DEFAULT 0,
47 date timestamp NOT NULL,
48 groupe_id integer NOT NULL,
49 PRIMARY KEY (concert_id)
50);
51CREATE TABLE IF NOT EXISTS billet (
52 billet_id SERIAL,
53 prixBillet real NOT NULL,
54 concert_id integer NOT NULL,
55 PRIMARY KEY (billet_id)
56);
57CREATE TABLE IF NOT EXISTS sponsor (
58 sponsor_id SERIAL,
59 sponsor_nom varchar(256) NOT NULL,
60 PRIMARY KEY (sponsor_id)
61);
62CREATE TABLE IF NOT EXISTS sponsorise (
63 sponsor_id integer NOT NULL,
64 concert_id integer NOT NULL,
65 UNIQUE (sponsor_id, concert_id)
66);
67CREATE TABLE IF NOT EXISTS tourneur (
68 tourneur_id SERIAL,
69 tourneur_nom varchar(256) NOT NULL,
70 groupe_id integer,
71 PRIMARY KEY (tourneur_id)
72);
73--Création des contraintes
74ALTER TABLE tourneur DROP CONSTRAINT IF EXISTS fk_groupe_id;
75ALTER TABLE tourneur
76ADD CONSTRAINT fk_groupe_id FOREIGN KEY (groupe_id) REFERENCES groupe(groupe_id);
77ALTER TABLE membresGroupe DROP CONSTRAINT IF EXISTS fk_personne_id;
78ALTER TABLE membresGroupe DROP CONSTRAINT IF EXISTS fk_groupe_id;
79ALTER TABLE membresGroupe
80ADD CONSTRAINT fk_personne_id FOREIGN KEY (personne_id) REFERENCES personne(personne_id),
81 ADD CONSTRAINT fk_groupe_id FOREIGN KEY (groupe_id) REFERENCES groupe(groupe_id);
82ALTER TABLE agentSecurite DROP CONSTRAINT IF EXISTS fk_personne_id;
83ALTER TABLE agentSecurite DROP CONSTRAINT IF EXISTS fk_groupe_id;
84ALTER TABLE agentSecurite DROP CONSTRAINT IF EXISTS fk_concert_id;
85ALTER TABLE agentSecurite
86ADD CONSTRAINT fk_personne_id FOREIGN KEY (personne_id) REFERENCES personne(personne_id),
87 ADD CONSTRAINT fk_groupe_id FOREIGN KEY (groupe_id) REFERENCES groupe(groupe_id),
88 ADD CONSTRAINT fk_concert_id FOREIGN KEY (concert_id) REFERENCES concert(concert_id);
89ALTER TABLE salle DROP CONSTRAINT IF EXISTS fk_typeSalle_id;
90ALTER TABLE salle
91ADD CONSTRAINT fk_typeSalle_id FOREIGN KEY (typeSalle_id) REFERENCES typeSalle(typeSalle_id);
92ALTER TABLE concert DROP CONSTRAINT IF EXISTS fk_salle_id;
93ALTER TABLE concert DROP CONSTRAINT IF EXISTS fk_groupe_id;
94ALTER TABLE concert
95ADD CONSTRAINT fk_salle_id FOREIGN KEY (salle_id) REFERENCES salle(salle_id),
96ADD CONSTRAINT fk_groupe_id FOREIGN KEY (groupe_id) REFERENCES groupe(groupe_id);
97
98ALTER TABLE billet DROP CONSTRAINT IF EXISTS fk_concert_id;
99ALTER TABLE billet
100ADD CONSTRAINT fk_concert_id FOREIGN KEY (concert_id) REFERENCES concert(concert_id);
101ALTER TABLE sponsorise DROP CONSTRAINT IF EXISTS fk_sponsor_id;
102ALTER TABLE sponsorise DROP CONSTRAINT IF EXISTS fk_concert_id;
103ALTER TABLE sponsorise
104ADD CONSTRAINT fk_sponsor_id FOREIGN KEY (sponsor_id) REFERENCES sponsor(sponsor_id),
105 ADD CONSTRAINT fk_concert_id FOREIGN KEY (concert_id) REFERENCES concert(concert_id);
106--Ajout des données
107INSERT INTO typeSalle
108VALUES (0, 'Stade', NULL, 20000, 50),
109 (1, 'Halle', 19999, 5000, 24),
110 (2, 'Cité des Congrès', 4999, 1, 12)
111 ON CONFLICT DO NOTHING;
112
113--Ajout de données bis
114---Salles disponibles
115INSERT INTO salle (
116 salle_nom,
117 typeSalle_id,
118 coutParConcert,
119 nbPlaces
120 )
121SELECT 'La Beaujoire' AS salle_nom,
122 typeSalle_id,
123 50000 AS coutParConcert,
124 50000 AS nbPlaces
125FROM typeSalle
126WHERE typeSalle ILIKE 'stade';
127
128INSERT INTO salle (
129 salle_nom,
130 typeSalle_id,
131 coutParConcert,
132 nbPlaces
133 )
134SELECT 'La Trocardière' AS salle_nom,
135 typeSalle_id,
136 12000 AS coutParConcert,
137 10000 AS nbPlaces
138FROM typeSalle
139WHERE typeSalle ILIKE 'halle';
140
141INSERT INTO salle (
142 salle_nom,
143 typeSalle_id,
144 coutParConcert,
145 nbPlaces
146 )
147SELECT 'Cité des Congrès de Nantes' AS salle_nom,
148 typeSalle_id,
149 10000 AS coutParConcert,
150 2000 AS nbPlaces
151FROM typeSalle
152WHERE typeSalle ILIKE 'Cité des Congrès';
153
154---Artistes
155INSERT INTO personne (
156 personne_nom,
157 personne_prenom
158 )
159VALUES ('Bruel', 'Patrick');
160
161INSERT INTO groupe (
162 nomDeScene
163)
164VALUES ('Patrick Bruel');
165
166INSERT INTO membresGroupe SELECT a.groupe_id, b.personne_id
167FROM
168(
169 SELECT groupe_id, row_number() over(order by groupe_id) rn FROM groupe WHERE nomDeScene ILIKE 'Patrick Bruel'
170) a
171LEFT JOIN
172(
173 SELECT personne_id, row_number() over(order by personne_id) rn FROM personne WHERE personne_nom ILIKE 'Bruel' AND personne_prenom ILIKE 'Patrick'
174) b
175 on a.rn = b.rn;
176
177INSERT INTO tourneur (tourneur_nom, groupe_id) SELECT '213 Productions' AS tourneur_nom, groupe_id FROM groupe WHERE nomDeScene ILIKE 'Patrick Bruel';
178
179INSERT INTO concert (cachet, salle_id, nbSecuritePermanent, date, groupe_id)
180SELECT 90000 AS cachet, a.salle_id, 50 AS nbSecuritePermanent, '2021-09-17' AS date, b.groupe_id
181FROM
182(
183 SELECT salle_id, row_number() over(order by salle_id) rn FROM salle WHERE salle_nom ILIKE 'La beaujoire'
184) a
185LEFT JOIN
186(
187 SELECT groupe_id, row_number() over(order by groupe_id) rn FROM groupe WHERE nomDeScene ILIKE 'Patrick Bruel'
188) b
189 on a.rn = b.rn;
190
191INSERT INTO concert (cachet, salle_id, nbSecuritePermanent, date, groupe_id)
192SELECT 90000 AS cachet, a.salle_id, 50 AS nbSecuritePermanent, '2021-09-19' AS date, b.groupe_id
193FROM
194(
195 SELECT salle_id, row_number() over(order by salle_id) rn FROM salle WHERE salle_nom ILIKE 'La beaujoire'
196) a
197LEFT JOIN
198(
199 SELECT groupe_id, row_number() over(order by groupe_id) rn FROM groupe WHERE nomDeScene ILIKE 'Patrick Bruel'
200) b
201 on a.rn = b.rn;
202
203INSERT INTO concert (cachet, salle_id, nbSecuritePermanent, date, groupe_id)
204SELECT 90000 AS cachet, a.salle_id, 50 AS nbSecuritePermanent, '2021-09-20' AS date, b.groupe_id
205FROM
206(
207 SELECT salle_id, row_number() over(order by salle_id) rn FROM salle WHERE salle_nom ILIKE 'La beaujoire'
208) a
209LEFT JOIN
210(
211 SELECT groupe_id, row_number() over(order by groupe_id) rn FROM groupe WHERE nomDeScene ILIKE 'Patrick Bruel'
212) b
213 on a.rn = b.rn;
214
215INSERT INTO concert (cachet, salle_id, nbSecuritePermanent, date, groupe_id)
216SELECT 90000 AS cachet, a.salle_id, 50 AS nbSecuritePermanent, '2021-09-26' AS date, b.groupe_id
217FROM
218(
219 SELECT salle_id, row_number() over(order by salle_id) rn FROM salle WHERE salle_nom ILIKE 'La beaujoire'
220) a
221LEFT JOIN
222(
223 SELECT groupe_id, row_number() over(order by groupe_id) rn FROM groupe WHERE nomDeScene ILIKE 'Patrick Bruel'
224) b
225 on a.rn = b.rn;
226
227
228---Aldebert
229INSERT INTO groupe (
230 nomDeScene
231)
232VALUES ('Aldebert');
233
234INSERT INTO tourneur (tourneur_nom, groupe_id) SELECT 'Jive/Epic' AS tourneur_nom, groupe_id FROM groupe WHERE nomDeScene ILIKE 'Aldebert';
235
236INSERT INTO concert (cachet, salle_id, nbSecuritePermanent, date, groupe_id)
237SELECT 20000 AS cachet, a.salle_id, 2 AS nbSecuritePermanent, '2021-12-20' AS date, b.groupe_id
238FROM
239(
240 SELECT salle_id, row_number() over(order by salle_id) rn FROM salle WHERE salle_nom ILIKE 'Cité des Congrès de Nantes'
241) a
242LEFT JOIN
243(
244 SELECT groupe_id, row_number() over(order by groupe_id) rn FROM groupe WHERE nomDeScene ILIKE 'Aldebert'
245) b
246 on a.rn = b.rn;
247
248---Reflets d’Acide
249INSERT INTO groupe (
250 nomDeScene
251)
252VALUES ('Reflets d’Acide');
253
254INSERT INTO tourneur (tourneur_nom, groupe_id) SELECT 'Reflets d’Acide' AS tourneur_nom, groupe_id FROM groupe WHERE nomDeScene ILIKE 'Reflets d’Acide';
255
256INSERT INTO concert (cachet, capaciteDemandee, salle_id, nbSecuritePermanent, date, groupe_id)
257SELECT 10000 AS cachet, 1230 as capaciteDemandee, a.salle_id, 0 AS nbSecuritePermanent, '2021-07-03' AS date, b.groupe_id
258FROM
259(
260 SELECT salle_id, row_number() over(order by salle_id) rn FROM salle WHERE salle_nom ILIKE 'La Trocardière'
261) a
262LEFT JOIN
263(
264 SELECT groupe_id, row_number() over(order by groupe_id) rn FROM groupe WHERE nomDeScene ILIKE 'Reflets d’Acide'
265) b
266 on a.rn = b.rn;
267
268
269INSERT INTO concert (cachet, capaciteDemandee, salle_id, nbSecuritePermanent, date, groupe_id)
270SELECT 10000 AS cachet, 1760 as capaciteDemandee, a.salle_id, 0 AS nbSecuritePermanent, '2021-07-05' AS date, b.groupe_id
271FROM
272(
273 SELECT salle_id, row_number() over(order by salle_id) rn FROM salle WHERE salle_nom ILIKE 'La Trocardière'
274) a
275LEFT JOIN
276(
277 SELECT groupe_id, row_number() over(order by groupe_id) rn FROM groupe WHERE nomDeScene ILIKE 'Reflets d’Acide'
278) b
279 on a.rn = b.rn;
280
281---Dire Traits
282INSERT INTO groupe (
283 nomDeScene
284)
285VALUES ('Dire Straits');
286
287INSERT INTO tourneur (tourneur_nom, groupe_id) SELECT 'Vertigo' AS tourneur_nom, groupe_id FROM groupe WHERE nomDeScene ILIKE 'Dire Straits';
288
289INSERT INTO concert (cachet, salle_id, nbSecuritePermanent, date, groupe_id)
290SELECT 100000 AS cachet, a.salle_id, 70 AS nbSecuritePermanent, '2021-09-26' AS date, b.groupe_id
291FROM
292(
293 SELECT salle_id, row_number() over(order by salle_id) rn FROM salle WHERE salle_nom ILIKE 'La beaujoire'
294) a
295LEFT JOIN
296(
297 SELECT groupe_id, row_number() over(order by groupe_id) rn FROM groupe WHERE nomDeScene ILIKE 'Dire Straits'
298) b
299 on a.rn = b.rn;
300
301--Verification contraintes d'intégrité
302INSERT INTO typeSalle
303VALUES (0, 'Place de spectacle', NULL, 5000, 10);
304
305--Modifs d'Aldebert
306--Mise à jour du premier concert
307UPDATE concert
308SET date = '2021-12-20 14:00:00'
309WHERE groupe_id IN (
310 SELECT groupe_id
311 FROM groupe WHERE nomDeScene ILIKE 'Aldebert'
312 ) AND
313 date = '2021-12-20'
314;
315
316--Ajout du deuxième concert
317INSERT INTO concert (cachet, salle_id, nbSecuritePermanent, date, groupe_id)
318SELECT 20000 AS cachet, a.salle_id, 2 AS nbSecuritePermanent, '2021-12-20 21:00:00' AS date, b.groupe_id
319FROM
320(
321 SELECT salle_id, row_number() over(order by salle_id) rn FROM salle WHERE salle_nom ILIKE 'Cité des Congrès de Nantes'
322) a
323LEFT JOIN
324(
325 SELECT groupe_id, row_number() over(order by groupe_id) rn FROM groupe WHERE nomDeScene ILIKE 'Aldebert'
326) b
327 on a.rn = b.rn;
328
329--Modifs Patrick Bruel
330--Concert n°1
331INSERT INTO concert (salle_id, date, groupe_id)
332SELECT a.salle_id, '2021-12-21' AS date, b.groupe_id
333FROM
334(
335 SELECT salle_id, row_number() over(order by salle_id) rn FROM salle WHERE salle_nom ILIKE 'La Trocardière'
336) a
337LEFT JOIN
338(
339 SELECT groupe_id, row_number() over(order by groupe_id) rn FROM groupe WHERE nomDeScene ILIKE 'Patrick Bruel'
340) b
341 on a.rn = b.rn;
342
343--Ajout de l'Onyx
344INSERT INTO salle (
345 salle_nom,
346 typeSalle_id,
347 nbPlaces
348 )
349SELECT 'L''Onyx' AS salle_nom,
350 typeSalle_id,
351 558 AS nbPlaces
352FROM typeSalle
353WHERE typeSalle ILIKE 'Cité des Congrès';
354
355--Concert n°2
356INSERT INTO concert (salle_id, date, groupe_id)
357SELECT a.salle_id, '2021-12-21' AS date, b.groupe_id
358FROM
359(
360 SELECT salle_id, row_number() over(order by salle_id) rn FROM salle WHERE salle_nom ILIKE 'L''Onyx'
361) a
362LEFT JOIN
363(
364 SELECT groupe_id, row_number() over(order by groupe_id) rn FROM groupe WHERE nomDeScene ILIKE 'Patrick Bruel'
365) b
366 on a.rn = b.rn;
367
368--rapport01.pdf
369SELECT nomDeScene, date, salle_nom FROM groupe JOIN concert ON concert.groupe_id=groupe.groupe_id JOIN salle on salle.salle_id=concert.salle_id WHERE EXTRACT(year FROM date)='2021';
370
371--rapport02.pdf
372SELECT salle_nom AS Salle, nbPlaces AS Capacité, coutParConcert AS Frais FROM salle;