TRACCIA
Il Ministero dell’Ambiente commissiona lo sviluppo di un nuovo Sistema Informativo per censire la fauna e la flora presenti nei vari parchi naturali di ciascuna regione. Le informazioni della fauna da raccogliere riguardano l’ordine di appartenenza (mammiferi, rettili, uccelli, …), la specie (scoiattolo, volpe, orso, vipera, falco, aquila, …), il numero di esemplari adulti e cuccioli (con distinzione maschio, femmina), lo stato di salute di ogni esemplare. La flora viene distinta in alberi (quercia, faggio, acacia, …), arbusti (lavanda, rosmarino, rododendro, …) e piante erbacee (fragola di bosco, margherita, primula, …) ed è necessario catalogarle memorizzandone anche la stagione di fioritura e altre caratteristiche peculiari.
In ciascun parco, un responsabile (guardaparco) raccoglie le informazioni attraverso un software installato sul notebook in dotazione; il programma, dopo avere acquisito i dati dalle maschere di immissione, li memorizza in un database.
Ciascuna regione, dotata del nuovo Sistema Informativo, deve provvedere alla gestione della raccol ta dati dei propri parchi ai fini di produrre e inviare al Ministero dell’Ambiente dei report periodici. Il candidato, formulate le opportune ipotesi aggiuntive, realizzi:
- un’analisi della realtà di riferimento, completa dello schema funzionale dell’architettura propo sta, evidenziandone le principali componenti e la tecnologia utilizzata per la trasmissione delle informazioni;
- uno schema concettuale e uno schema logico del database;
- la definizione delle relazioni e le seguenti interrogazioni espresse in linguaggio SQL:
- visualizzare l’elenco di tutti gli esemplari di fauna, suddivisi per specie, presenti nei vari parchi;
- visualizzare tutti gli esemplari a rischio di estinzione;
- calcolare e visualizzare il numero di nascite di un certo esemplare nell’arco di un anno di monitoraggio;
- calcolare e visualizzare il numero totale di diverse specie di arbusti presenti nei vari parchi della regione;
- visualizzare quante specie diverse di pino sono presenti in ciascun parco;
- calcolare il numero medio dei cuccioli di ciascuna specie presenti in tutti i parchi della re gione;
- visualizzare l’esemplare più anziano di ogni specie presente in un determinato parco.
- La codifica in un linguaggio di programmazione a scelta di un segmento significativo del progetto realizzato.
Durata massima della prova: 8 ore. È consentito soltanto l’uso di manuali tecnici e di calcolatrici non programmabili. Non è consentito lasciare l’Istituto prima che siano trascorse 3 ore dalla dettatura del tema.
Analisi per lo sviluppo della rete.
Per la struttura di raccolta e registrazione dati si devono distinguere tre aree, che comunicano sfruttando la rete internet:
- La raccolta delle informazioni viene effettuata in ciascun parco, attraverso un software installato sul notebook.
Il testo ci dice esplicitamente che i dati raccolti vengono salvati in un database locale, questo porta a poter formulare due ipotesi:
- Notebook offline con database locale , in questo caso i dati acquisiti giornalmente vengono inviati ogni sera alla sede regionale sotto forma di un file di testo contenente le query sql dei dati raccolti, e caricate sul server regionale, oppure usando dei software che permettono la sincronizzazione di database
- Notebook o smartphone con collegamento 4g che invia i dati in tempo reale al database regionale.
In tuti e due casi utilizzeremo un database Mysql e un applicativo Java lato client.
- Ciascuna regione, possiede un sistema Informativo, in cui salvare i dati e periodicamente esporta dei report in formato xml da inviare al ministero dell’ambiente. Qui dobbiamo pensare ad un architettura di rete interna e gestirne gli accessi e la sicurezza.
La comunicazione tra i client e questo server avviene sfruttando il protocollo TCP e non essendo dati sensibili non prevediamo la crittografia di tali dati. I software usati sono Mysql per il database e PHP come linguaggio lato Server.
Nelle sedi regionali prevediamo una rete con indirizzo ip publico e l’uso del NAT per permettere alla rete interna di connettersi a internet. Inoltre abbiamo un firewall in cui saranno Inserite delle ACL list per limitare i tipi di accesso. Gli operatori per poter accedere dovranno fare un login tramite username e password.
- Per il ministero il testo non da indicazione su come i report ricevuti vengono elaborati quindi non prevediamo un database accessibile all’esterno sincronizzato con i database regionali.
Modello E/R
Entità
Oltre all’entità Parco (nome regione sede) abbliamo le entità fondamentali che emergono dalle richieste della traccia sono:
Animale che identifica i singoli animali presenti i quali vengono catalogati in base alla Specie e all’Ordine di appartenenza:
Vegetale che identifica le piante presenti catalogate in base alla Specie

Associazioni
Tra Parco e Animale Trova 1:N
Regole di lettura
In ogni Parco devono trovarsi uno o più animali
Ogni Animale deve trovarsi in un parco
Tra Specie e Animale Appartiene 1:N
Regole di lettura
A ogni Specie può appartenere uno o più animali (considerando che non è detto che tutte le specie memorizzate Siano effettivamente presenti)
Ogni Animale deve Appartenere a una Specie
Tra Ordine Animale Appartiene 1:N
Regole di lettura
A ogni Ordine deve appartenere una o più Speci
Ogni Specie deve appartenere a un ordine
Tra Parco e Pianta Trova N:M
Regole di lettura
In ogni Parco devono trovarsi uno o più Piante
Ogni Pianta deve trovarsi in uno o più parco
Tra Tipologia e Pianta Appartiene 1:N
Regole di lettura
A ogni Tipologia può appartenere una o più pianta
Ogni Pianta deve appartenere a una Tipolgia
Ristrutturazione modello E/R
Il diagramma non presenta gerarchie, ne ridondanze o attributi mulivalore, presenta l’attributo composto indirizzoSede nell’entità parco il quale lo scomponiamo in via civico e città.
Diagramma relazionale
Trasformiamo ogni entità in una relazione e dove è necessario aggiungiamo la chiave esterna dell’associazione uno a molti, l’associazione molti a molti trova tra parco e pianta la trasformiamo in una relazione.
Ordine(ordine(PK))
Speci(specie(PK),rischioEstinzione, adulto,ordine(FK))
Parco(codice(PK),nome, regione, via, civico, città)
Animali(codice(PK), nome,sesso,dataNascita,salute,peso, specie(FK) parco(FK))
Pianta(codice(PK), nome, fioritura, note, tipo(FK))
Tipologia(tipo(PK))
Trova(parco(PK),pianta(PK))
Relazione |
Campo |
Chiave |
Tipo |
Dim. |
Vincoli |
Animali |
codice |
PK |
Intero |
auto incremento |
|
specie |
FK |
Varchar |
50 |
Speci.specie |
|
parco |
FK |
Intero |
Parchi.codice |
||
nome |
Varchar |
50 |
NOTT NULL |
||
dataNascita |
Data |
Data approssimata corrispondente(nel caso di cuccioli) al primo del mese in quale l’animale è stato avvistato |
|||
sesso |
char |
1 |
Sesso (M/F) not null |
||
salute |
ENUM |
|
Descrizione dello stato di salute (Buona, Cattiva, …) |
||
peso |
float |
3 2 |
>0 not null |
||
Ordini |
Ordine |
PK |
Varchar |
50 |
|
Parchi |
codice |
PK |
Intero |
|
auto incremento |
nome |
|
varchar |
50 |
not null | |
regione |
|
varchar |
10 |
Not Null | |
via |
|
varchar |
10 |
not null | |
civico |
|
varchar |
6 |
||
città |
|
varchar |
10 |
not null | |
Trova
|
parco |
pk |
Intero |
|
Parchi.codice |
pianta |
pk |
Intero |
|
Piante.codice | |
Speci |
specie |
PK |
Varchar |
50 |
|
ordine |
FK |
Varchar |
50 |
Ordini.ordine | |
rischioEstinzione |
|
Enum |
|
Nullo,basso,medio,alto,critico | |
etàAdulto |
|
intero |
|
>0 not null | |
Piante |
codice |
pk |
intero |
|
auto incremento |
nome |
varchar |
50 |
not null | ||
fioritura |
Enum |
|
(estate autunno, inverno primavera | ||
note |
Testo |
|
|||
tipo |
FK | varchar |
50 |
Tipologie.tipo | |
Tipologie |
tipo |
PK | varchar |
50 |
Modello fisico
CREATE DATABASE parchi CREATE TABLE parchi( codice INT(8) AUTO_INCREMENT PRIMARY KEY, nome VARCHAR(50) NOT NULL, regione VARCHAR(10) NOT NULL, via VARCHAR(10), civico VARCHAR(6), città VARCHAR (19) ); CREATE TABLE ordini( ordine VARCHAR(50) PRIMARY KEY ); CREATE TABLE speci( specie VARCHAR(50) PRIMARY KEY, rischioEstinzione ENUM ("basso","medio","alto","critico"), ordine VARCHAR(50), etàAdultp int NOT NULL, FOREIGN KEY(ordine) REFERENCES ordini(ordine) ); CREATE TABLE animali( codice INT(8) AUTO_INCREMENT PRIMARY KEY, nome VARCHAR(50) NOT NULL, sesso CHAR(1), salute ENUM ("ottima","buona","cattiva","pessima"), peso DECIMAL(5,2), dataNascita DATE, parco INT, specie VARCHAR(50), FOREIGN KEY(parco) REFERENCES parchi(codice), FOREIGN KEY(specie) REFERENCES speci(specie) ); CREATE TABLE Tipologie( tipo varchar(50) PRIMARY KEY ); CREATE TABLE piante( codice INT(8) AUTO_INCREMENT PRIMARY KEY, nome VARCHAR(50) NOT NULL, fioritura ENUM("estate","autunno","inverno","primavera"), note TEXT, tipo VARCHAR(50), FOREIGN KEY(tipo) REFERENCES Tipologie(tipo) ); CREATE TABLE trova( parco INT, pianta INT, PRIMARY KEY (parco,pianta), FOREIGN KEY(parco) REFERENCES parchi(codice), FOREIGN KEY(pianta) REFERENCES piante(codice) );
Query
Visualizzare l’elenco di tutti gli esemplari di fauna, suddivisi per specie, presenti nei vari parchi
SELECT animali.codice, animali.nome , speci.specie, parchi.nome AS nomeParco, animali.dataNascita, animali.salute, animali.sesso FROM parchi INNER JOIN animali ON parchi.codice=animali.parco NATURAL JOIN speci ORDER BY speci.specie, parchi.nome;
Visualizzare tutti gli esemplari a rischio di estinzione
SELECT * FROM speci NATURAL JOIN animali WHERE speci.rischioEstinzione = "alto" OR speci.rischioEstinzione = "critico"
Calcolare e visualizzare il numero di nascite di un certo esemplare nell’arco di un anno di monitoraggio
SELECT specie, COUNT(*) AS N_Nascite FROM speci NATURAL JOIN animali WHERE YEAR(dataNascita) = '2000' AND specie = 'lupo' GROUP BY Specie;
Calcolare e visualizzare il numero totale di diverse specie di arbusti presenti in tutti i parchi della regione
SELECT piante.nome, COUNT(*) AS TipoArbusti FROM piante JOIN trova on piante.codice=trova.pianta JOIN parchi on trova.parco=parchi.codice WHERE tipo = 'arbusto' and parchi.regione="abruzzo" GROUP BY nome Visualizzare quante specie diverse di pino sono presenti in ciascun parco
SELECT parchi.nome, COUNT(*) AS NSpeciePino FROM ( parchi JOIN trova ON parchi.codice = trova.parco ) JOIN piante ON piante.codice = trova.pianta WHERE piante.tipo = 'Alberi' AND piante.nome LIKE 'Pino%' GROUP BY parchi.nome;
Calcolare il numero medio dei cuccioli di ciascuna specie presenti in tutti i parchi della regione
creiamo una cartella temporanea dove contiamo il numero di cuxxioli e su questa facciamo la media CREATE TABLE cuccioli AS SELECT animali.specie, parchi.nome, COUNT(*) AS Cuccioli FROM parchi JOIN animali ON parchi.codice = animali.parco NATURAL JOIN speci WHERE YEAR(CURRENT_DATE)-YEAR(animali.dataNascita) < speci.etàAdultp GROUP BY animali.specie, parchi.nome; SELECT specie, AVG(Cuccioli) AS MediaCuccioli FROM cuccioli GROUP BY specie; DROP cuccioli
Visualizzare l’esemplare più anziano di ogni specie presente in un determinato parco Ipotesi: codiceparco=1
SELECT MIN(animali.dataNascita) AS "Animale più anziano", speci.specie, year(CURRENT_DATE)-year(animali.dataNascita) AS "anni" FROM animali NATURAL JOIN speci WHERE animali.parco = 1 GROUP BY speci.specie ORDER BY speci.specie
Lascia un commento