enunciado - Técnico Lisboa

Número:_________________ Nome: _________________________________________
--------------------------------------------------------------------------------------------------------------
INSTITUTO SUPERIOR TÉCNICO
Administração e Optimização de Bases de
Dados
Exame 1
2 Julho 2009
--------------------------------------------------------------------------------------------------------------






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 2008, indique nas alíneas seguintes quais as
verdadeiras e as falsas. Cada alínea vale 0,25 valores. Se acertar, tem a cotação toda; se errar,
é-lhe descontado 0,1 valores por cada alínea errada.
a) Uma base de dados SQL Server 2008 pode conter vários data files e vários log files, ambos
organizados em um ou vários file groups.
b) Uma interrogação SQL que utiliza o hint NOLOCK apresenta um comportamento
equivalente à utilização do nível de isolamento READ UNCOMMITED.
c) O SQL Server 2008 suporta transparentemente o particionamento horizontal e vertical de
índices e tabelas.
d) O SQL Server 2008 suporta a criação de vistas materializadas.
1.2)(1v) Indique se as seguintes afirmações são verdadeiras ou falsas. Cada alínea vale 0,25
valores. Se acertar, tem a cotação toda; se errar, é-lhe descontado 0,1 valores por cada alínea
errada.
a) Um índice do tipo extendable hash tem a vantagem de suportar de forma eficiente
pesquisas por intervalos de valores, enquanto que um índice baseado em hash convencional
apenas suporta pesquisas por condições de igualdade.
b) O nível de isolamento REPETABLE READ está sujeito a anomalias do tipo phantom
reads.
c) O algoritmo ARIES garante que uma operação de REDO nunca é feita mais que uma vez.
d) O algoritmo ARIES garante que uma operação de UNDO nunca é feita mais que uma vez.
Número:_________________ Nome: _________________________________________
3
2. (2v) Sistema de Ficheiros
2.1) (0,5v) Apresente um comando T-SQL que permita criar uma base de dados com o
seguinte conjunto de características:
A base de dados deve chamar-se BDExameAOBD e deve conter ter dois ficheiros de dados,
ambos no filegroup primário. O primeiro ficheiro de dados deve ter um tamanho inicial de
100MB, e o segundo ficheiro deve ter um tamanho inicial de 200MB. A base de dados deve
ainda ter um ficheiro de log com um tamanho inicial de 50MB. Tanto os data files como o
ficheiro de log devem ter um tamanho máximo de 500MB, crescendo a uma taxa de 10%.
2.2) (0,5v) Considere que, para a base de dados da alínea anterior, se pretendiam armazenar os
dois ficheiros de dados em filegroups distintos (i.e., o primeiro ficheiro de dados no filegroup
primário e outro no secundário). Apresente a instrução T-SQL correspondente a este caso e
indique uma potencial vantagem desta divisão em dois filegroups distintos.
Número:_________________ Nome: _________________________________________
4
1.3) (1v) Indique se, no SQL Server 2008, cada base de dados pode ter vários ficheiros de
log. Em caso de resposta afirmativa, indique ainda se estes ficheiros de log podem ser
associados em filegroups distintos.
Número:_________________ Nome: _________________________________________
5
3. (4v) Índices
3.1) (2v) Considere uma árvore B+ com n=3 que representa um índice para um atributo do
tipo inteiro. Partindo de uma árvore vazia, desenhe o resultado dos vários passos de inserção à
medida que insere os elementos 62, 81, 35, 41, 13, 22, 73, por esta ordem.
Número:_________________ Nome: _________________________________________
6
3.2) (2v) Desenhe o resultados dos vários passos de remoção à medida que remove os
mesmos elementos da alínea anterior, pela mesma ordem (i.e. do elemento 62 até ao elemento
73) até chegar a uma árvore vazia.
Número:_________________ Nome: _________________________________________
7
4. (4v) Processamento e optimização de interrogações
Considere o seguinte esquema relacional e a seguinte interrogação sobre o esquema.
LOJA(IDL,NOME,LOCALIZACAO,VVENDAS)
// 1000 tuplos - IDL chave primária
EMPREGADO(BI,IDL,IDD,SALARIO)
// 20000 tuplos - BI chave primária
DEPARTAMENTO(IDD,IDL,NOME)
// 1000 tuplos - IDD chave primária
SELECT
FROM
WHERE
AND
AND
AND
AND
E.BI, L.IDL, D.NOME
EMPREGADO E, LOJA L, DEPARTAMENTO D
D.IDL=L.IDL
L.VVENDAS<500.000
E.IDL=D.IDL
E.IDD=D.IDL
E.SALARIO>2500
Assuma que o atributo EMPREGADO.SALARIO está uniformemente distribuído no
intervalo 500-5000 e que o atributo LOJA.VENDAS está uniformemente distribuído no
intervalo 100.000-800.000. Assuma ainda que existe um índice clustered no atributo
EMPREGADO.SALARIO, um índice clustered em LOJA.IDL, e um índice clustered em
DEPARTAMENTO.IDD.
4.1) (0,5v) Explique por palavras suas qual o resultado obtido pela interrogação SQL que se
apresenta.
4.2) (1v) Liste os planos de execução considerados por um optimizador semelhante ao do
sistema-R para a interrogação apresentada.
Número:_________________ Nome: _________________________________________
8
4.3) (1,5v) Dos planos considerados anteriormente, qual seria o que apresenta um custo
estimado mais baixo?
Número:_________________ Nome: _________________________________________
9
4.4) (1v) Se o índice em DEPARTAMENTO.IDD fosse unclustered, o custo estimado
anteriormente mudaria substancialmente? Justifique a sua resposta
Número:_________________ Nome: _________________________________________
10
5. (4v) Transacções, controlo de concorrência e gestão de recuperação
5.1) (1v) Considere a técnica de checkpointing no sistema de recuperação de um dado SGBD.
a) (0,5v) Indique como a frequência dos checkpoints afecta o desempenho do sistema
quando não ocorrem falhas.
b) (0,5v) Indique como a frequência dos checkpoints afecta o tempo necessário para
recuperar de uma falha do sistema.
5.2) (2v) Considere um esquema de recuperação que use modificação imediata. Mostre,
através de um exemplo, como a base de dados pode ficar num estado inconsistente se não fôr
garantido que o log de recuperação é escrito em disco antes das páginas de dados.
Número:_________________ Nome: _________________________________________
11
5.3) (1v) Quais as vantagens e desvantagens de usar uma técnica de modificação imediata,
quando comparada com uma técnica de modificação diferida?
Número:_________________ Nome: _________________________________________
12
6. (4v) Database tuning
Considere a seguinte relação normalizada, em que a chave primária é o atributo BI:
Empregados(BI, NOME, ORDENADO, DEPARTAMENTO, ANO-CONTRATAÇÃO)
Existem quatro interrogações igualmente frequentes e importantes:
a) Qual o número médio de empregados por departamento?
b) Quais os B.I.s de empregados com o salário mais alto?
c) Qual o gasto em salários efectuado por cada departamento?
d) Quantos empregados foram contratados no ano corrente?
6.1) (1v) Que índices criaria sobre a relação? Para cada índice, indique qual o tipo (i.e., hash
ou B+tree) e indique se o índice seria clustered ou non-clustered. Justifique a sua resposta?
Número:_________________ Nome: _________________________________________
13
6.2) (1v) Sugeriria a utilização de algum esquema de particionamento horizontal, como forma
de melhorar o desempenho das interrogações? Justifique a sua resposta.
6.3) (1v) Que alterações, ao nível do modelo de dados, poderiam trazer benefícios em termos
de desempenho? Justifique a sua resposta.
Número:_________________ Nome: _________________________________________
14
6.4) (1v) Considere que o SGBD apenas implementa os níveis de isolamento READ
COMMITED e SERIALIZABLE. Qual o nível de isolamento mais indicado para cada uma
das interrogações? Justifique a sua resposta.