Processo de Normalização
• Entrada
– fonte de dados estruturados da organização
• relatório, fichário, documento estruturado, ...
• Saída
– esquema relacional para a fonte de dados
• Objetivos
– obtenção de um esquema relacional livre de
redundâncias
– método de projeto ou mesmo de validação do
projeto de um BD relacional obtido via ER
• Baseado no conceito de
formas normais
– processo sistemático de geração de tabelas
• Uma tabela T está em uma forma normal
se atende uma certa
regra
– se T está na Forma Normal (FN) i, então T
está automaticamente nas FNs i - 1, i - 2, ...
• Três FNs são mais utilizadas na prática
• A teoria nem sempre é ideal do ponto de
vista prático
– proliferação de tabelas!
R E L A T Ó R IO D E A LO C A Ç Ã O A P R O J E T O
C Ó D IG O D O P R O J E TO : L S C 0 01
T IP O : N o vo D es e n v.
D E S C R IÇ Ã O : S is te m a de E s to qu e
C Ó D IG O D O
E M P R E G A D O
N O M E
C A T E G O R IA
F U N C IO N A L
S A L Á R IO
D A T A D E
IN ÍC IO N O
P R O J E T O
T E M P O
A L O C A D O A O
P R O JE T O
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
C a rlos
A2
4
4 /10/92
18
8191
M ário
A1
4
1 /11/92
12
C Ó D IG O D O P R O J E TO : P A G 0 2
T IP O : M an u ten ç ã o
D E S C R IÇ Ã O : S is te m a de R H
C Ó D IG O D O
E M P R E G A D O
N O M E
C A T E G O R IA
F U N C IO N A L
S A L Á R IO
D A T A D E
IN ÍC IO N O
P R O J E T O
T E M P O
A L O C A D O
A O P R O J E T O
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
Exemplo de Fonte de Dados: Relatório
Passo1 – Conversão para Tabela ÑN
• Objetivo
– obtenção de uma representação padrão para
as fontes de dados
• facilita o processo de normalização
• Forma de representação:
Tabela ÑN
– pode ter uma ou mais
tabelas aninhadas
– tabela aninhada
• possui
atributos multivalorados
– atributo que ao invés de conter valores atômicos,
contém
múltiplos valores
ou contém uma
tabela
que
pode, por sua vez, ser aninhada
CódProj
Tipo
Descr
Emp
CodEmp
Nome
Cat
Sal
DataIni
TempAl
LSC001
Novo Desenv.
Sistema de
2146
João
A1
4
1/11/91
24
Estoque
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
8191
Mário
A1
4
1/05/93
12
RH
4112
João
A2
4
4/01/91
24
6126
José
B1
9
1/11/92
12
Tabela aninhada em uma linha de projeto
Exemplo de Tabela ÑN
Representação na Forma de
Tabela ÑN
indicam-se as chaves primárias (CPs) de cada tabela
Projetos (
codProj
, tipo, descr,
1
a
Forma Normal (1FN)
•
“Uma tabela está na 1FN sse ela não
possui tabelas aninhadas”
•
Procedimento usual
–
gerar uma tabela para cada aninhamento
1FN - Aplicação
1FN: Projetos (codProj, tipo, descr)
Alocações (
codProj
, codEmp, nome, categ, sal,
dataIni, tempoAloc)
• CP da tabela externa migra para a tabela aninhada
• Qual a CP da tabela aninhada?
ÑN: Projetos (codProj, tipo, descr,
1FN - Aplicação
1FN: Projetos (codProj, tipo, descr)
Alocações (codProj,
codEmp
, nome, categ, sal,
dataIni, tempoAloc)
• CP da tabela aninhada
–
codEmp
identifica unicamente uma tupla em Alocações?
(analisar os dados da tabela aninhada na fonte de dados como um
todo)
ÑN: Projetos (codProj, tipo, descr,
(codEmp, nome, categ, sal, dataIni, tempoAloc))
1FN - Aplicação
1FN: Projetos (codProj, tipo, descr)
Alocações (
codProj, codEmp
, nome, categ, sal,
dataIni, tempoAloc)
• CP da tabela aninhada
–
codEmp
identifica unicamente uma tupla em Alocações?
resposta: NÃO
–
logo,
codProj
deve fazer parte da CP da tabela aninhada
ÑN: Projetos (codProj, tipo, descr,
1 FN – Outro Exemplo
• CP da tabela aninhada
(Empregados)
–
CPF
identifica unicamente uma tupla em Empregados?
resposta: SIM
–
logo,
CPF
é suficiente como CP da tabela aninhada
ÑN: Departamentos (codD, nome,
(CPF, nome, salário))
1FN: Departamentos (codD, nome)
(codD,
CPF
, nome, salário)
Dependência Funcional (DF)
• Conceito necessário para o entendimento da
segunda e terceira formas normais
• Definição
– um atributo A
2
depende funcionalmente
de um
atributo A
1
(ou um atributo A
1
determina
um
atributo A
2
) quando,
em todas linhas da tabela, para cada valor de
A
1
que aparece na tabela, aparece o mesmo
valor de A
2
Dependência Funcional - Exemplo
Código
Salário
500
E2
. . .
. . .
450
E3
500
E1
500
E2
450
E3
500
E1
Salário
. . .
Código
. . .
Dependência Funcional - Exemplos
A
D
A
B
(A,B)
C
5
s
2
x
5
r
1
x
10
q
1
y
10
r
3
y
5
p
1
z
5
s
2
x
10
r
3
y
5
p
1
z
5
s
2
x
10
q
1
y
5
r
1
x
D
C
B
A
DF Total e DF Parcial
• DF Total
– se um atributo A
x
depende funcionalmente de
todos
os atributos que compõem a CP de uma
tabela T, diz-se que A
x
possui
DF total
da CP
de T
• DF Parcial
– se um atributo A
x
depende funcionalmente
apenas de
alguns
atributos (não todos!) que
compõem a CP de uma tabela T, diz-se que A
x
possui
DF parcial
da CP de T
•
“Uma tabela está na 2FN sse ela estiver
na 1FN e não possuir DFs parciais”
–
tabelas com DFs parciais devem ser
desmembradas em tabelas que possuam
DFs totais
•
Tabelas cuja CP possui apenas um
atributo estão automaticamente na 2FN
1FN: Projetos (CodProj, Tipo, Descr)
Alocações (CodProj, CodEmp, Nome, Cat, Sal,
DataIni, TempoAloc)
2FN - Aplicação
2FN: Projetos (CodProj, Tipo, Descr)
Alocações (CodProj, CodEmp, DataIni, TempoAloc)
Empregados (CodEmp, Nome, Cat, Sal)
DFs: CodEmp
Nome, Cat, Sal (DF Parcial!)
(CodProj, CodEmp)
DataIni, TempoAloc
DF Transitiva ou Indireta
• Se um atributo
não-chave
A
x
possui
DF total
da CP de uma tabela T e também possui DF
total de um ou mais atributos
não-chave
de
T, então diz-se que A
x
possui
DF transitiva
ou indireta
da CP de T
•
“Uma tabela está na 3FN sse ela estiver
na 2FN e não possuir DFs indiretas”
–
tabelas com DFs indiretas devem ser
desmembradas em tabelas que não
possuam tais DFs
•
Tabelas que possuem zero ou apenas
um atributo que não faz parte da CP
estão automaticamente na 3FN
3
a
Forma Normal (3FN)
3FN - Aplicação
2FN: Projetos (CodProj, Tipo, Descr)
Alocações (CodProj, CodEmp, DataIni, TempoAloc)
Empregados (CodEmp, Nome, Cat, Sal)
3FN: Projetos (CodProj, Tipo, Descr)
Alocações (CodProj, CodEmp, DataIni, TempoAloc)
Empregados (CodEmp, Nome,
Cat
)
CategoriasFuncionais(Cat, Sal)
DFs: CodEmp
Sal
Normalização – Questões
•
Análise de CPs
–
tabelas podem ou não ter atributos que
garantam identificação única de suas tuplas
ou ter uma CP muito extensa
•
sugestão: definir uma CP
ÑN: Projetos (CodProj, Tipo, Descr,
(Nome, Cat, Sal, DataIni, TempoAloc))
ÑN: Projetos (CodProj, Tipo, Descr,
(
CodEmp
, Nome, Cat, Sal, DataIni, TempoAloc))
•
Dados irrelevantes
–
tabelas podem ter atributos que não precisam
ser mantidos necessariamente no BD
•
sugestão: eliminar estes atributos
ÑN: Projetos (CodProj, Tipo, Descr,
NroEmps
,
DataRel
,
(CodEmp, Nome, Cat, Sal, DataIni, TempoAloc))
ÑN: Projetos (CodProj, Tipo, Descr,
(CodEmp, Nome, Cat, Sal, DataIni, TempoAloc))
•
Dados relevantes, porém implícitos
–
sugestão: definir tais dados
ÑN: Aprovação (CodCurso, Nome,
(CodCand, Nome, Endereço)
)
a ordem determina a
classificação do candidato
ÑN: Aprovação (CodCurso, Nome,
(CodCand, Nome, Endereço,
OrdemClass
))
Normalização – Questões
•
Relacionamentos incorretos
–
sugestão: validar as tabelas ao final do
processo!
Normalização – Questões
1FN: Pedidos (nroPed, dataPed, codCli, nomeCli)
Telefones (nroPed, nroTel)
Peças (nroPed, codPeça, nomePeça, qtde))
Relatório de Pedidos
ÑN: Pedidos (nroPed, dataPed, codCli, nomeCli,
(nroTel), (codPeça, descrPeça, qtdePedida))
. . .
(validação)
DF Multivalorada
• Se um atributo A
x1
de T determina um
conjunto finito de valores para os outros
atributos A
x2
, ..., A
xn
de T, então diz-se que
A
x2
, ..., A
xn
possuem
DF multivalorada
de A
x1
em T
Exemplo de Normalização até 3FN
Ficha de Estante e seus Livros
ÑN: Estantes (número, capacidade, (ISBN, título, ano
(codAutor, nome, nacionalidade)))
1FN: Estantes (número, capacidade)
DistribuiçãoLivros (número, ISBN, título, ano)
DistribuiçãoLivrosAutor (número, ISBN, codAutor,
nome, nacionalidade)
2FN = 3FN: Estantes (número, capacidade)
Livros (ISBN, título, ano)
DistribuiçãoLivros (número, ISBN)
Autores (codAutor, nome, nacionalidade)
DistribuiçãoLivrosAutor(
número, ISBN
, codAutor)
relacionamento
contido em outra
tabela! (pode ser
removido)
Exemplo de DF Multivalorada
número
ISBN
codAutor
E1
E1
E1
E2
E1
E2
E3
E3
E1
E3
L1
L2
L1
L1
L2
L1
L2
L2
L2
A7
L2
A1
A2
A8
A9
A1
A2
Redundância
de dados para
representar as
associações
multivaloradas!
ISBN = L1
codAutor = {A1, A2}
ISBN = L2
codAutor = {A7, A8,
A9}
A7
A8
A9
DF multivalorada:
ISBN
codAutor
DistribuiçãoLivrosAutor
•
“Uma tabela está na 4FN sse ela estiver
na 3FN e não possuir DFs multivaloradas”
–
tabelas com DFs multivaloradas devem ser
desmembradas em tabelas que não possuam
tais DFs
•
Tabelas que possuem CP composta por
um ou dois atributos, ou que possuem
atributos não-chave estão
automaticamente na 4FN
4FN - Aplicação
DF Multivalorada:
ISBN
codAutor
3FN: Estantes (número, capacidade)
Livros (ISBN, título, ano)
Autores (codAutor, nome, nacionalidade)
DistribuiçãoLivrosAutor(número, ISBN, codAutor)
4FN: Estantes (número, capacidade)
Livros (ISBN, título, ano)
Autores (codAutor, nome, nacionalidade)
DistribuiçãoLivros(número, ISBN)
Autoria(ISBN, codAutor)
Exercício 1
A tabela abaixo está na 1FN. Obtenha a 2FN,
3FN e 4FN
ArtigosAceitos (ID-artigo, título, ano, ID-autor,
codEvento, nomeEvento, ID-revisor)
Exercício 2 – Relatório de Reservas em
uma Locadora de DVDs
. . . 14/02/07 5,00 lançamento 98 ficção X-Men III D12 15/02/07 2,00 simples 105 guerra Platton D9 15/02/07 3,00 especial 99 infantil Os Incríveis D5 13/02/07 3,00 especial 99 infantil Os Incríveis D5 14/02/07 3,00 especial 88 comédia Os Normais D2 3221.1212 Ana Luz 0051 18/02/07 2,00 simples 105 guerra Platton D9 15/02/07 2,00 simples 75 infantil Rei Leão D4 12/02/07 5,00 lançamento 95 drama Casa de Areia D1 3222.3344 João Sá 0032 data retirada preço categoria duração gênero nome ID-DVD Reservados DVDs Telefone Nome ID-cliExercício 3 – Arquivo XML: Roteiros de Viagem
<?xml version =“1.0”?><roteiros>
<roteirocodigo=”R001”><nome>Serra Gaucha</nome><duracao>10</duracao> <pacotes>
<pacote><dataSaida>20-01</dataSaida><vagas>40</vagas><preco>1200.00</preco></pacote> <pacote><dataSaida>05-02</dataSaida><vagas>50</vagas><preco>1000.00</preco></pacote> ... </pacotes> <trechos> <cidadenumeroOrdem=”01”><nome>Gramado</nome> <estado>RS</estado><populacao>35000</populacao><tempoEstadia>2</tempoEstadia> </cidade>
<cidadenumeroOrdem=”02”><nome>Caxias do Sul</nome>
<estado>RS</estado><populacao>400000</populacao><tempoEstadia>2</tempoEstadia> </cidade> ... <cidadenumeroOrdem=”05”><nome>Gramado</nome> <estado>RS</estado><populacao>35000</populacao><tempoEstadia>1</tempoEstadia> </cidade> </trechos> </roteiro>
<roteirocodigo=”R002”><nome>Região dos Vinhos</nome><duracao>6</duracao> <pacotes>
<pacote><dataSaida>22-01</dataSaida><vagas>45</vagas><preco>800.00</preco></pacote> <pacote><dataSaida>06-07</dataSaida><vagas>40</vagas><preco>900.00</preco></pacote> ...
</pacotes> <trechos>
<cidadenumeroOrdem=”01”><nome>Caxias do Sul</nome>
<estado>RS</estado><populacao>400000</populacao><tempoEstadia>2</tempoEstadia> </cidade>
<cidadenumeroOrdem=”02”><nome>Bento Goncalves</nome>
<estado>RS</estado><populacao>235000</populacao><tempoEstadia>2</tempoEstadia> </cidade>
... </trechos> </roteiro>
