Exame de BD - Técnico Lisboa

Instituto Superior Técnico
Bases de Dados 2008/2009
Sem consulta
Exame de 12 de Janeiro de 2009
Duração: 2h30
Todas as alíneas têm igual cotação.
1. Considere o seguinte cenário relativo a uma farmácia:
Responder em folha separada
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.
(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.
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?
(b) Qual é a substância responsável pelo maior número de efeitos secundários?
… separada
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?
(b) Quais são os fármacos que estão indicados para o tratamento de todos os efeitos secundários do fármaco
“ABC”?
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.
(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.
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.
Página 1 de 2
Bases de Dados 2008/2009
Responder em folha separada
(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.
(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.
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.
(c) Indique as expressões gerais das regras de equivalência que usou.
7. Considere o seguinte escalonamento de transacções:
T1
T2
read(A)
read(B)
write(A)
read(B)
T3
read(A)
read(A)
T4
read(B)
write(B)
Responder em folha separada
T5
write(A)
(a) Desenhe o grafo de conflitos para este escalonamento. O escalonamento é serializável? Justifique.
(b) Apresente a ordem pela qual as transacções devem fazer commit de forma a que o escalonamento seja
recuperável. Justifique.
(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.
(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.
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.
(b) Diga qual é a forma normal mais elevada a que a relação obedece. Justifique.
(c) Apresente uma decomposição da relação para a Forma Normal de Boyce-Codd.
(d) A decomposição apresentada tem perdas ou não (lossless-join)? Justifique, apresentando uma prova da
sua afirmação.
Página 2 de 2