Exame de BD - Técnico Lisboa

Instituto Superior Técnico
Bases de Dados 2008/2009
Exame de 12 de Janeiro de 2009
--- Resolução --1. Considere o seguinte cenário relativo a uma farmácia:
Um paciente dirige-se ao médico e apresenta um conjunto de sintomas. O médico prescreve um ou mais
fármacos através de uma receita que especifica a quantidade de cada fármaco. Cada fármaco está indicado para
o tratamento de certos sintomas. Cada fármaco tem também um conjunto de efeitos secundários, que são
também eles sintomas. Os pacientes têm um número, nome e morada. Os médicos têm um número, nome e
especialidade. Os sintomas têm um código e uma descrição. As receitas têm um número sequencial para cada
médico e uma data. Os fármacos têm um nome, uma substância activa e um preço. É necessário guardar um
registo de cada venda de fármacos; a venda está associada com o paciente e os fármacos comprados, com
indicação da quantidade e preço total para cada fármaco. A venda tem uma data e um número único.
(a) Desenhe um diagrama Entidade-Associação para este domínio.
Resposta:
(b) Considere que certos fármacos só podem ser vendidos com receita médica. Que alterações teria que
fazer ao modelo para posteriormente poder verificar se este requisito está a ser cumprido? Explique.
Resposta:
Primeiro é necessário adicionar um atributo a Fármaco, indicando se se trata de medicamento que pode ser
vendido com ou sem receita. Em segundo lugar é necessário associar uma compra também com Receita.
Posteriormente, o requisito pode ser verificado confirmando que as compras de fármacos que exigem receita
estão associadas com uma receita onde constam esses mesmos fármacos em igual quantidade.
Página 1 de 8
Bases de Dados 2008/2009
2. Considere o seguinte modelo relacional, que poderia representar um domínio semelhante ao anterior:
Sintomas:
Fármacos:
Tratamento de sintomas:
Efeitos secundários:
sintoma(codigo, descricao)
farmaco(nomef, substancia, preco)
indicado(codigo, nomef) codigo: FK(sintoma) nomef: FK(farmaco)
efeito(codigo, nomef) codigo: FK(sintoma) nomef: FK(farmaco)
Escreva uma expressão em álgebra relacional para responder às seguintes questões:
(a) Qual o nome e preço dos fármacos indicados para o tratamento de “hipertensão” e que não provocam
“dores de cabeça” como efeito secundário?
Resposta:
t1 ← πnomef [σsintoma.codigo = indicado.codigo ∧ sintoma.descricao = “hipertensão” (sintoma × indicado)]
t2 ← πnomef [σsintoma.codigo = efeito.codigo ∧ sintoma.descricao = “dores de cabeça” (sintoma × efeito)]
d ← t1 – t2
resultado ← πnomef , preco[σd.nomef = farmaco.nomef (d × farmaco)]
(b) Qual é a substância responsável pelo maior número de efeitos secundários?
Resposta:
ne ← ρne(substancia, n) [substancia Gcount(codigo) (πsubstancia, codigo (σefeito.nomef = farmaco.nomef (efeito × farmaco)))]
resultado ← πsubstancia (farmaco) – πa.substancia [σa.num < b.num (ρa (ne) × ρb (ne))]
Página 2 de 8
Bases de Dados 2008/2009
3. Considerando que o modelo anterior se encontra implementado numa base de dados relacional, apresente uma
consulta SQL para responder a cada uma das seguintes questões:
(a) Qual é o nome e preço do fármaco que está indicado para o tratamento do maior número de sintomas?
Resposta:
select nomef, preco
from farmaco
where nomef in
(
select nomef
from indicado
group by nomef
having count(codigo) >= ALL
(
select count(codigo)
from indicado
group by nomef
)
)
(b) Quais são os fármacos que estão indicados para o tratamento de todos os efeitos secundários do fármaco
“ABC”?
Resposta:
select f.nomef
from farmaco as f
where not exists
(
select e.codigo
from efeito as e
where e.nomef = 'ABC'
and e.codigo not in
(
select i.codigo
from indicado as i
where i.nomef = f.nomef
)
)
Página 3 de 8
Bases de Dados 2008/2009
4. Considere uma árvore B+ com n=3 que representa um índice para um atributo do tipo inteiro.
(a) Partindo de uma árvore vazia, desenhe o resultado dos vários passos de inserção à medida que insere os
elementos 1, 3, 2, 5, 7, 4, 6 por esta ordem.
Resposta:
(b) Desenhe o resultados dos vários passos de remoção à medida que remove os mesmos elementos pela
mesma ordem até chegar a uma árvore vazia.
Resposta:
Página 4 de 8
Bases de Dados 2008/2009
5. Considere um calendário com todos os concertos agendados para a temporada 2009/2010. O calendário é
guardado numa tabela com 3 colunas: (artista, local, data). A chave da tabela é o conjunto das 3 colunas, dado
que um artista pode voltar ao mesmo local numa data diferente. Para além do índice associado à chave primária,
existe um índice para local (do tipo hash) e outro para data (do tipo árvore B+).
(a) Explique como é que o sistema pode usar os índices para descobrir quais são os artistas que actuam no
Porto e em Lisboa, nos dias 30 e 31 de Julho de 2009, respectivamente.
Resposta:
O sistema pode usar o índice para local e procurar os artistas que actuam no Porto. Depois pode usar o índice
para data para descobrir quais são os artistas que actuam no dia 2009-07-30. A intersecção dos dois
resultados dá os artistas que actuam no Porto no dia 2009-07-30. Em seguida o sistema pode verificar,
usando o índice da chave primária, se esses mesmos artistas actuam em Lisboa no dia 2009-07-31.
(b) Pretende-se saber todos os concertos que vão acontecer durante o mês de Maio de 2009. Explique de
que forma seria executada esta consulta, justificando a utilização ou não do índice.
Resposta:
Poderia ser usado o índice para data. Primeiro seria localizada 2009-05-01 no índice, e depois poderiam ser
percorridas sequencialmente as folhas do índice até 2009-05-31, obtendo desta forma todos os concertos
durante esse mês.
(c) Pretende-se descobrir se há artistas diferentes a actuar simultaneamente no mesmo local e data. Escreva
uma consulta em SQL que possa levar o sistema a usar os índices. Justifique.
Resposta:
select *
from tabela as a, tabela as b
where a.local = b.local
and a.data = b.data
and a.artista <> b.artista;
Ao fazer a junção, os critérios de igualdade em local/data podem fazer com que o sistema use os índices
correspondentes, para pesquisar em b o local/data que está a ser considerado em a.
Página 5 de 8
Bases de Dados 2008/2009
6. Considere o modelo relacional:
corrida(ncarro, npiloto, pista, data)
carro(ncarro, marca, modelo)
fabricante(nome, marca)
e a seguinte consulta expressa em álgebra relacional:
πnpiloto,nome [σcorrida.ncarro=carro.ncarro ∧ data>1/1/2008 ∧ carro.marca = fabricante.marca (corrida × carro × fabricante) ]
(a) Desenhe o plano de execução, usando a notação das aulas teóricas (não é necessário indicar os
algoritmos).
(b) Mostre um plano de execução equivalente ao dado na alínea anterior, mas que seja o mais eficiente
possível.
Respostas (a) e (b):
(c) Indique as expressões gerais das regras de equivalência que usou.
Resposta:
Página 6 de 8
Bases de Dados 2008/2009
7. Considere o seguinte escalonamento de transacções:
T1
T2
T3
read(A)
read(B)
write(A)
read(B)
read(A)
read(A)
T4
read(B)
write(B)
T5
write(A)
(a) Desenhe o grafo de conflitos para este escalonamento. O escalonamento é serializável? Justifique.
Resposta:
O escalonamento não é serializável porque o grafo contém ciclos: T3–T1–T2–T4–T3 e T3–T1–T4–T3.
(b) Apresente a ordem pela qual as transacções devem fazer commit de forma a que o escalonamento seja
recuperável. Justifique.
Resposta:
Para que seja recuperável, se há uma transacção que faz read(X) depois de outra fazer write(X) então a
primeira só pode fazer commit depois da segunda. É o caso de T1 e T2, e também de T4 e T3. Portanto, a
ordem dos commits ficaria: T1, T2, T4, T3, T5.
(c) Considere que o controlo de concorrência é feito com base em timestamps, em que o timestamp é o
número da transacção. No escalonamento apresentado, que transacções chegariam ao fim e que
transacções sofreriam rollback? Justifique.
Resposta:
T1 sobre rollback porque write(A) vem tarde demais, vem depois de T3 já ter feito read(A).
T3 sofre rollback porque read(B) vem tarde demais, vem depois de T4 ter feito write(B).
As restantes transacções chegariam ao fim sem problemas.
(d) Suponha que T1 e T5 fizeram commit e as restantes transacções estavam activas quando houve um crash
do sistema. Diga qual a ordem das operações de redo e undo quando o sistema reiniciar. Justifique.
Resposta:
Primeiro fazem-se os undos e depois os redos: undo(T4); redo(T1); redo(T5). Acerca de T2 e T3 não é preciso
fazer nada, uma vez que não fizeram operações de escrita.
Página 7 de 8
Bases de Dados 2008/2009
8. Considere a seguinte relação, com o conjunto de dependências dado:
R(A,B,C,D,E)
A → BC
E→D
C→E
Assuma que a relação está na primeira forma normal.
(a) Indique, justificando, todas as chaves candidatas.
Resposta:
A→BC com C→E e E→D resulta em A→ABCDE. Uma chave candidata é A. Como nada determina A, todas as
outras chaves, se existirem, devem conter A. Mas essas não serão chaves candidatas, porque o atributo A
sozinho já é chave candidata. Logo, não existem outras chaves candidatas.
(b) Diga qual é a forma normal mais elevada a que a relação obedece. Justifique.
Resposta:
Pelo enunciado, está na 1FN. Estará na 2FN se não houver dependências parciais de chaves candidatas. Como
a única chave candidata é A, não há dependências parciais. Logo, está na 2FN. Mas não está na 3FN, porque
na dependência E→D o atributo E não é chave e D não faz parte de chave candidata. O mesmo acontece com
C→E. A forma normal mais elevada em que se encontra é 2FN.
(c) Apresente uma decomposição da relação para a Forma Normal de Boyce-Codd.
Resposta:
A dependência E→D infringe a FNBC, logo decompõe-se R para R1(E,D) e R2(A,B,C,E)
Mas a dependência C→E continua a infringir a FNBC, logo decompõe-se R2 para R21(C,E) e R22(A,B,C).
(d) A decomposição apresentada tem perdas ou não (lossless-join)? Justifique, apresentando uma prova da
sua afirmação.
Resposta:
Em geral, a decomposição não tem perdas se R1∩R2  R1 ou se
Neste caso há duas decomposições a verificar:
R21∩R22=C e C→E, logo C→CE, logo R21∩R22  R21
•
R1∩R2=E e E→D, logo E→ED, logo R1∩R2  R1
•
Assim, as decomposições apresentadas não tem perdas.
Página 8 de 8
R1∩R2  R2 .