2011 – Parco naturale

Alcune considerazioni

1. Un esemplare di animale viene catalogato come maschio/femmina, cucciolo/adulto, stato di salute, data di nascita… quindi ogni esemplare è registrato singolarmente (?!)
2. Una specie è a rischio estinzione se il numero di esemplari è basso nel singolo parco, nella regione, o sull’intero pianeta?
3. Un esemplare diventa adulto quando raggiunge una certa età o quando la guardia lo classifica come adulto?
4. Un esemplare di animale appartiene a una specie che a sua volta appartiene a un ordine
5. Un esemplare di animale appartiene a un parco
6. Un parco appartiene a una regione e ha un responsabile (guardia)
7. Le piante sono trattate analogamente agli animali (??!)

SCHEMA E/R

SCHEMA RELAZIONALE

Proprietà degli attributi

1. IDxxxx, intero, AUTO_INCREMENT, PRIMARY KEY
2. xxxxID, intero, FOREIGN KEY
3. Nome (tranne GUARDIA), Username, stringa, UNIQUE

QUERY

Le query sono toste

1. Visualizzare l’elenco di tutti gli esemplari di fauna, suddivisi per specie, presenti nei vari parchi.
   Con il codice del parco

   SELECT   SPECIEA.Nome, ANIMALE.*
   FROM     SPECIEA INNER JOIN ANIMALE ON SPECIEA.IDspeciea = ANIMALE.specieaID
   ORDER BY SPECIEA.Nome, ANIMALE.Nome

   Con il nome del parco

   SELECT   SPECIEA.Nome, ANIMALE.*, PARCO.Nome
   FROM     (SPECIEA INNER JOIN ANIMALE ON SPECIEA.IDspeciea = ANIMALE.specieaID)
            INNER JOIN PARCO ON ANIMALE.parcoID = PARCO.IDparco
   ORDER BY SPECIEA.Nome, PARCO.Nome, ANIMALE.Nome

2. Visualizzare tutti gli esemplari a rischio di estinzione.
   La specie di animale a rischio di estinzione (meno di 100 esemplari?)

   SELECT   SPECIEA.Nome, Count(*) AS Numero
   FROM     SPECIEA INNER JOIN ANIMALE ON SPECIEA.IDspeciea = ANIMALE.specieID
   GROUP BY SPECIEA.Nome
   HAVING   Numero < 100
   ORDER BY SPECIEA.Nome

3. Calcolare e visualizzare il numero di nascite di un certo esemplare nell’arco di un anno di monitoraggio.
   Il numero di nascite di una certa specie di animale
   Controllare il manuale per NOW() - INTERVAL 1 YEAR

   SELECT Count(*) AS Numero
   FROM   SPECIEA INNER JOIN ANIMALE ON SPECIEA.IDspeciea = ANIMALE.IDspeciea
   WHERE  (SPECIEA.Nome = 'Nome specie') AND (DataNascita BETWEEN NOW()-INTERVAL 1 YEAR AND NOW())

4. Calcolare e visualizzare il numero totale di diverse specie di arbusti presenti nei vari parchi della regione.
   Quante piante per ogni tipo di arbusto nei parchi di una certa regione?

   SELECT   SPECIEP.Nome, Count(*) AS Numero
   FROM     ORDINEP INNER JOIN SPECIEP ON ORDINEP.IDordinep = SPECIEP.ordinepIP
                    INNER JOIN PIANTA  ON SPECIEP.IDspeciep = PIANTA.speciepID
                    INNER JOIN PARCO   ON PIANTA.parcoID    = PARCO.IDparco
   WHERE    (ORDINEP.Nome = 'Arbusto') AND (P.PARCO.regioneID = 'Codice regione')
   GROUP BY SPECIEP.Nome
   ORDER BY SPECIEP.Nome

   Quante piante per ogni tipo di arbusto per ogni parco di una certa regione?

   SELECT   SPECIEP.Nome, PARCO.Nome, Count(*) AS Numero
   FROM     ORDINEP INNER JOIN SPECIEP ON ORDINEP.IDordinep = SPECIEP.ordinepIP
                    INNER JOIN PIANTA  ON SPECIEP.IDspeciep = PIANTA.speciepID
                    INNER JOIN PARCO   ON PIANTA.parcoID    = PARCO.IDparco
   WHERE    (ORDINEP.Nome = 'Arbusto') AND (P.PARCO.regioneID = 'Codice regione')
   GROUP BY SPECIEP.Nome, PARCO.Nome
   ORDER BY SPECIEP.Nome, PARCO.Nome

5. Visualizzare quante specie diverse di pino sono presenti in ciascun parco.

   SELECT   PARCO.Nome, Count(DISTINCT SPECIEP.Nome) AS Numero
   FROM     SPECIEP INNER JOIN PIANTA ON SPECIEP.IDspeciep = PIANTA.speciepID
                    INNER JOIN PARCO  ON PIANTA.parcoID    = PARCO.IDparco
   WHERE    SPECIEP.Nome LIKE '%pino%'
   GROUP BY PARCO.Nome
   ORDER BY PARCO.Nome

6. Calcolare il numero medio dei cuccioli di ciascuna specie presenti in tutti i parchi della regione.
   Quanti cuccioli di ciascuna specie in ogni parco della regione

   CREATE VIEW CUCCIOLI AS
     SELECT   parcoID, specieaID, Count(*) AS Numero
     FROM     ANIMALE INNER JOIN PARCO ON ANIMALE.parcoID = PARCO.IDparco
     WHERE    (adulto = FALSE) AND (regioneID = 'Codice regione')
     GROUP BY parcoID, specieaID

   Numero medio per specie

   SELECT   SPECIEA.Nome, AVG(Numero)
   FROM     SPECIEA INNER CUCCIOLI ON SPECIEA.IDspeciea = CUCCIOLI.specieaID
   GROUP BY SPECIEA.Nome

7. Visualizzare l’esemplare più anziano di ogni specie presente in un determinato parco.
   La data di nascita minima per ogni specie di un certo parco

   CREATE VIEW ANZIANI AS
     SELECT   specieaID, MIN(DataNascita) AS Data
     FROM     ANIMALE
     WHERE    parcoID = 'Codice parco'
     GROUP BY specieaID

   Le scheda degli esemplari con la data di nascita giusta...

   SELECT ANIMALE.*
   FROM   ANIMALE INNER JOIN ANZIANI ON ANIMALE.specieaID = ANZIANI.specieaID
   WHERE  (ANIMALE.DataNascita = ANZIANI.Data) AND (parcoID = 'Codice parco')