Bases de Dados 2007/2008 Exame

Bases de Dados 2007/2008
Exame
25 de Janeiro de 2008
Instituto Superior Técnico
Departamento de Engenharia Informática
Regras
• O exame tem a duração de 2h30m.
• A folha de capa deve ser identificada com o nome e número do
aluno.
• Todas as restantes folhas devem ser identificadas com o número
do aluno.
• O exame deve ser resolvido nas folhas do enunciado.
• O enunciado já inclui espaço para rascunho, pelo que não são
permitidas folhas de rascunho.
• Os alunos devem ter em cima da mesa apenas o material para
escrita e a sua identificação.
• O exame é sem consulta.
Boa sorte!
Número do aluno:
Nome do aluno:
Bases de Dados 2007/2008
Número:
1
Num formigueiro existem dois tipos de formigas: obreiras e guerreiras. Cada obreira pode
ter uma tarefa, como limpar o formigueiro ou procurar comida. As guerreiras têm o dever
de garantir a segurança do formigueiro e da área em volta. A área em volta do formigueiro
está dividida em regiões. Cada área é explorada por várias formigas obreiras e protegida
por uma formiga guerreira.
Considere as seguintes relações, presentes na base de dados de gestão do formigueiro:
f ormiga(nome, idade)
obreira(nome, taref a)
nome : F K(f ormiga)
guerreira(nome)
nome : F K(f ormiga)
protege(nome, regiao)
nome : F K(guerreira)
explora(nome, regiao)
nome : F K(obreira)
Escreva, em álgebra relacional, as seguintes interrogações:
(a) Quais as formigas guerreiras mais velhas do formigueiro? Responda a esta questão
sem usar operadores de agregação.
(b) Quais as formigas que exploraram todas as regiões que a formiga ’Francisca’ também
explorou?
(c) Cada formiga guerreira protege as obreiras que exploram a sua região. Em média,
cada formiga guerreira protege quantas obreiras?
Solução
(a)
X ← f¡ormiga ⊲⊳ guerreira
¢
πnome (X) − πnome σa.idade<b.idade (ρa (X) × ρb (X))
(b)
explora ÷ πregiao (σnome=′ F rancisca′ (explora))
(c)
¡
¢
X ← πprotege.nome as g,explora.nome as o¡ σprotege.regiao=explora.regiao
(protege
×
explora)
¢
Gavg(c) g Gcount(o) as c (X)
Também foi aceite:
G ← Gcount(nome) as g (guerreira)
O ← Gcount(nome) as o (obreira)
πo/g (G × O)
(2,5 valores)
Folha 2 de 17
Bases de Dados 2007/2008
Número:
2
Um leiloeiro dispõe de uma base de dados onde regista informação relevante sobre os leilões
que realiza. Essa informação inclui: a data e local do leilão, todos os lotes que foram a
leilão, bem como todas as licitações que foram feitas sobre esses lotes.
leiloes
licitacao
catalogo
(a) Escreva uma consulta em SQL que devolva a descrição do lote que foi licitado o maior
número de vezes na história deste leiloeiro.
(b) Escreva uma consulta em SQL para determinar a média do valor base de licitação de
lotes que contenham ouro.
(c) Os clientes José Trigo e Joana Silva conheceram-se num leilão. Escreva uma consulta
em SQL para determinar a data e local de todos os leilões em que ambos estiveram
presentes.
Solução
(a) select catalogo.descricao
from catalogo natural join licitacao
group by licitacao.leilao, licitacao.lote
having count(montante) >= ALL(select count(montante)
from licitacao
group by leilao, lote)
(b) select avg(valorbase)
from catalogo
where descricao like ’%ouro%’;
Folha 3 de 17
Bases de Dados 2007/2008
Número:
(c) select leilao.data, leilao.local
from leiloes natural join licitacao
and licitacao.licitante = ’José Trigo’
and licitacao.leilao in (select leilao
from licitacao
where licitante = ’Joana Silva’)
(2,5 valores)
Folha 4 de 17
Bases de Dados 2007/2008
Número:
3
Considere novamente a base de dados da pergunta anterior.
(a) Escreva uma função que devolve o valor final pelo qual foi vendido um determinado
lote em leilão. Esse valor corresponde à licitação mais elevada que foi feita sobre o
lote em causa. A função recebe como parâmetros o número do leilão e o número do
lote.
(b) Assuma que resolveu a alı́nea anterior. Recorrendo a essa função, escreva uma consulta em SQL para determinar a diferença entre o valor final e o valor base de licitação
do lote 3 do leilão 23. (Para este caso em concreto, o resultado seria: 2500.00 e 2000.00 e = 500.00 e)
Solução
(a) create function valor final(numero leilao int, numero lote int)
returns decimal(10,2)
as
begin
declare maior licitacao decimal(10,2);
select max(montante) into maior licitacao
from licitacao
where leilao = numero leilao
and lote = numero lote;
return maior licitacao;
end
(b) select valor final(23,3) - valorbase
from catalogo
where leilao = 23
and lote = 3;
(2 valores)
Folha 5 de 17
Bases de Dados 2007/2008
Número:
4
Considere os dois modelos Entidade-Associação abaixo representados.
(a) Descreva um possı́vel cenário que permita chegar ao primeiro modelo.
(b) Quais as diferenças entre o primeiro modelo e o segundo? Estes dois modelos são
equivalentes? Justifique.
(c) Se respondeu afirmativamente a (b) indique um cenário em que este tipo de transformação não seja válida. Se respondeu negativamente a (b) mostre como tornar os
modelos equivalentes.
(d) Indique vantagens e inconvenientes de usar estes dois modelos e diga por qual optaria
para comunicar a um utilizador final o cenário que descreveu em (a).
Solução
(a) Um cliente bancário tem várias contas, estando cada conta sediada numa agência
bancária especı́fica. A mesma conta pode ser partilhada por diversos clientes.
(b) A relação ternária CAB do primeiro modelo foi convertida para duas relações binárias
no segundo. Os modelos não são equivalentes porque:
• O primeiro modelo permite associar um cliente a uma conta se e só se estes
estiverem associados à respectiva agência.
• O segundo modelo permite associar um conta a uma agência ou a um cliente de
forma independente.
(c) Os modelos não são equivalentes. Existem duas formas de os tornar equivalentes:
Folha 6 de 17
Bases de Dados 2007/2008
Número:
• Através da introdução de restrições de integridade. Neste caso, a RI obrigaria
a que a associação de uma conta a um cliente implique a sua associação a uma
agência.
• Através da introdução de uma terceira relação binária e uma nova entidade
(d) Uma relação ternária tem representação mais simples mas é mais complexa de gerir no
plano lógico. A conversão para relações binárias torna o modelo menos compreensı́vel.
Contudo, simplifica o desenho lógico.
Para comunicação com um utilizador final (i.e. alguém não técnico), faz sentido usar
um modelo que transmita directamente o universo de discurso o que implica a escolha
pelo primeiro modelo.
(2,5 valores)
Folha 7 de 17
Bases de Dados 2007/2008
Número:
5
Considere a relação
R(A, B, C, D)
em que se verifica o seguinte conjunto de dependências funcionais:
AB
AB
C
D
→C
→D
→A
→B
(a) Indique todas as chaves candidatas da relação R.
(b) A relação encontra-se na 3FN? Justifique.
(c) Apresente uma decomposição para FNBC, tendo o cuidado de verificar que o conjunto
de relações daı́ resultante obedece de facto à FNBC.
(d) Indique se a decomposição apresentada na alı́nea anterior preserva todas as dependências. Justifique.
Solução
(a) As chaves candidatas são AB, BC, CD e AD.
(b) Sim. As dependências AB → C e AB → D não infringem a 3FN porque AB é chave.
As outras duas dependências C → A e D → B também não infringem a 3FN porque
tanto A como B fazem parte de uma chave candidata.
(c) A dependência C → A leva à decomposição em AC e BCD. No entanto, BCD não
está na FNBC devido à dependência D → B, daı́ que se torna necessário decompor
BCD em BD e CD. O resultado da decomposição é então: AC, BD, CD.
(d) As dependências AB → C e AB → D não são preservadas. Para as preservar seria
necessário incluir na decomposição duas relações adicionais: ABC e ABD.
(2,5 valores)
Folha 8 de 17
Bases de Dados 2007/2008
Número:
6
Considere uma tabela destinada a guardar dados sobre os funcionários de uma empresa.
A tabela contém o número de BI, o nome, e o salário de cada funcionário. Os dados estão
ordenados alfabeticamente pelo nome do funcionário. Existe um ı́ndice para a coluna de
BI e outro para a coluna de salários.
Tendo em conta a existência destes ı́ndices, explique qual seria a forma mais eficiente de
responder a cada uma das seguintes consultas:
(a) Obter o número de BI de todos os funcionários com salário igual a 1000 e.
(b) Obter o número de BI e o nome de todos os funcionários com salário superior a
1000 e.
(c) Obter o nome de todos os funcionários com número de BI antigo (isto é, com menos
de 8 dı́gitos) e salário igual a 1000 e.
Solução
(a) Pode ser usado o ı́ndice sobre a coluna de salários para responder directamente a esta
pergunta de forma eficiente.
(b) Dado que o ı́ndice sobre os salários é secundário (os dados estão ordenados por nome)
o ı́ndice não ajuda nesta questão, que envolve uma comparação. Não havendo outra
forma de o fazer, o mais eficiente é a pesquisa sequencial.
(c) O ı́ndice sobre o BI não ajuda por ser secundário. Pode ser aplicado o ı́ndice sobre
os salários e uma vez obtidos os funcionários com salário igual a 1000 e então podem
ser removidos (em memória) os registos com número de BI maior ou igual a 10 000
000.
(1,5 valores)
Folha 9 de 17
Bases de Dados 2007/2008
Número:
7
Considere a seguinte árvore B + . Desenhe a árvore passo a passo e mostrando as alterações
após inserir cada um dos seguintes valores: 8, 0 (zero) e 4.
Solução
(2 valores)
Folha 10 de 17
Bases de Dados 2007/2008
Número:
8
Considere o seguinte modelo relacional:
branch(branch name, branch city, assets),
customer(customer name, customer street, customer city),
loan(loan number, branch name, amount),
account(account number, branch name, balance),
borrower(customer name, loan number),
depositor(customer name, account number, access date),
com
com
com
com
com
com
100
100
300
500
300
500
registos
000 registos
000 registos
000 registos
000 registos
000 registos
(a) Indique que algoritmos de junção conhece.
(b) Para as seguintes expressões indique que algoritmo de junção seleccionaria e, sempre que aplicável, indique a relação sobre a qual seria criado um ı́ndice hash (em
memória). Justifique a sua resposta.
1) SELECT * FROM account NATURAL JOIN branch
2) SELECT account.balance, loan.amount
FROM account, loan
WHERE account.branch name LIKE ’P%’ AND loan.branch name LIKE ’D%’
3) SELECT loan number
FROM account, loan
WHERE balance*2 = amount
Solução
(a)
• nested-loop join
• block nested-loop join
• indexed nested-loop join
• merge-join
• hash-join
(b)
1) Hash-join. Seria criado um ı́ndice (hash) sobre a relação ”branch”pois é a de
menor dimensão.
2) Block nested-loop-join. Dado tratar-se de uma junção com conjunto de critérios
(2 critérios) este é o algoritmo adequado.
3) Merge-join dado. O hashing (seja através de um ı́ndice hash, ou de um hashjoin, ou outra operação qualquer que envolva hashing) não é uma boa solução
para este tipo de atributos onde a gama de valores é contı́nua.
(1,5 valores)
Folha 11 de 17
Bases de Dados 2007/2008
Número:
9
Considere as seguintes relações:
R1 (a, b, c, d)
R2 (x, a, z)
Assuma que:
• Todos os atributos são inteiros.
• O atributo d e a chave primária encontram-se indexados, com ı́ndices do tipo árvore B+;
(a) Represente esquematicamente o plano de execução (em árvore) para a seguinte expressão (sem qualquer optimização):
σ(a<10)∧(b>20)∧(d=50)∧(z=100) (R1 ⊲⊳ R2 )
(b) Optimize a expressão anterior, indicando a nova expressão e as regras de equivalência
usadas.
(c) Represente o novo plano de execução, indicando justificadamente os algoritmos de
junção e pesquisa seleccionados.
Solução
(a)
(b)
σa<10 (σb>20 (σd=50 (R1 )) ⊲⊳ (σz=100 (R2 )))
Folha 12 de 17
Bases de Dados 2007/2008
Número:
(c)
(1,5 valores)
Folha 13 de 17
Bases de Dados 2007/2008
Número:
10
Considere a figura seguinte, a qual representa a execução de 5 transacções num sistema que
fez o checkpoint no momento Tc , que falhou no momento Tf e que arrancou no momento
Ta .
(a) Assuma que o SGBD adopta uma polı́tica de modificação imediata (immediate database modification). Diga quais as transacções a que será feito undo e quais aquelas
a que será feito redo. Justifique.
(b) Assuma que o SGBD adopta uma polı́tica de modificação diferida (deferred database
modification). Diga quais as transacções a que será feito undo e quais aquelas a que
será feito redo. Justifique.
Solução
(a) UNDO: T4 e T5 ; REDO: T2 e T3 . No caso de modificação imediata, todos os writes
das transacções podem ter sido já escritos no disco. Portanto, terão que ser desfeitos
os writes das transacções que não chegaram ao commit. Pela mesma razão, terão
que ser refeitos os writes das transacções que chegaram ao commit.
(b) REDO: T2 e T3 ; não são feitos UNDOs. Como a escrita é diferida, os writes só são
escritos no disco após o commit da transacção. Logo, têm que ser refeitas aquelas
que chegaram ao commit, para garantir que todos os writes são efectivados. Não
é necessário fazer undo porque as alterações das transacções que não chegaram ao
commit nunca foram escritas no disco.
(1,5 valores)
Folha 14 de 17
Bases de Dados 2007/2008
Número:
Página para Rascunho
Folha 15 de 17
Bases de Dados 2007/2008
Número:
Página para Rascunho
Folha 16 de 17
Bases de Dados 2007/2008
Número:
Página para Rascunho
Folha 17 de 17