Fundamentos e Projeto
de Banco de Dados
Lista de Exercícios
“Resolução do Trabalho Final da disciplina
Fundamentos e Projeto de Banco de Dados”
Índice
1.Questão...
...3
Projeto Conceitual...
...3
Projeto Lógico...
.4
Consultas...
...5
Questão 01...
5
Questão 02...
5
Questão 03...
6
Questão 04...
6
Questão 05...
6
Questão 06...
7
Questão 07...
7
Questão 08...
7
Questão 09...
7
Questão 10...
7
2.Questão...
...8
Item 1...
...8
Item 2...
...8
Item 3...
...9
Item 4...
...9
3.Questão...
...10
Modelo...
...10
Item A – Consultas em SQL...
...10
1. Questão
Consultas
Questão 01
INSERT INTO Autor (IdAutor, Nome, Sobrenome) VALUES (000101, 'J.K.', 'Rowling');
INSERT INTO Editora (IdEditora, Nome) VALUES (00011, 'Rocco');
INSERT INTO Categoria (IdCategoria, Nome, Descricao) VALUES(0001, 'Literatura Estrangeira', 'Não há.') ;
INSERT INTO Livro (ISBN, IdCategoria, IdEditora, Titulo, AnoPublicacao,
QtdExemplares, QtdPaginas) VALUES (8532511015, 00001, 00011, 'Harry Potter e a Pedra Filosofal', 2000, 5, 263);
INSERT INTO LivroAutor (IdAutor, ISBN) VALUES (000101, 8532511015);
INSERT INTO Autor (IdAutor, Nome, Sobrenome) VALUES (000102, 'Khaled', 'Housseini');
INSERT INTO Editora (IdEditora, Nome) VALUES (00012, 'Nova Fronteira');
INSERT INTO Categoria (IdCategoria, Nome, Descricao) VALUES (0001, 'Literatura Estrangeira', 'Não há.');
INSERT INTO Livro (ISBN, IdCategoria, IdEditora, Titulo, AnoPublicacao, QtdExemplares, QtdPaginas) VALUES (8520917674, 0001, 00012, 'O Caçador de Pipas', 2005, 3, 365);
INSERT INTO LivroAutor (IdAutor, ISBN) VALUES (000102, 8520917674);
INSERT INTO Autor (IdAutor, Nome, Sobrenome) VALUES (000103, 'Steven', 'Carter');
INSERT INTO Autor (IdAutor, Nome, Sobrenome) VALUES (000104, 'Julia', 'Sokol'); INSERT INTO Categoria (IdCategoria, Nome, Descricao) VALUES (0002,
'Auto-ajuda', 'Não há.');
INSERT INTO Livro (ISBN, IdCategoria, IdEditora, Titulo, AnoPublicacao, QtdExemplares, QtdPaginas) VALUES (8575422197, 00003, 000012, 'O Que Toda Mulher Inteligente Deve Saber', 2006, 5, 160);
INSERT INTO LivroAutor (IdAutor, ISBN) VALUES (000103, 8575422197); INSERT INTO LivroAutor (IdAutor, ISBN) VALUES (000104, 8575422197);
Questão 02
INSERT INTO Pessoa (CPF, Nome, DtNascimento, Logradouro, Numero, Cidade) VALUES (05577081688, 'Marta Sales', 01/01/1970, 'R. Augusto dos Anjos', 150,
'Fortaleza');
INSERT INTO Telefone (CPF, Telefone) VALUES (05577081688, '85.3294-5566'); INSERT INTO Bibliotecaria (CPF) VALUES (05577081688);
INSERT INTO Pessoa (CPF, Nome, DtNascimento, Logradouro, Numero, Cidade) VALUES (11990961304, 'Rosa Muniz', 02/07/1968, 'R. Vagão Sul', 1500, 'Fortaleza'); INSERT INTO Telefone (CPF, Telefone) VALUES (11990961304, '85.3290-6598'); INSERT INTO Bibliotecaria (CPF) VALUES (11990961304);
(37319221004, 'João Flávio', 07/09/1980, 'R. Mestre Antônio', 29, 'Fortaleza'); INSERT INTO Telefone (CPF, Telefone) VALUES (37319221004, '85.3241-2027'); INSERT INTO Cliente (CPF) VALUES (37319221004);
INSERT INTO Pessoa (CPF, Nome, DtNascimento, Logradouro, numero, cidade) VALUES (57278490049, 'Maria Nascimento', 15/12/1975, 'R. Amândio Lucas', 360,
'Fortaleza');
INSERT INTO Telefone (CPF, Telefone) VALUES (57278490049, '85.3229-1240'); INSERT INTO Cliente (CPF) VALUES (57278490049);
INSERT INTO Pessoa (CPF, Nome, DtNascimento, Logradouro, numero, cidade) VALUES (38700700053, 'Marcos André Simões', 09/12/1979, 'R. Cruzeiro do Sul', 1690, 'Fortaleza');
INSERT INTO Telefone (CPF, Telefone) VALUES (38700700053, '85.3496-0059'); INSERT INTO Cliente (CPF) VALUES (38700700053);
Questão 03
INSERT INTO Exemplar (IdExemplar, ISBN, Numero) VALUES (00001, 8520917674, 02); INSERT INTO Emprestimo (IdEmprestimo, Cliente_CPF, Bibliotecaria_CPF,
DtEmprestimo, DtDevolucao, Multa) VALUES (000000001, 38700700053, 05577081688, 16/02/2005, 00/00/0000, 0);
INSERT INTO EmprestimoExemplar (IdExemplar, IdEmprestimo, IdEmprestimoExemplar) VALUES (00001, 000000001, 000000000001);
INSERT INTO Exemplar (IdExemplar, ISBN, Numero) VALUES (00002, 8532511015, 05); INSERT INTO Emprestimo (IdEmprestimo, Cliente_CPF, Bibliotecaria_CPF,
DtEmprestimo, DtDevolucao, Multa) VALUES (000000002, 38700700053, 11990961304, 17/03/2005, 00/00/0000, 0);
INSERT INTO EmprestimoExemplar (IdExemplar, IdEmprestimo, IdEmprestimoExemplar) VALUES (00002, 000000002, 000000000002);
INSERT INTO Exemplar (IdExemplar, ISBN, Numero) VALUES(00003, 8520917674, 03); INSERT INTO Emprestimo (IdEmprestimo, Cliente_CPF, Bibliotecaria_CPF,
DtEmprestimo, DtDevolucao, Multa) VALUES (000000003, 57278490049, 5577081688, 21/06/2007, 00/00/0000, 0);
INSERT INTO EmprestimoExemplar (IdExemplar, IdEmprestimo, IdEmprestimoExemplar) VALUES(00003, 000000003, 000000000003);
Questão 04
SELECT * FROM biblio.emprestimo Where Cliente_CPF = 38700700053 and DtDevolucao is null
Questão 05
SELECT Distinct Pessoa.Nome, Livro.Titulo
FROM Emprestimo, EmprestimoExemplar, Cliente, Pessoa, Exemplar, Livro Where Cliente.CPF = Pessoa.CPF and
Emprestimo.idEmprestimo = EmprestimoExemplar.idEmprestimo and Emprestimo.Cliente_CPF = Cliente.CPF and
EmprestimoExemplar.IdExemplar = Exemplar.IdExemplar and Livro.ISBN = Exemplar.ISBN and
Emprestimo.DtDevolucao is null;
Questão 06
SELECT distinct MAX(multa) FROM emprestimo
Questão 07
SELECT Titulo FROM Livro Where Livro.ISBN not in (
Select Distinct Livro.ISBN
From Emprestimo, EmprestimoExemplar, Exemplar, Livro Where Livro.ISBN = Exemplar.ISBN
and Emprestimo.idEmprestimo = EmprestimoExemplar.idEmprestimo and EmprestimoExemplar.IdExemplar = Exemplar.IdExemplar)
Questão 08
Select Pessoa.Nome
From Pessoa, Bibliotecaria, Estante Where Pessoa.CPF = Bibliotecaria.CPF
And Estante.Bibliotecaria_CPF = Pessoa.CPF
Questão 09
SELECT Editora.nome, Count(*) as QtdeLivros FROM Editora, Livro
WHERE Editora.idEditora=Livro.idEditora Group by Editora.nome
Questão 10
SELECT p.Nome , count(*)
FROM cliente c, emprestimo e, pessoa p WHERE c.cpf=e.cliente_cpf
AND p.CPF=c.CPF
AND e.DtEmprestimo BETWEEN '2007-01-01' and '2007-01-31' GROUP BY c.CPF
2. Questão
Item 1
● Atributos da Super Chave: A, B, C, D, E, F, G, H, I, J, K -> todos os atributos da relação;
● Atributos de Chave Candidata: C, G, H, E, I, A, F, D, K -> atributos primos da relação, os que podem ser considerados para compor a chave-primária;
● Atributos da Chave Primária: E, I, A, F, H -> atributos que identificam unicamente uma tupla na relação.
Item 2
● Não. R não está em 3NF porque:
► não está na Segunda Forma Normal, pois há atributo não-primo, como por exemplo o atributo B, dependendo funcionalmente de parte da chave primária (F4: E -> BC);
► e há atributo não-chave, como por exemplo K, dependendo transitivamente da chave candidata C, G, H, E, I, A, F, D, K (AEFH -> G / G -> F)
● F1: C -> H: não viola porque C e H são atributos primos, sendo H atributo da chave primária;
● F2: G -> F: viola porque G é determinado por parte da chave primária (AEFH->G);
● F3: H -> IBK: viola porque o atributo não-primo B está dependendo de parte da chave primária;
● F4: E ->BC: viola, pois o atributo não-primo B depende funcionalmente de parte da chave primária;
● F4: E -> BC: viola, pois o atributo não-primo B depende funcionalmente de parte da chave primária;
● F5: EI -> K: viola, pois K é transitivamente dependente da chave primária (H ->IBK);
● F6: AEFH -> G: viola, porque Gesta determinando F, parte da chave candidata em F2;
● F7: EFI ->J: viola, pois o atributo não-primo J depende funcionalmente de parte da chave primária;
● R em 3NF sem perdas na junção e que preserva as dependências:
► R1: (EH -> B)
► R2: (EIH -> K)
► R3: (E -> C)
► R4: (EFI -> J)
Item 3
● R1: (EH -> B) está em BCNF porque EH -> B é uma dependência funcional não-trivial e EH é super-chave de R1;
● R2: (EIH -> K) está em BCNF porque EH -> B é uma dependência funcional não-trivial e EH é super-chave de R2;
● R3: (E -> C) está em BCNF porque EH -> B é uma dependência funcional não-trivial e EH é super-chave de R3;
● R4: (EFI -> J) está em BCNF porque EH -> B é uma dependência funcional não-trivial e EH é super-chave de R4;
● R5: (EFH -> G) não está em BCNF B é uma dependência funcional trivial;
Item 4
3. Questão
Modelo
Item A – Consultas em SQL
1. Recupere o modelo e o fabricante dos aviões para os quais todos os pilotos certificados a
operá-los ganham mais que 80.000,00.
SELECT DISTINCT a.modelo, a.fabricante FROM aviao a, certificado c, empregado e WHERE a.idAviao=c.idAviao
AND e.salario > 80000
AND c.idAviao not in (SELECT DISTINCT c.idAviao
FROM empregado e, certificado c WHERE e.salario <=80000
AND e.idEmpregado=c.idEmpregado)
2. 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 c.idEmpregado , max(a.autonomia)
FROM aviao a, certificado c, (SELECT c.idEmpregado
FROM empregado e, certificado c WHERE e.idEmpregado=c.idEmpregado GROUP by c.idEmpregado HAVING count(c.idEmpregado) >= 3) e WHERE a.idAviao=c.idAviao AND c.idEmpregado=e.idEmpregado GROUP by c.idEmpregado
3. Identifique as rotas que podem ser pilotadas por todos os pilotos que ganham mais de
100.000,00.
SELECT distinct e.nome as piloto, a.modelo as aviao, v.origem, v.destino FROM empregado e, certificado c, aviao a, rota r, voo v
WHERE e.salario > 100000 AND c.idEmpregado=e.idEmpregado AND c.idAviao=a.idAviao AND r.idAviao=a.idAviao AND r.idVoo=v.idVoo ORDER by e.nome,a.modelo,v.origem,v.destino
4. Encontre o menor preço de vôo direto para cada par de cidades.
SELECT v.origem,v.destino, min(v.valor) FROM voo v
5. Um cliente deseja viajar de “Madison” para “NY” com no máximo 2 mudanças de vôo. Liste
todas as possibilidades de horário de partida de “Madison”, sabendo que ele quer chegar em
“NY” as 18:00.
SELECT o.origem, o.destino, o.hora_partida, o.hora_chegada, d.*
FROM voo o LEFT JOIN (SELECT v1.origem as v1origem, v1.destino as v1destino, v1.hora_partida as v1hora_partida, v1.hora_chegada as v1hora_chegada,
v2.origem as v2origem, v2.destino as v2destino, v2.hora_partida as v2hora_partida, v2.hora_chegada as v2hora_chegada
FROM (SELECT v.* FROM voo v
WHERE v.origem<>'NY') v1 LEFT JOIN (SELECT v.*
FROM voo v
WHERE v.destino='NY' AND v.hora_chegada<='18:00') v2 ON v1.destino=v2.origem
WHERE v1.destino='NY' or v2.destino='NY'
ORDER BY v2.destino) d ON o.destino=d.v1origem WHERE o.origem='Madison'
Item B – Consultas em Álgebra Relacional
1. Recupere o modelo e o fabricante dos aviões para os quais todos os pilotos certificados a
operá-los ganham mais que 80.000,00.
R1 <- SELECAO (salario <=80000) EMPREGADO
R2 <- R1 JOIN (idEmpregado=idEmpregado) CERTIFICADO R3 <- PROJECAO (idAviao) R2
R4 <- SELECAO (salario > 80000) EMPREGADO
R5 <- R4 JOIN (idEmpregado=idEmpregado) CERTIFICADO R6 <- R5 JOIN (idAviao=idAviao) AVIAO
R7 <- R6 NAOESTANDO (idAviao=idAviao) R3
R8 <- PROJECAO SEMREPETICAO(modelo, fabricante) R7
2. Identifique as rotas que podem ser pilotadas por todos os pilotos que ganham mais de
100.000,00.
R1 <- SELECAO (salario>100000) EMPREGADO
R2 <- R1 JOIN (idEmpregado=idEmpregado) CERTIFICADO R3 <- R2 JOIN (idAviao=idAviao) AVIAO
R5 <- R4 JOIN (idVoo=idVoo) VOO
R6 <- PROJECAO (nome, modelo, origem, destino) R5
3. Encontre o menor preço de vôo direto para cada par de cidades.
R1 <- PROJECAO (origem,destino,MINIMUM valor) VOO AGRUPANDO origem,destino
