Prova2 20162 Banco de Dados II

Prévia do material em texto

1) [2,0 pontos] Um gerenciador de banco de dados possui mecanismos de recuperação de 
falhas que garantem a durabilidade de transações confirmadas. Descreva as estratégias 
de atualização adiada e atualização imediata para a implementação desses 
mecanismos de recuperação. 
 
2) [2,0 pontos] O que é e de que forma os sistemas gerenciadores de banco de dados 
utilização o “cache”? 
 
3) [2,0 pontos] Dada a tabela especificada a seguir escreva os comandos PostgreSQL para 
habilitar a segurança a nível de linhas para essa tabela de forma a permitir que somente 
os próprios usuários ou seu supervisor possam alterar e consultar os dados dos 
empregados. Assuma que os usuários do banco de dados possuem identificadores 
iguais aos identificadores dos empregados. 
 
CREATE TABLE empregado ( 
 ssn integer NOT NULL, 
 superssn integer NOT NULL, 
 pnome integer NOT NULL, 
 unome character varying NOT NULL, 
 endereco character varying NOT NULL, 
 CONSTRAINT empregado_pk PRIMARY KEY (ssn), 
 CONSTRAINT supervisor_fk FOREIGN KEY (superssn) REFERENCES empregado (ssn)); 
 
R.: 
 
ALTER TABLE empregado ENABLE ROW LEVEL SECURITY; 
CREATE POLICY empregado_policy ON empregado 
 USING (ssn = current_user OR superssn = current_user) 
WITH CHECK (ssn = current_user OR superssn = current_user); 
 
4) [2,0 pontos] Os pedidos de refeição em uma rede de restaurantes são armazenados em 
um banco de dados cuja especificação (em PostgreSQL) encontra-se a seguir. Os 
preços dos pratos podem variar de uma loja para outra e nem todos os restaurantes 
oferecem os mesmos pratos. Além disso, cada pedido deve ser feito a somente um 
único restaurante. Escreva, utilizando triggers PostgreSQL, a implementação da 
restrição de integridade que garante a consistência dos pedidos no que se refere 
somente a unicidade de restaurante do pedido. 
Universidade Federal Fluminense 
TCC00288 – Banco de Dados II, Turma A1/2016.2 
P2 – 19/12/2016 
 
Aluno: _________________________________________ 
Matrícula: __________________ Turma: _____________ 
Q1 (2,0) 
Q2 (2,0) 
Q3 (2,0) 
Q4 (2,0) 
Q5 (2,0) 
Nota: 
 
CREATE TABLE restaurante ( 
 cnpj integer NOT NULL, 
 endereco character varying NOT NULL, 
 CONSTRAINT rest_pk PRIMARY KEY (cnpj)); 
 
CREATE TABLE prato ( 
 prato_id integer NOT NULL, 
 nome character varying NOT NULL, 
 CONSTRAINT pra 
 
CREATE TABLE pedido ( 
 pedido_id integer NOT NULL, 
 cnpj integer NOT NULL, 
 CONSTRAINT pedido_pk PRIMARY KEY 
 (pedido_id), 
 CONSTRAINT pedido_rest_fk FOREIGN KEY 
 (cnpj) REFERENCES restaurante (cnpj)); 
CREATE TABLE menu ( 
 cnpj integer NOT NULL, 
 prato_id integer NOT NULL, 
 preco real NOT NULL, 
 CONSTRAINT menu_pk PRIMARY KEY 
 (cnpj,prato_id), 
 CONSTRAINT menu_rest_fk FOREIGN KEY 
 (cnpj) REFERENCES restaurante (cnpj), 
 CONSTRAINT menu_prato_fk FOREIGN KEY 
 (prato_id) REFERENCES prato (prato_id)); 
CREATE TABLE item_pedido ( 
 pedido_id integer NOT NULL, 
 item integer NOT NULL, 
 cnpj integer NOT NULL, 
 prato_id integer NOT NULL, 
 qtd integer NOT NULL, 
 CONSTRAINT item_pk PRIMARY KEY 
 (pedido_id,item), 
 CONSTRAINT item_pedido_fk FOREIGN KEY 
 (pedido_id) REFERENCES pedido 
 (pedido_id), 
 CONSTRAINT item_menu_fk FOREIGN KEY 
 (cnpj,prato_id) REFERENCES menu 
 (cnpj,prato_id)); 
 
R.: 
 
 CREATE OR REPLACE FUNCTION verifica_item() RETURNS trigger AS ' 
 BEGIN 
 IF ((SELECT cnpj FROM pedido WHERE pedido_id = NEW.pedido_id) <> NEW.cnpj) THEN 
 RAISE EXCEPTION $$Erro: prato % nao oferecido pelo restaurante!$$, NEW.prato_id; 
 END IF; 
 RETURN NEW; 
 END;' LANGUAGE plpgsql; 
 
 CREATE TRIGGER verifica_item_tgr BEFORE INSERT OR UPDATE ON item_pedido 
 FOR EACH ROW EXECUTE PROCEDURE verifica_item(); 
 
 CREATE OR REPLACE FUNCTION verifica_pedido() RETURNS trigger AS ' 
 BEGIN 
 IF (NEW.cnpj <> OLD.cnpj 
 AND EXISTS (SELECT 1 FROM item_pedido WHERE pedido_id = OLD.pedido_id)) THEN 
 RAISE EXCEPTION $$Erro: pedido ja possui itens de outro restaurante!$$; 
 END IF; 
 RETURN NEW; 
 END;' LANGUAGE plpgsql; 
 
 CREATE TRIGGER verifica_pedido_tgr BEFORE UPDATE ON pedido 
 FOR EACH ROW EXECUTE PROCEDURE verifica_pedido(); 
 
5) [2,0 pontos] A movimentação de uma conta bancária é registrada na tabela a seguir. As 
movimentações não podem ser excluídas ou alteradas, essas operações devem ser 
convertidas no registro de um estorno da movimentação anterior e na inclusão de outra 
movimentação quando necessário. Escreva, utilizando triggers PostgreSQL, a 
implementação de um mecanismo para implementar essa regra. 
 
CREATE TABLE movimentacao ( 
 conta integer NOT NULL, 
 agencia integer NOT NULL, 
 data timestamp NOT NULL, 
 valor real NOT NULL, 
 descricao character varying NOT NULL, 
 CONSTRAINT movimentacao_pk PRIMARY KEY (conta,agencia,data)); 
 
R.: 
 
CREATE OR REPLACE FUNCTION altera_conta() RETURNS trigger AS ' 
 BEGIN 
 IF (TG_OP = $$DELETE$$) THEN 
 INSERT INTO movimentacao (conta,agencia,data_hora,valor,descricao) 
 VALUES (OLD.conta,OLD.agencia,now(),-OLD.valor,OLD.descricao); 
 RETURN null; 
 ELSIF (TG_OP = $$UPDATE$$) THEN 
 IF (NEW.conta <> OLD.conta 
 OR NEW.agencia <> OLD.agencia 
 OR NEW.data_hora <> OLD.data_hora 
 OR NEW.valor <> OLD.valor 
 OR NEW.descricao <> OLD.descricao) THEN 
 INSERT INTO movimentacao (conta,agencia,data_hora,valor,descricao) 
 VALUES (OLD.conta,OLD.agencia,clock_timestamp(),-OLD.valor,OLD.descricao); 
 INSERT INTO movimentacao (conta,agencia,data_hora,valor,descricao) 
 VALUES (NEW.conta,NEW.agencia,clock_timestamp(),NEW.valor,NEW.descricao); 
 END IF; 
 RETURN null; 
 END IF; 
 END;' LANGUAGE plpgsql; 
 
 CREATE TRIGGER altera_conta_tgr before DELETE OR UPDATE ON movimentacao 
 FOR EACH ROW EXECUTE PROCEDURE altera_conta();