BD Vaccini

Progettazione Concettuale

Requisiti Iniziali

Si vuole realizzare una base di dati per la gestione di una campagna vaccinale su scala nazionale in risposta alla recente epidemia di COVID19 che ha colpito il Paese.

Ogni cittadino è un potenziale vaccinando di cui sono noti nome, cognome, età, città ed indirizzo di residenza, codice fiscale. I cittadini sono ulteriormente suddivisibili secondo l’occupazione in personale sanitario e scolastico, categorie fragili (es, soggetti immunodepressi) e altri.

Sono disponibili tre tipi di vaccino: COVIDIN, CORONAX e FLUSTOP. Ogni vaccino ha un’età minima e massima di somministrazione e una certa efficacia. Ogni vaccino può richiedere una (FLUSTOP) o due (COVIDIN, CORONAX) somministrazioni. Nel caso siano necessarie due somministrazioni, l’intervallo temporale minimo tra prima e seconda dose varia da vaccino a vaccino. Ogni vaccino è organizzato in lotti con data di produzione e scadenza. Di ogni lotto si conoscono le reazioni allergiche eventualmente riscontrate nel Paese.

I centri vaccinali sono distribuiti nelle varie città del Paese a diversi indirizzi e possono esserci più centri per ogni città; ogni centro vaccinale dispone in ogni momento di zero o più fiale di uno o più vaccini ed a ogni centro afferiscono uno o più medici.

Ogni cittadino può fare richiesta di vaccinazione prenotando tramite un sito web o una app per smartphone (Figura 1), lasciando anche un recapito per essere ricontattati. Nel caso del sito web, é richiesto un indirizzo email al posto del numero di cellulare.

Una task force di operatori riceve le richieste di vaccinazione ed organizza un appuntamento vaccinale come segue. Ogni cittadino vaccinando può essere indirizzato esclusivamente ad un centro localizzato nella città di residenza. Personale sanitario e personale scolastico possono accedere ai vaccini COVIDIN e CORONAX. I soggetti fragili possono accedere solo a CORONAX e FLUSTOP. Gli altri soggetti non possono accedere a CORONAX.

Nel caso il cittadino abbia avuto precedenti reazioni allergiche, non può essere raccomandato nessun vaccino per cui almeno un lotto abbia registrato una segnalazione di reazione avversa negli ultimi 30 giorni. Il cittadino viene indirizzato al centro più vicino alla residenza dove vi sia disponibilità di almeno una fiala di vaccino compatibile con i vincoli sopra. Nel caso in cui il vaccinando abbia positività pregressa ad un test COVID, viene specificato che la seconda dose del vaccino, se prevista, non deve essere somministrata. La persona riceve una convocazione al contatto indicato contenente data, ora, centro di vaccinazione, tipo di vaccino da somministrare. Il cittadino vaccinando, munito di convocazione, si reca in data indicata al centro vaccinale indicato.

Ogni medico è identificato tramite i medesimi dati registrati per i cittadini, e l’afferenza ad un particolare centro vaccinale. I medici si suddividono in medici di base e altri, dove i primi possono somministrare solo vaccini a doppia dose, mentre i secondi sono abilitati anche alla somministrazione di vaccini a singola dose. Al momento dell’arrivo del vaccinando al centro, un medico fra quelli abilitati a somministrare il vaccino richiesto si prende in carico il vaccinando.

In seguito ad ogni vaccinazione, il medico tiene il vaccinato sotto osservazione per 15 minuti per monitorare eventuali effetti allergici relativamente al particolare lotto di vaccino utilizzato. Se non si verificano particolari effetti avversi e il vaccino richiede un richiamo ed il soggetto non ha pregressi di positività COVID, il medico aggiorna la convocazione con un secondo appuntamento per la somministrazione presso la stessa sede dello stesso vaccino in data compatibile con le specifiche del vaccino. Al contrario, se si verifica un effetto allergico, il medico emana un report che indica data e luogo della vaccinazione, tipo vaccino e numero lotto che hanno causato l’allergia al paziente in questione. Il sistema deve permettere inoltre di eseguire le seguenti operazioni:

• Ogni sera viene stilato un rapporto che indica quante vaccinazioni sono state fatte nella giornata in tutti i centri vaccinali suddivise per categoria di cittadino.
• Ogni sera, ogni centro vaccinale fa l’inventario del numero di dosi ancora disponibili per ogni vaccino.
• Ogni fine settimana, viene stilato un report che indica quante vaccinazioni sono state fatte per ogni vaccino per ognuna delle categorie di cittadini e quante di queste abbiano causato allergie

\(\pagebreak\)

Requisiti Rivisti

Si vuole realizzare una base di dati per la gestione di una campagna vaccinale su scala nazionale in risposta alla recente epidemia di COVID19 che ha colpito il Paese.

Per i cittadini rappresentiamo nome, cognome, età, città, indirizzo di residenza, codice fiscale, numero di cellulare, email. I cittadini possono essere personale sanitario, scolastico, categorie fragili (es. soggetti immunodepressi) e altri.

Per i vaccini rappresentiamo nome (COVIDIN, CORONAX e FLUSTOP), etá minima di somministrazione, etá massima di somministrazione, efficacia, numero di somministrazioni, intervallo tra dosi.

Per i lotti rappresentiamo il nome del vaccino, data di produzione, data di scadenza.

Per le reazioni allergiche rappresentiamo la reazione, il lotto, codice fiscale del cittadino.

Per i centri vaccinali rappresentiamo cittá, indirizzo. Possono esserci più centri per ogni città. Ogni centro vaccinale dispone in ogni momento di zero o più fiale di uno o più vaccini. Ad ogni centro vaccinale afferiscono uno o più medici.

Per i medici rappresentiamo nome, cognome, etá, cittá, indirizzo di residenza, codice fiscale, centro vaccinale. Ogni medico afferisce ad un particolare centro vaccinale. I medici si suddividono in medici di base e altri.

Ogni cittadino può fare richiesta di vaccinazione prenotando tramite un sito web o una app per smartphone (Figura 1), lasciando anche un recapito per essere ricontattati. Nel caso del sito web, é richiesto un indirizzo email al posto del numero di cellulare.

Una task force di operatori riceve le richieste di vaccinazione ed organizza un appuntamento vaccinale come segue. Ogni cittadino vaccinando può essere indirizzato esclusivamente ad un centro localizzato nella città di residenza. Personale sanitario e personale scolastico possono accedere ai vaccini COVIDIN e CORONAX. I soggetti fragili possono accedere solo a CORONAX e FLUSTOP. Gli altri soggetti non possono accedere a CORONAX.

Nel caso il cittadino abbia avuto precedenti reazioni allergiche, non può essere raccomandato nessun vaccino per cui almeno un lotto abbia registrato una segnalazione di reazione avversa negli ultimi 30 giorni. Il cittadino viene indirizzato al centro più vicino alla residenza dove vi sia disponibilità di almeno una fiala di vaccino compatibile con i vincoli sopra. Nel caso in cui il vaccinando abbia positività pregressa ad un test COVID, viene specificato che la seconda dose del vaccino, se prevista, non deve essere somministrata.

La persona riceve una convocazione al contatto indicato contenente data, ora, centro di vaccinazione, tipo di vaccino da somministrare. Il cittadino vaccinando, munito di convocazione, si reca in data indicata al centro vaccinale indicato. Al momento dell’arrivo del vaccinando al centro, un medico fra quelli abilitati a somministrare il vaccino richiesto si prende in carico il vaccinando. I medici di base possono somministrare solo vaccini a doppia dose, mentre gli altri sono abilitati anche alla somministrazione di vaccini a singola dose

\(\pagebreak\)

Glossario dei termini

\(\pagebreak\)

Schema ER Iniziale

Business Rules

• Un cittadino puó accedere solo a centri vaccinali della propria cittá di residenza
• Un cittadino per un dato vaccino deve al massimo avere confermato \(n\) appuntamenti con \(n\) uguale al Numero Somministrazioni del Vaccino
• Un cittadino per poter ricevere un vaccino deve avere Etá compresa tra le etá minima e massima di somministrazione
• Un cittadino con positivitá pregressa non deve ricevere seconda dose se specificata
• Il tipo di un cittadino deve corrispondere ad una categorizzazione: fragile, personale sanitario, personale scolastico, altro
• Un cittadino deve ricevere un dato vaccino in base alla propria categorizzazione:
  • fragile, CORONAX, FLUSTOP
  • personale sanitario, COVIDIN, CORONAX
  • personale scolastico, COVIDIN, CORONAX
  • nessuno dei precedenti, CORONAX
• Un cittadino che ha riscontrato reazioni avverse non puó accedere a dosi il cui lotto ha riscontrato almeno una reazione avversa negli ultimi 30 giorni
• Medici di base somministrano solo vaccini a doppia dose
• Il numero di fiale disponibili per ogni lotto di cui dispone un centro vaccinale va tenuto aggiornato in base alle vaccinazione fatte
• intervallo dosi di un vaccino deve essere NOT-NULL in casi di piú dosi
• le fiale disponibili per una vaccinazioni devono essere associate ad un lotto non scaduto alla data odierna

\(\pagebreak\)

Progettazione Logica

Tavola dei Volumi

Giustificazioni per i volumi

• Approssimazione Cittadini in Italia
  • Sottoinsiemi dei Cittadini stimati in base al numero di Cittadini e Medici
• Approssimazione Medici in Italia
• Numero Vaccini dai requisiti
• Approssimazione Lotti in assenza di dati
• Centri vaccinali in base ai comuni italiani
• Reazioni allergiche in base ai dati
• Vaccinazioni supponendo base dati nel corso della campagna vaccinale
• Convocazioni supponendole maggiori delle Vaccinazioni
• Appuntamenti, in base al numero di Convocazioni
• Incarichi in base al numero di Medici
• Reazioni in base al numero di Reazioni allergiche
• Locazioni in base al numero di Vaccinazioni
• Amministrazioni in base al numero di Vaccinazioni
• Afferisce, in base al numero di Medici
• Disponibile, supponendo almeno 3 lotti diversi per ogni Centro Vaccinale
• Prodotto, in base al numero di lotti

Tavola delle Operazioni

\(\pagebreak\)

Analisi delle Ridondanze

• Ridondanza: reazioni allergiche verso un lotto come attributo dello stesso
  • impatta le operazioni 3, 9

Operazione 3

Con Ridondanza:

Lo schema di visita é: Vaccinazione - Perfezionata - Convocazione - Appuntamento

• Cittadino - Fragile - Personale Scolastico - Personale Sanitario - Altri
• Vaccino - Prodotto - Lotto

La tavola degli accessi é:

Dove:

• Fragili sono l'8.3% dei Cittadini
• Personale Sanitario sono l'1.3% dei Cittadini
• Personale Scolastico sono l'1.6% dei Cittadini
• Altri sono l'88.8% dei Cittadini

Considerando le stime indicate nella Tavola dei Volumi.

Accessi Totali: \(120\:004\:003\) in Lettura

Senza Ridondanza:

Lo schema di visita é: Vaccinazione - Perfezionata - Convocazione - Appuntamento

• Cittadino - Fragile - Personale Scolastico - Personale Sanitario - Altri
• Report - Reazione Allergica - Tipologia Vaccino - Vaccino

La tavola degli accessi é:

Dove valgono le considerazioni precedenti e:

• Report sono \[\frac{1000}{20000000} = 0.00005\%\] delle Vaccinazioni

Accessi Totali: \(160\:000\:640\) in Lettura

\(\pagebreak\)

Operazione 9

Con Ridondanza

Lo schema di visita é: Reazione Allergica - Report - Vaccinazione - Amministrato - Lotto

Accessi Totali: 8 (Supponendo gli accessi in scrittura equivalenti a 2 accessi in lettura)

Senza Ridondanza

Lo schema di visita é: Reazione Allergica - Report

Accessi Totali: 4 \(\pagebreak\)

Analisi

Presenza di Ridondanza:

• Spazio: \[4 \text{ byte} \times 2000 = 8000 \text{ byte}\]
• Tempo:
  • Operazione 3 - \[120004003 \text{ accessi} \times 1 \text{ volta a settimana}\]
  • Operazione 9 - \[8 \text{ accessi} \times 3 \text{ volte a settimana}\]
  • Totale - \[120004027 \text{ accessi a settimana}\]

Assenza di Ridondanza:

• Spazio: \[0 \text{ byte} \times 2000 = 0 \text{ byte}\]
• Tempo:
  • Operazione 3 - \[160000640 \text{ accessi} \times 1 \text{ volta a settimana}\]
  • Operazione 9 - \[4 \text{ accessi} \times 3 \text{ volte a settimana}\]
  • Totale - \[160000652 \text{ accessi a settimana}\]

Scegliamo di tenere la ridondanza a fronte del risparmio di quasi 40 milioni di accessi con l’utilizzo di solo 8000 byte. \(\pagebreak\)

Schema ER Ristrutturato

Business Rules

• Un cittadino puó accedere solo a centri vaccinali della propria cittá di residenza
• Un cittadino per un dato vaccino deve al massimo avere confermato \(n\) appuntamenti con \(n\) uguale al Numero Somministrazioni del Vaccino
• Un cittadino per poter ricevere un vaccino deve avere Etá compresa tra le etá minima e massima di somministrazione
• Un cittadino con positivitá pregressa non deve ricevere seconda dose se specificata
• Il tipo di un cittadino deve corrispondere ad una categorizzazione: fragile, personale sanitario, personale scolastico, altro
• Un cittadino deve ricevere un dato vaccino in base alla propria categorizzazione:
  • fragile, CORONAX, FLUSTOP
  • personale sanitario, COVIDIN, CORONAX
  • personale scolastico, COVIDIN, CORONAX
  • nessuno dei precedenti, CORONAX
• Un cittadino che ha riscontrato reazioni avverse non puó accedere a dosi il cui lotto ha riscontrato almeno una reazione avversa negli ultimi 30 giorni
• Medici di base somministrano solo vaccini a doppia dose
• Il numero di fiale disponibili per ogni lotto di cui dispone un centro vaccinale va tenuto aggiornato in base alle vaccinazione fatte
• intervallo dosi di un vaccino deve essere NOT-NULL in casi di piú dosi
• le fiale disponibili per una vaccinazioni devono essere associate ad un lotto non scaduto alla data odierna

L’attributo multivalore Allergia é stato convertito nella relazione molti a molti Ha e nell’entitá Allergia La generalizzazione totale/esclusiva di Cittadino é stata convertita nel attributo Tipo dello stesso \(\pagebreak\)

Schema Relazionale

Vaccino( Nome, EtáMinima, EtáMassima, IntervalloDosi*, Efficacia, NumSomministrazioni )

Lotto( ID, NomeVaccino, NumReazioniAllergiche, DataProduzione, Scadenza )

CentroVaccinale( Cittá, Indirizzo )

Disponibile( Cittá, Indirizzo, Lotto, NumFiale )

Convocazione( Data, CodiceFiscale, Cittá, Indirizzo, Ora )

Cittadino( CodiceFiscale, Etá, Nome, Cognome, Cittá, Indirizzo, EMail*,PositivitáPregressa, Cellulare*, Tipo )

Allergia( Nome )

Ha( CodiceFiscale, _Allergia _)

Vaccinazione( CodiceFiscale, Data, CodiceFiscaleMedico, ID )

Medico( CodiceFiscale, Cittá, Indirizzo, Nome, Cognome, Di Base )

ReazioneAllergica( ID ,Tipologia, Osservazioni, CodiceFiscale, Data )

\(\\)

Lotto(NomeVaccino) referenzia Vaccino(Nome)

Disponibile(Cittá, Indirizzo) referenzia CentroVaccinale(Cittá, Indirizzo)

Disponibile(Lotto) referenzia Lotto(ID)

Convocazione(CodiceFiscale) referenzia Cittadino(CodiceFiscale)

Convocazione(Cittá, Indirizzo) referenzia CentroVaccinale(Cittá, Indirizzo)

Ha(Codice Fiscale) referenzia Cittadino(CodiceFiscale)

Ha(Allergia) referenzia Allergia(Nome)

Medico(Cittá, Indirizzo) referenzia CentroVaccinale(Cittá, Indirizzo)

Vaccinazione(Codice Fiscale, Data) referenzia Convocazione(CodiceFiscale, Data)

ReazioneAllergica(CodiceFiscale, Data) referenzia Vaccinazione(CodiceFiscale, Data)

\(\pagebreak\)

Implementazione

DDL

DROP SCHEMA "BDvaccini" cascade;

CREATE SCHEMA "BDvaccini" AUTHORIZATION dbdanielbiasiotto;

CREATE TABLE Vaccino(
         Nome varchar(20) primary key,
         EtáMinima smallint default 0 not null,
         EtáMassima smallint default 100 not null,
         IntervalloDosi smallint not null,
         Efficacia float not null,
         NumSomministrazioni smallint not null
       );
CREATE TABLE Lotto(
         ID SERIAL primary key,
         NomeVaccino varchar(20),
         NumReazioniAllergiche smallint default 0 not null,
         DataProduzione date not null,
         Scadenza date not null,
         constraint fk_lotto
            foreign key(NomeVaccino)
                references Vaccino(Nome)
                    on update cascade
                    on delete cascade
       );
CREATE TABLE CentroVaccinale(
         Cittá varchar(30),
         Indirizzo varchar(50),
         primary key(Cittá, Indirizzo)
       );
CREATE TABLE Medico(
         CodiceFiscale char(16) primary key,
         Cittá varchar(30),
         Indirizzo varchar(50),
         Nome varchar(20) not null,
         Cognome varchar(20) not null,
         DiBase boolean default true not null,
         constraint fk_medico
            foreign key(Cittá, Indirizzo)
                references CentroVaccinale(Cittá, Indirizzo)
                    on update cascade
                    on delete cascade
       );
CREATE TABLE Disponibile(
         Cittá varchar(30),
         Indirizzo varchar(50),
         Lotto smallint,
         NumFiale smallint not null,
         primary key(Cittá, Indirizzo, Lotto),
         constraint fk_disponibile
             foreign key(Cittá, Indirizzo)
                 references CentroVaccinale(Cittá, Indirizzo),
             foreign key(Lotto)
                 references Lotto(ID)
                     on update cascade
                     on delete cascade
       );
CREATE TABLE Cittadino(
         CodiceFiscale char(16) primary key,
         Etá smallint not null,
         Nome varchar(20) not null,
         Cognome varchar(20) not null,
         Cittá varchar(20) not null,
         Indirizzo varchar(20) not null,
         Email varchar(30),
         Cellulare varchar(10),
         Tipo varchar(20) default 'altro' not null,
         PositivitáPregressa boolean default false not null,
         unique(Nome, Cognome, Cittá)
       );
CREATE TABLE Convocazione(
         Data date,
         CodiceFiscale char(16),
         Cittá varchar(30),
         Indirizzo varchar(50),
         Ora time not null,
         primary key(Data, CodiceFiscale),
         constraint fk_convocazione
             foreign key(CodiceFiscale)
                 references Cittadino(CodiceFiscale)
                     on update cascade
                     on delete cascade,
             foreign key(Cittá, Indirizzo)
                 references CentroVaccinale(Cittá, Indirizzo)
                     on update cascade
                     on delete cascade
       );

CREATE TABLE Allergia(
         Nome varchar(30) primary key
       );
CREATE TABLE Ha(
         CodiceFiscale char(16),
         Allergia varchar(30),
         primary key(CodiceFiscale, Allergia),
         constraint fk_ha
             foreign key(CodiceFiscale)
                 references Cittadino(CodiceFiscale)
                     on update cascade
                     on delete cascade,
         foreign key(Allergia)
             references Allergia(Nome)
                 on update cascade
                 on delete cascade
       );
CREATE TABLE Vaccinazione(
         Data date,
         CodiceFiscale char(16),
         CodiceFiscaleMedico char(16),
         ID smallint,
         primary key(CodiceFiscale,Data),
         constraint fk_vaccinazione
             foreign key(CodiceFiscale, Data)
                 references Convocazione(CodiceFiscale, Data)
                     on update cascade
                     on delete cascade,
             foreign key(CodiceFiscaleMedico)
                 references Medico(CodiceFiscale)
                     on update cascade
                     on delete cascade,
             foreign key(ID)
                 references Lotto(ID)
                     on update cascade
                     on delete cascade
       );
CREATE TABLE ReazioneAllergica(
         ID smallint,
         Tipologia varchar(30) not null,
         Osservazioni varchar(500) not null,
         CodiceFiscale char(16),
         Data date,
         primary key(ID),
         constraint fk_reazione
             foreign key(ID)
                 references Lotto(ID)
                     on update cascade
                     on delete cascade,
              foreign key(CodiceFiscale, Data)
                  references Vaccinazione(CodiceFiscale, Data)
                      on update cascade
                      on delete cascade
       );

\(\pagebreak\)

DML

insert into "BDvaccini".vaccino values
	('Coronax', 16, 80, 6, 0.9, 2),
	('Covidin', 13, 70, null, 0.88, 1),
	('Flustop', 20, 60, 4, 0.92, 2);

insert into "BDvaccini".cittadino values
	('BSTED23DGC23DT3C',40,'Giuseppe','Pizza',
    'Milano','Via P 17','etndduen@uen.it',387438457,
    'fragile',false),
	('CSTEEU3DGC256H3C',40,'Silvia','Enne',
    'Milano','Via Cile 1','yi@uen.it',374691923,
    'personale sanitario',false),
	('HIERR9GC287E9E8T',40,'Giusi','Emme','Piolo',
    'Via Guille 3','tieun@uen.en',387438457,
    'altro',true),
	('EUT923DGC287IET8',40,'Tito','Pi',
    'Padova','Via Maria 14b','ueioe@edu.uk',983746192,
    'personale medico',false);

insert into "BDvaccini".centrovaccinale values
	('Milano','Via Beccaria 3'),
	('Pisa','Via Venezia 12c'),
   ('Napoli','Via Tito 42'),
   ('Alessandria','Via Zito 15'),
	('Torino','Via Umberto 50');

insert into "BDvaccini".lotto values
	(nextval('"BDvaccini".lotto_id_seq'::regclass),
    'Coronax', 0, '12/09/2020', '12/09/2022'),
	(nextval('"BDvaccini".lotto_id_seq'::regclass),
    'Coronax', 0, '8/02/2020', '10/02/2023'),
	(nextval('"BDvaccini".lotto_id_seq'::regclass),
    'Covidin', 0, '1/10/2020', '12/10/2021'),
	(nextval('"BDvaccini".lotto_id_seq'::regclass),
    'Modernum', 0, '12/20/2019', '12/20/2022');

insert into "BDvaccini".disponibile values
	('Milano','Via Beccaria 3',2,20),
	('Milano','Via Beccaria 3',4,200),
	('Pisa','Via Venezia 12c',2,140),
	('Torino','Via Umberto 50',3,10);

insert into "BDvaccini".allergia values
	('Acari')
	('Muffe')
	('Vespe')
	('Gatti')
	('Graminacee')
	('Albicocche');

insert into "BDvaccini".ha values
	('EUT923DGC287IET8','Graminacee'),
	('BSTED23DGC23DT3C','Albicocche');

insert into "BDvaccini".medico values
	('BEGIED23DGC23DT3', 'Torino', 'Via Umberto 50',
    'Harry', 'Oliver', false),
	('GITIED23DGC23DT3', 'Pisa', 'Via Venezia 12c',
    'Carla', 'Ronda', true);

insert into "BDvaccini".convocazione values
	('02/13/2020','EUT923DGC287IET8','Torino','Via Umberto 50','12:00:00'),
	('04/23/2020','BSTED23DGC23DT3C','Pisa','Via Venezia 12c','16:30:00');

insert into "BDvaccini".vaccinazione values
	('02/13/2020','EUT923DGC287IET8','BEGIED23DGC23DT3',3);

insert into "BDvaccini".reazioneallergica values
	(3,'Rossore','Lorem Ipsum','EUT923DGC287IET8','02/13/2020');

\(\pagebreak\)

Operazioni

update "BDvaccini".vaccino
set efficacia = 0.95
where nome = 'Flustop';

delete from "BDvaccini".convocazione
where data = '04/23/2020'
and codicefiscale = 'BSTED23DGC23DT3C';

update "BDvaccini".convocazione
set data = '02/20/2020'
where data = '02/13/2020'
and codicefiscale = 'EUT923DGC287IET8';

delete from "BDvaccini".allergia
where nome = 'Albicocche';

update "BDvaccini".cittadino
set email = 'p123@elmail.org', cellulare = '338923004'
where codicefiscale = 'CSTEEU3DGC256H3C';

update "BDvaccini".vaccinazione
set medico = 'GITIED23DGC23DT3'
where data = '02/13/2020'
and codicefiscale = 'EUT923DGC287IET8';

update "BDvaccini".centrovaccinale
set cittá = 'Pisa', indirizzo = 'Via Venezia 13'
where cittá = 'Pisa'
and indirizzo = 'Via Venezia 12c';