Prova 2 - Inf

Prova de Fundamentos de Bancos de Dados
2a Prova
Prof. Carlos A. Heuser
Maio de 2006
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 imobiliária 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 à venda e vendidos – AnoImovel corresponde ao
ano de construção do Imóvel)
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)
(Tabela que contém dados dos corretores)
1
2 Questões
1. Sobre esta base de dados, expresse as seguintes consultas em cálculo relacional (caso sejam usadas variáveis de tupla com nome diferente do nome
da tabela, informar quais são)
a) (peso=8/7) Obter os nomes das pessoas que compraram ao menos um
imóvel do corretor denominado "Antenor Antunes".
Pessoa.NomePess WHERE
∃ Venda (
Venda.CIC = Pessoa.CIC AND
∃ Corretor (
Corretor.CodCorretor = Venda.CodCorretor AND
Corretor.NomeCorretor = ’Antenor Antunes’
)
)
2
b) (peso=8/7) Obter os nomes dos corretores que, em 2006 (DataVenda
maior ou igual a 20060101 e DataVenda menor ou igual a 20061231),
não venderam imóveis para compradores de sexo feminino (Sexo="F").
Corretor.NomeCorretor WHERE
NOT ∃ Venda (
Venda.CodCorretor = Corretor.CodCorretor AND
Venda.DataVenda >= 20060101 AND
Venda.DataVenda <= 20061231 AND
∃ Pessoa
(Pessoa.CIC = Venda.CIC AND
Pessoa.Sexo = ’F’
)
)
Observação: a consulta abaixo, que apareceu em várias provas, tem
um resultado diferente: Obter os nomes de corretores que venderam
imóveis em 2006 para pessoas que não sejam de sexo feminino. A
diferença está no fato de que na solução abaixo, o corretor tem que ter
tido ao menos uma venda em 2006, o que não é exigido pelo enunciado
original.
Corretor.NomeCorretor WHERE
∃ Venda (
Venda.CodCorretor = Corretor.CodCorretor AND
Venda.DataVenda >= 20060101 AND
Venda.DataVenda <= 20061231 AND
NOT ∃ Pessoa
(Pessoa.CIC = Venda.CIC AND
Pessoa.Sexo = ’F’
)
)
3
2. (peso=8/7) Expresse a consulta abaixo em SQL usando a sintaxe explícita
para junções.
Obter os nomes das pessoas que compraram algum imóvel construído em
1999 através do corretor denominado "Antenor Antunes"
SELECT NomePess
FROM
Pessoa
NATURAL JOIN
Venda
NATURAL JOIN
Imovel
NATURAL JOIN
Corretor
WHERE
Imovel.AnoImovel = 1999 AND
Corretor.CorretorImovel = ’Antenor Antunes’
4
3. (peso=8/7) Expresse a consulta abaixo em SQL.
Obter os nomes das pessoas que já compraram imóveis de todos corretores
cadastrados na base de dados.
SELECT NomePess
FROM Pessoa
WHERE
NOT EXISTS
(SELECT * FROM Corretor
WHERE
NOT EXISTS
(SELECT * FROM Venda
WHERE Venda.CIC = Pessoa.CIC AND
Venda.CodCorretor = Corretor.CodCorreto
)
)
4. (peso=8/7) Expresse a consulta abaixo em SQL.
Obter uma tabela com duas colunas, compostas do nome de uma pessoa de
sexo feminino, seguido do nome de seu marido, caso a pessoa tenha marido
e este tenha nascido em 1961-01-01. Caso a pessoa não tenha marido ou
este tenha nascido em outra data, a segunda coluna deve aparecer vazia.
SELECT Esposa.NomePess,
Marido.NomePess
FROM Pessoa AS Esposa
LEFT JOIN
Pessoa AS Marido
ON
(Esposa.EsposaDeCIC = Marido.CIC AND
Marido.DataNasc = 1961-01-01 AND
Esposa.Sexo = ’F’
)
5
5. (peso=8/7) Expresse a consulta abaixo em SQL.
Obter o número de diferentes corretores que já realizaram vendas.
SELECT COUNT(*)
FROM Corretor
WHERE CodCorretor IN
(SELECT CodCorretor
FROM Vendas
)
ou ainda
SELECT COUNT(DISTINCT Venda.CodCorretor)
FROM Venda
6. (peso=8/7) Expresse a consulta abaixo em SQL.
Para cada corretor que, em 2006, vendeu mais que três imóveis, obter seu
código, seu nome, o número de imóveis por ele vendidos em 2006, bem
como o valor total (soma dos preços) dos imóveis por ele vendidos em 2006.
SELECT Corretor.CodCorretor,
Corretor.NomeCorretor,
COUNT(*) AS NoDeImoveisVendidos,
SUM(PrecoImovel) as ValorTotalVendas
FROM Corretor
NATURAL JOIN
Venda
NATURAL JOIN
Imovel
WHERE Venda.DataVenda >= 20060101 AND
Venda.DataVenda <= 20061231
GROUP BY Corretor.CodCorretor,
Corretor.NomeCorretor
HAVING COUNT(*)>3
6
7. (peso=2) Deseja-se que a base de dados garanta as seguintes restrições de
integridade:
a) Uma pessoa cadastrada como esposa, isto é, que tem o campo EsposaDeCIC
preenchido, deve ser de sexo feminino (’F’).
b) Uma pessoa cadastrada como marido, isto é, que é referenciada pelo
campo EsposaDeCIC de algum registro, deve ser de sexo masculino
(’M’).
Para cada uma destas restrições, responda às seguintes questões:
a) Esta restrição já está implementada na base de dados? Caso afirmativo,
qual é a restrição que a implementa?
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 a) pode ser implementada por CHECK CONSTRAINT,
visto que para validá-la basta observar uma linha da tabela de pessoas:
ALTER TABLE Pessoa
ADD CONTRAINT ValidaEsposa
CHECK
(EsposaDeCIC IS NULL
OR
Sexo = ’F’
)
7
• Já a restrição b) envolve duas linhas de pessoa (marido e esposa) e
por isso não pode ser implementada por CHECK CONSTRAINT
requerendo implementação de um TRIGGER.
CREATE TRIGGER ValidaMarido
BEFORE INSERT OR UPDATE ON Pessoa
REFERENCING
NEW AS PessoaInclOuAlt
FOR EACH ROW
WHERE
/* pessoa inluída ou alterada é marido
e não é de sexo masculino */
( PessoaInclOuAlt.CIC IN
(SELECT EsposaDeCIC FROM Pessoa)
AND
( PessoaInclOuAlt.Sexo <> ’M’
)
)
OR
/* pessoa inluída ou alterada referencia
alguém como marido e este
não é de sexo masculino */
( EXISTS
(SELECT *
FROM Pessoa AS Marido
WHERE Marido.CIC =
PessoaIncOuAlt.EsposaDeCIC
AND
( Sexo <> ’M’
)
)
)
( sinalizar erro
’Marido têm que ser de sexo masculino’
)
8