Prova de Fundamentos de Bancos de Dados - Inf

Prova de Fundamentos de Bancos de Dados
1a Prova
Prof. Carlos A. Heuser
Abril de 2009
Prova sem consulta – duas horas de duração
1. (Peso 2)
Deseja-se projetar um banco de dados para o sítio de uma prefeitura. Neste sítio,
estão armazenadas informações sobre a rede de ruas de uma cidade.
Para cada rua, o banco de dados deve conter o nome atual da rua, e as coordenadas
geográficas (latitude e longitude) do início e do fim da rua. Cada rua possui um
identificador único. Para algumas ruas que mudaram de nome ao longo de tempo,
é necessário saber cada um de seus nomes antigos. Deve ser observada que uma
determinada rua pode ter tido vários nomes no passado. Não há limite superior
para o número de nomes que uma rua teve no passado.
Além de dados das ruas, o banco de dados deve conter dados sobre os cruzamentos
entre as ruas. Cada cruzamento tem um identificador único e situa-se em uma
determinada latitude e longitude. Em cada cruzamento, encontram-se várias ruas.
Para cada rua que faz parte do cruzamento é necessário conhecer o número, na
rua, em que passa o cruzamento. Exemplificando, um cruzamento poderia ser o
encontro da rua de nome "Rua do Arvoredo", no número 420, com a rua de nome
"Avenida do Parque", no número 100. Deve ser observado que, em um cruzamento,
podem encontrar-se mais que duas ruas (não há limite definido)
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. Caso necessário, podem ser criadas chaves primárias artificiais. Apresente o esquema na notação textual ou diagramática vista em aula.
Solução:
rua (rua_id, nome_rua, estensao_rua,
lat_inicio, long_incio, lat_fim, long_fim)
rua_nome_antigo (rua_id, no_nome, nome_rua)
(rua_id) referencia rua
cruzamento (cruz_id, lat_cruz, long_cruz)
rua_cruzamento (cruz_id,rua_id, nummero_na_rua)
1
2. Considere a seguinte base de dados, usada por um banco de dados de um software
de gerência de clínicas médicas.
/* tabela de clínicas médicas */
Clinica(CodCli,NomeCli,LocalCli)
/* tabela de médicos */
Medico(CodMed,NomeMed,CodEspeci)
CodEspeci referencia Especialidade
/* tabela que relaciona médicos a clínicas */
ClinicaMedico(CodCli,CodMed)
CodCli referencia Clinica,
CodMed referencia Medico
/* tabela coma agenda das consultas dos médicos */
AgendaConsulta(CodCodCli,CodMed,Data,Hora)
(CodCli,CodMed) referencia ClinicaMedico
/* tabela com as especialidades que os médicos podem ter - para cada especialidade,
pode ser indicada uma especialidade mais genérica (por exemplo ’cirurgia geral’
pode ser uma especialidade mais genérica que ’cirurgia pediátrica’); isto é indicado
pela coluna CodEspeciGenerica*/
Especialidade(CodEspeci,Nome,CodEspeciGenerica)
CodEspeciGenerica
referencia Especialidade
2
Sobre esta base de dados, resolver as consultas que seguem usando álgebra relacional:
2.a (Peso 2)
Obter o nome das clínicas que tem médicos que atuam na especialidade denominada ’Geriatria’.
i. Resolver sem usar os operadores explícitos de junção.
Solução:
π NomeCli
(σ Clinica.CodCli=ClinicaMedico.CodCli AND
ClinicaMedico.CodMed=Medico.CodMed AND
Medico.CodEspeci=Especialidade.CodEspeci AND
Especialidade.Nome=’Geriatria’
(Clinica ×
ClinicaMedico ×
Medico ×
Especialidade
)
)
ii. Resolver usando operadores explícitos de junção. Preferir o uso de junção
natural, senão, usar equi-junção e em último caso usar theta-junção.
Solução:
Como as junções são por colunas homônimas, dá para usar a junção natural.
π NomeCli
(σ Especialidade.Nome=’Geriatria’
(((Clinica
n
o
ClinicaMedico
)
n
o
Medico
)
n
o
Especialidade
)
)
3
2.b (Peso 1,2)
Obter os códigos dos médicos que atuam em todas clínicas cadastradas no
banco de dados.
Solução:
(π CodCli,CodMed (ClinicaMedico)
)
÷
(π CodCli (Clinica)
)
2.c (Peso 1,2)
Obter os códigos e nomes dos médicos que estão livres no dia 28/04/2009.
’Estar livre’ na data significa que, para aquele médico, não há consulta na
agenda nesta data.
Solução:
(π CodMed,
NomeMed
(Medico)
)
−
(π CodMed,
NomeMed
(σ Data=28/04/2009
(Medico
o
n
AgendaConsulta
)
)
)
4
2.d (Peso 1,2. . . )
Obter uma tabela contendo as seguintes colunas:
i. Código e nome de cada clínica;
ii. Nome de cada médico da clínica, que atua na especialidade denominada
’Obstetrícia’.
Caso a clínica não tiver médicos ou, caso ela não tiver nenhum médico atuando
nesta especialidade, o código e o nome da clínica devem aparecer seguidos de
vazio.
Solução:
(π CodCli, NomeCli, NomeMed
(Clinica
-o
n
(ClinicaMedico
n
o
(Medico
n
o (Especialidade.CodEspeci=MedicoCodEspeci AND
Especialidade.Nome=’Obstetrícia’
)
Especialidade
)
)
)
Observar que a seleção por nome de especialidade não pode ser realizada depois da junção externa, pois desfaz o efeito desta.
Observar que, caso a ordem das junções for diferente, pode ser necessário
fazer duas junções naturais:
(π CodCli, NomeCli, NomeMed
(( (Clinica
-n
o
ClinicaMedio
)
n
-o
Medico
)
-n
o (Especialidade.CodEspeci=MedicoCodEspeci AND
Especialidade.Nome=’Obstetrícia’
)
Especialidade
)
)
5
2.e (Peso 1,2. . . )
Para cada médico que atua em duas clínicas diferentes, obter o nome do médico seguido do nome das clínicas.
Se um médico atua em mais de duas clínicas, todos os pares formados por
essas clínicas devem aparecer no resultado.
Solução:
(π Medico.NomeMed,
Clinica1.NomeCli,
Clinica2.NomeCli
(σ Medico.CodMed=ClinicaMedico1.CodMed AND
Medico.CodMed=ClinicaMedico2.CodMed AND
ClinicaMedico1.CodCli<>ClinicaMedico2.CodCli AND
ClinicaMedico1.CodCli=Clinica1.CodCli AND
ClinicaMedico2.CodCli=Clinica2.CodCli AND
(Medico ×
(ρ ClinicaMedico1 (ClinicaMedico)) ×
(ρ ClinicaMedico2 (ClinicaMedico)) ×
(ρ Clinica1 (Clinica)) ×
(ρ Clinica2 (Clinica)) ×
)
6
3. (Peso 1,2. . . )
Considere a seguinte consulta em SQL:
SELECT Medico.NomeMed
FROM Medico,
ClinicaMedico,
Clinica
WHERE
Medico.CodMed = ClinicaMedico.CodMed AND
ClinicaMedico.CodCli = Clinica.CodCli AND
Medico.CodEspeci = ’Ortopedia’ AND
Clinica.NomeCli = ’ABD Ortopedia’
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):
π Medico.NomeMed
σ
Medico.CodMed = ClinicaMedico.CodMed AND
ClinicaMedico.CodCli = Clinica.CodCli AND
Medico.CodEspeci = ’M’ AND
Clinica.NomeCli = ’ABD Ortopedia’
( Medico
×
(ClinicaMedico
×
Clinica
)
)
As árvores não estão apresentadas.
7