Prova de Fundamentos de Bancos de Dados2a 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 elaatendidas. 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 dis-tância em quilômetros da cidade ao aeroporto)

Filial (CdoFilial,EnderecoFilial, CodCidade)(CodCidade) referencia Cidade

1

2 QuestõesTodas 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 quetêm ao menos uma filial e que fica a menos que 10Km de um aeroporto.

SELECT NomeCidade, NomePaisFROM Cidade

NATURAL JOINPais

NATURAL JOINFilial

NATURAL JOINAtendimento

WHEREDistanciaAeroCid < 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, NomePaisFROM Cidade, PaisWHERE

Cidade.CodPais=Pais.CodPaisAND

CodCidade IN(SELECT CodCidadeFROM AtendimentoWHERE DistanciaAeroCid < 10

)AND

CodCidade IN(SELECT CodCidadeFROM 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 cidadesdeste país.

Em cálculo relacional, esta consulta seria resolvida com o quantificadoruniversal. Como não temos o mesmo em SQL, a consulta foi resolvidapela negação do quantificador existencial.

SELECT SiglaAero, NomeAeroFROM AeroportoWHERE

NOT EXISTS(SELECT *FROM CidadeWHERE CodPais IN

(SELECT CodPaisFROM PaisWHERE NomePais=’Brasil’

)ANDNOT EXISTS

(SELECT *FROM AtendimentoWHERE

CodCidade=Cidade.CodCidade ANDSiglaAero=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 umaeroporto,

b) o código e o nome de cada cidade atendida pelo mesmo aeroporto(caso não existam outras cidades atendidas pelo mesmo aeroporto, es-tas duas colunas devem aparecer vazias).

SELECT CidadePerto.CodCidade,CidadePerto.NomeCidade.CidadeOutra.CodCidade,CidadeOutra.NomeCidade

FROM (Cidade AS CidadePertoJOIN

Atendimento AS AtendPertoON(CidadePerto.CodCidade=AtendPerto.CodCidade ANDAtendPerto.DistanciaAeroCid<15

)LEFT JOIN

(Cidade AS CidadeOutraJOIN

Atendimento AS AtendOutroON(CidadeOutra.CodCidade=AtendOutro.CodCidade

))ON

(AtendPerto.SiglaAero=AtendOutro.SiglaAero ANDAtendPerto.CodCidade<>AtendOutro.CodCidade

)

5

5. Expresse a consulta abaixo em SQL.

Para cada aeroporto na base de dados, obter sua sigla e seu nome, seguidodo número de cidades por ele atendidas e da média da distância das cidadespor ele atendidas. Aeroportos que não atendem nenhuma cidade devemigualmente aparecer no resultado.

SELECT SiglaAero,NomeAero,COUNT(CodCidade),AVG(DistanciaAeroCid)

FROM AeroportoNATURAL LEFT JOIN

AtendimentoGROUP BY SiglaAero, NomeAero

6

6. Expresse a consulta abaixo em SQL.

Para cada cidade que é atendida por um aeroporto, obter o nome da cidademais próxima ao aeroporto.

SELECT CidadeMaisProx.NomeCidadeFROM Cidade AS CidadeMaisProx

NATURAL LEFT JOINAtendimento AS AtendimentoMaisProx

WHEREAtendimentoMaisProx.DistanciaAeroCid =

(SELECT MIN (DistanciaAeroCid)FROM AtendimentoWHERE 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 JOINCidadeNATURAL JOIN

AtendimentoGROUP BY CodPais, NomePaisHAVING 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 mesmopaís.

Responda às seguintes questões:

a) Esta restrição já está implementada na base de dados através de umachave primária ou estrangeira? Caso afirmativo, de que forma estáimplementada?As restrições que existem na base de dados não implementam estarestrição.

b) Esta restrição pode ser implementada por um CHECK CONSTRAINT?Caso afirmativo, mostre o CHECK CONSTRAINT, caso negativo ex-plique porque não é possível implementar a restrição desta forma emostre como esta restrição pode ser implementada por TRIGGERs.A restrição envolve várias linhas e por isso não pode ser implemen-tada por CHECK CONSTRAINT requerendo implementação de umTRIGGER.

CREATE TRIGGER ValidaPaisAeroportoBEFORE INSERT OR UPDATE ON AtendimentoREFERENCING

NEW AS NovoAtendimentoFOR EACH ROWWHERE

( Atendimento.SiglaAero IN(SELECT OutroAtendimento.SiglaAeroFROM Atendimento AS OutroAtendimento,

Cidade AS OutraCidade,Cidade

WHEREOutraCidade.CodCidade=OutroAtendimento.CodCidade ANDCidade.SiglaAero=NovoAtendimento.SiglaAero ANDCidade.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.CodPaisAND Cidade.CodCidade=Atendimento.CodCidadeAND Atendimento.DistanciaAeroCid = 5

(Pais ×(Cidade × Atendimento)

))

Represente esta consulta em árvore e mostre o resultado de cada um dospassos 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

×aaaa

!!!!Pais ×

HHH©©©

Cidade Atendimento

2. Mover seleções para as folhas

π Pais.CodPais, Pais.NomePais, Atendimento.SiglaAero

σ Pais.CodPais=Cidade.CodPais

×̀`````

ÃÃÃÃÃÃPais σ Cidade.CodCidade=Atendimento.CodCidade

×XXXXXX»»»»»»

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.CodCidadeXXXXXX»»»»»»

Cidade σ Atendimento.DistanciaAeroCid = 5

Atendimento

4. Projeções são movidas para as folhas

π Pais.CodPais, Pais.NomePais, Atendimento.SiglaAero

1 Pais.CodPais=Cidade.CodPaishhhhhhhhh(((((((((

π CodPais, NomePais

Pais

π Cidade.CodPais, Atendimento.SiglaAero

1 Cidade.CodCidade=Atendimento.CodCidadehhhhhhh(((((((

π CodCidade,CodPais

Cidade

π CodCidade,SiglaAero

σ Atendimento.DistanciaAeroCid = 5

Atendimento

11