Solução - Técnico Lisboa

Número:_________________ Nome: _________________________________________
1
--------------------------------------------------------------------------------------------------------------
INSTITUTO SUPERIOR TÉCNICO
Administração e Optimização de Bases de
Dados
Exame 1 - Solução 21 Junho 2008
--------------------------------------------------------------------------------------------------------------






A duração deste exame é de 2 Horas.
É 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
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) A opção QUERY GOVERNOR do SQL Server 2005 permite influenciar optimizador
de queries no sentido de escolher determinado plano de execução.
F
b) O comando SET STATISTICS IO do SQL Server 2005 indica ao sistema que este
deve actualizar a informação estatística sobre o número de acessos efectuado aos
objectos da base de dados.
F
c) O SQL Server 2005 permite a utlização de um query hint HASH GROUP que força a
utilização de um algoritmo de join para o cálculo de agrupamentos.
V
d) O SQL Server 2005 armazena planos de execução em cache.
V
1.2.(1v) Indique se as seguintes afirmações são verdadeiras ou falsas. Justifique as falsas.
a) O protocolo strict-2PL não dá garantias sobre a possível ocorrência de deadlocks.
V
b) O nível de isolamento REPETABLE READ está sujeito a anomalias do tipo dirty reads.
F
c) Se duas expressões de algebra relacional são equivalentes, então têm o mesmo custo
associado ao seu processamento..
F
d)
Para executar uma selecção com apenas uma condição do tipo a<>b, é mais eficiente usar
um índice do tipo hash do que uma árvore B+.
F
Número:_________________ Nome: _________________________________________
3
2. (2v) Sistema de Ficheiros
2.1. (1v) Cada base de dados do SQL Server 2005 pode conter vários datafiles. Estes datafiles
podem ainda estar organizadas em um ou vários filegroups. Indique os benefícios envolvidos
em:
a) Ter uma base de dados com dois datafiles, ambos num único filegroup primário.
b) Ter uma base de dados com dois datafiles, cada um deles num filegroup separado.
Solução:
a) Tendo dois datafiles, a base de dados pode ser particionada, possibilitando por exemplo a
criação de bases de dados com um volume de dados superior ao espaço de armazenamento
disponível num único disco rígido, ou a execução em paralelo de algumas operações. Cabe no
entanto ao SGBD decidir pela melhor forma de parcicionar os dados, tipicamente com vista a
equilibrar o espaço utilizado nas duas datafiles.
b) Tendo dois filegroups, o administrador da base de dados tem mais flexibilidade na gestão
do particionamento da base de dados. Torna-se possível colocar explicitamente particções de
uma tabela ou de um índice num dado filegroup.
2.2. (1v) Considere a seguinte BD relacional com duas relações:
disciplina(nome-disc, sala, docente)
inscricao(nome-disc, nome-aluno, nota)
Defina instâncias destas relações para três disciplinas, em que cada uma tem inscritos cinco
alunos. Apresente uma estrutura de ficheiros dessas relações que utilize clustering.
Solução:
relação Disciplina
==================
nome-disc
| sala
| docente
--------------------------------------------------Pascal
| CS-101
| Calvin, B
C
| CS-102
| Calvin, B
LISP
| CS-102
| Kess, J
relação Inscricao
=================
nome-disc
| nome-aluno | nota |
--------------------------------------------Pascal
| Carper, D
|A
| e1
Pascal
| Merrick, L | A
| e2
Pascal
| Mitchell, N | B
| e3
Pascal
| Bliss, A
|C
| e4
|
| c1
| c2
| c3
Número:_________________ Nome: _________________________________________
Pascal
C
C
C
C
C
Lisp
Lisp
Lisp
Lisp
Lisp
| Hames, G
| Nile, M
| Mitchell, N
| Carper, D
| Hurly, I
| Hames, G
| Bliss, A
| Hurly, I
| Nile, M
| Stars, R
| Carper, D
|C
|A
|B
|A
|B
|A
|C
|B
|D
|A
|A
| e5
| e6
| e7
| e8
| e9
| e10
| e11
| e12
| e13
| e14
| e15
Página 0 contém: c1, e1, e2, e3, e4, and e5
Página 1 contém: c2, e6, e7, e8, e9 and e10
Página 2 contém: c3, e11, e12, e13, e14, and e15
4
Número:_________________ Nome: _________________________________________
5
3. (4v) Índices
3.1. (3v) Considere um índice hash do tipo extendable hashing, onde cada bucket
armazena 2 valores. Considere que é usada a função de dispersão:
h(x) = x mod 3
Mostre a estrutura obtida pela inserção dos valores 3, 8, 15, 17, 19 e 5
Deve apresentar a estrutura após a inserção de cada elemento, e não apenas o resultado final.
Em cada passo, não é necessário repetir os buckets que não mudaram.
Solução:
a)
3.2. (1v) Explique porque é que uma estrutura de índice baseada em hash não é a melhor
escolha para uma chave de procura sobre a qual são executadas interrogações de intervalo
(range queries)?
Solução:
Uma interrogação com intervalo não pode ser respondida de forma eficiente usando
um índice baseado em hash porque teria que ler todos os buckets. Isto acontece porque os
valores da chave de procura no intervalo não ocupam posições contíguas nos buckets. Em vez
disso, os valores estão distribuídos uniformemente e aleatoriamente por todos os buckets.
Número:_________________ Nome: _________________________________________
6
4. (4v) Processamento e optimização de interrogações
4.1. (3v) Considere duas relações R1(A,B) e R2(A,C) em que A é chave primária em R1 e
C é a chave primária em R2. Considere também que
* a relação R1 contém 20000 tuplos, em 300 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) Assuma que apenas a relação R2 está ordenada pelo atributo A. Assuma também que
apenas cabem 3 blocos de cada vez em memória. Calcule o custo, em termos de I/O, de
realizar um merge join para R1 |x| R2.
c) Assuma que o atributo B é chave primária em R1 e que o atributo C é chave primária em
R2. Neste caso, qual seria o tamanho máximo da relação resultante? Justifique.
Solução:
a) Como A é chave primária em R1, para cada tuplo em R2 vai haver no máximo
um tuplo em R1. O resultado terá, portanto, no máximo 10000 tuplos.
b) Custo de ordenar a relação R1 = br*(2*ceil(log_{M-1}(br/M))+1) =
300*(2*ceil(log_{2}(300/3))+1) =
300*(2*ceil(log_{2}(100))+1) =
300*(2*7+1) =
300*15 =
4500
Custo do merge-sort = br + bs = 400
Custo total = br + bs + ordenar R2 = 400 + 4500 = 4900
c) No pior caso, cada tuplo de R1 corresponderia a todos os tuplos de R2 e,
portanto, a relação final teria 20000x10000 = 200.000.000 tuplos. Isto
aconteceria se todos os tuplos de R1 e de R2 tivessem o mesmo valor no
atributo A.
Número:_________________ Nome: _________________________________________
7
4.2. (1v) Indique duas possíveis heurísticas para a selecção de planos de execução. Para cada
uma explique a sua utilidade e em que casos, se existirem, ela pode não ser útil.
Solução:
1. Executar selecções o mais cedo possível. Diminui o tamanho das tabelas o
mais cedo possível, para diminuir a complexidade das operações
seguintes.
Não é útil se a selecção é pouco específica (i.e., se escolhe quase todos
os tuplos da relação).
Pode piorar o resultado se tivermos uma selecção sobre um join, em que se
o join for executado primeiro, a tabela resultante é muito menor
(assumindo que o join pode ser feito com eficiência).
2. Executar as projecções o mais cedo possível. Diminui o tamanho das
tabelas o mais cedo possível, para diminuir a quantidade de dados usada
nas operações seguintes.
Pode não ser útil se a projecção elimina atributos com índices que podiam
ser usados em operações posteriores.
Número:_________________ Nome: _________________________________________
8
5. (4v) Transacções, controlo de concorrência e gestão de recuperação
5.1. (3v) Considere o seguinte escalonamento de transacções, o qual apresenta instruções de
locking binárias (i.e. lock/unlock).
Trans T1
1 Lock(X)
2 Read(X)
3
4
5 X=X+1
6
7
8
9
10
11
12 Write(X)
13 Unlock(X)
14
15
16
17
18
19
20 Lock(Z)
21 Read(Z)
22
23
24 Z=Z+3
25
26
27
28
29 Write(Z)
30 Unlock(Z)
Trans T2
Trans T3
Lock(Y)
Read(Y)
Lock(Z)
Read(Z)
Y=Y-1
Write(Y)
Unlock(Y)
Z=Z+1
Write(Z)
Unlock(Z)
Lock(Y)
Read(Y)
Lock(X)
Read(X)
X=X+2
Y=Y+3
Write(X)
Unlock(X)
Write(Y)
Unlock(Y)
a) Liste três conflitos que podem surgir no escalonamento apresentado.
b) Apresente o grafo de precedências para o escalonamento apresentado e indique se o
escalonamento é conflict-serializable.
c) O escalonamento apresentado obedece ao protocolo two-phase locking (2PL). Explique
porquê.
d) Apresente um escalonamento alternativo para as 3 transacções que obedeça ao protocolo
2PL e que faça uso de locks exclusivos e partilhados (i.e. lock-s/lock-x/unlock). Indique
ainda, justificando, se o escalonamento apresentado seria conflict-serializable.
Número:_________________ Nome: _________________________________________
9
Solução:
a) conflito entre 2:T1:Read(X) e 25:T2:Write(X)
conflito entre 4:T2:Read(Y) e 27:T3:Write(Y)
conflito entre 7:T3:Read(Z) e 29:T1:Write(Z)
conflito entre 9:T2:Write(Y) e 17:T3:Read(Y) e 27:T3:Write(Y)
conflito entre 14:T3:Write(Z) e 21:T1:Read(Z) e 29:T1:Write(Z)
conflito entre 17:T3:Read(Y) e 9:T2:Write(Y)
conflito entre 19:T2:Read(X) e 12:T1:Write(X)
conflito entre 21:T1:Read(Z) e 15:T3:Write(Z)
conflito entre 25:T2:Write(X) e 2:T1:Read(X) e 12:T1:Write(X)
conflito entre 27:T3:Write(Y) e 4:T2:Read(Y) e 9:T2:Write(Y)
conflito entre 29:T1:Write(Z) e 7:T3:Read(Z) e 14:T3:Write(Z)
b) Grafo com arcos de t1->t2, t2->t3, t3->t1. Não é conclict-serializable,
pois o grafo apresenta um ciclo.
c) Não, pois as transacções libertam os locks que tinham adquirido e
seguidamente tentam adquirir novos locks.
d) O novo escalonamento (e.g. executar primeiro T1, depois T2 e finalmente
T3) seria conflict-serializable, pois o protocolo 2PL garante esta
propriedade (neste caso estamos mesmo a propor um escalonamento série,
embora pudessem existir outros).
T1: Lock-S(X); R(X); X=X+1; Lock-X(X); W(X); Lock-S(Z); R(Z); Z=Z+3; LockX(Z); W(Z); Unlock(Z); Unlock(X)
T2: Lock-S(Y); R(Y); Y=Y-1; Lock-X(Y); W(Y); Lock-S(X); R(X); X=X+2; LockX(X); W(X); Unlock(X); Unlock(Y)
T3: Lock-S(Z); R(Z); Z=Z+1; Lock-X(Z); W(Z); Lock-S(Y); R(Y); Y=Y+3; LockX(Y); W(Y); Unlock(Y); Unlock(Z)
5.2. (1v) Explique a diferença entre a técnica de escrita diferida (deferred database
modification) e a técnica de escrita imediata (immediate modification) da base de dados. Qual
é usada pelo algoritmo ARIES?
Solução:
Na técnica de escrita diferida, todas as operações são registadas no log, mas as alterações não
são imediatamente propagadas para a base de dados. Assim, na ocorrência de uma falha, não é
necessário desfazer nenhuma operação, mas apenas refazer as que se encontram registadas.
Na técnica de escrita imediata, todas as alterações aos dados são imediatamente propagadas
para a base de dados, após o seu registo no log. Assim, na ocorrência de uma falha, algumas
operações terão que ser desfeitas.
O algoritmo Aries usa a técnica de escrita imediata.
Número:_________________ Nome: _________________________________________
10
6. (4v) Database tuning
6.1(1v) Considere a seguinte relação normalizada, que lista os ids, tipos, e custos de várias
peças, além da quantidade existente em stock:
Peças(pid, tipo, custo, quantidade)
Existem duas interrogações extremamente importantes:
- Qual o número de peças disponíveis por tipo de peça.
- Quais os identificadores de peças com o custo mais alto?
a) Qual o tipo de estrutura de ficheiro que escolheria para guardar os registos de Peças e que
índices criaria?
b) Suponha que os clientes se queixam continuamente que o tempo de resposta às duas
interrogações não é satisfatótio face à sua escolha da alínea a). Como não tem dinheiro para
comprar mais HW ou SW, tem que considerar redesenhar o esquema de outra maneira.
Explique como tentaria obter melhor desempenho usando outro esquema (por exemplo, outras
relações), que organização de ficheiros escolheria e que índices sobre as relações.
Solução:
a) Pode ser usado um heap file para a relação Peças.
Para responder de forma eficiente às interrogações, podem ser criados: um índice denso
unclustered B+tree sobre (tipo, quantidade) e uma B+tree densa unclustered sobre (custo,
pid).
b) O problema pode existir porque o optimizador não está a considerar os index-only plans
que podem ser obtidos usando o esquema prévio.
Assim, podemos então criar índices clustered sobre (pid, custo) e (pnome, quantidade).
Para fazer isso, é necessário particionar verticalmente a relação em duas:
Peças1(pid, custo)
Peças2(pid, tipo, quantidade).
Se, mesmo assim os índices não funcionarem, pode ser usada a organização de ficheiro
ordenada para estas duas relações.
Número:_________________ Nome: _________________________________________
11
6.2.(3v) Considere o seguinte esquema relacional normalizado correspondente a um
subconjunto da base de dados de uma empresa:
Projecto(pno, pname, pdept, pgestor, topico, orcamento)
Gestor(gid, gname, gdept, salario, idade, sexo)
Cada projecto tem como base um departamento, cada gestor pertence a um determinado
departamento, e o gestor de um projecto não tem que pertencer ao mesmo departamento no
qual o projecto tem base.
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) Quais os nomes de todos os projectos com gestores cujas idades pertencem a uma
determinada gama de valores?
b) Qual o nome do projecto com orçamento mais baixo?
c) Qual o orçamento total dos projectos geridos por cada gestor?
d) Qual o número de gestores masculinos?
e) Qual a idade média dos gestores?
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 nonclustering, e se a interrogação pode ser respondida só com a informação do índice (index-only
plan).
Solução:
a) select P.proj_name
from Projecto P, Gestor G
where P.pgestor =G.gid
and G.idade < 30
Unclustered B+ tree sobre Gestor<idade, gid> (=> index-only plan) e
Unclustered hash index sobre Projecto<pgestor> .
Se index-only plan não suportado, clustered B+ tree sobre Gestor<idade> e o mesmo
sobre Projecto.
b) select P.pnome
from Projecto P
where P.orcamento = (select min(P1.orcamento)
from Projecto P1)
Unclustered B+tree sobre Projecto<orcamento>
c) select pgestor, sum(orcamento)
Número:_________________ Nome: _________________________________________
12
from Projecto
group by pgestor
Unclustered B+ tree sobre Projecto<pgestor, orcamento> (=> index-only plan),
Se index-only plan não é suportado, então clustered index sobre Projecto<pgestor>
d) select count(*)
from Gestor
where sexo = ‘M’
Unclustered hash index sobre Gestor<sexo> (=>index-only scan para contar)
Se index-only plan não é suportado, então nenhum índice torna esta query mais rápida.
e) select avg(idade)
from Gestor
Unclustered hash index sobre Gestor<idade> (=> index-only scan para calcular a média).
Se index-only plan não é suportado, então nenhum índice torna esta query mais rápida.