LABORATORIO DI BASI DI DATI: ESERCITAZIONE 9

LABORATORIO DI BASI DI DATI: ESERCITAZIONE 9
(07/05/2010)
Con riferimento al database realizzato in PostgreSQL nelle lezioni di laboratorio
precedenti, relativo allo schema relazionale:
• persona (id persona, nome, cognome)
• f ilm (id f ilm, id regista, titolo, genere, anno): dove id registae’ una
chiave esterna che fa riferimento a persona;
• partecipazione (id attore,id f ilm, ruolo): dove id attore ed id f ilm sono
chiavi esterne che fanno riferimento rispettivamente a persona e f ilm;
• cinema (id cinema,nome,indirizzo)
• proiezione (id cinema,id f ilm,giorno): dove id cinema e id f ilm sono
chiavi esterne che fanno riferimento rispettivamente a cinema e f ilm.
Si svolgano i seguenti esercizi:
Esercizio 1
1.a) Definire un trigger per implementare il seguente vincolo: La data di proiezione
di un film deve essere posteriore all’anno di produzione del film.
Soluzione:
CREATE FUNCTION vincola data proiezione() RETURNS TRIGGER AS
$BODY$
DECLARE
a INTEGER;
BEGIN
– Determina l’anno di produzione del film
SELECT anno INTO a FROM film WHERE id film=NEW.id film;
– Testa la validita’ dell’anno di produzione
IF a > EXTRACT(’year’ FROM NEW.giorno) THEN
RAISE EXCEPTION $$ Data proiezione anteriore anno produzione $$
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE ’plpgsql’;
CREATE TRIGGER trigger data proiezione BEFORE INSERT OR UPDATE ON proiezione
FOR EACH ROW EXECUTE PROCEDURE vincola data proiezione();
1.b) Verificare il funzionamento del trigger definito al punto precedente.
1
2
LABORATORIO DI BASI DI DATI: ESERCITAZIONE 9 (07/05/2010)
Esercizio 2
2.a) Definire un trigger per impedire la cancellazione di un film ogniqualvolta
tale film e’ l’unica opera mantenuta nella BD del regista del suddetto film
(i.e. certifica che l’autore del film e’ un regista).
CREATE FUNCTION vincola soppressione film() RETURNS TRIGGER AS
$BODY$
DECLARE
n film INTEGER;
BEGIN
SELECT COUNT(∗) INTO n film FROM film WHERE id regista=OLD.id regista;
IF n film = 1 THEN
RAISE EXCEPTION $$ Soppressione film % causa perdita informazione
relativa al fatto che la persona % e’ un regista$$, OLD.id film, OLD.id regista;
END IF;
RETURN OLD;
END;
$BODY$
LANGUAGE ’plpgsql’;
CREATE TRIGGER trigger soppressione film BEFORE DELETE ON film
FOR EACH ROW EXECUTE PROCEDURE vincola soppressione film();
2.b) Verificare il funzionamento del trigger definito al punto precedente.
Esercizio 3
3.a) Definire un trigger per impedire la cancellazione di un film ogniqualvolta
tale film e’ l’unica opera in cui recita qualche attore della BD (i.e. il film
certifica che tale attore del film e’ appunto un attore).
CREATE FUNCTION vincola soppressione film attori() RETURNS TRIGGER AS
$BODY$
DECLARE
min n film INTEGER;
BEGIN
SELECT MIN(n film) INTO min n film FROM (
SELECT id attore, COUNT(∗) AS n film
FROM partecipazione
WHERE id attore IN ( SELECT DISTINCT id attore
FROM partecipazione WHERE id film=OLD.id film)
GROUP BY id attore);
IF min n film = 1 THEN
RAISE EXCEPTION $$ Soppressione film determina perdita informazione
su attori in DB$$;
END IF;
RETURN OLD;
END;
LABORATORIO DI BASI DI DATI: ESERCITAZIONE 9 (07/05/2010)
3
$BODY$
LANGUAGE ’plpgsql’;
CREATE TRIGGER trigger soppressione film attori BEFORE DELETE ON film
FOR EACH ROW EXECUTE PROCEDURE vincola soppressione film attori();
1.b) Verificare il funzionamento del trigger definito al punto precedente.