UNIVERSIT `
A DI PISA
Dipartimento di Informatica
Corso di Laurea Magistrale in Informatica per l’economia e per l’azienda (Business Informatics)
TESI DI LAUREA
Metodi di gestione del flusso informativo
tra aziende erogatrici di servizi pubblici e
autorit`
a di vigilanza:
il caso A.S.A. S.p.A.
Relatori:
Prof. Salvatore Ruggieri Prof. Riccardo Dulmin
Candidato: Carlo Macchia
Indice
1 INTRODUZIONE 3
1.1 Presentazione del problema . . . 3
1.2 Rassegna della letteratura . . . 4
1.3 Contenuto della tesi . . . 5
2 IL CASO DI STUDIO 7 2.1 Azienda servizi ambientali S.p.A. . . 8
2.2 Autorità idrica Toscana . . . 9
2.3 La piattaforma NetSIC per lo scambio dei dati . . . 10
2.4 Obiettivi del progetto . . . 12
3 ARCHITETTURA COMPLESSIVA 17 3.1 Presentazione dei singoli componenti . . . 17
4 PROGETTAZIONE E REALIZZAZIONE DELLE BASI DI DATI OPERAZIONALI PER L’INFRASTRUTTURA IDRI-CA 21 4.1 Progettazione della base di dati per l’infrastruttura dell’acque-dotto . . . 21
4.1.1 Analisi dei requisiti . . . 21
4.1.2 Progettazione concettuale . . . 25
4.1.3 Progettazione logica . . . 30
4.2 Progettazione della base di dati per l’infrastruttura fognaria . 44 4.2.1 Analisi dei requisiti . . . 44
4.2.2 Progettazione concettuale . . . 47
4.2.3 Progettazione logica . . . 51
4.3 Realizzazione delle basi di dati operazionali . . . 59
5 PROGETTAZIONE E REALIZZAZIONE DEI PROCESSI DI ETL 62 5.1 Processo di caricamento delle basi di dati dell’infrastruttura idrica . . . 65
5.2 Processo di estrazione e caricamento dalle basi di dati sull’in-frastruttura idrica al file Excel . . . 75
6 ESTRAZIONE DEL GRAFO DELLA RETE IDRICA 78 6.1 Presentazione del problema . . . 80
6.2 Progettazione e realizzazione di una visita sul grafo della rete idrica con linguaggio Java . . . 83
6.2.1 Utilizzo della libreria JGraphT . . . 84
6.2.2 Utilizzo di una metodologia alternativa basata sulla struttura HashMap . . . 90 6.3 Visita sul grafo della rete idrica con stored procedure in Pl-Sql 100
BIBLIOGRAFIA 109
APPENDICE 112
Strutture dati per la memorizzazione di grafi . . . 112 Algoritmi di visita (BreadthFirstSearch , DeepFirstSearch) . . . 116
Sommario
Il presente lavoro ha come oggetto la progettazione e lo sviluppo di una soluzione per migliorare i rapporti informativi tra le aziende erogatrici di servizi pubblici e gli enti di vigilanza e controllo. Vedremo come garantire la produzione dei report richiesti riducendo il più possibile l’utilizzo di risorse per l’impresa.
Partendo dalle richieste della autorità di vigilanza e dai dati disponibili in azienda, creeremo due basi di dati operazionali che possano costituire un ponte stabile tra le due realtà informative, spesso molto diverse tra loro. Svilupperemo dei processi ETL al fine di creare una continuità tra le fonti di dati preesistenti nell’azienda e gli obiettivi informativi che le autorità richiedono.
Il caso che nello specifico è stato affrontato riguarda il rapporto tra l’A-zienda Servizi Ambientali S.p.A. di Livorno e l’Autorità idrica Toscana. Lo studio della problematica per cui l’impresa si trova obbligata a redarre re-port veritieri e dettagliati sulla conformazione dell’infrastruttura utilizzata nel servizio idrico integrato, ci dà l’occasione di mostrare come sia possibile velocizzare e automatizzare tali processi.
Capitolo 1
INTRODUZIONE
1.1
Presentazione del problema
Negli ultimi anni gli obblighi informativi delle società di gestione dei servizi pubblici locali (in particolare di quelle a partecipazione pubblica) nei con-fronti degli enti di vigilanza, sono aumentati considerevolmente, e con essi le difficoltà per una loro gestione efficiente.
L’attenzione crescente a che queste società perseguano sia un equilibrio durevole, sia l’interesse pubblico a un corretto svolgimento del servizio, porta le autorità competenti a svolgere un controllo sempre più severo, soprattutto in un momento di instabilità economica, quale quello attuale.
Le autorità, per esplicare la loro funzione, impongono a livello normativo che tutte le aziende sottoposte al loro controllo trasmettano dati su ogni settore di attività, dal conto economico alla condizione delle infrastrutture utilizzate.
at-tualmente disponibili presso le basi dati aziendali, e anche nel caso in cui lo siano, richiedono un grosso dispendio di risorse per essere organizzati secondo le forme previste ed essere consegnati nel rispetto delle scadenze.
Poichè nella maggior parte delle realtà questo processo di produzione di informazioni è svolto senza alcuna forma di automatizzazione, il problema che ci poniamo è di garantire l’adempimento alle richieste minimizzando, ove possibile, l’utilizzo di risorse, sia in termini di tempo che manodopera.
A tal fine progetteremo e svilupperemo delle basi di dati operazionali che, prelevando dati da quelle esistenti e integrandoli con nuove fonti, possano rispondere alle nuove esigenze in modo efficiente.
Gestiremo il flusso di dati tramite processi ETL automatizzati in modo che l’azienda possa rispettare ogni scadenza senza allocarvi ulteriori risorse oltre a quelle strettamente necessarie a effettuare revisioni finali sui dati da trasmettere.
Mostreremo come affrontare una problematica di questo genere portando come esempio il caso dell’Azienda servizi Ambientali S.p.A., fornitrice di servizi idrici nella provincia di Livorno.
1.2
Rassegna della letteratura
Per svolgere questo lavoro abbiamo utilizzato sia testi teorici di progettazione di basi di dati sia le varie documentazioni per utilizzare al meglio gli strumenti scelti. Abbiamo inoltre attinto dalle fonti forniteci dall’azienda per poter comprendere sia la natura del problema, che il panorama normativo nel quale si muovono soggetti coinvolti. Infine, per il lavoro di approfondimento svolto
sulle tecniche di visita e analisi di grafi, abbiamo passato in rassegna testi teorici e articoli in rete per conoscere le scelte più comuni e i metodi per implementarle.
1. Per l’individuazione della problematica e l’analisi dei soggetti: [Ufficio Marketing Asa 13], [A.i.T. 13], [GST S.p.A. 13].
2. Per la progettazione e la realizzazione della base di dati sull’infrastrut-tura idrica: [Atzeni 09], documentazione del DBMS MySql e documen-tazione sul sistema informativo territoriale (SIT) gestito dall’Azienda Servizi Ambientali(Asa).
3. Per la progettazione e la realizzazione delle procedure ETL: [Pentaho 14]. 4. Per l’approfondimento sulle tecniche di visita su grafo:
[Cormen 05], [Naveh 14], [Wikipedia 10], [Brawley 11].
1.3
Contenuto della tesi
Nel prossimo capitolo presenteremo le caratteristiche dei soggetti coinvolti, soprattutto dal punto di vista normativo, al fine di comprendere l’evoluzione dei rapporti tra le società di servizi idrici e le autorità di vigilanza.
Nel Capitolo 3 chiariremo l’architettura data all’intero sistema di gestione del flusso dati, motivando le nostre scelte.
Nel Capitolo 4 affronteremo la progettazione di due basi di dati, una per l’infrastruttura dell’acquedotto e l’altra per l’infrastruttura fognaria, necessarie a garantire un’intermediazione tra sorgente e destinazione del flusso.
Nel Capitolo 5 spiegheremo i passaggi da compiere per realizzare i processi ETL tra i dati disponibili all’Azienda Servizi Ambientali S.p.A e le basi dati da noi realizzate. Inoltre, mostreremo come estrarre i dati dalle basi dati e come inserirli in appositi file per permetterne il trasferimento presso il sistema centralizzato dell’autorità.
Nel Capitolo 6 affronteremo la fase più critica del nostro progetto. Ap-profondiremo le tecniche di memorizzazione e visita su grafo per estrarre in-formazioni sui rapporti di interconnessione tra gli impianti della rete idrica. Risolveremo il problema con l’utilizzo di due distinti linguaggi di program-mazione, Java, orientato a oggetti e Pl-Sql, estensione procedurale dell’Sql. Di entrambi illustreremo vantaggi e svantaggi e valuteremo quale tra i due sia il più adatto al nostro specifico caso.
Nel Capitolo 7, il capitolo conclusivo, analizzeremo i vantaggi ottenuti dal migliormento del processo oggetto del nostro lavoro.
Infine in Appendice, sono inserite nozioni teoriche sulle strutture di me-morizzazione di grafi e sui metodi per visitarli, utili per comprendere meglio quanto fatto nel Capitolo 6.
Capitolo 2
IL CASO DI STUDIO
In questo capitolo presenteremo i soggetti coinvolti e gli obiettivi del caso affrontato.
Il flusso informativo che studieremo si colloca tra l’Azienda Servizi Am-bientali di Livorno, d’ora in poi indicata con la sigla Asa, e l’Autorità idrica Toscana, che indicheremo con la sigla AiT.
L’autorità espleta la sua funzione di controllo obbligando i gestori di servizi idrici integrati a comunicarle con regolarità numerose informazioni sull’attività svolta.
L’Asa, che si configura come uno dei gestori del servizio idrico integrato in Toscana, è chiamata ad adempiere a questo obbligo.
L’intermediazione tra i due soggetti è garantita da una piattaforma web, denominata NetSic, che oltre a permettere la consegna dei dati da parte dei singoli gestori si interfaccia con una base di dati centralizzata che supporta l’autorità nello svolgimento della propria funzione istituzionale.
2.1
Azienda servizi ambientali S.p.A.
L’Azienda Servizi Ambientali è una Società per Azioni a capitale prevalente-mente pubblico, costituita dal Comune di Livorno nel 1998 e successivamen-te parsuccessivamen-tecipata dai Comuni delle Province di Livorno, Pisa e Siena ricadenti nell’Ambito Territoriale Ottimale n. 5 “Toscana Costa” di cui alla legge Regionale n. 81/1985.
Le sono attribuiti istituzionalmente l’impianto e l’esercizio di servizi es-senziali per la vita urbana e per lo sviluppo delle comunità locali in cui opera, quali la gestione del ciclo completo delle acque per usi civili ed industriali (captazione, trattamento e distribuzione, raccolta e depurazione) e la distri-buzione del gas metano. L’Azienda opera inoltre nel settore della produzione energetica da fonti rinnovabili e nella offerta di servizi finalizzati al risparmio energetico.
Asa S.p.A ha assunto dal 1° gennaio 2002 il ruolo di Gestore Unico per il Ciclo integrato delle Acque dell’ambito territoriale ottimale n° 5 Toscana Co-sta : 33 comuni e 3 province da servire ed unire in una visione sovracomunale dell’uso efficiente ed ottimizzato delle risorse e degli strumenti di gestione, con un disegno di reti integrate di distribuzione, con una flessibile capacità di rappresentare le realtà locali e di attrarre investimenti privati.
Questo ruolo istituzionale, esercitato in una così ampia realtà territo-riale con articolati valori ambientali, economici e culturali, ha naturalmen-te accentuato il valore della capacità complessiva già sperimentato dall’A-zienda nella gestione di sinergie in termini industriali, con un alto livello d’affidabilità tecnologica. Un sistema d’imprese, necessariamente
comple-mentari e coerenti con la sua missione aziendale, è stato il valore aggiunto di Asa S.p.A, essenziale per competere in ambiti più estesi e diversificati [Ufficio Marketing Asa 13].
2.2
Autorità idrica Toscana
L’Autorità Idrica Toscana è situata a Firenze, è un ente pubblico rappresen-tativo di tutti i comuni toscani, al quale la legge regionale 69 del 28 dicembre 2011 attribuisce le funzioni di programmazione, organizzazione e controllo sull’attività di gestione del servizio idrico integrato.
Dal 1° gennaio 2012 le funzioni già esercitate dalle autorità di ambito territoriale ottimale sono state trasferite ai comuni che le esercitano obbli-gatoriamente tramite l’Autorità Idrica Toscana. Sono organi dell’Autorità: l’Assemblea, il Direttore Generale, il Revisore Unico dei Conti. La Direzione Generale è ubicata nella sede di Firenze, mentre sono presenti altre sedi nelle città di Arezzo, Grosseto, Livorno, Lucca e San Miniato.
Gli utenti a cui l’Autorità Idrica Toscana si rivolge sono le famiglie e le aziende. Le Conferenze Territoriali sono le seguenti: n.1 Toscana Nord a Lucca; n.2 Basso Valdarno a San Miniato; n.3 Medio Valdarno a Firenze; n.4 Alto Valdarno ad Arezzo; n.5 Toscana Costa a Livorno; n.6 Ombrone a Grosseto [A.i.T. 13].
2.3
La piattaforma NetSIC per lo scambio dei
dati
NetSIC è la piattaforma web che l’AiT, affinchè possa procedere nell’eserci-zio della propria funnell’eserci-zione istitunell’eserci-zionale, mette a disposinell’eserci-zione dei gestori del servizio idrico integrato per la consegna strutturata e organizzata dei da-ti. Questa piattaforma, costituita da diversi moduli software per ogni area tematica, consente lo scambio di informazioni e l’analisi della bontà della gestione.
I moduli, elencati di seguito, riguardano ogni settore dell’azienda e ga-rantiscono un’informazione completa e trasversale sulle attività svolte.
• Scadenziario/Registro adempimenti; • Conto Economico riclassificato; • dB Infrastrutture;
• modStandard Tecnici; • modStandard Organizzativi;
• dB Analisi delle politiche di articolazione Tariffaria; • modAnalisi dei Progetti;
• POT preventivo/consuntivo; • dB Scambi Infragruppo; • dB Analisi del Fatturato;
• modAnalisi e previsione dei consumi;
• AutoSKA per la gestione delle Autorizzazioni allo Scarico.
Ogni modulo ha un’interfaccia con la quale l’utente, oltre a poter vedere le scadenze imposte per la consegna, può importare i dati richiesti inserendoli all’interno di file in formato XLS, XML o CSV. Per ognuno di essi, AiT fornisce una dettagliata documentazione per indicarne le specifiche in termini di forma e di contenuto.
Il processo di acquisizione e validazione dei dati si innesca con l’upload del file, in conseguenza ad una consegna prevista ad una determinata scadenza. Il file viene registrato nel sistema e quindi viene verificata la rispondenza del formato e delle informazioni di identificazione. I dati sono importati in tabelle e quindi caricati nella base di dati centralizzata del sistema. A fronte di tale attività il gestore riceverà un’email di ricevuta consegna; dopo l’invio della email i dati non protranno più essere modificati.
NetSIC è accessibile via web tramite credenziali e profili di accesso per-sonalizzabili e può essere utilizzata internamente tra le diverse Conferenze Territoriali e i diversi gruppi organizzativi dell’AiT, per lo scambio dati e per la redazione congiunta dei documenti.
I’intera piattaforma si presenta come una fonte di dati unica e centraliz-zata per tutte le informazioni storicamente prodotte ed elaborate dai diversi soggetti per competenza e attività istituzionale. NetSIC ha una parte pub-blicata e pubblicabile su web che, in adempimento con gli obblighi di traspa-renza previsti per la Pubblica Amministrazione, rende disponibili e fruibili le
informazioni: alla Regione, ai Comuni, ai gestori ed in parte anche al generico utente web [GST S.p.A. 13].
Queste informazioni sono ottenute dall’elborazione di due categorie di-stinte di dati. La prima derivante dalla comunicazione tra il singolo gestore e l’AiT, mentre la seconda standard, contenente i dati e gli indici di riferimento forniti dall’AiT.
La Figura 2.1 illustra il flusso di dati all’interno di NetSIC. Per ogni modulo, il sistema effettua un confronto tra i dati ottenuti dai gestori e gli standard definiti dall’Autorità producendo informazioni di sintesi utili per predisporre, ove necessario, penalizzazioni o revisioni tariffarie.
2.4
Obiettivi del progetto
Asa richiede un metodo per automatizzare la redazione del file XLS, che andrà a costituire l’input del modulo relativo alle infrastrutture. Il nostro obiettivo sarà dunque, partendo dai dati a disposizione dell’azienda, garantire l’adempimento delle scadenze di consegna cercando di minimizzare l’utilizzo di risorse.
Il file XLS conterrà i dati sulla consistenza e sulla funzionalità di tutte le opere del servizio idrico integrato e costituirà una fonte di alimentazione per i successivi moduli.
L’importazione del file all’interno del modulo garantisce all’Autorità la conoscenza dello stato di conservazione delle opere puntuali e delle reti, ne-cessaria per la pianificazione degli interventi e la rilevazione di eventuali criticità delle infrastrutture [GST S.p.A. 13]. il modulo delle infrastrutture
CODICE A.i.T OPERA
a1x1 Captazione da corsi d’acqua (fiumi)
a1x2 Captazione da laghi
a1x3 Captazione da campi (pozzi)
a1x4 Captazione da sorgenti
a2 Impianti di potabilizzazione
a3 Reti di adduzione
a3-tratte Singole tubazioni delle reti di adduzione
a4 Opere di accumulo (serbatoi)
a5 Impianti di pompaggio
a6 Reti di distribuzione
a6-tratte Singole tubazioni delle reti di distribuzione
f1 Reti di raccolta (reti fognarie)
f2 Impianti di sollevamento
f3 Collettori
f4 Opere per il trattamento delle acque usate (depuratori)
f5 Scaricatori di piena
f6 Condotte sottomarine
Tabella 2.1: Opere di interesse per l’autorità. Componenti dell’infrastruttura idrica.
permette infatti l’archiviazione delle informazioni relative a tutte le opere idriche utilizzate nei servizi di acquedotto, depurazione e fognatura.
Nella Tabella 2.1 sono elencate tutte le tipologie di opere di cui sono richieste informazioni.
I dati raccolti per ognuna delle tipologie di opere sono individuati con lo scopo di caratterizzare le diverse categorie di infrastrutture e contengono tutte quelle informazioni ritenute necessarie alla determinazione di valori di ingresso per altri moduli.
Oltre a queste, il modulo prevede che vengano inserite informazioni sulle relazioni che esistono nella rete idrica tra le suddette opere. In altre parole si vuole sapere quali impianti e quali reti sono collegati tra loro dalle tubazioni
SORGENTE DESTINAZIONE Captazione da Fiumi Potabilizzatori Captazione da Fiumi Reti di distribuzione Captazione da Laghi Potabilizzatori Captazione da Laghi Reti di distribuzione Captazione da Pozzi Potabilizzatori Captazione da Pozzi Reti di distribuzione Captazione da Sorgenti Potabilizzatori Captazione da Sorgenti Reti di distribuzione
Potabilizzatori Reti di distribuzione
Serbatoi Reti di adduzione
Serbatoi Reti di distribuzione
Pompaggi Potabilizzatori
Pompaggi Serbatoi
Reti di adduzione Reti di distribuzione
Depuratori Collettori
Scaricatori Reti di raccolta
Tabella 2.2: I rapporti di raggiungibilità richiesti dall’Autorità idrica Toscana della rete. Nella Tabella 6.1 elenchiamo le coppie di opere di cui interessano i rapporti di raggiungibilità.
AiT fornisce un file Excel di esempio, da utilizzare come modello. Ogni foglio del file modello contiene una tabella relativa o ad un’opera o ad una rete o ad un coppia di esse e per ognuna sono inseriti tutti gli attributi ri-chiesti, con annesso il tipo di dato previsto. Nella Figura 2.2 è mostrata la struttura del file. La prima componente (Fogli 1-11) contiene i fogli neces-sari a identificare e caratterizzare ogni tipo di impianto o rete, la seconda (Fogli 12-18), contiene i fogli per inserire l’informazione sulle connessioni che esistono sulla rete.
Capitolo 3
ARCHITETTURA
COMPLESSIVA
Prima di affrontare nei dettagli ogni fase del lavoro svolto presentiamo uno schema (Fig. 3.1) che chiarifichi i ruoli dei singoli componenti necessari alla gestione del flusso informativo tra le fonti di dati infrastrutturali in Asa e la piattaforma NetSIC.
3.1
Presentazione dei singoli componenti
Abbiamo scelto di dare all’intero processo di integrazione dati un’architettura a tre livelli. Il primo costituito dalle fonti e dalle destinazioni del flusso, il secondo costituito da processi ETL e il terzo, più profondo, costituito da due basi di dati per la conservazione delle informazioni sull’infrastruttura idrica. Analizziamo brevemente ognuno dei singoli componenti per chiarire meglio i motivi per cui adottare questa tipologia di architettura.
• Basi di dati dell’infrastruttura idrica
Per mantenere le informazioni estratte dal sistema informativo di Asa e destinate a NetSIC, ricorreremo alla creazione di due basi di dati di-stinte, una progettata per accogliere i dati sull’infratruttura acquedotto e una adibita alla conservazione delle informazione sull’infrastruttra fo-gnaria. Entrambe svolgeranno la funzione di intermediazione tra dati disponibili e dati richiesti. Saranno predisposte per contenere tutti i dati di interesse dell’autorità e per garantirne l’accesso nel modo più semplice possibile.
La scelta di adottare delle basi di dati di appoggio è necessaria a causa dell’eterogeneità tra il sistema informativo sorgente e il sistema infor-mativo di destinazione. Permette un controllo più elevato sul flusso di dati diretto all’autorità, che per sua natura, deve essere integro e conforme alle specifiche.
La possibilità di sostituire le basi di dati di appoggio con un’unica cedura ETL, eliminando il livello più profondo dell’architettura pro-posta, non è consigliabile principalmente per due motivi, la necessità di apportare integrazioni al sistema informativo di Asa già da tempo consolidato e la mancanza di uno spazio adibito alla conservazione dei dati intermedi.
La possibilità di conservare i dati durante le varie fasi di elaborazione che dovremo svolgere su di essi è un vantaggio notevole e inoltre assicura la scomposizione dell’intero processo in più componenti permettendo una più semplice fase di progettazione e sviluppo.
• Processi ETL in ingresso e in uscita
Per collegare le basi di dati di appoggio alla sorgente e alla destinazione del flusso progetteremo e svilupperemo due distinti processi ETL. Il processo in ingresso sarà responsabile del popolamento delle basi di dati intermedie. Selezionerà le informazioni di interesse dalla fonte e opererà su queste le trasformazioni necessarie per essere immesse nelle basi di dati.
Il processo in uscita, una volta terminate le elaborazioni da parte del DBMS, preleverà i dati inserendoli nel file Excel da importare sulla piattaforma NetSic.
• Fase di elaborazione del grafo della rete idrica
Partendo dai contenuti delle basi di dati di intermediazione creeremo delle tabelle integrative valutando la struttura a grafo della rete per in-dividuare i rapporti di raggiungibilità reciproca tra gli impianti. Valu-teremo due distinte soluzioni per realizzare questa fase, una ricorrendo all’utilizzo di procedure in PL-Sql direttamente elaborate dal DBMS e un’altra effettuando le analisi con programmi in linguaggio Java. Questa fase del processo è la più rilevante per la scelta dell’architettura a tre livelli. Data la loro complessità è infatti sconsigliabile eseguirle parallelamente al resto delle componenti.
Per ogni fase affrontata vedremo come realizzarla avvalendoci di software open source come il DBMS MySql e il componente Kettle della suite Pentaho per il data integration.
Capitolo 4
PROGETTAZIONE E
REALIZZAZIONE DELLE BASI
DI DATI OPERAZIONALI PER
L’INFRASTRUTTURA IDRICA
4.1
Progettazione della base di dati per
l’infra-struttura dell’acquedotto
4.1.1
Analisi dei requisiti
La fase di analisi dei requisiti consiste nel raccogliere specifiche informali ed eterogenee indicate dai committenti. Tali specifiche descrivono le proprietà e le funzionalità che il sistema richiesto dovrebbe avere. Costituisce il punto di partenza del processo di progettazione.
Asa richiede la creazione di un’applicazione in grado di rispondere in modo veloce ed esaustivo alle esigenze informative di AiT, che per il ruolo di vigilanza che ricopre, è interessata a conoscere la struttura degli acquedotti presenti sul territorio toscano con particolare attenzione alle caratteristiche degli impianti e delle tratte che ne fanno parte.
Gli argomenti di interesse per AiT in ambito infrastrutturale si dividono in tre categorie:
1. Specifiche degli impianti presenti sulla rete. 2. Specifiche delle tubazioni di collegamento.
3. I rapporti esistenti tra gli impianti e tra le tubazioni dell’acquedotto, al fine di conoscere il percorso che l’acqua effettua sulla rete.
Per la realizzazione di questa base di dati avremo bisogno di:
• individuare tutti gli impianti dell’infrastruttura e memorizzarne le loro caratteristiche anagrafiche e tecniche;
• individuare tutti i nodi dell’infrastruttura (in cui possono essere localiz-zati o meno gli impianti) e memorizzarne le caratteristiche anagrafiche e geografiche (nella fattispecie coordinate);
• individuare tutte le tratte di congiungimento dei nodi e memorizzarne le caratteristiche anagrafiche e tecniche;
• individuare tutte le reti (insiemi di tratte relazionate tra loro) che ne fanno parte e memorizzarne le loro caratteristiche anagrafiche e tecniche;
Figura 4.1: Ruolo del database di intermediazione
Nel manuale di NetSIC è presente una dettagliata documentazione dove si definisce il tipo di informazioni richieste, la forma che esse devono avere e le scadenze imposte per la consegna dei report. Proprio questi file che costituiscono l’input per la piattaforma NetSIC costituiranno l’output che la base di dati deve essere in grado di fornire.
Lo schema all’interno della Figura 4.1 chiarifica il ruolo di intermediazione della base di dati.
Il sistema infomativo territoriale di Asa, che d’ora in poi indicheremo con la sigla SIT, raccoglie informazioni su tutti gli impianti e gli accessori coinvolti nella rete, descrivendo anche entità che vanno oltre le richieste di AiT, come camerette e organi speciali. Per questo si opererà una selezione dei soli dati interessanti.
Ricordiamoci che questa base di dati non ha come primo obiettivo il tracciamento dettagliato di ogni componente dell’infrastruttura idrica, argo-mento di stretto interesse dei gestori del servizio, ma quello di garantire una produzione efficiente dei report richiesti.
Analizzando approfonditamente il diagramma concettuale del SIT memo-rizzato presso Asa, di cui inseriamo un estratto nella Figura 4.2, è possibile capire ciò che deve essere mantenuto. Ad esempio le tabelle impianti, nodi e
Figura 4.2: Estratto dell’ E-R del sistema informativo territoriale di Asa tratte, garantiscono un’ossatura alla base di dati, e dovranno essere conserva-te. All’interno di queste tabelle è raccolta l’informazione sulla configurazione della rete dell’acquedotto.
La tabella Impianti contiene le informazioni riguardanti la tipologia, la localizzazione e lo stato di conservazione di tutti gli impianti, le tabelle nodi e tratte stabiliscono una relazione tra gli impianti creando la struttura a grafo che andremo in seguito ad analizzare. L’analisi ci permetterà di sapere quali sono i rapporti di raggiungibilità tra impianti e di conseguenza potremo conoscere in quali impianti e in quali reti scorre l’acqua captata.
Continuando l’analisi del sistema esistente risulta evidente che è stato pro-gettato prima che AiT rendesse nota la necessità di conoscere l’infrastruttura con un livello di dettaglio molto alto. Mancano infatti per gli impianti e le reti numerosi attributi che dovranno essere aggiunti.
Mantenendo da un lato la struttura del SIT Asa e dall’altro le specifiche del file di import di AiT procediamo con la progettazione concettuale della base di dati.
Figura 4.3: Schema della struttura di un acquedotto.
4.1.2
Progettazione concettuale
Per rappresentare l’acquedotto abbiamo scelto una astrazione basata sul mo-dello a grafo poichè più vicina alla realtà fisica da rappresentare. Un grafo è un insieme di nodi e di archi insistenti tra i nodi stessi. Nei nodi sono localizzati gli impianti e gli archi rappresentano le tubazioni che li collegano. Il ciclo delle acque è di per sè semplice se si pensa che l’acqua fluisce dai punti di raccolta a quelli di accumulo per essere infine distribuita alle utenze. Il modello utilizzato è schematizzato nella Figura 4.3.
L’obiettivo della progettazione concettuale è la realizzazione di un mo-dello entità-relazione. Chiamato anche con l’abbreviazione E-R, il momo-dello fornisce una serie di strutture, dette costrutti, atte a descrivere la realtà di interesse in una maniera facile da comprendere e che prescinde dai criteri di organizzazione dei dati nei calcolatori. Questi costrutti vengono utilizzati per definire schemi che descrivono l’organizzazione e la struttura delle occorrenze dei dati, ovvero, dei valori assunti dai dati al variare del tempo. Le entità
rappresentano classi di oggetti (fatti, cose, persone) che hanno proprietà co-muni ed esistenza autonoma ai fini dell’applicazione di interesse. Le relazioni, o associazioni, rappresentano legami logici, significativi per l’applicazione di interesse, tra due o più entità [Atzeni 09].
Individuiamo di seguito le varie entità coinvolte nella rappresentazione di una rete di acquedotto.
Gli impianti sono le strutture responsabili del trattamento dell’acqua dalla fonte all’utenza finale. A seconda della loro funzione durante il ciclo dell’ac-qua se ne individuano dell’ac-quattro diverse categorie, le captazioni, i pompaggi, i serbatoi e i potabilizzatori.
Le captazioni prelevano l’acqua dalle fonti naturali. Poichè tali fonti possono essere costituite da fiumi, laghi, sorgenti o pozzi, ognuna di esse co-stituisce un’entità distinta. L’acqua captata viene poi spinta naturalmente o tramite un pompaggio verso un potabilizzatore che ha il compito di ren-derla utilizzabile per il consumo. Al potabilizzatore sono collegati a valle ulteriori pompaggi e serbatoi in modo che l’acqua possa raggiungere le reti di distribuzione finale.
Considereremo come entità del nostro schema ogni categoria e sottotegoria di impianto. All’interno del modello E-R rappresentiamo queste ca-tegorie tramite una generalizzazione a due livelli. Entrambi i livelli della generalizzazione sono totali ed esclusivi (Fig. 4.4).
L’entità impianti è collegata all’entità nodi. I nodi oltre a costituire i punti di partenza e di arrivo delle tratte permettono di localizzare gli impianti nel grafo.
Figura 4.4: Schema delle generalizzazioni dell’entità opera.
nodi è localizzato un impianto. Alcuni di essi infatti, possono rappresentare semplici diramazioni nella rete o organi e accessori che non interessando AIT possono essere omessi.
Due nodi collegati tra loro da un arco costituiscono una tratta dell’acque-dotto. Le tratte, che costituiscono un’entità a se stante, a loro volta fanno parte di una rete.
Le reti sono di due tipologie, di adduzione o di distribuzione, le prime contengono tubazioni che collegano i punti di captazione ai serbatoi di ulti-mo livello passando da impianti di pompaggio e potabilizzazione, le seconde prelevano l’acqua dal serbatoio più vicino ai consumatori e la distribuiscono all’utenza finale. Come è stato fatto per gli impianti, rappresentiamo le re-ti come una generalizzazione totale ed esclusiva (Fig. 4.5). L’enre-tità rete è padre di due entità figlie, adduttrici e distribuzioni.
Le entità fino ad ora individuate costituiscono gli elementi basilari del grafo. Gli archi del grafo, chiamati nel nostro caso tratte, collegano tra loro i nodi e di conseguenza gli impianti in essi localizzati.
Figura 4.5: Schema della generalizzazione dell’entità rete.
Analizzando il contenuto che i report informativi deve avere si possono individuare altre entità.
Poichè AiT è interessata alle sostanze presenti nell’acqua trattata dagli impianti e alle sostanze che transitano nelle reti creiamo un entità che le rappresenti.
Inseriamo anche l’entità pompe, che rappresenta le pompe presenti in un impianto. Ogni tipo di impianto può avere delle pompe al suo interno.
Infine l’entità comuni contenente tutti i comuni serviti da l’infrastruttura di acquedotto di Asa.
Nella Tabella 4.1 elenchiamo tutte le entità individuate.
Definiamo ora le relazioni che intercorrono tra le entità appena indivi-duate, con particolare attenzione alle cardinalità. Partiamo dagli elementi principali del nostro schema che abbiamo individuato in Impianti, nodi, tratte e reti.
Ogni impianto è localizzato in un nodo ma non in tutti i nodi è presente un impianto. Sussiste quindi tra le due entità, impianti e nodi, una relazione uno a uno che chiamiamo localizzazione.
Tra nodi e tratte esistono due relazioni, un nodo può essere punto di inizio o punto di fine di una tratta e ogni tratta ha sia un nodo sorgente sia un
ENTITA’ DESCRIZIONE
Impianto Impianto dell’infrastruttura di acquedotto.
Fiume Impianto di captazione da fiume.
Lago Impianto di captazione da lago.
Pozzo Impianto di captazione da pozzo.
Sorgente Impianto di captazione da sorgente. Potabilizzatore Impianto di potabilizzazione.
Pompaggio Impianto di pompaggio dell’acqua. Accumulo Impianto di accumulo dell’acqua.
Pompa Gruppo pompa installato in un impianto.
Nodo Nodo del grafo dell’infrastruttura d’acquedotto. Tratta Tratta del grafo dell’infrastruttura di acquedotto.
Rete Rete dell’infrastruttura d’acquedotto.
Adduzione Rete di adduzione.
Distribuzione Rete di distribuzione.
Comune Comune d’Italia.
Sostanza Sostanze chimiche
Tabella 4.1: Entità dello schema d’acquedotto nodo di destinazione.
Le singole relazioni che chiamiamo “è inizio” e “è fine” , sono entrambe uno a molti. Infatti una tratta avrà un solo nodo di inizio ed un solo nodo di fine ma da un nodo potranno partire più tratte e allo stesso modo in un nodo potranno confluire piu tratte.
Più tratte fanno parte di una rete, quindi inseriamo una relazione uno a molti tra le due entità che chiamiamo appartiene.
Cosi facendo abbiamo definito i costrutti necessari alla rappresentazione del modello a grafo tramite lo schema E-R.
Aggiungiamo ora le relazioni che collegano le ulteriori entità individuate al passo precedente.
rela-ASSOCIAZIONE DESCRIZIONE
Presenza Associa impianto a pompa.
Localizzazione Associa impianto a nodo.
RilevazioneInImpianto Associa impianto a sostanza.
E’ inizio Associa nodo (di inizio) a tratta.
E’ fine Associa nodo (di fine) a tratta.
Appartiene Associa tratta a rete.
Servizio Associa rete a comune.
RilevazioneInRete Associa rete a sostanza.
Tabella 4.2: Associazioni dello schema d’acquedotto.
zione molti a molti. Questo perchè una particolare sostanza può essere pre-sente in più impianti/reti e nello stesso impianto/rete possono essere presenti più sostanze. Il nome scelto per le due relazioni è rilevazione.
L’entita pompe è collegata a impianti con relazione uno a molti chiamata presenza. Un’impianto può avere nessuna o più pompe e una pompa può appartenere ad un solo impianto.
Infine concludiamo lo schema E-R Acquedotto inserendo la relazione molti a molti tra comuni e reti. Una rete puo servire più comuni e un comune può essere servito da più reti.
Le associazioni sintetizzate nella Tabella 4.2 descrivono i legami logici tra le entità individuate nel paragrafo precedente.
Nella Figura 4.6 si si mostra lo schema Entità-Relazione prodotto dalla progettazione concettuale.
4.1.3
Progettazione logica
L’obiettivo della progettazione logica è quello di costruire uno schema logico in grado di descrivere, in maniera corretta ed efficace, tutte le informazioni
contenute nello schema Entità-Relazione prodotto nella fase di progettazione concettuale.
Diciamo subito che non si tratta di una semplice traduzione da un modello ad un altro perché, prima di passare allo schema logico, lo schema Entità-Relazione va ristrutturato per soddisfare due esigenze: quella di semplificare la traduzione e quella di ottimizzare il progetto. La semplificazione dello schema si rende necessaria perché non tutti i costrutti del modello Entità-Relazione hanno una traduzione naturale nei modelli logici. Per esempio, mentre un’entità può essere facilmente rappresentata da una relazione nel modello relazionale, per le generalizzazioni esistono varie alternative. Inoltre, mentre la progettazione concettuale ha come obiettivo la rappresentazione ac-curata e naturale dei dati di interesse dal punto di vista del significato che hanno nell’applicazione, la progettazione logica costituisce la base per l’effet-tiva realizzazione dell’applicazione e deve tener conto, per quanto possibile, delle sue prestazioni: questa necessità può portare a una ristrutturazione del-lo schema concettuale che renda più efficiente l’esecuzione delle operazioni previste. Pertanto, è necessario prevedere sia un’attività di ristrutturazio-ne, sia un’attività di traduzione (dal modello concettuale a quello logico) [Atzeni 09].
La fase di ristrutturazione dello schema E-R si compone di quattro fasi: 1. Eliminazione delle generalizzazioni
2. Analisi ed eventuale eliminazione delle ridondanze 3. Partizionamento/accorpamento di entità e relazioni
4. Scelta degli identificatori primari Eliminazione delle generalizzazioni
Il modello relazionale non è in grado di rappresentare direttamente le generalizzazioni, diventa quindi necessario scomporre tali costrutti in una combinazione di costrutti più semplici, ovvero un insieme di entità e relazioni. Tale scomposizione può essere eseguita in tre modalità differenti a seconda delle caratteristiche che l’applicazione obiettivo dovrà avere [Atzeni 09].
a) Accorpamento delle figlie della generalizzazione nel genitore. Si ag-giunge al genitore un attributo che indichi il “tipo” dell’individuo (a quale entità figlia eventualmente appartiene) Si aggiungono al padre gli attributi e le associazioni dei figli, che diventano opzionali Questa modalità è sempre applicabile, ma comporta la presenza di attributi e associazioni opzionali e l’aggiunta di vincoli.
b) Accorpamento del genitore della generalizzazione nelle figlie Per l’ere-ditarietà, attributi e associazioni del genitore vanno aggiunti a tutti i figli. Tale scelta è possibile solo se la generalizzazione è totale ed esclusiva.
c) Sostituzione della generalizzazione con relazioni Si aggiungono nuo-ve relazioni che rappresentano la generalizzazione. I figli partecipano obbligatoriamente all’associazione, il genitore opzionalmente.
Lo schema E-R prodotto dalla progettazione concettuale ha al suo interno tre generalizzazioni, tutte complete ed esclusive. Queste generalizzazioni sono
ENTITA’ PADRE ENTITA’ FIGLIE Captazione Fiume Lago Pozzo Sorgente Impianto Captazione Potabilizzatore Pompaggio Accumulo Rete AdduzioneDistribuzione
Tabella 4.3: Generalizzazioni dello schema d’acquedotto.
indicate nella Tabella 4.3. Analizziamo ognuna di esse per capire quale scelta è più adatta a garantire l’efficienza e l’integrità del database.
Dato che tutti i padri sono coinvolti in relazioni con altre entità e i figli possiedono numerosi attributi non comuni tra loro, la scelta più conveniente è la sostituzione delle generalizzazioni con delle relazioni 1 a 1. La scelta garan-tisce la diponibilità di tutte le istanze del padre senza ricorrere a giunzioni con i figli e permette il soddisfacimento dei vincoli di integrità referenzia-le sulreferenzia-le entità padre. È infatti impossibireferenzia-le creare una tabella che riferisca contemporaneamente a più tabelle.
In altre parole, affinché si possano utilizzare vincoli di integrità riferiti a tutte le entità figlie di una generalizzazione dovrò obbligatoriamente mante-nere una tabella che raccolga tutte le istanze dell’entità padre. Tale soluzione porta ad una ridondanza, il codice dell’impianto sarà infatti presente sia nel-l’entità padre che nelle entità figlie. Il mantenimento della ridondanza, anche se causa uno spreco di spazio, si giustifica sia con un più efficiente accesso alle istanze dell’entità padre sia con una piu alta qualità della base di dati
dovuta all’utilizzo massimizzato di vincoli di integrità referenziale. Analisi ed eventuale rimozione delle ridondanze
Una ridondanza in uno schema E-R è una informazione significativa, ma derivabile da altre. Nello schema E-R di partenza è presente una sola ri-dondanza. L’entità sostanze è in relazione sia con l’entità impianti che con l’entità reti e a loro volta queste due entità sono in relazione tra loro. Dato che AIT è interessata sia alle sostanze presenti negli impianti sia a quelle presenti nelle reti, con particolare riguardo alle reti di distribuzione, si opta per il mantenimento di tale ridondanza. Ricavare le sostanze presenti in una rete dalle sostanze presenti negli impianti che ne fanno parte rischierebbe di creare incongruenze e relativa confusione su un argomento di rilevante inte-resse per AIT. Potrebbe infatti accadere che una sostanza presente nell’acqua trattata da un potabilizzatore non rimanga nella rete una volta effettuato il trattamento.
Partizionamento ed accorpamento di entità e associazioni Tali ristrutturazioni si effettuano per rendere più efficienti determinate operazioni, ovvero per diminuire il numero di accessi richiesti [Atzeni 09].
Nel nostro particolare caso non ci troviamo a dover effettuare scelte di questo genere. Essendo partiti da ciò che AiT richiede, effettuare partizio-namenti ed accorpamenti causerebbe ovviamente un lavoro supplementare al momento della creazione del report complessivo richiesto.
Scelta degli identificatori primari
Tale operazione è fondamentale per la traduzione nel modello relazionale. Anche in questo caso sfruttando la documentazione fornitoci da AiT si capisce la convenienza nel mantenere i codici scelti, onde evitare inutili operazioni di
adattamento successive.
Nello schema E-R ristrutturato (Fig. 4.7) prodotto in questa prima fase della progettazione logica , si vedono le scelte fatte per l’eliminazione delle generalizzazioni e per la scelta degli identificatori primari.
Ristrutturato lo schema E-R affinché possa essere rappresentato tramite un modello relazionale, passiamo a tradurre una ad una ogni associazione in modo che venga mantenuta integrità e coerenza nell’informazione. Per comodità, nello spiegare le logiche di traduzione , omettiamo le lunghe liste di attributi anagrafici che AiT richiede per caratterizzare ogni entità e uti-lizziamo pseudo codice per definire chiaramente gli identificatori primari e i vincoli di integrità referenziale.
Partiamo dall’associazione tra impianti e nodi. Essendo un’associazione uno a uno inseriamo nella tabella impianti un attributo che indica il nodo in cui l’impianto è localizzato. Costituiamo su questo attributo aggiunto un vincolo di integrità nei confronti dell’identificatore della tabella nodi.
Impianti: • ids_codice (PK) • numNodo (FK) • altri attributi Nodi: • idNodo (PK) Vincoli integrità referenziale:
• Impianti.numNodo references to Nodi.idNodo
Continuiamo ad analizzare le associazioni sulla tabella Impianti. Le pom-pe sono collegate a impianti con associazione uno a molti. Abbiamo tradot-to questa associazione aggiungendo alla tabella Pompe un attributradot-to che ne indichi l’impianto di appartenenza.
Pompe:
• ids_codice (PK) • idImpianto (FK) • altri attributi
Vincoli di integrità referenziale:
• Pompe.idImpianto references to Impianti.ids_codice
L’associazione tra impianti e sostanze è diversa da quelle appena tradotte. Infatti la cardinalita dell’associazione è molti a molti. Per tradurre in modo corretto questo tipo di associazione si utilizza una tabella aggiuntiva che riferisce sia alla tabella impianti sia alla tabella sostanze.
Rilevazione:
• idSostanza (PK) (FK) • idImpianto (PK) (FK) • altri attributi
• idSostanza (PK) • altri attributi
Vincolo di integrità referenziale:
• Rilevazione.idSostanza references to Sostanze.idSostanza • Rilevazione.idImpianto references to Impianti.ids_codice
La tabella Impianti deve essere collegata anche con tutte le tabelle che ne contengono le sottocategorie. Ogni entità figlia dell’entità Impianti va tradotta inserendo un vincolo di integrità sulla chiave primaria che riferisce all’identificatore della tabella Impianti.
Captazioni: • idCaptazione (PK) (FK) • atri attributi Potabilizzatori: • idPotabilizzatore (PK) (FK) • altri attributi Accumuli: • idAccumulo (PK) (FK) • altri attributi Pompaggi:
• idPompaggio (PK) (FK) • altri attributi
Vincolo di integrità referenziale:
• Captazioni.idCaptazione references to Impianti.ids_codice • Potabilizzatori.idPotabil references to Impianti.ids_codice • Accumuli.idAccumulo references to Impianti.ids_codice • Pompaggi.idPompaggio references to Impianti.ids_codice
Alle associazioni tra l’entita captazioni e le sue entità figlie si applica lo stesso metodo di traduzione.
Fiumi: • idFiume (PK) (FK) • altri attributi Laghi: • idLago (PK) (FK) • altri attributi Pozzi: • idPozzo (PK) (FK) • altri attributi Sorgenti:
• idSorgente (PK) (FK) • altri attributi
Vincolo di integrità referenziale:
• Fiumi.idFiume references to Captazioni.idCaptazione • Laghi.idLago references to Captazioni.idCaptazione • Pozzi.idPozzo references to Captazioni.idCaptazione • Sorgenti.idSorgente references to Captazioni.idCaptazione
Concluse le traduzioni delle associazioni che intercorrono con l’entita Impianti passiamo a tradurre le associazioni rimanenti.
Per tradurre le due associazioni tra i nodi e le tratte inseriamo nella tabella tratte due attributi, idNodoInizio e idNodoFine. Questi attributi servono per identificare l’inizio e la fine di una tratta, e riferiscono entrambi all’attributo idNodo della tabella Nodi.
Tratte:
• ids_codiceTratto (PK) • NUMNODI (FK) • NUMNODF (FK) • altri attributi
Vincolo di integrità referenziale:
• Tratte.NUMNODF references to Nodi.idNodo
Poichè tratte è in associazione uno a molti anche con l’entità reti si deve inserire anche un’altro attributo con il codice identificativo della rete di ap-partenenza. Ovviamente anche su quest’ultimo pende un vincolo di integrità referenziale nei confronti di ids_codice della tabella Reti.
Tratte: • ids_codiceTratto(PK) • NUMNODI (FK) • NUMNODF (FK) • idRete (FK) • altri attributi Reti: • ids_codice (PK) • altri attributi
Vincolo di integrità referenziale:
• Tratte.idRete references to Reti.ids_codice
L’entità Reti come l’entità Impianti ha varie associazioni, una molti a molti con l’entità comuni, una molti a molti con l’entità sostanze e due associazioni uno a uno con le proprie entità figlie, ovvero Distribuzioni e Adduzioni. Inseriamo di seguito la traduzione di tutte queste associazioni.
Distribuzioni: • idDistribuzione (PK )(FK) • altri attributi Adduzioni: • idAdduzione (PK) (FK) • altri attributi RilevazioneInRete: • idRete (PK) (FK) • idSostanza (PK) (FK) • altri attributi ComuniServiti: • idRete (PK) (FK) • idComune (PK) (FK) Comuni: • codiceIstat (PK) • altri attributi
Vincolo di integrità referenziale:
• Distribuzioni.idDistribuzione references to Reti.ids_codice • Adduzioni.idAdduzione references to Reti.ids_codice
• RilevazioneInRete.idRete references to Reti.ids_codice
• RilevazioneInRete.idSostanza references to Sostanze.idSostanza • ComuniServiti.idRete references to Reti.ids_codice
• ComuniServiti.idComune references to Comuni.codiceIstat
Conclusa la traduzione dello shema E-R possiamo produrre lo schema logico della base di dati (Fig. 4.8).
Proseguiamo con la progettazione della base di dati della rete fognaria per rispettare tutte le esigenze di AiT e di conseguenza di Asa in ambito infrastrutturale.
4.2
Progettazione della base di dati per
l’infra-struttura fognaria
Affrontiamo la progettazione di una base di dati relativa all’infrastruttura fo-gnaria, che insieme a quella dell’acquedotto permette di completare il quadro infrastrutturale del servizio idrico integrato offerto da Asa.
4.2.1
Analisi dei requisiti
Asa richiede la creazione di una base di dati in grado di fornire informazioni sulla condizione degli impianti e delle tubazioni che costituiscono la rete fognaria. Tale rete ha il compito di smaltire, depurare e riciclare le acque bianche e nere provenienti dal tessuto urbano e dalle utenze. Con acque bianche si intendono :
• Le acque meteoriche di dilavamento provenienti da tutte le aree aperte impermeabilizzate quali, strade, parcheggi, tetti e cortili.
• Le Acque utilizzate per il lavaggio delle strade
• Le acque di raffreddamento provenienti da attività industriali
Mentre con il termine acque nere, si intendono quelle acque che sfruttate dalle utenze vengono riconosciute come nocive per la salute pubblica o moleste per il pubblico.
La base di dati sulla rete fognaria che ci accingiamo a progettare deve anch’essa svolgere un ruolo di intermediazione tra il sistema informativo esi-stente di Asa (SIT) e gli obblighi di comunicazione imposti dall’autorità. AiT infatti divide il modulo di comunicazione sulle infrastrutture in due macro classi, una relativa alla rete acquedotto e una alla rete fognaria. I due moduli sono strutturati in modo molto simile.
Proprio per questo, il procedimento di progettazione ha numerosi punti in comune con quello svolto nella sezione precedente a parte per la necessità di individuare nuovi tipi di entità e relazioni.
La base di dati deve essere strutturata mantenendo il modello a grafo utilizzato nel sistema informativo territoriale di Asa. Questo perchè, come nel caso dell’acquedotto, ci interessa conoscere il percorso che effettua l’acqua all’interno della rete e a questo scopo non possiamo fare a meno di utilizzare archi e nodi per rappresentarla.
Sapere da quale rete proviene l’acqua trattata da un depuratore è una domanda alla quale possiamo rispondere solo navigando tra i nodi e gli archi del grafo.
Figura 4.9: Struttura caratteristica di una rete fognaria.
La base di dati deve raccogliere le informazioni sulla localizzazione degli impianti e delle reti dal Sit tramite procedure di ETL e, opportunamente integrato, deve rendere disponibile il file XLS richiesto da AiT.
4.2.2
Progettazione concettuale
Analizzati i requisiti che il database deve avere, procediamo con la redazione dello schema E-R. Individuiamo prima di tutto le entità coinvolte. Per aiutare la comprensione nella figura 4.9 viene illustrata la struttura caratteristica di una rete fognaria.
L’entità nodi e l’entità tratte costituiscono il fulcro della base di dati esattamente come all’interno della base di dati dell’acquedotto. Un nodo può contenere un impianto. Tale impianto può essere di quattro tipologie diverse.
• Impianto di sollevamento : nelle condotte fognarie l’acqua scorre per gravità, può tuttavia accadere che non sia possibile ricorrervi, ad esem-pio quando ci troviamo a raccogliere i liquami da un abitazione situata sotto il livello del mare. Per risolvere questa problema si ricorre ap-punto agli Impianti di sollevamento che pompano l’acqua ad un livello sufficiente per essere immessa nella rete.
• Depuratore : è tendenzialmente localizzato nei nodi finali della rete e ha il compito di depurare l’acqua. L’acqua depurata viene reimmes-sa nell’ambiente oppure utilizzata per irrigazione o per applicazioni industriali.
• Scaricatore di piena : utilizzato solo nelle fognature a sistema misto. Nei periodi secchi si limita a raccogliere le acque nere prima del trat-tamento di depurazione. Altrimenti, in caso di pioggia, poiche il suo contenuto “nocivo” viene diluito dalle acque meteoriche, ha il compito di scaricare in ambiente la quantità in eccesso rispetto alla capacità di trattamento del depuratore.
• Condotta marine: condotta posata sotto la superficie del mare. Si trova agli estremi della rete fognaria e permette di scaricare in ambiente, lontano dalla costa, l’acqua depurata.
Tutte queste entità sopraelencate sono da considerarsi entità figlie di un entità padre che chiameremo Impianti.
AiT oltre a voler conoscere lo stato degli impianti è interessata a sapere anche la condizione delle pompe che in essi operano. Consideriamo quindi le pompe come un entità.
Individuiamo ora le entità relative alle reti. Le reti nell’infrastruttura fognaria sono di due tipi :
• Rete di raccolta : responsabile della presa delle acque bianche e nere. Costituisce il primo tratto che le acque reflue attraversano prima di essere trattate da un qualsiasi impianto.
• Collettore : localizzato a valle delle reti di raccolta ha il compito di indirizzare l’acqua nei depuratori affinchè possa essere reimmessa nell’ambiente, o direttamente o attraversando una condotta marina. Ogni tratta della rete appartiene ad una e una sola delle due classi. Con-cludiamo individuando Comuni come ultima entità presente, ci servirà per stabilire quale rete serve un determinato comune. Nella tabella 4.4 possiamo vedere le entità individuate in ambito fognario.
Possiamo procedere con l’individuazione delle relazioni tra le entità ap-pena definite. Manteniamo le stesse relazioni con le medesime cardinalità tra le entita Impianti, Nodi, Tratte e reti ricalcando quanto fatto per la base di dati dell’acquedotto.
Impianti stavolta è entità padre di tre entità figlie, depuratori, solleva-menti e scaricatori di piena. la generalizzazione è totale e esclusiva. Reti, come è illustrato nella Tabella 4.5, è entità padre delle due entità reti di raccolta e collettori.
ENTITA’ DESCRIZIONE
Impianto Impianto dell’infrastruttura fognaria. Sollevamento Impianto di sollevamento dell’acqua. Depuratore Impianto di depurazione dell’acqua. Scaricatore Impianto di scaricatore di piena.
Pompa Gruppo pompa installato in un impianto.
Nodo Nodo del grafo dell’infrastruttura fognaria
Tratta Tratta del grafo dell’infrastruttura fognaria.
Rete Rete dell’infrastruttura fognaria.
Collettore Rete di collettori.
Fognatura Rete fognaria.
Condotta marina Rete di condotta marina.
Comune Comune d’Italia.
Tabella 4.4: Entità dello schema fognario.
ENTITA’ PADRE ENTITA’ FIGLIE Impianto DepuratoreSollevamento
Scaricatore Rete CollettoreFognatura
Condotta marina
ASSOCIAZIONE DESCRIZIONE
Presenza Associa impianto a pompa.
Localizzazione Associa impianto a nodo.
Definisce inizio Associa nodo (di inizio) a tratta. Definisce fine Associa nodo (di fine) a tratta. Appartenenza Associa tratta a rete.
Servizio Associa rete a comune.
Immissione Associa fognatura a scaricatore di piena. Tabella 4.6: Associazioni dello schema fognario.
Tra impianti e pompe esiste un’associazione che chiamiamo presenza con cardinalità uno a molti. Infine anche le reti si collegano all’entità comuni con l’associazione comuni_serviti.
Nella Tabella 4.6 è possibile vedere l’elenco delle associazioni presenti nello schema concettuale.
Mettendo insieme tutte le informazioni sopraelencate possiamo redarre un corretto schema E-R (Fig. 4.10) che costituisce una buona base di partenza per la fase di progettazione logica.
4.2.3
Progettazione logica
Passiamo alla ristrutturazione dello schema E-R.
Poichè non ci sono ne ridondanze ne necessità di accorpare o dividere entità, gli unici due punti da analizzare sono i costrutti delle generalizzazioni presenti su impianti e su reti. Per entrambe le generalizzazioni presenti deci-diamo di mantenere entità padre e entità figlie senza effettuare alcun accor-pamento. Questa scelta oltre a mantenere semanticamente separata la realtà delle cose ci consente di poter stabilire un vincolo di integrità referenziale
riferito all’entità “Impianti”.
Lo stessa strategia risolutiva delle generalizzazioni è stata applicata al-l’entità reti. Gli attributi comuni delle entità collettore, fognatura e condotta marina sono stati accorpati sull’entità padre lasciando separate le entità figlie. Per quanto riguarda la scelta degli identificatori primari scegliamo quelli utilizzati nella documentazione AiT per facilitare la produzione del report informativo richiesto.
Una volta ottenuto lo schema E-R ristrutturato (Fig. 4.11) si opera la traduzione verso il modello relazionale. Traduciamo ogni entità individuata come una tabella a sè e valutiamo le cardinalità di ogni associazione per inserire dove necessario tabelle aggiuntive e vincoli di integrità referenziale. Per comodità, nello spiegare le logiche di traduzione, omettiamo le lunghe liste di attributi anagrafici che AiT richiede per caratterizzare ogni entità.
Valutiamo inizialmente l’associazione tra impianti e nodi. Eseguiamo per questa associazione gli stessi passaggi svolti per la traduzione dell’omonima associazione presente nella base di dati dell’acquedotto. Essendo un’ asso-ciazione uno a uno inseriamo nella tabella impianti un attributo che indica il nodo in cui l’impianto è localizzato. Costituiamo su questo attributo ag-giunto un vincolo di integrità nei confronti dell’identificatore della tabella nodi.
Impianti:
• ids_codice (PK) • numNodo(FK) • altri attributi
Nodi:
• idNodo (PK)
Vincoli di integrità referenziali:
• Impianti.numNodo references to Nodi.idNodo
Continuiamo ad analizzare le associazioni sulla tabella Impianti. Le pom-pe sono collegate a impianti con associazione uno a molti. Abbiamo tradot-to questa associazione aggiungendo alla tabella Pompe un attributradot-to che ne indichi l’impianto di appartenenza.
Pompe:
• ids_codice (PK) • idImpianto (FK) • altri attributi
Vincoli di integrità referenziali:
• Pompe.idImpianto references to Impianti.ids_codice
La tabella Impianti deve essere collegata anche con tutte le tabelle che ne descrivono le sottocategorie. Ogni entità figlia dell’entità Impianti va tradotta inserendo un vincolo di integrità sulla chiave primaria che riferisce all’identificatore della tabella Impianti.
Depuratori
• altri attributi Scaricatori: • idScaricatore (PK)(FK) • altri attributi Sollevamenti: • idSollevamento (PK)(FK) • altri attributi
Vincoli di integrità referenziali:
• Depuratori.idDepuratore references Impianti.ids_codice • Scaricatori.idScaricatore references to Impianti.ids_codice • Sollevamenti.idSollevamento references to Impianti.ids_codice Per tradurre le due associazioni presenti tra i nodi e le tratte inseriamo nella tabella tratte due attributi, idNodoInizio e idNodoFine. Questi at-tributi servono per identificare l’inizio e la fine di una tratta, e riferiranno entrambi all’attributo idNodo della tabella Nodi.
Tratte:
• ids_codiceTratto (PK) • numNodI(FK)
• numNodF(FK) • altri attributi
Vincoli di integrità referenziali:
• Tratte.numNodI references to Nodi.idNodo • Tratte.numNodF references to Nodi.idNodo
Poichè tratte è in relazione uno a molti anche con l’entità reti si deve inserire anche un’altro attributo con il codice identificativo della rete di ap-partenenza. Ovviamente anche su quest’ultimo pende un vincolo di integrità referenziale nei confronti di ids_codice della tabella reti.
Tratte: • ids_codiceTratto (PK) • numNodI(FK) • numNodF(FK) • idRete(FK)) • altri attributi Reti: • ids_codice (PK) • altri attributi
Vincoli di integrità referenziali:
• Tratte.idRete references to Reti.ids_codice
L’entità reti come l’entità impianti ha una associazione molti a molti con l’entità comuni e due associazioni uno a uno con le proprie entità figlie, ovvero
Reti di raccolta e Colettori. Inseriamo di seguito la traduzione di tutte queste associazioni. RetiRaccolta • idFognatura (PK)(FK) • altri attributi Collettori: • idCollettore (PK)(FK) • altri attributi ComuniServiti: • idRete (PK)(FK) • idComune (PK)(FK) Comuni: • codiceIstat (PK) • altri attributi
Vincoli di integrità referenziali:
• RetiRaccolta.idFognatura references to Reti.ids_codice • Collettori.idCollettore references to Reti.ids_codice • ComuniServiti.idRete references to Reti.ids_codice
Abbiamo tradotto lo schema E-R prodotto dalla progettazione concet-tuale in uno schema di base di dati relazionale (Fig. 4.12).
4.3
Realizzazione delle basi di dati operazionali
Conclusa la fase progettuale e ottenuti gli schemi logici delle due basi di dati è stato possibile procedere con l’implementazione vera e propria.
A questo scopo è stato scelto il database manager system open source MySql. Le ragioni di questa scelta sono sostanzialmente due: la licenza gra-tuita del software e la facilità del suo utilizzo. La licenza gragra-tuita permette di usufruire di questo software a chiunque, garantendo la standardizzazione del metodo di risoluzione della nostra problematica. In altre parole, ci permette di realizzare basi di dati di appoggio o di secondo livello a prescindere dal DBMS utilizzato dall’azienda. MySql potrebbe infatti essere utilizzato sia che l’azienda utilizzi sistemi Oracle sia che utilizzi sistemi Microsoft, basta porre attenzione alla creazione di un processo etl valido e coerente. Avrem-mo potuto utilizzare Oracle, database manager system utilizzato da Asa, ma avremmo dato un’accezione troppo specialistica al nostro lavoro.
Seconda ragione per l’adozione di MySql è da ricercarsi nella semplicità del suo utilizzo. Essendo uno dei DBMS più utilizzati al mondo vari team di sviluppatori hanno creato nel tempo una comoda interfaccia ricca di funzioni e strumenti per l’amministrazione delle basi di dati. Tale interfaccia prende il nome di MySqlWorkbench ed è facilmente e gratuitamente scaricabile dal sito di MySql. Questo strumento ci ha garantito la creazione fisica delle basi di dati semplicemente utilizzando un tool grafico. È bastato creare graficamente
uno schema (Fig. 4.12), seguendo gli elementi prodotti dalla progettazione logica, e utilizzare un’apposito comando per generare il codice SQL di cui avevamo bisogno.
Per non appesantire la documentazione del lavoro svolto il codice SQL non è stato incluso. Vale però la pena di dire che è stato generato velocemente e senza difficoltà.
Capitolo 5
PROGETTAZIONE E
REALIZZAZIONE DEI
PROCESSI DI ETL
Iniziamo con il capire cosa significa l’acronimo ETL. Le tre lettere sono le iniziali dei tre termini in lingua inglese Extract, Transform, Load. Con questi tre termini si fa riferimento alle tre funzioni che caratterizzano le procedure ETL, vale a dire: l’estrazione, la trasformazione e il caricamento di dati tra due o più basi di dati differenti, siano essi operazionali o decisionali (Data Mart, Data Warehouse).
Lo scopo principale di un’applicazione ETL è rendere disponibili i da-ti raccolda-ti in azienda, provenienda-ti dalle fonda-ti più disparate, sia a Database transazionali, sia a soggetti incaricati di assumere le decisioni, nella forma e secondo le tempistiche più idonee a supportare il processo decisionale. I software di ETL permettono, infatti, di leggere i dati dalla loro fonte, ripulirli
e formattarli in modo uniforme, per poi caricarli nel database di destinazione per essere utilizzati [Pentaho 14].
Il processo ETL come suggerito dal nome è formato da più fasi.
1. Extract: questo elemento è responsabile dell’estrazione dei dati dalla sorgente. I dati vengono estratti da delle basi di dati più o meno strut-turate, da piattaforme informatiche gestionali, da fogli elettronici o flat file.
2. Transform: I dati estratti vengono elaborati per essere trasformati in dati consolidati e rispondenti alle specifiche dettate dalla struttura della base di dati di destinazione. E’ il più complicato degli elementi ETL. Questa fase consiste, ad esempio, nel:
• Selezionare solo quei dati che sono di interesse per il sistema. • Normalizzare i dati (per esempio eliminando i duplicati). • Tradurre dati codificati.
• Derivare nuovi dati calcolati.
• Eseguire accoppiamenti (join) tra dati recuperati da differenti tabelle.
• Raggruppare i dati.
3. Load: Al termine dell’operazione di trasformazione, i dati appena ela-borati vengono caricati nel database di destinazione.
Definita la struttura di un processo ETL, è poi possibile programmar-ne la tempistica. Tale processo infatti può essere eseguito una tantum, o
ciclicamente rispettando specifici intervalli temporali (mensilmente, settima-nalmente o quotidianamente).
La scelta sullo strumento da utilizzare per la fase realizzativa è ricaduta su Kettle, un applicativo open source della suite Pentaho per il Data Inte-gration. Data la nostra scelta di implementare tutto il sistema utilizzando software a licenza gratuita, questo strumento si adatta perfettamente alle no-stre esigenze. Il programma mette a disposizione un’interfaccia grafica molto curata ed intuitiva con la quale è possibile creare sia job che trasformazioni sui dati. Selezionato il tipo di processo (Job o Trasformation) che vogliamo costruire, si sceglie tra un’ampia gamma di elementi disponibili che costitui-ranno gli step del flusso di dati. Questi elementi effettuano operazioni sui dati senza che si debba scrivere codice. Kettle è uno strumento User-friendly e facilita notevolmente il nostro lavoro.
Nelle sezioni seguenti parleremo dei processi che sono necessari alla riso-luzione del nostro particolare caso. Le fasi di Etl, seppur obbligatorie nel risolvere problematiche di gestione dei flussi di dati, possono differire anche di molto tra loro a seconda dei casi da trattare. Ogni scenario presenta osta-coli diversi e di conseguenza necessità di soluzioni diverse. Non è dunque possibile effettuare una standardizzazione su questa componente, ma il no-stro obiettivo si limita a voler presentare una metodologia di approccio al problema.
5.1
Processo di caricamento delle basi di dati
dell’infrastruttura idrica
Trattiamo prima il passaggio di dati tra il sistema informativo territoria-le(SIT) di Asa e la base di dati sull’infrastruttura dell’acquedotto. Vedremo in seguito che data la natura molto simile delle due basi di dati, i processi rea-lizzati per il caricamento della base di dati sulla fognatura avranno una strut-tura simile. La progettazione e la realizzazione di ogni singola trasformazione saranno affrontate parallelamente per comodità espositiva.
L’obiettivo del processo che andiamo a realizzare è il caricamento all’inter-no della base di dati dei dati disponibili nel sistema informativo di Asa, anche se, come vedremo, alcuni attributi richiesti rimarranno nulli per mancanza di informazione sul sistema di origine.
Ogni tabella, per essere caricata, richiede un processo apposito ed ognuno di questi processi ha un elemento che specifica la fonte dalla quale prelevare il dato, un elemento con cui si indicano le trasformazioni da effettuare ed infine un elemento che indica la destinazione dei dati appena trasformati.
Presentiamo di seguito i tre passaggi da rispettare per concludere un basilare processo ETL di caricamento con Kettle.
1. Definizione dell’elemento di input per selezionare un file o una tabella sorgente. L’elemento di input determina l’inizio del flusso di dati. E’ sufficiente scegliere tra i vari elementi di input quello adatto alla nostra fonte e configuralo in modo tale da selezionare solo i dati che vogliamo prelevare.
2. Inserimento durante il flusso degli elementi preposti ad eseguire trasfor-mazioni sui dati, conformandoli alle specifiche della tabella di destina-zione. Per effettuare tali trasformazioni abbiamo a disposizione un set molto ampio di elementi, sia per effettuare conversioni tra tipi diversi di dato sia per effettuare modifiche complesse ed eventuali aggregazioni. 3. Definizione dell’elemento di output con cui specificare la destinazione
del flusso di dati A questo scopo l’elemento che utilizzeremo più spesso sarà l’“Insert-Update”. Permette sia di inserire record non ancora pre-senti sia eventualmente di aggiornare quelli già caricati [Pentaho 14]. Questa funzionalità è molto utile per mantenere la nostra base di dati di destinazione aggiornata in seguito a variazioni avvenute nelle fon-ti e permette di eseguire ciclicamente la trasformazione senza incap-pare in errori di duplicazione della Primary Key. Le nostre tabelle di destinazione sono gestite dal servizio MySql, è pertanto necessario configurare quest’ultimo elemento inserendo i parametri per la connes-sione al Database. La connesconnes-sione è possibile tramite il driver JDBC “mySqlConnector” disponibile sul sito di MySql.
Confrontando lo schema del SIT Asa con quello del nostro database obiet-tivo si comprende che solo alcune informazioni possono essere sfruttate. In-fatti il SIT Asa, essendo stato progettato per uno scopo diverso da quello che noi ora ci proponiamo, contiene dati a livello di dettaglio molto inferiore rispetto a quello richiesto dall’AiT.
Seppur questo limite non permetta di completare il caricamento non ci impedisce di estrarre le informazioni che individuano tutti gli impianti, tutte
