25 de Janeiro de 2008
Instituto Superior T´ecnico
Departamento de Engenharia Inform´atica
Regras
• O exame tem a dura¸c˜ao de 2h30m.
• A folha de capa deve ser identificada com o nome e n´umero do aluno.
• Todas as restantes folhas devem ser identificadas com o n´umero do aluno.
• O exame deve ser resolvido nas folhas do enunciado.
• O enunciado j´a inclui espa¸co para rascunho, pelo que n˜ao s˜ao permitidas folhas de rascunho.
• Os alunos devem ter em cima da mesa apenas o material para
escrita e a sua identifica¸c˜ao.
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ˆem o dever de garantir a seguran¸ca do formigueiro e da ´area em volta. A ´area em volta do formigueiro est´a dividida em regi˜oes. Cada ´area ´e explorada por v´arias formigas obreiras e protegida por uma formiga guerreira.
Considere as seguintes rela¸c˜oes, presentes na base de dados de gest˜ao 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 ´algebra relacional, as seguintes interroga¸c˜oes:
(a) Quais as formigas guerreiras mais velhas do formigueiro? Responda a esta quest˜ao sem usar operadores de agrega¸c˜ao.
(b) Quais as formigas que exploraram todas as regi˜oes que a formiga ’Francisca’ tamb´em explorou?
(c) Cada formiga guerreira protege as obreiras que exploram a sua regi˜ao. Em m´edia, cada formiga guerreira protege quantas obreiras?
(2,5 valores)
Um leiloeiro disp˜oe de uma base de dados onde regista informa¸c˜ao relevante sobre os leil˜oes que realiza. Essa informa¸c˜ao inclui: a data e local do leil˜ao, todos os lotes que foram a leil˜ao, bem como todas as licita¸c˜oes que foram feitas sobre esses lotes.
leiloes
catalogo
licitacao
(a) Escreva uma consulta em SQL que devolva a descri¸c˜ao do lote que foi licitado o maior n´umero de vezes na hist´oria deste leiloeiro.
(b) Escreva uma consulta em SQL para determinar a m´edia do valor base de licita¸c˜ao de lotes que contenham ouro.
(c) Os clientes Jos´e Trigo e Joana Silva conheceram-se num leil˜ao. Escreva uma consulta em SQL para determinar a data e local de todos os leil˜oes em que ambos estiveram presentes.
Considere novamente a base de dados da pergunta anterior.
(a) Escreva uma fun¸c˜ao que devolve o valor final pelo qual foi vendido um determinado lote em leil˜ao. Esse valor corresponde `a licita¸c˜ao mais elevada que foi feita sobre o lote em causa. A fun¸c˜ao recebe como parˆametros o n´umero do leil˜ao e o n´umero do lote.
(b) Assuma que resolveu a al´ınea anterior. Recorrendo a essa fun¸c˜ao, escreva uma con-sulta em SQL para determinar a diferen¸ca entre o valor final e o valor base de licita¸c˜ao do lote 3 do leil˜ao 23. (Para este caso em concreto, o resultado seria: 2500.00 e -2000.00 e = 500.00 e)
(2 valores)
Considere os dois modelos Entidade-Associa¸c˜ao abaixo representados.
(a) Descreva um poss´ıvel cen´ario que permita chegar ao primeiro modelo.
(b) Quais as diferen¸cas entre o primeiro modelo e o segundo? Estes dois modelos s˜ao equivalentes? Justifique.
(c) Se respondeu afirmativamente a (b) indique um cen´ario em que este tipo de trans-forma¸c˜ao n˜ao seja v´alida. 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´ario que descreveu em (a).
Considere a rela¸c˜ao
R(A, B, C, D)
em que se verifica o seguinte conjunto de dependˆencias funcionais:
AB → C AB → D C → A D → B
(a) Indique todas as chaves candidatas da rela¸c˜ao R. (b) A rela¸c˜ao encontra-se na 3FN? Justifique.
(c) Apresente uma decomposi¸c˜ao para FNBC, tendo o cuidado de verificar que o conjunto de rela¸c˜oes da´ı resultante obedece de facto `a FNBC.
(d) Indique se a decomposi¸c˜ao apresentada na al´ınea anterior preserva todas as de-pendˆencias. Justifique.
(2,5 valores)
Considere uma tabela destinada a guardar dados sobre os funcion´arios de uma empresa. A tabela cont´em o n´umero de BI, o nome, e o sal´ario de cada funcion´ario. Os dados est˜ao ordenados alfabeticamente pelo nome do funcion´ario. Existe um ´ındice para a coluna de BI e outro para a coluna de sal´arios.
Tendo em conta a existˆencia destes ´ındices, explique qual seria a forma mais eficiente de responder a cada uma das seguintes consultas:
(a) Obter o n´umero de BI de todos os funcion´arios com sal´ario igual a 1000 e.
(b) Obter o n´umero de BI e o nome de todos os funcion´arios com sal´ario superior a 1000 e.
(c) Obter o nome de todos os funcion´arios com n´umero de BI antigo (isto ´e, com menos de 8 d´ıgitos) e sal´ario igual a 1000 e.
Considere a seguinte ´arvore B+. Desenhe a ´arvore passo a passo e mostrando as altera¸c˜oes
ap´os inserir cada um dos seguintes valores: 8, 0 (zero) e 4.
(2 valores)
Considere o seguinte modelo relacional:
branch(branch name, branch city, assets), com 100 registos
customer(customer name, customer street, customer city), com 100 000 registos loan(loan number, branch name, amount), com 300 000 registos
account(account number, branch name, balance), com 500 000 registos
borrower(customer name, loan number), com 300 000 registos
depositor(customer name, account number, access date), com 500 000 registos (a) Indique que algoritmos de jun¸c˜ao conhece.
(b) Para as seguintes express˜oes indique que algoritmo de jun¸c˜ao seleccionaria e, sem-pre que aplic´avel, indique a rela¸c˜ao sobre a qual seria criado um ´ındice hash (em mem´oria). 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
Considere as seguintes rela¸c˜oes:
R1(a, b, c, d)
R2(x, a, z)
Assuma que:
• Todos os atributos s˜ao inteiros.
• O atributo d e a chave prim´aria encontram-se indexados, com ´ındices do tipo ´arvore B+;
(a) Represente esquematicamente o plano de execu¸c˜ao (em ´arvore) para a seguinte ex-press˜ao (sem qualquer optimiza¸c˜ao):
σ(a<10)∧(b>20)∧(d=50)∧(z=100)(R1 ./ R2)
(b) Optimize a express˜ao anterior, indicando a nova express˜ao e as regras de equivalˆencia usadas.
(c) Represente o novo plano de execu¸c˜ao, indicando justificadamente os algoritmos de jun¸c˜ao e pesquisa seleccionados.
(1,5 valores)
Considere a figura seguinte, a qual representa a execu¸c˜ao de 5 transac¸c˜oes 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¸c˜ao imediata (immediate database modification). Diga quais as transac¸c˜oes a que ser´a feito undo e quais aquelas a que ser´a feito redo. Justifique.
(b) Assuma que o SGBD adopta uma pol´ıtica de modifica¸c˜ao diferida (deferred database modification). Diga quais as transac¸c˜oes a que ser´a feito undo e quais aquelas a que ser´a feito redo. Justifique.
