wiki

ESOF - Aula 14 - 2014/1

Normalização



Dependência Funcional


  • Consiste numa restrição entre dois conjuntos de atributos de uma mesma entidade/relação
  • Uma dependência funcional é representada pela relação X -> Y, em que X e Y são subconjuntos de atributos de uma relação qualquer
  • Isso impõe uma restrição na qual um componente Y de uma tupla (registro) é dependente de um valor do componente X (ou é determinado por ele)


  • Do mesmo modo, o valores do componente X determinam de forma unívoca os valores do componente Y
  • Resumindo, Y é dependente funcionalmente de X


  • Supondo o esquema de uma relação abaixo, onde os três primeiros atributos, cujos nomes se encontram destacados, representam a chave primária da relação
    • MatriculaAluno
    • CodigoCurso
    • CodigoDisciplina
    • NomeAluno
    • DataMatricula
    • NomeCurso
    • NomeDisciplina
    • NotaProva


  • Podemos estabelecer 4 dependências funcionais neste exemplo:
  1. MatriculaAluno -> {NomeAluno, DataMatricula) => O valor do atributo MatriculaAluno determina o valor dos atributos NomeAluno e DataMatricula
  2. CodigoCurso -> NomeCurso => O valor do atributo CodigoCurso determina o valor do atributo NomeCurso
  3. CodigoDisciplina -> NomeDisciplina => O valor do atributo CodigoDisciplina determina o valor do atributo NomeDisciplina
  4. {MatriculaAluno, CodigoCurso, CodigoDisciplina} -> NotaProva => A combinação de valores dos atributos MatriculaAluno, CodigoCurso e CodigoDisciplina determina o valor do atributo NotaProva.


Total ou Completa


  • Podemos ter situações em que não se utilizam atributos simples para determinar os valores de outros atributos
  • Neste caso, teremos um atributo que é dependente funcional da combinação de dois ou mais atributos
  • Quando um atributo que não faz parte da chave primária depende funcionalmente de todos os atributos que fazem parte da chave, tem-se uma dependência funcional total.


  • É considerado o 1o. tipo, onde a dependência só existe se a chave primária composta por vários atributos determinar univocamente um atributo ou um conjunto de atributos
  • No nosso exemplo, o atributo NotaProva é dependente total da chave primária formada pelos atributos MatriculaAluno/ CodigoCurso/CodigoDisciplina.
  • Exemplo: Entidade DEPENDENTE
    • (CodigoPaciente, DataNascimento} -> {NomeDependente} (?)



  • No caso, os valores dos atributos CodigoPaciente e DataNascimento determinam o valor para o atributo NomeDependente
    • O nome do dependente só pode ser determinado em função de dois atributos


Parcial


  • Temos uma situação em que um atributo/conjunto de atributos depende de outro(s) atributo(s) que não fazem parte de uma chave primária
  • Quando um atributo que não faz parte da chave primária depende funcionalmente de apenas alguns dos atributos que fazem parte da chave primária, o 2o. tipo, então, ocorre visto que o atributo/conjunto de atributos depende apenas de parte dos valores da chave primária


  • Exemplo: Banco de Dados com as entidades MEDICO e PACIENTE
    • CRM -> NomeMedico
    • CodigoPaciente -> {NomePaciente, CPF, RG }



  • A 1a, dependência especifica que o valor atributo CRM da entidade MEDICO determina de forma unívoca o valor do atributo NomeMedico dessa mesma entidade
  • O valor do atributo CodigoPaciente (entidade PACIENTE) determina o nome, o CPF e o RG do paciente


  • É importante notar que apenas o valor dos atributos CRM e CodigoPaciente é necessário para que seja possível determinar o nome do médico ou o CPF e RG do paciente, ou seja, é uma dependência parcial


Transitiva ou Indireta


  • Esta dependência ocorre quando a dependência funcional se realiza entre atributos que não fazem parte da chave primária
  • Exemplo:
    • Numa tabela de Vendas, temos o atributo PreçoTotal. Este campo é o resultado do valor unitário do produto multiplicado pela quantidade, isto é, para um preço total existir ele DEPENDE de valor unitário e quantidade
    • O ValorUnitário deve estar numa tabela Produtos, relacionada à venda e Quantidade está na própria tabela Vendas.
    • PreçoTotal depende destes dois campos e eles não são campos-chave.



Normalização


  • Após a construção do modelo conceitual dos objetos é feita a transformação para o modelo lógico (Esquema de Classes)
  • O desenho de classes obtido representa a estrutura da informação de um modo natural e completo.


  • Normalização é um processo baseado nas chamadas formais normais
    • Uma forma normal é uma regra que deve ser aplicada na construção das classes (tabelas do banco de dados) para que estas fiquem bem otimizadas
    • A normalização pode ser entendida como um processo submetido a estas varias formas normais


  • Objetivo principal:
    • eliminar a redundância nos dados armazenados em tabelas, resultando na diminuição do espaço e dos riscos de inconsistências em atualizações de dados
  • Quando um atributo é alterado em uma tabela que não está totalmente normalizada, é necessário alterá-lo em todas as linhas em que ele ocorre, haja visto a sua repetição
  • Tal operação poderia ser executada apenas uma vez, caso este atributo estivesse normalizado
  • As formas têm uma ordem e são dependentes, isto é, para se aplicar a segunda norma, deve-se obrigatoriamente ter aplicado a primeira e assim por diante.




  • Efetivamente, a Normalização tem como objetivo avaliar a qualidade do Modelo de Tabelas e transformá-lo (em caso de necessidade) num Modelo (Conjunto de Tabelas) equivalente, menos redundante e mais estável.


1FN


  • Verificação de Tabelas Aninhadas


  • Uma relação está na 1a. Forma Normal se e somente se cada linha contiver exatamente um valor para cada atributo
  • Dado que as Relações(Tabelas) são estruturas bidimensionais, então no cruzamento de uma linha com uma coluna (atributo) só é possível armazenar valores atômicos.
  • Para isso, não deve conter tabelas aninhadas


  • Um jeito fácil de verificar esta norma é fazer uma leitura dos campos das tabelas fazendo a pergunta:
    • Este campo depende de algum outro?


  • Se sim, então devemos arrumar um método para corrigir o problema.


  • Método:
    • Remover o grupo de repetição
    • Expandir a chave primária


  • Seguindo a definição devemos normalizar a tabela decompondo-a em duas
    • Uma relação R está na 1FN se:
    • Todo valor em R for atômico
    • Ou seja, R não contém grupos de repetição


  • Considerações:
    • Geralmente considerada parte da definição formal de uma relação
    • Não permite atributos multivalorados, compostos ou suas combinações




Caso 1


   cliente (NroCliente, Nome, {End-Cliente})
   Corrigindo o problema:
       Solução: cliente (NroCliente, Nome, End-Cliente, CidCliente, UFCliente)


Caso 2


  • Exemplificando com a tabela Venda
  • Esquema relacional da tabela:
       Venda
           Codvenda (Int)
           Cliente (Str)
           Endereco (Str)
           Cep (Int)
           Cidade (Str)
           Estado (Str)
           Telefone (Int)
           Produto (Str)
           Quantidade (Float)
           ValorUnitário (Float)
           PreçoTotal (Float)


  • Análise:
    • A tabela Venda, deve armazenar informações da venda
    • O campo Cliente é dependente de CodVenda, afinal para cada Venda há um cliente
    • Campo Endereço: não depende de Codvenda, e sim de Cliente, pois é uma informação particular ao cliente
    • Não existe um endereço de venda, existe sim um endereço do cliente para qual se fez a venda
    • Nisso podemos ver uma tabela aninhada. Os campos entre colchetes, são referentes ao cliente e não é venda
       Venda (Codvenda, [Cliente, Endereço, Cep, Cidade, Estado, Telefone], Produto, Quantidade, ValorUnitário, PreçoTotal)


  • Solução:
    • Extrair estes campos para uma nova tabela
    • Adicionar uma chave-primária à nova tabela
    • Relacioná-la com a tabela Venda criando uma chave-estrangeira


  • Resultado:
       Cliente (Codcliente, Nome, Endereço, Cep, Cidade, Estado, Telefone).
       Venda (Codvenda, Codcliente, Produto, Quantidade, ValorUnitário, PreçoTotal).


  • Aplicando novamente a 1a. forma normal às 2 tabelas geradas
  • Uma situação comum em tabelas de cadastro é o caso Cidade-Estado
  • Analisando friamente pela forma normal, o Estado na tabela Cliente, depende de Cidade
  • No entanto Cidade, também depende de Estado, pois no caso de a cidade ser Curitiba o estado sempre deverá ser Paraná, porém se o Estado for Paraná, a cidade também poderá ser Londrina
  • Isso é o que chamamos de Dependência funcional:
    • aparentemente, uma informação depende da outra


  • No caso Cidade-Estado a solução é simples:
    • Extraímos Cidade e Estado, de Cliente e geramos uma nova tabela
    • Em seguida, o mesmo processo feito anteriormente:
      • Adicionar uma chave-primária à nova tabela e relacioná-la criando uma chave-estrangeira na antiga tabela


   Cidade (Codcidade, Nome, Estado). 
   Cliente (Codcliente, Codcidade, Nome, Endereço, Cep, Telefone) 
   Venda (Codvenda, Codcliente, Codcidade, Produto, Quantidade, ValorUnitário, PreçoTotal)


  • Seguindo com o exemplo, a tabela Cliente encontra-se na 1a. forma normal, pois não há mais tabelas aninhadas
  • Verificando Venda, identificamos mais uma tabela aninhada
  • Os campos entre colchetes são referente à mesma coisa: Produto de Venda
   Venda (Codvenda, Codcliente, Codcidade, [Produto, Quantidade, Valorunitario, Valorfinal])


  • Na maioria das situações, produtos têm um valor previamente especificado
  • O ValorUnitário depende de Produto
  • Já a Quantidade não depende do Produto e sim da Venda
   Cidade (Codcidade, Nome, Estado) 
   Cliente (Codcliente, Codcidade, Nome, Endereço, Cep, Telefone) 
   Produto (Codproduto, Nome, ValorUnitário) 
   Venda (Codvenda, Codcliente, Codcidade, Codproduto, Quantidade, PreçoTotal)


  • Utilizando a 1a. Forma Normal na tabela Venda, obtivemos 3 tabelas


2FN


  • A 2a. Forma Normal exige um pouco mais de conhecimento sobre as dependências funcionais, prioritariamente para a dependência funcional total
  • Uma relação está na 2a. Forma Normal se estiver na 1a. e se todos os atributos descritores (não pertencentes a nenhuma chave candidata) dependerem da totalidade da chave (e não apenas de parte dela)


  • Isso quer dizer que uma tabela encontra-se na 2FN, quando, além de estar na 1FN, não contem dependências parciais
    • Dependência parcial = uma dependência parcial ocorre quando uma coluna depende apenas de parte de uma chave primária composta


  • Resumindo, a entidade se encontra na 2FN se, além de estar na 1a., todos os seus atributos são totalmente dependentes da chave primária composta
    • Significa que atributos que são parcialmente dependentes devem ser removidos.
    • Se observarmos as tabelas e identificarmos repetição dos dados nas tuplas requer-se a 2FN


  • Relendo cada campo e questionando:
    • Este campo depende de toda a chave?
    • Se não, temos uma dependência parcial




Caso 3


  • Uma entidade Item possui chave primária composta que é constituída pelos atributos NumPedido e CodProduto
  • Os atributos Descricao e PrecoUnit não dependem totalmente dessa chave, ao contrário de Quantidade e ValorTotal
    • Nova entidade: Produto


Caso 4


  • Reavaliando o caso Cidade-Estado que gerava uma dependência funcional
  • Após a normalização da tabela Venda, obtivemos uma chave composta de 4 campos:
       Venda (Codvenda, Codcliente, Codcidade, Codproduto, Quantidade, PreçoTotal)


  • A questão agora é verificar se cada campo não-chave depende destas 4 chaves


  • Procedimento:
  • O 1o. campo não-chave é Quantidade
    • Quantidade depende de Codvenda => Para cada venda há uma quantidade específica de itens
    • Quantidade depende de Codvenda e Codcliente => Para um cliente podem ser feitas várias vendas, com quantidades diferentes
    • Quantidade não depende de Cidade e quem depende de Cidade é Cliente


  • Temos uma dependência parcial pois Quantidade depende de Codproduto, pois para cada produto da Venda há uma quantidade certa


  • Quantidade depende de 3 campos, dos 4 que compõe a chave de Venda
  • Quem sobra nessa história é Codcidade
  • A tabela Cidade já está ligada com Cliente, que já está ligada com Venda
  • A chave Codcidade em Venda é redundante, portanto podemos eliminá-la
           Venda (Codvenda, Codcliente, Codproduto, Quantidade, PrecoTotal)


  • Problema:
    • Existe alguma necessidade de manter CodCidade como campo não-chave?


  • O próximo campo não-chave é PreçoTotal


  • Avaliando PreçoTotal da mesma forma que Quantidade
    • Chega-se à conclusão de que ele depende de toda a chave de Venda.


3FN


  • Uma relação encontra-se na 3a. Forma Normal se estiver na 2a. Forma Normal e se não existirem atributos descritores (não pertencentes a nenhuma Chave Candidata) a dependerem funcionalmente de outros atributos descritores (não-chaves)


  • Após a aplicação das regras da 2FN se ainda restarem dados redundantes na tabela avaliada pode-se empregar a 3FN


  • Terceira Forma Normal (3FN):
    • Efetivamente, 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 da tabela, depende de outra coluna ou conjunto de colunas da tabela
    • Assim sendo, cada atributo deve depender apenas das Chaves Candidatas da relação.




Caso 5


  • Em Pedidos, existem atributos que identificam:
    • um cliente (nome do cliente e endereço completo)
    • um vendedor (nome do vendedor)


  • Os dados dos atributos:
       NomeCliente
       EndCliente
       CidCliente
       UFCliente
  • são dependentes de CodCliente
  • Podemos criar outra entidade => Cliente


  • Da mesma forma, NomeVendedor é dependente de CodVendedor



Resumo


  • 1FN
  1. Cria-se uma tabela na 1FN referente à tabela N e que contém apenas colunas com valores atômicos, isto é, sem as tabelas aninhadas;
  2. Para cada tabela aninhada, cria-se uma tabela na 1FN compostas pelas seguintes colunas:
    1. A chave primária de uma das tabelas na qual a tabela em questão está aninhada
    2. As colunas da própria tabela
  3. São definidas as chaves primárias das tabelas na 1FN que correspondem a tabelas aninhadas


  • 2FN
  1. Copiar para a 2FN cada tabela que tenha chave primária simples ou que não tenha colunas além da chave
  2. Para cada tabela com chave primária composta e com pelo menos uma coluna não chave
  3. Criar na 2FN uma tabela com as chaves primárias da tabela na 1FN
  4. Para cada coluna não chave fazer a seguinte pergunta: a coluna depende de toda a chave ou de apenas parte dela?
  5. Caso a coluna dependa de toda a chave
    1. Criar a coluna correspondente na tabela com a chave completa na 2FN
  6. Caso a coluna não dependa apenas de parte da chave
    1. Criar, caso ainda não existir, uma tabela na 2FN que tenha como chave primária a parte da chave que é determinante da coluna em questão
  7. Criar a coluna dependente dentro da tabela na 2FN


  • 3FN
  1. Copiar para o esquema da 3FN cada tabela que tenha menos de duas colunas não chave, pois neste caso não há como haver dependências transitivas
  2. Para tabelas com duas ou mais colunas não chaves, fazer a seguinte pergunta: a coluna depende de alguma outra coluna não chave?
  3. Caso dependa apenas da chave
    1. Copiar a coluna para a tabela na 3FN
  4. Caso a coluna depender de outra coluna
    1. Criar, caso ainda não exista, uma tabela no esquema na 3FN que tenha como chave primária a coluna na qual há a dependência indireta
    2. Copiar a coluna dependente para a tabela criada
    3. A coluna determinante deve permanecer também na tabela original


Exemplo

<br:>


1a. Forma Normal

  • Uma Tabela está na 1a. FN quando seus atributos não contém grupos de repetição (tabelas aninhadas):
       Projeto (CodProjeto, Descricao, (CodFunc, Nome, Salario, DataInicio)) => Não está na 1FN
       Projeto (CodProjeto, Descricao) => Está na 1FN
       ProjFunc (CodProjeto, CodFunc, Nome, Salario, DataInicio) => Está na 1FN


2a. Forma Normal

  • Ocorre quando a chave primária é composta por mais de uma coluna
  • Neste caso, deve-se observar se todas as colunas que não fazem parte da chave dependem de todos os colunas que compõem a chave
  • Se alguma coluna depender somente de parte da chave composta (dependência funcional parcial), então esta coluna deve pertencer a outra tabela
       ProjFunc (CodProj, CodFunc, Nome, Cargo, Salario, DataInicio) => Não está na 2FN
       ProjFunc (CodProj, CodFunc, DataInicio) => Está na 2FN
       Func (CodFunc, Nome, Cargo, Salario) => Está na 2FN


3a. Forma Normal

  • Uma tabela está na 3a. Forma Normal quando cada coluna não chave depende diretamente da chave primária, isto é, quando não há dependências funcionais transitivas ou indiretas
  • Uma dependência funcional transitiva acontece quando uma coluna não chave primária depende funcionalmente de outra coluna ou combinação de colunas não chave primária
       Func (CodFunc, Nome, Cargo, Salario) => Não está na 3FN
       Func (CodFunc, Nome, CodCargo) => Está na 3FN
       Cargo (CodCargo,Salario) => Está na 3FN


  • Chaves candidatas:
       NomeAluno + EnderecoAluno
       Nomeluno + NroSala
       NomeAluno + NomeProfessor


  • Encontramos três chaves candidatas
    • Todas apresentam mais de um atributo (concatenados)
    • Todas compartilham um mesmo atributo: NomeAluno

Exercícios

  • Normalizar sequencialmente segundo as formas normais, sempre que possível:


01. Encomenda de Livros

   PedidoLivro (NomeCliente, ISBN, DataPedido, Titulo, Autor(es), Quantidade, Preço, ValorTotal)
  • Arquivo:Normalizacao-PedidoLivro.pdf


02. Projetos

       Empregado (NroEmpregado, NomeEmpregado, NroDepto, NomeDepto, NroGerente, NomeGerente, NroProjeto, NomeProjetom, DataInicio, FTE)
  • Arquivo:Normalizacao-Empregado.pdf


03. Compras

       Ordem de Compra (CodOrdemCompra, DataEmissao, CodFornecedor, NumFornecedor, EndFornecedor, CodMaterial (n vezes), DescricaoMaterial (n vezes), QuantComprada (n vezes), ValorUnit (n vezes), ValorTotalItem (n vezes), ValorTotalOC)
  • Arquivo:Normalizacao-OC.pdf


04. Notas Fiscais

       NotasFiscais (Num_ NF, Série, Data emissão, CodCliente, Nome Cliente, EndCliente, CgcCliente, CodigoMercadoria, DescricaoMercadoria, QuantidadeVendida, PrecoVenda, TotalVendaMercadoria, TotalGeral)
  • Arquivo:Normalizacao-NF.pdf


05. Gestão de Projetos

       Gestao Projetos (NumProjeto, NumEmpregado, NomeProjeto, NomeEmpregado, Funcao, Salario, Horas)
  • Arquivo:Normalizacao-GestaoProjetos.pdf


06. Vendas

       Vendedor (NumVendedor, NomeVendedor, EndVendedor, Telefone, Cep, Localidade, NumProduto, DescricaoProduto, Saldo, PrecoUnitário, NumFatura, QuantVendida, Total)
  • Arquivo:Normalizacao-Vendedor.pdf


07. Recursos Humanos

       Funcionario (NumFuncionario, NomeFuncionario, NumEmpresa, NomeEmpresa, NumDepto, NomeDepto)
  • Arquivo:Normalizacao-Funcionario.pdf


08. Controle Acadêmico

       Aluno ( NroAluno, CodDepto, NomeDepto, SiglaDepto, CodOrientador, NomeOrientador,FoneOrientador, CodCurso)
  • As seguintes depend�ncias funcionais devem ser garantidas na normalização:
           CodDepto ? {NomeDepto, SiglaDepto}
           CodOrientador ? {NomeOrientador, FoneOrientador}
           NroAluno ? {CodDepto, CodOrientador, CodCurso}
  • Observações adicionais:
    • Um aluno somente pode estar associado a um departamento
    • Um aluno cursa apenas um único curso
    • Um aluno somente pode ser orientado por um único orientador
           Arquivo:Normalizacao-Aluno.pdf


09. Corporativo

       Empresa (CodEmpresa, NomeEmpresa, EndEmpresa, NomeFundador, NacionalidadeFundador, { Filial (FilialNro, FilialLocal, FilialDataAbertura) })
  • As seguintes depend�ncias funcionais devem ser garantidas na normalização:
           CodEmpresa ? {NomeEmpresa, EndEmpresa, NomeFundador}
           NomeFundador ? NacionalidadeFundador
           {CodEmpresa, FilialNro} ? {FilialLocal, FilialDataAbertura}
  • Observações adicionais:
               Uma empresa somente pode ter sido fundada por um único fundador
  • Arquivo:Normalizacao-Empresa.pdf


10. Vendas

       Vendedor (NroVendedor, NomeVendedor, SexoVendedor,{Cliente (NroCliente, NomeCliente, EndCliente ) })
  • As seguintes depend�ncias funcionais devem ser garantidas na normalização:
           NroVendedor ? NomeVendedor, SexoVendedor
           NroCliente ? NomeCliente, EndCliente
  • Observações adicionais:
    • Um vendedor pode atender diversos clientes, e um cliente pode ser atendido por diversos vendedores
  • Arquivo:Normalizacao-Vendas.pdf



Disponível em “http://3.143.69.156/wiki/index.php?title=ESOF_-_Aula_14_-_2014/1&oldid=29387”