• Nenhum resultado encontrado

Administração e Optimização de BDs

N/A
N/A
Protected

Academic year: 2021

Share "Administração e Optimização de BDs"

Copied!
11
0
0

Texto

(1)

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

(2)

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:

(3)

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.

(4)

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).

(5)

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.

(6)

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.

(7)

IST/DEI

Pág. 7 de

11

(8)

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.

(9)

IST/DEI

Pág. 9 de

11

(10)

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.

(11)

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.

Referências

Documentos relacionados

O gráfico nº11 relativo às agências e à escola representa qual a percentagem que as agências tiveram no envio de alunos para a escola Camino Barcelona e ainda, qual a percentagem de

Os candidatos reclassificados deverão cumprir os mesmos procedimentos estabelecidos nos subitens 5.1.1, 5.1.1.1, e 5.1.2 deste Edital, no período de 15 e 16 de junho de 2021,

DEPARTAMENTO DE GENÉTICA Unidade de Citogenética Unidade de Genética Médica Unidade de Genética Molecular Unidade de Rastreio Neonatal Unidade de Investigação e

 Caminho simples que contém todas as arestas do grafo (e,. consequentemente, todos os

Este trabalho tem como objetivo contribuir para o estudo de espécies de Myrtaceae, com dados de anatomia e desenvolvimento floral, para fins taxonômicos, filogenéticos e

Changes in the gut microbiota appears to be a key element in the pathogenesis of hepatic and gastrointestinal disorders, including non-alcoholic fatty liver disease, alcoholic

A variável em causa pretende representar o elemento do modelo que se associa às competências TIC. Ainda que o Plano Tecnológico da Educação preveja a conclusão da

libras ou pedagogia com especialização e proficiência em libras 40h 3 Imediato 0821FLET03 FLET Curso de Letras - Língua e Literatura Portuguesa. Estudos literários