prova2-2008

Prévia do material em texto

Prova de Fundamentos de Bancos de Dados
2a Prova
Prof. Carlos A. Heuser
Novembro de 2008
Prova com consulta a livros, apostilas e anotações
1 Base de dados para as questões
(Esta é a mesma base de dados que foi usada na primeira prova)
Considere a seguinte base de dados, usada por um banco de dados de informações sobre
genealogia de pessoas. Todas colunas, exceto as chaves primárias são opcionais.
/* tabela de localidades */
localidade (codigo_local, nome_local);
/* tabela com dados das pessoas – codigo_uniao_pais identifica a união que gerou
a pessoa */
pessoa (cod_pessoa, sexo, nome, sobrenome,
data_de_nascimento, codigo_local_nasc,
data_de_falecimento, codigo_local_falec,
codigo_uniao_pais);
(codigo_uniao_pais)
references uniao (codigo_uniao);
(codigo_local_nasc)
references localidade (codigo_local);
(codigo_local_falec)
references localidade (codigo_local);
/* tabela com as uniões entre pessoas */
uniao (codigo_uniao, codigo_local,
cod_pessoa_esposa, cod_pessoa_marido,
data_uniao);
(cod_pessoa_esposa)
references pessoa (cod_pessoa);
(codigo_local)
references localidade (codigo_local);
(cod_pessoa_marido)
references pessoa (cod_pessoa)
1
2 Questões
Todas questões têm o mesmo peso.
1. Expresse a consulta abaixo, usando cálculo relacional.
Obter os nomes das localidades, nas quais ocorreu ao menos uma união, mas não
ocorreu nenhum falecimento.
{r |
∃ l ∈ localidade (
r.nome_local=l.nome_local ∧
∃ u ∈ uniao (
u.codigo_local=l.codigo_local
) ∧
¬ ∃ p ∈ pessoa (
p.codigo_local_falec=l.cod_local_falec
)
)
}
2
2. Expresse a consulta abaixo em SQL.
Para cada pessoa na base de dados, obter seu código e seu nome, seguidos do nome
de seu pai. Caso o nome do pai não esteja na base de dados, este deve aparecer
vazio.
SELECT filho.cod_pessoa,
filho.nome,
pai.nome
FROM ( (pessoa AS filho
LEFT JOIN
uniao AS uniao_pai
ON
(filho.cod_uniao_pais=
uniao_pai.codigo_uniao)
)
LEFT JOIN
pessoa AS pai
ON
(uniao_pai.cod_pessoa_marido=
pai.cod_pessoa)
)
3
3. Expresse a consulta abaixo em SQL.
Sabe-se que, por um erro de programação, na base de dados, foram incluídas uniões
que de fato nunca existiram. A característica comum de cada uma destas uniões é
ter como filhos, todas pessoas nascidas em 1900-01-01.
Obter o código de cada união que tem como filhos todas pessoas nascidas em 1900-
01-01.
Solução:
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 quan-
tificador existencial.
SELECT codigo_uniao
FROM uniao
WHERE
NOT EXISTS
(SELECT *
FROM pessoa
WHERE data_nasc=1900-01-01 AND
codigo_uniao_pais<>codigo_uniao
)
Como cada pessoa somente pode ser filha de uma união, a solução mais simples é
obter uma união qualquer em que a pessoa tenha nascido em 1900-01-01:
SELECT codigo_uniao_pais
FROM pessoa
WHERE
data_nasc=1900-01-01
4
4. Expresse a consulta abaixo em SQL.
Obter uma tabela com quatro colunas:
a) o código e o nome de cada localidade,
b) o código e o nome de cada pessoa, que tenha nascido nesta localidade na data
1932-11-30.
Também localidades nas quais ninguém nasceu devem constar do resultado. Neste
caso, o código e o nome da pessoa devem aparecer vazios.
SELECT localidade.codigo_local,
localidade.nome_local,
pessoa.codigo_pess,
pessoa.nome
FROM localidade
LEFT JOIN
pessoa
ON
(localidade.codigo_local
=pessoa.codigo_local_nasc AND
pessoa.data_nasc=1932-11-30
)
A solução abaixo está incorreta, já que a cláusula WHERE anula o efeito da junção
externa.
SELECT localidade.codigo_local,
localidade.nome_local,
pessoa.codigo_pess,
pessoa.nome
FROM localidade
LEFT JOIN
pessoa
ON
(localidade.codigo_local
=pessoa.codigo_local_nasc
)
WHERE
pessoa.data_nasc=1932-11-30
5
5. Expresse a consulta abaixo em SQL.
Para cada localidade na qual nasceram ao menos três pessoas, obter o código e
nome da localidade, seguidos do número de pessoas que nela nasceram.
SELECT localidade.codigo_local,
nome_local,
COUNT(*)
FROM localidade
JOIN
pessoa
ON localidade.codigo_local
=pessoa.codigo_local_nasc
GROUP BY localidade.codigo_local, nome_local
HAVING COUNT(*)>=3
6
6. Expresse a consulta abaixo em SQL.
Obter o número de localidades, nas quais tenha nascido pelo menos uma pessoa.
SELECT count (distinct codigo_local_nasc)
FROM pessoa
7
7. Deseja-se que a base de dados garanta a seguinte restrição de integridade: Para cada
união, deve valer que, caso exista uma data da união informada, esta data deve ser
maior que a data de nascimento da pessoa que é marido na união (isto caso o marido
seja informado, e caso ele tenha data de nascimento informada).
Esta restrição pode ser implementada por um CHECK CONSTRAINT?
• Caso afirmativo, mostre o CHECK CONSTRAINT, que implementa esta res-
trição de integridade.
• Caso negativo, explique porque não é possível implementar a restrição através
de um CHECK CONSTRAINT e indique como esta restrição pode ser im-
plementada por triggers. Basta dizer para quais eventos (inclusão, exclusão,
alteração), de que tabelas, devem ser definidos triggers, bem como informar,
em Português, que erro o trigger deve detectar. Não é necessário mostrar o có-
digo SQL do trigger. Ao definir os triggers, lembrar que todas colunas, exceto
as chaves primárias podem ser vazias.
Solução:
A restrição envolve várias linhas e por isso não pode ser implementada por CHECK
CONSTRAINT requerendo implementação de um TRIGGER.
Devem ser definidos os seguintes triggers:
• Inclusão e alteração de pessoa: se tiver data de nascimento e for marido de
casamento, garantir que a data de nascimento seja menor que a da união.
• Inclusão e alteração de união: se a data da união e o código do marido forem
informados e se o marido tiver data de nascimento informada, garantir que
que a data de nascimento seja menor que a da união.
8