Prova de Fundamentos de Bancos de Dados - Inf

Prova de Fundamentos de Bancos de Dados
2a Prova
Prof. Carlos A. Heuser
Junho de 2009
Prova com consulta – duas horas de duração
Considere a base de dados abaixo (base de dados idêntica a da 1a prova). Esta base de
dados é usada para 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
1
1. (Peso 1,25) Sobre a base de dados acima, resolver a seguinte consulta usando cálculo relacional:
Obter os códigos dos médicos que atuam em todas clínicas cadastradas no banco
de dados (se a base de dados não contiver nenhuma clínica, todos médicos devem
aparecer no resultado).
Solução:
{r|
∀ c ∈ Clinica (
∃ cm ∈ ClinicaMedico (
cm.CodCli=c.CodCli ∧
cm.CodMed=r.CodMed
)
)
}
2. Sobre a base de dados acima, resolver as consultas abaixo usando SQL:
2.a (Peso 1,25) Obter os códigos e nomes dos médicos que não atuam em nenhuma clínica.
Solução:
SELECT CodMed, NomeMed
FROM Medico
EXCEPT
(SELECT CodMed, NomeMed
FROM Medico
NATURAL JOIN
ClinicaMedico
)
ou
SELECT CodMed, NomeMed
FROM Medico
WHERE
CodMed NOT IN
(SELECT CodMed
FROM ClinicaMedico
)
ou
2
SELECT CodMed, NomeMed
FROM Medico
WHERE
NOT EXISTS
(SELECT *
FROM ClinicaMedico
WHERE CodMed = Medico.CodMed
)
ou
SELECT CodMed, NomeMed
FROM Medico
LEFT NATURAL JOIN
ClinicaMedico
WHERE
ClinicaMedico.cODcLI IS NULL
3
2.b (Peso 1,25) Obter uma tabela contendo as seguintes colunas:
i. Código e nome de cada especialidade;
ii. código e nome de cada médico que atua nesta especialidade (se a especialidade não tiver médicos, estas colunas devem aparecer em branco);
iii. código e nome de cada clínica em que atua o médico (se o médico não
atua em nenhuma clínica, estas colunas devem aparecer vazias).
Solução:
SELECT
Especialidade.CodEspci,
Especialidade.Nome,
Medico.CodMed,
Medico.NomeMed,
Clinica.CodCli,
Clinica.NomeCli
FROM Especialidade
NATURAL LEFT JOIN
Medico
NATURAL LEFT JOIN
ClinicaMedico
NATURAL LEFT JOIN
Clinica
2.c (Peso 1,25) Obter uma tabela com duas colunas:
i. Nome de uma especialidade mais genérica, seguida de
ii. nome de uma especialidade mais específica (apenas se houver uma especialidade mais específica para a especialidade em questão).
A tabela não deve ser recursiva, ou seja apenas um nível de relacionamento
deve aparecer.
Solução:
SELECT EspecialidadeGenerica.Nome,
EspecialidadeEspecifica.Nome
FROM Especialidade as EspecialidadeGenerica,
Especialidade as EspecialidadeEspecifica,
WHERE
EspecialidadeEspecifica.CodEspeciGenerica=
EspecialidadeGenerica.CodEspeci
4
2.d (Peso 1,25) – Mesmo enunciado que a questão 1
Obter os códigos dos médicos que atuam em todas clínicas cadastradas no
banco de dados (se a base de dados não contiver nenhuma clínica, todos médicos devem aparecer no resultado).
Solução:
SELECT Medico.CodMed,
FROM
Medico
WHERE
NOT EXISTS (
SELECT * FROM Clinica
WHERE
NOT EXISTS (
SELECT * FROM ClinicaMedico
WHERE Clinica.CodCli
= ClinicaMedico.CodCli AND
Medico.CodMed
= ClinicaMedico.CodMed
)
)
2.e (Peso 0,625) Obter o número de clínicas que têm uma localidade informada.
Solução:
SELECT COUNT (LocalCli)
FROM Clinica
2.f (Peso 0,625) Obter o número de clinicas que têm pelo menos um médico.
Solução:
SELECT COUNT (DISTINCT CodCli)
FROM ClinicaMedico
2.g Para cada especialidade, obter:
i. O código e o nome do especialidade, seguido do
ii. número de médicos que atuam na especialidade.
Resolver esta consulta considerando duas variantes:
i. (Peso 1,25) Mesmo especialidades nas quais não aparecem médicos devem aparecer no resultado.
Solução:
SELECT Especialidade.CodEspeci,
Especialidade.Nome
COUNT (CodMed) AS NoDeMedicos
FROM Especialidade
NATURAL LEFT JOIN
Medico
GROUP BY Especialidade.CodEspeci,
Especialidade.Nome
5
ii. (Peso 1,25) Apenas as especialidades com mais que trinta médicos devem
aparecer no resultado.
Solução:
SELECT Especialidade.CodEspeci,
Especialidade.Nome
COUNT (*) AS NoDeMedicos
FROM Especialidade
NATURAL JOIN
Medico
GROUP BY Especialidade.CodEspeci,
Especialidade.Nome
HAVING COUNT (*) > 30
6