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 fau­na 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 di­stinzione 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:
  1. visualizzare l’elenco di tutti gli esemplari di fauna, suddivisi per specie, presenti nei vari parchi;
  2. visualizzare tutti gli esemplari a rischio di estinzione;
  3. calcolare e visualizzare il numero di nascite di un certo esemplare nell’arco di un anno di monitoraggio;
  4. calcolare e visualizzare il numero totale di diverse specie di arbusti presenti nei vari parchi della regione;
  5. visualizzare quante specie diverse di pino sono presenti in ciascun parco;
  6. calcolare il numero medio dei cuccioli di ciascuna specie presenti in tutti i parchi della re­ gione;
  7. 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 pro­getto 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:

1. 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:

1. 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
2. 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.

2. 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.

3. 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

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