BD3_Normalizacao

Prévia do material em texto

1
BANCO DE DADOS IBANCO DE DADOS I
Prof. Joaquim Cezar FelipeProf. Joaquim Cezar FelipeProf. Joaquim Cezar FelipeProf. Joaquim Cezar Felipe
NormalizaçãoNormalização
MODELO RELACIONALMODELO RELACIONALMODELO RELACIONALMODELO RELACIONALMODELO RELACIONALMODELO RELACIONALMODELO RELACIONALMODELO RELACIONAL
• RELAÇÃORELAÇÃORELAÇÃORELAÇÃO = coleção de dados armazenados 
em uma tabela
• ESQUEMAESQUEMAESQUEMAESQUEMA = cjto de atributos que compõem 
uma tabela (assinatura) 
• TUPLATUPLATUPLATUPLA = linha (ou instância, ou registro) da 
tabela
Aluno (nro_matr, nome, fone, RG, curso)
r(Aluno):
t1 = {111111-1, Lucas, 6101010, 11111111, Economia}
t1 [nome, curso] = {Lucas, Economia}
r (Aluno)= {t1, t2, t3}
• Obs: 1 relação pode conter parte do conteúdo de 1 tabela
ex: r1 (Aluno)= {t1, t3}
MODELO RELACIONALMODELO RELACIONALMODELO RELACIONALMODELO RELACIONALMODELO RELACIONALMODELO RELACIONALMODELO RELACIONALMODELO RELACIONAL
nro_matr nome fone RG curso
t1 111111-1 Lucas 6101010 11111111 Economia
t2 222222-2 Rodrigo 6252525 22222222 Eng Comp
t3 333333-3 Luciana 6343434 33333333 Enfermagem
t1
t2
t3
ESQUEMA
RELAÇÃO
TUPLA
CHAVESCHAVESCHAVESCHAVESCHAVESCHAVESCHAVESCHAVES
• CHAVE CANDIDATACHAVE CANDIDATACHAVE CANDIDATACHAVE CANDIDATA é todo cjto de 
atributos que identifica univocamente 
cada tupla da relação
– Ex: {nro_matr}, {RG} e {nome, fone}
• CHAVE PRIMÁRIA CHAVE PRIMÁRIA CHAVE PRIMÁRIA CHAVE PRIMÁRIA é a chave candidata que 
foi escolhida para identificar as tuplas
– Ex: {nro_matr}
• SUPERCHAVESUPERCHAVESUPERCHAVESUPERCHAVE é qualquer cjto de atributos 
que contenha pelo menos uma chave
– Ex: {nro_matr}, {nro_matr, nome, curso}, {RG}, 
{RG, curso}, {nro_matr, RG, fone}, etc
Aluno (nro_matr, nome, fone, RG, curso)
RESTRIÇÕES DE INTEGRIDADERESTRIÇÕES DE INTEGRIDADERESTRIÇÕES DE INTEGRIDADERESTRIÇÕES DE INTEGRIDADERESTRIÇÕES DE INTEGRIDADERESTRIÇÕES DE INTEGRIDADERESTRIÇÕES DE INTEGRIDADERESTRIÇÕES DE INTEGRIDADE
• RESTRIÇÕES DE INTEGRIDADE são regras
que dizem respeito aos valores que podem
ser armazenados nas relações, e que devem
sempre ser satisfeitas.
• - Restrição de Unicidade de Chave: uma
chave primária não pode ter o mesmo valor
em duas tuplas distintas da relação.
• - Restrição de Integridade da Entidade: a
chave primária não pode ter valor nulo em
nenhuma tupla da relação.
RESTRIÇÕES DE INTEGRIDADERESTRIÇÕES DE INTEGRIDADERESTRIÇÕES DE INTEGRIDADERESTRIÇÕES DE INTEGRIDADERESTRIÇÕES DE INTEGRIDADERESTRIÇÕES DE INTEGRIDADERESTRIÇÕES DE INTEGRIDADERESTRIÇÕES DE INTEGRIDADE
• - Restrição de Integridade Referencial: uma
tupla de uma relação que faz referência a outra
relação (por meio de uma chave estrangeira) deve
referir-se a uma tupla existente naquela relação
(consistência)
Ex: o atributo nro_depto na relação Projeto deve possuir
um valor existente em alguma tupla da relação Depto.
• - Restrição de Integridade Semântica: diz
respeito às regras de uso do banco de dados no
contexto em que o mesmo se encontra inserido
Ex: o valor do atributo horas_trab não pode exceder 200.
O valor do atributo salário não pode ser negativo.
2
DEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONAL
• Seja r uma relação sobre um esquema R,
com X e Y sendo subconjuntos de atributos
de R.
• A DEPENDÊNCIA FUNCIONAL X ���� Y
estabelece que
para quaisquer duas tuplas t1 e t2 em r
se t1[X] = t2[X], então t1[Y] = t2[Y]
Dizemos que Y depende funcionalmente de
X, ou que X determina Y
DEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONAL
cep � cidade id_cliente� nome
id_cliente� fone
id_cliente� cidade
id_cliente� cep
Ex: Cliente (id_cliente, nome, fone, cidade, cep)
Ex: Aluno (nro_matr, nome, fone, RG, curso)
nro_matr � nome
nro_matr � fone
nro_matr � RG
nro_matr � curso
RG � nro_matr
RG � nome
RG � fone
RG � curso
Em termos de DF: CHAVE para um esquema R é um subconjunto K de 
atributos de R, tal que para qualquer possível relação r(R), a DF K � R é 
satisfeita
nome, fone � nro_matr
nome, fone � RG
nome, fone � curso
DEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONAL
Outra notação:
Aluno (nro_matr, nome, fone, RG, curso)
DEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONAL
• Y é TOTALMENTE DEPENDENTE de X, 
se não há um subconjunto X' de X, 
tal que X' � Y
caso contrário, a dependência é PARCIAL
• Seja um atributo A ∈ R, A ⊄ XY
A é TRANSITIVAMENTE DEPENDENTE de X 
em R, se X � Y, Y não determina X e Y � A
• Um atributo A ∈ R é PRIMO em R 
se A está contido em alguma ch candidata de R. 
Caso contrário, A é dito NÃO PRIMO em R.
DEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONALDEPENDÊNCIA FUNCIONAL
Axiomas da Dependência Funcional
• 1) Reflexiva: se X ⊃ Y, então X � Y
• 2) Aumentativa: se X � Y, então XZ � YZ
• 3) Transitiva: se X � Y e Y � Z, então X � Z
• 4) Decompos./Projeção: se X � YZ, então X � Y e X � Z
• 5) União/Aditiva: se X � Y e X � Z, então X � YZ
• 6) Pseudo-Transitiva: se X � Y e WY � Z, então WX � Z
NORMALIZAÇÃONORMALIZAÇÃONORMALIZAÇÃONORMALIZAÇÃONORMALIZAÇÃONORMALIZAÇÃONORMALIZAÇÃONORMALIZAÇÃO
• Como garantir consistënciaconsistënciaconsistënciaconsistëncia e impedir 
redundânciaredundânciaredundânciaredundância dos dados?
– Recursos do SGBD
– Programação bem feita dos aplicativos
– Projetista do banco � modelagem de qualidademodelagem de qualidademodelagem de qualidademodelagem de qualidade
• NORMALIZAÇÃONORMALIZAÇÃONORMALIZAÇÃONORMALIZAÇÃO � garante aspectos de 
qualidade do projeto do banco 
– Etapa subsequente ao mapeamento
– Otimização de projeto pré-existente
– Modelagem pelo método “mega relations”
• validaçãovalidaçãovalidaçãovalidação dos esquemas criados � FORMAS FORMAS FORMAS FORMAS 
NORMAISNORMAISNORMAISNORMAIS
3
1a FORMA NORMAL1a FORMA NORMAL1a FORMA NORMAL1a FORMA NORMAL1a FORMA NORMAL1a FORMA NORMAL1a FORMA NORMAL1a FORMA NORMAL
Um esquema de relação R está na 1a Forma 
Normal (1FN), se para todo atributo A ∈∈∈∈ R, os 
valores desse atributo são obrigatoriamente 
atômicos em todas as tuplas da relação.
� Ex:
Func = (nro_INSS, nome, dta_nasc, empregos_antempregos_antempregos_antempregos_ant, 
nro_depto)
Func = (nro_INSS, nome, dta_nasc, nro_depto)
Empr_Ant = (nro_INSS, emprego_anterior)
2a FORMA NORMAL2a FORMA NORMAL2a FORMA NORMAL2a FORMA NORMAL2a FORMA NORMAL2a FORMA NORMAL2a FORMA NORMAL2a FORMA NORMAL
Um esquema de relação R está na 2a Forma Normal (2FN) 
se ele está na 1FN e se todo atributo NÃO PRIMO de R é 
TOTALMENTE DEPENDENTE de todas as chaves 
candidatas de R.
Ex: Func_Projeto =
(nro_INSS, nro_proj, horas_trab, nome_func, nome_proj, local_trab)
Funcionário = (nro_INSS, nome_func)
Projeto = (nro_proj, nome_proj)
Func_Projeto = (nro_INSS, nro_proj, horas_trab, local_trab)
3a FORMA NORMAL3a FORMA NORMAL3a FORMA NORMAL3a FORMA NORMAL3a FORMA NORMAL3a FORMA NORMAL3a FORMA NORMAL3a FORMA NORMAL
Um esquema de relação R está na 3a Forma Normal (3FN), 
se ele está na 2FN e se não há nenhum atributo NÃO 
PRIMO que seja TRANSITIVAMENTE DEPENDENTE sobre 
alguma chave de R.
Ex: Funcionário = 
(nro_INSS, nome, dta_nasc, nro_depto, gerente_depto)
Funcionário = (nro_INSS, nome, dta_nasc, nro_depto)
Depto = (nro_depto, gerente_depto)
ExercíciosExercíciosExercíciosExercíciosExercíciosExercíciosExercíciosExercícios1) Coloque na 3FN o seguinte esquema:
Pedido_Livro = (cod_cli, dta_pedido, ISBN, título, autores,
qtde, preço_unit, desconto%, fones_cli)
Considere que o desconto é proporcional à qtde de livros.
2) Os condomínios da cidade são divididas em lotes. Cada
lote em um condomínio recebe um nro de lote. O valor do
metro quadrado do lote depende só do condomínio onde o
mesmo se encontra. O valor do IPTU depende só da área
do lote:
Lote = (cod_lote, condomínio, nro_lote, área, valor_IPTU,
valor_m2)
Coloque na 3FN esse esquema.
ExercíciosExercíciosExercíciosExercíciosExercíciosExercíciosExercíciosExercícios
3) Normalizar o seguinte esquema:
( A , B , C , D , E , F , G , H , I , J )
FORMA NORMAL DE BoyceFORMA NORMAL DE BoyceFORMA NORMAL DE BoyceFORMA NORMAL DE BoyceFORMA NORMAL DE BoyceFORMA NORMAL DE BoyceFORMA NORMAL DE BoyceFORMA NORMAL DE Boyce--------CoddCoddCoddCoddCoddCoddCoddCodd
Ex: suponhamos que, no exerc. 2, a mesma área de lote não se repita 
em diferentes condomínios:
Lote = (cod_lote, cond , nro_lote, área)
Chave candidata 
Lote = (cod_lote, nro_lote, área)
Cond_Área = (área, cond)
Um esquema está na FNBC se, para toda dependência 
funcional X�A, X é uma SUPERCHAVE.
Diferença para a 3FN: a FNBC não admite que haja um 
atributo PRIMO que seja transitivamente dependente.
4
DEPENDÊNCIA MULTIVALORADADEPENDÊNCIA MULTIVALORADADEPENDÊNCIA MULTIVALORADADEPENDÊNCIA MULTIVALORADADEPENDÊNCIA MULTIVALORADADEPENDÊNCIA MULTIVALORADADEPENDÊNCIA MULTIVALORADADEPENDÊNCIA MULTIVALORADA
Haverá DEPENDÊNCIA MULTIVALORADA DEPENDÊNCIA MULTIVALORADA DEPENDÊNCIA MULTIVALORADA DEPENDÊNCIA MULTIVALORADA (DM)
X �� Y ( ou X ⇒ Y) 
se um valor de X é associado a uma coleçãocoleçãocoleçãocoleção
específicaespecíficaespecíficaespecífica de valores de Y 
Ex: cada professor possui um cjto de disciplinas que 
ele está apto a ministrar
cod_prof �� cod_disc
4a FORMA NORMAL4a FORMA NORMAL4a FORMA NORMAL4a FORMA NORMAL4a FORMA NORMAL4a FORMA NORMAL4a FORMA NORMAL4a FORMA NORMAL
Se duas ou mais dependências dependências dependências dependências 
multivaloradasmultivaloradasmultivaloradasmultivaloradas forem mapeadas na 
mesma tabela, haverá a geração de uma 
significativa redundância de dados. 
A 4444aaaa Forma NormalForma NormalForma NormalForma Normal (4FN) impede que 
isso aconteça.
4a FORMA NORMAL4a FORMA NORMAL4a FORMA NORMAL4a FORMA NORMAL4a FORMA NORMAL4a FORMA NORMAL4a FORMA NORMAL4a FORMA NORMAL
Ex: 
Funcionário
Nessa relação 
aparece uma 
redundância 
desnecessária, 
que pode ser 
resolvida 
assim: Func_Locais = (cod_func, local_trab)
Func_Atribs = (cod_func, atribuição)
cod_funccod_funccod_funccod_func local_trablocal_trablocal_trablocal_trab atribuiçãoatribuiçãoatribuiçãoatribuição
001 D f m carpintaria
001 D f m marcenaria
001 D química carpintaria
001 D química marcenaria
002 D psico segurança
002 D psico malote
002 D educ segurança
002 D educ malote
002 D química segurança
002 D química malote
ExercíciosExercíciosExercíciosExercíciosExercíciosExercíciosExercíciosExercícios
1) Normalizar:
Cirurgia = (cod_médico, cod_cirurgia, 
cod_material, data, duração, 
especialidade_méd, qtde_material, preço_hora)
Uma cirurgia pode ser realizada por vários médicos.
Uma cirurgia utiliza diferentes quantidades de vários
materiais.
O preço da hora do médico depende de sua
especialidade.
ExercíciosExercíciosExercíciosExercíciosExercíciosExercíciosExercíciosExercícios
2) Normalizar:
Empréstimo = (cod_emprest, nro_agência, 
nro_transação, gerente_que_aprovou, total$, 
clientes, juros_mês, cidade_agência)
Um mesmo empréstimo poder ser feito para vários 
clientes. 
Uma agência pode ter vários gerentes. Porém um 
gerente só está em uma agência. 
A taxa de juros depende do total de dinheiro 
emprestado.
Nro_agência e nro_transação constituem uma chave 
candidata para o esquema.
ExercíciosExercíciosExercíciosExercíciosExercíciosExercíciosExercíciosExercícios
3) Normalizar:
Fatura_Venda = (cod_fatura, cod_pedido, cod_produto, 
fabricante, preço_unit, quantidade, desconto, 
dta_pedido)
O atributo qtde é a quantidade de unidades de 1 certo produto 
em um certo pedido.
O desconto é proporcional à qtde.
Cada fatura pode referir-se a diversos pedidos.
Cada pedido pode possuir diversos produtos.