Esercizi basi di dati attive

Esercizi basi di dati attive
Esercizio n.1
Dato lo schema relazionale
IMPIEGATO(Nome, Salario,DipNum)
DIPARTIMENTO (DipNum, MomeManager)
definire le seguenti regole attive in Oracle ed in DB2.
T1. Quando un dipartimento viene cancellato cancella tutti gli impiegati di quel dipartimento.
T2. Reagisce alla cancellazione di un impiegato che è manager in un dipartimento cancellando
quel dipartimento e tutti i suoi impiegati
T3. Ogni qual volta il salario di un impiegato supera il salario del suo manager pone tale salario
uguale al salario del manager.
T4. Ogni volta che i salari vengono modificati, verifica che non vi siano dipartimenti in cui il salario
medio cresce più del 3% e in tal caso annulla la modifica.
T5. Ogni volta che i salari sono modificati, verifica il loro valore medio, e se maggiore di 50000,
cancella tutti gli impiegati il cui salari sono stati modificati e sono maggiori di 80000.
Esercizio n.2
Riferendosi alla base dati dell’esercizio precedente, considerare una popolazione con 8 impiegati:
Glenna, Mary, Tom, Bob, Andrew, Gary, Sandro, Clara in cui:
 Glenna è manager nel dipartimento 1
 Mary è manager nel dipartimento 2 in cui lavorano Tom e Andrew
 Gary è manager del dipartimento 3 in cui lavorano Sandro e Clara
 Bob è manager del dipartimento 4
Descrivere una transazione SQL che cancella l’impiegato Glenna e che modifica alcuni dei salari degli
impiegati, quindi attivando le regole 3-5. Descrivere il comportamento dei trigger dopo queste
modificazioni; descrivere lo stato del database dopo ciascuno statement e esecuzione di regola e alla
fine della transazione.
Esercizio n 3
Descrivere le proprietà di terminazione, confluenza e determinismo delle osservazioni per le regole
dell’esercizio n.1
Esercizio n.4
Dato lo schema relazionale:
DOTTORANDO ( Nome, Disciplina, Relatore)
PROFESSORE( Nome, Disciplina)
CORSO( Titolo, Professore)
ESAMI ( NomeStud, TitoloCorso)
Descrivere i trigger che gestiscono i seguenti vincoli di integrità (business rules)
1. ogni dottorando deve lavorare nella stessa area del suo relatore.
2. ogni dottorando deve aver sostenuto almeno tre corsi nell’area del suo relatore
3. ogni dottorando deve aver sostenuto l’esame del corso di cui è responsabile il suo relatore
SOLUZIONI BASI DI DATI ATTIVE
Soluzione Esercizio n.1
I primi quattro trigger da T1 a T4 hanno la stessa sintassi in ORACLE
ed in DB2
1)
create trigger T1
after delete on DIPARTIMENTO
for each row
when (IMPIEGATO.DipNum=Old.DipNum)
delete from IMPIEGATO where DipNum=Old.DipNum
2)
create trigger T2
after delete on IMPIEGATO
for each row
when ( old.Nome in ( select NomeManager
from DIPARTIMENTO )
begin
delete from IMPIEGATO where Dipnum = old.Dipnum;
delete from DIPARTIMENTO where Dipnum = old.Dipnum;
end
3)
create trigger T3
after update of Salario on IMPIEGATO
for each row
declare x number;
begin
select Salario into x
from IMPIEGATO join DIPARTIMENTO on
Nome=NomeManager
where DIPARTIMENTO.Dipnum=new.Dipnum
if new.Salario > x then
update IMPIEGATO set Salario=x
where Nome=new.Nome
end
create trigger T4
after update of Salario on IMPIEGATO
for each row
declare x number;
declare y number;
declare l number;
begin
select avg(Salario), count(*) into x,l
from IMPIEGATO
where Dipnum=new.Dipnum;
y=((x*l)-new.Salario+old.Salario)/l;
if (x>(y*1.03)) then
update IMPIEGATO set Salario=old.Salario
where Dipnum=new.Dipnum;
end
4)
5) (ORACLE)
create trigger T5
after update of Salario on IMPIEGATO
for each statement
when ((select avg(Salario) from new_table) > 50000)
delete from IMPIEGATO
where Salario>80000
and Nome in ( Select new_table.Nome
from new_table as n join old_table as o
on n.Nome=o.Nome
where n.Salario<>old.Salario )
(DB2)
create trigger T5
after update of Salario on IMPIEGATO
when ((select avg(Salario) from new_table) > 50000)
delete from IMPIEGATO
where Salario>80000
and Nome in ( Select new_table.Nome
from new_table as n join old_table as o
on n.Nome=o.Nome
where n.Salario<>old.Salario )
Soluzione esercizio n. 2
Stato iniziale del database
IMPIEGATO
Nome
Salario
Glenna
50000
Mary
50000
Tom
40000
Bob
50000
Andrew
40000
Gary
50000
Sandro
40000
Clara
50000
Dipnum
1
2
2
4
2
3
3
3
DIPARTIMENTO
DipNum
1
2
3
4
NomeManager
Glenna
Mary
Gary
Bob
Transazioni SQL
1-Delete from IMPIEGATO where Nome=”Glenna”;
2-update IMPIEGATO set Salario=55000 where Nome=”Sandro”
3-update IMPIEGATO set Salario=85000 where Nome= “Mary”
La operazione 1 causa l’attivazione del trigger T2, che cancellerà dalla tabella
DIPARTIMENTO la tupla 1 Glenna).
Il nuovo stato del database
IMPIEGATO
Nome
Salario
Mary
50000
Tom
40000
Bob
50000
Andrew
40000
Gary
50000
Sandro
40000
Clara
50000
Dipnum
2
2
4
2
3
3
3
DIPARTIMENTO
DipNum
2
3
4
NomeManager
Mary
Gary
Bob
La seconda operazione causa l’attivazione del trigger T3 e così la tupla (Sandro,40000,3)
diventa (Sandro, 50000, 3).
Nuovo stato del database
IMPIEGATO
Nome
Salario
Mary
50000
Tom
40000
Bob
50000
Andrew
40000
Gary
50000
Sandro
50000
Clara
50000
(la tabella DIPARTIMENTO non cambia)
Dipnum
2
2
4
2
3
3
3
L’ultima operazione causa l’attivazione dei trigger T4 e T5 . Il trigger T4 è attivato per primo
poiché T5 è un trigget after-statement, mentre T4 è after row. La modificazione prodotta da
T4 viene cancellata e lo stato finale del database non cambia.
Soluzione esercizio n.3
Per discutere le proprietà di terminazione per le regole dell’esercizio n.1 dobbiamo
innanzitutto esaminare il grafo delle attivazioni:
T1
T2
T5
T3
T4
Il grafo è ciclico, ma tuttavia l’insieme di regole garantiscono la terminazione perché T1 e T2
possono attivarsi scambievolmente una sola volta; la seconda attivazione non produce
nessun effetto nel database poiché tutti gli impiegati sono già stati rimossi e pertanto non
attivano altri trigger.
L’insieme di regole non garantiscono la confluenza poiché lo stesso evento può attivare più
di un trigger (per esempio un aumento del salario per un impiegato può attivare i trigger T3,T4
e T5).
Il risultato finale è differente se il trigger T5 è attivato prima o dopo degli altri Trigger ( esso
cancella un impiegato mentre T3 e T4 aggiornano il salario.
Naturalmente l’insieme di regole non garantisce il determinismo delle osservazioni poiché
esso non garantisce la confluenza.
Soluzione esercizio n.4
1)
create trigger T1
after update of Disciplina on DOTTORANDO
for each row
when Disciplina <> select Disciplina
from PROFESSORE
where PROFESSORE.Nome=new.Relatore
then signal SQLSTATE 70005 ( “Wrong Disciplina” )
2)
create trigger T2
after update of Disciplina on DOTTORANDO
for each row
when 3 < ( select count(*)
from Exam join CORSO on TitoloCorso=Titolo
join PROFESSORE on Professore=PROFESSORE.Nome
join DOTTORANDO on NomeStud=DOTTORANDO.Nome
join PROFESSORE as P2 on Relatore=P2.Nome
where PROFESSORE.Disciplina=P2.Disciplina
and DOTTORANDO.Nome=new.Nome )
then signal SQLSTATE 70006 (“Two few Courses”)
3)
create trigger T3
after update of Relatore on DOTTORANDO
for each row
when not exist ( select *
from ESAMI join CORSO on TitoloCorso=Titolo
where NomeStud=new.Nome and
Professore=new.Relatore )
then signal SQLSTATE 70007 (“Exam loss”)