Baixe o app para aproveitar ainda mais
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.
Compartilhar