Some Question True/False

Número:_________________ Nome: _________________________________________
--------------------------------------------------------------------------------------------------------------
INSTITUTO SUPERIOR TÉCNICO
Administração e Optimização de Bases de
Dados
Exame: tipo
Junho 2008
--------------------------------------------------------------------------------------------------------------






A duração deste exame é de 2Horas.
É um exame com consulta.
O número total de pontos é 20.
Marque as suas respostas NA FOLHA DE EXAME.
Escreva o seu número e nome no topo de cada página.
Escreva todas as fórmulas.
Para o uso oficial somente
1
2
2
2
3
4
4
4
5
4
6
4
SUM
20
1
Número:_________________ Nome: _________________________________________
2
1. (2v) Miscelânia
1.1.(1v) Relativamente ao SQL Server 2005, indique nas alíneas seguintes quais as
verdadeiras e as falsas.
a) Um datafile não pode simultaneamente ser usado para armazenar os dados e a
informação do log de transacções.
V
b) O commando DBCC showcontig do SQL Server 2005 permite obter estatísticas
sobre a utilização da cache.
F
c) O query hint NOLOCK equivale à utilização do nível de isolamento
READ COMMITED.
F
d) O Database Tuning Advisor do SQL Server 2005 permite obter recomendações
relacionadas com o modelo de dados de uma BD, incluindo a utilização de vistas e de
esquemas de particionamento.
V
1.2.(1v) Indique se as seguintes afirmações são verdadeiras ou falsas. Justifique as falsas.
a) O desempenho de um índice do tipo árvore B+ degrada-se com o crescimento
da tabela indexada.
V
b) Mesmo com ambas as relações ordenadas, um merge-join nem sempre é mais
eficiente, em termos de I/O, do que um block nested-loop join.
V
c) Num sistema de recuperação que usa modificação deferida da base de dados, não é
necessário guardar no log informação para fazer o undo das operações.
V
d) A utilização de locks exclusivos e partilhados garante a geração de escalonamentos
conflict-serializable.
F
Número:_________________ Nome: _________________________________________
3
2. (2v) Sistema de Ficheiros
2.1. (1v) Considere as estruturas de dados Heap File (e.g. uma lista), Sorted File (e.g. uma
B+Tree) e Hashed File. Qual delas escolheria para armazenar uma tabela de uma base de
dados apresentando os seguintes padrões de acesso:
a) Pesquisas muito frequentes por tuplos na tabela com base num intervalo de valores
dado para um atributo.
b) Pesquisas muito frequentes por tuplos na tabela com base na igualdade de um
atributo a um dado valor constante.
c) Inserções frequentes na tabela, em conjunto com pesquisas que envolvem a consulta
da totalidade dos tuplos armazenados.
d) Pesquisas frequentes sobre a tabela, que envolvam cálculos sobre agrupamentos de
tuplos.
Solução:
a) Usando o atributo em questão como chave de pesquisa, um Sorted File seria o mais
indicado.
b) Usando o atributo em questão como chave de pesquisa, uma Hashed File seria o
mais indicado.
c) Um Heap file seria o mais indicado neste caso.
d) Usando o atributo sobre o qual se vão gerar os agrupamentos como chave de
pesquisa, um Hashed File seria o mais indicado.
2.2. (1v) a) Explique em que difere a estratégia RAID 5 da estratégia RAID 4.
b) Qual das duas estratégias (RAID 4 ou RAID 5) oferece melhor performance na
execução de leituras sequenciais? Justifique a sua resposta.
Solução:
a) As informações sobre paridade para os dados do array são distribuídas ao longo de todos
os discos do array , ao invés de serem armazenadas num disco dedicado, oferecendo assim
mais desempenho nas escritas do que o RAID 4 (i.e. evita-se a contenção nas escritas dos
blocos de paridade sempre no mesmo disco), e simultaneamente, tolerância a falhas.
b) O desempenho geral de um array RAID 5 é equivalente ao de um RAID 4, excepto no
caso de leituras sequenciais (i.e. o RAID 5 reduz a eficiência dos algoritmos de leitura
sequenciais por causa da distribuição dos blocos de paridade).
Número:_________________ Nome: _________________________________________
4
3. (4v) Índices
3.1. (3v) Considere um índice do tipo árvore B+, em que o número máximo de descendentes
de cada nó, N, é 3.
a) Desenhe a árvore resultante da inserção dos seguintes valores, pela ordem dada
1 3 10 5 8 14 17
b) Desenhe a árvore resultante da inserção dos valores na alínea (a), mas considerando que
N = 4.
Em ambas as respostas, deve apresentar a árvore após a inserção de cada elemento, e não
apenas a árvore final. Em cada árvore, não é necessário repetir os nós que não mudaram.
Solução:
3.1. a)
3.1.b)
Número:_________________ Nome: _________________________________________
5
3.2. (1v) Explique a diferença entre as seguintes estruturas.
a) Índices densos vs. indices esparsos (sparse).
b) Índices clustered vs. indices unclustered. Neste caso, dê um exemplo de uma
situação em que um índice unclustered pode ser preferível a um clustered.
Solução:
3.2.a) Num índice denso, todas as chaves são representadas no ficheiro de índice,
enquanto que um índice esparso normalmente apenas armazena uma chave por cada bloco de
dados (i.e., algumas chaves não vão ser explicitamente armazenadas no ficheiro de índice).
3.2.b) Num índice clustered, os dados são fisicamente armazenados na mesma ordem
que as chaves do índice. Num índice unclustered, os dados são físicamente armazenados
numa ordem diferente das chaves do índice. Um indice unclustered será preferível para
interrogações que se possam executar com index-only plans.
Número:_________________ Nome: _________________________________________
6
4. (4v) Processamento e optimização de interrogações
4.1. (2,5v) Considere duas relações R1(A,B,C) e R2(A,D) em que A é chave primária em
ambas.
Considere também que:
* a relação R1 contém 20000 tuplos, em 200 blocos
* a relação R2 contém 10000 tuplos, em 100 blocos
a) Estime, justificando, o tamanho máximo em tuplos da relação resultante da operação
R1 |x| R2.
b) Considerando o algoritmo block nested-loop join, seria mais eficiente, em termos de I/O,
executar R1 |x| R2 ou R2 |x| R1? Justifique apresentando os cálculos. Considere o pior caso.
c) É sempre mais eficiente usar um block nested-loop join do que um nested-loop join?
Justifique a sua afirmação.
Solução:
4.1. a) Como A é chave primária, para cada tuplo em R1 vai haver no máximo um tuplo em
R2, e vice-versa. O maior resultado terá, portanto, o tamanho da menor relação, i.e., 10000
tuplos.
b) Custo do BNLJ = br * bs + br
R1 |x| R2
200 * 100 + 200 = 20200
R2 |x| R1
100 * 200 + 100 = 20100
Seria mais eficiente fazer R2 |x| R1
c) Não.
- Se R1 e R2 couberem na memória o custo do NLJ é igual ao do BNLJ:
br + bs
ou
- Custo do NLJ = nr * bs + br. Portanto, se nr = br, o custo do NLJ é igual ao do BNLJ.
Isto pode acontecer se os tuplos de r têm o tamanho de um bloco.
4.2. (1,5v) Considere as relações r1(A,B,C), r2(C,D,E), e r3(E,F), com chaves primárias
A, C, e E, respectivamente. Assuma que r1 tem 1000 tuplos, r2 tem 1500 tuplos, e r3 tem 750
tuplos. Estime o tamanho do resultado de r1 |X| r2 |X| r3, e indique uma estratégia eficiente
para calcular a junção.
Número:_________________ Nome: _________________________________________
7
Solução:
A relação resultante da junção de r1, r2 e r3 seria a mesma independentemente do modo como
fazemos a junção, pois este operador é associativo e comutativo.
Calculando o tamanho do resultado baseando-nos na estratégia ((r1 |X| r2) |X| r3):
 juntando r1 com r2 resulta uma relação com no máximo 1000 tuplos, porque C é
chave de r2.
 Do mesmo modo, efectuando a junção desse resultado com r3 retornará uma relação
com 1000 tuplos no máximo, porque E é chave de r3. Assim, cada tuplo de r1|X|r2
corresponde, no máximo, a um tuplo de r3. Ou seja, é o tamanho de r1|X|r2 que
estabelece o limite máximo no tamanho do resultado final.
 Assim, a relação final terá no máximo 1000 tuplos.
Uma estratégia eficiente para calcular esta junção seria criar um índice sobre o atributo C da
relação r2 e sobre E da relação r3.
Depois, para cada tuplo em r1, fazer o seguinte:
 Usar o índice sobre r2 para procurar, no máximo, um tuplo que corresponda ao valor
de C de r1.
 Usar o índice sobre E para procurar em r3, no máximo, um tuplo que satisfaça o valor
único para E em r2.
5. (4v) Transacções, controlo de concorrência e gestão de recuperação
Número:_________________ Nome: _________________________________________
8
5.1. (2v) Considere o seguinte excerto de um log do algoritmo ARIES, lido após uma falha no
sistema.
100: T1, start
110: T2, start
120: T1, insert P5, 100
125: checkpoint
130: T4, start
140: T1, delete P6, 120
150: T2, delete P5, 110
170: T4, insert P5, 130
180: T1, end
190: T2, end
Os registos de update do log indicam:
LSN, "transacção", "operação" "página alterada", lastLSN
No momento do checkpoint, foram escritas para o disco as tabelas
Dirty Page Table
PID PageLSN
P5 120
P6 80
Transaction Table
RecLSN TID LastLSN
100
T1 120
30
T2 110
O PageLSN das páginas P5 e P6, no disco é, respectivamente, 170 e 30.
a) Indique uma razão para que o PageLSN em disco da página P5 seja maior que o seu
RecLSN gravado aquando do checkpoint.
b) Mostre, em cada passo da fase de análise, o conteúdo das tabelas Dirty Page Table,
Transaction Table e Undo List. Em cada passo, indique o LSN que está a ser analisado.
Solução:
5.1. a) Para que tal aconteça, deverá ter ocorrido um flush da página P5 após o checkpoint,
mais precisamente, após a operação registada no LSN 170.
b)
125: DPT = (P5,120,100), (P6,80,30)
TT = (T1,120), (T2,110)
UL = T1, T2
130: DPT = (P5,120,100), (P6,80,30)
TT = (T1,120), (T2,110), *(T4, 130)*
UL = T1, T2, *T4*
140: DPT = (P5,120,100), (P6,*140*,30)
TT = (T1,*140*), (T2,110), (T4, 130)
Número:_________________ Nome: _________________________________________
9
UL = T1, T2, T4
150: DPT = (P5,*150*,100), (P6,140,30)
TT = (T1,140), (T2,*150*), (T4, 130)
UL = T1, T2, T4
170: DPT = (P5,*170*,100), (P6,140,30)
TT = (T1,140), (T2,150), (T4, *170*)
UL = T1, T2, T4
180: DPT = (P5,170,100), (P6,140,30)
TT = (T2,150), (T4, 170)
UL = T2, T4
190: DPT = (P5,170,100), (P6,140,30)
TT = (T4, 170)
UL = T4
5.2. (1v) Explique o que se entende por anomalias de dirty reads, non-repetable reads e
phantoms.
Solução:
5.2. dirty reads - leitura de dados intermédios (i.e. actualizações que ainda não foram
"commited") gerados por outra transacção que se executa concorrentemente.
non-repetable reads - duas interrogações executadas no contexto de uma mesma
transacção devolvem resultados diferentes (i.e. dados commited por outra transacção que se
executa concorrentemente).
fantasmas - duas interrogações executadas no contexto de uma mesma transacção
devolvem conjuntos de resultados de tamanho diferente.
5.3. (1v) Considerando que se usa o nível de isolamento REPEATABLE_READ com TABLE
LOCKING, seria possível a ocorrência da anomalia Phantom reads? Justifique porquê.
Solução:
Os phantom reads não iriam ocorrer porque é feito o locking a toda a tabela. Assim, não é
possível a uma transacção inserir novos tuplos nessa tabela sem ter adquirido inicialmente o
lock.
Número:_________________ Nome: _________________________________________
10
6. (4v) Database tuning
6.1. (1v) Considere as seguintes interrogações SQL executadas sobre relações:
PESSOA(nome,morada)
EMPREGADO(nome,categoria,salario).
a) select nome
from EMPREGADO
WHERE nome IN (select nome from PESSOA where morada='Lisboa')
b) select nome
from EMPREGADO
WHERE salario*12 >= 24000
Considere ainda que existem índices clustered sobre o atributo nome nas duas tabelas.
Como reescreveria as duas interrogações de a) e b) de modo a tornar a sua execução mais
eficiente?
Solução:
a) select nome
from PESSOA P, EMPREGADO E
WHERE P.nome=E.nome
AND P.morada='Lisboa'
b) select nome
from EMPREGADO
WHERE salario >= 2000
6.2.(3v) Considere o seguinte esquema relacional normalizado correspondente a um
subconjunto da base de dados de uma empresa:
Empregado (eid, enome, morada, salario, idade, deptid)
Departamento(did, dnome, andar, orcamento)
Suponha que sabe que as seguintes interrogações são as mais frequentes e que são igualmente
equivalentes em frequência e importância:
a) Qual o id, nome e morada dos empregados cuja idade pertence a uma determinada
gama de valores?
b) Qual o id e endereço dos empregados com um determinado nome?
c) Qual a média dos salários dos empregados por idade?
d) Devolva toda a informação sobre departamentos, ordenada pelos números de andar.
e) Qual a soma dos orçamentos de todos os departamentos por andar?
Assumindo que estas interrogações são mais importantes do que quaisquer actualizações,
escreva as interrogações correspondentes em SQL, e diga quais os atributos que devem ser
indexados, que tipo de índice deve ser usado (B+tree ou hash-based), se é clustering ou non-
Número:_________________ Nome: _________________________________________
11
clustering, e se a interrogação pode ser respondida só com a informação do índice (index-only
plan).
Solução:
a) select E.eid, E.ename, E.address
from Emp E
where age > k1
and age > k2
-- k1 e k2 são constantes inteiras
Clustered B+ tree index sobre Emp<age>
b) select E.eid, E.address
from Emp E
where E.ename = ‘ABC’
-- ‘ABC’ é nome de pessoa
Unclustered hash index sobre Emp<ename>,
Se existem muitos empregados chamados ‘ABC’, então clustered é melhor.
c) select age, avg(sal)
from Emp
group by age
Unclustered B+tree sobre Emp<age,sal> (=> index-only plan),
Se index-only plans não são suportados, clustered B+tree sobre Emp<age>
d) select *
from Dept
order by floor
Clustered B+ tree sobre Dept<floor>
e) select floor, sum(budget)
from Dept
group by floor
Unclustered index sobre Dept<floor,budget> (=> index-only plan),
Se index-only plans não forem suportados, clustered B+tree sobre Dept<floor>