· 4 years ago · Dec 30, 2020, 10:16 AM
1-- ______ __ __
2-- /_ __/ ___ _ / / / / ___ ___
3-- / / / _ `/ / _ \ / / / -_) (_-<
4-- /_/ \_,_/ /_.__//_/ \__/ /___/
5--
6
7
8CREATE TABLE users
9(
10 user_id INTEGER NOT NULL,
11 first_name VARCHAR2(25) NOT NULL,
12 last_name VARCHAR2(25) NOT NULL,
13 gender CHAR(1) NOT NULL,
14 date_of_birth DATE NOT NULL,
15 date_of_death DATE,
16
17 CONSTRAINT users_pk PRIMARY KEY (user_id),
18 CONSTRAINT users_ck1 CHECK (date_of_death > date_of_birth),
19 CONSTRAINT users_ck2 CHECK (gender IN ('M', 'F'))
20);
21
22
23CREATE TABLE relationships
24(
25 relationship_id INTEGER NOT NULL,
26 type VARCHAR2(9) NOT NULL,
27
28 CONSTRAINT relationships_pk PRIMARY KEY (relationship_id),
29 CONSTRAINT relationships_ck CHECK (type IN ('family', 'work'))
30);
31
32
33CREATE TABLE membership
34(
35 relationship_id INTEGER NOT NULL,
36 user_id INTEGER NOT NULL,
37
38 CONSTRAINT membership_pk PRIMARY KEY (relationship_id, user_id)
39);
40
41
42CREATE TABLE contacts
43(
44 contact_id INTEGER NOT NULL,
45 location_id INTEGER NOT NULL,
46
47 CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
48);
49
50
51CREATE TABLE participants
52(
53 contact_id INTEGER NOT NULL,
54 user_id INTEGER NOT NULL,
55 date_received DATE NOT NULL,
56
57 CONSTRAINT participants_pk PRIMARY KEY (contact_id, user_id),
58 CONSTRAINT participants_uk UNIQUE (user_id, date_received)
59);
60
61
62CREATE TABLE locations
63(
64 location_id INTEGER NOT NULL,
65 name VARCHAR2(25) NOT NULL,
66 city VARCHAR2(25) NOT NULL,
67 category VARCHAR(14) NOT NULL,
68
69 CONSTRAINT locations_pk PRIMARY KEY (location_id),
70 CONSTRAINT locations_uk UNIQUE (name),
71 CONSTRAINT locations_ck CHECK (category IN
72 ('recreational', 'entertainment', 'religious', 'education', 'healthcare',
73 'transportation', 'business'))
74);
75
76/*
77 CATEGORIES / CONTEXT:
78 recreational: hotel, restaurants, bar, public meeting
79 entertainment: movie theatre, theatre, stadium, concert area, ski resort.
80 religious: churches, monasteries, sacred places
81 education: university, school, library
82 healthcare: hospital, private clinic
83 transportation: airport, train station, bus station, metro station
84 business: barber shop, private stores
85*/
86
87
88CREATE TABLE swabs
89(
90 swab_id INTEGER,
91 user_id INTEGER NOT NULL,
92 date_result DATE NOT NULL,
93 positivity CHAR(1) NOT NULL,
94
95 CONSTRAINT swabs_pk PRIMARY KEY (swab_id),
96 CONSTRAINT swabs_ck1 CHECK ( positivity IN ('y', 'n') ),
97 CONSTRAINT swabs_uk UNIQUE (user_id, date_result)
98);
99
100
101CREATE TABLE serological
102(
103 serological_id INTEGER,
104 user_id INTEGER NOT NULL,
105 date_result DATE NOT NULL,
106 igm CHAR(1) NOT NULL,
107 igg CHAR(1) NOT NULL,
108
109 CONSTRAINT serological_pk PRIMARY KEY (serological_id),
110 CONSTRAINT serological_uk UNIQUE (user_id, date_result),
111 CONSTRAINT serological_ck1 CHECK ( igm IN ('positive', 'negative') ),
112 CONSTRAINT serological_ck2 CHECK ( igg IN ('positive', 'negative') )
113);
114
115
116CREATE TABLE health_checks
117(
118 health_check_id INTEGER NOT NULL,
119 user_id INTEGER NOT NULL,
120 date_of_check DATE NOT NULL,
121 fever CHAR(1) NOT NULL,
122 respiratory_disorder CHAR(1) NOT NULL,
123 smell_taste_disorder CHAR(1) NOT NULL,
124
125 CONSTRAINT health_checks_ck1 CHECK (fever IN ('y', 'n')),
126 CONSTRAINT health_checks_ck2 CHECK (respiratory_disorder IN ('y', 'n')),
127 CONSTRAINT health_checks_ck3 CHECK (smell_taste_disorder IN ('y', 'n')),
128 CONSTRAINT health_checks_uk UNIQUE (user_id, date_of_check)
129);
130
131
132-- ___ __ __ ______ __ __
133-- / _ | / / / /_ ___ ____ /_ __/ ___ _ / / / / ___ ___
134-- / __ | / / / __// -_) / __/ / / / _ `/ / _ \ / / / -_) (_-<
135-- /_/ |_|/_/ \__/ \__/ /_/ /_/ \_,_/ /_.__//_/ \__/ /___/
136--
137
138
139ALTER TABLE membership
140 ADD
141 (
142 CONSTRAINT membership_relationships_fk FOREIGN KEY (relationship_id) REFERENCES relationships (relationship_id) ON DELETE CASCADE,
143 CONSTRAINT membership_users_fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
144 );
145
146ALTER TABLE contacts
147 ADD
148 (
149 CONSTRAINT contacts_locations_fk FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE
150 );
151
152ALTER TABLE participants
153 ADD
154 (
155 CONSTRAINT participants_contacts_fk FOREIGN KEY (contact_id) REFERENCES contacts (contact_id) ON DELETE CASCADE,
156 CONSTRAINT participants_users_fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
157 );
158
159ALTER TABLE swabs
160 ADD (
161 CONSTRAINT swabs_users_fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
162 );
163
164ALTER TABLE health_checks
165 ADD (
166 CONSTRAINT hc_users_fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
167 );
168
169ALTER TABLE serological
170 ADD (
171 CONSTRAINT serological_users_fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
172 );
173
174
175-- ____
176-- / __/ ___ ___ _ __ __ ___ ___ ____ ___ ___
177-- _\ \ / -_)/ _ `// // // -_) / _ \/ __// -_) (_-<
178-- /___/ \__/ \_, / \_,_/ \__/ /_//_/\__/ \__/ /___/
179-- /_/
180
181
182CREATE SEQUENCE users_seq;
183CREATE SEQUENCE relationships_seq;
184CREATE SEQUENCE contacts_seq;
185CREATE SEQUENCE locations_seq;
186CREATE SEQUENCE swabs_seq;
187CREATE SEQUENCE serological_seq;
188CREATE SEQUENCE health_check_seq;
189
190
191-- _ __ _
192-- | | / / (_) ___ _ __ ___
193-- | |/ / / / / -_)| |/|/ / (_-<
194-- |___/ /_/ \__/ |__,__/ /___/
195--
196
197-- all users
198CREATE OR REPLACE VIEW users_all_v AS
199SELECT first_name, last_name, gender, date_of_birth, date_of_death
200FROM users;
201
202
203-- all contacts between users (user_id1, user_id2, location_id, date_received) [used when inserting a new contact]
204CREATE OR REPLACE VIEW contacts_all_v AS
205SELECT p1.user_id AS user_id1,
206 p2.user_id AS user_id2,
207 c.location_id AS location_id,
208 p1.date_received AS date_received
209FROM contacts c
210 JOIN participants p1 ON c.contact_id = p1.contact_id
211 JOIN participants p2 ON p1.contact_id = p2.contact_id
212WHERE p1.user_id <> p2.user_id
213 AND p1.user_id < p2.user_id;
214
215
216-- all relationships (user_id1, user_id2, relationship type)
217CREATE OR REPLACE VIEW relationships_all_v AS
218SELECT m1.user_id AS user_id1, m2.user_id AS user_id2, r.type
219FROM relationships r
220 JOIN membership m1 ON r.relationship_id = m1.relationship_id
221 JOIN membership m2 ON m1.relationship_id = m2.relationship_id;
222
223
224-- ______ _
225-- /_ __/ ____ (_) ___ _ ___ _ ___ ____ ___
226-- / / / __/ / / / _ `/ / _ `// -_) / __/ (_-<
227-- /_/ /_/ /_/ \_, / \_, / \__/ /_/ /___/
228-- /___/ /___/
229
230
231CREATE OR REPLACE TRIGGER users_all_v_tr
232 INSTEAD OF INSERT
233 ON users_all_v
234 FOR EACH ROW
235DECLARE
236 l_users_pk INTEGER;
237BEGIN
238 l_users_pk := users_seq.nextval;
239 INSERT INTO users
240 VALUES (l_users_pk, :new.first_name, :new.last_name, :new.gender, :new.date_of_birth, :new.date_of_death);
241END;
242
243
244-- used when inserting a new contact between two users
245CREATE OR REPLACE TRIGGER contacts_all_v_tr
246 INSTEAD OF INSERT
247 ON contacts_all_v
248 FOR EACH ROW
249DECLARE
250 l_contact_pk INTEGER;
251BEGIN
252
253 FOR user_row IN (SELECT u.date_of_birth, u.date_of_death
254 FROM users u
255 WHERE u.user_id = :new.user_id1
256 OR u.user_id = :new.user_id2)
257 LOOP
258 -- check, for both users, if the date of contact is between the date_of_birth and date_of_death (excluding extremes)
259 IF (:new.date_received < user_row.date_of_birth) THEN
260 RAISE_APPLICATION_ERROR(-20001, 'date received < date of birth');
261 ELSIF (user_row.date_of_death IS NOT NULL) THEN
262 IF (:new.date_received > user_row.date_of_death) THEN
263 RAISE_APPLICATION_ERROR(-20001, 'date received > date of death');
264 END IF;
265 END IF;
266 END LOOP;
267
268 l_contact_pk := contacts_seq.nextval;
269 INSERT INTO contacts VALUES (l_contact_pk, :new.location_id);
270 INSERT INTO participants VALUES (l_contact_pk, :new.user_id1, :new.date_received);
271 INSERT INTO participants VALUES (l_contact_pk, :new.user_id2, :new.date_received);
272END;
273
274
275-- checks if user_id1 and user_id2 don't share already the same type of relationship
276CREATE OR REPLACE TRIGGER relationships_all_v_tr
277 INSTEAD OF INSERT
278 ON relationships_all_v
279 FOR EACH ROW
280DECLARE
281 l_relationship_pk INTEGER;
282 l_shared_relationship INTEGER;
283BEGIN
284 -- counts the number of relationships of the same type between user_id1 and user_id2
285 SELECT COUNT(*)
286 INTO l_shared_relationship
287 FROM relationships r
288 JOIN membership m1 ON r.relationship_id = m1.relationship_id
289 JOIN membership m2 ON m1.relationship_id = m2.relationship_id
290 WHERE m1.user_id = :new.user_id1
291 AND m2.user_id = :new.user_id2
292 AND r.type = :new.type;
293 IF (l_shared_relationship > 0) THEN
294 RAISE_APPLICATION_ERROR(-20001, 'a relationship of this type between these two users already exists');
295 ELSE
296 l_relationship_pk := relationships_seq.nextval;
297 INSERT INTO relationships VALUES (l_relationship_pk, :new.type);
298 INSERT INTO membership VALUES (l_relationship_pk, :new.user_id1);
299 INSERT INTO membership VALUES (l_relationship_pk, :new.user_id2);
300 END IF;
301END;
302
303
304CREATE OR REPLACE TRIGGER swabs_tr
305 BEFORE INSERT
306 ON swabs
307 FOR EACH ROW
308DECLARE
309 l_date_of_birth DATE;
310 l_date_of_death DATE;
311 l_swabs_pk INTEGER;
312BEGIN
313 SELECT date_of_birth, date_of_death
314 INTO l_date_of_birth, l_date_of_death
315 FROM users
316 WHERE user_id = :new.user_id;
317 IF (:new.date_result < l_date_of_birth) THEN
318 RAISE_APPLICATION_ERROR(-20001, 'date result < date of birth');
319 ELSIF (l_date_of_death IS NOT NULL) THEN
320 IF (:new.date_result < l_date_of_death) THEN
321 RAISE_APPLICATION_ERROR(-20001, 'date result > date of death');
322 END IF;
323 END IF;
324 l_swabs_pk := swabs_seq.nextval;
325 INSERT INTO swabs VALUES (l_swabs_pk, :new.user_id, :new.date_result, :new.positivity);
326END;
327
328
329CREATE OR REPLACE TRIGGER serological_tr
330 BEFORE INSERT
331 ON serological
332 FOR EACH ROW
333DECLARE
334 l_date_of_birth DATE;
335 l_date_of_death DATE;
336 l_serological_pk INTEGER;
337BEGIN
338 SELECT date_of_birth, date_of_death
339 INTO l_date_of_birth, l_date_of_death
340 FROM users
341 WHERE user_id = :new.user_id;
342 IF (:new.date_result < l_date_of_birth) THEN
343 RAISE_APPLICATION_ERROR(-20001, 'date result < date of birth');
344 ELSIF (l_date_of_death IS NOT NULL) THEN
345 IF (:new.date_result < l_date_of_death) THEN
346 RAISE_APPLICATION_ERROR(-20001, 'date result > date of death');
347 END IF;
348 END IF;
349 l_serological_pk := serological_seq.nextval;
350 INSERT INTO swabs VALUES (l_serological_pk, :new.user_id, :new.date_result, :new.igm, :new.igg);
351END;
352
353
354CREATE OR REPLACE TRIGGER health_checks_tr
355 BEFORE INSERT
356 ON health_checks
357 FOR EACH ROW
358DECLARE
359 l_date_of_birth DATE;
360 l_date_of_death DATE;
361 l_health_check_pk INTEGER;
362BEGIN
363 SELECT date_of_birth, date_of_death
364 INTO l_date_of_birth, l_date_of_death
365 FROM users
366 WHERE user_id = :new.user_id;
367 IF (:new.date_of_check < l_date_of_birth) THEN
368 RAISE_APPLICATION_ERROR(-20001, 'date of check < date of birth');
369 ELSIF (l_date_of_death IS NOT NULL) THEN
370 IF (:new.date_of_check < l_date_of_death) THEN
371 RAISE_APPLICATION_ERROR(-20001, 'date of check > date of death');
372 END IF;
373 END IF;
374 l_health_check_pk := serological_seq.nextval;
375 INSERT INTO health_checks
376 VALUES (l_health_check_pk, :new.user_id, :new.date_of_check, :new.fever, :new.respiratory_disorder,
377 :new.smell_taste_disorder);
378END;
379
380
381-- ____ __
382-- / _/ ___ ___ ___ ____ / /_ ___
383-- _/ / / _ \ (_-</ -_) / __// __/ (_-<
384-- /___/ /_//_//___/\__/ /_/ \__/ /___/
385--
386
387
388-- USERS
389INSERT INTO users (user_id, first_name, last_name, gender, date_of_birth, date_of_death)
390VALUES (0, 'Adriano', 'Peron', 'M', TO_DATE('14-05-2000', 'dd-mm-yyyy'), NULL); -- [1]
391INSERT INTO users
392VALUES (0, 'Massimo', 'Benerecetti', 'M', TO_DATE('15-05-1964', 'dd-mm-yyyy'), NULL); -- [2]
393INSERT INTO users
394VALUES (0, 'Piero', 'Bonatti', 'M', TO_DATE('20-07-1994', 'dd-mm-yyyy'), NULL); -- [3]
395INSERT INTO users
396VALUES (0, 'Alessandro', 'De Luca', 'F', TO_DATE('30-12-1975', 'dd-mm-yyyy'), NULL); -- [4]
397INSERT INTO users
398VALUES (0, 'Guido', 'Russo', 'M', TO_DATE('02-01-1980', 'dd-mm-yyyy'), NULL); -- [5]
399INSERT INTO users
400VALUES (0, 'Sergio', 'Di Martino', 'M', TO_DATE('19-09-1979', 'dd-mm-yyyy'), NULL); -- [6]
401INSERT INTO users
402VALUES (0, 'Guglielmo', 'Tamburrini', 'M', TO_DATE('06-12-1980', 'dd-mm-yyyy'), NULL); -- [7]
403INSERT INTO users
404VALUES (0, 'Daniel', 'Riccio', 'M', TO_DATE('29-11-1968', 'dd-mm-yyyy'), NULL); -- [8]
405INSERT INTO users
406VALUES (0, 'Alberto', 'Aloisio', 'M', TO_DATE('17-07-1992', 'dd-mm-yyyy'), NULL); -- [9]
407INSERT INTO users
408VALUES (0, 'Giovanni', 'Cutolo', 'M', TO_DATE('06-12-1957', 'dd-mm-yyyy'), NULL); -- [10]
409INSERT INTO users
410VALUES (0, 'Walter', 'Balzano', 'M', TO_DATE('22-10-2001', 'dd-mm-yyyy'), NULL); -- [11]
411INSERT INTO users
412VALUES (0, 'Aniello', 'Murano', 'M', TO_DATE('15-09-1952', 'dd-mm-yyyy'), NULL); -- [12]
413INSERT INTO users
414VALUES (0, 'Francesca', 'Cioffi', 'F', TO_DATE('13-06-1966', 'dd-mm-yyyy'), NULL); -- [13]
415INSERT INTO users
416VALUES (0, 'Paola', 'Festa', 'F', TO_DATE('27-10-1983', 'dd-mm-yyyy'), NULL); -- [14]
417INSERT INTO users
418VALUES (0, 'Francesco', 'Isgro', 'M', TO_DATE('07-08-1985', 'dd-mm-yyyy'), NULL); -- [15]
419INSERT INTO users
420VALUES (0, 'Roberto', 'Prevete', 'M', TO_DATE('20-05-1991', 'dd-mm-yyyy'), NULL); -- [16]
421INSERT INTO users
422VALUES (0, 'Luigia', 'Caputo', 'F', TO_DATE('02-09-1979', 'dd-mm-yyyy'), NULL); -- [17]
423INSERT INTO users
424VALUES (0, 'Francesco', 'Tramontano', 'M', TO_DATE('17-01-1969', 'dd-mm-yyyy'), NULL);
425-- [18]
426
427
428-- RELATIONSHIPS
429INSERT INTO relationships_all_v (user_id1, user_id2, type)
430VALUES (1, 2, 'work'); -- [1]
431INSERT INTO relationships_all_v (user_id1, user_id2, type)
432VALUES (4, 17, 'family'); -- [2]
433INSERT INTO relationships_all_v (user_id1, user_id2, type)
434VALUES (7, 18, 'work');
435-- [3]
436
437
438-- LOCATIONS
439INSERT INTO locations (location_id, name, city, category)
440VALUES (locations_seq.nextval, 'San Paolo', 'Napoli', 'entertainment'); -- [1]
441INSERT INTO locations
442VALUES (locations_seq.nextval, 'Monte S.Angelo', 'Napoli', 'education'); -- [2]
443INSERT INTO locations
444VALUES (locations_seq.nextval, 'Centro Direzionale', 'Napoli', 'business'); -- [3]
445INSERT INTO locations
446VALUES (locations_seq.nextval, 'Stazione Termini', 'Roma', 'transportation'); -- [4]
447INSERT INTO locations
448VALUES (locations_seq.nextval, 'Piazza Affari', 'Milano', 'business');
449-- [5]
450
451
452-- CONTACTS
453INSERT INTO contacts_all_v (user_id1, user_id2, location_id, date_received)
454VALUES (1, 5, 1, TO_DATE('01-11-2020 17:30:14', 'dd-mm-yyyy hh24:mi:ss')); -- [1]
455INSERT INTO contacts_all_v
456VALUES (6, 2, 2, TO_DATE('02-11-2020 09:30:14', 'dd-mm-yyyy hh24:mi:ss')); -- [2]
457INSERT INTO contacts_all_v
458VALUES (3, 7, 4, TO_DATE('03-11-2020 20:30:14', 'dd-mm-yyyy hh24:mi:ss')); -- [3]
459INSERT INTO contacts_all_v
460VALUES (12, 14, 3, TO_DATE('09-11-2020 12:27:00', 'dd-mm-yyyy hh24:mi:ss')); -- [4]
461INSERT INTO contacts_all_v
462VALUES (13, 15, 1, TO_DATE('11-11-2020 10:30:14', 'dd-mm-yyyy hh24:mi:ss')); -- [5]
463INSERT INTO contacts_all_v
464VALUES (10, 13, 3, TO_DATE('12-11-2020 14:30:14', 'dd-mm-yyyy hh24:mi:ss')); -- [6]
465INSERT INTO contacts_all_v
466VALUES (11, 8, 2, TO_DATE('13-11-2020 14:50:14', 'dd-mm-yyyy hh24:mi:ss')); -- [7]
467INSERT INTO contacts_all_v
468VALUES (4, 9, 4, TO_DATE('15-11-2020 08:50:14', 'dd-mm-yyyy hh24:mi:ss')); -- [8]
469INSERT INTO contacts_all_v
470VALUES (14, 2, 3, TO_DATE('18-11-2020 19:50:14', 'dd-mm-yyyy hh24:mi:ss')); -- [9]
471INSERT INTO contacts_all_v
472VALUES (5, 2, 5, TO_DATE('18-11-2020 11:21:14', 'dd-mm-yyyy hh24:mi:ss')); -- [10]
473INSERT INTO contacts_all_v
474VALUES (4, 11, 3, TO_DATE('23-11-2020 10:11:14', 'dd-mm-yyyy hh24:mi:ss')); -- [11]
475INSERT INTO contacts_all_v
476VALUES (12, 10, 3, TO_DATE('24-11-2020 17:01:33', 'dd-mm-yyyy hh24:mi:ss')); -- [12]
477INSERT INTO contacts_all_v
478VALUES (9, 8, 2, TO_DATE('25-11-2020 21:10:00', 'dd-mm-yyyy hh24:mi:ss')); -- [13]
479INSERT INTO contacts_all_v
480VALUES (7, 1, 4, TO_DATE('28-11-2020 20:13:27', 'dd-mm-yyyy hh24:mi:ss')); -- [14]
481INSERT INTO contacts_all_v
482VALUES (3, 6, 1, TO_DATE('29-11-2020 15:31:07', 'dd-mm-yyyy hh24:mi:ss')); -- [15]
483INSERT INTO contacts_all_v
484VALUES (15, 13, 1, TO_DATE('29-11-2020 12:22:58', 'dd-mm-yyyy hh24:mi:ss')); -- [16]
485INSERT INTO contacts_all_v
486VALUES (16, 5, 3, TO_DATE('30-11-2020 16:02:38', 'dd-mm-yyyy hh24:mi:ss'));
487-- [17]
488
489
490-- ____ __ _
491-- / __/ __ __ ___ ____ / /_ (_) ___ ___ ___
492-- / _/ / // / / _ \/ __// __/ / / / _ \ / _ \ (_-<
493-- /_/ \_,_/ /_//_/\__/ \__/ /_/ \___//_//_//___/
494--
495
496
497-- ___ __
498-- / _ \ ____ ___ ____ ___ ___/ / __ __ ____ ___ ___
499-- / ___/ / __// _ \/ __// -_)/ _ / / // / / __// -_) (_-<
500-- /_/ /_/ \___/\__/ \__/ \_,_/ \_,_/ /_/ \__/ /___/
501--
502
503
504-- ___
505-- / _ \ ____ ___ ___ ___
506-- / // / / __// _ \ / _ \ (_-<
507-- /____/ /_/ \___/ / .__//___/
508-- /_/
509
510
511DROP SEQUENCE users_seq;
512DROP SEQUENCE relationships_seq;
513DROP SEQUENCE locations_seq;
514DROP SEQUENCE contacts_seq;
515DROP SEQUENCE swabs_seq;
516DROP SEQUENCE serological_seq;
517DROP SEQUENCE health_check_seq;
518DROP TABLE membership CASCADE CONSTRAINTS;
519DROP TABLE relationships CASCADE CONSTRAINTS;
520DROP TABLE participants CASCADE CONSTRAINTS;
521DROP TABLE contacts CASCADE CONSTRAINTS;
522DROP TABLE locations CASCADE CONSTRAINTS;
523DROP TABLE swabs CASCADE CONSTRAINTS;
524DROP TABLE health_checks CASCADE CONSTRAINTS;
525DROP TABLE users CASCADE CONSTRAINTS;
526DROP TABLE serological CASCADE CONSTRAINTS;
527DROP VIEW users_all_v;
528DROP VIEW contacts_all_v;
529DROP VIEW relationships_all_v;
530
531
532
533-- _ __ ____ ___
534-- | | /| / / / _/ / _ \
535-- | |/ |/ / _/ / / ___/
536-- |__/|__/ /___/ /_/
537--
538
539
540select *
541from locations;
542
543
544DELETE
545FROM users
546WHERE user_id = 3;
547
548
549DELETE
550FROM contacts;
551
552
553SELECT *
554FROM users;
555
556
557SELECT *
558FROM contacts_all_v;
559
560
561-- tutti i contatti (coppia di user, luogo, data)
562SELECT p1.user_id, p2.user_id, c.location_id, p1.date_received
563FROM contacts c
564 JOIN participants p1 ON c.contact_id = p1.contact_id
565 JOIN participants p2 ON p1.contact_id = p2.contact_id
566WHERE p1.user_id <> p2.user_id;
567
568
569-- calcola il numero di contatti di uno user nelle ultime 2 settimane (da controllare)
570SELECT COUNT(i.contact_id)
571FROM contacts i
572 JOIN participants p ON i.contact_id = p.contact_id
573WHERE p.user_id = 1
574 AND p.date_received BETWEEN TO_DATE('01-12-2020', 'dd-mm-yyyy') - 30 AND TO_DATE('01-12-2020', 'dd-mm-yyyy')
575ORDER BY p.date_received DESC;
576