prova de BDII

Prévia do material em texto

1) [2,0 pontos] Em um SGBD, diz-se que um plano de execução de transações é serial 
quando as operações da cada transação são executadas sem intercalação com operações 
de outras transações. Por outro lado, diz-se que um plano de execução é serializável 
quando ele é equivalente à um plano de execução serial das transações. Verifique, com 
a construção do grafo de precedência de transações, se os planos de execução P1 e P2 
de três transações T1, T2 e T3 apresentados a seguir são serializáveis, justifique sua 
resposta e apresente o plano de execução serial equivalente. 
 
P1: R2(Z); R2(Y); W2(Y); R3(Y); R3(Z); R1(X); W1(X); W3(Y); 
W3(Z); R2(X); R1(Y); W1(Y); W2(X) 
 
P2: R3(Y); R3(Z); R1(X); W1(X); W3(Y); W3(Z); R2(Z); R1(Y); 
W1(Y); R2(Y); W2(Y); R2(X); W2(X) 
 
R.: 
 
O plano de execução P1 não é serializável porque o grafo de precedência apresenta 
ciclos. 
 
O plano de execução P2 não apresenta ciclos e, portanto. é serializável. A plano serial 
equivalente ao plano de execução P2 é T3 Æ T1 Æ T2. 
 
 
 
 
Universidade Federal Fluminense 
TCC00288 – Banco de Dados II, Turma A1/2016.2 
P1 – 31/10/2016 
 
Aluno: _________________________________________ 
Matrícula: __________________ Turma: _____________ 
Q1 (2,0) 
Q2 (2,0) 
Q3 (2,0) 
Q4 (2,0) 
Q5 (2,0) 
Nota: 
 
2) [2,0 pontos] Dadas as operações das transações T1 e T2 a seguir, acrescente operações 
de RL(.) [read lock], WL(.) [write lock] e UL(.) [unlock] às operações 
existentes seguindo à estratégia de BLOQUEIO EM DUAS FASES RIGOROSO de 
modo a garantir a serializabilidade em planos de execução concorrentes dessas duas 
transações. 
 
T1: R(Y); R(X); X=X+Y; W(X) 
T2: R(X); R(Y); Y=X+Y; W(Y) 
 
R.: 
 
T1: RL(Y); R(Y); WL(X); R(X); X=X+Y; W(X) ; UL(Y); UL(X) 
T2: RL(X); R(X) ; WL(Y); R(Y); Y=X+Y; W(Y) ; UL(X); UL(Y) 
 
3) [2,0 pontos] Um SGBD deve garantir quatro propriedades, chamadas propriedades 
ACID, ao processamento de transações. Defina-as. 
 
R.: 
 
Atomicidade: uma transação é uma unidade atômica de processamento; ou ela será 
executada em sua totalidade ou nenhum dos seus efeitos será persistido no BD. 
 
Consistência: Qualquer transação deverá levar um BD de um estado consistente para 
outro também consistente. 
 
Isolamento: Qualquer transação deverá ser processada de modo a não sofrer 
nenhuma interferência de outras transações concorrentes. 
 
Durabilidade: As mudanças aplicadas ao BD por uma transação que termina com 
sucesso não poderão ser perdidas. 
 
4) [2,0 pontos] Dado o esquema de BD apresentado a seguir, escreva um programa 
(função) em PL/pgSQL para atualizar o adicional salarial por dependente 
(adicional_dep) dos empregados. O valor de adicional_dep é expresso em 
unidades monetárias e deverá ser reajustado em 5% por dependente, ou seja, 
adicional_dep = adicional_dep * (1 + num_dependentes * 
0,5%). Certifique-se de garantir a serializabilidade, se necessário, com os bloqueios 
adequados. 
 
CREATE TABLE empregado ( 
 empregado_id integer NOT NULL, 
 nome character varying NOT NULL, 
 salario real NOT NULL, 
 adicional_dep real NOT NULL, 
 CONSTRAINT empregado_pk PRIMARY KEY 
 (empregado_id) 
); 
CREATE TABLE dependente ( 
 empregado_id integer NOT NULL, 
 seq smallint NOT NULL, 
 nome character varying NOT NULL, 
 CONSTRAINT dependente_pk PRIMARY KEY 
 (empregado_id, seq), 
 CONSTRAINT empregado_fk FOREIGN KEY 
 (empregado_id) REFERENCES empregado 
 (empregado_id)); 
 
R.: 
 
CREATE OR REPLACE FUNCTION public.update_empregado() 
 RETURNS void AS 
$BODY$ 
DECLARE 
 c_empregado CURSOR FOR SELECT * FROM empregado FOR UPDATE OF 
 empregado; 
 v_count integer; 
BEGIN 
 FOR r IN c_empregado LOOP 
 select count(seq) into v_count from dependente where 
 empregado_id = r.empregado_id; 
 if v_count > 3 then 
 UPDATE empregado SET r.adicional_dep = r.adicional_dep * 
 (1 + v_count * 0.05) WHERE CURRENT OF c_empregado; 
 end if; 
 END LOOP; 
END; 
$BODY$ LANGUAGE plpgsql 
 
5) [2,0 pontos] Dado o esquema de BD apresentado a seguir, escreva um programa 
(função) em PL/pgSQL para listar a duração média de ligação telefônica entre regiões 
em um período de tempo [data/hora 1,data/hora 2] a ser informado como parâmetro. 
A listagem deve ser ordenada em ordem decrescente de duração média. Uma região é 
identificada por um par (bairro,município). Certifique-se de garantir a 
serializabilidade, se necessário, com os bloqueios adequados. 
 
CREATE TABLE bairro ( 
 bairro_id integer NOT NULL, 
 nome character varying NOT NULL, 
 CONSTRAINT bairro_pk PRIMARY KEY 
 (bairro_id)); 
 
CREATE TABLE municipio ( 
 municipio_id integer NOT NULL, 
 nome character varying NOT NULL, 
 CONSTRAINT municipio_pk PRIMARY KEY 
 (municipio_id)); 
 
CREATE TABLE antena ( 
 antena_id integer NOT NULL, 
 bairro_id integer NOT NULL, 
 municipio_id integer NOT NULL, 
 CONSTRAINT antena_pk PRIMARY KEY 
 (antena_id), 
 CONSTRAINT bairro_fk FOREIGN KEY 
 (bairro_id) REFERENCES bairro 
 (bairro_id), 
 CONSTRAINT municipio_fk FOREIGN KEY 
 (municipio_id) REFERENCES municipio 
 (municipio_id)); 
CREATE TABLE ligacao ( 
 ligacao_id bigint NOT NULL, 
 numero_orig integer NOT NULL, 
 numero_dest integer NOT NULL, 
 antena_orig integer NOT NULL, 
 antena_dest integer NOT NULL, 
 inicio timestamp NOT NULL, 
 fim timestamp NOT NULL, 
 CONSTRAINT ligacao_pk PRIMARY KEY 
 (ligacao_id), 
 CONSTRAINT antena_orig_fk FOREIGN KEY 
 (antena_orig) REFERENCES antena 
 (antena_id), 
 CONSTRAINT antena_dest_fk FOREIGN KEY 
 (numero_dest) REFERENCES antena 
 (antena_id)); 
 
R.: 
 
CREATE OR REPLACE FUNCTION public.report(p_d1 timestamp,p_d2 
timestamp) 
 RETURNS TABLE( 
 bairro_orig character varying, 
 municipio_orig character varying, 
 bairro_dest character varying, 
 municipio_dest character varying, 
 duracao double precision 
 ) AS $BODY$ 
DECLARE 
BEGIN 
 RETURN 
 QUERY 
 WITH 
 t1 AS (SELECT 
 t2.bairro_id AS bairro_orig, 
 t2.municipio_id AS municipio_orig, 
 t3.bairro_id AS bairro_dest, 
 t3.municipio_id AS municipio_dest, 
 (EXTRACT (EPOCH FROM LEAST(p_d2,t1.fim)) 
 - EXTRACT (EPOCH FROM GREATEST(p_d2,t1.inicio)))/60 
 AS duracao 
 FROM ligacao t1 
 INNER JOIN antena t2 ON t2.antena_id = t1.antena_orig 
 INNER JOIN antena t3 ON t3.antena_id = t1.antena_dest 
 WHERE 
 t1.inicio BETWEEN p_d1 AND p_d2 
 OR t1.fim BETWEEN p_d1 AND p_d2 
 FOR SHARE OF t1), 
 t2 AS (SELECT 
 t1.bairro_orig, 
 t1.municipio_orig, 
 t1.bairro_dest, 
 t1.municipio_dest, 
 AVG(t1.duracao) AS duracao_media 
 FROM t1 
 GROUP BY 
 t1.bairro_orig, 
 t1.municipio_orig, 
 t1.bairro_dest, 
 t1.municipio_dest) 
 SELECT 
 t3.nome AS bairro_orig, 
 t4.nome AS municipio_orig, 
 t5.nome AS bairro_dest, 
 t6.nome AS municipio_dest, 
 t2.duracao_media 
 FROM t2 
 INNER JOIN bairro t3 ON t3.bairro_id = t2.bairro_orig 
 INNER JOIN municipio t4 ON t4.municipio_id = t2.municipio_orig 
 INNER JOIN bairro t5 ON t5.bairro_id = t2.bairro_dest 
 INNER JOIN municipio t6 ON t6.municipio_id = t2.municipio_dest 
 ORDER BY t2.duracao_media DESC; 
 RETURN; 
END; 
$BODY$ LANGUAGE plpgsql;