· 4 years ago · May 10, 2021, 05:28 PM
1drop table if exists Laboratorio cascade ;
2drop table if exists Struttura cascade ;
3drop table if exists CentroDiPrenotazione cascade ;
4drop table if exists Paziente cascade ;
5drop table if exists Squadra cascade ;
6drop table if exists Gruppo cascade ;
7drop table if exists Screening cascade ;
8drop table if exists Comunicazione cascade ;
9drop table if exists Analista cascade ;
10drop table if exists MedicoCurante cascade ;
11drop table if exists Prenotazione cascade ;
12drop table if exists OperatoreSanitario cascade ;
13drop table if exists Tampone cascade ;
14drop table if exists Analisi cascade ;
15drop table if exists Presso cascade ;
16drop table if exists Relativo cascade ;
17drop table if exists PartOf cascade ;
18drop table if exists TS cascade ;
19drop table if exists Composizione cascade ;
20drop table if exists TelefonoLaboratorio cascade ;
21drop table if exists TelefonoPaziente cascade ;
22
23
24CREATE TABLE Laboratorio
25(
26 LABID character(5) NOT NULL,
27 Nome character varying(50) NOT NULL,
28 CasiTotaliGiornalieri integer NOT NULL,
29 Comune character varying(30) NOT NULL,
30 CAP character(5) NOT NULL,
31 Via character varying(50) NOT NULL,
32 Civico character varying(30) NOT NULL,
33 PRIMARY KEY (LABID),
34 constraint checkCasi CHECK(CasiTotaliGiornalieri>=0)
35);
36
37
38
39CREATE TABLE Struttura
40(
41 STRID character(5) NOT NULL,
42 Nome character varying(50) NOT NULL,
43 NumeroTamponiMassimo integer NOT NULL,
44 Comune character varying(30) NOT NULL,
45 CAP character(5) NOT NULL,
46 Via character varying(50) NOT NULL,
47 Civico character varying(30) NOT NULL,
48 Tipologia character varying(30) NOT NULL,
49 PRIMARY KEY (STRID),
50 constraint checkNumeroTampMax CHECK(NumeroTamponiMassimo>0)
51);
52
53CREATE TABLE CentroDiPrenotazione
54(
55 CPID character(5) NOT NULL,
56 Nome character varying(50) NOT NULL,
57 Comune character varying(30) NOT NULL,
58 CAP character(5) NOT NULL,
59 Via character varying(50) NOT NULL,
60 Civico character varying(30) NOT NULL,
61 PRIMARY KEY (CPID)
62);
63
64CREATE TABLE Paziente
65(
66 CF character(16) NOT NULL,
67 Nome character varying(30) NOT NULL,
68 Cognome character varying(30) NOT NULL,
69 Comune character varying(30) NOT NULL,
70 CAP character(5) NOT NULL,
71 Via character varying(30) NOT NULL,
72 Civico character varying(30) NOT NULL,
73
74 DataDiNascita date NOT NULL,
75 PRIMARY KEY (CF)
76);
77
78CREATE TABLE OperatoreSanitario
79( OPID character(5) NOT NULL,
80 CF character(16) NOT NULL unique,
81 Nome character varying(30) NOT NULL,
82 Cognome character varying(30) NOT NULL,
83 Tipo character varying(30) NOT NULL,
84
85 PRIMARY KEY (OPID)
86);
87CREATE TABLE Tampone
88(
89 TID character(5) NOT NULL,
90 Tipologia character varying(30) NOT NULL,
91 Tramite character varying(30) NOT NULL,
92 Paziente character(16) references Paziente(CF) NOT NULL,
93 OperatoreSanitario character(5) references OperatoreSanitario(OPID)NOT NULL,
94 DataEsecuzione date NOT NULL,
95 Ora time NOT NULL,
96 PRIMARY KEY (TID,Tipologia),
97 constraint checkTramite check(Tramite='Prenotazione' or Tramite= 'Screening')
98);
99
100CREATE TABLE TelefonoPaziente
101(
102 NumeroDiTelefono character(10) NOT NULL,
103 Paziente character(16) references Paziente(CF) NOT NULL,
104 PRIMARY KEY (NumeroDiTelefono)
105);
106CREATE TABLE Squadra
107(
108 SQID character(5) NOT NULL,
109 PRIMARY KEY (SQID)
110);
111
112CREATE TABLE Gruppo
113(
114 GRID character(5) NOT NULL,
115 PRIMARY KEY (GRID)
116);
117
118CREATE TABLE Screening
119(
120 SCRID character(5) NOT NULL,
121 Tipo character varying(30) NOT NULL,
122 Comune character varying(30) NOT NULL,
123 CAP character(5) NOT NULL,
124 Via character varying(30) NOT NULL,
125 DataScreening date NOT NULL,
126 Target character varying(30),
127 AreaInteressata character varying(30),
128 Squadra character(5) references Squadra(SQID) NOT NULL,
129 Gruppo character(5) references Gruppo(GRID) NOT NULL,
130 PRIMARY KEY (SCRID)
131);
132
133CREATE TABLE Comunicazione
134(
135 COMID character(5) NOT NULL,
136 DataComunicazione date NOT NULL,
137 Tampone character(5) NOT NULL,
138 TipologiaTampone character varying(30) NOT NULL,
139
140 Laboratorio character(5) references Laboratorio(LABID) NOT NULL,
141 TelefonoPaziente character(10) references TelefonoPaziente(NumeroDiTelefono) NOT NULL,
142 PRIMARY KEY (COMID),
143 constraint comunicazioneKey foreign key (Tampone, TipologiaTampone) references Tampone(TID,Tipologia)
144
145);
146
147CREATE TABLE Analista
148(
149 AID character(5) NOT NULL,
150 CF character(16) NOT NULL,
151 Nome character varying(30) NOT NULL,
152 Cognome character varying(30) NOT NULL,
153
154 PRIMARY KEY (AID)
155);
156
157
158--CFMedicoCurate -> NomeMedicoCurante,CognomeMedicoCurante
159CREATE TABLE MedicoCurante(
160 CF character(16) NOT NULL,
161 Nome character varying(30) NOT NULL,
162 Cognome character varying(30) NOT NULL,
163 PRIMARY KEY (CF)
164);
165
166CREATE TABLE Prenotazione
167(
168 PID character(5) NOT NULL,
169 DataPrenotazione date NOT NULL,
170 ASL character varying(30),
171 Quarantena boolean,
172 CFMedicoCurante character(16) references MedicoCurante(CF),
173 Prezzo numeric(4, 2),
174 CentroDiPrenotazione character(5) references CentroDiPrenotazione(CPID) NOT NULL,
175 Paziente character(16) references Paziente(CF) NOT NULL,
176 PRIMARY KEY (PID, CentroDiPrenotazione)
177
178);
179
180
181
182
183
184
185
186
187CREATE TABLE Analisi
188( Tampone character(5) NOT NULL,
189 TipologiaTampone character varying(30) NOT NULL,
190 Laboratorio character(5) references Laboratorio(LABID) NOT NULL,
191 Analista character(5) references Analista(AID) NOT NULL,
192 Esito character (8) NOT NULL,
193 DataAnalisi date NOT NULL,
194 Ora time with time zone NOT NULL,
195 constraint checkEsito check (Esito='Positivo' or Esito='Negativo'),
196 constraint analisiKey foreign key (Tampone, TipologiaTampone) references Tampone(TID,Tipologia)
197
198);
199
200CREATE TABLE Presso
201(
202 Tampone character(5) ,
203 TipologiaTampone character varying(30) NOT NULL,
204 Struttura character(5) references Struttura(STRID) NOT NULL,
205 PRIMARY KEY (Tampone),
206 constraint tamponeKey foreign key (Tampone, TipologiaTampone) references Tampone(TID,Tipologia)
207);
208
209CREATE TABLE Relativo
210(
211 Tampone character(5) NOT NULL,
212 TipologiaTampone character varying(30) NOT NULL,
213 Prenotazione character(5) NOT NULL,
214 CentroDiPrenotazione character(5) NOT NULL,
215 PRIMARY KEY (Tampone),
216 constraint prenKey foreign key (Prenotazione,CentroDiPrenotazione) references Prenotazione(PID,CentroDiPrenotazione),
217 constraint tamponeKey foreign key (Tampone, TipologiaTampone) references Tampone(TID,Tipologia)
218);
219
220CREATE TABLE TS
221(
222 Tampone character(5) NOT NULL,
223 TipologiaTampone character varying(30) NOT NULL,
224 Screening character(5) references Screening(SCRID) NOT NULL,
225 constraint tamponeKey foreign key (Tampone, TipologiaTampone) references Tampone(TID,Tipologia)
226
227);
228
229
230CREATE TABLE PartOf
231(
232 Medico character(5) references OperatoreSanitario(OPID) NOT NULL,
233 Squadra character(5) references Squadra(SQID) NOT NULL
234
235
236);
237
238CREATE TABLE Composizione
239(
240 Paziente character(16) references Paziente(CF) NOT NULL,
241 Gruppo character(5) references Gruppo(GRID) NOT NULL
242);
243
244CREATE TABLE TelefonoLaboratorio
245(
246 NumeroDiTelefono character(10) NOT NULL,
247 Laboratorio character(5) references Laboratorio(LABID) NOT NULL,
248 PRIMARY KEY (NumeroDiTelefono)
249);
250
251
252
253Insert into Paziente (CF,Nome,Cognome,Comune,CAP,via,Civico,DataDiNascita) values('JVNHWM32A09D210O','Angelo','Cicalese','Torino','09854','Via Alcide de Gasperi','2','19500110'),
254('PLVTHM94R61L206M','Consiglia','Gambardella','Palermo','45454','Via Toppole','2','19800210'),
255('LGSTDI93M18E770R','Jacopo','D Ambrosio','Avellino','01234','Piazza de Martiri','1','19500110'),
256('CCHSHQ56R61L819E','Attilio','Caputo','Milano','54454','Via Alcide de Gasperi','3','19500110'),
257('SBEWDV68A22I801A','Salvatore','Cicalese','Palermo','09854','Via Ferrovia','7','19830302'),
258('MSVNWW93D52I184N','Martina','Gambardella','Torino','09435','Via Toppole','3','19500110'),
259('FVHGRN73C04I994D','Martina','Caputo','Napoli','44353','Piazza de Martiri','4','19781003'),
260('LGBBRB38E44F214Z','Caterina','Amato','Avellino','01234','Via Fontana','2','19800210'),
261('GDLGPC59P09B658P','Giovanni','Gambardella','Catania','33245','Via Puzzaco','2','19670101'),
262('GQVHSD94T64I765F','Attilio','De Cristofaro','Palermo','54454','Via Alcide de Gasperi','1','19670101'),
263('MCHJTZ60B49D495D','Angelo','Amato','Caserta','44353','Piazza de Martiri','2','19500110'),
264('NVZGGB63M61G469O','Marco','Cristoforo','Milano','45454','Via Ferrovia','3','19990212'),
265('VNJBZP84S42I210M','Daniele','De Cristofaro','Milano','01234','Piazza de Martiri','2','19800210'),
266('CJNLDG42E08L809S','Aurelio','Cicalese','Milano','45454','Piazza de Martiri','2','19990212'),
267('YCRCDY59L43F947V','Marco','D Amato','Milano','45454','Via Fontana','7','19830302'),
268('TJZVGP56P24A407A','Attilio','Cicalese','Roma','13455','Via Ferrovia','3','19670101'),
269('KGSNPW45R52F751N','Jacopo','D Ambrosio','Anzio','44353','Piazza de Martiri','3','19781003'),
270('SNTFFJ97E52F404G','Mario','D Amato','Milano','33245','Via Toppole','6','19800210'),
271('CNRBME58E49I633H','Attilio','Cicalese','Milano','45454','Via Ferrovia','3','19781003'),
272('HYBSCS52M70C347X','Marco','D Ambrosio','Milano','09854','Via Toppole','5','19990212');
273
274Insert into OperatoreSanitario(OPID,CF,Nome,Cognome,Tipo) values ('00001','JVNHWM32A09D210O','Gianluca','De Musis','Infermiere'),
275('00002','PLVTHM94R61L206M','Salvatore','Cicalese','Infermiere'),
276('00003','LGSTDI93M18E770R','Caterina','Cicalese','Medico'),
277('00004','CCHSHQ56R61L819E','Attilio','Gambardella','Paramedico'),
278('00005','SBEWDV68A22I801A','Caterina','Amato','Paramedico'),
279('00006','MSVNWW93D52I184N','Consiglia','Amato','Paramedico'),
280('00007','FVHGRN73C04I994D','Giovanni','De Musis','Medico');
281
282Insert into tampone(TID,Tipologia,Tramite,Paziente,OperatoreSanitario,DataEsecuzione,Ora) values
283('00011','Rapido','Prenotazione','JVNHWM32A09D210O','00002','20200304','10:20:20'),
284('01002','Rapido','Screening','PLVTHM94R61L206M','00006','20200101','12:33:03'),
285('34003','Rapido','Screening','LGSTDI93M18E770R','00002','20200130','12:04:06'),
286('04404','Molecolare','Prenotazione','CCHSHQ56R61L819E','00002','20200202','10:30:30'),
287('02305','Molecolare','Screening','SBEWDV68A22I801A','00004','20200506','12:33:03'),
288('02206','Seriologico','Prenotazione','MSVNWW93D52I184N','00004','20200101','13:04:53'),
289('12007','Molecolare','Screening','FVHGRN73C04I994D','00003','20200101','13:04:53'),
290('04308','Rapido','Screening','LGBBRB38E44F214Z','00001','20200506','12:32:02'),
291('04309','Seriologico','Screening','GDLGPC59P09B658P','00005','20200130','12:33:03'),
292('00012','Rapido','Screening','GQVHSD94T64I765F','00005','20200304','12:04:06'),
293('00011','Molecolare','Screening','MCHJTZ60B49D495D','00002','20200205','12:33:03'),
294('01122','Molecolare','Screening','NVZGGB63M61G469O','00005','20200205','12:33:03'),
295('32013','Rapido','Prenotazione','VNJBZP84S42I210M','00001','20200303','12:04:06'),
296('04314','Rapido','Prenotazione','CJNLDG42E08L809S','00002','20200304','13:04:53'),
297('02115','Seriologico','Screening','YCRCDY59L43F947V','00006','20200506','10:30:30'),
298('21016','Seriologico','Prenotazione','TJZVGP56P24A407A','00004','20200205','12:32:02'),
299('04137','Seriologico','Prenotazione','KGSNPW45R52F751N','00007','20200506','12:04:06'),
300('02118','Molecolare','Prenotazione','SNTFFJ97E52F404G','00001','20200304','10:30:30'),
301('01219','Seriologico','Screening','CNRBME58E49I633H','00001','20200304','12:04:06'),
302('00024','Rapido','Prenotazione','HYBSCS52M70C347X','00002','20200131','12:33:03');
303
304
305INSERT INTO Laboratorio(LABID, nome,casitotaligiornalieri, comune, CAP, via, civico) values
306 ('LB000', 'Centro Polid.Gammacord-Sannio Tac Srl', 0,'Bareggio', '20010', 'Via Santa Barbara', '25'),
307 ('LB001', 'Centro Diagnostico Morgagni', 0,'Gragnano', '80054', 'Piazza Giacomo Matteotti', '48'),
308 ('LB002', 'IGEA Piedimonte', 0,'Bagnolo di Po', '45022', 'Via Piella', '1'),
309 ('LB003', 'Medicina Nucleare E Analisi Cliniche', 0,'Nole', '10076', 'Via del Borgo di San Pietro', '8'),
310 ('LB004', 'Lab.Biomedical Srl', 0,'Picinisco', '03040', 'Via Guerrazzi', '40'),
311 ('LB005', 'Centro Gamma', 0,'Gorno', '24020', 'Via Leonida Bissolati', '60'),
312 ('LB006', 'Centro Futura', 0,'Brivio', '23883', 'Via Sant Arcangelo', '72'),
313 ('LB007', 'Centro Diagnostico Delta', 0,'Castelsardo', '07031', 'Galleria Camillo Benso di Cavour', '1'),
314 ('LB008', 'Centro Futura', 0,'Albese con Cassano', '22032', 'Via Ammiraglio Gregorio Ronca', '20'),
315 ('LB009', 'Diagnostica Medica Srl', 0,'Rionero in Vulture', '85028', 'Via Ostieri', '71'),
316 ('LB010', 'Laboratorio Biomedico Marcianise SRL', 0,'Ottana', '08020', 'Via Francesco Salamone', '52'),
317 ('LB011', 'Centro Futura', 0,'Gizzeria', '88040', 'Via Giovanni di Guglielmo', '33'),
318 ('LB070', 'Laboratorio Guarino', 0,'Coreno Ausonio', '03040', 'Via fra Scipione Bellabona', '2'),
319 ('LB013', 'Lab.Biomedical Srl', 0,'Rignano Flaminio', '00068', 'Via Sancio', '73'),
320 ('LB014', 'Centro Futura', 0,'Rasun Anterselva', '39030', 'Via Vittorio Alfieri', '5'),
321 ('LB015', 'Laboratorio Casa Di Cura San Michele', 0,'Aiello del Sabato', '83020', 'Arco Leonardo Miraglia', '33'),
322 ('LB016', 'Medicina Nucleare E Analisi Cliniche', 0,'Elva', '48020', 'Via Fornello', '4'),
323 ('LB017', 'Cerasole – Centro Polispecialistico SNC', 0,'Noasca', '10080', 'Via Giuseppe Garibaldi', '19'),
324 ('LB018', 'Lab.Biomedical Srl', 0,'Cornovecchio', '26842', 'Via Giuseppe Nappi', '57'),
325 ('LB019', 'Diagnostica Medica Srl', 0,'Castiglione Falletto', '36060', 'Via Mario Caputi', '71'),
326 ('LB020', 'Medicina Nucleare E Analisi Cliniche', 0,'Canistro', '67050', 'Via le Croci', '74'),
327 ('LB021', 'Centro Medico Polispecialistico Hermes', 0,'Pelugo', '38079', 'Via Fornello', '59'),
328 ('LB022', 'Lab.Biomedical Srl', 0,'Broni', '27043', 'Via le Croci', '77'),
329 ('LB023', 'Centro Diagnostico Morgagni', 0,'Gorlago', '24060', 'Via Giovanni Capocci da Roma', '44'),
330 ('LB024', 'Unilab Sannio', 0,'Peia', '24020', 'Via Luigi Zamboni', '72'),
331 ('LB025', 'Medicina Nucleare E Analisi Cliniche', 0,'Guarcino', '03016', 'Via Serafino Pionati', '36'),
332 ('LB026', 'Centro Medico Polispecialistico Hermes', 0,'Sadali', '08030', 'Via Guglielmo Oberdan', '64'),
333 ('LB027', 'Medicina Nucleare E Analisi Cliniche', 0,'Ozegna', '10080', 'Via Fratelli Bisogno', '12'),
334 ('LB028', 'Centro Polid.Gammacord-Sannio Tac Srl', 0,'Carmignano', '59015', 'Via del Pratello', '12'),
335 ('LB029', 'Centro Diagnostico Delta', 0,'Candiolo', '10060', 'Via Mario Minghetti', '12');
336
337select * from tampone where operatoresanitario='00006' or operatoresanitario='00007';
338select distinct OperatoreSanitario from Tampone T where not exists (select * from Tampone T1 where T1.operatoresanitario = T.operatoresanitario and Tipologia='Molecolare');
339
340select TID,Tipologia,Nome from Tampone,Paziente where Paziente.nome='Jacopo' and Tampone.Paziente=Paziente.cf;
341
342select * from laboratorio;
343
344
345drop function if exists azioni() cascade;
346create function azioni() returns trigger as $$
347 begin
348 if((select count(*) from (select * from Tampone where DataEsecuzione = '20210508' and Tramite='Prenotazione')
349 TamponiGiornalieri, Presso where Presso.Tampone=TamponiGiornalieri.TID and Presso.TipologiaTampone=TamponiGiornalieri.Tipologia and
350 Presso.struttura = NEW.struttura)>(select NumeroTamponiMassimo from Struttura where Struttura.STRID = NEW.Struttura)) then
351 raise Exception 'Limite numero tamponi massimo raggiunto!';
352 end if;
353 return NEW;
354end $$ language plpgsql;
355create trigger checkLimiteMassimoTamponi
356before insert on Presso
357for each row
358execute procedure azioni();
359
360
361
362drop function if exists azioniTampone() cascade;
363create function azioniTampone() returns trigger as $$
364 begin
365 if(not exists(select PID,CentroDiPrenotazione from Prenotazione where Prenotazione.paziente = NEW.paziente except select Prenotazione,CentroDiPrenotazione from Relativo)) then
366 raise Exception 'Non è presente nessuna prenotazione attiva per questo paziente';
367 end if;
368 insert into Relativo values (NEW.TID,NEW.Tipologia,( select PID from Prenotazione where DataPrenotazione = (select max(DataPrenotazione) from (select Prenotazione.PID,Prenotazione.DataPrenotazione from (select PID,CentroDiPrenotazione from Prenotazione where Prenotazione.paziente = NEW.paziente except select Prenotazione,CentroDiPrenotazione from Relativo)PrenotazioniAttivePerIlPaziente, Prenotazione where PrenotazioniAttivePerIlPaziente.PID = Prenotazione.PID and PrenotazioniAttivePerIlPaziente.CentroDiPrenotazione = Prenotazione.CentroDiPrenotazione) PrenotazioniPaziente) and NEW.paziente = Prenotazione.paziente
369
370
371
372
373),( select CentroDiPrenotazione from Prenotazione where DataPrenotazione = (select max(DataPrenotazione) from (select Prenotazione.PID,Prenotazione.DataPrenotazione from (select PID,CentroDiPrenotazione from Prenotazione where Prenotazione.paziente = NEW.paziente except select Prenotazione,CentroDiPrenotazione from Relativo)PrenotazioniAttivePerIlPaziente, Prenotazione where PrenotazioniAttivePerIlPaziente.PID = Prenotazione.PID and PrenotazioniAttivePerIlPaziente.CentroDiPrenotazione = Prenotazione.CentroDiPrenotazione) PrenotazioniPaziente) and NEW.paziente = Prenotazione.paziente
374
375)
376);
377
378 return NEW;
379end $$ language plpgsql;
380create trigger insertRelativo
381after insert on Tampone
382for each row
383when (NEW.Tramite = 'Prenotazione')
384execute procedure azioniTampone();
385
386insert into tampone values ('AACCQ','Rapido','Prenotazione','JVNHWM32A09D210O','00002','20210508','10:20:20');
387insert into tampone values ('AACCW','Rapido','Prenotazione','JVNHWM32A09D210O','00002','20210508','10:20:20');
388
389delete FROM prenotazione where PID='PR001';
390insert into prenotazione values ('PR001','20201023',null,null,null,null,null,null,'CP001','PLVTHM94R61L206M');
391insert into tampone values ('AAAAG','Rapido','Prenotazione','PLVTHM94R61L206M','00002','20210508','10:20:20');
392DELETE FROM TAMPONE where TID='AAAAG';
393insert into relativo values ('AAAAG','Rapido','PR001','CP001');
394
395
396
397select * from relativo;
398insert into CentroDiPrenotazione values ('CP001','Pree','Candida','83040','Via Alcide de Gasperi','8');
399
400 select PID from Prenotazione where DataPrenotazione = (select max(DataPrenotazione) from (select Prenotazione.PID,Prenotazione.DataPrenotazione from (select PID,CentroDiPrenotazione from Prenotazione where Prenotazione.paziente = NEW.paziente except select Prenotazione,CentroDiPrenotazione from Relativo)PrenotazioniAttivePerIlPaziente, Prenotazione where PrenotazioniAttivePerIlPaziente.PID = Prenotazione.PID and PrenotazioniAttivePerIlPaziente.CentroDiPrenotazione = Prenotazione.CentroDiPrenotazione) PrenotazioniPaziente) and NEW.paziente = Prenotazione.paziente;
401
402
403
404select CentroDiPrenotazione from (select Prenotazione.CentroDiPrenotazione,Prenotazione.DataPrenotazione from (select PID,CentroDiPrenotazione from Prenotazione where Prenotazione.paziente = NEW.paziente except select Prenotazione,CentroDiPrenotazione from Relativo)PrenotazioniAttivePerIlPaziente, Prenotazione where PrenotazioniAttivePerIlPaziente.PID = Prenotazione.PID and PrenotazioniAttivePerIlPaziente.CentroDiPrenotazione = Prenotazione.CentroDiPrenotazione) PrenotazioniPaziente where DataPrenotazione = max(DataPrenotazione);
405
406
407select * from paziente
408