Prova Banco de Dados I - UECE, Transcrições de Informática
arnaldo-araujo-11
arnaldo-araujo-11

Prova Banco de Dados I - UECE, Transcrições de Informática

6 páginas
50Números de download
1000+Número de visitas
Descrição
Prova de Bando de Dados do Professor Wamberg da Universidade Estadual do Ceará
40 pontos
Pontos de download necessários para baixar
este documento
Baixar o documento
Pré-visualização3 páginas / 6
Esta é apenas uma pré-visualização
3 mostrados em 6 páginas
Esta é apenas uma pré-visualização
3 mostrados em 6 páginas
Esta é apenas uma pré-visualização
3 mostrados em 6 páginas
Esta é apenas uma pré-visualização
3 mostrados em 6 páginas

1a Questão.

Considere os seguintes esquemas de relação.

VOOS (vcodigo: integer, origem:string, destino:string, preço:real);

ALOCAÇÃO (vcodigo:integer, acodigo:integer);

AVIÕES (acodigo:integer, modelo:string, fabricante:string, autonomia_voo:integer);

CERTIFICADOS (ecodigo:integer, acodigo:integer);

PILOTOS (pcodigo:integer, pnome:string, esalario:integer, HorasVoo:integer);

Escreva as seguintes consultas em SQL:

1. Obtenha os vôos de Fortaleza para São Paulo para os quais todos os aviões alocados só pode serpilotado por pilotos com número de horas de vôo maior do que 3.000. (PS: Um piloto só pode pilotar os aviões para os quais ele é certificado a opera-los ) (Dica: Um vôo de fortaleza para São Paulo satisfaz a condição sss todos os pilotos certificados para operar um (qualquer um) avião alocado para o vôo tem número de horas de vôo maior do que 3.000.

SELECT V.* FROM VOOS V WHERE V.ORIGEM = ‘Fortaleza’ AND V.DESTINO = “São Paulo” AND NOT EXISTS (

SELECT * FROM PILOTO P, CERTIFICADO C, ALOCACAO AL

WHERE P.PCODIGO = C.PCODIGO AND C.ACODIGO = AL.ACODIGO AND P.HORASVOO < 3000 AND V.VCODIGO = AL.VCODIGO

)

2. Obtenha os vôos de Fortaleza para São Paulo para os quais todos os aviões alocados pode ser pilotado por algum piloto com número de horas de vôo maior do que 3.000. (Dica: Um vôo de fortaleza para São Paulo satisfaz a condição sss para qualquer avião alocado para o vôo existe um piloto certificado a operá-lo com número de horas de vôo maior do que 3.000.

SELECT V.* FROMVOOS V WHERE V.ORIGEM = ‘Fortaleza’ AND V.DESTINO = “São Paulo” AND NOT EXISTS

( SELECT AL.VCODIGO

FROMALOCACAO AL WHERE AL.VCODIGO = V.VCODIGO AND NOT EXISTS

( SELECT P.PCODIGO FROMPILOTO P, CERTIFICADO C WHEREP.PCODIGO = C.PCODIGO AND P.HORASVOO > 3000 AND C.ACODIGO = AL.ACODIGO

) )

3. Para cada piloto que é certificado a pilotar mais de 3 aviões, encontre o código do piloto e a máxima autonomia de vôo dos aviões que ele é certificado.

SELECT P.PCODIGO, MAX(AV.AUTONOMIA_VOO) FROMPILOTO P, AVIAO AV, CERTIFICADO C WHERE P.PCODIGO = C.PCODIGO AND C.ACODIGO = AV.ACODIGO GROUP BY P.PCODIGO HAVING COUNT(*) > 3

2a Questão (visões e triggers)

Visões em SQL são tabelas virtuais, i.e., elas não estão armazenadas no banco de dados, mas suas definições são usadas para traduzir consultas nas visões em consultas sobre as tabelas do banco. Uma desvantagem de visões virtuais é que elas tem que ser re-computadas todas as vezes que uma consulta referencia a visão. Um enfoque alternativo é materializar a visão: O conteúdo de uma visão é computado e o resultado é armazenado em uma tabela do banco. Desta forma, uma consulta envolvendo a visão pode acessar diretamente a tabela já materializada. O problema é que quando o conteúdo das tabelas do banco que são usadas na definição da visão mudam, então o conteúdo da visão materializada também tem que mudar para refletir estas mudanças.

Considere as tabelas:

EMPREGADOS (CPF, nome, salário, codDepto).

DEPARTAMENTOS (codigo, nome, telefone, CPF_gerente)

Uma visão materializada contendo os empregados do departamento de Vendas pode ser criada como se segue:

1. CREATE TABLE EMPREGADOS_VENDAS (CPF:Integer, nome:string, salário:real, telefone:string).

2. Inicialmente, V é povoada usando o seguinte comando SQL:

INSERT INTO V SELECT e.CPF, e.nome, e.salario, d.telefone FROM EMPREGADOS e, DEPARTAMENTOS d WHERE d.código = e.código and d.nome =”vendas”

Agora a visão/ relação EMPREGADOS_VENDAS pode ser consultada diretamente. Se uma inserção ocorre na tabela EMPREGADOS, então EMPREGADOS_VENDAS

precisa ser atualizada para refletir estas mudanças. Para mantermos a consistência de V com R podemos usar um trigger:

(a) Escreva um trigger para manter a consistência de EMPREGADOS_VENDAS para refletir uma inserção em EMPREGADOS.

CREATE TRIGGER Ins_Empregados AFTER INSERT ON EMPREGADOS

AS WHEN :NEW.codDepto = (select codigo

from DEPARTAMENTOS WHERE nome = ‘vendas’)

THEN INSERT INTO EMPREGADOS_VENDAS

SELECT :NEW.CPF, :NEW.nome, :NEW.salario, :NEW.telefone FROM :NEW, DEPARTAMENTOS d

WHERE d.codigo = :NEW.codDepto

(b) Escreva outro trigger para manter a consistência de EMPREGADOS_VENDAS para refletir um “UPDATE” em DEPARTAMENTOS.

CREATE TRIGGER Upd_Departamentos AFTER UPDATE ON DEPARTAMENTOS AS WHEN (:NEW.nome = ‘vendas’) and (:OLD.telefone <> :NEW.telefone)

THEN UPDATE EMPREGADOS_VENDAS ev

SET ev.telefone = :NEW.telefone

(c) Identifique outros triggers necessários para a manutenção da consistência de EMPREGADOS_VENDAS.

UPDATE na tabela EMPREGADOS e DELETE na tabela EMPREGADOS.

3a Questão

Seja R (A, B, C, D, E, F, G, I) um esquema de relação com as seguintes dependências funcionais:

F ={DEF, HDEI , DAB, BG, GHA, FE}.

Chaves de F:

{C, D, E} e {C, D, F} (Vocês justificar porque são chaves...)

Cobertura Mínima: (Vocês devem mostrar cada passo do algoritmo na obtenção da cobertura mínima)

={(1) DEF, (2) DEI , (3) DB, (4) BG, (5) GHA, (6) FE}.

a) R está em 2NF? Justifique sua resposta. (Para cada FD verifique se viola 2NF e justifique porque viola ou porque não viola.)

R. Não. As FDs (2) e (3) violam 2NF. (Vocês devem justificar porque violam... notem que F e E são atributos primos).

b) R está em 3NF? Justifique sua resposta. (Para cada FD verifique se viola 3NF e justifique porque viola ou porque não viola.). Caso R não esteja em 3NF, encontre uma decomposição de R em 3NF sem perdas na junção e que preserva as dependências. Mostre cada passo do algoritmo utilizado.

R. Não. As FDs (2), (3), (4), e (5) violam 3NF. Decomposição em 3NF: R1( D,E,F,I), R2(D,B), R3( B,G) R5(G,H,A), R6( F,E), R7( C,D,F)}.

b) Verifique se a decomposição obtida no item b está em BCNF. Justifique sua resposta.

R. Não. R1 não está em BCNF. A FD FE viola BCNF.

Caso a decomposição obtida no item b não esteja em BCNF, encontre uma decomposição de R em BCNF sem perdas na junção. Mostre cada passo do algoritmo utilizado.

Decomposição em BCNF: R1( D,E,I), R2(D,B), R3( B,G) R5(G,H,A), R6( F,E), R7( C,D,F)}.

Até o momento nenhum comentário
Esta é apenas uma pré-visualização
3 mostrados em 6 páginas