Normalização de Bancos de Dados Relacionais - Dependência Funcional

Banco de Dados Capítulo 10 Normalização de Bancos de Dados Relacionais Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Dependência Funcional T podemos dizer que a coluna Y de T é funcionalmente dependente da coluna X de T se e somente se cada valor de Y em T tiver a ele associado precisamente um único valor X em T Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Dependência Funcional Opcionalmente dizse que X determina Y Exemplos Matrícula determina NomeAluno e endereço telefone etc Placa determina Veículo Número determina Competidor Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Dependência Funcional A coluna determinante pode ser composta Aluno Disciplina determina Média Veículo Condutor Data determina Multa Existem tipos variados de dependência funcional Transitiva Multivalorada de Junção Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Dependência Funcional Em Bancos de Dados Relacionais podemos dizer que as chaves são as colunas que orientam para a seleção de uma ou mais linhas específicas em uma tabela Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Dependência Funcional Exemplos de chaves A matrícula de um aluno existe outro com a sua A Placa de um carro ou o número do Chassi ou o Renavam O Número de Inscrição de um competidor só há um piloto com o carro número 1 Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Dependência Funcional Chave primária Conjunto único de colunas uma ou mais colunas escolhido como identificador de uma Linha da tabela Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Dependência Funcional Chave Alternativa ou candidata Conjunto único de colunas normalmente unitário que pode ser usado alternativamente como chave primária como o número de chassis no lugar da placa do veículo Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Dependência Funcional Chave Estrangeira Conjunto de colunas muitas vezes unitário que aponta uma Linha em outra tabela é SEMPRE a chave primária da outra tabela Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Dependência Funcional Exemplo Seja a tabela VendaVendedor NumNota Loja As colunas Vendedor e Loja são identificadores únicos Chaves Primárias em outras tabelas Vendedor também possui salário nome etc Loja possui razão social endereço etc Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Normalização uma organização tal que atenda a um Usualmente determinase 3 formas normais acrescidas eventualmente de mais uma que podem ser estendidas em mais 2 Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Normalização Porque normalizar Para diminuir as dificuldades e os problemas anomalias em operações de transação inclusão exclusão alteração nos dados Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Primeira Forma Normal se todos os domínios básicos contiverem Toda tabela que não possua colunas multivaloradas está na 1FN Tabelas em Bancos de Dados Relacionais tradicionais estão necessariamente na 1FN Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Primeira Forma Normal Ex R1F Status Cidade P Qtd onde F é o número do Fornecedor uma chave estrangeira Status é a situação atual em termos de probabilidade de negócio de uma cidade P é a identificação código de uma peça outra chave estrangeira Cidade e Qtd são autoexplicativos Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Primeira Forma Normal R1 Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Primeira Forma Normal R1 continuação Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Primeira Forma Normal R1 está na 1FN Anomalias Inclusão Só se sabe que um fornecedor está em uma determinada cidade quando ele passa a oferecer alguma peça Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Primeira Forma Normal Anomalias Exclusão Se for excluída a última linha de um fornecedor não se saberá mais em que cidade ele está Alteração Se um fornecedor troca de cidade muitas linhas precisam ser alteradas sob pena de haver inconsistência Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Segunda Forma Normal Qual o problema Há dependência entre colunas não chave e chaves diferentes ao mesmo tempo F determina Cidade e o par F e P determina Qtd Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Segunda Forma Normal somente se ela está em 1FN e todas as colunas nãochave forem totalmente Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Segunda Forma Normal R2F Status Cidade e FPF P Qtd Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Segunda Forma Normal FPF P Qtd continuação Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Segunda Forma Normal R2 e FC estão na 2FN Observem que não há perda de informação Anomalias Inclusão O Status de uma cidade só é determinado se houver algum fornecedor para ela Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Segunda Forma Normal Anomalias Exclusão Removendose o último fornecedor de uma cidade perdese o seu Status Atualização Para se substituir o Status de uma cidade muitas linhas precisam ser trocadas Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Terceira Forma Normal Qual o problema Há dependência entre colunas não chave e outras colunas também não chave Dependência funcional transitiva se que A depende transitivamente de C se A depende de B e B depende de Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Terceira Forma Normal No exemplo Status depende de Cidade e Cidade depende de F Daí que Status depende transitivamente de F Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Terceira Forma Normal 3FN tabela está na 3FN se e somente se estiver na 2FN e todos as colunas nãochave forem dependentes não transitivas da chave Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Terceira Forma Normal FCF Cidade CSCidade Status FPF P Qtd Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Terceira Forma Normal FCF Cidade CSCidade Status FPF P Qtd FC CS E FP estão na 3FN Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios 1 Normalize até à terceira forma normal as seguintes estruturas de dados Encomendas NumEncomenda CodEncomenda EndCliente Produtos CodProduto NomeProduto ValorProduto DataEncomenda Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios 2 Normalize até à terceira forma normal as seguintes estruturas de dados Livros Codigo Nome AnoEdição CodigoTipo NomeTipo Autores CodigoAutor NomeAutor NacionalidadeAutor CodigoEditora NomeEditora Preço Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios 3 Normalize até a terceira forma normal a estrutura de dados seguinte referente a uma plantação que contém várias estufas As plantas que se encontram nas estufas apresentam cuidados especiais que poderão ser aplicados em diferentes plantas em qualquer estufa Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios Estufa Codigo Descrição Área Plantas Codigo Descrição DataPlantação Origem Localização QuantidadePlantada CuidadosEspeciais Codigo Descrição Duração Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios 4 A empresa XPTO tem para cada funcionário uma ficha com os seus dados pessoais onde consta o seu número nome data de nascimento e o código e o descritivo da função desempenhada pelo funcionário tem ainda o nome e a data de nascimento de cada um dos seus filhos e o nome do conjugue Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios Na ficha constam também dados referentes ao seu trabalho na empresa o número do chefe e as datas de quando começou e de quando terminou de trabalhar com esse chefe Nesta empresa um funcionário só tem um chefe mas pode ter tido vários chefes durante a sua atividade na empresa e pode ainda ter tido o mesmo chefe em datas diferentes Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios Um chefe é um funcionário da empresa Escreva a estrutura de dados não normalizada correspondente ao processo descrito no texto Normalize a estrutura de dados obtida apresentando a 1ª 2ª e a 3ª FN Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios 5 Considere a seguinte tabela que pretende registar os dados dos funcionários de várias empresas que nos prestam serviços Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios Cód da Empresa Nome Empresa Cód do Funcionári o Nome do Funcionário Telefone Empresa Telefone Funcionário Local Empresa Nome dos Filhos A Amorim A321 Fátima Teixeira 6002888 09367575757 Porto Duarte A Amorim A324 Tiago Brandão 6002888 09366758585 Porto Teresa B Sonae B211 Ana Rodrigues 9060099 09317896787 Maia João B Sonae B211 Ana Rodrigues 9060099 09317896787 Maia Pedro B Sonae B211 Ana Rodrigues 9060099 09317896787 Maia Catarina B Sonae B212 Telmo Antunes 9060099 09336898888 Maia Joana B Sonae B212 Telmo Antunes 9060099 09336898888 Maia Rita C Telecom C067 Diogo Freitas 4550022 09317686896 Matosinhos Mariana C Telecom C067 Diogo Freitas 4550022 09317686896 Matosinhos Rodrigo C Telecom D144 Isabel Batista 4550022 09333535333 Matosinhos Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios Nota o código de funcionário é único isto é um código de funcionário não se repete em empresas diferentes Em que forma normal se encontra a tabela Justifique Conceba a estrutura da base de dados sugerida para que se encontre na 3ª Forma Normal Apresente as estruturas obtidas em todas as Formas Normais Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios 6 Normalize até à terceira forma normal a seguinte estrutura de dados Viagens NumViagem Destino Confirmação NumConfirmação Data CodCliente NomeCliente Cpf Transporte CodTransporte Tipo DestinoTransporte CodMotorista NomeMotorista DataChegada DataSaida Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios 7 Normalize até à terceira forma normal a seguinte estrutura de dados atendendo a que um clube só pertence a uma associação e um orgão social é ocupado por uma só pessoa Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios Federação CodigoFederação Nome Endereço Telefone CEP Localidade OrgaosSociais CodigoOrgao DescOrgao NomeDirigente Associações CodAssociação NomeAssociação CodDistrito NomeDistrito Clubes NomeClube CodDivisão NomeDivisão Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios 8 Numa determinada escola pretendese conceber um sistema de informação para auxiliar na elaboração de horários Obteve se entre outras a seguinte estrutura adequada ao modo de funcionamento daquela escola Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios Horarios CodHorario CodCurso NomeCurso CodAno Turma Dia CodDia NomeDia Caracteristicas CodDisciplina NomeDisciplina TipoDisciplina Sala Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios Em que forma normal se encontra a tabela Justifique Conceba a estrutura da base de dados sugerida de forma a que se encontre na 3ª Forma Normal Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios 9 Normalize até à terceira forma normal a seguinte estrutura de dados tendo em consideração que um automóvel pode ser alugado por vários clientes Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios Alugueis NumeroCliente NomeCliente EndereçoCliente CEP Cidade NumeroCNH Automóvel Placa CodigoMarca Marca CodModelo Modelo Ano Cilindrada Aluguel DataInicio DataFim Desconto CustoDia Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios 10Normalize até à terceira forma normal a seguinte estrutura de dados tendo em consideração que um paciente pode consultar um médico em diferentes especialidades Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios Pacientes NumeroPaciente NomePaciente EndereçoPaciente CEP Cidade Telefone NumeroBeneficio Instituição Médico CodigoMedico NomeMedico EndereçoMedico Celular Telefone Especialidade CodigoEspecialidade DescriçãoEspecialidade Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios 11A Joana decidiu organizar o seu arquivo pessoal de contatos onde além dos contatos de telefones e endereço guardasse também as datas importantes de cada pessoa por exemplo nascimento casamento nascimento filho etc Normalize até à 3a Forma Normal a seguinte estrutura de dados Banco de Dados Professora Mariangela Molina FATEC Mogi das Cruzes Exercícios Pessoa CodigoPessoa Nome Apelido Endereço CEP Cidade Telefone Celular Fax TelefoneComercial Email NomeConjuge AniversarioConjuge Acontecimento CodigoAcontecimento DescricaoAcontecimento Data Observação