Esercizio 1

Si consideri il seguente schema di base di dati:

ATTORI(codAttore, nome, annoNascita, nazionalità) Recita(codAttore (attori), codFilm (film)) Film(codFilm, titolo, annoProduzione, nazionalità, regista, genere)

1. Data la definizione dello schema, rispondere alle seguenti domande motivando le risposte date:

   1. può un film essere diretto da più registi?

      No, l’attributo è uno solo e quindi per ogni film c’è solo un regista

   2. può un attore recitare in film diversi?

      Sì, si tratta di un’associazione molti a molti

   3. in un film possono recitare attori con lo stesso nome?

      Sì, gli attori sono identificati dal loro codice, non dal loro nome

   4. un regista può dirigere film di generi diversi?

      Sì, non c’è nessun vincolo ne associazione che lo vieta

Scrivere i comandi SQL per:

1. Aggiungere alla tabella Attori un vincolo che specifichi che l’attributo anno di nascita non può essere superiore al 2019

   ALTER TABLE Attori ADD CONSTRAINT vincolo1 annoNascita <= 2019
   

2. Creare una vista che contenga il codice ed il nome degli attori di nazionalità tedesca.

   CREATE VIEW vista1 AS 
   SELECT codAttore, nome FROM Attori
   WHERE nazionalità = 'Tedesca';
   

3. Cancellare i film in cui ha recitato l’attore con codice X509

   DELETE FROM Film
   WHERE codFilm IN (
   	SELECT codFilm FROM Recita NATURAL JOIN Film NATURAL JOIN Attori
   	WHERE codAttore = 'X509'
   )
   

Esercizio 2

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

1. Il codice dei film in cui non hanno recitato attori inglesi

   SELECT codFilm 
   FROM Film NATURAL JOIN Recita NATURAL JOIN Attori
   WHERE nazionalità = 'Inglese'
   HAVING COUNT(*) = 0
   

2. Per i film di registi che hanno diretto almeno tre film, il numero di attori che vi hanno recitato

   SELECT COUNT(*) 
   FROM (
   	SELECT codFilm FROM Film
   	GROUP BY regista, codFilm
   	HAVING COUNT(*) >= 3
   )NATURAL JOIN Recita NATURAL JOIN Attori
   GROUP BY codFilm
   

3. Il codice e il nome degli attori che hanno recitato in film sia di genere commedia che horror ma che non hanno mai recitato in un film drammatico

   SELECT codAttore, nome
   FROM Attori NATURAL JOIN Recita NATURAL JOIN Film
   WHERE genere = 'commedia'
   INTERSECT
   SELECT codAttore, nome
   FROM Attori NATURAL JOIN Recita NATURAL JOIN Film
   WHERE genere = 'horror'
   EXCEPT
   SELECT codAttore, nome
   FROM Attori NATURAL JOIN Recita NATURAL JOIN Film
   WHERE genere = 'drammatico'
   

4. Il codice e titolo del film italiano in cui hanno recitato il maggior numero di attori

   SELECT codFilm, titolo
   FROM Film NATURAL JOIN Recita NATURAL JOIN Attori
   WHERE Film.nazionaità = 'Italiana'
   GROUP BY codFilm, titolo
   HAVING COUNT(*) >= ALL(
   	SELECT COUNT(*)
   	FROM Film NATURAL JOIN Recita NATURAL JOIN Attori
   	WHERE Film.nazionalità = 'Italiana'
   	GROUP BY codFilm
   )