Baixe o app para aproveitar ainda mais
Prévia do material em texto
Banco de Dados Apresentação da Disciplina Prof Melo Apresentação • Técnico em Desenvolvimento de Sistemas - Ibratec, Recife-PE • Bacharel em Sistemas de Informação – FIR, Recife-PE • Especialista em Docência no Ensino Superior – Faculdade Maurício de Nassau, Recife-PE • Mestre em Ciência da Computação – UFPE/CIN, Recife-PE • Currículo Lattes http://lattes.cnpq.br/0759508594425296) • Homepage https://sites.google.com/site/hildebertomelo/ Disciplinas Lecionadas • Desenvolvimento de Aplicações Desktop • Programação Orientada a Objetos • Estrutura de Dados • Tecnologia da Informação & Sociedade • Sistemas Operacionais • Sistemas Distribuídos • Introdução a Informática • Lógica de Programação • Informática Aplicada a Saúde • Banco de Dados • Projeto de Banco de Dados • Análise de Projetos Orientado a Objetos • Programação Cliente Servidor • Linguagens de Programação: C, C#, Pascal, PHP, ASP, Delphi, Java, JavaScript • Programação WEB Roteiro • Ementa • Competências • Conteúdo • Plano de Aula • Bibliografia Ementa • Introdução a Banco de Dados (Conceito e objetivos). • Modelo Relacional. • Restrições de Integridade. • Modelagem conceitual de dados com entidades, relacionamentos e atributos. • Modelos lógicos de dados e sistemas correlatos. • Normalização de dados. • Práticas: metodologias e simulações. Competências • Conhecer Ambientes de Banco de Dados. • Interpretar Modelos do Mundo Real. • Utilizar técnicas e ferramentas para a Solução de Problemas do Mundo Real. • Conhecer modelos e Tecnologias de Banco de dados (hierárquico, rede e relacional). • Conhecer novas tecnologias de Banco de Dados (Relacional, Orientado a Objeto, Textual). Utilizar Técnicas e Ferramentas de Modelagem. • Normalizar dados. Conhecer de Banco de Dados Relacionais. • Ter os conhecimentos básicos de Documentação Técnica. • Desenvolver, nos laboratórios da instituição ou nas empresas conveniadas, um projeto, referente aos conteúdos estudados na disciplina. 6 Avaliação - Conteúdo • UNIDADE I – Prova teórica (10,0 dez pontos) • Introdução aos bancos de dados. • Conceitos de sistemas de banco de dados. • Modelo Entidade-Relacionamento – Modelagem – Ferramentas de modelagem • UNIDADE II – Prova Colegiada Teórica (10,0 dez pontos) • Assunto da UNIDADE I • Modelo Relacional – Projeto de bancos de dados relacionais – Implementação de bancos de dados relacionais. • Linguagem SQL. • Tecnologias emergentes para bancos de dados. 7 Avaliação - Conteúdo • Segunda Chamada e Final – Prova teórica (10,0 dez pontos) • Introdução aos bancos de dados. • Conceitos de sistemas de banco de dados. • Modelo Entidade-Relacionamento – Modelagem – Ferramentas de modelagem • Modelo Relacional – Projeto de bancos de dados relacionais – Implementação de bancos de dados relacionais. • Linguagem SQL. • Tecnologias emergentes para bancos de dados. 8 Horário Horário / Dias Seg Ter Qua Qui Sex 1-2: 07:50-09:20 X Intervalo 3-4: 09:30 – 11:00 X Horário / Dias Seg Ter Qua Qui Sex 1-2: 18:40-20:10 X Intervalo 3-4: 20:20– 21:50 X Plano de Aula • Verificar no Clube Nabuco Perguntas 12 Referências • BIBLIOGRAFIA BÁSICA – ELMASRI, Ramez . NAVATHE, Shamkant B. SISTEMAS DE BANCO DE DADOS 4ed. PEARSON ADDISON WESLEY 2008 – COUGO, Paulo Sérgio MODELAGEM CONCEITUAL E PROJETO DE BANCOS DE DADOS. CAMPUS 1997 – GUIMARÃES, Célio Cardoso FUNDAMENTOS DE BANCO DE DADOS: MODELAGEM, PROJETO E LINGUAGEM SQL 1ed. UNICAMP 2008 13 Referências • BIBLIOGRAFIA COMPLEMENTAR – RANGEL, Alexandre. MYSQL - PROJETO, MODELAGEM E DESENVOLVIMENTO DE BANCO DE DADOS. ALTA BOOKS. 2004 – HEUSER, Carlos Alberto. PROJETO DE BANCO DE DADOS. 6ed. BOOKMAN 2009. – KORTH, Henry F. SUDARSHAN, S .SILBERSCHATZ, Abraham. SISTEMA DE BANCO DE DADOS 5ed. CAMPUS 2006. – MACHADO, Felipe. ABREU, Mauricio. PROJETO DE BANCO DE DADOS: UMA VISÃO PRÁTICA 15ed. ÉRICA 2007. – GRAVES, Mark. PROJETOS DE BANCO DE DADOS COM XML PEARSON EDUCATION 2003 Banco de Dados Introdução a Banco de Dados Prof Melo Dado x Informação – Dado : É a matéria prima para a geração da informação. É a entrada do sistema. – Informação : É o produto final, ou seja, são os dados organizados ou classificados (processados), conforme especificado pelo objetivo, que geram uma informação útil para alguém. É a saída do sistema. • Quando falamos em processo de transformação de dados em informações utilizando o computador para isso, dizemos que é um Processamento de Dados. De que forma a Informação passa a ser Valiosa ? Relevante Precisa Completa Pontual Econômica Acessível Confiável Flexível Segura Simples Verificável Dado x Informação • Dado: É um elemento/registro da informação. • Informação: É o significado dos dados. • O dado é fato, ele só se torna informação quando visto dentro de um contexto (transmitir algum significado às pessoas). • A informação acrescenta algo ao conhecimento da realidade de forma tal que possa ser interpretado pelas pessoas. • Informação = Dados + Contexto • O tratamento dos DADOS dá origem a vários tipos de INFORMAÇÕES Histórico • Início da computação: – dados guardados em arquivos de texto • Problemas nesse modelo: – redundância não-controlada de dados – aplicações devem se preocupar com a forma de armazenamento dos dados • Início dos anos 60: primeiros bancos de dados Evolução • Sistemas de arquivos: – Principal característica é a replicação e isolamento de dados (ilhas de informações) • Aplicações eram escritas para um determinado arquivo – Para cada nova informação criava-se um novo arquivo, mesmo esta existindo em outros arquivos (redundância descontrolada) • Arquivos possuíam formatos diferentes – EX: Sexo = M ou F e Sexo = 0 ou 1 – Nome CHAR (50) e Nome CHAR (40) Evolução Evolução • Já que arquivos e aplicações são criados e mantidos por diferentes programadores, em geral, durante longos períodos de tempo, é comum que os arquivos possuam formatos diferentes e os programas sejam escritos em diversas linguagens de programação. Ainda mais, a mesma informação pode ser repetida em diversos lugares (arquivos). Dificuldade no Acesso a Dados • Já que arquivos e aplicações são criados e mantidos por diferentes programadores, em geral, durante longos períodos de tempo, é comum que os arquivos possuam formatos diferentes e os programas sejam escritos em diversas linguagens de programação. Ainda mais, a mesma informação pode ser repetida em diversos lugares (arquivos). Dificuldade no Acesso a Dados • Questão: – Uma empresa precisa dos nomes de todos os clientes que residam no CEP 78733. – Esta solicitação foi prevista no projeto do sistema ? • H1 - SIM ® OK ! • H2 - NÃO (mas existe uma aplicação para gerar a relação de todos os clientes e seus endereços) – S - separar manualmente os clientes • H3 - NÃO (e não existe nada semelhante) – S - Escrever o programa necessário. • H2 e H3 são, obviamente, insatisfatórias – ...Dias depois é necessário obter quais clientes com saldo superior a R$6.000,00. Isolamento dos Dados • Como os dados estão dispersos em vários arquivos, e estes arquivos podem possuir diferentes formatos, é difícil escrever novas aplicações para recuperação apropriada dos dados. Banco de Dados • Bancos de dados são conjuntos de dados integrados que tempor objetivo atender a uma comunidade de usuários • Redundância controlada • Dados armazenados de forma mais consistente • Gerenciamento facilitado Contexto da disciplina O que é um Banco de Dados (BD)? É uma coleção de dados relacionados e armazenados em algum dispositivo. Porquê estudar BD? • Administrador de Banco de Dados (DBA) • Administradores de Rede • Desenvolvedores de Aplicações • Administradores do SO • Usuários de Banco de Dados Porquê estudar BD? Administrador de Banco de Dados (DBA) – Cada banco de dados requer pelo menos um DBA – A tarefa de um Administrador de Banco de Dados (DBA) pode ser exercida por uma única pessoa ou por um grupo de pessoas, dependendo do tamanho da empresa – Principal meta de um DBA: configurar o manter o BD de modo a torná-lo robusto, seguro e rápido nos serviços prestados Porquê estudar BD? Administradores de Rede – responsáveis por administrar produtos de rede (exemplo: Oracle Net). – definir conectividade (protocolos, segurança, portas...) Desenvolvedores de Aplicação – projetar e desenvolver as aplicações – definir as necessidades de memória de uma aplicação – especificar modificações em um esquema de BD – relacionar-se com o DBA – ajustar as aplicações durante o seu desenv. Porquê estudar BD? Administradores do SO – responsáveis por administrar sistemas operacionais (exemplo: Windows 2000, UNIX). Usuários de Banco de Dados – inserir, modificar e remover dados, onde permitido – gerar relatórios Conceitos de Banco de Dados Motivação: A importância da informação para a tomada de decisões nas organizações tem impulsionado o desenvolvimento dos sistemas de processamento de informações. • Informalmente: – Sistema computadorizado de armazenamento de registros. – O banco de dados, pode ser visto como o equivalente eletrônico de um armário de arquivamento. – É um repositório ou recipiente para uma coleção de arquivos de dados computadorizados. O que é um Banco de Dados (BD)? É uma coleção de dados relacionados e armazenados em algum dispositivo Possibilitar ao Usuário: • Acrescentar novos arquivos, vazios, ao banco de dados. • Inserir novos dados em arquivos existentes. • Buscar dados de arquivos existentes. • Alterar dados em arquivos existentes. • Eliminar dados de arquivos existentes. • Sistema de Gerenciamento de Bancos de Dados SGBD (Database Management System – DBMS) – Banco de Dados (BD) = Coleção de dados inter- relacionados – DBMS = Módulo que proporciona a interface entre dados armazenados no banco de dados e os programas de aplicação e consultas submetidas ao sistema • retirar e armazenar informações no BD • envolve diversos profissionais Um sistema cujo propósito geral é armazenar informações e permitir ao usuário buscar e atualizar essas informações quando solicitado. Banco de Dados Sistema de Gerenciamento de Bancos de Dados Usuários Finais Programas de Aplicação Quatro componentes: Dados, Hardware, software e usuários. • Dados: – O que realmente está armazenado no banco de dados. • Informações: – Referência ao significado dos dados para um determinado usuário. • Dados persistentes: – Termo usado para os dados armazenado em um banco de dados. – Diferente de certos tipos de dados mais efêmeros: • Dados de entrada, saída, resultados intermediários Objetivos de um Sistema de Bancos de Dados – Isolar os usuários dos detalhes mais internos do banco de dados (abstração de dados). – Prover independência de dados às aplicações (estrutura física de armazenamento e à estratégia de acesso). SGBD é um software com recursos específicos para facilitar a manipulação das informações dos bancos de dados e o desenvolvimento de programas aplicativos. SGBD - É o software que está entre o banco de dados físico (isto é, os dados armazenados) e os usuários Banco de Dados Usuários de Aplicação Programadores de Aplicação DBA SGBD Aplicações Vantagens – rapidez na manipulação e no acesso à informação, – redução do esforço humano (desenvolvimento e utilização), – disponibilização da informação no tempo necessário, – controle integrado de informações distribuídas fisicamente, – redução de redundância e de inconsistência de informações, – compartilhamento de dados, Vantagens • aplicação automática de restrições de segurança. • redução de problemas de integridade. Requisitos para SGBDs Requisito Descrição Facilidade de uso a modelagem do banco de dados deve refletir a realidade das aplicações, e o acesso aos dados deve ser feito de forma simples Correção os dados armazenados no banco de dados devem refletir um estado correto da realidade modelada Facilidade de manutenção alterações na forma de armazenamento dos dados devem afetar as aplicações o mínimo possível Confiabilidade atualizações não devem ser perdidas e não devem interferir umas com as outras Segurança o acesso aos dados deve ser controlado de acordo com os direitos definidos para cada aplicação ou usuário Desempenho o tempo de acesso aos dados deve ser compatível com a complexidade da consulta 1-Nível Externo 2-Nível Lógico 3-Nível Interno Visão Visão Conceitual Físico 1.Descreve parte do BD por meio de estruturas mais simples que no nível conceitual, mas alguma complexidade perdura devido ao tamanho do BD. 2.Descreve quais dados estão armazenados de fato e as relações entre eles. Aqui o BD é descrito totalmente em termos de estruturas relativamente simples. 3.Descreve como os dados realmente estão armazenados, onde complexas estruturas são descritas em detalhes. Externo (PL/I) DCL 1 EMPP, 2 EMP# CHAR(6), 2 SAL FIXED BIN(31); Externo (COBOL) 01 EMPC 02 EMPNO PIC X(6) 02 DEPTNO PIC X(4) Conceitual EMPREGADO CODIGO_FUNCIONAL CARACTERE (6) NUMERO_DEPARTAMENTO NUMERO (3) SALARIO NUMERO (5) Interno EMP_ARMAZENADO BYTE=20 PREFIXO TYPE=BYTE(6), OFFSET=0 EMP# TYPE=BYTE(6), OFFSET=6, 3 N Í V E I S Abstração de Dados: • Omite certos detalhes de como os dados são armazenados e mantidos. Nível Físico – nível mais baixo de abstração – se descreve como os dados são armazenados • estruturas complexas de baixo nível são descritas em detalhe Nível Conceitual – nível intermediário de abstração – descreve quais dados são armazenados no BD e quais relacionamentos existem entre os dados – descreve inteiramente o BD com um pequeno número de estruturas relativamente simples • que podemrefletir em estruturas complexas no nível físico – usados pelos administradores do banco de dados • que devem decidir qual informação deve ser mantida no BD Nível Visão –nível conceitual utiliza estruturas mais simples •mas há ainda um tipo de complexidade resultante do grande tamanho do BD •muitos usuários não estão preocupados com toda esta informação –necessitam apenas uma parte do BD Independência de Dados Capacidade de modificar a definição dos esquemas em determinado nível, sem afetar o esquema do nível superior. Independência de Dados Física: modificar o esquema físico sem alterar qualquer aplicação. Associada a desempenho. Independência de Dados Lógica: modificar o esquema lógico sem alterar qualquer aplicação 1960 1970 1980 1990 2000 Sistemas de Gerenciamento de Arquivos (ISAM e VSAM) Gerenciadores de BD Hierárquicos - IMS Gerenciadores de BD em Rede (CODASYL) IDMS Gerenciadores de Bancos de Dados Relacionais (Oracle, DB2, SQL Server) Modelos Lógicos de Dados Conjunto de ferramentas conceituais para a descrição dos dados, dos relacionamentos entre os mesmos e das restrições de consistência e integridade. . Dividem-se em: – baseados em objetos, – baseados em registros Modelos lógicos baseados em objetos Descrição dos dados nos níveis conceitual e de visões de usuários. Exemplos: entidade-relacionamento, orientado a objetos. No modelo orientado a objetos, código executável é parte integrante do modelo de dados. Modelos lógicos baseados em registros – descrição dos dados nos níveis conceitual e de visões de usuários; – o banco de dados é estruturado em registros de formatos fixos, de diversos tipos; – cada tipo de registro tem sua coleção de atributos; – há linguagens para expressar consultas e atualizações no banco de dados. Exemplos: relacional, rede, hierárquico. No modelo relacional, dados e relacionamentos entre dados são representados por tabelas, cada uma com suas colunas específicas. Exemplo das Informações em um Banco de Dados Modelo Hierárquico • Os dados e relacionamentos são representados por registros e ligações, respectivamente. •Os registros são organizados como coleções arbitrárias de árvores. Modelo de Rede Os dados são representados por coleções de registros e os relacionamentos por elos. Modelo Relacional Modelo Relacional • Tanto os dados quanto os relacionamentos são representados por tabelas. • Possui fundamento matemático sólido. • Prescinde de estruturas de índice eficientes e hardware adequado para alcançar desempenho viável em situações práticas. Diferença entre Modelos •O modelo relacional difere dos modelos hierárquico e em rede por não utilizar nem ponteiros nem links. •Relaciona os registros por valores próprios a eles. •Como não é necessário o uso de ponteiros, houve a possibilidade do desenvolvimento de fundamentos matemáticos para sua definição. Linguagens de Definição e Manipulação de Dados Esquema do Banco de Dados É o “projeto geral” (estrutura) do banco de dados. – não muda com freqüência; – há um esquema para cada nível de abstração e um subesquema para cada visão de usuário. Linguagem de Definição de Dados ( DDL) Permite especificar o esquema do banco de dados, através de um conjunto de definições de dados. – A compilação dos comandos em DDL é armazenada no dicionário (ou diretório) de dados. metadados Manipulação de dados – recuperação da informação armazenada, – inserção de novas informações, – exclusão de informações, – modificação de dados armazenados. Linguagem de Manipulação de Dados ( DML) Permite ao usuário acessar ou manipular os dados, vendo-os da forma como são definidos no nível de abstração mais alto do modelo de dados utilizado. – Uma consulta (“ query”) é um comando que requisita uma recuperação de informação. – A parte de uma DML que envolve recuperação de informação é chamada linguagem de consulta. Perguntas... Bibliografia • DATE, Christopher J., DARWEN, Hugh. Foundations for future database systems. New York: Addison Wesley, 2000. • ELMASRI, Rames, NAVATHE, Shamkant B. Sistema de Banco de Dados – Fundamentos e Aplicações. 3ª Ed., Rio de Janeiro: LTC, 2000. • MANZANO, José Augusto N. G. Estudo Dirigido de SQL. São Paulo: Érica, 2002. Bibliografia Complementar • OPPEL, Andy. Banco de Dados Desmistificado. Rio de Janeiro: Alta Books, 2004. • COSTA, Rogério Luís de C. SQL - Guia Prático. São Paulo: Brasport, 2004. • DATE, Christopher J. Introdução a Sistemas de Banco de Dados. Rio de Janeiro: Campus, 2004. • DICOVERY. Princípios de Banco de Dados. São Paulo: Senac, 1999. • ELMASRI, R., NAVATHE, S. B. Fundamentals of Database Systems. Califórnia: Benjamin/ Cummings, 2000. Banco de Dados Modelo Entidade Relacionamento Prof Melo 65 Projeto de Banco de Dados •Processo de Análise dos Requisitos Organizacionais •Desenvolvimento de um Modelo Conceitual que represente esses requisitos •Realização do Modelo Conceitual num Modelo Lógico e Modelo Físico, utilizando uma tecnologia de Base de Dados 66 Projeto de Banco de Dados Análise de requisitos Construção do modelo Conceitual Construção do modelo físico necessidades de informação modelo de dados da empresa características hw/so necessidades de processamento características do SGBD especificação de requisitos estrutura Conceitual da informação estrutura lógica dos dados e aplicações estrutura física dos dados Construção do modelo lógico 67 Projeto de Banco de Dados 1 - Análise de Requisitos – identificar e descrever os dados e processos pretendidos pela organização, no que diz respeito a determinado subsistema de informação em estudo 68 2 - Construção do Modelo Conceitual sintetizar num modelo global as diferentes necessidades dos vários tipos de usuários descrever entidades, atributos e associações, independentemente da tecnologia de BD que venha a ser utilizada 3 - Construção do Modelo lógico transformar o modelo Conceitual num modelo lógico dependente da tecnologia de Bases de Dados a utilizar 4 - Construção do modelo físico definir estruturas físicas de dados que sejam mais adequadas num ambiente informático particular estabelecer estratégias de segurança, recuperação e backup 69 Modelo Entidade-Relacionamento O grande objetivo de um sistema de BD é oferecer uma visão “abstrata” dos dados aos usuários. Os detalhes referentes a forma como estes dados estão armazenados e mantidos não interessa aos usuários, mas a disponibilidade eficiente destes dados é que são fundamentais. Mundo real modelo Representação em computadores 70 Modelo Entidade-Relacionamento • O conceito de abstração está associado à característica de se observar somente os aspectos de interesse, sem se preocupar com maiores detalhes envolvidos. • No contexto de abstração de dados, um banco de dados pode ser visto sem se considerar a forma como os dados estão armazenados fisicamente. 71 Modelo Entidade-Relacionamento Coleta e Análise de Requisitos Projeto Conceitual Projeto Lógico Projeto Físico Mini-mundo Requisitos de BD Esquema conceitual Esquema lógico Esquema interno 72 Modelo Entidade-Relacionamento Representação semântica das estruturas de dados mantidas num banco de dados Foi proposto por Peter Chen em 1976 Possui várias notações: - Relacionamentos como objetos do Modelo (Chen) - Relacionamentos apenas como simples ligações (Codd, Martin) 73 ENTIDADE –Uma entidade é tudo aquilo sobre o qual se deseja manter informações. –Podendo representar: •objetos concretos: pessoas, livros, carros, … •conceitos abstratos: empresas, eventos, embarques, … –Possui propriedades que a distingue de outras entidades. –É um subconjunto de objetos (instâncias) que: •desempenha o mesmo papel semântico •possui os mesmos tipos de propriedades (atributos) 74 • Exemplos: • Conjunto de todas as contas correntes de um banco • Conjunto de todos os empregados de uma empresa • Conjunto de todos os filmes de um produtor • Representação de entidades no diagrama E-R (entidades e relacionamentos): Empregado Aluno Empréstimo Designa o conjunto de todos os empregados sobre as quais se deseja manter informações no banco de dados 75 Modelo Entidade-Relacionamento Entidade: EMPREGADO Descrição: Pessoa que mantém vínculo empregatício com a Empresa através de um contrato de trabalho de acordo com a legislação trabalhista Entidade: ENCOMENDA Descrição: Instrumento contratual de emissão unilateral pela empresa e aceitação, expressa ou tácita, pelo fornecedor do material. Entidades devem ser descritas num Dicionário de Dados 76 São as propriedades que caracterizam ou descrevem uma entidade ou um relacionamento. Ex.: A entidade CARRO poderia ter os seguintes atributos: Placa, fabricante, modelo, ano de fabricação, cor, preço O relacionamento TRABALHA entre EMPREGADO e PROJETO pode ter o atributo: horasTrabalhadas. ATRIBUTOS Cada atributo possui um domínio que identifica o conjunto de valores permitidos para aquele atributo. Ex.: nome: domínio string(20) salário: domínio numérico 77 Modelo Entidade-Relacionamento Atributos devem também ser descritos no Dicionário de Dados: Entidade: EMPREGADO Atributo: Data de Admissão Descrição: data na qual foi assinado o contrato de trabalho entre a empresa e o empregado Domínio: data posterior a 03/01/78 (data de criação da empresa) e a data de nascimento do empregado 78 Modelo Entidade-Relacionamento – Simples: é atômico. Ex. Idade: numérico Nome: cadeia de caracteres – Composto: contém sub-atributos que compõem o atributo. Ex.: Endereço (rua, número, bairro, CEP, cidade, ) ATRIBUTOS 79 – Simplesmente valorados: têm um único valor para uma instância de uma entidade. Ex.: PESSOA: Idade – Multivalorados: possuem vários valores numa instância de uma entidade. PESSOA:Endereço (rua, bairro, cidade, uf, etc.) – Atributos derivados: podem ser determinados a partir de outros atributos/entidades. Ex. Idade e dataAniversário ATRIBUTOS 80 Modelo Entidade-Relacionamento Instância: objeto de uma entidade com suas respectivas propriedades que é distinguível dos outros objetos. Ex.: A entidade Empregado poderia ter a seguinte instância: “Maria dos Anjos, 31 anos, Secretária, Solteira, R$ 800,00” 81 Modelo Entidade-Relacionamento RELACIONAMENTOS –São funções que mapeiam um conjunto de instâncias de uma entidade em um outro conjunto de instâncias de outra entidade (ou da mesma entidade: “auto relacionamento”). Em outras palavras, são associações entre diversas entidades. –Ex.: “Um empregado trabalha num projeto” “Um cliente possui conta bancária” “Um filme possui vários atores” 82 Modelo Entidade-Relacionamento Empregado Projeto trabalh a matricula nome salário horas 1..N 1..N 83 Modelo Entidade-Relacionamento Pessoa Departamento Lotação Relacionamento Um relacionamento é, portanto, um conjunto de associações entre entidades. Representado graficamente por um losango 84 Modelo Entidade-Relacionamento A figura anterior expressa que o BD mantém informações sobre: Um conjunto de objetos classificados como pessoas (entidade Pessoa). Um conjunto de objetos classificados como departamentos (entidade Departamento). Um conjunto de associações, cada uma ligando um departamento a uma pessoa (relacionamento Lotação). 85 Modelo Entidade-Relacionamento 1. Quando nos referirmos a associações particulares dentro de um conjunto, vamos nos referir a ocorrências ou instâncias de relacionamentos: oNo caso do relacionamento Lotação, uma ocorrência seria um par específico, formado por uma determinada ocorrência da entidade Pessoa e por uma determinada ocorrência da entidade Departamento. p1 p2 p4 p3 p7 p6 p5 p8 p1d1 p2d1 p4d2 p5d3 d1 d2 d3 86 Auto-Relacionamentos • Não necessariamente um relacionamento associa entidades diferentes. • Auto-relacionamento: – Um relacionamento entre ocorrências de uma mesma entidade. Pessoa Casamento marido esposa p1 p2 p4 p3 p7 p6 p5 p8 p1p3 p6p8 marido marido esposa esposa 87 CARDINALIDADE de Relacionamentos • Propriedade importante de um relacionamento: – Quantas ocorrências de uma entidade podem estar associadas a uma determinada ocorrência através do relacionamento (Cardinalidade). João Luiz Maria Afonso José Pedro A B C PESSOA DEPARTAMENTO 88 Modelo Entidade-Relacionamento Ex: Vamos considerar que a entidade Pessoa tem cardinalidade 1 no relacionamento Lotação. Entidade Departamento tem cardinalidade 120 no relacionamento Lotação. Pessoa Departamento Lotação 89 Modelo Entidade-Relacionamento Pessoa Departamento Lotação 1 Expressa que uma ocorrência de Pessoa (entidade do lado oposto da notação) pode estar associado a no máximo uma ocorrência de Departamento 90 Modelo Entidade-Relacionamento Pessoa Departamento Lotação 1 Expressa que uma ocorrência de Departamento (entidade do lado oposto da notação) pode estar associado a muitas (“n”) ocorrências de Pessoa n 91 Modelo Entidade-Relacionamento • 1:1 (um-para-um) Pessoa Casamento marido esposa 1 1 Empregado Mesa Alocação 1 1 92 Modelo Entidade-Relacionamento Cada empregado pertence a no máximo um departamento. Cada departamento possui no máximo um empregado. Exemplo: Emp...DEPTO E1….…COMPRAS E2….…ALMOXARIFADO E3….…GERÊNCIA E4….…VENDAS Emp…….DEPTO E1…………COMPRAS E2…………COMPRAS E3…………GERÊNCIA E3…………VENDAS Incorreto Incorreto EMPREGADO DEPARTAMENTO LOTADO 1 1 1:1 (um-para-um) 93 Modelo Entidade-Relacionamento • 1:n (um-para-muitos) Aluno Curso Inscrição 1 n Empregado Dependente n 1 Empregado Supervisão supervisor supervisionado 1 N 94 Modelo Entidade-Relacionamento Cada empregado pertence a no máximo um departamento. Cada departamento pode lotar vários empregados. Exemplo: Emp...DEPTO E1….…COMPRAS E2….…COMPRAS E3….…GERÊNCIA E4….…VENDAS E5…….GERÊNCIA Emp……..DEPTO E1…………COMPRAS E1…………GERÊNCIA E3…………GERÊNCIA E4…………VENDAS E5………..GERÊNCIA Incorreto EMPREGADO DEPARTAMENTO LOTADO N 1 1:n (um-para-muitos) ou n:1 (muitos-para-um) 95 Engenheiro Projeto Alocação n n Médico Paciente Consulta n n Peça Composiçãocomposto componente n N n:n (muitos-para-muitos) 96 ENGENHEIRO PROJETO Alocação N N Um engenheiro pode participar de vários projetos. Um projeto pode ter a participação de vários engenheiros. Exemplo: ENG...PROJETO E1….…P1 E2….…P1 E3….…P2 E1….…P2 E1…….P3 n:n (muitos-para-muitos) 97 Um relacionamento é dito total quando todos os elementos da classe de entidades participam obrigatoriamente no relacionamento. Exemplo: EMPREGADO DEPARTAMENTO LOTADO N 1 Indica que entidades da classe EMPREGADO devem obrigatoriamente se relacionar com um departamento => não há funcionário sem departamento. Relacionamento Total 98 Cidade Distribuidor Alocação 1 n Produto n •No caso de um relacionamento ternário, a cardinalidade refere-se a pares de entidades. •Em um relacionamento R entre três entidades A, B e C, a cardinalidade de A e B dentro de R indica quantas ocorrências de C podem estar associadas a um par de ocorrências de A e B. Relacionamento Ternário 99 1 do lado do Distribuidor: Indica que cada par Cidade/Produto está associado a no máximo 1 distribuidor. N do lado do Produto: Indica que um par Cidade/Distribuidor pode estar associado a muitos produtos. N do lado Cidade: Indica que um par Produto/Distribuidor pode estar associado a muitas cidades. 100 Vamos elaborar um MER que permita o correto controle das matrículas dos alunos em uma escola, onde a preocupação concentra-se no acompanhamento da vida acadêmica dos alunos e no resultado final (APROVADO ou REPROVADO). Exercício um aluno pode matricular-se em um único curso nesta escola, mas um curso contém vários alunos. um curso é formado por diversas disciplinas, mas uma mesma disciplina pode estar em mais que um curso vários alunos podem cursar uma mesma disciplina e uma disciplina tem vários alunos 101 Identificar as Entidades de acordo com os requisitos deste sistema. TAREFA 1: ALUNO CURSO DISCIPLINA Atributos 102 ALUNO - matricula-se - CURSO um aluno pode matricular-se em um único curso nesta escola, mas um curso contém vários alunos Cardinalidade N : 1 CURSO - formado - DISCIPLINA um curso é formado por diversas disciplinas, mas uma mesma disciplina pode estar em mais que um curso Cardinalidade N : M ALUNO - cursa - DISCIPLINA nota, falta, situação vários alunos podem cursar uma mesma disciplina e uma disciplina tem vários alunos Cardinalidade N : M TAREFA 2: Identificar os Relacionamentos entre as Entidades de acordo com os requisitos deste sistema. 103 Disciplina nome id_disciplin a Aluno nome matrícul a RG matrícula-se Curso nome código telefone cursa formando nota situação falta 104 Entidade Fraca Entidade que só existe quando relacionada a outra entidade, sendo seu identificador composto por atributos de outra entidade. Empregado Dependente Atuação n 1 nome num seq nome código 105 MER Estendido Novas Necessidades (em Banco de Dados): •DataWareHousing •Aplicações Multimídia •Data Mining •Sistemas de Informação Geográficas (GIS) Novos conceitos de Modelagem de Dados Modelo ER Estendido = Modelo EER Orientação a Objetos 106 Especialização • Através deste conceito é possível atribuir propriedades particulares a um subconjunto das ocorrências (especializadas=subclasse) de uma entidade genérica (superclasse). • Representação gráfica: Filial Cliente n 1 Pessoa Física Pessoa Jurídica código nome sexo CGC tipo de organização atende CIC superclasse subclasses 107 O relacionamento entre a superclasse e suas subclasses é denominado de relacionamento é um tipo de Especialização Filial Cliente n 1 Pessoa Física Pessoa Jurídica código nome sexo CGC tipo de organização atende subclasses superclasse CIC 108 MER Estendido Empregado Motorista nome matrícula num_habilitação Secretária •Subclasses herdam as propriedades (atributos e relacionamentos) da superclasse, podendo possuir propriedades adicionais. •As subclasses podem adicionar novos atributos ou relacionamentos, além dos herdados da superclasse. dirige Automóvel 109 Situações Recomendadas: 1. Possibilidade de definir um conjunto de subclasses de uma entidade 2. Estabelecer atributos adicionais específicos a cada subclasse 3. Estabelecer relacionamentos adicionais específicos entre cada subclasse e outras entidades ou entre subclasses. Especialização 110 MER Estendido Generalização É o processo inverso à Especialização, isto é, é um processo de síntese em que suprimimos as diferenças entre várias entidades (subclasses), identificamos suas características comuns e as generalizamos numa superclasse. 111 MER Estendido • Há 4 restrições que podem ser usadas com relacionamentos de generalização/especialização: – Parcial – Total – Disjunção – Sobreposição Restrições 112 MER Estendido • Total – Para toda ocorrência da entidade genérica (superclasse), deve existir uma ocorrência em uma das entidades específicas (subclasses). – Representada por um “t”, próximo do triângulo que representa o relacionamento de generalização/especialização. Restrições 113 MER Estendido Restrições - Total CLIENTE PESSOA FÍSICA PESSOA JURÍDICA nome código CPF sexo CGC t Não existem ocorrências de Cliente que não sejam pessoas físicas ou pessoas jurídicas. 114 MER Estendido Restrições • Parcial – Nem toda ocorrência da entidade genérica (superclasse) possui uma ocorrência correspondente em uma entidade especializada (subclasses). – Representada por um “p”, próximo do triângulo que representa o relacionamento de generalização/especialização. 115 MER Estendido Restrições - Parcial Empregado Secretária Motorista Engenheiro matrícula num_idiomas num_habilitação CREA p nome Existem ocorrências de Empregado que não são Motoristas, secretárias ou engenheiros. 116 MER Estendido Restrições • Disjunção – Cada ocorrência da entidade mais genérica (superclasse) não pode pertencer a mais de uma subclasse. – Representado por um “d”, próximo ao triângulo que representa o relacionamento de generalização/especialização. 117 MER Estendido Empregado Secretária Motorista Engenheiro matrícula num_idiomas num_habilitação CREA d nome Restrições - Disjunção Cada ocorrência de Empregado ou é Motorista ou é Secretária ou é Engenheiro. 118 MER Estendido • Sobreposição – Cada ocorrência da entidade mais genérica (superclasse) pode pertencer a mais de uma subclasse. – Representado por um “s”, próximo ao triângulo que representa o relacionamento de generalização/especialização. Restrições 119 MER Estendido Pessoa Funcionário Professor Aluno s Restrições - Sobreposição Uma pessoa pode ser Professor e também Aluno ou uma Pessoa pode ser Funcionário e também Aluno. 120 MER Estendido • Uma entidade pode ser uma especialização de mais de uma entidade genérica, ou seja, uma subclasse pode ter mais de uma superclasse. Herança Múltipla 121 MER Estendido Veículo Veículo TerrestreVeículo Aquático Automóvel Veículo Anfíbio Barco Herança Múltipla 122 Herança Múltipla Pessoa Aluno Professor Funcionário Monitor 123 MER Estendido Entidade Associativa (Agregação) • Um relacionamento é uma associação entre entidades. • Não foi previsto no modelo ER: – A associação entre uma entidade e um relacionamento. – A associação entre dois relacionamentos entre si. • Mas existem situações em que é desejável permitir a associação de uma entidade a um relacionamento. 124 Entidade Associativa (Agregação) • Suponha que seja necessário modificar o modelo abaixo para incluir que medicamentos existem e que medicamentos foram prescritos em cada consulta. Médico Paciente Consulta n n A questão agora é: Com que entidade existente deve estar relacionada a nova entidade (Medicamento)? 125 MER Estendido • Se Medicamento fosse relacionado a Médico: – Teríamos apenas a informação de que médico prescreveu que medicamento, faltando a informação do paciente que os teve prescritos. Entidade Associativa (Agregação) Médico Paciente Consulta n n Medicamento Prescrição n n 126 MER Estendido • Se Medicamento fosse relacionado a Paciente: – Faltaria a informação do médico que prescreveu o medicamento. Entidade Associativa (Agregação) Médico Paciente Consulta n n Medicamento Prescrição n n 127 • Solução: – Relacionar Medicamento à Consulta, isto é, vamos relacionar uma entidade a um relacionamento. – Como fazer isso: usar o conceito de “Entidade Associativa” ou “Agregação” Entidade Associativa (Agregação) Médico Paciente Consulta n n Medicamento Prescrição n n Uma entidade associativa nada mais é do que uma redefinição de um relacionamento que passa a ser tratado como se fosse também uma entidade. 128 • A figura anterior é equivalente a: Médico Paciente n n Medicamento Prescrição n n Consulta 1 1 Entidade Associativa (Agregação) 129 MER Estendido Entidade Associativa (Agregação) • Qual a diferença para um relacionamento ternário? Médico Paciente Medicamento Prescrição n n n 130 Perguntas... Banco de Dados Modelo RelacionaL Prof Melo Modelo Relacional Introduzido pelo pesquisador da IBM, Edward Codd, em 1970, gerou uma grande quantidade de pesquisa acadêmica ao longo da década de 70. O modelo relacional baseia-se na teoria matemática de conjuntos, onde os dados são representados através de uma coleção de relações ou tabelas Características marcantes: modelo formal por natureza, estruturas de dados simples e uniformes Objetivos do Modelo Relacional Prover esquemas de fácil utilização; Melhorar a independência lógica e física de dados; Prover usuários com linguagens de manipulação de BD de alto nível, permitindo o seu uso por usuários não experientes; Otimizar o acesso ao BDs; Melhorar a integridade e segurança dos dados; Permitir a sua utilização em uma variedade ampla de aplicações; Prover uma abordagem metodológica para o projeto de esquemas. Modelo Relacional • Razões de grande aceitação – Simplicidade nos conceitos – Poder dos operadores de manipulação • Propriedades – No modelo relacional, o resultado de qualquer operação é sempre uma tabela – O resultado de qualquer operação, sempre uma tabela, é do mesmo tipo do objeto de entrada. – A saída de uma operação pode se tornar a entrada de outra Definições • Relação • Subconjunto do produto cartesiano de domínios • Domínio – D é um conjunto de valores atômicos, isto é, cada valor no domínio é indivisível no modelo relacional. – Um tipo de dados ou formato é também associado a cada domínio. – Exemplos: conjunto dos inteiros, dos caracteres de comprimento 20, das cores, dos brasileiros, etc Domínio • Um domínio deve possuir um nome e o tipo de dados dos valores do domínio. • Exemplos: – Conceitos: possíveis valores de conceitos (SFA, SFS, SFO) – Nomes: o conjunto de nomes de pessoas – Idades dos empregados: possíveis idades dos empregados de uma empresa; cada idade deve ser um valor entre 18 e 80 anos. – Nomes dos departamentos: conjunto de nomes dos departamentos (Matemática, Informática, Física, Educação) Produto cartesiano Modelo Relacional Uma maneira mais fácil de visualizar uma relação é através de uma tabela: Ano Vencedor Perdedor 1952 Eisenhower Stevenson 1956 Eisenhower Stevenson 1960 Kennedy Nixon 1964 Johnson Goldwater 1968 Nixon {Humphrey, Wallace} 1972 Nixon McGovern Modelo Relacional Representação tabular de uma relação R n-ária: Cada linha representa uma n-tupla de R. A ordem das linhas não é significante. Todas as linhas são distintas. A ordem das colunas é significante: Corresponde à ordem S1, S2, ..., Sn dos domínios* sobre os quais R é definido. Cada tabela possui um nome. Cada coluna (também chamada de atributo ou campo) possui um nome. Modelo Relacional • Um banco de dados relacional é composto de Tabelas ou Relações: – Tabela – termo mais empregado na prática. – Relações – termo mais empregado na literatura formal da área de BD. Matricula Nome Fone 12345 Gilberto 6481 12346 Sandra 6444 12347 Isabela 6333 12349 Jonas 6233 Domínio de um atributo: é o conjunto de valores permitidos para um atributo. Termo relacional formal Equivalentes informais Relação Tabela Tupla Linha Cardinalidade Número de linhas Atributo Coluna ou campo Grau Número de colunas Chave primária Identificador exclusivo Domínio Conjunto de valores válidos Modelo Relacional - Propriedades das relações • Não existem tuplas duplicadas: – Duas linhas, em uma relação, não podem ser iguais. – Esta propriedade é derivada do conceito de conjunto matemático (um conjunto não permite a existência de elementos repetidos) – SQL não obriga que uma tabela não possua linhas duplicadas. Modelo Relacional - Propriedades das relações • Tuplas não são ordenadas de cima para baixo: – Não existe nenhuma seqüência entre as tuplas. – Esta propriedade também é derivada do conceito de conjunto matemático (os elementos de um conjunto não são ordenados). Modelo Relacional - Propriedades das relações • Atributos não são ordenados da esquerda para a direita: – Não existe nenhuma seqüência particular entre os atributos de uma relação. – Este conceito decorre do fato de que o cabeçalho de uma relação é também um conjunto (de atributos). – Os atributos de uma relação são sempre referenciados pelo nome, nunca pela posição. Modelo Relacional - Propriedades das relações • Cada tupla contém exatamente um valor para cada atributo: – Cada valor em uma tupla é um valor atômico, ou seja, um valor que não pode ser dividido. Portanto, atributos compostos e multivalorados não são permitidos. Modelo Relacional - Chaves • Conceito básico de um banco de dados relacional que se aplica: – Na identificação de linhas – Estabelecimento de relações entre linhas de tabelas. • Tipos de chaves: – Primária (Primary Key – PK) – Alternativa (Candidata) – Estrangeira (Foreign Key – FK) Modelo Relacional – Chave primária • Coluna ou combinação de colunas cujos valores distinguem uma linha das demais dentro de uma tabela. Chave Primária Tabela: Alunos Matricula Nome Fone 12345 Gilberto 6481 12346 Sandra6444 12347 Isabela 6333 12349 Jonas 6481 Modelo Relacional – Chave primária Ano Vencedor Perdedor 1952 Eisenhower Stevenson 1956 Eisenhower Stevenson 1960 Kennedy Nixon 1964 Johnson Goldwater 1968 Nixon {Humphrey, Wallace} 1972 Nixon McGovern Eleicoes Modelo Relacional • Requisitos: – Chave Mínima: todas as colunas são realmente necessárias para garantir o requisito de unicidade de valores da chave. • A conotação dada a chave no modelo relacional é diferente da usada em organização de arquivos: – Chave em outros conceitos é entendido como um caminho de acesso – No modelo relacional estamos definindo uma restrição de integridade: unicidade de valores nas colunas que compõem a chave. Modelo Relacional – Esquema de relação • Forma textual empregada para descrever o esquema de uma relação: – Esquema_aluno = (matricula, nome, cursoid) – Esquema_curso = (cursoid, titulo, duração) Modelo Relacional – Chave estrangeira • Coluna ou combinação de colunas, cujos valores aparecem necessariamente na chave primária de uma tabela. • Mecanismo que permite a implementação de relacionamentos em um banco de dados relacional. Chave Estrangeira MATRICULA NOME CURSOID 98765 João MAT 67765 José BIO 84562 Maria ENG 34256 Luis INFO 3452672 Ana MAT 34529 Luana MAT CURSOID TITULO DURAÇÃO INFO Informática Indust. 4 BIO Biologia 4 ENG Engenharia Civil 5 MAT Licenciatura Mat. 4 Curso Aluno Obs.: Através do relacionamento, evitamos a repetição de informações. Relacionamento Chave Estrangeira MATRICULA NOME CURSOID 98765 João MAT 67765 José BIO 84562 Maria ENG 34256 Luis INFO 3452672 Ana MAT 34529 Luana MAT CURSOID TITULO DURAÇÃO INFO Informática Indust. 4 BIO Biologia 4 ENG Engenharia Civil 5 MAT Licenciatura Mat. 4 Aluno Curso CHAVE PRIMÁRIA CHAVE ESTRANGEIRA Chave Estrangeira • Observações: – Uma chave estrangeira não precisa ter o mesmo nome do que a chave primária correspondente na outra tabela (apenas o mesmo domínio). Chave Estrangeira • Impõe restrições que devem ser garantidas ao serem executadas diversas operações no Banco de Dados: – A inclusão de uma linha na tabela que contém a chave estrangeira: • Garantir que o valor da chave estrangeira exista na coluna da tabela da chave primária. – Alteração do valor da chave estrangeira: • O novo valor deve aparecer na coluna da chave primária referenciada. Modelo Relacional – Chave estrangeira’ – Exclusão de uma linha da tabela que contém a chave primária referenciada por uma chave estrangeira: • Não se exclui a linha caso exista um valor na tabela com a chave estrangeira. • Remove-se também a linha com o valor de chave estrangeira. • Valor da chave estrangeira é ajustado como NULL. – Alteração do valor da chave primária referenciada por alguma chave estrangeira: • Propagar a modificação • Não deixar que seja feita a modificação Modelo Relacional – Chave alternativa • Em alguns casos, mais de uma coluna ou combinações podem servir para identificar uma linha. • A coluna escolhida é denominada chave primária e as demais chaves candidatas. CodigoEmp Nome Depto CIC E1 Gilberto DPI 123456789-02 E2 Eduardo LAC 876574345-45 E3 Antonio ALC 324255365-81 E4 José SERE 023330000-39 Restrições de Integridade • Uma das funcionalidades básicas que todo SGBD deve oferecer, sendo um de seus objetivos também. • Restrição de integridade: – É uma regra de consistência de dados que é garantida pelo SGBD. • Tipos de Restrições: – Chave – Integridade – Referencial – Semântica Restrições de Chaves: cada atributo das chaves candidatas deve possuir valor único em todas as tuplas da relação. Restrição de Integridade de Entidade: uma chave primária não pode assumir valor nulo em qualquer tupla da relação. Restrição de Integridade Referencial: uma tupla em uma relação que se refere a outra relação, deve se referenciar a uma tupla existente nesta relação (chave estrangeira). Restrições de Integridade Semântica: se referem mais especificamente sobre valores ou características que determinados atributos podem assumir no contexto de uma determinada aplicação (por exemplo sexo). Restrições de Integridade Restrições de Integridade Transformações entre Modelos MER – Voltado para a Modelagem de Dados independente do SGBD – Adequado para construção do Modelo Conceitual Modelo Relacional – Modela os dados no nível de SGBD Relacional (Modelo Lógico) Transformação entre MER e Modelo Relacional Projeto Lógico: transformação de um MER em um modelo lógico que implementa, em um SGBD Relacional, os dados representados abstratamente no MER. (Para outros tipos de SGBD outras regras são necessárias). Transformações entre Modelos Engenharia Reversa Processo inverso: Parte-se do Modelo Relacional e obtém-se um MER, que representa de forma abstrata os dados armazenados em um BD. Modelo ER (nível conceitual) Modelo Relacional (nível lógico) Engenharia Reversa de BD relacional Projeto Lógico de BD relacional MER – Pode ser implementado através de diversos Modelos Relacionais. Cada modelo alternativo pode ser considerado uma implementação correta. Cada Modelo alternativo pode resultar em diferentes performances e facilidades no desenvolvimento e manutenção do sistema construído sobre o BD. Fator Determinante de Sucesso: Modelo ER (nível conceitual) Modelo Relacional (nível lógico) Refinamento do modelo relacional Transformação de ER para Relacional Conhecimento sobre a aplicação Alcançar os objetivos Regras de Tradução Evitar Junções – dados necessários a consulta em uma única linha. Diminuir o número de chaves – utilização de índices Evitar colunas opcionais – valor vazio (nulo) • Principais objetivos: – Banco de Dados com melhor performance na manipulação. – Simplificar o desenvolvimento e a manutenção de aplicações. Passos: 1. Implementação de entidades e respectivos atributos 2. Implementação de relacionamentos e respectivos atributos 3. Implementação de generalizações especializações Passo 1: Entidade = Tabela Atributo = Coluna Atributo identificador = Chave Primária Cliente nome endereço data de nascimento código Cliente (CodigoCli, Nome, Endereco, DataNasc) Nomes de Atributo e Colunas – Devem obedecer ao padrão estabelecido (domínio: código, número, percentual, valor) – Devem ser curtos, mas sem perder semântica. – Não podem conter brancos nem hífen. – Evitar concatenar com nome da tabela. – Chave Primária prefixado ou sufixado com nome ou sigla da tabela. – Utilizar abreviaturas. Relacionamentos • A tradução do relacionamento depende da cardinalidade (mínima e máxima) das entidades que participam do relacionamento. • Formas básicas de tradução: – Tabela própria – Colunas adicionais dentro da tabela de entidade – Fusão de Tabelas de entidades Tabela Própria: Colunas correspondentes aos identificadores das entidades relacionadas. Colunas correspondentes aos atributos do relacionamento. Engenheiro Projeto código nome código título n n função Engenheiro (CodEng, Nome) Projeto (CodProj, Titulo) Atuação (CodEng, CodProj, Funcao) CodEng referencia Engenheiro CodProj referencia Projeto atuação Adição deColuna (1:N ou N:1) Colunas correspondentes ao identificador da entidade relacionada (forma a chave estrangeira) inserir, na entidade com cardinalidade máxima N, colunas para conter o atributo identificador da entidade relacionada mais os atributos do relacionamento. Departamento Empregado código nome código nome 1 n Data de lotação lotação Departamento (CodDept, Nome) Empregado (CodEmp, Nome, CodDept, DataLot) CodDept referencia Departamento • Empregado: CodEmp Nome CodDept DataLot 101 João 1 30/12/1976 102 José 2 12/06/2001 103 Maria 1 21/03/1987 • Departamento: CodDept Nome 1 Gerência 2 Vendas 3 Compras • Fusão de tabelas de entidades – Só é possível quando o relacionamento é do tipo 1:1 – Consiste na fusão das tabelas referentes às entidades envolvidas no relacionamento. Pessoa Habilitação CPF endereço número Data de validade 1 1 possui nome Pessoa (CPF, nome, endereço, NoHabilitacao, DatValHabilitacao) Relacionamento 1:1 Entidades têm participação opcional = (0,1) e (0,1) Primeira Solução (indicada) Homem Mulher código código nome (0,1) (0,1) casamento nome Homem (IdentH, Nome) Mulher (IdentM, Nome, IdentH, Data, Regime) IdentH referencia Homem data regime Não Utilizar : Tabela (IdentM, NomeM, IdentH, NomeH, Data, Regime) Homem Mulher código código nome (0,1) (0,1) casamento nome Homem (IdentH, Nome) Mulher (identM, Nome) Casamento (IdentH, IdentM, Data, Regime) IdentH referencia Homem IdentM referencia Mulher Relacionamento 1:1 Entidades têm participação opcional = (0,1) e (0,1) Segunda Solução data regime Relacionamento 1:1 Uma entidade tem participação opcional e a outra tem participação obrigatória = (1,1) e (0,1) Primeira Solução Correntista Cartão magnético código número Data Exp (1,1) (0,1) nome Correntista (CodCorrent, Nome, CodCartao, DataExp) Relacionamento 1:1 Uma entidade tem participação opcional e a outra tem participação obrigatória = (1,1) e (0,1) Segunda Solução (indicada) Correntista Cartão magnético código número Data Exp nome Correntista (CodCorrent, Nome) Cartao (CodCartao, DataExp, CodCorrent) CodCorrent referencia Correntista (1,1) (0,1) Relacionamento 1 : 1 Duas entidades com participação obrigatória = (1,1) e (1,1) Conferência Comissão Código Nome Ender (1,1) (1,1) Organização Data Instalação Conferencia (CodConf, Nome, DtInstalacao, EndeComOrg) Relacionamento 1 : N Adição de Colunas Departamento Empregado código nome código nome 1 n Data de lotação Departamento (CodDept, Nome) Empregado (CodEmp, Nome, CodDept, DataLot) CodDept referencia Departamento •Entidade Fraca = Tabela onde: Na Entidade Fraca é criada uma chave estrangeira formada pelas colunas da chave primária. A chave primária da Entidade Fraca é formada pela combinação do atributo identificador da entidade fraca mais a chave estrangeira. Edifício Apartamento código endereço número área 1 n Edifício (CodigoEd, Endereço) Apartamento (CodEdificio, NroApart, AreaAp) CodigoEd referencia Edifício Relacionamento 1 : N Adição de Colunas – Entidade Fraca • Mapeando para o Modelo Relacional: – Empregado(CodigoEmp, Nome) – Dependente(CodigoEmp, Nroseq, Nome) • Chave primária da entidade fraca: – Composta Empregado Dependente n 1 nome num seq nome código Entidades Fracas associadas há a propagação em vários níveis. Grupo Empresa Empregado código nome 1 n número da empresa nome n 1 número do empregado nome Grupo (CodGrupo, Nome) Empresa (CodGrupo,NoEmpresa, Nome) CodGrupo referencia Grupo Empregado (CodGrupo, NoEmpresa, NoEmpreg, Nome) (CodGrupo, NoEmpresa) referencia Empresa Financeira Venda código nome id data (0,1) (0,n) financiam taxa juros nro parcelas Financeira (CodFin, Nome) Venda( IdVenda, Data, CodFin, TxJuros, NoParc) CodFin referencia Financeira Relacionamento 1 : N Adição de Colunas - Primeira Solução Financeira Venda código nome id data (0,1) (0,n) financiam taxa juros Relacionamento 1 : N Tabela Própria - Segunda Solução Financeira (CodFin, Nome) Venda (IdVenda, Data) Financiamento (IdVenda, CodFin, NoParc, TxJuros) IdVenda referencia Venda CodFin referencia Financeira Relacionamento N : N Sempre Tabela Própria Engenheiro Projeto código nome código título n n função Engenheiro (CodEng, Nome) Projeto (CodProj, Nome) Atuação (CodEng, CodProj, Funcao) CodEng referencia Engenheiro CodProj referencia Projeto atuação Auto-Relacionamento • Além das relações das entidades, cria-se uma para o relacionamento. Relações: •Peca(CodPeca, Descricao, Peso, Cor) •Composicoes(CodPeca, CodPecaCompoe, Quantidade) CodPeca Referencia Peca CodPecaCompoe Referencia Peca PEÇA COMPOSIÇÕES n n quantidade Atributo Multivalorado • Uma tabela para cada atributo multivalorado, contendo uma coluna para cada atributo propagando a chave primária. Cliente código nome telefone Cliente (CodCliente, Nome) Telefone (CodCliente, NroTelefone) CodCliente referencia Cliente * Relacionamento Ternário Sequência de Passos: • Relacionamento transformado em Entidade. Nova entidade ligada através de um relacionamento binário às outras entidades. • Aplicar regras de relacionamentos binários (respeito à cardinalidade). Cidade Distribuidor Produto (0,n) (0,n) (0,1) distribuição código nome código nome código nome data de início Relacionamento Ternário Situação: Cidade Distribuidor Distribuição Produto (1,1) (0,n) (0,n) (1,1) (0,n) (1,1) código nome código nome código nome data de início Relacionamento Ternário Solução: Produto (CodProd, Nome) Cidade (CodCid, Nome) Distribuidor (CodDistr, Nome) Distribuicao (CodProd, CodDistr, CodCid, DataInicio) CodProd referencia Produto CodDistr referencia Distribuidor CodCid referencia Cidade Relacionamento Ternário Tabelas: Associação (Agregação) Sequência de Passos: • Relacionamento transformado em Entidade. Nova entidade ligada através de um relacionamento binário às outras entidades. • Aplicar regras de relacionamentos binários (respeito à cardinalidade). Associação (Agregação) Situação: Médico Paciente Consulta n n Medicamento Prescrição n n código nome código nome data consulta código nome quantidade Associação (Agregação) Tabelas: Medico (CodMedico, Nome) Paciente (CodPaciente, Nome) Consulta (CodMedico, CodPaciente, DtConsulta) CodMedico referencia Medico CodPaciente referencia Paciente Medicamento (CodMedicamento, Nome) Prescricao (CodMedico, CodPaciente, CodMedicamento, quantidade) CodMedico, CodPaciente referencia Consulta CodMedicamento referencia Medicamento Associação (Agregação) Solução: Médico Paciente n n Medicamento Prescrição n n Consulta 1 1 código nome código nome data consulta código nome quantidade código Associação (Agregação) Tabelas: Medico (CodMedico,Nome) Paciente (CodPaciente, Nome) Consulta (NroConsulta, CodMedico, CodPaciente, DtConsulta) CodMedico referencia Medico CodPaciente referencia Paciente Medicamento (CodMedicamento, Nome) Prescricao (NroConsulta, CodMedicamento, quantidade) NroConsulta referencia Consulta CodMedicamento referencia Medicamento Generalização/Especialização Alternativas de implementação: Uma tabela para toda hierarquia (generalização) Uma tabela para cada Entidade Especializada Subdivisão da Entidade Genérica Generalização/Especialização nome Empregado Secretária Engenheiro idiomas CREA código nome Motorista habilitação Departamento Projeto (1,1) (0,n) (0,n) (0,n) código p código nome Generalização/Especialização Uma tabela para toda hierarquia A chave primária corresponderá ao identificador da entidade mais genérico Coluna “Tipo” que indica que tipo de entidade especializada está representando cada linha Uma coluna para cada atributo da entidade genérica Colunas dos relacionamentos da entidade genérica Uma coluna para cada atributo das entidades especializadas (colunas opcionais) Colunas dos relacionamentos da entidade especializada (colunas opcionais) respeitando cardinalidade Uma tabela para toda hierarquia Empregado Secretária Engenheiro idiomas CREA código nome Motorista habilitação Departamento Projeto (1,1) (0,n) (0,n) (0,n) código p código nome Departamento (CodDepto, Nome) Empregado (CodEmpreg, Tipo, Nome, Idioma, NroHabilit, CREA, CodDepto) CodDepto referencia Departamento Projeto (CodProjeto, Nome) Participacao (CodEmpreg, CodProjeto) CodEmpreg referencia Empregado CodProj referencia Projeto Vantagens: Menos Junções Menos PK Desvantagens: Atributos Opcionais Generalização/Especialização Uma tabela por Entidade Especializada Criar tabelas para cada entidade especializada. Implementação semelhante à anterior com acréscimo da chave primária da tabela correspondente à entidade genérica. Uma tabela por Entidade Especializada Empregado Secretária Engenheiro idiomas CREA código nome Motorista habilitação Departamento Projeto (1,1) (0,n) (0,n) (0,n) código p código nome Departamento (CodDepto, Nome) Empregado (CodEmpreg, Tipo, Nome, CodDepto) CodDepto referencia Departamento Secretaria (CodEmpreg, Idioma) CodEmpreg referencia Empregado Motorista (CodEmpreg, NroHabilit) CodEmpreg referencia Empregado Engenheiro (CodEmpreg, CREA) CodEmpreg referencia Empregado Projeto (CodProj, Nome) Participacao (CodEmpreg, CodProj) CodEmpreg referencia Engenheiro CodProj referencia Projeto Vantagens: Menos Atributos Opcionais Desvantagens: Mais Junções Mais PK Generalização/Especialização Subdivisão da Entidade Genérica Criar tabelas para cada entidade especializada que contém tanto os dados da entidade especializada, quanto os de suas entidades genéricas. Subdivisão da entidade genérica Empregado Secretária Engenheiro idiomas CREA código nome Motorista habilitação Departamento Projeto (1,1) (0,n) (0,n) (0,n) código p código nome Departamento (CodDepto, Nome) EmpOutros (CodEmpreg, Tipo, Nome, CodDepto) CodDepto referencia Departamento Secretaria (CodEmpreg, Nome, Idioma, CodDepto) CodEmpreg referencia Empregado CodDepto referencia Departamento Motorista (CodEmpreg, Nome, NroHabilit, CodDepto) CodEmpreg referencia Empregado CodDepto referencia Departamento Engenheiro (CodEmpreg, Nome, CREA, CodDepto) CodEmpreg referencia Empregado CodDepto referencia Departamento Projeto (CodProj, Nome) Participacao (CodEmpreg, CodProj) CodEmpreg referencia Engenheiro CodProj referencia Projeto Vantagens: Menos Atributos Opcionais Menos PK Desvantagens: Unicidade da PK FK empregados geral Refinamento do Modelo Relacional Processo de transformação nos leva a um BD correto na abordagem relacional. Deve-se analisar as redundâncias e o compromisso entre o ideal e o realizável (performance). Alternativas de implementação: Redundância no BD projetado Manutenção da redundância Simulação de atributos multivalorados Relacionamento mutuamente exclusivos Refinamento do Modelo Relacional Redundância no BD projetado Problema associado ao uso de relacionamentos identificadores CPF data início data número Curso (0,n) (0,n) (1,1) nome código data fim Inscritos código Prova avaliação (0,n) (0,n) (1,1) nota número Curso (0,n) (0,n) (1,1) nome código data fim Inscritos CPF código Prova avaliação (0,n) (0,n) (1,1) nota data data início Curso (CodCurso, Nome, DtIni, DtFim) Prova (CodCurso, NroProva, DtProva) CodCurso referencia Curso Inscrito (CodCurso, NroInscrito, CPF) CodCurso referencia Curso Avaliacao (CodCursoProva, NroProva, CodCursoInscrito, NroInscrito, Nota) (CodCursoProva, NroProva) referencia Prova (CodCursoInscrito, NroInscrito) referencia Inscrito Avaliacao (CodCurso, NroProva, NroInscrito, Nota) (CodCurso, NroProva) referencia Prova (CodCurso, NroInscrito) referencia Inscrito Redundância no BD projetado Refinamento do Modelo Relacional Manutenção da redundância Desejável pela performance Vôo roteiro código Reserva (0,n) (1,1) número passageiro Número de reservas Soma do número de passageiros Refinamento do Modelo Relacional Simulação de atributo Multivalorados Cliente nome código número de telefone (0,n) código Cliente nome Telefone telefone (1,1) (0,n) Cliente (CodCli, Nome) Telefone (CodCli, Numero) CodCli referencia Cliente 1. Raros Clientes com mais que 2 telefone 2. Não há consultas ao BD por telefone Cliente (CodCli, Nome, Fone1, Fone2) Refinamento do Modelo Relacional Relacionamento mutuamente exclusivo Pessoa Física CPF nome Venda (0,n) (0,1) Pessoa Jurídica (0,1) (0,n) CGC Razão social número data Relacionamento mutuamente exclusivo número Pessoa Física CPF nome Venda (0,n) (0,1) Pessoa Jurídica (0,1) (0,n) CGC Razão social data PessFis (CPF, Nome) PessJur (CGC, Nome) Venda (NumVenda, data, CPF, CGC) CPF referencia PessFis CGC referencia PessJur Venda (NumVenda, data, CP_CGC, TipoCliente) Vantagem: Menos Colunas Opcionais Desvantagem: Ausência de FK Engenharia Reversa de Modelos Relacionais Engenharia Reversa um processo de abstração que parte de um modelo de implementação e resulta em um modelo conceitual. Utilizadas quando BD desenvolvido de forma empírica (sem metodologia) Esquemas sofreram modificações sem atualização do MER “Herança” de sistemas Engenharia Reversa de Modelos Relacionais Principais Passos: Identificação das tabelas Definição dos relacionamentos 1:n e 1:1 Definição de atributos Definição de identificadores de entidades e relacionamentos Engenharia Reversa de Modelos Relacionais Disciplina (CodDisciplina, NomDisc) Curso (CodCurso, Nome) Curric (CodCurso, CodDisc, ObrOpc) CodCurso referencia Curso CodDisc referencia Disciplina Sala (CodPredio, CodSala,Capacidade) CodPredio referencia Predio Predio (CodPredio, Endereco) Turma (AnoSem, CodDisc, SigraTur, Capacidade, CodPredio, CodSala) CodDisc referencia Disciplina (CodPredio, CodSala) referencia Sala Laboratorio (CodPredio, CodSala, Equipamento) (CodPredio, CodSala) referencia Sala Perguntas... Bibliografia • DATE, Christopher J., DARWEN, Hugh. Foundations for future database systems. New York: Addison Wesley, 2000. • ELMASRI, Rames, NAVATHE, Shamkant B. Sistema de Banco de Dados – Fundamentos e Aplicações. 3ª Ed., Rio de Janeiro: LTC, 2000. • MANZANO, José Augusto N. G. Estudo Dirigido de SQL. São Paulo: Érica, 2002. Bibliografia Complementar • OPPEL, Andy. Banco de Dados Desmistificado. Rio de Janeiro: Alta Books, 2004. • COSTA, Rogério Luís de C. SQL - Guia Prático. São Paulo: Brasport, 2004. • DATE, Christopher J. Introdução a Sistemas de Banco de Dados. Rio de Janeiro: Campus, 2004. • DICOVERY. Princípios de Banco de Dados. São Paulo: Senac, 1999. • ELMASRI, R., NAVATHE, S. B. Fundamentals of Database Systems. Califórnia: Benjamin/ Cummings, 2000. Banco de Dados Normalização Prof Melo Normalização • Normalização de dados é o processo formal e passo a passo que examina os atributos de uma entidade, com o objetivo de evitar anomalias observadas na inclusão, exclusão e alteração de registros. Normalização • Método que permite identificar a existência de problemas potenciais (anomalias de atualização) no projeto de um BD relacional. • Converte progressivamente uma tabela em tabelas de grau e cardinalidade menores até que pouca ou nenhuma redundância de dados exista. • Se a normalização é bem sucedida: – O espaço de armazenamento dos dados diminui (redundâncias) – A tabela pode ser atualizada com maior eficiência – A descrição do BD será imediata Entidade • O conceito de entidade é muito importante neste processo, ou seja, devemos identificar quais são as entidades que farão parte do projeto de banco de dados. Entidade é qualquer coisa, pessoa ou objeto que abstraído do mundo real torna-se uma tabela para armazenamento de dados. Normalmente usa-se o Modelo de Entidade e Relacionamento para criar o modelo do banco. Exemplo Entidades Relacionadas Normalização • A regra de ouro que devemos observar no projeto de um banco de dados baseado no Modelo Relacional de Dados é a de "não misturar assuntos em uma mesma Tabela". Por exemplo: na Tabela Clientes devemos colocar somente campos relacionados com o assunto Clientes. Não devemos misturar campos relacionados com outros assuntos, tais como Pedidos, Produtos, etc. Essa "Mistura de Assuntos" em uma mesma tabela acaba por gerar repetição desnecessária dos dados bem como inconsistência dos dados. Exemplo Tabela não Normalizada Normalização • Normalmente após a aplicação das regras de normalização de dados, algumas tabelas acabam sendo divididas em duas ou mais tabelas, o que no final gera um número maior de tabelas do que o originalmente previsto. Este processo causa a simplificação dos atributos de uma tabela, colaborando significativamente para a estabilidade do modelo de dados, reduzindo-se consideravelmente as necessidades de manutenção. Exemplo Tabela não Normalizada Repetição de Informações Exemplo Tabelas Normalizadas Objetivos da Normalização • Minimização de redundâncias e inconsistências; • Facilidade de manipulações do banco de dados; • Ganho de performance no SGBD; • Facilidade de manutenção do sistema de Informação; As Formas Normais • O Processo de normalização aplica uma série de regras sobre as tabelas de um banco de dados, para verificar se estas estão corretamente projetadas. Embora existam cinco formas normais (ou regras de normalização), na prática usamos um conjunto de três Formas Normais. • Vejamos as três primeiras formas normais do processo de normalização de dados. – Primeira Forma Normal (1FN) – Segunda Forma Normal (2FN) – Terceira Forma Normal (3FN) Primeira Forma Normal • A Primeira Forma Normal, denominada 1FN e aplicada no processo de normalização de dados no processo de modelagem de banco de dados. • A normalização de dados é um processo importante no processo de modelagem de dados. A primeira parte da normalização é chamada de 1FN ou primeira forma normal, em uma escala que vai até cinco. Primeira Forma Normal • Uma relação estará na primeira forma normal 1FN, se não houver grupo de dados repetidos, isto é, se todos os valores forem únicos. Em outras palavras podemos definir que a primeira forma normal não admite repetições ou campos que tenha mais que um valor. • Os procedimentos mais recomendados para aplicar a 1FN são os seguintes: – Identificar a chave primária da entidade; – Identificar o grupo repetitivo e removê-lo da entidade; – Criar uma nova entidade com a chave primária da entidade anterior e o grupo repetitivo. • A chave primária da nova entidade será obtida pela concatenação da chave primária da entidade inicial e a do grupo repetitivo. Exemplo Primeira Forma Normal • Considere a seguinte estrutura: – O Cliente é composto pelas seguintes dados: • Codigo_cliente • Nome • Telefone (multivalorado) • endereço Analisando os Dados • Todos os clientes possuem Rua, CEP e Bairro, e essas informações estão na mesma célula da tabela, logo ela não está na primeira forma normal. Analisando os Dados • Para normalizar, deveremos colocar cada informação em uma coluna diferente, como no exemplo a seguir: Analisando os Dados • Mesmo com o ajuste acima, a tabela ainda não está na primeira forma normal, pois há clientes com mais de um telefone e os valores estão em uma mesma célula. Analisando os Dados • Para normalizar será necessário criar uma nova tabela para armazenar os números dos telefones e o campo-chave da tabela cliente. Veja o resultado a seguir: Conclusões • Na segunda tabela a chave primária está implícita, isto voe poderá encontrar algumas literaturas especializadas, onde nem sempre ela é especificada, mas ela deverá existir. • No exemplo acima foi gerado uma segunda entidade para que a primeira forma normal fosse satisfeita, contudo é importante ressaltar que nem sempre encontramos banco de dados com tabelas normalizadas. Existem casos onde as repetições são poucas ou o cenário permite administrar as repetições sem trazer grandes consequências. Segunda Forma Normal • Uma tabela está na Segunda Forma Normal2FN se ela estiver na 1FN e todos os atributos não chave forem totalmente dependentes da chave primária (dependente de toda a chave e não apenas de parte dela). • Se o nome do produto já existe na tabela produtos, então não é necessário que ele exista na tabela de vendas. • A segunda forma normal trata destas anomalias e evita que valores fiquem em redundâcia no banco de dados. Segunda Forma Normal • Procedimentos: – Identificar os atributos que não são funcionalmente dependentes de toda a chave primária; – Remover da entidade todos esses atributos identificados e criar uma nova entidade com eles. • A chave primária da nova entidade será o atributo do qual os atributos do qual os atributos removidos são funcionalmente dependentes. Exemplo de Segunda Forma Normal • Considere a tabela venda com os seguintes atributos – N_pedido – Código_produto – Produto – Quant – Valor_unit – Subtotal Analisando os Dados • O nome do produto depende do código do produto, porém não depende de N_pedido
Compartilhar