ProjetoFísico

Prévia do material em texto

Prof. Luiz A.Prof. Luiz Vivacqua
Administração de Banco de Dados
Projeto Físico de Banco de Dados
Prof. Luiz A.Prof. Luiz Vivacqua
• “É uma atividade na qual o objetivo não é
apenas obter uma estrutura de dados apropriada 
para armazenamento, mas desenvolve-lo de 
maneira que garanta um bom desempenho.”
» Navathe
Projeto Físico
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Considerações sobre desempenho
� Normalização X Desnormalização
� Índice
� Estruturas de Armazenamento
� Ajuste de SQL
�Recomendações de ambiente
�PostgreSql
�Arquitetura
�Armazenamento
�Usuário
�Utilitários
�SQL
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Normalização
�Elimina anomalias de modificação
�Elimina redundância
�Facilita manutenção
�Reduz o espaço de armazenamento
� Normalização tende a produzir numerosas relações.
– As consultas nas relações normalizadas requerem muitas 
junções.
– Muitas junções e muitas relações reduzem a performance 
geral
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Desnormalização
� “O processo de armazenar a junção de relações 
definidas em formas normais de mais alta ordem em 
uma relação base ( que está definida em uma forma 
normal de mais baixa ordem ) é conhecido como 
desnormalização”
• Elmasri/Navathe – Fundamentals of Database Systems
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Candidatos a Desnormalização
� Tabelas envolvidas em relacionamentos 1:1
� Tabelas envolvidas em relacionamentos n:n
� Tabelas usadas para validar domínio
� Relacionamentos 1:n onde n é conhecido e pequeno
� Dados derivados
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Relacionamento 1:1
Funcionário Contrato
1 1
Funcon
Incluir duas relações envolvidas em um 
Relacionamento 1:1 em uma única relação
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
Numero Nome Endereço
1 Luiz Silveira Rua 5 de julho no 30
2 Carlos Sa Av maracana 101 
3 Maria Lins Sao clemente 30/101
Id Funcionário salário
10 1 1000
20 2 1500
22 3 5000
Funcionario Contrato
Numero Nome Endereço Id salário
1 Luiz Silveira Rua 5 de julho no 30 10 1000
2 Carlos Ramos Av maracana 101 20 1500
3 Maria Lins Sao clemente 30/101 22 5000
Funcon
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Relacionamento n:n
Funcionário Curso
n n
Funcionário Curso
1 n
Armazenado como 1:n, curso terá registros duplicados
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
Numero Nome Endereco
1 Luiz Silveira Rua 5 de julho no 30
2 Carlos Sa Av maracana 101 
3 Maria Lins Sao clemente 30/101
Codigo Nome
1 Segurança
5 Rede
6 Sql
7 Java
Numero codigo
1 1
1 5
1 6
1 7
2 5
2 7Curso
Funcionario Realizou
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
Numero Nome Endereco
1 Luiz Silveira Rua 5 de julho no 30
2 Carlos Sa Av maracana 101 
3 Maria Lins Sao clemente 30/101
Codigo Nome Numero
1 Segurança 1
5 Rede 1
6 Sql 1
7 Java 1
5 Rede 2
7 Java 2
Funcionario
Curso
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Tabelas usadas para validar domínio
� Tabelas de domínio são geralmente vistas em banco 
de dados na forma codigo -> informação
� Exemplo:
• cidade(id,nome)
� Para desnormalizar, simplesmente armazena-se o 
campo informação na relação onde o dado é
referenciado.
� Isto tem o efeito de re-introduzir dependências 
transitivas no banco de dados.
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
Numero Nome CodCidade
1 Luiz Silveira 5
2 Carlos Sa 6
3 Maria Lins 5
Funcionario Cidade
Codigo Nome
5 Rio de janeiro
6 Sao Paulo
Numero Nome Codigo Cidade
1 Luiz Silveira 5 Rio de janeiro
2 Carlos Sa 6 Sao Paulo
3 Maria Lins 5 Rio de janeiro
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Relacionamentos 1:n onde n é conhecido e pequeno
� Considere que um funcionário pode desempenhar várias 
funções e uma funçao tem até 3 tarefas.
Funcionario(numero,nome)
Função(Titulo,descriçao,numeroFunc)
Tarefa(funcaoTitulo,nomeTarefa)
Funcionario Funcao Tarefa1
1* *
Pode ser implementado como:
Funcionario(numero,nome)
FunçãoTarefa(Titulo,descriçao,numeroFunc,tarefa1,tarefa2,tarefa3)
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Dados derivados
� Dados derivados não são armazenados diretamente no banco 
de dados mas calculados a partir dos dados do banco.
� Alguns sistemas gerenciadores de banco de dados 
disponibilizam recursos para o gerenciamento de dados 
derivados no banco tais como views materializadas.
� “Materialized View” (Visões materializadas)
• Totalizações/Uniões (Bancos de apoio a decisão – Data 
Warehouse)
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
Normalização/Desnormalização
Obter a população de Curitiba?
Select municipio, count(sexo)
From Geografia G, Domicilio D, Pessoa P
Where estado=‘PR’ and municipio=‘curitiba’
and G.id=id_geo and D.id=id_domi
Group by municipio; 
JUNÇÃO DAS 3 TABELAS
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
Normalização/Desnormalização
View materializada GEOTOT (estado, municipio, totpess)
Obter a população de Curitiba?
Select municipio, totpess
From GEOTOT
Where estado=‘PR’ and municipio=‘curitiba’;
Pré-armazenamento de totalizações para melhorar desempenho
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
Índice
�O que é ?
� Estrutura de dados mantida internamente pelo SGBD
� 2 campos:
• Valores dos atributos indexados (ordenados)
• Endereço da linha na tabela
Exemplo
TABELA FUNCIONARIO INDICE NA COLUNA NOME
CODIGO NOME CIDADE RID
300 MENDES RIO 3870
250 CASTRO RIO 4568
220 NEVES BH 1245
RID NOME
4568 CASTRO
3870 MENDES
1245 NEVES
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
Índice
� Finalidade
�Otimizar o acesso aos dados nas relações
�Analogia a um índice de um livro
�Acesso mais rápido ao registro no caso da chave de busca 
for igual ao campo indexado.
�Busca no arquivo índice é mais rápida porque o arquivo 
de índice é menor e é ordenado. Logo é possível fazer um 
acesso mais rápido via busca binária.
• Número de acessos (médio) em busca binária é logn .
• Acesso seqüencial (ordenado) pelo campo indexado.
� Desvantagens
☹ Maior espaço de armazenamento.
☹ Inclusão, exclusão, alteração mais lentos.
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Tipos
�Estrutura de dados
• Árvores B ou B+ ( comuns em chaves primárias)
• Alta seletividade
• Hash
�Forma
• Únicos (implementam a restrição “unique”)
• Compostos (definidos sobre múltiplos atributos)
�Quando usar?
� Em atributos usados como filtros (cláusula “where”) caso a 
freqüência de execução seja alta ou tempo de resposta 
insatisfatório.
� Em chaves estrangeiras (Facilitam a junção)
Prof. Luiz A.Prof. Luiz Vivacqua
Motivação: Desempenho fraco
� Indicações para o ajuste:
1. Consulta emite muitos acessos ao disco (por exemplo, uma consulta 
varre a tabela inteira).
2. O plano de consulta mostra que índices relevantes não estão sendo 
usados.
Projeto Físico
Ajuste de Consultas
Prof. Luiz A.Prof. Luiz Vivacqua
Alguns casos típicos de ajuste:
1. Expressões em comparações onde o índice não é usado
� Comparações com expressões: 
� salario/365 > 10
� Comparações numéricas de campos com diferentes tamanhos
� X = Y, onde X é integer e Y é smallint
� Comparações com NULL
� Data_nascimento IS NULL
� Comparações com substrings
� Nome like ‘%eira’
Muitos otimizadores não usam índices nestas situações
Projeto Físico
Ajuste de Consultas
Prof. Luiz A.Prof. Luiz Vivacqua
Casos típicos de ajuste:
2. Subconsultas com operador IN – índices não costumam ser 
usados pelos otimizadores
� SELECT MATRICULA FROM FUNCIONARIO
WHERE COD_DEPTO IN(SELECT CODIGO FROM DEPARTAMENTO
WHERE MATRICULA_GERENTE = 1235);
� PODE NÃO USAR O ÍNDICE EM COD_DEPTO
� SELECT MATRICULA FROM FUNCIONARIO, DEPARTAMENTO
WHERE COD_DEPTO = CODIGO AND MATRICULA_GERENTE = 1235;
� PODE USAR O ÍNDICE
Projeto Físico
Ajuste de Consultas
Prof. Luiz A.Prof. Luiz Vivacqua
Casos típicos de ajuste:
3. Uso da clausula “distinct”
� Muitas vezes seu uso é redundante. Normalmente causa uma operação 
de ordenação e deve ser evitado4. Ordem das tabelas na cláusula “FROM”
� Alguns otimizadores levam isso em consideração.
5. Divisão de consultas com múltiplos “OR”.
� Dividir e formular como uma UNIÃO de consultas.
� SELECT PNOME,UNOME,SALARIO,IDADE
FROM FUNCIONARIO WHERE IDADE > 45 OR SALARIO < 5000;
� SELECT PNOME,UNOME,SALARIO,IDADE
FROM FUNCIONARIO WHERE IDADE > 45 
UNION
SELECT PNOME,UNOME,SALARIO,IDADE
FROM FUNCIONARIO WHERE SALARIO < 5000;
PODE USAR ÍNDICES EM IDADE E SALÁRIO
Projeto Físico
Ajuste de Consultas
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Estruturas de Armazenamento
Lógica Física
Tablespace
Tabela
Índice
1
*
1
*
Arquivo1
*
Tablespace define a localização de
armazenamento para tabelas e índices
Nível Lógico
Usar tablespaces diferentes para cada projeto.
Usar tablespaces diferentes para índice e tabela
Nível Físico
Usar unidades de disco diferentes para índices
e tabelas, melhorando o “througput” de E/S
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
� Ambiente de desenvolvimento permite que as aplicações 
sejam testadas antes de entrarem em produção.
� Usar mesma versão do sistema operacional e sistema 
gerenciador de banco de dados.
� Se for possível usar também um ambiente de homologação
� Aplicação de patch e upgrade
� Testes de ferramentas auxiliares
� Documentar problemas e soluções
� Facilita o trabalho de suporte
� Testar a recuperação do backup antes de entra em produção
Ambientes de Desenvolvimento/Produção
Prof. Luiz A.Prof. Luiz Vivacqua
�PostgreSql
�Arquitetura
�Armazenamento
�Usuário
�Utilitários
�SQL
Projeto Físico
Prof. Luiz A.Prof. Luiz Vivacqua
�O PostgreSQL é um sistema de gerenciamento de banco 
de dados objeto-relacional, derivado do pacote 
POSTGRES escrito na Universidade da Califórnia em 
Berkeley.
�Qualquer plataforma Unix-compatível pode rodar 
PostgreSQL.
�A compatibilidade nativa para Windows está disponível 
desde a versão 8.0. Versões antigas do Windows 
baseadas no MSDOS (Win95, Win98, WinMe) podem 
rodar PostgreSQL usando Cygwin.
Projeto Físico
Prof. Luiz A.Prof. Luiz Vivacqua
�Fundamentos da Arquitetura
� O PostgreSQL utiliza o modelo cliente-servidor.
� O processo servidor, que gerencia os arquivos de banco de 
dados, aceita conexões dos aplicativos-cliente e executa 
ações no banco de dados em nome dos clientes. O programa 
servidor de banco de dados se chama postgres.
� O aplicativo-cliente do usuário (frontend), que deseja 
executar operações de banco de dados, pode ser uma 
ferramenta no modo caractere, um aplicativo gráfico, um 
servidor Web que acessa o banco de dados para mostrar 
páginas Web, ou uma ferramenta especializada para 
manutenção do banco de dados. 
� Alguns aplicativos-cliente são fornecidos na distribuição do 
PostgreSQL, sendo a maioria desenvolvido pelos usuários.
Projeto Físico
Prof. Luiz A.Prof. Luiz Vivacqua
�Armazenamento
� Esquema
• É uma coleção de tabelas, visões, índices, tipos de dados e 
funções.
• Facilita a organização lógica dos objetos do banco
• Por padrão, as tabelas (e outros objetos) são colocadas 
automaticamente no esquema chamado "public", presente 
em todos os banco de dados.
� Banco de Dados
• É uma coleção de esquemas. Quando um cliente se 
conecta a um servidor, ele especifica o banco de dados que 
ele quer acessar.
Projeto Físico
Prof. Luiz A.Prof. Luiz Vivacqua
� Tablespace
� Por padrão são criados dois tablespaces no PostgreSQL:
• pg_default – repositório padrão dos objetos e dados fornecidos 
pelos usuários
• pg_global – armazena o catálogo do servidor e templates
(protótipos) de banco de dados
� Para definir um tablespace é utilizado o comando CREATE 
TABLESPACE.
• CREATE TABLESPACE ´ST_TABELA´ OWNER vivacqua 
LOCATION 'c:/tablespace/tabelas';
Projeto Físico
Prof. Luiz A.Prof. Luiz Vivacqua
• Usuário
• Usuário é equivalente a Role com a exceção de que 
quando um usuário é criado é assumido que ele 
pode dar login enquanto que na Role isto não 
acontece. 
• Ex: create user vivacqua with createdb password 
'vivacqua'
Projeto Físico
Prof. Luiz A.Prof. Luiz Vivacqua
� Utilitários
� Backup
• Pgdump
• Pgrestore
� Carga / Extração
• Copy
� Recuperar o espaço em disco ocupado por registros deletados.
• Vacuum
� Coleta de estatísticas
• Analyse
Projeto Físico
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
• PGADMIN
• Ferramenta cliente para projeto e administração distribuída gratuitamente
• Permite executar comandos SQL (DDL e DML)
Prof. Luiz A.Prof. Luiz Vivacqua
�SQL - (Structured Query Language)
• Surgiu no início na década de 70 pela IBM. 
• Em1980 o produto mudou seu nome para SQL.
• “American National Standards Institute” é o mantenedor e 
responsável pelo padrão.
• A linguagem é enorme ( especificação contém + 2000 
páginas).
Os comandos de SQL podem ser usados interativamente como 
uma linguagem de consulta ou podem ser incorporados a 
programas de aplicações.
Projeto Físico
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
A Linguagem de Definição de Dados:
• Subcategoria de SQL que permite a criação e a alteração dos 
objetos do banco de dados. 
• Normalmente, é de uso exclusivo do Administrador de Banco 
de Dados; contudo, seu uso fica sujeito à política da empresa.
• PRINCIPAIS COMANDOS DDL:
– CREATE, ALTER, DROP TABLE
– CREATE, DROP INDEX
– CREATE VIEW
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Criando tabela
CREATE TABLE nome-da-tabela 
( definição-da-coluna1 , definição-da-coluna2, ..., definição-da-
colunaN )
ONDE
definição-da-coluna é definida como:
nome-da-coluna tipo-de-dado [CONSTRAINT]
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Principais tipos de dados numéricos
� smallint 2 bytes inteiro com faixa pequena -32768 a +32767
� integer 4 bytes escolha usual para inteiro -2147483648 a 
+2147483647
� bigint 8 bytes inteiro com faixa larga -9223372036854775808 a
� 9223372036854775807
� decimal variável precisão especificada pelo usuário sem limite
� numeric variável precisão especificada pelo usuário sem limite
� serial 4 bytes inteiro com auto-incremento 1 a 2147483647
�Principais tipos de dados de caracteres
� character(n), char(n)
� character varying(n), varchar(n) – limite 1GB
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
PRINCIPAIS CONSTRAINTS:
� Primary key – define a chave primária da tabela
� Foreign key – define uma chave estrangeira
� Unique – define que o valor da coluna é único 
� Not Null – especifica que a coluna não pode ter valores nulos
� Check – estabelece um domínio para os valores da coluna
Podem ser especificadas:
� na definição da coluna
� ao final da definição das colunas
� através do comando ALTER TABLE
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
� Exemplos
CREATE TABLE DEPARTAMENTO
( CODIGO INTEGER PRIMARY KEY,
NOME CHAR(20) NOT NULL,
STATUS INTEGER,
CIDADE CHAR(20)
)
CREATE TABLE FUNCIONÁRIO
( MATRICULA INTEGER PRIMARY KEY,
NOME VARCHAR (20) NOT NULL,
SEXO CHAR (1),
DEPTO INTEGER,
CONSTRAINT FK_DEP FOREIGN KEY(DEPTO) REFERENCES 
DEPARTAMENTO (CODIGO)
);
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Alterando a estrutura da tabela
� Adicionando coluna
� Removendo coluna
� Renomeando coluna
� Adicionando constraint
� Removendo constraint
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
SINTAXE:
ALTER TABLE nome-da-tabela
ADD CONSTRAINT ou
DROP CONSTRAINT ou
ADD COLUMN ou
DROP COLUMN ou
RENAME TO novo_nome_tabela ou 
RENAME nome_da_coluna TO novo_nome_coluna
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
Exemplos:
ALTER TABLE DEPARTAMENTO ADD TELEFONE CHAR(11);
TRANSFORMA A TABELA DE DEPARTAMENTO EM:
DEPARTAMENTO (CÓDIGO, NOME, STATUS, CIDADE, TELEFONE);
ALTER TABLE DEPARTAMENTO ADD CONSTRAINT 
FORNECEDOR_PK PRIMARY KEY (CODIGO);
CRIA A CHAVE PRIMÁRIA NA TABELA FORNECEDOR.
ALTER TABLE FUNCIONARIO 
ADD CONSTRAINT FK_DEPARTAMENTO FOREIGN KEY(DEPTO) 
REFERENCES DEPARTAMENTO( CODIGO);
CRIA O RELACIONAMENTO ENTRE UMA TABELA DEPARTAMENTO E A TABELA 
FUNCIONARIO.
ALTER TABLE FUNCIONÁRIO ADD CONSTRAINTCHK_SEXO
CHECK (SEXO IN (‘M’, ‘F’));
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Removendo uma tabela
SINTAXE:
DROP TABLE nome-da-tabela
Esta sentença não só remove a descrição da tabela do catálogo 
do banco, como todas suas instâncias, visões e índices 
definidos sobre ela.
EXEMPLO:
DROP TABLE Funcionario; 
/* Remove do banco de dados a tabela Funcionario */
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Criando Índice
� Sintaxe: CREATE INDEX 
nome-indice ON nome-da-tabela(nome_coluna)
[Tablespace nome-da-tablespace]
� Exemplos
• Create Index ind_nome on Funcionario(NOME) tablespace
ST_INDICE ;
• Create Index ind_nome on Funcionario(NOME) using hash 
tablespace ST_INDICE;
Prof. Luiz A.Prof. Luiz Vivacqua
Projeto Físico
�Removendo índice
� Sintaxe: Drop index nome-do-índice
� Exemplo:
• DROP INDEX IND_NOME;