Baixe o app para aproveitar ainda mais
Prévia do material em texto
Modelo Relacional Roteiro 1. Banco de dados com uso de UML e modelagem entidade e relacionamento (ER): regras de normalização (primeira forma normal, segunda forma normal, terceira forma normal, forma normal "Boyce-Codd" e quarta e quinta formas normais). Bibliografia • Elmasri, Ramez E e Navathe, Shamkant B. Sistemas de Banco de Dados. Pearson Brasil, 2010. Bibliografia • Heuser, Carlos Alberto. Projeto de banco de dados: Volume 4 da Série Livros didáticos informática UFRGS. Bookman Editora, 2009. Ferramentas CASE Computer-Aided Software Engineering http://www.fabforce.net/dbdesigner4/ http://astah.net/ http://www.modelright.com/index.asp http://erwin.com/ https://www.navicat.com/ http://www.sqlpower.ca/ http://www.casestudio.com/enu/free-modeling-tool.aspx http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html Fases do Projeto de Banco de Dados Fases do Projeto de Banco de Dados Modelo Relacional • O modelo relacional representa o banco de dados como uma coleção de relações. Informalmente, cada relação é semelhante a uma tabela de valores ou, até certo ponto, a um arquivo plano de registros. • Quando uma relação é considerada uma tabela de valores, cada linha na tabela representa uma coleção de valores de dados relacionados. Uma linha representa um fato que normalmente corresponde a uma entidade ou relacionamento do mundo real. Modelo Relacional Modelo Relacional • Chaves – Chave primária – Chave estrangeira – Chave alternativa ou candidata • Domínio e valores vazios • Restrições de integridade – Integridade de domínio – Integridade de vazio – Integridade de chave – Integridade referencial Exemplo de aplicação de banco de dados • EMPRESA – Funcionários, departamentos e projetos – A empresa é organizada em departamentos – Um departamento controla uma série de projetos – Funcionário: armazena o nome, número do Cadastro de Pessoa Física, endereço, salário, sexo (gênero) e data de nascimento de cada funcionário – Registrar os dependentes de cada funcionário Projeto de banco de dados relacional usando o mapeamento ER para relacional Projeto de banco de dados relacional usando o mapeamento ER para relacional Algoritmo de mapeamento ER para relacional • Etapa 1: Mapeamento de tipos de entidade regular • Etapa 2: Mapeamento de tipos de entidade fraca • Etapa 3: Mapeamento dos tipos de relacionamento binários 1:1 • Etapa 4: Mapeamento de tipos de relacionamento binário 1:N • Etapa 5: Mapeamento de tipos de relacionamento binário M:N • Etapa 6: Mapeamento de atributos multivalorados • Etapa 7: Mapeamento de tipos de relacionamento n-ário Etapa 1: Mapeamento de tipos de entidade regular Etapa 1: Mapeamento de tipos de entidade regular • Para cada tipo de entidade regular (forte) E no esquema ER, crie uma relação R que inclua todos os atributos simples de E. • Inclua apenas os atributos de componente simples de um atributo composto. • Escolha um dos atributos-chave de E como chave primária para R. • Se a chave escolhida de E for composta, então o conjunto de atributos simples que a compõem juntos formarão a chave primária de R. • Se várias chaves fossem identificadas para E durante o projeto conceitual, a informação que descreve os atributos que formam cada chave adicional é mantida a fim de especificar chaves secundárias (únicas) da relação R. O conhecimento sobre as chaves também é mantido para fins de indexação e outros tipos de análises. Etapa 1: Mapeamento de tipos de entidade regular Etapa 2: Mapeamento de tipos de entidade fraca Etapa 2: Mapeamento de tipos de entidade fraca • Para cada ripo de entidade fraca F no esquema ER com tipo de entidade proprietária E, crie uma relação R e inclua todos os atributos simples (ou componentes simples dos atributos compostos) de F como atributos de R. • Inclua como atributos de chave estrangeira de R os atributos de chave primária da(s) relação(ões) que corresponde(m) aos tipos de entidade proprietária. • A chave primária de R é a combinação das chaves primárias dos proprietários e a chave parcial do tipo de entidade fraca F, se houver. Etapa 3: Mapeamento dos tipos de relacionamento binários 1:1 Etapa 3: Mapeamento dos tipos de relacionamento binários 1:1 • Para cada tipo de relacionamento binário 1:1 R no esquema ER, identifique as relações S e T que correspondem aos tipos de entidade participantes em R. • Existem três técnicas possíveis: (1) a técnica de chave estrangeira, (2) a técnica de relacionamento mesclado e (3) a técnica de relação de referência cruzada ou relacionamento. Etapa 3: Mapeamento dos tipos de relacionamento binários 1:1 1. Técnica de chave estrangeira • Escolha uma das relações - digamos, S - e inclua como chave estrangeira em S a chave primária de T. • É melhor escolher um tipo de entidade com participação total em R no papel de S. • Inclua todos os atributos simples (ou componentes simples dos atributos compostos) do tipo de relacionamento 1:1 R como atributos de S. ST Etapa 3: Mapeamento dos tipos de relacionamento binários 1:1 2. Técnica de relação mesclada: • Mesclar os dois tipos de entidade e o relacionamento em uma única relação, quando ambas as participações são totais. Etapa 3: Mapeamento dos tipos de relacionamento binários 1:1 3. Técnica de relação de referência cruzada ou relacionamento: • Configurar uma terceira relação R para a finalidade de referência cruzada das chaves primárias das duas relações S e T representando os tipos de entidade. • A chave primária de R será uma das duas chaves estrangeiras, e a outra chave estrangeira será uma chave unique de R. • Desvantagem é ter uma relação extra e exigir uma operação de junção extra ao combinar tuplas relacionadas das tabelas. Etapa 4: Mapeamento de tipos de relacionamento binário 1:N Etapa 4: Mapeamento de tipos de relacionamento binário 1:N • Identifique a relação S que representa o tipo de entidade participante no lado N do tipo de relacionamento. • Inclua como chave estrangeira em S a chave primária da relação T que representa o outro tipo de entidade participante em R. • Inclua quaisquer atributos simples (ou componentes simples dos atributos compostos) do tipo de relacionamento 1:N como atributos de S. Etapa 5: Mapeamento de tipos de relacionamento binário M:N Etapa 5: Mapeamento de tipos de relacionamento binário M:N • Crie uma nova relação S para representar R. • Inclua como atributos de chave estrangeira em S as chaves primárias das relações que representam os tipos de entidade participantes; sua combinação formará a chave primária de S. • Inclua também quaisquer atributos simples do tipo de relacionamento M:N (ou componentes simples dos atributos compostos) como atributos de S. Etapa 6: Mapeamento de atributos multivalorados Etapa 6: Mapeamento de atributos multivalorados • Para cada atributo multivalorado A, crie uma relação R. • Essa relação R incluirá um atributo correspondente a A, mais o atributo da chave primária Ch – como uma chave estrangeira em R - da relação que representa o tipo de entidade ou tipo de relacionamento que tem A como atributo multivalorado. • A chave primária de R é a combinação de A e Ch. • Se o atributo multivalorado for composto, incluímos seus componentes simples. Etapa 7: Mapeamento de tipos de relacionamento n-ário • Para cada ripo de relacionamento n-ário R, onde n > 2, crie uma relação S para representar R. • Inclua como atributos de chave estrangeira em S as chaves primárias das relações que representam os tipos de entidade participantes. • Inclua também quaisquer atributos simples do tipo de relacionamento n-ário (ou componentes simples de atributos compostos) como atributos de S • A chave primária de S normalmente é uma combinação de todas as chaves estrangeiras que referenciam as relações representandoos tipos de entidade participantes. Etapa 7: Mapeamento de tipos de relacionamento n-ário FORNECEDOR PROJETO PECA FORNECIMENTO Esquema de banco de dados relacional EMPRESA Engenharia Reversa (Heuser, 2009) Atividade Prática 1 • Tente mapear o esquema relacional da Figura abaixo em um esquema ER. Isso faz parte de um processo conhecido como engenharia reversa, em que um esquema conceitual é criado para um banco de dados implementado existente. Indique quaisquer suposições que você fizer. Atividade Prática 1 • Tente mapear o esquema relacional da Figura ao lado em um esquema ER. Isso faz parte de um processo conhecido como engenharia reversa, em que um esquema conceitual é criado para um banco de dados implementado existente. Indique quaisquer suposições que você fizer. Atividade Prática 2 • A próxima Figura mostra um esquema ER para um banco de dados que pode ser usado para registrar navios de transporte e seus locais para autoridades marítimas. Mapeie esse esquema para um esquema relacional e especifique todas as chaves primárias e estrangeiras. Regras de Normalização Bibliografia • Elmasri, Ramez E e Navathe, Shamkant B. Sistemas de Banco de Dados. Pearson Brasil, 2010. Bibliografia • Heuser, Carlos Alberto. Projeto de banco de dados: Volume 4 da Série Livros didáticos informática UFRGS. Bookman Editora, 2009. Bibliografia • Puga, Sandra; Goya, Miton; França, Edson. Banco de Dados Implementação em SQL, PL/SQL e Oracle 11g. Pearson, 2016. Capítulo 5 Normalização Normalização • A normalização é um procedimento que examina os atributos de uma entidade. • O objetivo é evitar anomalias que possam ocorrer na inclusão, na exclusão ou na alteração de uma ocorrência específica em uma entidade. Introdução • O conceito de normalização foi introduzido por E. F. Codd em 1970. • Através do processo de normalização pode-se, gradativamente, substituir um conjunto de entidades e relacionamentos por um outro, o qual se apresenta "purificado" em relação às Anomalias de Atualização (inclusão, alteração e exclusão). Introdução • As Anomalias de Atualização podem causar certos problemas, tais como: – redundância de dados desnecessária; – perdas (acidentais) de informação; – inconsistência; – dificuldade na representação de fatos da realidade observada; – grupos repetitivos (atributos multivalorados) de dados; – dependências funcionais totais ou parciais em relação a uma chave concatenada; – dependências transitivas entre atributos. Introdução • Anomalias de Atualização – Que anomalias podem ser encontradas na tabela PEDIDO, mostrada abaixo? ◼ Anomalia de Inclusão: ◼ Ao ser incluído um novo cliente, o mesmo tem que estar (obrigatoriamente) relacionado a uma venda. ◼ Desta forma, toda vez que um cliente efetuar uma compra, o sistema armazenará seus dados novamente (nome-do-cliente, endereco-cliente e cpf-cliente) => REDUNDÂNCIA DE DADOS DESNECESSÁRIA. Introdução • Anomalias de Atualização – Que anomalias podem ser encontradas na tabela PEDIDO, mostrada abaixo? ◼ Anomalia de Exclusão: ◼ Ao ser excluído um cliente, os dados referentes as suas compras serão perdidos, ocasionando PERDA DE INFORMAÇÃO. ◼ Desta forma, consultas importantes não poderão ser efetuadas. ◼ Exemplo: ◼ No total, quantos litros de álcool foram vendidos no mês? ◼ Quantos quilogramas de cimento foram vendidos em 20 de março de 2008? Introdução • Anomalias de Atualização – Que anomalias podem ser encontradas na tabela PEDIDO, mostrada abaixo? ◼ Anomalia de Alteração: ◼ Caso o endereço de um cliente seja alterado, essa alteração tem de ser feita em várias linhas da tabela, podendo provocar INCONSISTÊNCIA na base de dados, isto é, numa determinada linha, o cliente poderá aparecer em um endereço e, em outra linha, em outro. Introdução • Normalização de relações é portanto uma técnica que permite depurar um projeto de banco de dados, através da identificação de inconsistências (informações em duplicidade, dependências funcionais mal resolvidas, etc) • À medida que um conjunto de relações passa para uma forma normal, vamos construindo um banco de dados mais confiável • O objetivo da normalização não é eliminar todas as inconsistências, e sim controlá-las Normalização • Formas Normais (FN): – Primeira Forma Normal (1FN). – Segunda Forma Normal (2FN). – Terceira Forma Normal (3FN). • Posteriormente, foram acrescidas as: – Forma Normal Boyce-Codd (FNBC). – Quarta Forma Normal (4FN). – Quinta Forma Normal (5FN). Introdução 1ª Forma Normal (1 FN) • Uma tabela encontra-se na 1FN se todos os seus atributos estiverem definidos em domínios que contenham apenas valores atômicos e monovalorados. • Os domínios devem ser formados por valores elementares e não por conjuntos de valores. 1ª Forma Normal (1 FN) • Imaginemos uma tabela destinada a registrar a informação sobre os alunos e as disciplinas em que estes estão matriculados ALUNOS (CodAluno, Nome, Morada, Disciplinas) 1ª Forma Normal (1 FN) Esta tabela não obedece à primeira forma normal (1FN), uma vez que o atributo Disciplinas admite conjuntos de valores. Atributos Compostos e Multivalorados. 1ª Forma Normal (1 FN) • Soluções: – Quando a quantidade de valores é pequena e conhecida a priori: • Substitui o atributo multivalorado por um conjunto de atributos de mesmo domínio, cada um representando a ocorrência de um valor – Exemplo: Cada aluno só pode pagar duas disciplinas 1ª Forma Normal (1 FN) • Soluções – Quando a quantidade de valores é muito grande, variável ou desconhecida. • Retira-se da relação o atributo multivalorado, e cria- se uma nova relação que tem o mesmo conjunto de atributos chave, mais o atributo multivalorado como chave, porém tomado como monovalorado 1ª Forma Normal (1 FN) • Resumindo... – A primeira forma normal impõe uma diretiva básica de organização e projeto de tabelas • Eliminar colunas duplicadas dentro da mesma tabela Anomalias Está na 1FN? Anomalias • A tabela ALUNOS agora está na 1FN, pois todos os atributos contêm apenas valores elementares . • Apresenta, no entanto, grande redundância de informação, que se reflete na repetição dos identificadores dos nomes e moradas dos alunos. • Além desse inconveniente, podem apontar-se outros. Anomalias • Problemas de atualização - se a morada de um aluno for alterada, essa alteração tem de ser feita em várias linhas da tabela, sob o risco de gerar incoerências na Base de Dados, isto é, numa determinada linha o aluno poderá aparecer uma morada e noutra linha outra; Anomalias • Problemas de eliminação - porque para anular a matrícula de um aluno implica ter de eliminar várias linhas da tabela, e mesmo perder a informação do aluno, tal como NÚMERO, NOME e MORADA. Dependência Funcional • Um atributo ou conjunto de atributos é determinante de outros atributos quando os identifica de modo único – Ex: • codAluno • codDisciplina ALUNOS (CodAluno, Nome, Morada, CodDisciplina, Disciplina) • Os atributos identificados de modo único por um outro atributo, ou conjunto de atributos, são funcionalmente dependentes deste último – Ex: • NomeAluno e Morada_aluno • Disciplina Dependência Funcional • Exemplo: ALUNOS (CodAluno, Nome, Morada, CodDisciplina, Disciplina) • Temos: – Nome e Morada são dependentes de CodAluno – Disciplina é dependente de CodDisciplina • Podemos ainda ler da seguinte forma: – Com um código do aluno, podemos encontrar seu nome e sua morada – Com um código da disciplina, podemos encontrar sua disciplina. 2ª Forma Normal (2 FN) • Uma relação está na 2FN quando duas condições são satisfeitas: – a relação está na primeira forma normal; – todos os atributos não-chave (ou atributos primos) dependem funcionalmente de toda a chave primária 2ª Forma Normal (2 FN) • Observe a relação abaixo: BOLETIM = {matricula-aluno, codigo-materia, numero-prova, nota, data-da- prova, nome-aluno, endereço-aluno, nome-materia}Qual a chave-primária? • Fazendo a análise da dependência funcional de cada atributo primo, chegamos às seguintes dependências funcionais: – matricula-aluno, codigo-materia, numero-prova -> nota • Nota depende de matricula-aluno, codigo-materia, numero-prova – codigo-materia, numero-prova -> data-da-prova • Data da prova depende de... – matricula-aluno -> nome-aluno, endereço-aluno • ? – codigo-materia -> nome-materia • ? 2ª Forma Normal (2 FN) • Concluímos então que na tabela Boletim (mostrada anteriormente) apenas o atributo primo (ou não chave) nota depende totalmente de toda chave primária. • Desta forma, os atributos data-da-prova, nome-aluno, endereço-aluno, nome-materia não respeitam a 2FN. • Sendo assim, para que toda a relação seja passada para a 2FN, deve-se criar novas relações, agrupando os atributos de acordo com suas dependências funcionais: BOLETIM = {matricula-aluno, codigo-materia, numero-prova, nota} PROVA = {codigo-materia, numero-prova, data-da-prova} ALUNO = {matricula-aluno, nome-aluno, endereço-aluno} MATERIA = {codigo-materia, nome-materia} Dependências Transitivas • Dependência Transitiva – Significa que um atributo não depende diretamente do atributo determinante e sim de algum outro atributo, que por sua vez depende do determinante. – Dessa forma, todo atributo não-chave deve ser analisado em relação a outros atributos não-chave. ◼ Y (tipo) depende de X (no- avião) ◼ Z (capacidade) depende de Y (tipo) ◼ Logo, Z (capacidade) também depende de X (no- avião) Dependências Transitivas • CPF -> código-cidade – Código-cidade depende de CPF • código-cidade -> nome-cidade – Nome-cidade depende de Código-cidade Logo CPF -> nome-cidade – Logo, Nome-cidade depende (transitivamente) de CPF Você pode ler o exemplo acima também da seguinte maneira: • Se com um número de CPF eu encontro o código da cidade de uma pessoa, e com o código da cidade eu encontro o nome da cidade, então com o número do CPF eu posso encontrar o nome da cidade 3ª Forma Normal (3 FN) • Uma relação está na 3FN quando duas condições forem satisfeitas: • a relação está na segunda forma normal • todos os atributos primos dependem não transitivamente de toda a chave primária 3ª Forma Normal (3 FN) • Observe a relação abaixo: PEDIDO = {numero-pedido, codigo-cliente, data-pedido, nome-cliente, codigo-cidade-cliente, nome-cidade-cliente} • Fazendo a análise da dependência funcional de cada atributo primo, chegamos às seguintes dependências funcionais: numero-pedido -> codigo-cliente numero-pedido -> data-pedido codigo-cliente -> nome-cliente codigo-cliente -> codigo-cidade-cliente codigo-cidade-cliente -> nome-cidade-cliente 3ª Forma Normal (3 FN) • Isto é dependência transitiva, devemos resolver inicialmente as dependências mais simples, criando uma nova relação onde codigo-cliente é a chave, o codigo-cliente continuará na relação PEDIDO como atributo primo, porém, os atributos que dependem dele devem ser transferidos para a nova relação: PEDIDO = {numero-pedido, codigo-cliente, data-pedido} CLIENTE = {codigo-cliente, nome-cliente, codigo-cidade-cliente, nome- cidade-cliente} • Assim, as dependências transitivas da relação PEDIDO são eliminadas • Porém, ... 3ª Forma Normal (3 FN) • Agora devemos analisar a nova relação CLIENTE: codigo-cliente -> codigo-cidade-cliente -> nome-cidade-cliente • Observem que o nome-cidade-cliente continua com uma dependência transitiva, vamos resolvê-la da mesma maneira : PEDIDO = {numero-pedido, codigo-cliente, data-pedido} CLIENTE = {codigo-cliente, nome-cliente, codigo-cidade-cliente} CIDADE = {codigo-cidade-cliente, nome-cidade-cliente} Tabelas normalizadas na 3FN 3ª Forma Normal (3 FN) • Resumindo.... Convertendo da 2FN para a 3FN 1) Verificar se existem atributos que sejam dependentes transitivos (isto é, se a depende de b, e b depende de c, por transitividade, a também depende de c) de outros que não pertencem à chave primária, sendo ela concatenada ou não, bem como atributos que sejam dependentes de cálculo realizado a partir de outros atributos (ex: idade, tempo_de_casa) 2) Destacar os atributos com dependência transitiva, gerando uma nova tabela com este atributo e cuja chave primária é o atributo que originou a dependência. 3) Eliminar os atributos obtidos através de cálculos realizados a partir de outros atributos. Considerações Finais Normalizar evita introduzir inconsistências quando se alteram relações; porém obriga a execução de custosas operações de junção para a consulta de informações. Considerações Finais A decisão deve ser tomada considerando-se o compromisso entre se garantir a eliminação de inconsistências na base, e eficiência de acesso. Mas, e aí?! Normalizar ou não Normalizar? Eis a questão! © 2013 Pearson. Todos os direitos reservados.slide 167 Forma Normal Boyce-Codd (FNBC) • A FNBC é uma extensão da 3FN, que não resolvia certas anomalias presentes na informação contida em uma entidade. • Toda entidade na FNBC também está na 3FN, no entanto, o inverso não é necessariamente verdadeiro. • Veja a seguir a aplicação da FNBC na entidade ENDERECO. © 2013 Pearson. Todos os direitos reservados.slide 168 Forma Normal Boyce-Codd (FNBC) © 2013 Pearson. Todos os direitos reservados.slide 169 Forma Normal Boyce-Codd (FNBC) • Normalização da entidade ENDERECO © 2013 Pearson. Todos os direitos reservados.slide 170 Quarta Forma Normal (4FN) • A Quarta Forma Normal (4FN) é aplicada para evitar a redundância em situações de ocorrência de fatos multivalorados. • Uma relação está na 4FN quando estiver em concordância com a FNBC e não existirem dependências multivaloradas. • A representação da entidade CORRIDA na 4FN pode ser vista na figura a seguir. © 2013 Pearson. Todos os direitos reservados.slide 171 Quarta Forma Normal (4FN) © 2013 Pearson. Todos os direitos reservados.slide 172 Quinta Forma Normal (5FN) • A Quinta Forma Normal (5FN) é aplicada para impedir que a decomposição de entidades gere inconsistências originadas da junção entre entidades. • Deve ser aplicada sempre que existirem relacionamentos ternários ou n-ários. • Uma entidade está na 5FN quando estiver na 4FN e o conteúdo de cada ocorrência não puder ser reconstruído a partir de ocorrências menores. ATÉ O PRÓXIMO ENCONTRO!
Compartilhar