Prova de Fundamentos de Bancos de Dados - Inf

Prova de Fundamentos de Bancos de Dados
2a Prova
Prof. Carlos A. Heuser
Novembro de 2007
Prova com consulta – duas horas de duração
1 Base de dados para as questões
(Esta é a mesma base de dados que foi usada na primeira prova)
Uma companhia de aviação possui uma base de dados sobre as localidades por ela
atendidas. Esta base de dados contém as seguintes tabelas:
Aeroporto (SiglaAero, NomeAero)
(Tabela com dados dos aeroportos.)
Cidade (CodCidade,NomeCidade,CodPais)
(CodPais) referencia Pais
(Tabela com dados das cidades)
Pais (CodPais,NomePais)
(Tabela com dados de países)
Atendimento (CodCidade,SiglaAero,DistanciaAeroCid)
(SiglaAero) referencia Aeroporto
(CodCidade) referencia Cidade
(Tabela que relaciona as cidades aos aeroportos que as servem e informa a distância em quilômetros da cidade ao aeroporto)
Filial (CdoFilial,EnderecoFilial, CodCidade)
(CodCidade) referencia Cidade
1
2 Questões
Todas questões têm o mesmo peso.
1. Expresse a consulta abaixo em SQL, usando a sintaxe explícita para junções.
Obter o nome da cidade e o nome dos respectivo país, para cada cidade que
têm ao menos uma filial e que fica a menos que 10Km de um aeroporto.
SELECT NomeCidade, NomePais
FROM Cidade
NATURAL JOIN
Pais
NATURAL JOIN
Filial
NATURAL JOIN
Atendimento
WHERE
DistanciaAeroCid < 10
2
2. Expresse a consulta anterior em SQL, sem usar produto cartesiano ou junção, isto é, usando o estilo de cálculo relacional.
SELECT NomeCidade, NomePais
FROM Cidade, Pais
WHERE
Cidade.CodPais=Pais.CodPais
AND
CodCidade IN
(SELECT CodCidade
FROM Atendimento
WHERE DistanciaAeroCid < 10
)
AND
CodCidade IN
(SELECT CodCidade
FROM Filial
)
3
3. Expresse a consulta abaixo em SQL.
Por um erro de programação, foram incluídas linhas na tabela Atendimento,
relacionando vários aeroportos a todas cidades do país de nome "Brasil".
Obter o código e nome de cada aeroporto que está ligado a todas cidades
deste país.
Em cálculo relacional, esta consulta seria resolvida com o quantificador
universal. Como não temos o mesmo em SQL, a consulta foi resolvida
pela negação do quantificador existencial.
SELECT SiglaAero, NomeAero
FROM Aeroporto
WHERE
NOT EXISTS
(SELECT *
FROM Cidade
WHERE CodPais IN
(SELECT CodPais
FROM Pais
WHERE NomePais=’Brasil’
)
AND
NOT EXISTS
(SELECT *
FROM Atendimento
WHERE
CodCidade=Cidade.CodCidade AND
SiglaAero=Aeroporto.SiglaAero
)
)
4
4. Expresse a consulta abaixo em SQL.
Obter uma tabela com quatro colunas:
a) o código e o nome de cada cidade que dista a menos de 15Km de um
aeroporto,
b) o código e o nome de cada cidade atendida pelo mesmo aeroporto
(caso não existam outras cidades atendidas pelo mesmo aeroporto, estas duas colunas devem aparecer vazias).
SELECT CidadePerto.CodCidade,
CidadePerto.NomeCidade.
CidadeOutra.CodCidade,
CidadeOutra.NomeCidade
FROM (Cidade AS CidadePerto
JOIN
Atendimento AS AtendPerto
ON
(CidadePerto.CodCidade
=
AtendPerto.CodCidade AND
AtendPerto.DistanciaAeroCid<15
)
LEFT JOIN
(Cidade AS CidadeOutra
JOIN
Atendimento AS AtendOutro
ON
(CidadeOutra.CodCidade
=
AtendOutro.CodCidade
)
)
ON
(AtendPerto.SiglaAero=AtendOutro.SiglaAero AND
AtendPerto.CodCidade<>AtendOutro.CodCidade
)
5
5. Expresse a consulta abaixo em SQL.
Para cada aeroporto na base de dados, obter sua sigla e seu nome, seguido
do número de cidades por ele atendidas e da média da distância das cidades
por ele atendidas. Aeroportos que não atendem nenhuma cidade devem
igualmente aparecer no resultado.
SELECT SiglaAero,
NomeAero,
COUNT(CodCidade),
AVG(DistanciaAeroCid)
FROM Aeroporto
NATURAL LEFT JOIN
Atendimento
GROUP BY SiglaAero, NomeAero
6
6. Expresse a consulta abaixo em SQL.
Para cada cidade que é atendida por um aeroporto, obter o nome da cidade
mais próxima ao aeroporto.
SELECT CidadeMaisProx.NomeCidade
FROM Cidade AS CidadeMaisProx
NATURAL LEFT JOIN
Atendimento AS AtendimentoMaisProx
WHERE
AtendimentoMaisProx.DistanciaAeroCid =
(SELECT MIN (DistanciaAeroCid)
FROM Atendimento
WHERE SiglaAero=AtendimentoSiglaAero
)
7
7. Expresse a consulta abaixo em SQL.
Obter o código do país, o nome do país e o número de aeroportos do país.
No resultado, devem aparecer somente países com mais de três aeroportos.
SELECT CodPais, NomePais, COUNT(*)
FROM Pais
NATURAL JOIN
Cidade
NATURAL JOIN
Atendimento
GROUP BY CodPais, NomePais
HAVING COUNT(*)
8
8. Deseja-se que a base de dados garanta a seguinte restrição de integridade:
Todas cidades atendidas por um aeroporto devem estar localizadas no mesmo
país.
Responda às seguintes questões:
a) Esta restrição já está implementada na base de dados através de uma
chave primária ou estrangeira? Caso afirmativo, de que forma está
implementada?
As restrições que existem na base de dados não implementam esta
restrição.
b) Esta restrição pode ser implementada por um CHECK CONSTRAINT?
Caso afirmativo, mostre o CHECK CONSTRAINT, caso negativo explique porque não é possível implementar a restrição desta forma e
mostre como esta restrição pode ser implementada por TRIGGERs.
A restrição envolve várias linhas e por isso não pode ser implementada por CHECK CONSTRAINT requerendo implementação de um
TRIGGER.
CREATE TRIGGER ValidaPaisAeroporto
BEFORE INSERT OR UPDATE ON Atendimento
REFERENCING
NEW AS NovoAtendimento
FOR EACH ROW
WHERE
( Atendimento.SiglaAero IN
(SELECT OutroAtendimento.SiglaAero
FROM Atendimento AS OutroAtendimento,
Cidade AS OutraCidade,
Cidade
WHERE
OutraCidade.CodCidade=OutroAtendimento.Cod
Cidade.SiglaAero=NovoAtendimento.SiglaAero
Cidade.CodPais<>OutraCidade.CodPais
)
)
( sinalizar erro
’Aeroporto atende diferentes países’
)
9
9. Considere a seguinte consulta de álgebra relacional:
π Pais.CodPais,
Pais.NomePais,
Atendimento.SiglaAero
(σ Pais.CodPais=Cidade.CodPais
AND Cidade.CodCidade=Atendimento.CodCidade
AND Atendimento.DistanciaAeroCid = 5
(Pais ×
(Cidade × Atendimento)
)
)
Represente esta consulta em árvore e mostre o resultado de cada um dos
passos da otimização algébrica.
1. Representação da consulta em árvore
π Pais.CodPais, Pais.NomePais, Atendimento.SiglaAero
σ Pais.CodPais=Cidade.CodPais
σ Cidade.CodCidade=Atendimento.CodCidade
σ Atendimento.DistanciaAeroCid = 5
×
!aa
a
!!
a
!
Pais
×
©HH
©©
H
Cidade
Atendimento
2. Mover seleções para as folhas
π Pais.CodPais, Pais.NomePais, Atendimento.SiglaAero
σ Pais.CodPais=Cidade.CodPais
×
ÃÃ````
Ã
ÃÃÃ
Pais
``
σ Cidade.CodCidade=Atendimento.CodCidade
×
»»XXXX
»»»
X
»
X
Cidade
σ Atendimento.DistanciaAeroCid = 5
Atendimento
10
3. Produtos cartesianos seguidos de seleções são transformados em junções
π Pais.CodPais, Pais.NomePais, Atendimento.SiglaAero
1 Pais.CodPais=Cidade.CodPais
ÃÃ````
Ã
ÃÃÃ
Pais
``
1 Cidade.CodCidade=Atendimento.CodCidade
»»XXXX
X
X
»»»
»
Cidade
σ Atendimento.DistanciaAeroCid = 5
Atendimento
4. Projeções são movidas para as folhas
π Pais.CodPais, Pais.NomePais, Atendimento.SiglaAero
1 Pais.CodPais=Cidade.CodPais
(((
((((
π CodPais, NomePais
Pais
((hhhhh
hhhh
π Cidade.CodPais, Atendimento.SiglaAero
1 Cidade.CodCidade=Atendimento.CodCidade
((((
(((hhhhhh
h
π CodCidade,CodPais
Cidade
π CodCidade,SiglaAero
σ Atendimento.DistanciaAeroCid = 5
Atendimento
11