Esercizio 1

Si consideri il seguente schema di base di dati relativa ad una azienda che effettua revisioni periodiche di caldaie:

1. Per come sono definite le chiavi:
   1. Una caldaia può essere revisionata più volte? Si, anche dallo stesso manutentore, a patto che le revisioni vengano fatte in date distinte
   2. Una revisione può essere effettuata da due manutentori? No, una tupla in REVISIONE (corrispondente ad una singola manutenzione) si riferisce ad un solo manutentore
   3. Una caldaia può essere revisionata due volte nella stessa data con esito diverso? Se le due revisioni sono fatte da due manutentori diversi sì.
   4. Un manutentore può revisionare caldaie di modelli diversi? Sì, l’attributo modello non compare nella tabella REVISIONE
2. Scrivere i comandi SQL per:

   1. Creare la tabella REVISIONE specificando gli opportuni vincoli di integrità e le opzioni per gestire l’integrit referenziale

      CREATE TABLE REVISIONE(
      	CodM VARCHAR(8) REFERENCES MANUTENTORE(CodM)
      		ON UPDATE CASCADE
      		ON DELETE RESTRICT,
      	MatricolaC VARCHAR(8) REFERENCES CALDAIA(Matricola)
      		ON UPDATE CASCADE
      		ON DELETE RESTRICT,
      	Data DATE,
      	Esito CHAR,
      	PRIMARY KEY (CodM, MatricolaC, Data)
      );
      

      se viene modificata la matricola di una caldaia nella tabella CALDAIA o il codice di un manutentore nella tabella MANUTENTORE, tale modifica si propaga alla tabella REVISIONE. Per la clausola ON DELETE vale per entrambe le FK l’opzione di default, ovvero non viene permesso di cancellare le informazioni su un manutentore o su una caldaia se ci sono revisioni associate

   2. Aggiungere alla tabella CALDAIA un attributo Azienda che indica il nome della azienda che produce la caldaia

      ALTER TABLE CALDAIA ADD COLUMN Azienda VARCHAR(20);
      

Esercizio 2

1. Codice, nome e cognome dei manutentori che hanno revisionato soltanto caldaie con alimentazione a metano (attributo Alimentazione)

   SELECT CodM, Nome, Cognome
   FROM MANUTENTORE NATURAL JOIN REFVISIONE NATURAL JOIN CALDAIA
   WHERE Alimentazione = 'Metano' 
   MINUS
   SELECT CodM, Nome, Cognome
   FROM MANUTENTORE NATURAL JOIN REVISIONE NATURAL JOIN CALDAIA
   WHERE Alimentazione <> 'Metano';
   

2. Nome, cognome e numero totale di revisioni eseguite, per i manutentori che hanno eseguito un numero totale di revisioni maggiore di 500

   SELECT Nome, Cognome, COUNT(*)
   FROM MANUTENTORE NATURAL JOIN REVISIONE
   GROUP BY CodM, Nome, Cognome
   HAVING COUNT(*) > 500
   

3. Le caldaie fabbricate tra il 2015 e il 2016 che sono state revisionate sia da manutentori con cognome Gialli che Rossi ma non da manutentori con cognome Bianchi

   SELECT Matricola 
   FROM CALDAIA NATURAL JOIN REVISIONE NATURAL JOIN MANUTENTORE
   WHERE AnnoFabbricazione BETWEEN 2015 AND 2016
   	AND Cognome = 'Gialli'
   INTERSECT
   SELECT Matricola 
   FROM CALDAIA NATURAL JOIN REVISIONE NATURAL JOIN MANUTENTORE
   WHERE AnnoFabbricazione BETWEEN 2015 AND 2016
   	AND Cognome = 'Rossi'
   MINUS
   SELECT Matricola 
   FROM CALDAIA NATURAL JOIN REVISIONE NATURAL JOIN MANUTENTORE
   WHERE AnnoFabbricazione BETWEEN 2015 AND 2016
   	AND Cognome = 'Bianchi'
   

4. La matricola e il modello delle caldaie con alimentazione a gasolio che hanno avuto almeno una revisione negativa tra il 1 gennaio 2014 e il 31 gennaio 2016

   SELECT Matricola, Modello 
   FROM CALDAIA NATURAL JOIN REVISIONE
   WHERE Esito = 'N' AND Data >= '1/1/2014' DATE 
   	AND Data <= '31/1/2016' DATE;