Banco de Dados - 2ª Prova (Resposta)

Prévia do material em texto

OBS: Todas as questões valem 2,0 pontos. 
 
1) Crie um gatilho que ao se devolver um filme, que seu status passe a ser de disponível (D). 
 
create or alter trigger num1 
for Locacao after update 
as 
begin 
 update DVD 
 set Status = 'D' 
 where NumeroDVD = old.NumeroDVD; 
end 
 
2) Crie um procedimento que dado no nome de um filme ele retorne quantas vezes o(s) DVD(s) contendo 
esse filme já foi locado. 
 
create or alter procedure num2 
(pTitulo varchar(30)) 
returns 
(pQtde integer) 
as 
begin 
 select count(Locacao.NumLocacao) 
 from Locacao, DVD, Filme 
 where Filme.IdFilme = DVD.IdFilme 
 and DVD.NumeroDVD = Locacao.NumeroDVD 
 and Filme.Titulo = :pTitulo 
 into :pQtde; 
 
 suspend; 
end 
 
 
3) Crie um gatilho que ao se excluir um filme, que o próprio SGBD faça o gerenciamento da integridade 
referencial. 
 
create or alter trigger num3 
for Filme before delete 
as 
begin 
 delete from Locacao 
 where NumeroDVD in (select NumeroDVD from DVD where IdFilme = old.IdFilme); 
 
 delete from DVD 
 where IdFilme = old.IdFilme; 
 
 delete from Estrela 
 where IdFilme = old.IdFilme; 
end 
 
 
4) Crie um procedimento, que dado o nome de um filme, que ele verifique se há algum filme disponível. 
Caso todos os filmes estejam indisponíveis, que ele retorne a data prevista para devolução do dvd mais 
próxima. 
 
create or alter procedute num4 
(pTitulo varchar(30)) 
returns 
(pData date) 
as 
declare variable vQtde integer; 
begin 
 select count(DVD.NumeroDVD) 
 from DVD, Filme 
 where DVD.IdFilme = Filme.IdFilme 
 and Filme.Titulo = :pTitulo 
 and DVD.Status = 'D' 
 into :vQtde; 
 
 if (:Qtde > 0) then 
 begin 
 select Locacao.DataPrevDevolucao 
 from Locacao, DVD, Titulo 
 where Filme.IdFilme = DVD.IdFilme 
 and DVD.NumeroDVD = Locacao.NumeroDVD 
 and Filme.Titulo = :pTitulo 
 and Locacao.DataPrevDevolucao having min(Locacao.DataPrevDevolucao) 
 into :pData; 
 
 suspend; 
 end; 
end 
 
 
5) Crie um gatilho que ao realizar uma locação, que ele verifique se existe alguma locação desse cliente em 
aberto. Caso tenha, enviar uma mensagem solicitando a quitação do débito ao cliente e não permitir a 
realização da locação. 
 
create exception erro 'Locação em aberto, favor efetuar o pagamento'; 
 
create or alter trigger num5 
for Locacao before insert 
as 
declare variable vQtde integer; 
begin 
 select count(NumLocacao) 
 from Locacao 
 where IdCliente = new.IdCliente 
 and Pago = 'N' 
 into :vQtde; 
 
 if (:vQtde > 0) then 
 begin 
 exception erro; 
 end; 
end