Prova de Fundamentos de Bancos de Dados - Inf

Prova de Fundamentos de Bancos de Dados
1a Prova
Prof. Carlos A. Heuser
Setembro de 2007
Prova sem consulta – duas horas de duração
1. (Peso 2,5) Uma companhia de aviação deseja montar uma base de dados
para divulgação em um site Web. A base de dados deve conter as seguintes
informações.
A companhia oferece vários vôos. Cada vôo é identificado por um código
numérico e a base de dados deve registrar o tipo de aeronave que é usada
para o vôo. Cada aeronave é identificada por uma sigla alfa-numérica, como
"B-767"e possui uma descrição. Obviamente, um tipo de aeronave pode ser
usado em muitos vôos.
Além dos dados acima, é necessário saber os trechos que compõe o vôo.
Um vôo é composto de vários trechos, numerados de um em diante, na
ordem em que são voados. Para cada trecho é necessário saber o horário de
saída, o horário de chegada, o aeroporto de origem e o aeroporto de destino.
Um aeroporto é identificado por uma sigla de três letras, como "POA"ou
"RIO"e tem um nome.
Projete uma base de dados relacional que armazene os dados acima sem redundância de dados. Enumere as tabelas, suas colunas, as chaves primárias
e a as chaves estrangeiras. Não devem ser criadas colunas artificiais, além
das apresentadas no enunciado. Apresente o esquema na notação textual
vista em aula.
1
Solução:
Voo (CodVoo,SiglaTipoAero)
CodTipoAero referencia TipoAeronave
TipoAeronave (SiglaTipoAero,DescricaoTipoAero)
Trecho (CodVoo,NumTrecho,SiglaAeroSaida,HorarioSaida,
SiglaAeroChegada,HorarioChegada)
SiglaAeroSaida referencia Aeroporto
SiglaAeroChegada referencia Aeroporto
CodVoo referencia Voo
Aeroporto (SiglaAero,NomeAero)
2
2. A companhia de aviação referida na questão anterior já 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
(Tabela com as filiais onde acontece atendimento ao público)
Sobre esta base de dados, resolver as consultas que seguem usando álgebra
relacional:
3
a) (Peso 1) Um aeroporto está localizado em um país e atende cidades
deste país. Entretanto, sabe-se que podem ter ocorrido alterações na
base de dados que a levaram a um estado inconsistente quanto a este
requisito. Para cada par de cidades de diferentes países e que são
atendidas pelo mesmo aeroporto, obter o nome do aeroporto, seguido
dos nomes das cidades e respectivos nomes de país. Observar que o
mesmo par não deve constar duas vezes. Por exemplo, se no resultado
constar (Aeroporto X,Cordoba,Porto Alegre) não deve constar (Aeroporto X,Porto Alegre,Cordoba).
π Aeroporto.NomeAero,
Cidade1.NomeCidade,
Pais1.NomePais,
Cidade2.NomeCidade,
Pais2.NomePais
(
σ Pais1.CodPais=Cidade1.CodPais AND
Cidade1.CodCidade=Atendimento1.CodCidade AND
Atendimento1.SiglaAero=Aeroporto.SiglaAero AND
Pais2.CodPais=Cidade2.CodPais AND
Cidade2.CodCidade=Atendimento2.CodCidade AND
Atendimento2.SiglaAero=Aeroporto.SiglaAero
Pais1.CodPais<Pais2.CodPais
(
ρ Pais1 (Pais) ×
ρ Cidade1 (Cidade) ×
ρ Atendimento1 (Atendimento) ×
ρ Pais2 (Pais) ×
ρ Cidade2 (Cidade) ×
ρ Atendimento2 (Atendimento) ×
Aeroporto
)
)
ou
4
π Aeroporto.NomeAero,
Cidade1.NomeCidade,
Pais1.NomePais,
Cidade2.NomeCidade,
Pais2.NomePais
(
σ Pais1.CodPais=Cidade1.CodPais AND
Cidade1.CodCidade=Atendimento1.CodCidade AND
Atendimento1.SiglaAero=Aeroporto.SiglaAero AND
Pais2.CodPais=Cidade2.CodPais AND
Cidade2.CodCidade=Atendimento2.CodCidade AND
Atendimento2.SiglaAero=Aeroporto.SiglaAero
Pais1.CodPais<Pais2.CodPais
(
(ρ Pais1 (Pais) 1
ρ Cidade1 (Cidade) 1
ρ Atendimento1 (Atendimento) 1
)
1 (Atendimento2.SiglaAero=
Atendimento1.SiglaAero AND
Cidade1.CodCidade<
Cidade2.CodCidade
)
(ρ Pais2 (Pais) 1
ρ Cidade2 (Cidade) 1
ρ Atendimento2 (Atendimento) 1
)
1 (Atendimento1.SiglaAero)(SiglaAero)
Aeroporto
)
)
5
b) (Peso 1,5) Para cada filial localizada em uma cidade atendida pelo
aeroporto de sigla "YYC"e que fica a menos de 20 Km do aeroporto,
obter o endereço da filial.
i. Resolver usando produto cartesiano.
π EnderecoFilial
(σ Atendimento.CodCidade=Filial.CodCidade AND
Atendimento.SiglaAero=‘‘YYC’’ AND
Atendimento.DistanciaAeroCid<20
(Atendimento ×
Filial
)
ii. Resolver usando junções, de preferência junções naturais
π EnderecoFilial
(σ Atendimento.DistanciaAeroCid<20
Atendimento.SiglaAero=‘‘YYC’’ AND
(Atendimento 1
Filial
)
6
c) (Peso 1) Obter os nomes das cidades que não são atendidas por nenhum aeroporto.
π NomeCid
(
(π CodCid, NomeCid (Cidade)
)
−
(π CodCid, NomeCid (Cidade 1 Atendimento)
)
)
ou
π NomeCid
(
((π CodCid (Cidade))
−
(π CodCid (Atendimento))
)
1
Cidade
)
ou, ainda,
π NomeCid
(
σ Atendimento.CodCid IS NULL
(Cidade=1 Atendimento))
)
Observar que a consulta abaixo não é bem correta. O que acontece
quando duas cidades, uma atendida e outra não, têm o mesmo nome?
(π NomeCid (Cidade)
)
−
(π NomeCid (Cidade 1 Atendimento)
)
7
d) (Peso 1) 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.
(
π SiglaAero,NomeAero,CodPais
(Atendimento 1
Aeroporto
)
)
÷
(
π CodPais
(σ NomePais=’Brasil’
(Pais
)
)
)
8
3. Sobre a base de dados da questão precedente (Questão 2), expresse as seguintes consultas em SQL, sem o uso da sintaxe para junções explícitas,
nem sub-consultas (ainda não vistas em aula):
a) (Peso 1) Obter os nomes das cidades que não são atendidas por nenhum aeroporto com menos de 20 Km de distância.
SELECT NomeCidade
FROM
(SELECT CodCidade,NomeCidade
FROM Cidade
)
EXCEPT
(SELECT CodCidade,NomeCidade
FROM
Cidade, Atendimento
WHERE Cidade.CodCidade =
Atendimento.CodCidade
AND
DistanciaAeroCid < 20
)
)
b) (Peso 1) Obter o nome de cada aeroporto que existe no país de nome
"Brasil".
SELECT NomeAero
FROM
Aeroporto,
Atendimento,
Cidade,
Pais
WHERE Aeroporto.SigalAero=Atendimento.SiglaAero
AND Atendimento.CodCidade=Cidade.CodCidade
AND Cidade.CodPais=Pais.CodPais
AND NomePais=´Brasil´
9