Mini-Projecto 1
IST/DEI
Pág. 1 de
11
A resolução deve ser claramente identificada com o número de grupo e entregue sob a
forma de um relatório impresso, seguindo o template dado na página da cadeira.
Deve ainda ser feita uma entrega electrónica do mesmo relatório (em formato PDF).
1. Considerando o SGBD relacional SQL Server 2008, responda às seguintes questões:
a. Indique qual o comando T-SQL que permite criar uma base de dados (BD) com o
seguinte conjunto de características:
A BD deve chamar-se BaseDeDadosAOBD e deve conter um ficheiro de log e cinco ficheiros
de dados em três “filegroups” diferentes (i.e., dois “ficheiros” de dados no “filegroup”
primário, dois ficheiros de dados num “filegroup” secundário, e um ficheiro de dados num
segundo “filegroup” secundário). O ficheiro de log deve ter um tamanho inicial de 25MB e
um tamanho máximo de 350MB, enquanto que os ficheiros de dados devem ter um
tamanho máximo ilimitado. O ficheiro de dados no segundo “filegroup” secundário deve
ter um tamanho inicial de 50MB, e os restantes devem ter um tamanho inicial de 180MB.
Todos os ficheiros devem crescer a uma taxa de 20%, exceptuando-se o ficheiro de dados
no segundo “filegroup” secundário que deve crescer em 51200KB, sempre que necessário.
b. Ao criar uma tabela de nome TabelaNaBaseDeDadosAOBD na base de dados
BaseDeDadosAOBD, seria possível associar explicitamente o armazenamento da
tabela TabelaNaBaseDeDadosAOBD a um dos ficheiros de dados que formam a
base de dados? Justifique a sua resposta.
c. Seria possível efectuar a criação da base de dados BaseDeDadosAOBD sem a
indicação de um ficheiro de log? Justifique a sua resposta.
d. Indique uma potêncial vantagem (i.e., uma situação em que existisse um claro
benefício na utilização) para cada uma das seguintes situações:
i. Existência de uma base de dados no SQL Server 2008 com vários
ficheiros de dados dentro de um único filegroup.
ii. Existência de uma base de dados no SQL Server 2008 com vários
filegroups distíntos.
2. Considerando a base de dados da alínea anterior, responda às seguintes questões:
a. Qual o comando T-SQL para criar uma tabela sobre a base de dados
IST/DEI
Pág. 2 de
11
A tabela deverá chamar-se TabelaNaBaseDeDadosAOBD, tendo um atributo alfanumérico
com um tamanho máximo de 10 caracteres e o nome IdentificadorAlfanumérico, um atributo
alfanumérico de nome Dados com um tamanho máximo de 50 caracteres, e um atributo de
nome CodigoAuxiliar correspondente a um valor numérico que assume valores únicos na
relação (i.e., permite identificar cada tuplo da relação). A tabela deve ser particionada por
forma a que todos os tuplos cujo CodigoAuxiliar seja superior a ”500” e inferior a “5000”
fiquem no segundo filegroup, os tuplos cujo CodigoAuxiliar seja superior a “50” e inferior a
“500” fiquem no terçeiro filegroup, e todos os restantes tuplos fiquem no filegroup primário.
b. Indique se o SQL Server 2008 iria construir automáticamente algum índice sobre a
tabela
TabelaNaBaseDeDadosAOBD
desta pergunta. Em caso afirmativo, indique o
tipo do índice, indique se o mesmo seria clustered ou non-clustered, e indique se o
mesmo poderia ser esparso, justificando a sua resposta. Em caso afirmativo,
indique ainda qual a razão de fundo que levaria o sistema a executar esta operação
sem intervenção explícita do utilizador.
c. Indique uma vantagem em ter a tabela
TabelaNaBaseDeDadosAOBD
particionada da
forma descrita na primeira alínea deste exercício. Exemplifique a vantagem através
de um cenário concreto envolvendo diferentes dispositivos de armazenamento
(e.g., discos rígidos SCSI e IDE).
3. Considere que na tabela da pergunta anterior poderia existir um índice do tipo B+Tree
sobre o atríbuto alfanumérico de nome Dados.
a. Indique um comando T-SQL para criar um índice B+Tree sobre o atríbuto de
nome Dados, considerando que o índice deve incluir também o atributo
IdentificadorAlfanumérico, embora este não faça parte da chave de pesquisa.
b. Considere que a estrutura de dados do tipo B+Tree da alínea anterior usaria um
número máximo de 3 apontadores em cada bucket, e que os valores se
encontram ordenados alfabeticamente. Mostre a estrutura da árvore B+Tree
resultante da inserção sequencial de cada um dos seguintes valores: D, F, C, E,
F, A, B. Desenhe claramente a árvore resultante de cada passo de inserção.
No contexto desta pergunta, pode ignorar os valores do atributo
IdentificadorAlfanumérico.
c. Sobre o resultado da alínea anterior, mostre qual a árvore resultante após a
remoção sequencial de cada um dos seguintes valores: D, E, C, B, F, E, A.
Desenhe a árvore resultante de cada passo de remoção.
d. Sobre a árvore resultante da alínea b), indique os passos envolvidos no
algoritmo de pesquisa sobre a árvore B+Tree para as seguintes duas situações:
IST/DEI
Pág. 3 de
11
ii. Encontrar os registos com chaves de pesquisa tomando valores entre A e
C, inclusive.
4. Considere uma estrutura de dados do tipo extendable hash onde cada bucket
armazena 2 registos. Considere ainda os seguintes registos e a chaves de hash que
lhes correspondem:
Primeiro
0000
Segundo
1001
Terceiro
0010
Quarto
0100
Quinto
1011
Sexto
0110
Sétimo
1000
Indique qual a estrutura obtida após a inserção sequencial de cada um dos valores
acima, em cada um dos passos. Assuma que a ordem para a qual devem considerar os
bits das chaves de hash é do bit menos significativo (direita) para o bit mais
significativo (esquerda).
5. Considere uma base de dados no sistema SQL Server 2008, por exemplo a base de
dados Adventure Works usada como demonstrador do sistema.
a. Dê um exemplo de uma interrogação T-SQL onde a existência de um índice
B+Tree, criado com a opção INCLUDE (i.e., um covering index) e com uma
ordenação específica das chaves de pesquisa, poderia trazer benefícios. Indique
qual o esquema das relações envolvidas e justifique o porquê da sua escolha,
apresentando também uma instrução T-SQL para criar o índice.
b. Dê um exemplo de uma interrogação T-SQL onde, caso o SQL Server 2008 tivesse
suporte para tal, seria mais útil um índice do tipo hash do que um índice do tipo
B+Tree. Indique qual o esquema das relações envolvidas e justifique o porquê da
sua escolha.
c. Dê um exemplo de uma interrogação T-SQL onde a utilização de um índice bitmap
poderia trazer benifícios. Indique qual o esquema das relações envolvidas e quais
os valores que os atributos envolvidos podem tomar. Justifique a sua escolha.
IST/DEI
Pág. 4 de
11
Abaixo encontram-se as resoluções para os problemas propostos no Mini-Projecto 1.
Resolução do Problema 1
Pergunta 1.A
CREATE DATABASE BaseDeDadosAOBD ON PRIMARY
(NAME = BaseDeDadosAOBD_Primary_1, FILENAME=BaseDeDadosAOBD_Primary_F1.mdf, size=180MB, FILEGROWTH=20%),
(NAME = BaseDeDadosAOBD_Primary_2, FILENAME = BaseDeDadosAOBD_Primary_F2.mdf, size = 180MB, FILEGROWTH = 20%),
FILEGROUP SECONDARY_FIRST
(NAME = BaseDeDadosAOBD_Secondary_1_F1, FILENAME=BaseDeDadosAOBD_Secondary_1_F1.ndf, size=180MB, FILEGROWTH=20%),
(NAME = BaseDeDadosAOBD_Secondary_1_F2, FILENAME=BaseDeDadosAOBD_Secondary_1_F2.ndf, size=180MB, FILEGROWTH=20%),
FILEGROUP SECONDARY_SECOND
(NAME = BaseDeDadosAOBD_Secondary_2_F1, FILENAME=BaseDeDadosAOBD_Secondary_2_F1.ndf, size=50MB, FILEGROWTH=51200KB),
LOG ON
(NAME = BaseDeDadosAOBD_Log, FILENAME=BaseDeDadosAOBD_Log.ldf, size=25MB, MAXSIZE=350MB, FILEGROWTH=20%);
Pergunta 1.B
Não seria possível associar explicitamente o armazenamento da tabela
TabelaNaBaseDeDadosAOBD a um dos ficheiros de dados, dado que o SQL Server 2008
apenas permite que os utilizadores associem explicitamente tabelas, índices ou partições de tabelas/índices a filegroups existentes.
Pergunta 1.C
Não seria possível criar a base de dados sem pelo menos um ficheiro correspondente ao log transaccional, dado que esta informação è fundamental à realização das operações de recuperação associadas ao rollback de transacções e à recuperação de falhas.
Pergunta 1.D
No primeiro caso, i.e. vários ficheiros num único filegroup, o sistema pode particionar os dados por vários ficheiros, podendo-se desta forma ter base de dados maiores do que o tamanho disponível num único disco rígido e podendo o sistema realizar leituras/escritas em paralelo.
No segundo caso, i.e. vários ficheiros em vários filegroups, temos todas as vantagens associadas ao primeiro caso, podendo ainda os utilizadores associar partições específicas dos dados a filegroups distintos, optimizando desta forma o armazenamento dos dados de acordo com os padrões de utilização associados aos mesmos (e.g., colocando informação frequentemente acedida em dispositivos de armazenamento mais rápidos).
IST/DEI
Pág. 5 de
11
Resolução do Problema 2
Pergunta 2.A
USE BaseDeDadosAOBD;
CREATE PARTITION FUNCTION BaseDeDadosAOBD_Range1(INT) AS RANGE LEFT FOR VALUES (50,500,5000);
CREATE PARTITION SCHEME BaseDeDadosAOBD_PartScheme1 AS PARTITION BaseDeDadosAOBD_Range1 TO
([PRIMARY], SECONDARY_SECOND,SECONDARY_FIRST,[PRIMARY]);
CREATE TABLE TabelaNaBaseDeDadosAOBD ( IdentificadorAlfanumérico VARCHAR(10), Dados VARCHAR(50),
CodigoAuxiliar INT NOT NULL UNIQUE) ON BaseDeDadosAOBD_PartScheme1(CodigoAuxiliar);
Pergunta 2.B
O atributo CodigoAuxiliar foi definido como a única chave candidata (i.e., um atríbuto UNIQUE e a relação não tem uma chave primária) e, como tal, o SQL Server 2008 cria um índice primário (i.e., clustered) automáticamente sobre este atributo. A razão pela qual o sistema cria automáticamente este índice relaciona-se com o facto dos atributos chave relaciona-serem frequentemente utilizados em várias operações de selecção, inclusívem em joins entre tabelas. Estes índices podem portanto optimizar significativamente o desempenho da base de dados. O índice poderia ser esparso, uma vez que é clustered (i.e., os tuplos da relação seguem a mesma ordem da chave de pesquisa do índice, e como tal não é estritamente necessário armazenar todas as chaves de pesquisa do índice).
Pergunta 2.C
Podemos utilizar as partições por intervalos de valores por forma a associar explicitamente as partições dos dados a filegroups, consoante os seus padrões de acesso. Por exemplo, o intervalo de valores correspondendo aos tuplos mais acedidos pode ser associado a um filegroup em que os ficheiros estejam colocados num dispositivo de armazenamento mais rápido, enquanto que os restantes intervalos de valores, eventualmente correspondendo a um número de tuplos maior e com um número de acessos mais reduzido, pode ser colocado num filegroup em que os ficheiros sejam maiores e se encontrem colocados em dispositivos de armazenamento diferentes, eventualmente mais lentos mas também mais baratos ou mais fiáveis.
IST/DEI
Pág. 6 de
11
Resolução do Problema 3
Pergunta 3.A
USE BaseDeDadosAOBD;
CREATE INDEX dados_index ON TabelaNaBaseDeDadosAOBD(Dados)
INCLUDE(IdentificadorAlfanumérico) ON BaseDeDadosAOBD_PartScheme1 (Dados));
Pergunta 3.B
Nota: As árvores B+ não suportam directamente o armazenamento de elementos
repetidos, sendo necessária a realização de alterações nos algoritmos de inserção e remoção de valores. Soluções comuns para o armazenamento de valores duplicados em árvores B+ envolvem o uso de um mecanismo externo de resolução das colisões, por exemplo através de uma lista de valores ligados associada às folhas da árvore B+. Na resolução apresentada para este exercício não se tratar a inserção de elementos duplicados.
IST/DEI
Pág. 7 de
11
IST/DEI
Pág. 8 de
11
Pergunta 3.D.i
Começar na raiz da árvore B+ e percorrer o nó raiz até se encontrar um valor maior que F. Como esse valor não existe, segue-se o ponteiro correspondente a nós >= E. Repete-se o processo de pesquisa em cada nó intermédio da árvore B+ até chegarmos aos nós folha. Ao chegarmos a um nó folha, inicia-se uma pesquisa sequencial até se encontrar o valor F, terminando nesse caso ou ao encontrar o primeiro valor >F.
Pergunta 3.D.ii
Começar na raiz da árvore percorrer o nó raiz até se encontrar um valor maior que A. Como na raiz só existe o valor E, e sendo este maior que A, segue-se o ponteiro correspondente a nós < E. Repete-se o processo de pesquisa em cada nó intermédio da árvore B+ até chegarmos aos nós folha. Ao chegarmos a um nó folha inicia-se uma pesquisa sequencial até se encontrar o primeiro valor >= A. De seguida, pesquisa-se sequencialmente os valores seguintes nos nós folha da árvore B+, até se encontrar um valor > C, retornando neste caso os valores A, B e C.
IST/DEI
Pág. 9 de
11
IST/DEI
Pág. 10 de
11
Resolução do Problema 5
Pergunta 5.A
SELECT EmployeeID, AddressID FROM HumanResources.EmployeeAddress WHERE EmployeeID>10 and EmployeeID<12;
CREATE INDEX index1 ON
HumanResources.EmployeeAddress (EmployeeID ASC) INCLUDE (AddressID);
Como a interrogação possui um intervalo na condição de pesquisa, e como a condição de pesquisa é muito selectiva, sería útil ter um índice B+Tree. Como além do atributo envolvido na chave de pesquisa a querie envolve também um segundo atributo, e como o número de atributos na relação é elevado, seria útil incluir esse segundo atributo na chave de pesquisa do índice.
IST/DEI
Pág. 11 de
11
Pergunta 5.B
SELECT EmployeeID FROM HumanResources.EmployeeAddress WHERE EmployeeID=10;
Como a interrogação possui um pesquisa por igualdade sobre um atríbuto chave, numa relação com muitos tuplos, seria sería útil ter um índice Hash.
Pergunta 5.C
SELECT EmployeeID FROM HumanResources.Employee
WHERE SalariedFlag=0 and Gender='F' and MaritalStatus='S';
A querie envolve selecções sobre um conjunto de atributos que pode assumir um número de valores muito reduzido, tendo-se que a criação online de um índice bitmap poderia ajudar na filtragem dos resultados.
