Prova 1 - Inf

Prova de Fundamentos de Bancos de Dados
1a Prova
Prof. Carlos A. Heuser
Setembro de 2008
Prova sem consulta – duas horas de duração
1. (Peso 2)
Deseja-se projetar um banco de dados para um sítio de relacionamento. Neste sítio
estão armazenadas informações sobre os usuários, seus relacionamentos e mensagens entre eles trocadas.
Para cada usuário, é necessário conhecer o seu nome de usuário (identificador de
usuário), sua senha, seu nome real e seu endereço de correio eletrônico, bem como
sua lista de relacionamentos, ou seja, os usuários a ele relacionados. Para montar
sua lista de relacionamentos, o usuário pode convidar quaisquer outros usuários.
Na lista de relacionamentos de um usuário, um outro usuário aparece com um dos
seguintes status: ’convidado’, ’ativo’ ou ’desativado’. Observar que, o fato de um
usuário X aparecer na lista de relacionamentos de um usuário Y, não implica no
relacionamento contrário, ou seja no fato de Y aparecer na lista de X.
Para cada mensagem, é necessário saber o usuário remetente, o usuário destinatário,
o assunto da mensagem, a data e hora da mensagem, o corpo da mensagem e, caso a
mensagem seja resposta a uma outra mensagem, a mensagem da qual a mensagem
em questão é resposta. As mensagens são identificadas por um código numérico
interno ao sistema. Observar que uma determinada mensagem aparece duas vezes
para os usuários, uma vez, na lista de mensagens enviadas pelo remetente e outra
vez, na lista de mensagens recebidas pelo destinatário. Mesmo assim, a mensagem
deve ser armazenada uma vez somente.
Projete uma base de dados relacional para armazenar 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 ou diagramática vista em aula.
Solução:
Usuario (id_us, senha_us, nome_us, correio_us)
Relacionamento (id_us, id_us_rel, status)
(id_us) referencia Usuario
(id_us_rel) referencia Usuario
Mensagem (no_msg, id_us_origem, id_us_destino, data, hora
assunto, corpo, no_msg_resposta)
(id_us_origem) referencia Usuario
1
(id_us_destino) referencia Usuario
(no_msg_resposta) referencia Mensagem
2. Considere a seguinte base de dados, usada por um banco de dados de informações
sobre genealogia de pessoas (mesma base de dados vista em aula para exercícios).
/* 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)
2
Sobre esta base de dados, resolver as consultas que seguem usando álgebra relacional:
a) (Peso 1,33. . . )
Por um erro de software, um grande de número de uniões incorretas foi cadastrado na base de dados. Estas uniões têm a característica de ligar uma pessoa
como marido de todas pessoas da localidade denominada "Guanxuma".
Obter os códigos das pessoas que são marido de todas pessoas da localidade
"Guanxuma".
Solução:
π COD_PESSOA_ESPOSA, COD_PESSOA_MARIDO (UNIAO)
÷
(ρ PESSOA-GUNAXUMA (COD_PESSOA_ESPOSA)
(π COD_PESSOA
(σ (PESSOA.CODIGO_LOCAL_NASC=
LOCALIDADE.CODIGO_LOCAL AND
LOCALIDADE.NOME_LOCAL = ’Guanxuma’
) (PESSOA X LOCALIDADE)
)
)
)
3
b) (Peso 1,33. . . )
Obter os nomes das localidades, tal que na localidade tenha nascido uma pessoa em 12/12/1900 gerada por uma união ocorrida em 01/01/1900.
i. Resolver usando produto cartesiano.
Solução:
π LOCALIDADE.NOME_LOCAL
(σ LOCALIDADE.COD_LOCAL = PESSOA.COD_LOCAL_NASC AND
PESSOA.COD_UNIAO_PAIS = UNIAO.COD_UNIAO AND
PESSOA.DATA_NASC = 12/12/1900 AND
UNIAO.DATA_UNIAO = 01/10/1900
(UNIAO ×
LOCALIDADE ×
PESSOA
)
)
ii. Resolver usando junções. Se possível, usar junção natural, senão, usar
equi-junção e em último caso usar theta-junção.
Solução:
Observar que não é possível usar junções naturais, já que não há as colunas homônimas.
π LOCALIDADE.NOME_LOCAL
(σ PESSOA.DATA_NASC = 12/12/1900 AND
UNIAO.DATA_UNIAO = 01/10/1900
(UNIAO 1 (COD_UNIAO)
(COD_UNIAO_PAIS)
(LOCALIDADE 1 (COD_LOCAL)(COD_LOCAL_NASC)
PESSOA
)
)
)
4
c) (Peso 1,33. . . )
Obter os códigos e nomes das localidades que não estão em uso, ou seja, nas
quais não houve nem nascimentos, nem falecimentos, nem uniões.
Solução:
(π COD_LOCAL (LOCALIDADE)
−
(
(π COD_LOCAL_NASC (PESSOA))
∪
(π COD_LOCAL_FALEC (PESSOA))
∪
(π COD_LOCAL_UNIAO (UNIAO))
)
)
1
LOCALIDADE
d) (Peso 1,33. . . )
Obter uma tabela contendo as seguintes colunas:
i. Código e nome de cada localidade;
ii. Código e nome de cada mulher (PESSOA.SEXO = ’F’) nascida na localidade (se na localidade não houver nascimentos de mulheres, estas colunas
devem aparecer em branco);
iii. Código de cada marido desta mulher (se a pessoa não tiver maridos, esta
coluna deve aparecer vazia).
Solução:
π LOCALIDADE.COD_LOCAL,
LOCALIDADE.NOME_LOCAL,
PESSOA.COD_PESSOA,
PESSOA.NOME_PESSOA,
UNIAO.COD_PESSOA_MARIDO
(LOCALIDADE
=1 (COD_LOCAL)(COD_LOCAL_NASC)
(
(σ (PESSOA.SEXO = ’F’)
(PESSOA)
)
=1 (COD_PESSOA)(COD_PESSOA_ESPOSA)
UNIAO
)
)
5
3. (Peso 1,33. . . )
Sobre a base de dados da questão precedente (Questão 2), expresse a seguinte consulta em SQL, sem o uso da sintaxe para junções explícitas, nem sub-consultas
(ainda não vistas em aula). Na consulta, devem aparecer apenas as tabelas necessárias.
Obter o nome de cada pessoa que nasceu e faleceu em duas localidades diferentes,
mas cujo nome é igual.
Solução:
SELECT
PESSOA.NOME_PESSOA
FROM PESSOA AS P,
LOCALIDADE AS LN,
LOCALIDADE AS LF
WHERE
P.COD_LOCAL_NASC <> P.COD_LOCAL_FALEC AND
P.COD_LOCAL_NASC = LN.COD_LOCAL AND
P.COD_LOCAL_FALEC = LF.COD_LOCAL AND
LN.NOME_LOCAL = LF.NOME_LOCAL
6
4. (Peso 1,33. . . )
Considere a seguinte consulta em SQL:
SELECT NOME_LOCALIDADE
FROM PESSOA,
UNIAO,
LOCALIDADE
WHERE
PESSOA.COD_UNIAO_PAIS = UNIAO.COD_UNIAO AND
LOCALIDADE.COD_LOCAL = UNIAO.COD_LOCAL_UNIAO AND
PESSOA.SEXO = ’M’ AND
UNIAO.DATA_UNIAO = ’01/07/1750’
Mostre a consulta equivalente em álgebra relacional, depois, mostre a representação
da consulta em forma de arvore e após, mostre cada um dos passos da otimização
algébrica.
Solução:
Consulta em álgebra relacional (a ordem dos produtos cartesianos foi arbitrada;
outras ordens são equivalente e levam a uma consulta diferente com o mesmo resultado):
π NOME_LOCALIDADE
σ
PESSOA.COD_UNIAO_PAIS = UNIAO.COD_UNIAO AND
LOCALIDADE.COD_LOCAL = UNIAO.COD_LOCAL_UNIAO AND
PESSOA.SEXO = ’M’ AND
UNIAO.DATA_UNIAO = ’01/07/1750’
( PESSOA
×
(UNIAO
×
LOCALIDADE
)
)
7
Representação da consulta em árvore:
π NOME_LOCALIDADE
σ PESSOA.COD_UNIAO_PAIS = UNIAO.COD_UNIAO
σ LOCALIDADE.COD_LOCAL = UNIAO.COD_LOCAL_UNIAO
σ PESSOA.SEXO = ’M’
σ UNIAO.DATA_UNIAO = ’01/07/1750’
×
!aa
!!
a
!
a
PESSOA
×
©©HH
©
H
UNIAO
LOCALIDADE
Passo 1: Mover seleções para as folhas:
π NOME_LOCALIDADE
σ PESSOA.COD_UNIAO_PAIS = UNIAO.COD_UNIAO
×
(((hhhhhh
(((
hhhh
(
(
((
σ PESSOA.SEXO = ’M’
σ LOCALIDADE.COD_LOCAL = UNIAO.COD_LOCAL_UNIAO
PESSOA
×
ÃÃÃ`````
Ã
Ã
Ã
`
σ UNIAO.DATA_UNIAO = ’01/07/1750’
LOCALIDADE
UNIAO
Passo 2: Substituir produtos cartesianos por junções:
π NOME_LOCALIDADE
1(PESSOA.COD_UNIAO_PAIS = UNIAO.COD_UNIAO)
((hhhhh
hhhh
h
(((
(
((((
σ PESSOA.SEXO = ’M’
PESSOA
1(LOCALIDADE.COD_LOCAL = UNIAO.COD_LOCAL_UNIAO)
ÃÃÃ`````
ÃÃÃ
`
σ UNIAO.DATA_UNIAO = ’01/07/1750’
UNIAO
8
LOCALIDADE
Passo 3: Mover projeções para as folhas:
π LOCALIDADE.NOME_LOCALIDADE
1(PESSOA.COD_UNIAO_PAIS = UNIAO.COD_UNIAO)
((((hhhhhhh
((
((((
(
hhh
h
π PESSOA.COD_UNIAO_PAIS
π UNIAO.COD_UNIAO, LOCALIDADE.NOME_LOCALIDADE
σ PESSOA.SEXO = ’M’
1(LOCALIDADE.COD_LOCAL = UNIAO.COD_LOCAL_UNIAO)
ÃÃÃ`````
`
ÃÃÃ
`
Ã
PESSOA
π UNIAO.COD_UNIAO,
UNIAO.COD_LOCAL_UNIAO
σ UNIAO.DATA_UNIAO = ’01/07/1750’
UNIAO
9
π LOCALIDADE.COD_LOCAL,
LOCALIDADE.NOME_LOCALIDADE
LOCALIDADE