Tesina basi di dati

CLOB: Permette di rappresentare testi di grandi dimensioni

Risulta essere più flessibile dei varchar per rappresentare testi di tipo descrittivo.

5 covigraph2.2. Creazione e popolamento tabella master l'inserimento

In questo capitolo elencheremo le istruzioni DDL per la creazione e dei dati nella tabella master la quale ci agevolerà nella popolazione dello schema normalizzato.

La struttura della tabella master è la stessa struttura del file CSV dove sono contenuti i file, ovvero:

covid_19_data_master (data, stato, cod_reg, denominazione_reg, cod_provincia, denominazione_provincia, sigla_provincia, latitudine, longitudine, totale_casi, note_it, note_en)

Attributo Tipo Byte Descrizione

data Date 3 Riporta la data relativa al record di contagio

stato Char (3) 3 Permette di rappresentare lo stato a cui appartiene la provincia

cod_reg Int 4 Riporta il codice della regione alla quale la provincia appartiene

denominazione_reg Varchar2 (50) 50 Riporta il nome per esteso della provincia

<table>
  <tr>
    <th>Attributo</th>
    <th>Tipo dati</th>
    <th>Descrizione</th>
  </tr>
  <tr>
    <td>regionezione_regcod_provincia</td>
    <td>Int 4</td>
    <td>Riporta il codice della provincia alla quale il record di contagio si riferisce</td>
  </tr>
  <tr>
    <td>denomna</td>
    <td>Varchar2 (50)</td>
    <td>Riporta il nome della provincia per esteso</td>
  </tr>
  <tr>
    <td>provinciasigla_provincia</td>
    <td>Char (3)</td>
    <td>Riporta la sigla della provincia</td>
  </tr>
  <tr>
    <td>latitudine</td>
    <td>Number(10) 8</td>
    <td>Riporta la latitudine della provincia a cui il record di contagio si riferisce</td>
  </tr>
  <tr>
    <td>longitudine</td>
    <td>Number(10) 8</td>
    <td>Riporta la longitudine della provincia a cui il record di contagio si riferisce</td>
  </tr>
  <tr>
    <td>totale_casi</td>
    <td>Int 4</td>
    <td>Riporta i casi rilevati in quella data dal record di contagi</td>
  </tr>
  <tr>
    <td>covigraphnote_it</td>
    <td>CLOB 64K</td>
    <td>Riporta eventuali note e commenti riguardanti il record in lingua italiana</td>
  </tr>
  <tr>
    <td>note_en</td>
    <td>CLOB 64K</td>
    <td>Riporta eventuali note e commenti riguardanti il record in lingua inglese</td>
  </tr>
</table>

<p>Invece di seguito si riporta lo statement DDL per la creazione di suddetta tabella</p>

<pre>
<code>
CREATE TABLE covid_19_data_master (
  data date,
  stato CHAR (3),
  cod_regione
</code>
</pre>

INT,denominazione_regione VARCHAR2 (50),cod_provincia int,denominazione_provincia VARCHAR2 (50),sigla_provincia CHAR (3),latitudine NUMBER(10,5),longitudine NUMBER(10,5),totale_casi INT,note_it CLOB,note_en CLOB);

Come chiave primaria di questa tabella si sceglie la coppia data, cod_provincia e la settiamo col seguente statement

ALTER TABLE covid_19_data_master
ADD CONSTRAINT pk_master_table PRIMARY KEY (data,cod_provincia)

Una volta creata la tabella master la si popola con i record forniti dai documenti.
Di seguito riportiamo alcuni statement di inserimento, se si è interessati a vederli tutti aprire il file Insert_completo_mergedOracle.doc 7 covigraph

INSERT INTO covid_19_data_master(data,stato,cod_regione,denominazione_regione,cod_provincia,denominazione_provincia,sigla_provincia,latitudine,longitudine,totale_casi,note_it,note_en) VALUES(DATE'2020-02-24', 'ITA', 1, 'Piemonte', 1, 'Torino', 'TO', 45.0732745, 7.680687483, 0, NULL,

NULL);INSERT INTO covid_19_data_master(data,stato,cod_regione,denominazione_regione,cod_provincia,deno-minazione_provincia,sigla_provincia,latitudine,longitudine,totale_casi,note_it,note_en) VALUES(DATE'2020-02-24', 'ITA', 1, 'Piemonte', 2, 'Vercelli', 'VC', 45.32398135, 8.423234312, 0, NULL, NULL);INSERT INTO covid_19_data_master(data,stato,cod_regione,denominazione_regione,cod_provincia,deno-minazione_provincia,sigla_provincia,latitudine,longitudine,totale_casi,note_it,note_en) VALUES(DATE'2020-02-24', 'ITA', 1, 'Piemonte', 3, 'Novara', 'NO', 45.44588506, 8.621915884, 0, NULL, NULL);………… 8 covigraph3. Normalizzazione3.1. Normalizzazione schema masterDi seguito si presenta il processo di normalizzazione a cui è sottoposta la tabella master poichéquest’ultima per via della sua struttura presenta diverse anomalie.l’organizzazione

1. CASO (data, cod_provincia: PROVINCIA, totale_casi, note_it, note_en)
2. PROVINCE (cod_provincia, cod_regione:REGIONE, denominazione_provincia, sigla_provincia,latitudine, longitudine)
3. REGIONI (cod_regione, denominazione_regione, stato)

Gli attributi evidenziati sono quelli scelti per essere chiave primaria della relazione.

Il seguente schema si presenta in 3NF e sarà lo schema che implementeremo successivamente.

9 covigraph3.2. Creazione dello schema normalizzato

Di seguito illustreremo la creazione delle varie tabelle generate dal processo di normalizzazione della relazione covid_19_data_master.

Le relazioni da tradurre in tabelle sono le seguenti:

1. CASI (totale_casi, note_it, note_en)
2. PROVINCE (denominazione_provincia, sigla_provincia, latitudine, longitudine)
3. REGIONI (denominazione_regione, stato)

Si noti che se sullo schema normalizzato si esegue di prodotto cartesiano ci si ritrova con la struttura della tabella master, ulteriore dimostrazione che il nostro schema non è stato alterato.

4. Arricchimento dello schema

4.1. Aggiunte allo schema

Allo scopo di migliorare le tipologie di analisi effettuabili sui dati dello schema sono state aggiunte nuove colonne e tabelle.

Di seguito le aggiunte:

Creazione

CREATE TABLE contagi_regione(
    data DATE,
    cod_regione INT,
    totale_casi INT,
    ricoverati_con_sintomi INT,
    terapia_intensiva INT,
    totale_ospedalizzati INT,
    isolamento_domiciliare INT,
    dimessi_guariti INT,
    casi_testati INT,
    deceduti INT,
    tamponi INT,
    variazione_totale_positivi INT,
    nuovi_positivi INT,
    totale_positivi INT,
    CONSTRAINT pk_contagi_regione PRIMARY KEY(cod_regione,data)
);

ALTER TABLE regione
ADD COLUMN popolazione INT,
ADD COLUMN densita_abitative INT,
ADD COLUMN numero_aeroporti INT,
ADD COLUMN superficie NUMBER(10,5);

covid_19_data_master_region(data,stato,cod_regione,denominazione_regione,latitu-dine,longitudine,ricoverati_con_sintomi,terapia_intensiva,totale_ospedalizzati,isolamento_domiciliare,di-messi_guariti,casi_testati,deceduti,tamponi,variazione_totale_positivi,nuovi_positivi,totale_positivi,to-tale_casi,note_it,note_en) VALUES(DATE'2020-02-24', 'ITA', 2, 'Valle d''Aosta', 45.73750286,7.320149366, 0, 0, 0, 0, 0, NULL, 0, 7, 0, 0, 0, 0, NULL, NULL);
INSERT INTO covid_19_data_master_region(data,stato,cod_regione,denominazione_regione,latitu-dine,longitudine,ricoverati_con_sintomi,terapia_intensiva,totale_ospedalizzati,isolamento_domiciliare,di-messi_guariti,casi_testati,deceduti,tamponi,variazione_totale_positivi,nuovi_positivi,totale_positivi,to-tale_casi,note_it,note_en) VALUES(DATE'2020-02-24', 'ITA', 2, 'Valle d''Aosta', 45.73750286,7.320149366, 0, 0, 0, 0, 0, NULL, 0, 7, 0, 0, 0, 0, NULL, NULL);