Prova 1 - Inf

Prova de Fundamentos de Bancos de Dados
1a Prova
Prof. Carlos A. Heuser
Abril de 2007
Prova sem consulta – duas horas de duração
1. (Peso 2,5) Uma imobiliária deseja montar uma base de dados contendo os
dados de imóveis que tem para vender ou que já vendeu. Na base de dados somente estarão armazenados dados de apartamentos residenciais em
edifícios.
Cada edifício é identificado por um código de 5 dígitos e é necessário saber
seu nome, seu endereço, o bairro em que está localizado e o ano de construção, bem como os apartamentos que estão a venda ou já foram vendidos.
Para cada apartamento, além de saber se ele já foi ou não vendido, é necessário conhecer seu preço, sua área total, sua área real, bem como o seu
número identificador dentro do prédio.
Para cada apartamento é necessário conhecer os quartos que o compõe.
Cada quarto tem um número que o identifica dentro do apartamento, tem
uma descrição (como "sala"ou "cozinha") e é necessário conhecer sua área.
Alguns apartamentos estão relacionados a uma ou mais vagas de estacionamento. Uma vaga tem um número que a identifica dentro do prédio (atenção, não é um número que identifica a vaga dentro do apartamento). Para
cada vaga é necessário saber sua área, quantos veículos nela cabem e o seu
tipo (se é coberta ou não).
Projete uma base de dados relacional que armazena 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
Edificio (CodEd,NomeEd,EnderecoEd,
BairroEd,AnoConstrEd)
Apartamento (CodEd,NumeroAp,VendidoAp,PrecoAp,
AreaTotalAp,AreaRealAp)
(CodEd) referencia Edificio
Quarto (CodEd,NumeroAp,NumeroQuarto,
DescricaoQuarto,AreaQuarto)
(CodEd,NumeroAp) referencia Apartamento
VagaEst (CodEd,NoVaga,NoAp,AreaVaga,NoCarrosVaga,
CobertaVaga)
(CodEd,NumeroAp) referencia Apartamento
2. A imobiliária referida na questão anterior já possui uma base de dados de
clientes (vendedores e compradores de imóveis. Esta base de dados contém
as seguintes tabelas:
Pessoa (CIC,NomePess,Sexo,DataNasc,EsposaDeCIC)
(EsposaDeCIC) referencia Pessoa
(Tabela com dados dos clientes. Em caso de clientes casados em comunhão
de bens, ambos estão registrados na tabela, sendo que as esposas contém
uma referencia para seus maridos)
Imóvel (CodImovel,DescricaoImovel,PrecoImovel,AnoImovel)
(Tabela com dados dos imóveis a venda e vendidos)
Venda (CodImovel,CIC,DataVenda,CodCorretor)
(CodImovel) referencia Imovel
(CIC) referencia Pessoa
(CodCorretor) referencia Corretor
(Tabela que contém dados das vendas. Quando a venda for para um casal,
apenas um deles aparece nesta tabela)
Corretor (CodCorretor,NomeCorretor)
2
(Tabela que contém dados dos corretores)
Sobre esta base de dados, resolver as consultas que seguem usando álgebra
relacional:
a) (Peso 1,5) Para cada imóvel adquirido pelo cliente de nome ‘José
Silva’, sexo ‘M’, obter o código do imóvel, sua descrição, a data de
venda e o nome do corretor que realizou a venda.
i. Resolver usando produto cartesiano.
π Imovel.CodImovel,
Imovel.DescricaoImovel,
Venda.DataVenda,
Corretor.NomeCorretor
(σ Pessoa.CIC
= Venda.CIC AND
Venda.CodImovel
= Imovel.CodImovel AND
Venda.CodCorretor
= Corretor.CodCorretor
Pessoa.NomePess
= ’José Silva’ AND
Pessoa.Sexo
= ’M’
(Pessoa ×
Imovel ×
Venda ×
Corretor
)
3
ii. Resolver usando junções, de preferência junções naturais
π Imovel.CodImovel,
Imovel.DescricaoImovel,
Venda.DataVenda,
Corretor.NomeCorretor
(σ Pessoa.NomePess
= ’José Silva’ AND
Pessoa.Sexo
= ’M’
(Pessoa 1
(Imovel 1
(Venda 1
Corretor
)
)
)
4
b) (Peso 1) Obter os nomes das pessoas que não adquiriram nenhum imóvel.
π NomePess
(Pessoa 1
( (π CIC (Pessoa))
−
(π CIC (Venda))
)
)
ou
(π CIC,NomePess
(Pessoa))
−
(π Venda.CIC,NomePess
(σ Venda.CIC = Pessoa.CIC
(Pessoa × Venda)
)
)
ou então (dependendo como se interpreta o enunciado, esta solução
abaixo não está correta – caso existam duas pessoas com o mesmo
nome, uma que fez compra e outra que não, elas iriam desaparecer do
resultado, enquanto que na primeira solução a pessoa iria aparecer)
(π NomePess
(Pessoa))
−
(π NomePess
(σ Venda.CIC = Pessoa.CIC
(Pessoa × Venda)
)
)
5
c) (Peso 1) Obter uma tabela com cinco colunas, cada linha correspondendo a uma venda de um imóvel de mais de R$300.000,00. A tabela
deve conter o CIC da pessoa que comprou o imóvel, o nome da pessoa,
o código do imóvel, sua descrição e a data de venda. Caso uma pessoa
não tenha adquirido nenhum imóvel nesta condição, as três últimas
colunas devem aparecer vazias.
π Pessoa.CIC,
Pessoa.NomePess,
Venda.CodImovel,
Imovel.DescrImovel,
Venda.DataVenda
(Pessoa
=1
( Venda
1
(σ PrecoImovel > 300000(Imovel))
)
)
ou
π Pessoa.NumeroCartao,
π Pessoa.CIC,
Pessoa.NomePess,
Venda.CodImovel,
Imovel.DescrImovel,
Venda.DataVenda
(
(Pessoa =1 Venda)
=1
(σ PrecoImovel > 300000(Imovel))
)
6
d) (Peso 1) Obter os nomes das pessoas que adquiriram imóveis de todos
corretores.
π Pessoa.Nomepess
(Pessoa
1
((π CIC,CodCorretor (Venda))
÷
(π CodCorretor (Corretor))
)
)
7
e) (Peso 1) Para cada imóvel vendido a um casal, obter o código do imóvel, seguido do CIC e nome do cônjuge que o adquiriu, seguido do
CIC e nome do outro cônjuge. Uma venda a casal ocorre quando a
venda é realizada para uma pessoa que tem cônjuge (marido ou esposa) registrado na base de dados. Observar que a venda tanto pode
estar registrada para o marido, quanto para a esposa.
(π Venda.CodImovel,
Comprador.CIC,
Comprador.NomePess,
Conjuge.CIC,
Conjuge.NomePess
(Venda 1 (Venda.CIC)(Comprador.CIC)
(
(ρ Comprador (Pessoa))
1
(Pessoa.EsposaDeCIC = Conjuge.CIC
OR
Pessoa.CIC = Conjuge.EsposaDeCIC
)
(ρ Conjuge (Pessoa))
)
)
)
ou
8
(π Venda.CodImovel,
Comprador.CIC,
Comprador.NomePess,
Conjuge.CIC,
Conjuge.NomePess
(Venda 1 (Venda.CIC)(Comprador.CIC)
(
(ρ Comprador (Pessoa))
1 (Pessoa.EsposaDeCIC)(Conjuge.CIC)
(ρ Conjuge (Pessoa))
)
)
)
∪
(π Venda.CodImovel,
Comprador.CIC,
Comprador.NomePess,
Conjuge.CIC,
Conjuge.NomePess
(Venda 1 (Venda.CIC)(Comprador.CIC)
(
(ρ Comprador (Pessoa))
1 (Pessoa.CIC)(Conjuge.EsposaDeCIC)
(ρ Conjuge (Pessoa))
)
)
)
9
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 o nome de cada corretor que tenha vendido um imóvel
para o marido de uma mulher nascida em 12/12/1972.
SELECT NomeCorretor
FROM
Corretor,
Venda,
Pessoa as Marido,
Pessoa as Esposa
WHERE Corretor.CodCorretor = Venda.CodCorretor AND
Venda.CIC = Marido.Cid AND
Esposa.EsposaDeCIC = Marido.CIC AND
Esposa.DataNas = ’12/12/1972’
b) (Peso 1) Obter os nomes dos corretores que não venderam nenhum
imóvel de mais de R$200.000,00.
π NomeCorretor(
(SELECT CodCorretor, NomeCorretor
FROM Corretor
)
EXCEPT
(SELECT CodCorretor, NomeCorretor
FROM
Corretor,
Venda,
Imovel
WHERE Corretor.CodCorretor = Venda.Corretor AND
Venda.CodImovel = Imovel.CodImovel AND
Venda.PrecoImovel > 200000
)
)
10