Gabarito AP2-2014.1

Prévia do material em texto

Fundação CECIERJ - Vice Presidência de Educação Superior a Distância 
Curso de Tecnologia em Sistemas de Computação 
Disciplina Banco de Dados 
AP2 1° semestre de 2014 
 
 
Nome – 
 
Assinatura – 
 
Observações: 
1. Prova sem consulta e sem uso de máquina de calcular. 
2. Use caneta para preencher o seu nome e assinar nas folhas de questões e nas folhas de 
respostas. 
3. Você pode usar lápis para responder as questões. 
4. Ao final da prova devolva as folhas de questões e as de respostas. 
5. Todas as respostas devem ser transcritas nas folhas de respostas. As respostas nas folhas 
de questões não serão corrigidas. 
 
 
Questão 1 [6,0 pontos] 
Considere o esquema relacional abaixo. As chaves primárias estão sublinhadas. 
 
Pessoa(Cod,Nome,DataNasc) 
 
Publicacao(Cod,Titulo,CodArea) 
CodArea referencia Area (Cod) 
 
Autor(CodAutor,CodPublicacao) 
CodAutor referencia Pessoa (Cod), 
CodPublicacao referencia Publicacao (Cod) 
 
Revisao(CodRevisor,CodPublicacao,Nota) 
CodRevisor referencia Pessoa (Cod), 
CodPublicacao referencia Publicacao (Cod) 
 
Area(Cod,Nome,CodAreaGenerica) 
CodAreaGenerica referencia Area (Cod) 
 
(a) Escreva um comando SQL para excluir a tabela Autor [1,0 ponto] 
 
DROP TABLE Autor; 
 
(b) Escreva um comando SQL para incluir uma coluna nova, chamada Ano, do tipo inteiro, na 
tabela Publicacao. [0,5 ponto] 
 
ALTER TABLE Publicacao 
ADD Ano INTEGER; 
 
(c) Escreva o comando SQL necessário para excluir todas as revisões que tiveram nota 5. [0,5 
ponto] 
 
DELETE FROM Revisao 
WHERE Nota = 5 
 
(d) Escreva uma instrução SQL que retorna o nome da pessoa e o título de todas as publicações 
das quais ela foi autora. [1,0 ponto] 
SELECT p.Nome, pub.Titulo 
FROM Pessoa p, Publicacao pub, Autor a 
WHERE p.Cod=a.CodAutor 
AND a. CodPublicacao = pub.Cod 
 
(e) Escreva uma instrução SQL que retorna o nome do revisor e o número de revisões que cada 
pessoa efetuou. [1,0 ponto] 
 
SELECT p.Nome, p.Cod, COUNT(CodPublicacao) AS NumRevisoes 
FROM Pessoa p, Revisao 
WHERE p.Cod=r.CodRevisao 
GROUP BY p.Cod 
 
(f) Escreva uma instrução SQL que retorne o nome da pessoa, seu código e a média das notas de 
suas revisões, mas apenas para as pessoas que tenham revisado mais do que 10 publicações. [1,0 
ponto] 
 
SELECT p.Cod, p.Nome, AVG(r.Nota) AS NotaMedia 
FROM Pessoa p, Revisao 
WHERE p.Cod=r.CodReviso 
GROUP BY p.Cod, p.Nome 
HAVING COUNT(*) > 10 
 
(g) Escreva uma instrução SQL que retorne o nome das pessoas que não trabalharam como 
revisores de nenhuma publicação. [1,0 ponto] 
 
SELECT p.Nome 
FROM Pessoa p 
WHERE p.Cod NOT IN (SELECT r.CodRevisor 
 FROM Revisao) 
 
 
 
Questão 2 [1,5 pontos] 
 
Considere a seguinte tabela, não necessariamente normalizada, de uma base de dados referente a 
locadoras de veículos (as chaves primárias estão sublinhadas): 
 
Tab (CodEst, CEP, Km, NomeMun, SiglaUF, NomeUF, NomeEst) 
 
As dependências funcionais (podendo incluir dependências transitivas) que existem nesta tabela 
são as seguintes: 
 
SiglaUF → NomeUF 
CEP → NomeMun 
CEP → SiglaUF 
CEP → NomeUF 
CodEst → NomeEst 
(CodEst, CEP) → Km 
 
(a) Diga em que forma normal encontra-se a tabela [0,5 ponto] 
 
A tabela encontra-se na primeira forma normal. 
 
(b) Caso a tabela não se encontre na terceira forma normal, mostre a transformação da tabela para 
a terceira forma normal. Mostre cada forma normal intermediária, entre aquela em que a tabela se 
encontra e a terceira forma normal. [1,0 ponto] 
 
Passagem para a Segunda Forma Normal 
 
Tab1 (CodEst, CEP, Km) 
Tab2 (CodEst, NomeEst) 
Tab3 (CEP, NomeMun, SiglaUF, NomeUF) 
 
Passagem para a Terceira Forma Normal 
 
Tab1 (CodEst, CEP, Km) 
Tab2 (CodEst, NomeEst) 
Tab3 (CEP, NomeMun, SiglaUF) 
Tab4 (SiglaUF, NomeUF) 
 
Questão 3 [2,5 pontos] 
 
Considere o diagrama ER mostrado abaixo. Construa um esquema relacional equivalente a este 
diagrama ER. O diagrama encontra-se na notação do DIA (ferramenta que usamos em sala de 
aula para construir modelos ER). 
 
 
 
País (SiglaPaís, NomePaís) 
Copa (AnoCopa, DataInicioCopa, DataFimCopa, CidadeAbertura) 
Pessoa (CodPessoa, NomePessoa, DataNascimento, SiglaPaísNascimento) 
 SiglaPaísNascimento referencia País 
Equipe (CodEquipe, SiglaPaís, CodTreinador, CodCopa) 
 SiglaPaís referencia País 
 CodTreinador referencia Pessoa 
 CodCopa referencia Copa 
Jogador (CodPessoa, CodEquipe) 
 CodPessoa referencia Pessoa 
 CodEquipe referencia Equipe 
Jogo (NumeroJogo, EstadioJogo, DataJogo, HoraJogo, CodEquipe1, GolsEquipe1, CodEquipe2, 
GolsEquipe2) 
 CodEquipe1 referencia Equipe 
 CodEquipe2 referencia Equipe