2003 – Vivaio

Con diverse semplificazioni rispetto alla traccia (tipi, sottotipi, fasce di prezzo, …)

SCHEMA E/R

Le associazioni tra PERSONALE e AGRONOMO, OPERAIO e AMMINISTRATIVO (in grigio) sono di tipo Is-A.
Se per interventi esterni si intendono attività pratiche (potatura, manutenzione giardini, …) allora vengono affidati agli operai.

Manca ancora l’associazione tra piante e interventi esterni…

SCHEMA RELAZIONALE

Si tratta di uno schema molto grande: lo affronto per sottoschemi e contemporaneamente scrivo le query.

Le piante

Dato il nome di una pianta, riportare quanti esemplari di quella pianta sono presenti nel vivaio

SELECT Quantità
FROM   PIANTA
WHERE  Nome = [Quanti esemplari di quale pianta?]

Dato il nome di una stagione, visualizzare il nome delle piante che fioriscono in quella stagione

SELECT PIANTA.Nome
FROM   (STAGIONE INNER JOIN CATEGORIA ON STAGIONE.IDstagione   = CATEGORIA.stagioneID)
                 INNER JOIN PIANTA    ON CATEGORIA.IDcategoria = PIANTA.categoriaID
WHERE  STAGIONE.Nome = [Le piante di quale stagione?]

Visualizzare nome, descrizione e quantità di esemplari presenti nel vivaio, della pianta più economica da interno

SELECT PIANTA.Nome, PIANTA.Descrizione, PIANTA.Quantità
FROM   PIANTA
WHERE  (CATEGORIA.Interno = True) AND
       (Prezzo = (SELECT Min(Prezzo)
                  FROM   CATEGORIA INNER JOIN PIANTA ON CATEGORIA.IDcategoria = PIANTA.categoriaID
                  WHERE  CATEGORIA.Interno = True))

Visualizzare nome, descrizione e quantità di esemplari (se presente nel vivaio…) della pianta più economica da interno

SELECT PIANTA.Nome, PIANTA.Descrizione, PIANTA.Quantità
FROM   PIANTA
WHERE  (CATEGORIA.Interno = True) AND (Quantità > 0) AND
       (Prezzo = (SELECT Min(Prezzo)
                  FROM   CATEGORIA INNER JOIN PIANTA ON CATEGORIA.IDcategoria = PIANTA.categoriaID
                  WHERE  CATEGORIA.Interno = True))

Visualizzare nome, descrizione e quantità di esemplari della pianta più economica da interno tra quelle presenti nel vivaio…

SELECT PIANTA.Nome, PIANTA.Descrizione, PIANTA.Quantità
FROM   PIANTA
WHERE  (CATEGORIA.Interno = True) AND
       (Prezzo = (SELECT Min(Prezzo)
                  FROM CATEGORIA INNER JOIN PIANTA ON CATEGORIA.IDcategoria = PIANTA.categoriaID
                  WHERE (CATEGORIA.Interno = True) AND (Quantità > 0)))

Il personale

…

Gli agronomi

Dato il nome di un agronomo, riportare quanti esemplari di piante sono sotto la sua responsabilità

SELECT SUM(Quantità)
FROM   ((PERSONALE INNER JOIN AGRONOMO  ON PERSONALE.IDpersonale = AGRONOMO.IDagronomo)
                   INNER JOIN CATEGORIA ON AGRONOMO.IDagronomo   = CATEGORIA.agronomoID)
                   INNER JOIN PIANTA    ON CATEGORIA.IDcategoria = PIANTA.categoriaID
WHERE  (PERSONALE.Cognome = [Cognome agronomo?]) AND (PERSONALE.Nome = [Nome agronomo?])

Gli interventi esterni

Dato il nome di un intervento esterno, tra quelli previsti dal vivaio, riportare il nome e il telefono dei soggetti che hanno richiesto quell’intervento nel corso di un determinato anno solare

SELECT CLIENTE.Cognome CLIENTE.Nome, CLIENTE.Telefono
FROM   (TIPO INNER JOIN INTERVENTO ON TIPO.IDtipo          = INTERVENTO.tipoID)
             INNER JOIN CLIENTE    ON INTERVENTO.clienteID = CLIENTE.IDcliente
WHERE  (TIPO.Nome = [Tipo di intervento?]) AND ([DATA richiesta] BETWEEN #01/01/2003# AND #31/12/2003#)

Riportare nome degli interventi richiesti non ancora eseguiti con il nome e il telefono del richiedente

SELECT   TIPO.Nome, CLIENTE.Cognome, CLIENTE.Nome, CLIENTE.Telefono
FROM     (TIPO INNER JOIN INTERVENTO ON TIPO.IDtipo          = INTERVENTO.tipoID)
               INNER JOIN CLIENTE    ON INTERVENTO.clienteID = CLIENTE.IDcliente
WHERE    INTERVENTO.[DATA intervento] IS NULL
ORDER BY TIPO.Nome

Schema finale?

Continua…

1. Le categorie di piante primaverili
2. Il prezzo minimo e massimo delle piante con immagine disponibile
3. Le piante autunnali con il prezzo tra i € 10 e i €20
4. Il valore complessivo delle piante presenti
5. La data di assunzione di un certo agronomo, dato il nome e il cognome
6. Il personale di Feltre, con eventuale qualifica
7. Tutte le informazioni disponibili sugli agronomi
8. Quante piante è possibile scegliere per ogni categoria
9. La classifica degli agronomi per numero di categorie
10. Le stagioni con almeno 5 categorie di piante