Esercizio 1

Si consideri il seguente schema di base di dati:

TESI(CodT, Titolo, Tipo, Descrizione, Durata) LAUREANDI(Matricola, Nome, Cognome, Tel, Email ,CodT, DataI) DOCENTI(Matricola, CorsoLaurea, Nome, Cognome, Ufficio, Email) RELATORE(Studente, Docente)

Si richiede di:

1. Individuare le chiavi esterne presenti nello schema indicando per ognuna di essere tabella referente e riferita

   LAUREANDI.CodT chiave esterna su TESI (riferita)

   RELATORE.Studente chiave esterna su LAUREANDI (riferita)

   RELATORE.Docente chiave esterna su DOCENTI (riferita)

2. Scrivere i comandi SQL per:

   1. Creare la tabella LAUREANDI e tutti i vincoli indicati nello schema, motivando anche la soluzione scelta per gestire l’integrità referenziale

      CREATE TABLE LAUREANDI(
      	Matricola NUMERIC(6) PRIMARY KEY,
      	Nome VARCHAR(20) NOT NULL,
      	Cognome VARCHAR(20) NOT NULL,
      	Tel NUMERIC(10) NOT NULL,
      	Email VARCHAR(30) NOT NULL,
      	CodT NUMERIC(5) REFERENCES TESI(CodT) 
      		ON DELETE RESTRICT
      		ON UPDATE CASCADE
      	DataI DATE NOT NULL
      );
      

   2. Aggiungere alla tabella TESI un vincolo che imponga che la durata non possa essere inferiore a 3.

      ALTER TABLE TESI ADD CONSTRAINT vincoloDurata CHECK(Durata >= 3);
      

   3. Cancellare le tesi iniziate prima del 1/1/2010

      DELETE FROM TESI 
      WHERE CodT IN(
      	SELECT CodT FROM LAUREANDI 
      	WHERE DataI < DATE'1-Gen-2010'
      )
      

Esercizio 2

In riferimento allo schema proposto nell’Esercizio 1, formulare in SQL le interrogazioni per restituire:

1. La matricola ed il cognome dei docenti che hanno avuto almeno un tesista sia nel 2015 che nel 2016 (un docente ha avuto un tesista nell’anno X se almeno uno degli studenti di cui è relatore ha iniziato la tesi nell’anno X)

   SELECT Docente 
   FROM RELATORE JOIN LAUREANDI ON (Studente = Matricola)
   WHERE EXTRACT(YEAR FROM DataI) = 2015
   INTERSECT
   SELECT Docente 
   FROM RELATORE JOIN LAUREANDI ON (Studente = Matricola)
   WHERE EXTRACT(YEAR FROM DataI) = 2016
   

2. La matricola dei docenti di Informatica che hanno avuto il maggior numero di laureandi

   SELECT Matricola FROM DOCENTI WHERE CorsoLaurea = 'Informatica'
   AND Matricola IN (
   	SELECT Docente 
   	FROM RELATORE JOIN LAUREANDI ON (Studente = Matricola)
   	GROUP BY Docente
   	HAVING COUNT(*) >= ALL(
   		SELECT COUNT(*) FROM RELATORE, LAUREANDI, DOCENTI
   		WHERE CorsoLaurea = 'Informatica' 
   			AND DOCENTI.Matricola = Docente 
   			AND Studente = LAUREANDI.Matricola
   		GROUP BY Docente
   		)
   	)
   

3. I docenti che non hanno mai seguito tesi di durata inferiore ai 4 mesi.

   SELECT Matricola FROM DOCENTI 
   MINUS
   SELECT Docente 
   FROM RELATORE JOIN LAUREANDI ON (Studente = Matricola)
   	NATURAL JOIN TESI
   WHERE Durata < 4;
   
   

4. Per tutti i docenti che hanno avuto almeno 5 laureandi, il numero di laureandi che hanno iniziato la tesi tra maggio e settembre 2016.

   SELECT Docente, COUNT(*)
   FROM RELATORE JOIN LAUREANDI ON (Studente = Matricola)
   WHERE DataI BETWEEN DATE'1-Mag-2016' AND DATE'30-Set-2016'
   AND Docente IN(
   	SELECT Docente FROM RELATORE JOIN LAUREANDI ON (Studente = Matricola)
   	GROUP BY Docente
   	HAVING COUNT(*) >= 5
   )GROUP BY Docente