UNIVERSITA DEGLI STUDI DI UDINE

Facolta di AgrariaCORSO DI LAUREA IN SCIENZE E TECNOLOGIE DELL’AMBIENTE E DEL TERRITORIO

Caso di Studio:

ANAGRAFE ZOOTECNICA SUINA∗

1 Descrizione del dominio applicativo

Il sistema di registrazione ed identificazione degli animali, comunementeindicato come anagrafe zootecnica, e un sistema basato sulla registrazio-ne di aziende, consistenza degli allevamenti, movimentazioni degli animali,identificazione dei capi (individuale o per partita), macellazioni con diffe-renze legate a diverse specie di animali da reddito e tipologie d’allevamentopresenti sul territorio.

Il sistema, inizialmente circoscritto alla specie bovina, si e evoluto nel-le funzionalita ed e stato esteso alle altre specie d’interesse zootecnico. E’uno strumento obbligatorio nell’ambito dell’intera Unione Europea ed e in-dispensabile nell’ambito della sanita animale, con risvolti e applicazioni peril controllo delle malattie infettive e delle problematiche relative alle sicu-rezza alimentare. Riveste ruolo primario per la tracciabilita degli animali edei prodotti di origine animale.

La “Banca Dati Nazionale” (BDN) vede attualmente operativi i seguen-ti sistemi anagrafici: Anagrafe bovina, Anagrafe ovina e caprina, Anagrafesuina, Anagrafe avicola, Registrazione circhi. Nella fase attuale attraverso ilsito www.vetinfo.sanita.it, previa autenticazione, e consentito l’accessoall’“Anagrafe Zootecnica Nazionale” (AZN) e al “Sistema Informativo Ma-lattie Animali Nazionale” (SIMAN). Sono invece accessibile pubblicamenteuna serie di funzioni statistiche dell’AZN.

In questo contesto, visto l’attuale impegno dipartimentale nell’ambitodel progetto AGER SEES-PIG, siamo interessati a considerare nel dettagliol’anagrafica suina, per la quale nel seguito si riporta una descrizione piudettagliata delle diverse entita considerate.

∗Analisi e progettazione eseguita per scopi didattici

1

1.1 Registrazione aziende e allevamenti

Il responsabile legale dell’azienda entro venti giorni dall’inizio dell’attivita,richiede l’attribuzione del codice di identificazione aziendale al Servizio ve-terinario competente per territorio. Il Servizio veterinario competente perterritorio attribuisce il codice aziendale alfanumerico recante nell’ordine:

• due lettere per la sigla dello Stato;

• tre cifre per il codice ISTAT del Comune;

• due lettere per la sigla della Provincia;

• numero progressivo assegnato all’azienda su base comunale (tre cifre).

Il Servizio veterinario provvede a registrare nella BDN le seguenti informa-zioni:

• dati aziendali: codice aziendale, indirizzo e coordinate geografiche;

• struttura zootecnica1: la tipologia (allevamento, stalla di sosta, fiera emercato, centro di raccolta, punto di sosta, centro materiale genetico),la denominazione o ragione sociale, nome, indirizzo e codice fiscale delproprietario degli animali o del detentore della struttura zootecnica (sestalla di sosta, centro materiale genetico o centro di raccolta), capacitadella struttura e numero di animali detenuti al momento (suddivisi incinghiali e/o maiali);

• se la struttura e di tipo allevamento, bisogna anche indicare l’orienta-mento produttivo (familiare, da ingrasso, da riproduzione), la tecnicaproduttiva (per gli allevamenti da ingrasso ciclo completo, svezzamen-to, magronaggio o finissaggio; per gli allevamenti da riproduzione ci-clo chiuso o ciclo aperto), la modalita di allevamento (stabulato osemibrado), se si tratta di un sistema multisito e se e di tipo stagionale;

• se la struttura e di tipo stalla, l’ordinamento produttivo dovra esseredistinto fra da macello o da vita.

1.2 Identificazione degli animali

I suini si identificano mediante l’esecuzione di un tatuaggio che riporta ilcodice identificativo dell’azienda di nascita (o dell’azienda di prima desti-nazione per gli animali importati da Paesi terzi e destinati a rimanere sul

1Dal manuale operativo dell’anagrafe bovina (pag. 15), in cui la struttura zootecnica eidentificata in modo uguale, e indicato che, poiche all’interno della stessa azienda possonoesserci piu allevamenti di specie diversa o appartenenti a proprietari diversi, e necessarioche, a fronte dell’identico codice aziendale, si identifichi il singolo allevamento attraversola codifica della specie allevata e gli estremi del codice fiscale.

2

territorio nazionale). L’identificazione deve essere effettuata entro il 70o

giorno dalla nascita e comunque prima di essere spostati dall’azienda nellaquale sono nati. L’identificazione degli animali e a carico del detentore deglianimali.

1.3 Registrazione consistenza

Il detentore degli animali, direttamente o tramite persona delegata, dovraregistrare nella BDN i seguenti dati:

• consistenza totale dell’allevamento rilevata il 31 marzo corrispondentea quanto riportato sul registro di carico e scarico relativamente ai suinipresenti di eta superiore a 70 giorni;

• totale nascite/decessi (da effettuarsi entro il 31 marzo);

• numero di riproduttori quando presenti, specificando il numero diverri, scrofe e scrofette (dal primo intervento fecondativo).

1.4 Registrazione movimenti

Il detentore, per ciascuna partita di suidi movimentata, registra nella BDN,direttamente o tramite persona delegata, i seguenti dati entro 7 giorni dalverificarsi dell’evento:

• numero degli animali movimentati in entrata o uscita;

• codice d’identificazione della struttura di partenza e di quella di desti-nazione, compreso lo stabilimento di macellazione;

• causale della movimentazione;

• data di arrivo o di partenza;

• numero del documento d’accompagnamento/certificato sanitario.

1.5 Registrazione macellazioni

Il responsabile del macello, direttamente o tramite persona delegata, regi-stra in BDN i seguenti dati entro 7 giorni dalla macellazione le seguentiinformazioni:

a) per ciascuna partita di suidi macellati:

• la data dell’avvenuta macellazione;

• il codice dell’allevamento di provenienza della partita;

• il numero di animali macellati distinti per categoria;

3

b) per ciascuna partita di suidi macellati provenienti da altri Stati sonorichieste altresı le seguenti informazioni:

• paese di proenienza;

• estremi del certificato sanitario;

• numero di animali macellati distinti per categoria2;

• la data dell’avvenuta macellazione.

1.6 Riferimenti:

Attuazione della direttiva 2008/71/CE relativa all’identificazione e alla regi-strazione dei suini: http://www.izs.it/bollettino_segn_legislative/

bollettini_2010/dicembre_10/3_Identificazione_%20D.L.vo_%20200.

pdf

Sito del Ministero della Salute - Anagrafe suina: http://www.salute.gov.it/sanitaAnimale/paginaInternaMenuSanitaAnimale.jsp?id=2273

Manuale operativo anagrafe bovina: http://www.regione.veneto.it/NR/rdonlyres/DEE9E18D-B05A-47CB-B6FF-68F125322FDA/0/Manuale_operativo_

anagrafe_bovina.pdf

2Per categoria si intende: verri, scrofe, lattonzoli (sino a 25-30 kg), magroncelli (sino a60 kg), magroni (sino a 90 kg), suini magri (sino a 100-110 kg, da bistecca), suini pesanti(sino 160 kg, da insaccato).

4

2 Progettazione Concettuale

Partendo dal presupposto che i suini/cinghiali vengano gestiti in partitedefinite alla nascita che non variano fino alla macellazione (se non percausa di decesso), lo schema risultante dalla progettazione concettuale erappresentato in Figura 1.

Figura 1: Schema CGG.

Note e assunzioni:

• AZIENDA: tipologia (allevamento, stalla di sosta, fiera e mercato,centro di raccolta, punto di sosta, centro materiale genetico);

5

• ALLEVAMENTO: ordinamento produttivo (familiare, da ingrasso, dariproduzione), modalita di allevamento (stabulato o semibrado);

• ALLEVAMENTO DA INGRASSO: tecnica produttiva (ciclo comple-to, svezzamento, magronaggio o finissaggio);

• ALLEVAMENTO DA RIPRODUZIONE: tecnica produttiva (ciclo chiu-so o ciclo aperto);

• STALLA: orientamento produttivo (da macello o da vita);

• PARTITA: tipoAnimale permette di distinguere fra maiali e cinghiali;

6

3 Progettazione logica

Il primo passo della progettazione concettuale consiste nella ristrutturazionedello schema concettuale che, a partire dallo schema rappresentato in Figura1, ha dato origine allo schema CGG ristrutturato rappresentato in Figura 2.

Figura 2: Schema CGG ristrutturato.

Le diverse fasi della ristrutturazione possono cosı essere sintetizzate:

1. attributo multivalore telefono dell’entita DITTA: si introduce un’en-tita TELEFONO che viene legata all’entita AZIENDA mediante una

7

relazione;

2. attributi composto indirizzo dell’entita AZIENDA, PERSONA, DIT-TA, attributo numerodell’entita STRUTTURA ZOOTECNICA, at-tributo numero di DECESSO e numeroEffettivo e numeroIniziale diPARTITA: l’attributo composto viene eliminato e sono mantenuti isingoli attributi semplici che lo compongono;

3. specializzazione dell’entita PARTITA, MOVIMENTAZIONE e STRUT-TURA ZOOTECNICA: supponendo che in generale le diverse istanzedelle entita specializzate vengono accedute interamente e senza diffe-renze rispetto alle diverse sottoclassi (ad esempio le movimentazionisono tutte registrate in un unico registro senza differenza dalle prove-nienze o dalle destinazioni) si e deciso di ristrutturare tutte le specia-lizzazioni riportando tutte le proprieta al padre eliminando quindi lefiglie.

4. dall’analisi degli attributi derivati (analisi delle ridondanze), per scopiprincipalmente didattici, si e deciso di mantenere gli attributi derivatidell’entita STRUTTURA ZOOTECNICA (per il calcolo dei quali sa-rebbero necessare operazioni relativamente pesanti) ed eliminare quellidell’entita PARTICA (calcolabili direttamente dagli altri attributi del-l’entita stessa o al piu attraverso la relazione REGISTRA sull’entitaDECESSO).

NOTE:

• in STRUTTURA ZOOTECNICA l’attributo orientamentoProdutti-voStalla e deve essere istanziato solo se la tipologia e stalla;

• in STRUTTURA ZOOTECNICA gli attributi modalitaAllevamento,multistito, stagionale, familiare devono essere istanziati solo se la tipo-logia e allevamento; inoltre, se la tipologia e allevamento se l’orienta-mento produttivo e ingrasso deve essere istanziato l’attributo tecnica-ProduttivaIngrasso, se e riproduzione deve essere istanziato l’attributotecnicaProduttivaRiproduzione;

• in MOVIMENTAZIONE deve essere istanziata una relazione da euna a; inoltre, almeno una delle due deve essere con una strutturazootecnica;

• ogni PARTITA deve provenire/nascere da qualche parte e quindi esat-tamente una relazione fra provenienza, nasceE e nasceI deve essereistanziata.

8

Si puo quindi procedere con traduzione vera e propria dello schema CGGristrutturato sul modello logico relazionale esteso per il trattamento dei da-ti spaziali (gli attributi con nome geometria avranno infatti associato undominio di tipo spaziale).

1. Procedendo con ordine, andiamo prima a tradurre tutte le entita:

STATO(sigla, nome, the geom)nome, the geom NOT NULL

PROV INCIA(stato, sigla, nome, codiceISTAT, the geom)nome, codiceISTAT, the geom NOT NULLUNIQUE (stato, codiceISTAT)

COMUNE(stato, provincia, codiceISTAT , nome, the geom)nome, the geom NOT NULL

AZIENDA(stato, provincia, comune, progressivo, via, numero, the geom)via, numero, the geom NOT NULL

PERSONA(codicefiscale, nome, cognome, via, numero)nome, cognome, via, numero NOT NULL

DITTA(codicefiscale, ragionesociale, via, numero)ragionesociale, via, numero NOT NULL

TELEFONO(numero, ditta)

STRUTTURA ZOOTECNICA(stato, provincia, comune, azienda, ditta,capacita, numeromaiali, numerocinghiali, tipologia,modalitaallevamento,multisito, familiare, stagionale, tecnicaproduttivaingrasso,tecnicaprosuttivariproduzione, orientamentoproduttivostalla)

capacita, numeromaiali, numerocinghiali, tipologia NOT NULL

PARTITA(id, tipoAnimale, datanascita, numeroinizialescofe,numeroinizialeverri, datamacellazione)

tipoAnimale, datanascita, numeroinizialescofe, numeroinizialeverri NOTNULL

9

MOV IMENTAZIONE(partita, datapartenza, causale, docaccompagnatorio,oraarrivo)

causale, docaccompagnatorio NOT NULL

DECESSO(partita, data, numeroscrofe, numeroverri)numeroscrofe, numeroverri NOT NULL

2. Quindi, consideriamo le relazioni di tipo uno a molti che apporterannodelle modifiche ad alcune delle precedenti tabelle:

relazione locazione:AZIENDA(stato, provincia, comune, progressivo, via, numero, the geom)via, numero, the geom NOT NULLfk (stato,provincia,comune) references COMUNE(stato,provincia,codiceISTAT)

relazione compostada:STRUTTURA ZOOTECNICA(stato, provincia, comune, azienda, ditta,

capacita, numeromaiali, numerocinghiali, tipologia,modalitaallevamento,multisito, familiare, stagionale, tecnicaproduttivaingrasso,tecnicaprosuttivariproduzione, orientamentoproduttivostalla)

capacita, numeromaiali, numerocinghiali, tipologia NOT NULLfk (stato,provincia,comune,azienda) references AZIENDA

relazione di:STRUTTURA ZOOTECNICA(stato, provincia, comune, azienda, ditta,

capacita, numeromaiali, numerocinghiali, tipologia,modalitaallevamento,multisito, familiare, stagionale, tecnicaproduttivaingrasso,tecnicaprosuttivariproduzione, orientamentoproduttivostalla)

capacita, numeromaiali, numerocinghiali, tipologia NOT NULLfk (stato,provincia,comune,azienda) references AZIENDAfk (ditta) references DITTA(codicefiscale)

relazione residenza:PERSONA(codicefiscale, nome, cognome, via, numero, stato, provincia, comune)nome, cognome, via, numero, stato, provincia, comune NOT NULLfk (stato,provincia,comune) references COMUNE(stato,provincia,codiceISTAT)

relazione sede:DITTA(codicefiscale, ragionesociale, via, numero, stato, provincia, comune)ragionesociale, via, numero, stato, provincia, comune NOT NULL

10

fk (stato,provincia,comune) references COMUNE(stato,provincia,codiceISTAT)

relazioni provenienza, nasceEU, nasceI:PARTITA(id, tipoAnimale, datanascita, numeroinizialescofe,

numeroinizialeverri, datamacellazione, stato, provincia, comune, azienda, ditta)tipoAnimale, datanascita, numeroinizialescofe, numeroinizialeverri, sta-to NOT NULLfk (stato,provincia,comune,azienda,ditta) references STRUTTURA ZOOTECNICAfk (stato,provincia,comune,azienda) references AZIENDAfk (stato) references STATO(sigla)

relazioni daEST, daEU, daIT:MOV IMENTAZIONE(partita, datapartenza, causale, docaccompagnatorio,

oraarrivo, dastato, daprovincia, dacomune, daazienda, daditta)causale, docaccompagnatorio, dastato NOT NULLfk (dastato,daprovincia,dacomune,daazienda,daditta) references STRUT-TURA ZOOTECNICAfk (dastato,daprovincia,dacomune,daazienda) references AZIENDAfk (dastato) references STATO(sigla)

relazioni aEST, aEU, aIT:MOV IMENTAZIONE(partita, datapartenza, causale, docaccompagnatorio,

oraarrivo, dastato, daprovincia, dacomune, daazienda, daditta,astato, aprovincia, acomune, aazienda, aditta)

causale, docaccompagnatorio, dastato, astato NOT NULLfk (dastato,daprovincia,dacomune,daazienda,daditta) references STRUT-TURA ZOOTECNICAfk (dastato,daprovincia,dacomune,daazienda) references AZIENDAfk (dastato) references STATO(sigla)fk (astato,aprovincia,acomune,aazienda,aditta) references STRUTTU-RA ZOOTECNICAfk (astato,aprovincia,acomune,aazienda) references AZIENDAfk (astato) references STATO(sigla)

relazione trasferita:MOV IMENTAZIONE(partita, datapartenza, causale, docaccompagnatorio,

dataarrivo, dastato, daprovincia, dacomune, daazienda, daditta,astato, aprovincia, acomune, aazienda, aditta, partita)

causale, docaccompagnatorio, dastato, astato NOT NULLfk (dastato,daprovincia,dacomune,daazienda,daditta) references STRUT-TURA ZOOTECNICA

11

Figura 3: Schema logico relazionale.

fk (dastato,daprovincia,dacomune,daazienda) references AZIENDAfk (dastato) references STATO(sigla)fk (astato,aprovincia,acomune,aazienda,aditta) references STRUTTU-RA ZOOTECNICAfk (astato,aprovincia,acomune,aazienda) references AZIENDAfk (astato) references STATO(sigla)fk (partita) references PARTITA(id)

relazione registra:DECESSO(partita, data, numeroscrofe, numeroverri, partita)numeroscrofe, numeroverri NOT NULLfk (partita) references PARTITA(id)

aggregazione stato-provincia:PROV INCIA(stato, sigla, nome, codiceISTAT, the geom)nome, codiceISTAT, the geom NOT NULLUNIQUE (stato, codiceISTAT)fk (stato) references STATO(sigla)

aggregazione provincia-comune:COMUNE(stato, provincia, codiceISTAT , nome, the geom)nome, the geom NOT NULLfk (stato,provincia) references PROVINCIA(stato,sigla)

12

relazione ha:TELEFONO(telefono, ditta)fk (ditta) references DITTA(codicefiscale)

3. Infine, introduciamo le tabelle necessarie per la traduzione delle rela-zioni molti a molti:

relazione detentore:DETENTORE(stato, provincia, comune, azienda, ditta, persona)fk (stato,provincia,comune,azienda,ditta) references STRUTTURA ZOOTECNICAfk (persona) references PERSONA(codicefiscale)

Lo schema logico relazionale finale risultera essere quindi quello rappre-sentato in Figura 3.

13

4 Linguaggio SQL

4.1 Creazione della base di dati

CREATE TABLE s t a to (s i g l a char (2 ) PRIMARY KEY

,nome varchar (50) NOT NULL) ;SELECT AddGeometryColumn( ’ ’ , ’ s t a t o ’ , ’ the geom ’ ,4326 , ’MULTIPOLYGON’ ,2 )

;CREATE INDEX i d x g i s t s t a t o ON s t a to USING g i s t ( the geom ) ;

CREATE TABLE prov inc i a (s t a to CHAR(2 ) REFERENCES s ta to ( s i g l a ) ON UPDATE CASCADE

, s i g l a CHAR(2 ),nome VARCHAR(50) NOT NULL, c o d i c e i s t a t CHAR(3 ) NOT NULL,PRIMARY KEY ( s tato , s i g l a ),UNIQUE ( s tato , c o d i c e i s t a t )

) ;SELECT AddGeometryColumn( ’ ’ , ’ p r ov inc i a ’ , ’ the geom ’ ,4326 , ’MULTIPOLYGON

’ ,2 ) ;CREATE INDEX i d x g i s t p r o v i n c i a ON prov inc i a USING g i s t ( the geom ) ;

CREATE TABLE comune (s t a to CHAR(2 )

, p rov in c i a CHAR(2 ), c o d i c e i s t a t CHAR(3 ),nome VARCHAR(50) NOT NULL,PRIMARY KEY ( s tato , prov inc ia , c o d i c e i s t a t ),FOREIGN KEY ( s tato , p rov in c i a ) REFERENCES prov inc i a ( stato , s i g l a )

ON UPDATE CASCADE) ;SELECT AddGeometryColumn( ’ ’ , ’ comune ’ , ’ the geom ’ ,4326 , ’MULTIPOLYGON’

,2 ) ;CREATE INDEX i dx g i s t comune ON comune USING g i s t ( the geom ) ;

CREATE TABLE az ienda (s t a to CHAR(2 )

, p rov in c i a CHAR(2 ), comune CHAR(3 ), p r og r e s s i v o INTEGER, v ia VARCHAR(50) NOT NULL, numero VARCHAR(5 ) NOT NULL,PRIMARY KEY ( s tato , prov inc ia , comune , p r og r e s s i v o ),FOREIGN KEY ( s tato , prov inc ia , comune ) REFERENCES comune ON UPDATE

CASCADE) ;SELECT AddGeometryColumn( ’ ’ , ’ az ienda ’ , ’ the geom ’ ,4326 , ’POINT ’ ,2 ) ;CREATE INDEX i d x g i s t a z i e n d a ON az ienda USING g i s t ( the geom ) ;

CREATE TABLE DITTA (c o d i c e f i s c a l e CHAR(16) PRIMARY KEY

, r a g i o n e s o c i a l e VARCHAR(50) NOT NULL, v ia VARCHAR(50) NOT NULL, numero VARCHAR(5 ) NOT NULL, s t a t o CHAR(2 ) NOT NULL, p r ov in c i a CHAR(2 ) NOT NULL, comune CHAR(3 ) NOT NULL,FOREIGN KEY ( s tato , prov inc ia , comune ) REFERENCES comune ON UPDATE

CASCADE

14

) ;

CREATE TABLE s t r u t t u r a z o o t e c n i c a (s t a to CHAR(2 )

, p rov in c i a CHAR(2 ), comune CHAR(3 ), az ienda INTEGER, d i t t a CHAR(16) REFERENCES d i t t a ON UPDATE CASCADE, c apac i ta INTEGER NOT NULL DEFAULT 0, numeromaiali INTEGER NOT NULL DEFAULT 0, numeroc ingh ia l i INTEGER NOT NULL DEFAULT 0, t i p o l o g i a VARCHAR(20) NOT NULL, modal i taa l l evamento VARCHAR(10), mu l t i s i t o BOOLEAN, f am i l i a r e BOOLEAN, s t a g i o n a l e BOOLEAN, t e cn i c ap r odu t t i v a i n g r a s s o VARCHAR(20), t e cn i c ap r o su t t i v a r i p r odu z i on e VARCHAR(20), o r i en tamentop rodu t t i vo s t a l l a VARCHAR(20),PRIMARY KEY ( s tato , prov inc ia , comune , azienda , d i t t a ),FOREIGN KEY ( s tato , prov inc ia , comune , az ienda ) REFERENCES azienda

ON UPDATE CASCADE) ;ALTER TABLE STRUTTURAZOOTECNICA ADD CONSTRAINT checkTipo log ia

CHECK( t i p o l o g i a=’ al levamento ’ OR t i p o l o g i a=’ s t a l l a d i s o s t a ’ ORt i p o l o g i a=’ f i e r a ’ OR t i p o l o g i a=’ mercato ’ OR t i p o l o g i a=’ cent ro d ir a c c o l t a ’ OR t i p o l o g i a=’ punto d i s o s t a ’ OR t i p o l o g i a=’ cent romate r i a l e g ene t i c o ’ ) ;

ALTER TABLE STRUTTURAZOOTECNICA ADD CONSTRAINTcheckModal i taa l levamento CHECK( modal i taa l l evamento=’ s tabu la to ’ ORmodal i taa l l evamento=’ semibrado ’ ) ;

ALTER TABLE STRUTTURAZOOTECNICA ADD CONSTRAINTcheckTecn i caprodut t iva ingra s so CHECK( t e cn i c ap r odu t t i v a i n g r a s s o=’c i c l o completo ’ OR t e cn i c ap r odu t t i v a i n g r a s s o=’ svezzamento ’ ORt e cn i c ap r odu t t i v a i n g r a s s o=’ magronaggio ’ ORt e cn i c ap r odu t t i v a i n g r a s s o=’ f i n i s s a g g i o ’ ) ;

ALTER TABLE STRUTTURAZOOTECNICA ADD CONSTRAINTcheckTecn i capro su t t i va r ip roduz i one CHECK(t e cn i c ap r o su t t i v a r i p r odu z i on e=’ c i c l o ch iuso ’ ORt e cn i c ap r odu t t i v a i n g r a s s o=’ svezzamento ’ ORt e cn i c ap r odu t t i v a i n g r a s s o=’ magronaggio ’ ORt e cn i c ap r o su t t i v a r i p r odu z i on e=’ c i c l o aperto ’ ) ;

ALTER TABLE STRUTTURAZOOTECNICA ADD CONSTRAINTcheckOr i entamentoprodut t ivos ta l l a CHECK(o r i en tamentop rodu t t i vo s t a l l a=’ da mace l lo ’ ORo r i en tamentop rodu t t i vo s t a l l a=’ svezzamento ’ ORt e cn i c ap r odu t t i v a i n g r a s s o=’ magronaggio ’ ORt e cn i c ap r o su t t i v a r i p r odu z i on e=’ da v i t a ’ ) ;

CREATE TABLE pa r t i t a (id INTEGER PRIMARY KEY

, t ipoan imale VARCHAR(10) NOT NULL, da tanasc i t a DATE NOT NULL, n ume r o i n i z i a l e s c o f e INTEGER NOT NULL DEFAULT 0, nume r o i n i z i a l e v e r r i INTEGER NOT NULL DEFAULT 0, datamace l l az ione DATE, s t a t o CHAR(2 ), p rov in c i a CHAR(2 ), comune CHAR(3 ), az ienda INTEGER, d i t t a CHAR(16)

15

,FOREIGN KEY ( s tato , prov inc ia , comune , azienda , d i t t a ) REFERENCESSTRUTTURAZOOTECNICA ON UPDATE CASCADE

,FOREIGN KEY ( s tato , prov inc ia , comune , az ienda ) REFERENCES AZIENDAON UPDATE CASCADE

,FOREIGN KEY ( s t a t o ) REFERENCES STATO) ;ALTER TABLE pa r t i t a ADD CONSTRAINT checkPart i taTipo CHECK (

t ipoan imale=’ maiale ’ OR t ipoan imale=’ c i n c h i a l e ’ ) ;

CREATE TABLE dece s so (p a r t i t a INTEGER REFERENCES pa r t i t a ( id ) ON UPDATE CASCADE

, data DATE, numeroscrofe INTEGER NOT NULL DEFAULT 0, numeroverr i INTEGER NOT NULL DEFAULT 0,CHECK ( numeroscrofe+numeroverri>0),PRIMARY KEY ( pa r t i t a , data )

) ;

CREATE TABLE movimentazione (p a r t i t a INTEGER

, datapartenza TIMESTAMP, c au sa l e VARCHAR(20), docaccompagnatorio VARCHAR(20), da taa r r i vo TIMESTAMP, dastato CHAR(2 ) NOT NULL, daprov inc ia CHAR(2 ), dacomune CHAR(3 ), daazienda INTEGER, dad i t ta CHAR(16), a s ta to CHAR(2 ) NOT NULL, ap rov inc i a CHAR(2 ), acomune CHAR(3 ), aaz ienda INTEGER, ad i t t a CHAR(16),PRIMARY KEY ( pa r t i t a , datapartenza ),FOREIGN KEY ( dastato , daprovinc ia , dacomune , daazienda , dad i t ta )

REFERENCES s t r u t t u r a z o o t e c n i c a ON UPDATE CASCADE,FOREIGN KEY ( dastato , daprovinc ia , dacomune , daazienda ) REFERENCES

azienda ON UPDATE CASCADE,FOREIGN KEY ( dastato ) REFERENCES s ta to ON UPDATE CASCADE,FOREIGN KEY ( astato , aprov inc ia , acomune , aazienda , ad i t t a ) REFERENCES

s t r u t t u r a z o o t e c n i c a ON UPDATE CASCADE,FOREIGN KEY ( astato , aprov inc ia , acomune , aaz ienda ) REFERENCES

azienda ON UPDATE CASCADE,FOREIGN KEY ( a s ta to ) REFERENCES s ta to ON UPDATE CASCADE,FOREIGN KEY ( p a r t i t a ) REFERENCES pa r t i t a ON UPDATE CASCADE

,CHECK ( dad i t ta IS NOT NULL OR ad i t t a IS NOT NULL)) ;

CREATE TABLE persona (c o d i c e f i s c a l e CHAR(16) PRIMARY KEY

,nome VARCHAR(20) NOT NULL, cognome VARCHAR(20) NOT NULL, v ia VARCHAR(50) NOT NULL, numero VARCHAR(5 ) NOT NULL, s t a t o CHAR(2 ) NOT NULL, p r ov in c i a CHAR(2 ) NOT NULL, comune CHAR(3 ) NOT NULL,FOREIGN KEY ( s tato , prov inc ia , comune ) REFERENCES comune ON UPDATE

CASCADE) ;

16

CREATE TABLE detentore (s t a to CHAR(2 )

, p rov in c i a CHAR(2 ), comune CHAR(3 ), az ienda INTEGER, d i t t a CHAR(16), persona CHAR(16),PRIMARY KEY ( s tato , prov inc ia , comune , azienda , d i t ta , persona ),FOREIGN KEY ( s tato , prov inc ia , comune , azienda , d i t t a ) REFERENCES

STRUTTURAZOOTECNICA ON UPDATE CASCADE,FOREIGN KEY ( persona ) REFERENCES persona ( c o d i c e f i s c a l e ) ON

UPDATE CASCADE) ;

CREATE TABLE t e l e f o n o (d i t t a CHAR(16) REFERENCES d i t t a ( c o d i c e f i s c a l e ) ON UPDATE

CASCADE ON DELETE CASCADE, t e l e f o n o VARCHAR(10)

,PRIMARY KEY ( d i t ta , t e l e f o n o )) ;

Note versione: si ricorda che il precedente codice puo essere eseguitosia su PostGIS 1.5 che 2. Nel secondo caso pero, vista la trasformazionedella tabella geometry columns in una vista, le colonne spaziali posso essereinserite direttamente nella tabella come nel seguente esempio:

CREATE TABLE az ienda (s t a to CHAR(2 )

, p rov in c i a CHAR(2 ), comune CHAR(3 ), p r og r e s s i v o INTEGER, v ia VARCHAR(50) NOT NULL, numero VARCHAR(5 ) NOT NULL

, the geom geometry (POINT,4326),PRIMARY KEY ( s tato , prov inc ia , comune , p r og r e s s i v o ),FOREIGN KEY ( s tato , prov inc ia , comune ) REFERENCES comune ON UPDATE

CASCADE) ;

A scopo di esempio si riporta la creazione di alcuni trigger. Il pri-mo insieme di trigger e necessario per gestire automaticamente il valo-re dell’attributo derivato numeromaiali e numerocinghiali nella tabellaSTRUTTURA ZOOTECNICA:

−− a l l a n a s c i t aCREATE OR REPLACE FUNCTION p a r t i t a i n s e r t ( ) RETURNS t r i g g e r AS$BODY$BEGIN

IF new . d i t t a IS NOT NULL THENIF new . t ipoan imale=’ maiale ’ THEN

UPDATE STRUTTURAZOOTECNICASET numeromaiali = numeromaiali + new . nume r o i n i z i a l e s c o f e + new .

nume r o i n i z i a l e v e r r iWHERE new . s t a to=s ta to AND new . p rov inc i a=prov inc i a AND new . comune

=comune AND new . az ienda=azienda AND new . d i t t a=d i t t a ;ELSE

UPDATE STRUTTURAZOOTECNICASET numeroc ingh ia l i = numeroc ingh ia l i + new . nume r o i n i z i a l e s c o f e +

new . nume r o i n i z i a l e v e r r i

17

WHERE new . s t a to=s ta to AND new . p rov inc i a=prov inc i a AND new . comune=comune AND new . az ienda=azienda AND new . d i t t a=d i t t a ;

END IF ;END IF ;RETURN new ;

END;$BODY$ LANGUAGE ’ p lpg sq l ’ ;−− b i s o g n e r e b b e p r e v e d e r e t r i g g e r anche in caso d i m o d i f i c a e

c a n c e l l a z i o n eCREATE TRIGGER t p a r t i t a i n s e r t

AFTER INSERT ON pa r t i t aFOR EACH ROW EXECUTE PROCEDURE p a r t i t a i n s e r t ( ) ;

−− a l d e c e s s oCREATE OR REPLACE FUNCTION de c e s s o i n s e r t ( ) RETURNS t r i g g e r AS$BODY$DECLARE

vt ipoanimale VARCHAR(10) ;v s ta to CHAR(2 ) ;vprov inc i a CHAR(3 ) ;vcomune CHAR(3 ) ;vazienda INTEGER ;vd i t t a VARCHAR(16) ;

BEGINSELECT t ipoan imale INTO vt ipoanimaleFROM PARTITAWHERE new . pa r t i t a=id ;

−− l a r e g i s t r a z i o n e d e l d e c e s s o d o v r e b b e e s s e r e f a t t a s o l o dac h i ha l a p a r t i t a

SELECT astato , aprov inc ia , acomune , aazienda , ad i t t a INTO vstato ,vprov inc ia , vcomune , vazienda , vd i t t a

FROM MOVIMENTAZIONEWHERE pa r t i t a=new . pa r t i t a AND dataa r r i vo = (SELECT MAX(

da taa r r i vo ) FROM MOVIMENTAZIONE AS M1 WHERE M1. pa r t i t a=new .pa r t i t a ) ;

−− se non c ’ e almeno una movimentazione l a p a r t i t a deve e s s e r enata in una s t r u t t u r a z o o t e c n i c a s u l t e r r i t o r i o i t a l i a n o

IF vs ta to IS NULL THENSELECT P. stato ,P . prov inc ia ,P . comune ,P. azienda ,P. d i t t a INTO vstato ,

vprov inc ia , vcomune , vazienda , vd i t t aFROM PARTITA AS PWHERE new . pa r t i t a=P. id ;END IF ;IF vt ipoan imale=’ maiale ’ THEN

UPDATE STRUTTURAZOOTECNICASET numeromaiali = numeromaiali − new . numeroscrofe − new . numeroverr iWHERE s t a to=vsta to AND prov inc i a=vprov inc i a AND comune=vcomune AND

az ienda=vazienda AND d i t t a=vd i t t a ;ELSE

UPDATE STRUTTURAZOOTECNICASET numeroc ingh ia l i = numeroc ingh ia l i − new . numeroscrofe − new .

numeroverr iWHERE s t a to=vsta to AND prov inc i a=vprov inc i a AND comune=vcomune AND

az ienda=vazienda AND d i t t a=vd i t t a ;END IF ;RETURN new ;

END;$BODY$ LANGUAGE ’ p lpg sq l ’ ;−− b i s o g n e r e b b e p r e v e d e r e t r i g g e r anche in caso d i m o d i f i c a e

c a n c e l l a z i o n eCREATE TRIGGER t d e c e s s o i n s e r t

AFTER INSERT ON dece s so

18

FOR EACH ROW EXECUTE PROCEDURE de c e s s o i n s e r t ( ) ;

−− a l l a movimentazioneCREATE OR REPLACE FUNCTION movimentaz ione inser t ( ) RETURNS t r i g g e r

AS$BODY$DECLARE

vt ipoanimale VARCHAR(10) ;vnume r o i n i z i a l e s c r o f e INTEGER ;v nume r o i n i z i a l e v e r r i INTEGER ;vnumerodece s s i s c ro f e INTEGER ;vnumerodece s s ive r r i INTEGER ;

BEGINSELECT t ipoanimale , nume ro i n i z i a l e s c o f e , n ume r o i n i z i a l e v e r r i INTO

vt ipoanimale , vnumero i n i z i a l e s c r o f e , vnume r o i n i z i a l e v e r r iFROM PARTITAWHERE new . pa r t i t a=id ;

SELECT SUM( numeroscrofe ) ,SUM( numeroverr i ) INTOvnumerodeces s i s c ro f e , vnumerodece s s i ve r r i

FROM DECESSOWHERE new . pa r t i t a=pa r t i t a ;IF vnumerodece s s i s c ro f e IS NULL THEN vnumerodece s s i s c ro f e =0; END

IF ;IF vnumerodece s s ive r r i IS NULL THEN vnumerodece s s ive r r i =0; END

IF ;IF vt ipoan imale=’ maiale ’ THEN

UPDATE STRUTTURAZOOTECNICASET numeromaiali = numeromaiali + vnume r o i n i z i a l e s c r o f e +

vnume r o i n i z i a l e v e r r i − vnumerodece s s i s c ro f e −vnumerodece s s ive r r i

WHERE s t a to=new . a s ta to AND prov inc i a=new . aprov inc i a AND comune=new . acomune AND az ienda=new . aaz ienda AND d i t t a=new . ad i t t a ;

UPDATE STRUTTURAZOOTECNICASET numeromaiali = numeromaiali − vnume r o i n i z i a l e s c r o f e −

vnume r o i n i z i a l e v e r r i + vnumerodece s s i s c ro f e +vnumerodece s s ive r r i

WHERE s t a to=new . dastato AND prov inc i a=new . daprov inc ia AND comune=new . dacomune AND az ienda=new . daazienda AND d i t t a=new . ad i t t a ;

ELSEUPDATE STRUTTURAZOOTECNICASET numeroc ingh ia l i = numeroc ingh ia l i + vnume r o i n i z i a l e s c r o f e +

vnume r o i n i z i a l e v e r r i − vnumerodece s s i s c ro f e −vnumerodece s s ive r r i

WHERE s t a to=new . a s ta to AND prov inc i a=new . aprov inc i a AND comune=new . acomune AND az ienda=new . aaz ienda AND d i t t a=new . ad i t t a ;

UPDATE STRUTTURAZOOTECNICASET numeroc ingh ia l i = numeroc inghia le − vnume r o i n i z i a l e s c r o f e −

vnume r o i n i z i a l e v e r r i + vnumerodece s s i s c ro f e +vnumerodece s s ive r r i

WHERE s t a to=new . dastato AND prov inc i a=new . daprov inc ia AND comune=new . dacomune AND az ienda=new . daazienda AND d i t t a=new . dad i t ta ;

END IF ;RETURN new ;

END;$BODY$ LANGUAGE ’ p lpg sq l ’ ;−− b i s o g n e r e b b e p r e v e d e r e t r i g g e r anche in caso d i m o d i f i c a e

c a n c e l l a z i o n eCREATE TRIGGER t mov imentaz i one in se r t

AFTER INSERT ON movimentazioneFOR EACH ROW EXECUTE PROCEDURE movimentaz ione inser t ( ) ;

−− a l l a m a c e l l a z i o n e

19

CREATE OR REPLACE FUNCTION par t i t a upda t e ( ) RETURNS t r i g g e r AS$BODY$DECLARE

vnume r o i n i z i a l e s c r o f e INTEGER ;v nume r o i n i z i a l e v e r r i INTEGER ;vnumerodece s s i s c ro f e INTEGER ;vnumerodece s s ive r r i INTEGER ;v s ta to CHAR(2 ) ;vprov inc i a CHAR(3 ) ;vcomune CHAR(3 ) ;vazienda INTEGER ;vd i t t a VARCHAR(16) ;

BEGINIF (new . datamace l l az ione IS NOT NULL AND o ld . datamace l l az ione IS

NULL) THENSELECT SUM( numeroscrofe ) ,SUM( numeroverr i ) INTO

vnumerodeces s i s c ro f e , vnumerodece s s i ve r r iFROM DECESSOWHERE new . id=pa r t i t a ;IF vnumerodece s s i s c ro f e IS NULL THEN vnumerodece s s i s c ro f e =0; END

IF ;IF vnumerodece s s ive r r i IS NULL THEN vnumerodece s s ive r r i =0; END IF ;

−− l a r e g i s t r a z i o n e d e l d e c e s s o d o v r e b b e e s s e r e f a t t a s o l oda c h i ha l a p a r t i t a

SELECT astato , aprov inc ia , acomune , aazienda , ad i t t a INTO vstato ,vprov inc ia , vcomune , vazienda , vd i t t a

FROM MOVIMENTAZIONEWHERE pa r t i t a=new . id AND dataa r r i vo = (SELECT MAX(

da taa r r i vo ) FROM MOVIMENTAZIONE AS M1 WHERE M1. pa r t i t a=new . id ) ;

−− se non c ’ e almeno una movimentazione l a p a r t i t a devee s s e r e nata in una s t r u t t u r a z o o t e c n i c a s u l t e r r i t o r i oi t a l i a n o

IF vs ta to IS NULL THENSELECT P. stato ,P . prov inc ia ,P . comune ,P. azienda ,P. d i t t a INTO

vstato , vprov inc ia , vcomune , vazienda , vd i t t aFROM PARTITA AS PWHERE new . id=P. id ;

END IF ;IF new . t ipoan imale=’ maiale ’ THEN

UPDATE STRUTTURAZOOTECNICASET numeromaiali = numeromaiali − o ld . nume r o i n i z i a l e s c o f e − o ld .

nume r o i n i z i a l e v e r r i + vnumerodece s s i s c ro f e +vnumerodece s s ive r r i

WHERE s t a to=vsta to AND prov inc i a=vprov inc i a AND comune=vcomuneAND az ienda=vazienda AND d i t t a=vd i t t a ;

ELSEUPDATE STRUTTURAZOOTECNICASET numeroc ingh ia l i = numeroc inghia le − o ld . nume r o i n i z i a l e s c o f e

− o ld . nume r o i n i z i a l e v e r r i + vnumerodece s s i s c ro f e +vnumerodece s s ive r r i

WHERE s t a to=vsta to AND prov inc i a=vprov inc i a AND comune=vcomuneAND az ienda=vazienda AND d i t t a=vd i t t a ;

END IF ;END IF ;RETURN new ;

END;$BODY$ LANGUAGE ’ p lpg sq l ’ ;−− b i s o g n e r e b b e p r e v e d e r e t r i g g e r anche in caso d i m o d i f i c a e

c a n c e l l a z i o n eCREATE TRIGGER t p a r t i t a upda t e

AFTER UPDATE ON pa r t i t a

20

FOR EACH ROW EXECUTE PROCEDURE par t i t a upda t e ( ) ;

Vanno introdotti anche alcuni trigger per controllare i vincoli spazia-li definiti attraverso le relazioni. Ad esempio per la relazione topologicalocazione uno dei trigger necessari e il seguente:

CREATE OR REPLACE FUNCTION az i e nd a i n s e r t ( ) RETURNS t r i g g e r AS$BODY$DECLARE

conta INTEGER ;BEGIN

SELECT count (∗ ) INTO contaFROM COMUNEWHERE ST CONTAINS( the geom , new . the geom )

AND new . s t a to=s ta to AND new . p rov inc i a=prov inc i a AND new . comune=c o d i c e i s t a t ;

IF conta=0 THENRAISE EXCEPTION ’ Vincolo s p a z i a l e e r r a t o ( punto non i n c l u s o ne l

comune ) ! ’ ;ELSERETURN new ;

END IF ;END;$BODY$ LANGUAGE ’ p lpg sq l ’ ;CREATE TRIGGER t p a r t i t a upda t e

BEFORE INSERT OR UPDATE ON az iendaFOR EACH ROW EXECUTE PROCEDURE az i e nd a i n s e r t ( ) ;

4.2 Popolamento della base di dati

Procediamo inizialmente tralasciando le componenti spaziali delle tabelleCOMUNE, PROVINCIA, STATO:

−− INSERIMENTO STATOINSERT INTO s t a to ( s i g l a , nome) VALUES ( ’ IT ’ , ’ ITALIA ’ ) , ( ’AU’ , ’AUSTRIA

’ ) , ( ’SV ’ , ’SVIZZERA ’ ) , ( ’US ’ , ’USA ’ ) ;

−− INSERIMENTO PROVINCIAINSERT INTO prov inc i a ( stato , s i g l a , nome , c o d i c e i s t a t ) VALUES ( ’ IT ’ , ’

UD’ , ’UDINE ’ , ’ 030 ’ ) , ( ’ IT ’ , ’PN ’ , ’PORDENONE’ , ’ 093 ’ ) ;

−− INSERIMENTO COMUNEINSERT INTO comune ( stato , prov inc ia , c o d i c e i s t a t , nome) VALUES ( ’ IT ’ , ’

UD’ , ’ 129 ’ , ’UDINE ’ ) , ( ’ IT ’ , ’UD’ , ’ 118 ’ , ’TAVAGNACCO’ ) , ( ’ IT ’ , ’UD’ , ’ 068 ’, ’PAGNACCO’ ) , ( ’ IT ’ , ’PN ’ , ’ 033 ’ , ’PORDENONE’ ) ;

−− INSERIMENTO AZIENDAINSERT INTO az ienda ( stato , prov inc ia , comune , p rog r e s s i vo , via , numero )

VALUES ( ’ IT ’ , ’UD’ , ’ 129 ’ , 1 , ’ Via Nazionale ’ , ’ 10/A ’ ) ;INSERT INTO az ienda ( stato , prov inc ia , comune , p rog r e s s i vo , via , numero )

VALUES ( ’ IT ’ , ’UD’ , ’ 129 ’ , 2 , ’ Via Nazionale ’ , ’ 103 ’ ) ;INSERT INTO az ienda ( stato , prov inc ia , comune , p rog r e s s i vo , via , numero )

VALUES ( ’ IT ’ , ’UD’ , ’ 118 ’ , 1 , ’ Via Udine ’ , ’ 20 ’ ) ;

−− INSERIMENTO DITTAINSERT INTO DITTA ( c o d i c e f i s c a l e , r a g i o n e s o c i a l e , via , numero , s tato ,

prov inc ia , comune ) VALUES ( ’ 0012345678900000 ’ , ’FRIULI SUINI ’ , ’ ViaNazionale ’ , ’ 10/A ’ , ’ IT ’ , ’UD’ , ’ 129 ’ ) ;

21

INSERT INTO DITTA ( c o d i c e f i s c a l e , r a g i o n e s o c i a l e , via , numero , s tato ,prov inc ia , comune ) VALUES ( ’ 0098765432100000 ’ , ’AGRARIA’ , ’ ViaFortunato ’ , ’ 101 ’ , ’ IT ’ , ’UD’ , ’ 118 ’ ) ;

INSERT INTO DITTA ( c o d i c e f i s c a l e , r a g i o n e s o c i a l e , via , numero , s tato ,prov inc ia , comune ) VALUES ( ’ 0033333333300000 ’ , ’CENTRO AGRARIO’ , ’Via R o s s e l l i ’ , ’ 60 ’ , ’ IT ’ , ’UD’ , ’ 118 ’ ) ;

−− INSERIMENTO TELEFONOINSERT INTO TELEFONO ( d i t ta , t e l e f o n o ) VALUES ( ’ 0012345678900000 ’ , ’

0432555555 ’ ) ;INSERT INTO TELEFONO ( d i t ta , t e l e f o n o ) VALUES ( ’ 0012345678900000 ’ , ’

3475555555 ’ ) ;INSERT INTO TELEFONO ( d i t ta , t e l e f o n o ) VALUES ( ’ 0098765432100000 ’ , ’

3473333333 ’ ) ;INSERT INTO TELEFONO ( d i t ta , t e l e f o n o ) VALUES ( ’ 0033333333300000 ’ , ’

3334444444 ’ ) ;

−− INSERIMENTO STRUTTURA ZOOTECNICAINSERT INTO s t r u t t u r a z o o t e c n i c a ( stato , prov inc ia , comune , azienda ,

d i t ta , capac i ta , numeromaiali , numeroc ingh ia l i , t i p o l o g i a ,modal i taal levamento , mu l t i s i t o , f am i l i a r e , s t ag i ona l e ,t e cn i c ap rodu t t i va i ng ra s s o , t e cn i c ap r o su t t i v a r i p r oduz i on e ,o r i en tamentop rodu t t i vo s t a l l a )

VALUES ( ’ IT ’ , ’UD’ , ’ 129 ’ , 1 , ’ 0012345678900000 ’ , 1000 ,0 ,0 , ’ a l levamento ’ , ’s t abu la to ’ , f a l s e , f a l s e , f a l s e , ’ c i c l o completo ’ , nul l , nu l l ) ;

INSERT INTO s t r u t t u r a z o o t e c n i c a ( stato , prov inc ia , comune , azienda ,d i t ta , capac i ta , numeromaiali , numeroc ingh ia l i , t i p o l o g i a ,modal i taal levamento , mu l t i s i t o , f am i l i a r e , s t ag i ona l e ,t e cn i c ap r odu t t i v a i n g r a s s o )

VALUES ( ’ IT ’ , ’UD’ , ’ 129 ’ , 1 , ’ 0098765432100000 ’ , 900 ,0 ,0 , ’ a l levamento ’ , ’s t abu la to ’ , f a l s e , f a l s e , f a l s e , ’ magronaggio ’ ) ;

INSERT INTO s t r u t t u r a z o o t e c n i c a ( stato , prov inc ia , comune , azienda ,d i t ta , capac i ta , numeromaiali , numeroc ingh ia l i , t i p o l o g i a ,modal i taal levamento , mu l t i s i t o , f am i l i a r e , s t ag i ona l e ,t e cn i c ap r odu t t i v a i n g r a s s o )

VALUES ( ’ IT ’ , ’UD’ , ’ 118 ’ , 1 , ’ 0098765432100000 ’ , 500 ,0 ,0 , ’ a l levamento ’ , ’s t abu la to ’ , f a l s e , f a l s e , f a l s e , ’ c i c l o completo ’ ) ;

INSERT INTO s t r u t t u r a z o o t e c n i c a ( stato , prov inc ia , comune , azienda ,d i t ta , capac i ta , t i p o l o g i a )

VALUES ( ’ IT ’ , ’UD’ , ’ 129 ’ , 2 , ’ 0033333333300000 ’ ,5000 , ’ mercato ’ ) ;

−− INSERIMENTO PERSONAINSERT INTO persona ( c o d i c e f i s c a l e , nome , cognome , via , numero , s tato ,

prov inc ia , comune )VALUES ( ’RSSMRA77C60H816M ’ , ’Mario ’ , ’ Ross i ’ , ’ Via Roma ’ , ’ 5 ’ , ’ IT ’ , ’UD’ , ’

129 ’ ) ;INSERT INTO persona ( c o d i c e f i s c a l e , nome , cognome , via , numero , s tato ,

prov inc ia , comune )VALUES ( ’MRAMRC74D70N846X ’ , ’ Marce l lo ’ , ’Mauro ’ , ’ V ia l e Trices imo ’ , ’ 15/C

’ , ’ IT ’ , ’UD’ , ’ 118 ’ ) ;INSERT INTO persona ( c o d i c e f i s c a l e , nome , cognome , via , numero , s tato ,

prov inc ia , comune )VALUES ( ’BNCMRA67C65H736L ’ , ’Mauro ’ , ’ Bianchi ’ , ’ Via Roma ’ , ’ 104 ’ , ’ IT ’ , ’

UD’ , ’ 068 ’ ) ;INSERT INTO persona ( c o d i c e f i s c a l e , nome , cognome , via , numero , s tato ,

prov inc ia , comune )VALUES ( ’BNCMRA81Y51H165M ’ , ’ Antonio ’ , ’ Bianchi ’ , ’ Piazza indipendenza ’ ,

’ 1 ’ , ’ IT ’ , ’UD’ , ’ 118 ’ ) ;

−− INSERIMENTO DETENTOREINSERT INTO detentore ( stato , prov inc ia , comune , azienda , d i t ta , persona )VALUES ( ’ IT ’ , ’UD’ , ’ 129 ’ , 1 , ’ 0012345678900000 ’ , ’RSSMRA77C60H816M ’ ) ;

22

INSERT INTO detentore ( stato , prov inc ia , comune , azienda , d i t ta , persona )VALUES ( ’ IT ’ , ’UD’ , ’ 129 ’ , 1 , ’ 0098765432100000 ’ , ’BNCMRA81Y51H165M ’ ) ;INSERT INTO detentore ( stato , prov inc ia , comune , azienda , d i t ta , persona )VALUES ( ’ IT ’ , ’UD’ , ’ 118 ’ , 1 , ’ 0098765432100000 ’ , ’BNCMRA67C65H736L ’ ) ;INSERT INTO detentore ( stato , prov inc ia , comune , azienda , d i t ta , persona )VALUES ( ’ IT ’ , ’UD’ , ’ 118 ’ , 1 , ’ 0098765432100000 ’ , ’BNCMRA81Y51H165M ’ ) ;INSERT INTO detentore ( stato , prov inc ia , comune , azienda , d i t ta , persona )VALUES ( ’ IT ’ , ’UD’ , ’ 129 ’ , 2 , ’ 0033333333300000 ’ , ’MRAMRC74D70N846X ’ ) ;

−− INSERIMENTO PARTITA−− i n s e r i m e n t o a l l a n a s c i t aINSERT INTO PARTITA ( id , t ipoanimale , da tanasc i t a , nume ro i n i z i a l e s c o f e ,

nume ro i n i z i a l e v e r r i , datamace l laz ione , s tato , prov inc ia , comune ,azienda , d i t t a )

VALUES(1 , ’ maia le ’ , ’ 01/11/2012 ’ ,20 ,20 ,NULL, ’ IT ’ , ’UD’ , ’ 129 ’ , 1 , ’0012345678900000 ’ ) ;

−− i n s e r i m e n t o p a r t i t a d a l l ’ e s t e r o ( segue r e l a t i v a movimentazione )INSERT INTO PARTITA ( id , t ipoanimale , da tanasc i t a , nume ro i n i z i a l e s c o f e ,

nume ro i n i z i a l e v e r r i , datamace l laz ione , s tato , prov inc ia , comune ,azienda , d i t t a )

VALUES(2 , ’ maia le ’ , ’ 01/11/2012 ’ ,20 ,20 ,NULL, ’US ’ ,NULL,NULL,NULL,NULL) ;

−− INSERIMENTO DECESSOINSERT INTO DECESSO( par t i t a , data , numeroscrofe , numeroverr i ) VALUES ( ’

1 ’ , ’ 20/11/2012 ’ , 1 , 2 ) ;

−− INSERIMENTO MOVIMENTAZIONE−− esempio b l o c c a t o da v i n c o l o checkINSERT INTO movimentazione ( par t i t a , datapartenza , causa le ,

docaccompagnatorio , dataarr ivo , dastato , a s ta to ) VALUES (2 , ’19/11/2012 ’ , ’ a cqu i s t o ’ , ’DDT.11−19/11/2012 ’ , ’ 20/11/2012 ’ , ’US ’ , ’US ’ );

−− esempio c o r r e t t oINSERT INTO movimentazione ( par t i t a , datapartenza , causa le ,

docaccompagnatorio , dataarr ivo , dastato , daprovinc ia , dacomune ,daazienda , daditta , astato , aprov inc ia , acomune , aazienda , ad i t t a )VALUES (2 , ’ 19/11/2012 ’ , ’ a cqu i s t o ’ , ’DDT.1501−19/11/2012 ’ , ’21/11/2012 ’ , ’US ’ , nul l , nul l , nul l , nul l , ’ IT ’ , ’UD’ , ’ 129 ’ , ’ 1 ’ , ’0012345678900000 ’ ) ;

−− MACELLAZIONE PARTITAUPDATE PARTITASET datamace l l az ione=’ 26/11/2012 ’WHERE id =2;

Per quanto riguarda la componente spaziale, eseguiamo preliminarmentel’importazione dei dati dei comuni italiani da shapefile attraverso la funzioneShape File to PostGIS Importer rappresentata in Figura 4.

Successivamente operiamo modificando le istanze preliminarmente in-serire3. A partire dai dati dei comuni italiani e possibile recuperare lageometria dei comuni precedentemente inseriti:

UPDATE comuneSET the geom = (SELECT C. geom

3Si potrebbe ovviamente fare direttamente degli inserimenti a partire dalla tabellaimportata ma per procedere per passi (prima l’SQL standard e poi l’estensione spaziale)seguiamo questa sequenza di passi.

23

Figura 4: Interfaccia per l’importazione di file shape

FROM comun i geopor ta l enaz iona l e as c j o i n prov inc i aas p

on c . cod prov in=p . c o d i c e i s t a tWHERE p . s i g l a=comune . p rov in c i a AND comune . c o d i c e i s t a t

=c . cod comune ) ;

Sempre a partire dai dati dei comuni italiani e possibile ricostruire legeometrie delle province precedentemente inserite nella tabella PROVINIA edello stato italiano:

UPDATE prov inc i aSET the geom = (SELECT ST MULTI(ST UNION(geom) )

FROM COMUNI geoportalenazionaleWHERE prov inc i a . nome=prov inc i a ) ;

UPDATE s t a toSET the geom = (SELECT ST MULTI(ST UNION(geom) )

FROM COMUNI geoportalenazionale )WHERE s i g l a=’ IT ’ ;

Volendo poi inserire manualmente i punti associati alle aziende si puoprocedere come segue:

UPDATE AZIENDASET the geom=ST PointFromText ( ’POINT(13 .2574 46 .0832) ’ ,4326)WHERE s t a to=’ IT ’ AND prov inc i a=’UD’ AND comune=’ 129 ’ AND p rog r e s s i v o

=’ 1 ’ ;

UPDATE AZIENDA

24

SET the geom=ST PointFromText ( ’POINT(13 .2400 46 .0600) ’ ,4326)WHERE s t a to=’ IT ’ AND prov inc i a=’UD’ AND comune=’ 129 ’ AND p rog r e s s i v o

=’ 2 ’ ;

UPDATE AZIENDASET the geom=ST PointFromText ( ’POINT(13 .2100 46 .1100) ’ ,4326)WHERE s t a to=’ IT ’ AND prov inc i a=’UD’ AND comune=’ 118 ’ AND p rog r e s s i v o

=’ 1 ’ ;

4.3 Interrogazione dei dati

Determinare la tipologia e la capacita di ogni struttura zootecnica:

SELECT s tato , prov inc ia , comune , azienda , d i t ta , t i p o l o g i a , capac i t aFROM STRUTTURAZOOTECNICA;

Determinare tutti i dati associabili direttamente ad ogni struttura zoo-tecnica di tipo allevamento:

SELECT ∗FROM STRUTTURAZOOTECNICAWHERE t i po=’ al levamento ’ ;

Determinare il numero degli animali presenti in ogni struttura zootecnica(maiali + cinghiali) di tipo allevamento:

SELECT s tato , prov inc ia , comune , azienda , d i t ta , ma ia l i+c i n g h i a l i asnumeroAnimali

FROM STRUTTURAZOOTECNICAWHERE t i po=’ al levamento ’ ;

Determinare le distente tipologie di strutture zootecniche esistenti:

SELECT DISTINCTFROM STRUTTURAZOOTECNICA;

Determinare il codice fiscale e la denominazione delle ditte associate aogni struttura zootecnica di tipo allevamento:

SELECT DISTINCT SZ . d i t ta , D. r a g i o n e s o c i a l eFROM STRUTTURAZOOTECNICA as SZ , DITTA as DWHERE SZ . d i t t a=D. c o d i c e f i s c a l e AND SZ . t i p o l o g i a=’ al levamento ’ ;

SELECT DISTINCT SZ . d i t ta , D. r a g i o n e s o c i a l eFROM STRUTTURAZOOTECNICA as SZ JOIN DITTA as D ON SZ . d i t t a=D.

c o d i c e f i s c a l eWHERE SZ . t i p o l o g i a=’ al levamento ’ ;

Determinare il codice fiscale, la denominazione e il comune delle ditteassociate a ogni struttura zootecnica di tipo allevamento:

SELECT DISTINCT SZ . d i t ta , D. r a g i o n e s o c i a l e , C. nome as comuneFROM STRUTTURAZOOTECNICA as SZ , DITTA as D, COMUNE as CWHERE SZ . d i t t a=D. c o d i c e f i s c a l e AND SZ . t i p o l o g i a=’ al levamento ’AND D. s ta to=C. s t a to AND D. prov inc i a=C. p rov inc i a AND D. comune=C.

c o d i c e i s t a t ;

25

SELECT DISTINCT SZ . d i t ta , D. r a g i o n e s o c i a l e , C. nome as comuneFROM STRUTTURAZOOTECNICA as SZ JOIN DITTA as D ON SZ . d i t t a=D.

c o d i c e f i s c a l eJOIN COMUNE as C ON D. s ta to=C. s t a to AND D. prov inc i a=C. p rov inc i a

AND D. comune=C. c o d i c e i s t a tWHERE SZ . t i p o l o g i a=’ al levamento ’ ;

Determinare il codice fiscale delle ditte associate a piu di una strutturazootecnica:

SELECT DISTINCT SZ1 . d i t t aFROM STRUTTURAZOOTECNICA as SZ1 , STRUTTURAZOOTECNICA as SZ2WHERE SZ1 . d i t t a=SZ2 . d i t t a AND (SZ1 . s tato<>SZ2 . s t a t o OR SZ1 . prov inc ia

<>SZ2 . p rov inc i a OR SZ1 . comune<>SZ2 . comune OR SZ1 . azienda<>SZ2 .az ienda ) ;

Determinare la ragione sociale delle ditte associate a piu di una strutturazootecnica:

SELECT DISTINCT D. r a g i o n e s o c i a l eFROM STRUTTURAZOOTECNICA as SZ1 , STRUTTURAZOOTECNICA as SZ2 JOIN

DITTA as D ON SZ2 . d i t t a=D. c o d i c e f i s c a l eWHERE SZ1 . d i t t a=SZ2 . d i t t a AND (SZ1 . s tato<>SZ2 . s t a t o OR SZ1 . prov inc ia

<>SZ2 . p rov inc i a OR SZ1 . comune<>SZ2 . comune OR SZ1 . azienda<>SZ2 .az ienda ) ;

Determinare i nomi che risultano essere anche dei cognomi:

SELECT nomeFROM PERSONAINTERSECTSELECT cognomeFROM PERSONA

Determinare il numero di aziende nel comune di Udine:

SELECT COUNT(∗ ) as numeroAziendeUdineFROM AZIENDA as A JOIN COMUNE as C ON A. s t a to=C. s t a to AND A.

prov inc i a=C. p rov inc i a AND A. comune=C. c o d i c e I s t a tWHERE C. nome=’UDINE ’ ;

Determinare il numero complessivo di maiali per struttura zootecnicaordinando il risultato secondo l’ordine crescente dei codici:

SELECT s tato , prov inc ia , comune , d i t ta , SUM( numeroMaiali ) asnumeroMaial iComplessivo

FROM STRUTTURAZOOTECNICAORDER BY s tato , prov inc ia , comune , d i t t a ;

Determinare il numero complessivo di animali (maiali e cinghiali) perstruttura zootecnica ordinando il risultato secondo l’ordine decrescente delnumero di animali:

SELECT s tato , prov inc ia , comune , d i t ta , SUM( numeroMaiali+numeroc ingh ia l i ) as numeroMaial iComplessivo

FROM STRUTTURAZOOTECNICAORDER BY SUM( numeroMaiali+numeroc ingh ia l i ) DESC ;

Determinare i comuni con almeno 2 aziende:

26

SELECT DISTINCT a1 . s tato , a1 . p rov in c i a , a1 . comuneFROM az ienda as a1 , az ienda as a2WHERE a1 . s t a t o=a2 . s t a to and a1 . p rov in c i a=a2 . p rov in c i aand a1 . comune=a2 . comune and a1 . p rog r e s s i vo<>a2 . p r og r e s s i v o ;

SELECT s tato , prov inc ia , comune , p rog r e s s i vo , COUNT(∗ ) asnumeroaziende

FROM AZIENDAGROUP BY s tato , prov inc ia , comuneHAVING COUNT(∗ )>1;

Determinare per ogni comune il numero di aziende, anche per i comuniche non ne hanno (soluzione con l’uso delle viste):

CREATE VIEW numeroAziendePerComune ASSELECT s tato , prov inc ia , comune , p rog r e s s i vo , COUNT(∗ ) as

numeroaziendeFROM AZIENDAGROUP BY s tato , prov inc ia , comune ;

SELECT nome , numeroaziendeFROM numeroAziendePerComune as v RIGHT OUTER JOIN comune as cON v . s t a t o=c . s t a t o and v . p rov in c i a=c . p rov in c i aand v . comune=c . c o d i c e i s t a t ;

Determinare la ragione sociale della struttura zootecnica con il maggiornumero di maiali (potrebbe essere anche piu di una):

SELECT r a g i o n e s o c i a l eFROM DITTA JOIN STRUTTURAZOOTECNICA ON c o d i c e f i s c a l e=d i t t aWHERE numeromaiali IN (SELECT MAX( numeromaiali ) FROM

STRUTTURAZOOTECNICA) ;

SELECT r a g i o n e s o c i a l eFROM DITTA JOIN STRUTTURAZOOTECNICA as SZ ON c o d i c e f i s c a l e=SZ . d i t t aWHERE NOT EXISTS (SELECT ∗

FROM STRUTTURAZOOTECNICAWHERE numeromaiali>SZ . numeromaiali ) ;

Determinare le strutture zootecniche che attualmente hanno animali natifuori dall’italia ma che nella loro attivita non hanno mai fatto importazioni:

−− s t r u t t u r e che p o s s i e d o n o a t t u a l m e n t e an ima l i non i t a l i a n iSELECT M. astato , M. aprov inc ia , M. acomune , M. aazienda , M. ad i t t aFROM PARTITA as P JOIN MOVIMENTAZIONI as M ON P. id=M. pa r t i t aWHERE P. d i t t a IS NULL AND P. datamace l l az ione IS NULL

AND M. datapartenza IN (SELECT MAX( datapartenza )FROM MOVIMENTAZIONEWHERE M. pa r t i t a=pa r t i t a )

EXCEPT

−− s t r u t t u r e che hanno e f f e t t u a t o i m p o r t a z i o n iSELECT astato , aprov inc ia , acomune , aazienda , ad i t t aFROM MOVIMENTAZIONIWHERE dad i t ta IS NULL

27

4.3.1 Interrogazioni spaziali

Determinare la geometria dei comuni:

SELECT nome , a s t ex t ( the geom )FROM comune ;

Determinare l’area dei diversi comuni:

SELECT nome , s t a r e a ( s t t r an s f o rm ( the geom ,32233) )FROM comune ;

Determinare il numero di punti che descrivono le geometrie dei comuniinseriti e il numero di geometrie per ogni comune:

SELECT nome , s t npo i n t s ( the geom ) , st numgeometr ies ( the geom )FROM comune ;

Determinare i comuni che si trovano ad una distanza inferiore di 10Kmdal comune di Udine, restituendo anche la misura della distanza:

SELECT nome , s t d i s t a n c e ( s t t r an s f o rm (C1 . the geom ,32233) , s t t r an s f o rm(C2 . the geom ,32233) )

FROM comune as C1 , comune as C1WHERE C1 . nome=’UDINE ’ AND C2 . nome<> ’UDINE ’ AND s t d i s t a n c e (

s t t r an s f o rm (C1 . the geom ,32233) , s t t r an s f o rm (C2 . the geom ,32233) )>10000;

Determinare la distanza fra le tutte le coppie di aziende del comune diUdine:

SELECT A1 . prog r e s s i vo , A2 . p rog r e s s i vo , ST DISTANCE(A1 . the geom ,A2 .the geom )

FROM az ienda as A1 , az ienda as A2 , comune as CWHERE A1 . comune=C. c o d i c e i s t a t AND A1 . p rov inc i a=C. p rov inc i a AND A1 .

s t a to=C. s t a to AND C. nome=’UDINE ’ AND A1 . comune=A2 . comune AND A1 .p rov inc i a=A2 . p rov inc i a AND A1 . comune=A2 . comune AND A1 . prog r e s s i vo<A2 . p r og r e s s i v o ;

Determinare le aziende la cui geometria (punto) non e inclusa nel comunea cui sono associate (non dovrebbero essercene):

SELECT A. stato , A. prov inc ia , A. comune , A. p r og r e s s i v oFROM az ienda as A, comune as CWHERE A. s t a to=C. s t a to AND A. prov inc i a=C. p rov inc i a AND A. comune=C.

c o d i c e i s t a t AND NOT(ST CONTAINS(C. the geom ,A. the geom ) ) ;

Determinare l’area complessiva dei comuni della provincia di Udine:

SELECT SUM( s t a r e a ( s t t r an s f o rm (C. the geom ,32233) ) )FROM COMUNE as C JOIN PROVINCIA as P ON C. s ta to=P. s t a to AND C.

prov inc i a=P. s i g l aWHERE P. nome=’UDINE ’ ;

SELECT s t a r e a (ST UNION( s t t r an s f o rm (C. the geom ,32233) ) )FROM COMUNE as C JOIN PROVINCIA as P ON C. s ta to=P. s t a to AND C.

prov inc i a=P. s i g l aWHERE P. nome=’UDINE ’ ;

28

4.3.2 Creazione di una vista spaziale

CREATE OR REPLACE VIEW numeroaziendepercomune ASSELECT az ienda . s tato , az ienda . prov inc ia , az ienda . comune , comune . nome

, count (∗ ) AS numeroaziende , comune . the geomFROM az ienda j o i n comune on az ienda . s t a t o=comune . s t a to AND

az ienda . p rov in c i a=comune . p rov in c i a AND az ienda . comune=comune .c o d i c e i s t a t

GROUP BY az ienda . s tato , az ienda . prov inc ia , az ienda . comune , comune .the geom , comune . nome ;

INSERT INTO geometry columns ( f t a b l e c a t a l o g , f tab l e schema ,f tab le name , f geometry column , coord dimension , s r id , type )VALUES ( ’ ’ , ’ pub l i c ’ , ’ numeroaziendepercomune ’ , ’ the geom ’ , 2 ,4326 , ’MULTIPOLYGON’ ) ;

Note versione: si ricorda che il precedente codice puo essere eseguitosu PostGIs 1.5. Con PostGIS 2, la vista geometry columns viene popolataautomaticamente. Bisogna pero ricordarsi di indicare il tipo geometrico e ilsistema di riferimento nella vista:

CREATE OR REPLACE VIEW numeroaziendepercomune ASSELECT az ienda . s tato , az ienda . prov inc ia , az ienda . comune , comune . nome

, count (∗ ) AS numeroaziende , comune . the geom : : geometry (MULTIPOLYGON,4326) as the geom

FROM az ienda j o i n comune on az ienda . s t a t o=comune . s t a to ANDaz ienda . p rov in c i a=comune . p rov in c i a AND az ienda . comune=comune .c o d i c e i s t a t

GROUP BY az ienda . s tato , az ienda . prov inc ia , az ienda . comune , comune .the geom , comune . nome ;

29

5 Visualizzazione dei dati spaziali mediante siste-mi GIS

Molti sistemi GIS utilizzano come sorgente dei dati basi di dati spaziali epermettono quindi la loro visualizzazione, oltre che la possibilita di effet-tuare tutta una serie di operazioni. Sfruttando il sistema Quantum GIS sipossono creare delle viste analoghe a quelle rappresentate in Figura 5 incui sono stati integrati layer raster (nell’esempio e stato utilizzato WMS delgeoportale nazionale accessibile dall’url http://wms.pcn.minambiente.it/ogc?map=/ms_ogc/WMS_v1.3/raster/de_agostini.map) e vettoriali (cari-camento delle tabelle comune e azienda presenti nella base di dati).

Figura 5: Visualizzazione dei dati spaziali da Quantum GIS.

Sfruttando la vista numeroaziendepercomune precedentemente creatae possibile visualizzare un ulteriore layer come visibile in Figura 6. In essosi puo vedere come in base al numero di aziende presenti all’interno di ognicomune (il risultato della vista e visualizzato nella tabella) varia il colore diriempimento del comune.

30

Figura 6: Visualizzazione di una vista con il numero di aziende per comune.

31