Prova BDII

Prévia do material em texto

1) [2,0 pontos] Defina o que é um plano de execução de transações serializável e explique 
qual a utilidade de se garantir que um plano de execução seja serializável. 
 
2) [2,0 pontos] Descreva como funciona o processo de controle de concorrência baseado 
em ordenação de timestamps. 
 
3) [2,0 pontos] Dada a tabela de auditoria especificada a seguir escreva os comandos de 
segurança PostgreSQL para habilitar a segurança a nível de linhas para essa tabela de 
forma a permitir que somente usuários de um grupo identificado com o rótulo 
“auditores” possam alterar o conteúdo da tabela, mas qualquer usuário possa consultar 
os seus dados. Esses comandos devem incluir a criação do grupo “auditores” e de um 
usuário pertencente a esse grupo. 
 
CREATE TABLE emp_audit ( 
 operacao text NOT NULL, 
 data_hora timestamp NOT NULL, 
 usuario text NOT NULL, 
 ssn integer NOT NULL, 
 nome integer NOT NULL, 
 endereco character varying NOT NULL); 
 
R.: 
 
ALTER TABLE emp_audit ENABLE ROW LEVEL SECURITY; 
 
CREATE ROLE lapaesleme PASSWORD 'pass' LOGIN INHERIT; 
CREATE ROLE auditores; 
GRANT auditores TO lapaesleme; 
GRANT all ON public.emp_audit TO lapaesleme; 
 
CREATE POLICY emp_audit_policy1 ON emp_audit FOR select USING (true); 
CREATE POLICY emp_audit_policy2 ON emp_audit FOR insert WITH CHECK (false); 
CREATE POLICY emp_audit_policy3 ON emp_audit FOR update USING (false) WITH CHECK (false); 
CREATE POLICY emp_audit_policy4 ON emp_audit FOR delete USING (false); 
CREATE POLICY emp_audit_policy5 ON emp_audit TO auditores USING (true) WITH CHECK (true); 
 
4) [2,0 pontos] Para se hospedar em uma rede de hotéis é necessário fazer uma reserva 
prévia informando-se o período de estadia desejado. A estadia só poderá ocorrer dentro 
do período da reserva, não é exigido que todo o período reservado seja utilizado para a 
estadia e a reserva só terá validade até o final do primeiro dia reservado. O banco de 
dados utilizado para controlar os aluguéis é apresentado a seguir. Escreva, utilizando 
triggers em PL/pgSQL, a implementação das restrições de integridade que garantem a 
consistência entre reservas e estadias no que se refere somente às regras apresentadas. 
 
Universidade Federal Fluminense 
TCC00288 – Banco de Dados II, Turma A1/2016.2 
VR – 02/01/2017 
 
Aluno: _________________________________________ 
Matrícula: __________________ Turma: _____________ 
Q1 (2,0) 
Q2 (2,0) 
Q3 (2,0) 
Q4 (2,0) 
Q5 (2,0) 
Nota: 
 
Dica: var1 + $$1 day$$::INTERVAL -- adiciona 1 dia ao valor 
na variável var1 
 
CREATE TABLE hotel ( 
 numero integer NOT NULL, 
 nome TEXT NOT NULL, 
 CONSTRAINT hotel_pk PRIMARY KEY 
 (numero)); 
 
CREATE TABLE reserva ( 
 numero integer NOT NULL, 
 hotel integer NOT NULL, 
 cpf_cnpj integer NOT NULL, 
 inicio timestamp not null, 
 fim timestamp not null, 
 CONSTRAINT reserva_pk PRIMARY KEY 
 (numero), 
 CONSTRAINT reserva_hotel_fk FOREIGN KEY 
 (hotel) REFERENCES hotel (numero)); 
CREATE TABLE estadia ( 
 numero integer NOT NULL, 
 quarto text not null, 
 inicio timestamp not null, 
 fim timestamp, 
 CONSTRAINT estadia_pk PRIMARY KEY 
 (numero), 
 CONSTRAINT estadia_reserva_fk FOREIGN KEY 
 (numero) REFERENCES reserva (numero) 
 on delete restrict on update cascade); 
 
 
R.: 
 
 CREATE OR REPLACE FUNCTION verifica_estadia() RETURNS trigger AS ' 
 DECLARE 
 v_inicio timestamp; 
 v_fim timestamp; 
 BEGIN 
 SELECT inicio, fim INTO v_inicio,v_fim FROM reserva WHERE numero = NEW.numero; 
 
 IF (NOT (NEW.inicio BETWEEN v_inicio AND (v_inicio + $$1 day$$::INTERVAL) 
 AND (NEW.fim BETWEEN v_inicio AND v_fim OR NEW.fim IS NULL) 
 AND (NEW.inicio < NEW.fim OR NEW.fim IS NULL)) THEN 
 RAISE EXCEPTION $$Erro: periodo de estadia invalido!$$; 
 END IF; 
 
 RETURN NEW; 
 END;' LANGUAGE plpgsql; 
 
 CREATE TRIGGER verifica_estadia_tgr BEFORE INSERT OR UPDATE ON estadia 
 FOR EACH ROW EXECUTE PROCEDURE verifica_estadia(); 
 
 
 CREATE OR REPLACE FUNCTION verifica_reserva() RETURNS trigger AS ' 
 DECLARE 
 v_inicio timestamp; 
 v_fim timestamp; 
 BEGIN 
 SELECT inicio, fim INTO v_inicio,v_fim FROM estadia WHERE numero = OLD.numero; 
 
 IF (NOT (FOUND 
 AND (v_inicio BETWEEN NEW.inicio AND (NEW.inicio + $$1 day$$::INTERVAL)) 
 AND (v_fim BETWEEN NEW.inicio AND NEW.fim OR v_fim IS NULL) 
 AND (NEW.inicio < NEW.fim))) THEN 
 RAISE EXCEPTION $$Erro: periodo de estadia incompativel com reserva!$$; 
 END IF; 
 
 RETURN NEW; 
 END;' LANGUAGE plpgsql; 
 
 CREATE TRIGGER verifica_reserva_tgr BEFORE UPDATE ON reserva 
 FOR EACH ROW EXECUTE PROCEDURE verifica_reserva(); 
 
5) [2,0 pontos] Considerando o esquema lógico do banco de dados apresentado a seguir 
para campeonatos de futebol, especifique uma função para computar a tabela de 
classificação dos campeonatos. A função deverá ter como parâmetros de entrada 1) o 
código do campeonato para o qual se deseja gerar a tabela de classificação, 2) a posição 
inicial do ranque e 3) a posição final do ranque. 
 
Obs. 1: Uma vitória vale 3 pontos e um empate 1 ponto. 
Obs. 2: A classificação é feita por ordem decrescente de pontuação. 
Obs. 3: O critério de desempate é o número de vitórias 
 
Dica: SELECT… LIMIT l OFFSET o; -- recupera l tuplas a partir 
da posição o do result set. 
 
drop table if exists campeonato cascade; 
CREATE TABLE campeonato ( 
 codigo text NOT NULL, 
 nome TEXT NOT NULL, 
 ano integer not null, 
 CONSTRAINT campeonato_pk PRIMARY KEY 
 (codigo)); 
 
drop table if exists time_ cascade; 
CREATE TABLE time_ ( 
 sigla text NOT NULL, 
 nome TEXT NOT NULL, 
 CONSTRAINT time_pk PRIMARY KEY 
 (sigla)); 
drop table if exists jogo cascade; 
CREATE TABLE jogo ( 
 campeonato text not null, 
 numero integer NOT NULL, 
 time1 text NOT NULL, 
 time2 text NOT NULL, 
 gols1 integer not null, 
 gols2 integer not null, 
 data_ date not null, 
 CONSTRAINT jogo_pk PRIMARY KEY 
 (campeonato,numero), 
 CONSTRAINT jogo_campeonato_fk FOREIGN KEY 
 (campeonato) REFERENCES campeonato 
(codigo), 
 CONSTRAINT jogo_time_fk1 FOREIGN KEY 
 (time1) REFERENCES time_ (sigla), 
 CONSTRAINT jogo_time_fk2 FOREIGN KEY 
 (time2) REFERENCES time_ (sigla)); 
 
 
R.: 
 
CREATE OR REPLACE FUNCTION pontuacao(p_campeonato text, p_pos1 integer, p_pos2 integer) 
 RETURNS table ( 
 campeonato text, 
 time_ text, 
 pontos smallint, 
 vitorias smallint, 
 posicao smallint) AS ' 
 DECLARE 
 BEGIN 
 RETURN 
 QUERY 
 WITH 
 pontos(c,j,t,p) as (select campeonato,numero,time1,3 
 from jogo where gols1 > gols2 AND campeonato = p_campeonato 
 union select campeonato,numero,time2,3 
 from jogo where gols2 > gols1 AND campeonato = p_campeonato 
 union select campeonato,numero,time1,1 
 from jogo where gols1 = gols2 AND campeonato = p_campeonato 
 union select campeonato,numero,time2,1 
 from jogo where gols1 = gols2 AND campeonato = p_campeonato), 
 vitorias(c,t,v) as (select campeonato,time1,1 
 from jogo where gols1 > gols2 AND campeonato = p_campeonato 
 union select campeonato,time2,1 
 from jogo where gols2 > gols1 AND campeonato = p_campeonato), 
 pontuacao(c,t,tp) as (select c,t,sum(p) as pontos 
 from pontos group by c,t), 
 saldo(c,t,tv) as (select c,t,sum(v) as tfrom vitorias group by c,t) 
 select p.c,t.nome,p.tp,s.tv 
 from pontuacao as p 
 natural join saldo as s 
 inner join time_ as t on t.sigla = p.t 
 order by p.tp desc, s.tv desc 
 limit p_pos2 - p_pos2 +1 offset p_pos1; 
 
 return; 
 END;' LANGUAGE plpgsql