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.