4U2qMJAe

1drop table if exists history, connction_admin_marathon, connection_coordinator_runner,
2    connection_inventory_marathon, connection_location_marathon, connection_sponsor_runner,
3    connection_volunteer_marathon;
4drop table if exists marathon;
5drop table if exists admin, coordinator, location, runners, sponsors, inventory, volanteer;
6
7
8create table runners (
9    id serial primary key unique,
10    name text not null,
11    password text not null
12        constraint chk_password
13            check (length(password) >= 6 and length(password) <= 20),
14    age int
15        constraint chk_age
16            check (age >= 18 and age <= 45),
17    number text,
18    email text unique
19        constraint chk_email
20            check (email LIKE '%_@__%.__%'),
21    info text
22);
23
24insert into runners (id, name, password, age, number, email, info) values (1, 'Mélanie', 'AAw4rufdRHzZ', 29, '+52 311 533 9448', 'lpickwell0@360.cn', 'San Antonio');
25insert into runners (id, name, password, age, number, email, info) values (2, 'Stéphanie', 'VSA1RPnth', 22, '+27 180 643 6562', 'cpopham1@a8.net', 'Botshabelo');
26insert into runners (id, name, password, age, number, email, info) values (3, 'Océane', 'HWy4we', 39, '+595 479 120 6930', 'bbullant2@list-manage.com', 'Areguá');
27insert into runners (id, name, password, age, number, email, info) values (4, 'Aimée', 'qx0T8D418LSu', 26, '+63 217 599 4051', 'cburl3@angelfire.com', 'Basey');
28insert into runners (id, name, password, age, number, email, info) values (5, 'Célia', 'bDjdlrOyTKr', 28, '+31 217 899 3634', 'ascimoni4@oaic.gov.au', 'Amsterdam Nieuw West');
29insert into runners (id, name, password, age, number, email, info) values (6, 'Léane', '3rABsG', 25, '+62 912 432 0775', 'pbooton5@dmoz.org', 'Ngkiong');
30insert into runners (id, name, password, age, number, email, info) values (7, 'Angélique', 'epnfk1svNk', 29, '+351 873 699 8441', 'lbearcroft6@vinaora.com', 'Estrada');
31insert into runners (id, name, password, age, number, email, info) values (8, 'Tú', '2fr4ePY5Ayyj', 32, '+61 883 463 9615', 'jlangwade7@livejournal.com', 'Sydney');
32insert into runners (id, name, password, age, number, email, info) values (9, 'Kuí', 'XigZSlQz', 34, '+86 396 479 6649', 'mtillot8@flickr.com', 'Xialaxiu');
33insert into runners (id, name, password, age, number, email, info) values (10, 'Loïc', 'XFsvanin5b', 40, '+351 250 983 0679', 'askuce9@bing.com', 'Agrela');
34
35create table location(
36    id serial primary key unique,
37    name text not null
38);
39
40insert into location (id, name) values (1, '5004 Gulseth Point');
41insert into location (id, name) values (2, '08089 Merchant Avenue');
42insert into location (id, name) values (3, '318 Harbort Park');
43insert into location (id, name) values (4, '644 Charing Cross Avenue');
44insert into location (id, name) values (5, '611 Hermina Way');
45insert into location (id, name) values (6, '3 Loftsgordon Alley');
46insert into location (id, name) values (7, '74 Lindbergh Avenue');
47insert into location (id, name) values (8, '03137 Grasskamp Center');
48insert into location (id, name) values (9, '5383 Delaware Avenue');
49insert into location (id, name) values (10, '1389 Lakeland Junction');
50
51create table marathon (
52    id serial primary key unique,
53    length int,
54    info text not null,
55    start_point int references location(id),
56    end_point int references location(id),
57    date date
58);
59
60insert into marathon (id, length, info, start_point, end_point, date) values (1, 4448, 'Shanjiang', 4, 5, '12.10.2018');
61insert into marathon (id, length, info, start_point, end_point, date) values (2, 2137, 'Tuanzhou', 5, 10, '13.01.2018');
62insert into marathon (id, length, info, start_point, end_point, date) values (3, 5778, 'San Agustin', 4, 2, '10.01.2020');
63insert into marathon (id, length, info, start_point, end_point, date) values (4, 9132, 'Berlin', 10, 6, '13.01.2019');
64insert into marathon (id, length, info, start_point, end_point, date) values (5, 9895, 'Parigi', 7, 5, '22.02.2018');
65insert into marathon (id, length, info, start_point, end_point, date) values (6, 2387, 'Hāla', 8, 6, '20.09.2018');
66insert into marathon (id, length, info, start_point, end_point, date) values (7, 4156, 'Pingshan', 10, 10, '10.11.2018');
67insert into marathon (id, length, info, start_point, end_point, date) values (8, 5353, 'San Vicente de Bique', 2, 10, '23.11.2018');
68insert into marathon (id, length, info, start_point, end_point, date) values (9, 2091, 'Lianhe', 10, 6, '27.12.2019');
69insert into marathon (id, length, info, start_point, end_point, date) values (10, 9121, 'Quthing', 4, 9, '23.02.2019');
70
71create table coordinator(
72    id serial primary key unique,
73    name text not null,
74    password text not null
75        constraint chk_password
76            check (length(password) >= 6 and length(password) <= 20)
77);
78
79insert into coordinator (id, name, password) values (1, 'Stévina', '3f1U4Ql92h');
80insert into coordinator (id, name, password) values (2, 'Tú', '8i4Te2r5h8V');
81insert into coordinator (id, name, password) values (3, 'Réservés', 'P9szafxTP');
82insert into coordinator (id, name, password) values (4, 'Edmée', 'Z6vPbz');
83insert into coordinator (id, name, password) values (5, 'Mélys', 'y2kjBB');
84insert into coordinator (id, name, password) values (6, 'Personnalisée', 'f8L7EyLDrNMM');
85insert into coordinator (id, name, password) values (7, 'Mylène', 'UmNUDGTEkjwl');
86insert into coordinator (id, name, password) values (8, 'Intéressant', 'D8zJga');
87insert into coordinator (id, name, password) values (9, 'Maïté', 'Jf5OhrQ57n9');
88insert into coordinator (id, name, password) values (10, 'Tú', 'nJ073coco');
89
90create table volunteer(
91    id serial primary key unique,
92    name text not null,
93    email text unique
94        constraint chk_email
95            check (email LIKE '%_@__%.__%')
96);
97
98insert into volunteer (id, name, email) values (1, 'Åslög', 'jtamplin0@craigslist.org');
99insert into volunteer (id, name, email) values (2, 'Eléonore', 'hpatmore1@xing.com');
100insert into volunteer (id, name, email) values (3, 'Judicaël', 'jscurlock2@irs.gov');
101insert into volunteer (id, name, email) values (4, 'Salomé', 'sknowller3@paypal.com');
102insert into volunteer (id, name, email) values (5, 'Styrbjörn', 'tcaig4@phoca.cz');
103insert into volunteer (id, name, email) values (6, 'Pò', 'dstacy5@businessinsider.com');
104insert into volunteer (id, name, email) values (7, 'Åke', 'dsantostefano6@ocn.ne.jp');
105insert into volunteer (id, name, email) values (8, 'Andréanne', 'rguerrin7@craigslist.org');
106insert into volunteer (id, name, email) values (9, 'Mégane', 'sbaynom8@howstuffworks.com');
107insert into volunteer (id, name, email) values (10, 'Anaïs', 'nwyd9@newsvine.com');
108
109create table sponsors(
110    id serial primary key unique,
111    name text not null,
112    info text
113);
114
115insert into sponsors (id, name, info) values (1, 'Marie-françoise', 'Jaxworks');
116insert into sponsors (id, name, info) values (2, 'Dorothée', 'Brainsphere');
117insert into sponsors (id, name, info) values (3, 'Célestine', 'Eare');
118insert into sponsors (id, name, info) values (4, 'Zhì', 'Brightbean');
119insert into sponsors (id, name, info) values (5, 'Mà', 'Blogpad');
120insert into sponsors (id, name, info) values (6, 'Alizée', 'Twiyo');
121insert into sponsors (id, name, info) values (7, 'Daphnée', 'Brainlounge');
122insert into sponsors (id, name, info) values (8, 'Léonie', 'Geba');
123insert into sponsors (id, name, info) values (9, 'Mélina', 'Muxo');
124insert into sponsors (id, name, info) values (10, 'Naéva', 'Bubbletube');
125
126create table admin(
127    id serial primary key unique,
128    name text not null,
129    password text not null
130        constraint chk_password
131            check (length(password) >= 6 and length(password) <= 20)
132);
133
134insert into admin (id, name, password) values (1, 'Maëlle', 'h1s39Wy1Th');
135insert into admin (id, name, password) values (2, 'Angèle', 'c6p6usKOG7');
136insert into admin (id, name, password) values (3, 'Ruì', 'msLfmD');
137insert into admin (id, name, password) values (4, 'Léandre', '1lDSi0SX8plz');
138insert into admin (id, name, password) values (5, 'Liè', 'a18OVxG');
139insert into admin (id, name, password) values (6, 'Esbjörn', 'k67LLzFRa');
140insert into admin (id, name, password) values (7, 'Crééz', '0xO2upj9FWI');
141insert into admin (id, name, password) values (8, 'Yáo', 'Ai8kQt');
142insert into admin (id, name, password) values (9, 'Marylène', 'tmGy9id0');
143insert into admin (id, name, password) values (10, 'Mélys', 'kazWOVHog');
144
145create table inventory(
146    id serial primary key unique,
147    name text not null
148);
149
150insert into inventory (id, name) values (1, 'ball');
151insert into inventory (id, name) values (2, 'rope');
152insert into inventory (id, name) values (3, 'portable toilet');
153insert into inventory (id, name) values (4, 'cone');
154insert into inventory (id, name) values (5, 'flag');
155insert into inventory (id, name) values (6, 'first aid kit');
156insert into inventory (id, name) values (7, 'waterbottle');
157
158create table connection_inventory_marathon(
159    id serial primary key unique,
160    id_inventory int references inventory(id),
161    id_marathon int references marathon(id),
162    count int
163);
164
165insert into connection_inventory_marathon (id, id_inventory, id_marathon, count) values (1, 4, 10, 64);
166insert into connection_inventory_marathon (id, id_inventory, id_marathon, count) values (2, 3, 6, 75);
167insert into connection_inventory_marathon (id, id_inventory, id_marathon, count) values (3, 2, 4, 78);
168insert into connection_inventory_marathon (id, id_inventory, id_marathon, count) values (4, 5, 10, 28);
169insert into connection_inventory_marathon (id, id_inventory, id_marathon, count) values (5, 3, 9, 17);
170insert into connection_inventory_marathon (id, id_inventory, id_marathon, count) values (6, 7, 2, 10);
171insert into connection_inventory_marathon (id, id_inventory, id_marathon, count) values (7, 4, 7, 69);
172insert into connection_inventory_marathon (id, id_inventory, id_marathon, count) values (8, 6, 8, 26);
173insert into connection_inventory_marathon (id, id_inventory, id_marathon, count) values (9, 5, 9, 71);
174insert into connection_inventory_marathon (id, id_inventory, id_marathon, count) values (10, 1, 3, 93);
175
176create table connection_location_marathon(
177    id serial primary key unique,
178    id_location int references location(id),
179    id_marathon int references marathon(id)
180);
181
182insert into  connection_location_marathon (id, id_location, id_marathon) values (1, 1, 6);
183insert into  connection_location_marathon (id, id_location, id_marathon) values (2, 6, 10);
184insert into  connection_location_marathon (id, id_location, id_marathon) values (3, 6, 4);
185insert into  connection_location_marathon (id, id_location, id_marathon) values (4, 10, 2);
186insert into  connection_location_marathon (id, id_location, id_marathon) values (5, 5, 3);
187insert into  connection_location_marathon (id, id_location, id_marathon) values (6, 9, 5);
188insert into  connection_location_marathon (id, id_location, id_marathon) values (7, 2, 8);
189insert into  connection_location_marathon (id, id_location, id_marathon) values (8, 8, 6);
190insert into  connection_location_marathon (id, id_location, id_marathon) values (9, 7, 3);
191insert into  connection_location_marathon (id, id_location, id_marathon) values (10, 2, 6);
192
193create table connection_volunteer_marathon(
194    id serial primary key unique,
195    id_volunteer int references volunteer(id),
196    id_marathon int references marathon(id)
197);
198
199insert into  connection_volunteer_marathon (id, id_volunteer, id_marathon) values (1, 2, 4);
200insert into  connection_volunteer_marathon (id, id_volunteer, id_marathon) values (2, 4, 9);
201insert into  connection_volunteer_marathon (id, id_volunteer, id_marathon) values (3, 7, 2);
202insert into  connection_volunteer_marathon (id, id_volunteer, id_marathon) values (4, 8, 6);
203insert into  connection_volunteer_marathon (id, id_volunteer, id_marathon) values (5, 4, 9);
204insert into  connection_volunteer_marathon (id, id_volunteer, id_marathon) values (6, 3, 4);
205insert into  connection_volunteer_marathon (id, id_volunteer, id_marathon) values (7, 10, 10);
206insert into  connection_volunteer_marathon (id, id_volunteer, id_marathon) values (8, 10, 9);
207insert into  connection_volunteer_marathon (id, id_volunteer, id_marathon) values (9, 4, 3);
208insert into  connection_volunteer_marathon (id, id_volunteer, id_marathon) values (10, 8, 8);
209
210create table connction_admin_marathon(
211    id serial primary key unique,
212    id_admin int references admin(id),
213    id_marathon int references marathon(id)
214);
215
216insert into connction_admin_marathon (id, id_admin, id_marathon) values (1, 10, 8);
217insert into connction_admin_marathon (id, id_admin, id_marathon) values (2, 1, 9);
218insert into connction_admin_marathon (id, id_admin, id_marathon) values (3, 9, 6);
219insert into connction_admin_marathon (id, id_admin, id_marathon) values (4, 10, 5);
220insert into connction_admin_marathon (id, id_admin, id_marathon) values (5, 4, 10);
221insert into connction_admin_marathon (id, id_admin, id_marathon) values (6, 2, 8);
222insert into connction_admin_marathon (id, id_admin, id_marathon) values (7, 5, 8);
223insert into connction_admin_marathon (id, id_admin, id_marathon) values (8, 1, 1);
224insert into connction_admin_marathon (id, id_admin, id_marathon) values (9, 10, 7);
225insert into connction_admin_marathon (id, id_admin, id_marathon) values (10, 5, 3);
226
227create table connection_sponsor_runner(
228    id serial primary key unique,
229    id_runners int references runners(id),
230    id_sponsors int references sponsors(id)
231);
232
233insert into connection_sponsor_runner (id, id_runners, id_sponsors) values (1, 2, 9);
234insert into connection_sponsor_runner (id, id_runners, id_sponsors) values (2, 2, 9);
235insert into connection_sponsor_runner (id, id_runners, id_sponsors) values (3, 9, 7);
236insert into connection_sponsor_runner (id, id_runners, id_sponsors) values (4, 9, 5);
237insert into connection_sponsor_runner (id, id_runners, id_sponsors) values (5, 6, 2);
238insert into connection_sponsor_runner (id, id_runners, id_sponsors) values (6, 9, 1);
239insert into connection_sponsor_runner (id, id_runners, id_sponsors) values (7, 1, 9);
240insert into connection_sponsor_runner (id, id_runners, id_sponsors) values (8, 9, 10);
241insert into connection_sponsor_runner (id, id_runners, id_sponsors) values (9, 2, 7);
242insert into connection_sponsor_runner (id, id_runners, id_sponsors) values (10, 6, 3);
243
244create table connection_coordinator_runner(
245    id serial primary key unique,
246    id_runners int references runners(id),
247    id_coordinator int references coordinator(id)
248);
249
250insert into connection_coordinator_runner (id, id_runners, id_coordinator) values (1, 5, 10);
251insert into connection_coordinator_runner (id, id_runners, id_coordinator) values (2, 6, 4);
252insert into connection_coordinator_runner (id, id_runners, id_coordinator) values (3, 8, 10);
253insert into connection_coordinator_runner (id, id_runners, id_coordinator) values (4, 5, 3);
254insert into connection_coordinator_runner (id, id_runners, id_coordinator) values (5, 1, 3);
255insert into connection_coordinator_runner (id, id_runners, id_coordinator) values (6, 5, 10);
256insert into connection_coordinator_runner (id, id_runners, id_coordinator) values (7, 3, 4);
257insert into connection_coordinator_runner (id, id_runners, id_coordinator) values (8, 3, 1);
258insert into connection_coordinator_runner (id, id_runners, id_coordinator) values (9, 8, 10);
259insert into connection_coordinator_runner (id, id_runners, id_coordinator) values (10, 6, 5);
260
261create table history(
262    id serial primary key unique,
263    id_runners int references runners(id),
264    id_marathon int references marathon(id),
265    result text
266);
267
268insert into history (id, id_runners, id_marathon, result) values (1, 4, 10, '2:03:17 AM');
269insert into history (id, id_runners, id_marathon, result) values (2, 10, 7, '4:17:33 AM');
270insert into history (id, id_runners, id_marathon, result) values (3, 8, 5, '4:29:54 AM');
271insert into history (id, id_runners, id_marathon, result) values (4, 9, 3, '2:34:31 AM');
272insert into history (id, id_runners, id_marathon, result) values (5, 2, 9, '2:33:15 AM');
273insert into history (id, id_runners, id_marathon, result) values (6, 10, 3, '2:32:35 AM');
274insert into history (id, id_runners, id_marathon, result) values (7, 4, 7, '5:32:26 AM');
275insert into history (id, id_runners, id_marathon, result) values (8, 8, 1, '2:17:12 AM');
276insert into history (id, id_runners, id_marathon, result) values (9, 1, 2, '4:29:27 AM');
277insert into history (id, id_runners, id_marathon, result) values (10, 2, 3, '4:55:43 AM');