moddad_a6_normalizacao

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

A6 – NORMALIZAÇÃO
MODELAGEM DE DADOS
Cátia Garcia Morais
catia.gm@ufpr.br
http://people.ufpr.br/~catia.gm
[UFPR] Tecnologia em Análise e Desenvolvimento de Sistemas (2011/1)
Roteiro de Aula
•Projeto do Banco de Dados
•Estratégias de Modelagem de Dados
• Processo de Engenharia Reversa
• Tabela Não Normalizada
• Aplicar formais normais (Normalização)
• 1ª Forma Normal
• 2ª Forma Normal
• 3ª Forma Normal
• Mapear de Relações para DER
[MODDAD] A6 – Normalização 2
[MODDAD] A6 – Normalização 3
Projeto do Banco de Dados
• ORIGEM: Modelagem de Dados
• descrição formal da estrutura de um banco de dados
• Projeto do Banco de Dados
• Três níveis de abstração
• Realizar o mapeamento entre os 3 modelos
1) Modelo Conceitual (DER)
Entidade-atributos, relacionamentos-cardinalidade
2) Modelo Lógico (Esquema do BD)
Relações e Restrições de Integridade (PK, FK, dom(A))
3) Modelo Físico (Script do BD)
Tabelas e Restrições
• Fonte para construção do DER: minimundo
• Descrição textual formal dos dados
• Estratégia que parte do conhecimento que as pessoas
possuem sobre o novo sistema sendo modelado
• Porém, os dados podem estar em:
• Sistemas legados em bancos não relacionais
• Banco relacional, mas não documentado
• Ideia: aplicar engenharia reversa
• Estratégia de modelagem em que se parte de descrições
de dados existentes (arquivos e/ou documentos) até
atingir o modelo conceitual (DER)
[MODDAD] A6 – Normalização 4
Estratégias de Modelagem
• Passo 1: parte da representação da descrição
de cada arquivo/documento existente na
forma de um esquema de uma tabela
relacional não normalizada (ÑN)
[MODDAD] A6 – Normalização 5
Processo de Engenharia Reversa
modelo
relacional
esquema de
arquivo/documento
normalização
• Passo 2: aplicar o processo de
normalização a este esquema de
tabela não normalizada
• Eliminar redundâncias e também
desperdício de armazenamento
• Obtenção do modelo ER
[MODDAD] A6 – Normalização 6
Documento Exemplo para Normalização
RELATÓRIO DE ALOCAÇÃO A PROJETO
CÓDIGO DO PROJETO:
LSC001 TIPO: Novo Desenv.
DESCRIÇÃO: Sistema de Estoque
CÓDIGO DO
EMPREGADO
NOME CATEGORIA
FUNCIONAL
SALÁRIO DATA DE INÍCIO
NO PROJETO
TEMPO
ALOCADO AO
PROJETO
2146 João A1 4 1/11/91 24
3145 Sílvio A2 4 2/10/91 24
6126 José B1 9 3/10/92 18
1214 Carlos A2 4 4/10/92 18
8191 Mário A1 4 1/11/92 12
CÓDIGO DO PROJETO:
PAG02 TIPO: Manutenção
DESCRIÇÃO: Sistema de RH
CÓDIGO DO
EMPREGADO
NOME CATEGORIA
FUNCIONAL
SALÁRIO DATA DE INÍCIO
NO PROJETO
TEMPO
ALOCADO AO
PROJETO
8191 Mário A1 4 1/05/93 12
4112 João A2 4 4/01/91 24
6126 José B1 9 1/11/92 12
O processo de
normalização pode ser
executado sobre
qualquer tipo de
representação de
dados: arquivo de
computador, lay-out de
relatório/formulário ou
mesmo de uma tela.
Sistema de
Gerência de
Projetos
[MODDAD] A6 – Normalização 7
Documento Exemplo para Normalização
RELATÓRIO DE ALOCAÇÃO A PROJETO
CÓDIGO DO PROJETO:
LSC001 TIPO: Novo Desenv.
DESCRIÇÃO: Sistema de Estoque
CÓDIGO DO
EMPREGADO
NOME CATEGORIA
FUNCIONAL
SALÁRIO DATA DE INÍCIO
NO PROJETO
TEMPO
ALOCADO AO
PROJETO
2146 João A1 4 1/11/91 24
3145 Sílvio A2 4 2/10/91 24
6126 José B1 9 3/10/92 18
1214 Carlos A2 4 4/10/92 18
8191 Mário A1 4 1/11/92 12
CÓDIGO DO PROJETO:
PAG02 TIPO: Manutenção
DESCRIÇÃO: Sistema de RH
CÓDIGO DO
EMPREGADO
NOME CATEGORIA
FUNCIONAL
SALÁRIO DATA DE INÍCIO
NO PROJETO
TEMPO
ALOCADO AO
PROJETO
8191 Mário A1 4 1/05/93 12
4112 João A2 4 4/01/91 24
6126 José B1 9 1/11/92 12
Descrição:
Para cada projeto, são
informados o código, a
descrição e o tipo de
projeto, bem como os
empregados que atuam
no projeto. Já para cada
empregado do projeto,
são informados o seu
número, nome,
categoria funcional e
salário, bem como a
data em que foi alocado
ao projeto e o tempo
pelo qual foi alocado ao
projeto.
• Transformar a descrição do documento ou
arquivo a ser normalizado em um esquema de
tabela relacional
[MODDAD] A6 – Normalização 8
[P1] Representação na forma de tabela ÑN
CódProj Tipo Descr Emp
CodEmp Nome Cat Sal DataIni TempAl
LSC001 Novo
Desenv.
Sistema
de
Estoque
2146 João A1 4 1/11/91 24
3145 Sílvio A2 4 2/10/91 24
6126 José B1 9 3/10/92 18
1214 Carlos A2 4 4/10/92 18
8191 Mário A1 4 1/11/92 12
PAG02 Manuten
ção
Sistema
de RH
8191 Mário A1 4 1/05/93 12
4112 João A2 4 4/01/91 24
6126 José B1 9 1/11/92 12
tabela não normalizada (ÑN)
• Esta tabela é dita não normalizada (ÑN) pois possui tabela
aninhada, grupo repetido, coluna multivalorada ou ainda
coluna não atômica
[MODDAD] A6 – Normalização 9
Representação na forma de tabela ÑN
CódProj Tipo Descr Emp
CodEmp Nome Cat Sal DataIni TempAl
LSC001 Novo
Desenv.
Sistema
de
Estoque
2146 João A1 4 1/11/91 24
3145 Sílvio A2 4 2/10/91 24
6126 José B1 9 3/10/92 18
1214 Carlos A2 4 4/10/92 18
8191 Mário A1 4 1/11/92 12
PAG02 Manuten
ção
Sistema
de RH
8191 Mário A1 4 1/05/93 12
4112 João A2 4 4/01/91 24
6126 José B1 9 1/11/92 12
tabela
aninhada
• A tabela ÑN pode ser descrita pelo seguinte esquema
de tabela relacional:
• Recordar: projeto de BD passa por três níveis de abstração
1) Modelo Conceitual (DER)
2) Modelo Lógico (Esquema do BD – Relações e Restrições de
Integridade)
3) Modelo Físico (Script do BD – Tabelas e Restrições)
[MODDAD] A6 – Normalização 10
Representação na forma de tabela ÑN
PROJETO (@codProj, descrição, tipo,
(@codEmp, nome, cat, sal, dtIni, tAloc)
)
[MODDAD] A6 – Normalização 11
Outro exemplo: arquivo Alunos
type reg_aluno= record
cod_al: integer;
nome_al: char_60;
ingressos_cursos_al: array [1..10] of record
cod_curso: integer;
semestre_ingresso: integer end;
disciplinas_cursadas_al: array [0..200] of record
cod_disc: integer;
semestres_cursados: array [1..20] of record
semestre_disc: integer;
nota_disc: integer end
end
end;
arq_alunos= file of reg_aluno;
Descrição:
O registro do aluno contém seu código, nome, uma lista de ingressos em cursos e outra
de disciplinas cursadas. Para cada ingresso em curso é registrado o código de curso e o
semestre de ingresso. Para cada disciplina cursada é informado seu código, e os
semestres em que cursou. Para cada semestre é informado o semestre e anota obtida.
• A tabela ÑN pode ser descrita pelo seguinte esquema
de tabela relacional:
[MODDAD] A6 – Normalização 12
Representação na forma de tabela ÑN
ARQ_ALUNOS (@codAlu, nomeAlu,
(@codCur, semIng),
(@codDisc,
(@semDiscCursada, notaDisc)
)
)
[MODDAD] A6 – Normalização 13
Outro Exemplo: Formulário de Pedido
Matrícula Nome Nome CPF End CódigoQtde Nome V.Unit V. Total
12 12 João Maria 123 E1 01/11/2010 123 1 Pr123 1,23 1,23
234 1 Pr234 2,34 2,34
456 3 Pr456 1,56 7,02
34 15 Ana Daniel 345 E2 12/11/2010 567 1 Pr567 5,67 5,67
234 1 Pr234 2,34 2,34
56 19 Pedro Isa 567 E3 21/01/2011 765 1 Pr765 7,65 7,65
ClientePedido Data
Pedido
Funcionário Produtos
[MODDAD] A6 – Normalização 14
Representação na forma de tabela ÑN
Tabela ÑN:
PEDIDO (@numPed, matFunc, nomeFunc, nomeCli,
CPFCli, endCli, dtPed,
(@codProd, qtd, nomeProd, vlrUnit, vlrTot)
)
• Tabelas em BD relacional podem sofrer com
problemas de desempenho, integridade e
facilidade de manutenção
• BD em uma única e grande tabela
• Problemas:
• Redundância, o que pode gerar inconsistência nos valores
dos dados e também desperdício de armazenamento
• Muito tempo gasto em buscas
• Ideia de Solução? Desmembrar em tabelas menores
• Aplicar normalização: analisar interdependências entre
atributos individuais associados a essas tabelas
[MODDAD] A6 – Normalização 15
Normalização
[MODDAD] A6 – Normalização 16
Normalização - Passos
esquema de
arquivo
ou
documento
Representação
como tabela
ÑN esquema não
normalizado
Passagem
a 1FN
esquema
na 1FN
Passagem
a 2FN
esquema
na 2FN
Passagem
a 3FN
Passagem
a 4FN
esquema
na 3FN
esquema relacional
normalizado
1 – tabela
não
normalizada
2 – aplicar
formas
normais...
• Um projeto bom e bem elaborado de um
modelo conceitual resultará em bancos de
dados que já estão normalizados ou que
podem ser facilmente normalizados
(Teorey et all, 2007)
• Projetista experiente poderá produzir diretamente
um modelo normalizado
[MODDAD] A6 – Normalização 17
Normalização - Observação
• A partir do esquema relacional correspondente
(ÑN), inicia-se a normalização
• Baseia-se no conceito de forma normal
• Regra que deve ser obedecida por uma tabela para
que seja considerada “bem projetada”
• Há diversas formas normais, cada vez mais rígidas,
para verificar tabelas relacionais.
• primeira forma normal (1FN),
• segunda forma normal (2FN),
• terceira forma normal (3FN),
• ...
[MODDAD] A6 – Normalização 18
[P2] Normalização
• Para chegar a 1FN
• Construa uma tabela para cada tabela aninhada
1. uma tabela referente a própria tabela que está
sendo normalizada (tabela externa) e
2. uma tabela para cada tabela aninhada
[MODDAD] A6 – Normalização 19
Passagem à primeira forma normal (1FN)
primeira forma normal (1FN)
=
diz-se que uma tabela está na primeira
forma normal, quando ela não contém
tabelas aninhadas
1) criar tabela referente a tabela externa
[MODDAD] A6 – Normalização 20
Passagem à primeira forma normal (1FN)
ÑN:
(@CodProj, Tipo, Descr,
(@CodEmp, Nome, Cat, Sal, DataIni, TempAl))
1FN:
(CodProj, Tipo, Descr)
2) criar uma tabela composta pelas
seguintes colunas:
a) a chave primária de cada uma das tabelas
na qual a tabela em questão está aninhada;
b) as colunas da própria tabela aninhada.
[MODDAD] A6 – Normalização 21
Passagem à primeira forma normal (1FN)
ÑN:
(@CodProj, Tipo, Descr,
(@CodEmp, Nome, Cat, Sal, DataIni, TempAl))
1FN:
(CodProj, Tipo, Descr)
(CodProj, CodEmp, Nome, Cat, Sal, DataIni,TempAl)
c) Definir, na 1FN, as chaves primárias das
tabelas que correspondem a tabelas
aninhadas.
[MODDAD] A6 – Normalização 22
Passagem à primeira forma normal (1FN)
ÑN:
(@CodProj, Tipo, Descr,
(@CodEmp, Nome, Cat, Sal, DataIni, TempAl))
Tabela de nível mais externo:
basta transcrever a chave primária
Tabela de nível mais interno:
Se há repetições distintas, basta acrescentar
a chave primária da tabela aninhada1FN:
(@CodProj, Tipo, Descr)
(@CodProj, @CodEmp, Nome, Cat, Sal, DataIni, TempAl)
• Representação no DER
[MODDAD] A6 – Normalização 23
Tabelas na primeira forma normal (1FN)
1FN:
Proj (@CodProj, Tipo, Descr)
ProjEmp (@CodProj, @CodEmp, Nome, Cat, Sal, DataIni, TempAl)
ÑN:
Proj (@CodProj, Tipo, Descr,
(@CodEmp, Nome, Cat, Sal, DataIni, TempAl)
)
PROJETO PROJEMPpossui1 N
[MODDAD] A6 – Normalização 24
Tabelas (com dados) na 1FN
CódProj Tipo Descr
LSC001 Novo Desenv. Sistema de Estoque
PAG02 Manutenção Sistema de RH
CódProj CodEmp Nome Cat Sal DataIni TempAl
LSC001 2146 João A1 4 1/11/91 24
LSC001 3145 Sílvio A2 4 2/10/91 24
LSC001 6126 José B1 9 3/10/92 18
LSC001 1214 Carlos A2 4 4/10/92 18
LSC001 8191 Mário A1 4 1/11/92 12
PAG02 8191 Mário A1 4 1/05/93 12
PAG02 4112 João A2 4 4/01/91 24
PAG02 6126 José B1 9 1/11/92 12
Proj:
ProjEmp:
• Outro exemplo: arquivo Alunos
[MODDAD] A6 – Normalização 25
Tabelas na primeira forma normal (1FN)
ÑN:
Arq-Alunos (@Cod-Al, Nome-Al,
(@Cod-Curso, Sem-Ingresso)
(@Cod-Disc,
(@Sem-Disc-Cursada, Nota-Disc)))
1FN:
Aluno (@Cod-Al, Nome-Al)
AlunoCurso (@Cod-Al, @Cod-Curso, Sem Ingresso)
AlunoDisc (@Cod-Al, @Cod-Disc)
AlunoDiscSem (@Cod-Al, @Cod-Disc, @Sem-Disc-Cursada, Nota-Disc)
[MODDAD] A6 – Normalização 26
1FN:
PEDIDO (@numPed, matFunc, nomeFunc, nomeCli, CPFCli, endCli, dtPed)
ITEM_PEDIDO(@numPed, @codProd, qtde, nomeProd, vlrUnitProd, vlrTotal)
Matrícula Nome Nome CPF End CódigoQtde Nome V.Unit V. Total
12 12 João Maria 123 E1 01/11/2010 123 1 Pr123 1,23 1,23
234 1 Pr234 2,34 2,34
456 3 Pr456 1,56 7,02
34 15 Ana Daniel 345 E2 12/11/2010 567 1 Pr567 5,67 5,67
234 1 Pr234 2,34 2,34
56 19 Pedro Isa 567 E3 21/01/2011 765 1 Pr765 7,65 7,65
ClientePedido Data
Pedido
Funcionário Produtos
ÑN:
PEDIDO (@numPed, matFunc, nomeFunc, nomeCli, CPFCli, endCli, dtPed,
(@codProd, qtde, nomeProd, vlrUnitProd, vlrTot) )
PEDIDO ITEM-PEDIDOpossui1 N
• Para entender 2FN e 3FN é necessário compreender o
conceito de dependência funcional
• Um atributo ou conjunto de atributos A é dependente
funcional de outro atributo B da mesma tabela, se a cada
valor de B existir, nas linhas da tabela em que aparece, um
único valor de A. Assim, A depende funcionalmente de B
[MODDAD] A6 – Normalização 27
Outras Formas Normais
… Código … Salário …
... E1 ... 10 ...
... E3 ... 10 ...
... E1 ... 10 ...
... E2 ... 5 ...
... E3 ... 10 ...
... E2 ... 5 ...
... E1 ... 10 ...
Salário depende
funcionalmente do
código do
empregado.
Código Salário
AB
• Na ocorrência de chave primária concatenada
(mais de um atributo), a Dependência
Funcional pode ser:
• Total: a cada valor completo da chave está
associado um valor de atributo
• Parcial: quando o valor do atributo depende de
parte da chave
• Observar cada entidade/tabela com chave
primária concatenada e analisar se existe
algum atributo (ou conjunto de atributos) com
dependência parcial
[MODDAD] A6 – Normalização 28
Outras Formas Normais
• Exemplo:
• Dados referentes a empregados (Nome, Cat e Sal) são
redundantes para os empregados que trabalham em
mais de um projeto
[MODDAD] A6 – Normalização 29
Outras Formas Normais
CódProj CodEmp Nome Cat Sal DataIni TempAl
LSC001 2146 João A1 4 1/11/91 24
LSC001 3145 Sílvio A2 4 2/10/91 24
LSC001 6126 José B1 9 3/10/92 18
LSC001 1214 Carlos A2 4 4/10/92 18
LSC001 8191 Mário A1 4 1/11/92 12
PAG02 8191 Mário A1 4 1/05/93 12
PAG02 4112 João A2 4 4/01/91 24
PAG02 6126 José B1 9 1/11/92 12
ProjEmp:
ProjEmp (@CodProj, @CodEmp, Nome, Cat, Sal,
DataIni, TempAl)
• Assim:
[MODDAD] A6 – Normalização 30
Segunda Forma Normal (2FN)
segunda forma normal (2FN)
=
uma tabela encontra-se na segunda forma
normal, quando, além de estar na 1FN,
não contém dependências parciais
dependência parcial
=
uma dependência (funcional) parcial ocorre quando uma coluna
depende apenas de parte de uma chave primária composta
dependência parcial
=
uma dependência (funcional) parcial ocorre quando uma coluna
depende apenas de parte de uma chave primária composta
1FN:
ProjEmp (@CodProj,@CodEmp, Nome, Cat, Sal, DataIni, TempAl)
• 1) Copiar para a 2FN cada tabela que tenha
chave primária simples
• OBSERVAÇÃO: Toda tabela na 1FN que possui apenas
uma coluna como chave primária já está na 2FN
[MODDAD] A6 – Normalização 31
Passagem da 1FN à 2FN
1FN:
Proj (@CodProj, Tipo, Descr)
ProjEmp (@CodProj, @CodEmp, Nome, Cat, Sal, DataIni, TempAl)
2FN:
Proj (@CodProj, Tipo, Descr)
• 2) Para cada tabela com chave primária composta e
com pelo menos uma coluna não chave
• Para cada coluna não chave, fazer a pergunta: “a coluna
depende de toda a chave ou de apenas parte dela?”
• Se sim, as colunas permanecem na tabela original
• Se não, criar outra tabela na 2FN com parte da chave e as colunas da
qual dependem
[MODDAD] A6 – Normalização 32
Passagem da 1FN à 2FN
1FN:
Proj (@CodProj, Tipo, Descr)
ProjEmp (@CodProj, @CodEmp, Nome, Cat, Sal, DataIni, TempAl)
2FN:
ProjEmp (@CodProj, @CodEmp, DataIni, TempAl)
Emp (@CodEmp, Nome, Cat, Sal)
• Representação no DER
[MODDAD] A6 – Normalização
33
Tabelas na segunda forma normal (2FN)
2FN:
Proj (@CodProj, Tipo, Descr)
ProjEmp (@CodProj,@CodEmp, DataIni, TempAl)
Emp (@CodEmp, Nome, Cat, Sal)
1FN:
Proj (@CodProj, Tipo, Descr)
ProjEmp (@CodProj, @CodEmp, Nome, Cat, Sal, DataIni, TempAl)
PROJETO PROJEMPpossui1 N alocado 1N EMPREGADO
[MODDAD] A6 – Normalização 34
Tabelas (com dados) na 2FN
CódProj Tipo Descr
LSC001 Novo Desenv. Sistema de Estoque
PAG02 Manutenção Sistema de RH
Proj:
CodEmp Nome Cat Sal
2146 João A1 4
3145 Sílvio A2 4
1214 Carlos A2 4
8191 Mário A1 4
4112 João A2 4
6126 José B1 9
Emp:
CódProj CodEmp DataIni TempAl
LSC001 2146 1/11/91 24
LSC001 3145 2/10/91 24
LSC001 6126 3/10/92 18
LSC001 1214 4/10/92 18
LSC001 8191 1/11/92 12
PAG02 8191 1/05/93 12
PAG02 4112 4/01/91 24
PAG02 6126 1/11/92 12
ProjEmp:
[MODDAD] A6 – Normalização 35
Outro Exemplo na 2FN
1FN
PEDIDO (@numPed, matFunc, nomeFunc, nomeCli, CPFCli,
endCli, dtPed)
ITEM-PEDIDO(@numPed, @codProd, descProd, quant,
vlrUnitProd, vlrTotProd)
2FN
PEDIDO (@numPed, matFunc, nomeFunc, nomeCli, CPFCli,
endCli, dtPed)
ITEM-PEDIDO(@numPed, @codProd, quant, vlrTotProd)
PRODUTO(@codProd, descProd, vlrUnitProd)
PEDIDO ITEM-PEDIDOpossui1 N PRODUTOéUm 1N
• Exemplo: Se salário (coluna Sal) é determinado pela
categoria funcional (coluna Cat). Salário que é pago a uma
categoria funcional é armazenado tantas vezes quantos empregados
possui a categoria funcional
[MODDAD] A6 – Normalização 36
Terceira Forma Normal (3FN)
terceira forma normal (3FN)
=
uma tabela encontra-se na terceira forma
normal, quando, além de estar na 2FN, não
contém dependências transitivas
dependência transitiva
=
uma dependência (funcional) transitiva ocorre quando uma
coluna, além de depender da chave primária, depende de
outra(s) coluna(s) da tabela
dependência transitiva
=
uma dependência (funcional) transitiva ocorre quando uma
coluna, além de depender da chave primária, depende de
outra(s) coluna(s) da tabela
2FN:
Emp (@CodEmp, Nome, Cat, Sal)
• Dividir em tabelas de forma a eliminar as
dependências transitivas
• 1) Colunas que dependem da chave primária
permanecem na tabela original
• 2) Colunas que dependem de coluna não chave vão
para outra tabela
[MODDAD] A6 – Normalização 37
Passagem da 2FN à 3FN
2FN:
Emp (@CodEmp, Nome, Cat, Sal)
3FN:
Emp (@CodEmp, Nome, Cat)
Cat (@Cat, Sal)
[MODDAD] A6 – Normalização 38
Tabelas na terceira forma normal (3FN)
3FN:
Proj (@CodProj, Tipo, Descr)
ProjEmp (@CodProj, @CodEmp, DataIni, TempAl)
Emp (@CodEmp, Nome, Cat)
Cat (@Cat, Sal)
2FN:
Proj (@CodProj, Tipo, Descr)
ProjEmp (@CodProj,@CodEmp, DataIni, TempAl)
Emp (@CodEmp, Nome, Cat, Sal)
[MODDAD] A6 – Normalização 39
Outro Exemplo na 3FN
2FN
PEDIDO (@numPed, matFunc, nomeFunc, nomeCli, CPFCli,
endCli, dtPed)
ITEM-PEDIDO(@numPed, @codProd, quantProd, vlrTotProd)
PRODUTO(@codProd, descProd, vlrUnitProd)
3FN
PEDIDO (@numPed, dtPed, CPFcli, matFunc)
CLIENTE(@CPFcli, nomeCliente, endCli)
FUNCIONÁRIO(@matFunc, nomeFunc)
ITEM-PEDIDO(@numPed, @codProd, quantProd, vlrTotProd)
PRODUTO(@codProd, descProd, vlrUnitProd)
[MODDAD] A6 – Normalização 40
Representação no DER
• 6ª Lista de Exercícios
[MODDAD] A6 – Normalização 41
Exercícios