enunciado - Técnico Lisboa

Número:_________________ Nome: _________________________________________
--------------------------------------------------------------------------------------------------------------
INSTITUTO SUPERIOR TÉCNICO
Administração e Optimização de Bases de
Dados
Exame 1
21 Junho 2008
--------------------------------------------------------------------------------------------------------------






A duração deste exame é de 2H30.
É 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ânea
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 o optimizador de
interrogações no sentido de escolher determinado plano de execução.
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 às tabelas da base de
dados.
c) O SQL Server 2005 suporta um query hint HASH GROUP que força a utilização de um
algoritmo de join para o cálculo de agrupamentos.
d) O SQL Server 2005 armazena planos de execução em cache para posterior reutilização.
1.2.(1v) Indique se as seguintes afirmações são verdadeiras ou falsas.
a) O protocolo strict-2PL não dá garantias sobre a possível ocorrência de deadlocks.
b) O nível de isolamento REPETABLE READ está sujeito a anomalias do tipo dirty reads.
c) Se duas expressões de álgebra relacional são equivalentes, então têm o mesmo custo
associado ao seu processamento.
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+.
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.
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.
Número:_________________ Nome: _________________________________________
4
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.
Número:_________________ Nome: _________________________________________
5
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)?
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.
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.
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
trinco 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) Indique 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
Número:_________________ Nome: _________________________________________
10
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?
Número:_________________ Nome: _________________________________________
11
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ório 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 criaria sobre as relações.
Número:_________________ Nome: _________________________________________
12
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. 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 dos 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,
execute para cada alínea o seguinte:

Escreva as interrogações correspondentes em SQL.

Diga quais os atributos que devem ser indexados, qual o tipo de índice que deve ser
usado (árvore B+ ou hash), e se é clustered ou non-clustered.

Para os índices indicados na alínea anterior, indique os casos em que a interrogação
pode ser respondida só com a informação do índice (index-only plan), se o SGBD o
permitir. Caso o SGBD não o permita, indique quais os índices alternativos, se
existirem.
Número:_________________ Nome: _________________________________________
13
Número:_________________ Nome: _________________________________________
14