Normalização de Banco de Dados

Prévia do material em texto

Normalização
Banco de Dados
Prof. Luís Alexandre
2
Requisitos 
de Dados
Design
Conceitual 
Design
Lógico
Design
Físico
Esquema Conceitual
Esquema Físico
Esquema Lógico
Projeto de banco de dados relacional
3
Resultado do design conceitual = DER
EMPREGADO
DEPARTAMENTO
PROJETO
DEPENDENTE
supervisiona
emprega
trabalha
gerencia
possui
controla
Data início
Número
Nome
Localizacao*
Numero
Nome
Local
Horas
CPF
Nome
Data nascimento
Sexo
Endereco
Salario
Nome
Sexo
data nascimento
Relacionamento
supervisionadosupervisor
(1,N) (1,1)
(1,N)
(0,M)
(0,N)(0,M)
(0,1)
(1,1)
(1,1)
(0,N)
(0,N)
(0,N)
Normalização 4
Normalização
•Normalização de dados: decomposição de
esquemas para evitar anomalias de atualização.
• Objetivo: evitar redundância de dados e
anomalias, isto é conseguido com um bom
projeto.
• Mecanismo formal para analisar esquemas de
relações baseado nas suas chaves e nas
dependências funcionais entre seus atributos.
• Projeto conceitual bem feito resulta naturalmente
em esquemas normalizados.
Normalização 5
Anomalias de Atualização
CPF Nome-Emp Data-Nasc Endereço Num-Dept Nome-Dept Ger-Dept
EMP-DEPT
Relações não normalizadas são sujeitas a anomalias durante as
atualizações:
•Anomalias de inserção
• Inserir empregado requer repetir dados de departamento.
• Anomalias de exclusão
• Para excluir um único empregado do departamento
também exclui-se o departamento.
• Anomalias de modificação
• Mudar o nome do departamento requer modificar várias
tuplas. A operação de atualização seria repetida várias
vezes.
6
Formas Normais
Relação qualquer, não normalizada
Relações em 1FN
Relações em 2FN
Relações em 3FN
Relações em FNBC
Relações em 4FN
Relações em 5FN e outras 
Normalização 7
Primeira Forma Normal (1FN)
• É parte da definição formal de uma relação; foi
definida para não permitir atributos multivalorados,
atributos compostos e suas combinações.
Uma relação está em 1FN se e somente se todos os
seus atributos contêm apenas valores atômicos
(simples, indivisíveis). Ou ainda, não contém
tabelas aninhadas.
Normalização 8
Passagem à 1 FN
° Retirar da relação o (s) atributo (s) repetido 
(s) transformando a relação em novas 
relações:
° A nova relação formada terá uma nova chave 
escolhida entre os atributos repetidos e para 
relecionamento com a antiga relação, a chave 
primária da relação antiga;
° As antigas relações mantém os atributos restantes e 
a chave original (de R)
Normalização 9
atributo multivalorado
Nome Núm Gerente Data-Inicio
DEPARTAMENTO
Pesq
Pesq
Pesq
Adm
Sede
5
5
5
4
1
333445555
333445555
333445555
987654321
888665555
22Mai88
22Mai88
22Mai88
01Jan92
19Jun91
Nome Núm Gerente Data-Inicio
DEPARTAMENTO
Pesq
Adm
Sede
5
4
1
333445555
987654321
888665555
22Mai88
01Jan92
19Jun91
Lins, Bauru, Santos
Campinas
Santos
Localização
Lins
Bauru
Santos
Campinas
Santos
Localização
Relação que não está em 1FN
Relação em 1FN, porém 
com redundância
atributo multivalorado
Obs: Relação não em 2FN
Normalização 10
Num-Dept Localização
LOCAL_DEPT
1
4
5
5
5
Santos
Campinas
Lins
Bauru
Santos
Nome Núm Gerente Data-Inicio
DEPARTAMENTO
Pesq
Adm
Sede
5
4
1
333445555
987654321
888665555
22Mai88
01Jan92
19Jun91
Lins, Bauru, Santos
Campinas
Santos
Localização
Nome Núm Gerente Data-Inicio
DEPARTAMENTO
Pesq
Adm
Sede
5
4
1
333445555
987654321
888665555
22Mai88
01Jan92
19Jun91
Relação que não está em 1FN
Relações em 1FN
sem redundância
atributo multivalorado
Normalização 11
CPF-Emp Num-Proj Horas
TRABALHA_EM
123456789
123456789
666884444
453453453
453453453
333445555
333445555
333445555
333445555
999887777
999887777
987987987
987987987
987654321
987654321
888665555
1
2
3
1
2
2
3
10
20
30
10
10
30
30
20
20
32,5
7,5
40,0
20,0
20,0
10,0
10,0
10,0
10,0
30,0
10,0
35,0
5,0
20,0
15,0
nulo
Relação em 1FN
CPF-Emp Projetos
Num-Proj Horas
TRABALHA_EM
123456789
666884444
453453453
333445555
999887777
987987987
987654321
888665555
1
2
3
1
2
2
3
10
20
30
10
10
30
30
20
20
32,5
7,5
40,0
20,0
20,0
10,0
10,0
10,0
10,0
30,0
10,0
35,0
5,0
20,0
15,0
nulo
atributo composto
Normalização 12
Dependência Funcional
CPF Num-Proj Horas Nome-Emp Nome-Proj Local-Proj
EMP-PROJ
df1
df2
df3
df1 : {CPF, Num-Proj} → {Horas}
df2 : {CPF} → {Nome-Emp}
df3 : {Num-Proj} → {Nome-Proj, Local-Proj}
•DF é um relacionamento entre atributos;
•DF deve ser explicitamente definida por alguém que 
conheça a semântica dos atributos de uma relação.
Normalização 13
Segunda Forma Normal (2FN)
Uma relação está em 2FN se e somente se estiver
em 1FN e todo atributo não-primo (isto é, que não
seja membro de uma chave) for totalmente
dependente de qualquer chave.
Normalização 14
Passagem à 2 FN
° Examinar somente as relações (em 1FN) com chave primária 
composta, as demais já estão em 2FN;
° Retirar da relação original as dependências parciais, isto é, e 
determinar se todos atributos, que não fazem parte da chave, 
dependem de toda a chave ou somente de parte dela;
° Separar os atributos de relação em:
° Relações que contém os atributos que dependem de parte da 
chave; sua a chave primária é a parte da chave da qual os 
atributos retirados dependem;
° Relação onde permanecem os atributos que dependem de toda 
com a chave. 
15
CPF Num-Proj Horas 
CPF Nome-Emp 
Num-Proj Nome-Proj Local-Proj
df1
TRABALHA_EM
EMPREGADO
PROJETO
df2
df3
Relações
em 2FN
CPF Num-Proj Horas Nome-Emp Nome-Proj Local-Proj
EMP-PROJ
df1
df2
df3
Relação em 1FN que não está em 2FN
Normalização 16
Ou ainda ...
Uma relação está em 2FN se e somente se estiver
em 1FN e, não contém dependências parciais.
Uma dependência (funcional) parcial ocorre quando
uma coluna depende apenas de parte de uma chave
primária composta.
Normalização 17
Terceira Forma Normal (3FN)
Uma relação está em 3FN se e somente se estiver
em 2FN e nenhum atributo não-primo (isto é, que
não seja membro de uma chave) for transitivamente
dependente da chave primária.
Em outras palavras, para toda df X → A, uma das
duas condições seguintes devem valer:
• X é uma superchave ou
• A é membro de uma chave candidata
Normalização 18
Passagem à 3 FN
° Examinar todas as relações em 2FN, para 
verificar se existem dependências transitivas;
° Atributos dependentes de outro (s) atributo 
(s) não chaves vão constituir nova (s) relação 
(ões), cuja chave primária é (são) o (s) 
atributo (s) do (s) qual (ais) depende (m);
° Os demais atributos vão constituir uma 
relação com chave igual a relação original.
19
CPF Nome-Emp Data-Nasc Endereço Num-Dept Nome-Dept Ger-Dept
EMP-DEPT
Relação em 2FN que não está em 3FN
df1
df2
CPF Nome-Emp Data-Nasc Endereço Num-Dept 
df1
Num-Dept Nome-Dept Ger-Dept
df2
EMPREGADO
DEPARTAMENTO
Relações
em 3FN
Normalização 20
Ou ainda ...
Uma relação está em 3FN se e somente se estiver
na 1FN e na 2FN e, não contém dependências
transitivas.
Uma dependência funcional transitiva ocorre quando
uma coluna, além de depender da chave primária da
tabela, depende de outra coluna ou conjunto de
colunas da tabela.
Normalização 21
Atributos calculados
• Exemplos:
– Aluno (Matricula, P1, P2, P3, Media);
– Nota_fiscal (NFNF, data, codprod, 
preco_unitario, qtde_vendida, subtotal)
Normalização 22
Resumodo processo para as 3 
formas vistas
° eliminar atributos multivalorados ou 
compostos; 
° eliminar atributos que dependem 
apenas de parte da chave primária 
composta;
° eliminar atributos que dependem de 
outros atributos que não pertencem a 
chave primária da relação
Normalização 23
Exemplo com anomalias
• PEDIDO (numeroPedido, 
dataEmissãoPedido, códigoCliente, 
endereçoCliente, cidadeCliente, 
CEP_Cliente, produtosPedidos 
(numeroProduto, descriçãoProduto, 
qtdePedidaProduto, 
valorUnitarioProdutoPedido, 
totalProdutoPedido), totalPedido)
Normalização 24
Relação em 1FN
• PEDIDO (numeroPedido, dataEmissãoPedido, 
códigoCliente, nomeCliente, endereçoCliente, 
cidadeCliente, estadoCliente, cepCliente, 
totalPedido)
• PRODUTO_PEDIDO (numeroProduto, 
descriçãoProduto, qtdeProdutoPedido, 
valorUnitarioProdutoPedido, totalProdutoPedido, 
numeroPedido)
Normalização 25
Relação em 2FN
• PEDIDO (numeroPedido, dataEmissãoPedido, 
códigoCliente, nomeCliente, endereçoCliente, 
cidadeCliente, estadoCliente, cepCliente, 
totalPedido)
• PRODUTO (numeroProduto, descriçãoProduto)
• PRODUTO_PEDIDO (numeroPedido , 
numeroProduto, qtdeProdutoPedido, 
valorUnitarioProdutoPedido),
Normalização 26
Relação em 3FN
• PEDIDO (numeroPedido, dataEmissãoPedido, 
códigoCliente)
• PRODUTO (numeroProduto, descriçãoProduto)
• PRODUTO_PEDIDO(numeroPedido , 
numeroProduto, qtdeProdutoPedido, 
valorUnitarioProdutoPedido)
• CLIENTE (códigoCliente, nomeCliente, 
endereçoCliente, cidadeCliente, estadoCliente, 
cepCliente)
Normalização 27
Forma Normal de Boyce-Codd (FNBC)
• É uma forma mais restritiva de 3FN, isto é toda relação em 
FNBC está também em 3FN; entretanto, uma relação em 3FN 
não está necessariamente em FNBC.
Uma relação está em FNBC se para toda df X → Z, 
X é uma super-chave.
28
Aluno Disciplina Professor 
df1
df2
Aluno Disciplina Professor 
Carlos Química Ana
Carlos Física Antonio
Marta Química Ana
Marta Português Maria
João Português Manoel
Relação em 3FN que não está em FNBC
Anomalia de exclusão: Se Carlos sair da aula de Física, não teremos 
nenhum registro de que Antonio leciona Física. 
AULA
AULA
df2 : {Professor} → {Disciplina} 
{Professor} não é uma superchave.
Normalização 29
Normalização como Ferramenta para 
Validação da Qualidade de um Esquema
• As formas normais até FNBC são baseadas em
dependências funcionais, exceto a 1FN, que faz parte da
definição do modelo relacional.
• O design conceitual baseado no modelo ER tende
naturalmente a produzir esquemas normalizados, a
menos da 1FN.
• A separação de conceitos é o resultado natural do
design conceitual bem feito.
• Na prática, esquemas que violam a normalização são
exemplos de esquemas mal projetados.
Normalização 30
Carlos Ana
Carlos Antonio
Marta Ana
Marta Maria
João Manoel
Relações em FNBC
Note, entretanto, que a dependência funcional
df1 : {Aluno, Disciplina} → {Professor}
foi perdida na decomposição. 
Considere, por exemplo, a inserção de (Marta, Manoel) em R1.
Aluno Professor
Ana Química
Antonio Física
Maria Português
Manoel Português
Professor Disciplina 
R1 R2
Normalização 31
• Alguns esquemas não podem ser normalizados em FNBC e ao
mesmo tempo preservar todas as dfs.
• Solução: redundância parcial.
Decomposição / Junção sem Perda
Aluno Disciplina Professor 
Carlos Química Ana
Carlos Física Antonio
Marta Química Ana
Marta Português Maria
João Português Manoel
AULA
Ana Química
Antonio Física
Maria Português
Manoel Português
Professor Disciplina 
R2
Relações em FNBC, com redundância parcial
e todas as dependências funcionais preservadas
Normalização 32
• dentro de uma entidade:
EMPREGADO
Entidade não normalizada
esquema mal projetado, 
conceitos diversos numa 
mesma entidade
EMPREGADO
DEPARTAMENTO
Entidades em 3FN (FNBC) 
CPF
Nome
Data nascimento
Endereço
Numero departamento
Nome departamento
Telefone departamento
CPF
Nome
Data nascimento
Endereço
Numero
Nome
Telefone
Equivalência Funcional entre o Modelo ER 
e Relações Normalizadas
Normalização 33
• Relacionamento 1:1 ou 1:N binário (ou unário)
EMPREGADO
DEPARTAMENTO
TRAB-EM
(1,1)
(1,N)
lado “pai”
lado “filho”
ambas as
entidades
em FNBC
CPF
Nome
Data nascimento
Endereço
Numero
Nome
Telefone
Equivalência Funcional entre o Modelo ER 
e Relações Normalizadas
Normalização 34
Equivalência Funcional entre o Modelo ER 
e Relações Normalizadas
• Relacionamento M:N binário (ou unário)
EMPREGADO
PROJETO
TRAB-EM
M
N
Num-Horas
relacionamento
em FNBC
CPF
Nome
Data nascimento
Endereço
Numero
Nome
Local
Normalização 35
Quarta Forma Normal
• Dependências multivaloradas (DMVs)
Quarta forma normal (4FN)
Uma relação R está em 4FN se e somente se estiver
em FNBC e, caso exista alguma DMV X --->> Y,
a DMV é trivial `(i.e., Y⊂ X ou X ∪Y = R)
ou
X é uma superchave de R.
36
Nome-Emp Nome-Proj Nome-Depend
Prado X João
Prado Y Ana
Prado X Ana
Prado Y João
Borba W José
Borba X José
Borba Y José
Borba Z José
Borba W Joana
Borba X Joana
Borba Y Joana
Borba Z Joana
Borba W Beto
Borba X Beto
Borba Y Beto
Borba Z Beto
EMPREGADO
Nome-Emp Nome-Proj Nome-Emp Nome-Depend
Prado X
Prado Y
Borba W
Borba X
Borba Y
Borba Z
Prado João
Prado Ana
Borba José
Borba Joana
Borba Beto
Relação EMPREGADO não está
em 4FN.
2 DMVs não triviais:
Nome-Emp --->> Nome-Proj
Nome-Emp --->> Nome-Depend
EMPREGADO-PROJETO EMPREGADO-DEPENDENTE
Normalização 37
Outras Formas Normais
• Tipos adicionais de dependências. 
• dependências de junção e de inclusão, que 
levam a formas normais mais restritas (Quinta 
Forma Normal, Forma Normal de Domínio-
Chave). 
• A utilidade prática destas formas normais é 
limitada, porque num banco de dados real com 
muitos atributos, é muito difícil (e praticamente 
irrelevante) descobrir tais dependências e 
restrições.