UNIVERSIDADE DE SÃO PAULO
INSTITUTO DE CIÊNCIAS MATEMÁTICAS E DE COMPUTAÇÃO DEPARTAMENTO DE CIÊNCIAS DE COMPUTAÇÃO
SCC0141 - Bancos de Dados e Suas Aplicações
Prof. José Fernando Rodrigues Júnior – 2º semestre de 2011
Lista de Exercícios 13 – Bancos de Dados Distribuídos/Data Warehouse
A ser vistada em 28/11/2011
Nomes:_______________________________________________________________________________
BDD
1. Quais as vantagens e desvantagens dos SGBD Distribuídos em relação aos SGBD Centralizados?
Vantagens
disponibilidade; acesso distribuído localidade desempenho análise distribuída de dados escalabilidade.
Desvantagens
Maior complexidade de projeto (fragmentação/replicação) Necessidade de políticas de sincronização
Necessidade de estrutura de rede
Necessidade de software gerenciador do BDD
2. Qual diferença entre replicação síncrona e assíncrona incluindo, discutindo os problemas apresentados por cada uma delas.
replicação síncrona: todas as cópias são atualizadas dentro de uma mesma transação problema? – tráfego intenso de rede
replicação assíncrona: atualizações periódicas das cópias de dados modificados
problema? – controle de transação mais elaborado é necessário, alguns dados não tem garantia de consistência e não devem ser lidos
3. Discuta os tipos de transparência de dados em um SGBDD na perspectiva de operações de consulta e de atualização.
A transparência se refere à separação entre os detalhes de implementação e a semântica do sistema. Os detalhes de como o sistema é distribuído devem ser “escondidos” de quem o usa, sendo acessíveis apenas àqueles que o gerenciam.
de distribuição localização nomenclatura
todos os sites devem ser acessados independentemente de sua localização (sem que detalhes sobre a rede sejam necessários) física por meio de uma nomenclatura padronizada
de replicação
todos os sites devem acessar os dados com a suposição de que possuem a versão mais atualizada das informações; os usuários também não devem se envolver com as tarefas de replicação, ou mesmo ter conhecimento de que os dados são replicados
de fragmentação horizontal (WHERE) vertical (SELECT)
todos os sites devem acessar os fragmentos com a suposição de que possuem todas as informações de que precisam; as consultas devem ser executadas sem que seja necessário conhecer os problemas de otimização de consultas distribuídas
4. Consulte a apresentação da aula 21 e explique como funciona o controle de transações 2PC. 5. Considere as relações
Aluno = {Aluno_id (20 B), Nome (30 B), Sobrenome (40 B), Idade (10 B)} site 1, 20 mil tuplas, 10% Idade > 20 Curso = {Curso_id (20 B), Nome_curso (30 B), Ementa (100 B)} site 2, 50 tuplas
Matricula = {Aluno_id (20 B), Curso_id (20 B), Data(10 B)} site 3, 200 mil tuplas,
cada aluno tem 10 matriculas Para a seguinte consulta
Site 1: SELECT Nome, Sobrenome, Nome_curso, Data FROM Aluno A, Matricula M, Curso C
WHERE A.Idade > 20 AND
A.Aluno_id = M.Aluno_id AND M.Curso_id = C.Curso_id;
Determine como a consulta distribuída poderia ser executada por meio de duas semi-junções simultâneas, calculando o tráfego da rede para cada etapa.
Site 1: C1 = SELECT Aluno_id FROM Aluno
WHERE Idade > 20; 2mil * 20 = 20 mil B Site 3
Site 2: C2 = SELECT Curso_id, Nome_curso
FROM Curso; 50 * (20+30) = 2500B Site 3 Site 3: C3 = SELECT Aluno_id, Nome_curso, Data
FROM C1, Matricula M, C2
WHERE C1.Aluno_id = M.Aluno_id AND
M.Curso_id = C2.Curso_id; 2mil * 10 * (20+30+10) = 1.200.000B Site 1 Site 1: C4 = SELECT Nome, Sobrenome, Nome_curso, Data
FROM Aluno, C4
WHERE Aluno.Aluno_id = C4.Aluno_id; Tráfego total: 20.000 + 2.500 + 1.200.000 = 1.222.500 B
6. Considere as seguintes relações
Livros = {Num_livro, Autor_principal, Assunto, Estoque_total, preco}
Livraria = {Num_livraria, Cidade, Estado, CEP, Valor_estoque_total}
Estoque = {Num_livraria, Num_livro, Qtd, UltimaAtualizacao, Funcionario}
Estoque_total é o número total de livros em estoque e Valor_estoque_total é o valor de estoque total para a loja em reais.
a) Defina dois possíveis partiticionamentos horizontais para a relação Livraria.
- particionamento por um dos atributos Cidade, Estado ou CEP localidade
- particionamento por faixas de Valor_estoque_total, de acordo com o tamanho das lojas particionamentos de dados orientado a grande lojas e subsidiárias (hierarquização)
b) Como um particionamento horizontal derivado de Estoque seria baseado no particionamento de Livraria?
- o particionamento de Estoque poderia seguir o mesmo particionamento de Livraria por meio da chave estrangeira Num_livraria;
- por exemplo:
CREATE MATERIALIZED VIEW Estoque_cep_x AS SELECT Num_livraria, Num_livro, Qtd
FROM Estoque
WHERE Estoque.Num_livraria IN (SELECT Num_livraria FROM Livraria
WHERE CEP = ‘cep_x’);
c) Também é possível fragmentar verticalmente Estoque, quais atributos você escolheria? Escreva o correspondente SQL.
CREATE MATERIALIZED VIEW Estoque_vertical AS SELECT *
FROM Estoque
7. Considere a seguinte relação
Empregado = {Nome, Cidade, Salario, UnidadeDeLotacao}
fragmentada horizontalmente pelo atributo UnidadeDeLotacao. Assuma que os fragmentos encontram-se no site local (UnidadeDeLotacao) e também em um site central SP. Considerando o processamento distribuído de consultas, descreva como cada uma das seguintes operações será realizada a partir da UnidadeDeLotacao BH:
a) Encontre todos os empregados que estão na UnidadeDeLotacao RJ;
Envio da consulta SELECT Nome FROM EMPREGADO, para o site RJ Recebimento do resultado enviado pelo site RJ para o site BH.
b) Calcule a média salarial de todos os empregados;
Cálculo da média salarial no site central SP;
DW
1. Descreva o modelo de dados multidimensional. Explique a distinção entre medidas e dimensões, e entre tabelas de fatos e tabelas de dimensões. O que é um esquema estrela? Ilustre estes conceitos.
O modelo multidimensional é constituído por tabelas de contexto relacionadas a uma tabela central (fatos) segundo relacionamentos 1:N. Na tabela de fatos, as medidas são a informação de interesse para a análise, para a geração de relatório e para técnicas OLAP. O esquema estrela é a modelagem multidimensional mais simples e mais comum.
2. As informações sobre operações diárias de uma organização são armazenadas em bancos de dados operacionais. Por que seria usado um data warehouse?
O datawarehouse visa criar uma réplica dos dados operacionais: a partir de múltiplas fontes, limpos e validados, em um formato padronizado, e em armazenamento otimizado para operações de leitura.
3. Em um processo de data warehousing, explique o que são e quais são os principais aspectos a serem tratados nas seguintes tarefas: extração, transformação e carregamento.
Extração:
extração de múltiplas fontes;
consolidação e integração de dados de múltiplas fontes; Transformação (não necessariamente nesta ordem):
seleção: escolha das colunas de interesse;
limpeza: remoção da tuplas fora do escopo temporal, e dos interesses de análise; remoção de dados espúrios ou irrecuperáveis;
validação: verificação da integridade, padronização, e consistência dos dados; conversão dos dados para o modelo do DW: conversão de tipos, nomes e regras;
remodelagem: tradução dos esquemas operacionais para o esquema histórico multidimensional; derivação (processamento): cálculo de valores de interesse para análise, tais como totalizações,
produtos e sumarizações (agregações);
normalização: quebra de atributos compostos e multi-valorados;
de-normalização: junção de dados de diferentes relações seguindo o esquema do DW; Carregamento:
armazenamento de acordo com o modelo do DW; criação e manutenção de estruturas de dados; criação e manutenção de caminhos de acesso; tratamento de dados que variam no tempo; suporte a atualização.
4. Qual o relacionamento entre data warehousing e replicação de dados? Que forma de replicação (síncrona ou assíncrona) é mais eficiente para um DW? Por que?
Um DW é uma replicação dos dados operacionais. A replicação assíncrona é mais adequada, pois o dw trabalha com massivos volumes de dados históricos; assim, a atualização dos dados é mais eficiente se feita em lotes, bastando que os dados estejam atualizados com relação a um marco temporal de interesse, não com relação à base operacional.
5. Especifique um domínio de aplicação (diferente do que foi usado em aula) em que seria interessante a criação de um data warehouse. No contexto escolhido, especifique:
a. o propósito do DW;
Um banco, para o qual o DW ofereceria análises das operações dos clientes, especialmente sua movimentação financeira e os serviços consumidos.
b. a tabela de fatos e as tabelas de dimensões;
Fato = {Cliente_id, Tempo_id, Operacao_id, Nro_operacoes, Valor_operacoes, Custo_servicos} Cliente = {Cliente_id, Tipo_cliente (físico/jurídico), Nome}
Tempo = {Tempo_id, Ano, Mês, Dia}
Operacao = {Operacao_id, Tipo (Credito, Debito, Emprestimo,...), Variacao (CC, Poupanca, CI, ...)}
c. principais operações OLAP a serem aplicadas: indique a operação (drill-down, roll-up, etc...), ilustre uma consulta (semântica) associada à operação e mostre uma maneira de implementá-la em SQL. Explore os recursos ROLLUP e CUBE também.
6. Defina uma tabela, instancie dados (em torno de 20 tuplas) e calcule o resultado de uma operação GROUP BY CUBE e de uma operação GROUP BY ROLLUP sobre dois atributos.
7. As operações OLAP comuns são: roll-up, drill-down, pivoting, slicing e dicing. Descreva cada uma delas e ilustre com exemplos. Descreva, em termos gerais, como cada uma delas pode ser implementada em SQL.
