Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original
PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS Eduardo Marcelino História dos Bancos de dados Sistemas com gestão de ficheiros, desenvolvidos com COBOL; Possibilitou a automação de diversas tarefas; Cada sistema como se fosse uma ilha isolada; Aplicação => Ficheiros, gerava-se vários arquivos, replicação para controles diferentes; Probabilidade de erro, bastante alta -> devido a redundância dos dados; Dias Atuais Dados organizados em um único conjunto; O Sistema de Gerenciamento de Banco de Dados (SGBD) centraliza acessos a dados; Aplicações com apenas uma interface lógica e não física; Aplicações não necessitam conhecer nem implementar lógicas de armazenamento; Vários SGBD’s diferentes para varias tipos de soluções: open source, licenciadas, diversas arquiteturas; Motivação da utilização de Sistemas Gerenciadores de Bancos de Dados As empresas possuem quantidades cada vez maiores de dados e informações a armazenar; Manipulação destas informações quase impossível de ser realizada manualmente (via papéis, principalmente) Facilidade de encontrar a informação numa base de dados em que estão implementadas as melhorias praticas para armazenamento e obtenção dos dados; Controlar Acesso a informação; Ciclo de Desenvolvimento em Bancos de Dados Estratégia Análise de Requisitos Projeto Construção Implementação Implantação Estratégia Sucesso do desenvolvimento Entendimento claro conciso Reais necessidades O que deverá ser feito Análise Como deverá ser feito Projeto Resultado: Conjunto de modelos Planos de desenvolvimento do sistema Equipe que será responsável Stakeholders Cobertura das necessidades atuais e futuras Pautados nos limites: Financeiros Organizacionais Técnicos Estratégia Analise completa porém não detalhada Diretrizes, objetivos, metas, prioridades, premissa, delimitações, fatores críticos Diagrama Entidade/Relacionamento (ER) Diagramas funcionais Abordagem O-O Diagramas UML Análise de Requisitos Recebe e verifica os resultados da fase de estratégia Expande resultados em detalhes suficientes para assegurar A precisão do sistema Viabilidade Fundamento minucioso do projeto Analise de Requisitos Fatores críticos Envolvimento compromissado do usuário Verificação exata de completude e qualidade Identificação de todos os requisitos chave e adoção para as fases de projeto e Implementação Informação exata sobre funções chave e dados Controle rígido Definição concordada da palavra adequado Análise de Requisitos Principais resultados insumos Diagrama ER aprimorado Diagramas funcionais Plano revisado de desenvolvimento do sistema Volumes de dados, frequência funcional e expectativas de desempenho Definição do estilo de trabalho Estratégia de implementação inicial Requisitos para auditoria e segurança Esboço do manual de procedimentos Critérios de aceitação pelo usuário Dimensionamento preliminar Delimitações, domínios e pressuposições Projeto Definição precisa da implementação Diagrama E/R transforma-se em tabelas (modelo relacional) Funções traduzidas em programas Surgem telas, relatórios, gatilhos, procedimentos, etc Prototipação pode ser utilizada para ajudar a tomada de decisão em áreas onde existam dúvidas Especificação de programas e plano de teste Projeto Principais resultados Arquitetura final do sistema Projeto dos módulos Esquema lógico e físico Construção do banco de dados Dimensionamento detalhado Especificações de programas Especificações de procedimentos manuais Rascunho do manual do usuário Plano de teste Rascunho da documentação Plano revisado de desenvolvimento Construção Codificar e testar programas Uso de Ferramentas CASE Envolve Planejamento Estruturação de programas Codificação Testes Produzir documentação necessária Manuais de usuários Instruções operativas Dar suporte às tarefas de testes Deve estar completa antes da implementação Não precisa ser impressa Construção Principais resultados Estruturação de programas Banco de Dados afinado Programas executáveis já testados Estratégia de implementação revisada Resultados de testes globais Manuais Usuário Operacional Produção Demais áreas envolvidas Implementação Treinamento dos usuários Conversão de dados e instalação de hardware/software completada Principais resultados Treinamento e material didático Usuários e operadores treinados Sistema completamente operacional Dados convertidos Cadastro de novos dados em processo de criação Relatório revisado de pós-implementação Facilidades de suporte Documentação completa do sistema Implantação Pessoal de operação fornece os níveis de serviço acertados Pessoal de suporte responde a problemas Nível de serviço monitorado Faltas corrigidas e alterações executadas de maneira controlada Administração desenvolve planos para aperfeiçoar ou substituir os sistemas Implantação Resultados Cópia, backup, recuperação e armazenamento de arquivos Registro de controle de alterações Relatórios de falhas Alterações do sistema Estatística de desempenho Novos requisitos Resultados da auditoria de sistemas O QUE DÁ ERRADO NOS PROJETOS O QUE DÁ ERRADO NOS PROJETOS Ideia de gravar os dados não é correta; Tabela ou planilha projetada para simular uma tela de entrada de dados ou relatório; Imediatismo nas soluções de armazenamento; Redundâncias dos dados; Inexistência de requisitos revisados e maduros; EXEMPLOS DE PROJETOS COM PROBLEMAS Tratamento inadequado de palavra-chave e categorias Problema: criação de relatórios, analises dos dados, redundâncias, etc. EXEMPLOS DE PROJETOS COM PROBLEMAS - Tratamento inadequado de palavra-chave e categorias Registro de informações sobre plantas Gênero Espécie Nome Comum Uso 1 Uso 2 Uso 3 Dodonaea viscosa akeake construção cobertura Estabilizaçãodo solo Cedrus atlantica Cedro-do-Líbano construção Alnus glutinosa Cedro negro construção Estabilizaçãodo solo lenha Ecalyptus nichollii Eucaliptonegro construção Problemas Como encontrar plantas para um determinado uso? Colunas separadas As plantas possuem no máximo 3 usos ? No futuro, pode ser necessário incluir mais usos, acarretando retrabalho. EXEMPLOS DE PROJETOS COM PROBLEMAS Informações Repetidas Problema: criação de relatórios, analises dos dados, redundâncias, etc. ERROS COMUNS EM PROJETOS DE BANCOS DE DADOS 1. Má concepção / planejamento; 2. Ignorar a normalização; 3. Falta de padrões de nomenclatura; 4. Falta de documentação; 5. Uma tabela para armazenar todos os valores de domínio; 6. Usar coluna auto-incremento como sua única chave; 7. Não utilizar as características do SQL para proteger a integridade de dados; 8. Não usar stored procedures para acessar dados; 9. Tentativa de construção de objetos genéricos; 10. Falta de testes. Por Louis Davidson ETAPAS DE UM PROJETO DE BANCO DE DADOS AS ETAPAS DE UM PROJETO DE BANCO DE DADOS Análise de Requisitos Documentação do Sistema (casos de uso, docs de arquitetura, visão, etc) Minumundos Modelos Externos Etc. Projeto Conceitual Modelo conceitual Projeto Lógico Modelo Interno Projeto Físico Scripts Modelos físicos Estratégias de armazenamento Politicas de Backup ANÁLISE DE REQUISITOS A primeira etapa do projeto de banco de dados é a identificação dos requisitos que o banco de dados deve atender. Nessa fase, realiza-se entrevistas com as pessoas envolvidas no processo, cria-se uma descrição textual macro do processo (mini-mundo), modelos externos (que devem ser entendidos por todos). É o momento em que as regas de negócio devem ser identificadas, se a fase de análise de requisitos for mal executada e se identificar regras de negócio que não representam a realidade, tudo o que for feito em seguida no projeto será perda de tempo. Parte mais importante do projeto. ANÁLISE DE REQUISITOS Passo extremamente importante no ciclo de vida do banco de dados e é tipicamente o mais trabalhoso. O analista de banco de dados deve entrevistar a população de usuários finais e determinar exatamente o que o banco de dados é para ser usado e o que deve conter. Os objetivos básicos da análise de requisitos são: Delineia os requisitos de dados da empresa; Descrever a informação sobre os elementos de dados e os relacionamentos entre eles; Determinar os tipos de transações que são desejadas para serem executadas no banco de dados e a interação entre as transações e os elementos de dados; Definir desempenho, integridade, segurança, ou restrições administrativas; ANÁLISE DE REQUISITOS Para especificar qualquer projeto e restrições de implementação, tal como tecnologias específicas, hardware e software, linguagens de programação, políticas, padrões ou interfaces externas. Para bem documentar todas as anteriores em uma especificação de requisitos detalhados. Os elementos de dados também podem ser definidos em um sistema de dicionário de dados, muitas vezes fornecidas como uma parte integral do sistema de gerenciamento de banco de dados. O modelo conceitual de dados ajuda o analista capturar precisamente os requisitos de dados reais porque exige-os para focar em detalhes de semântica nos relacionamentos de dados, que é maior que o detalhe que seria fornecido por uma dependência funcional exclusiva. ENGENHARIA DE REQUISITOS São funções desta fase do processo: Concepção; Elicitação; Elaboração; Negociação; Especificação; Validação; Gerenciamento. Independente se feitas em sequência ou em paralelo... ENGENHARIA DE REQUISITOS – CONCEPÇÃO Geralmente é a primeira fase do processo; Identificação de um problema ou oportunidade; Perguntas genéricas e superficiais; Objetivo é estabelecer: Um entendimento básico do problema; Quem são os stakeholders; A natureza da solução desejada; A eficácia da comunicação entre o engenheiro de requisitos e os especialistas de domínio. ENGENHARIA DE REQUISITOS – CONCEPÇÃO Primeiras perguntas (exemplos) Quem está pedindo esta solução? Quem irá usá-la? Qual é seu benefício econômico? De que problema(s) esta solução irá tratar? Em que ambiente de negócio ela está inserida? Existem qualidades fundamentais (desempenho, segurança, etc.) relevantes ao problema? ENGENHARIA DE REQUISITOS – CONCEPÇÃO Meta-questões auxiliam a estabelecer a eficiência da comunicação analista-especialistas: Você é a pessoa certa para responder estas questões? Suas respostas são “oficiais”? Minhas questões são relevantes? Estou perguntando coisas demais? Existe outra pessoa que pode prover mais informações? Existe alguma pergunta que eu deveria ter feito e não fiz? Com quem mais eu deveria conversar sobre isso? ENGENHARIA DE REQUISITOS – ELICITAÇÃO A meta é descobrir informações sobre o problema: Os objetivos dos stakeholders (problema); As funções do sistema (solução) a ser construído (o que ele deve fazer); Como o sistema se encaixa nas necessidades de negócio do cliente; Como será usado no dia-a-dia. Atividade muito complicada; Requer alto nível de organização. ENGENHARIA DE REQUISITOS – ELICITAÇÃO As “primeiras perguntas” darão somente um entendimento básico do problema; Para elicitar os requisitos, devemos utilizar abordagens mais sofisticadas. Algumas abordagens: Amostragem; Investigação; Entrevista; Observação; Questionário; Prototipação. ENGENHARIA DE REQUISITOS – ELICITAÇÃO (PROBLEMAS) Escopo: Os limites do sistema não são bem definidos; O cliente especifica muitos detalhes inúteis. Entendimento: O cliente não tem certeza do que quer; Não conhece as capacidades e limitações do ambiente computacional; Possui problemas de comunicação com os engenheiros de software; Omite informações consideradas “óbvias”; Especifica requisitos que conflitam com os de outro cliente; Especifica requisitos ambíguos. ENGENHARIA DE REQUISITOS – ELICITAÇÃO (PROBLEMAS) Políticos: Funcionários não colaboram por acharem que o sistema lhes custará o emprego; Brigas políticas internas. De volatilidade: Os requisitos mudam com o tempo. ENGENHARIA DE REQUISITOS – ELICITAÇÃO (PROBLEMAS) ENGENHARIA DE REQUISITOS – ELABORAÇÃO Tem por objetivo a redução da ambiguidade (se possível, sua eliminação); São utilizados modelos (o nível de formalidade pode variar, dependendo do projeto); Formalizar os requisitos em modelos auxilia na identificação de falhas na elicitação; Modelos são úteis também para negociação e validação. ENGENHARIA DE REQUISITOS – NEGOCIAÇÃO Não é incomum: Clientes quererem mais do que é possível ser feito; Stakeholders terem requisitos conflitantes. Deve-se reconhecer os múltiplos pontos de vista e tentar negociar uma solução adequada; Idealmente, deve-se evitar situações em que hajam “vencedores” e “perdedores”. ENGENHARIA DE REQUISITOS – ESPECIFICAÇÃO Produto final da Engenharia de Requisitos; Uma especificação pode ser: Um documento escrito; Modelos gráficos; Um modelo formal matemático; Um protótipo; É a base das fase seguintes da Engenharia de Sotware; Varia de acordo com características do projeto (tamanho, “criticalidade”, etc.). ENGENHARIA DE REQUISITOS – VALIDAÇÃO Revisões técnicas para averiguar que: Todos os requisitos foram elencados sem ambiguidade; Inconsistências, omissões e erros foram detectadas e corrigidas; Tudo está documentado de acordo com padrões estabelecidos pela organização. ENGENHARIA DE REQUISITOS – GERENCIAMENTO Atividades que ajudam no controle e rastreamento de mudanças nos requisitos: Cada requisitos recebe um identificador; São montadas tabelas de rastreamento: funcionalidades, dependências, subsistemas, interface, etc.; Mudanças nos requisitos podem ser mais facilmente propagadas “para frente”; Bugs no software pronto podem ser analisados em termos dos requisitos (“para trás”). ANALISE DE REQUISITOS Descrição inicial do problema: Documento de visão Casos de uso (UML), historias (Scrum), entre outros. INSUMOS PARA MODELAGEM Documento de Visão Diagrama de Caso de Uso Diagrama de Classe Outros artefatos DOCUMENTO DE VISÃO O documento de visão define o escopo de alto nível e o propósito de um programa, produto ou projeto. Uma instrução clara do problema, solução proposta e os recursos de alto nível do produto ajudam a estabelecer expectativas e a reduzir riscos. DIAGRAMA DE CASO DE USO Descreve a funcionalidade proposta para um novo sistema que será projetado e uma excelente ferramenta para o levantamento dos requisitos funcionais do sistema. CASOS DE USO Uma forma de estruturar requisitos: Modelos gráficos e linguagem natural baseada em formulários; Representam o que os usuários podem fazer no sistema; São independentes do método de análise (OO, estruturado, etc.). “Um caso de uso conta uma história sobre como um usuário final (interpretando um de uma série de papéis) interage com o sistema dentro de um conjunto de circunstâncias.” (Roger Pressman) “Um caso de uso captura um contrato que descreve o comportamento do sistema sob várias condições a medida que ele responde a requisições de um de seus usuários.” (Alistair Cockburn) “Um caso de uso especifica um comportamento de um sistema segundo uma perspectiva externa e é uma descrição de um conjunto de sequências de ações realizadas pelo sistema para produzir um resultado de valor observável por um ator.” (Grady Booch) ATIVIDADES DO PROJETO DO BANCO DE DADOS PRINCIPAIS ATIVIDADES Projeto do esquema conceitual: produzir um esquema conceitual do banco de dados independente do SGBD que será utilizado pelas aplicações Projeto transacional: determinar e descrever detalhadamente as operações que serão realizadas pelas aplicações que deverão manipular as informações do SGBD Seleção de um SGBD: selecionar o produto que será utilizado pelas aplicações (depende de uma variedade de fatores técnicos e não-técnicos) Projeto lógico de dados: especializar o modelo conceitual para o SGBD que foi selecionado e definir os modelos externos com que o banco de dados será manipulado armazenamento, caminhos de acesso, índices, agrupamentos, tamanho dos blocos de disco, entre outras decisões de nível interno que otimizem o desempenho do sistema Implementação: colocar o SGBD em operação, executar os programas em DDL que criam a estrutura vazia de tabelas do banco de dados e povoar as tabelas com informações Ajuste fino (tunning): ajustar as características do BD de acordo com os acessos realizados e os gargalos de desempenho Estudo de caso Elicitar requisitos de um sistema, para criar os modelos de dados nos três níveis. MODELAGEM ESQUEMA CONCEITUAL Abstração Nível de visão do usuário: são as partes do banco de dados que o usuário tem acesso de acordo com a necessidade individual de cada usuário ou grupo de usuários. Nível conceitual: define quais os dados que estão armazenados e qual o relacionamento entre eles. Nível físico: é o nível mais baixo de abstração, em que define efetivamente de que maneira os dados estão armazenados. Visão 1 Visão 2 Visão 3 Visão 4 Nível de visão do usuário Nível Conceitual Nível Físico Abstração de dados Nível Externo - Nível do usuário Nível mais alto de abstração; Visão do usuário; Nível conceitual – Nível Logico Temos os dados que serão armazenados e seus relacionamentos; Visão geral do conteúdo total do banco de dados; Nível Interno – Nível de armazenamento Nível mais baixo da abstração, mais próximo do armazenamento físico; Descreve de fato como está o armazenamento; Independência dos dados Altera o esquema em um determinado nível sem afetar o esquema do nível superior; Independência de dados física: alterar o nível físico sem que seja necessário alterar o programa ou aplicação; Independência de dados lógica: alterar o nível logico sem que seja necessário alterar o programa ou aplicação, mais difícil de ser alcançada, pois as aplicações são fortemente ligadas a estrutura logica dos dados do que seu acesso; Etapas para a Modelagem de Banco de Dados Levantamento de requisitos (elicitação) Requisitos de dados; Requisitos funcionais; Modelo Conceitual Visão global dos principais dados e relacionamentos; Descrição em alto nível (tenta retratar toda a realidade de uma organização); Resultado final: esquema que representa a realidade das informações existentes e as estruturas para representa-las; Etapas para a Modelagem de Banco de Dados Modelo Lógico Inicia a partir do modelo conceitual; Descreve as estruturas que estarão contidas no banco de dados, sem considerar características do SGBD; Etapas para a Modelagem de Banco de Dados Modelo físico Parte do modelo logico; Descreve as estruturas físicas do armazenamento dos dados: tamanho, índices, tipos de dados, nomenclaturas, etc. Detalha o estudo dos métodos de acesso ao SGBD Etapa final na qual se utiliza DDL (Data Definition Language) Apresentação dos Modelos Modelo Conceitual Descrição de um banco de dados de forma independente de implementação em um SGBD; Não registra como os dados são armazenados; Modelo Entidade Relacionamento (MER) Definido por Peter Chen em 1976, utilizando como base a teoria relacional de Edgard F. Cood (1970); Notação em forma de diagramas; Diferentes leitores devem entender sempre o mesmo; Modelo formal Preciso Não ambíguo; Serve de insumo para ferramentas CASE (Computer Aided Software Engineering) Modelo Entidade Relacionamento (MER) Entidade Conjunto de objetos (perceptível ou manipulável) da realidade que deseja-se modelar a qual deseja-se armazenar os dados; São objetos do “mundo real” sobre os quais deseja-se manter informações no banco de dados; Entidade Exemplos EMPREGADO LIVRO ESCRITOR DEPARTAMENTO JOGO SERVIDOR CLIENTE FUNCIONARIO VENDA Atributos Propriedades que descrevem entidades; O que deseja-se armazenar das entidades; Nome Nacionalidade Data de nascimento Local de nascimento Data de morte Pseudonimo Atributos Valores Nome: Fernando Pessoa Nacionalidade: portuguesa Data de nascimento: 13 de Junho de 1888 Local de nascimento: Lisboa Data de morte: 30 de Novembro de 1935 Pseudonimos: Alberto Caeiro, Ricardo Reis, Alvaro de Campos, Bernardo Soares Atributo Simples X Atributo Composto Atributo Simples: atributo tem um único valor atômico; Exemplo: Nacionalidade, nome; Atributo Composto: atributo composto por vários componentes; Exemplo: Data de nascimento: dia, mês, ano. Atributo Simples ESCRITOR nome nacionalidade Atributo Composto ESCRITOR Data de nascimento Dia Mês Ano Atributo Monovalorado atributo que tem um único valor. Exemplo: Data de Nascimento; Atributo Multivalorado atributo que pode ter múltiplos valores Pseudônimo: Alberto Caeiro, Ricardo Reis, Álvaro de Campos, Bernardo Soares; Atributo Armazenado Exemplo: Data de Nascimento; Atributo Derivado Exemplo: Idade do escritor; Atributo Nulo Uma entidade pode ter um atributo que não é necessariamente obrigatório, alguns possuem e outros não. Exemplo: uma entidade professor pode não ter telefone celular Atributo Identificador Atributo (ou combinação de atributos) que identifica univocamente uma instância de entidade (identifica uma tupla); O atributo chave pode ser composto de vários atributos Uma entidade pode tem mais de uma chave Relacionamento Relacionamento é uma associação entre entidades; Cada tipo entidade que participa de um tipo relacionamento executa um papel no relacionamento. Papéis não são necessários em relacionamentos cujas entidades associadas sejam distintas. Relacionamento Exemplos ESCRITOR LIVRO ESCREVE PROFESSOR DISCIPLINA MINISTRA ALUNO DISCIPLINA CURSA Auto-relacionamento Relacionamento entre instâncias da mesma entidade; Instâncias participam com papéis diferentes; Auto-relacionamento FUNCIONARIO SUPERVISIONA Modelo Entidade Relacionamento (MER) Cardinalidade Numero (mínimo ou máximo) de ocorrências de uma entidade associada a outra entidade através do relacionamento; Cardinalidade Mínima: Numero mínimo de ocorrências nas associações entre as entidades; Cardinalidade Máxima Numero máximo de ocorrências nas associações entre as entidades Entidade Forte Uma entidade forte tem chave; Entidade Fraca Uma entidade fraca Não tem atributo chave Tem apenas uma chave parcial Deve participar de um relacionamento com uma entidade forte; Entidade Forte X Entidade Fraca EMPREGADO DEPENDENTE POSSUI Cardinalidade de relacionamentos Propriedade importante de um relacionamento Quantas ocorrências de uma entidade podem estar associadas a uma determinada ocorrência de entidade através do relacionamento Chamada de cardinalidade de uma entidade em um relacionamento duas cardinalidades máxima mínima Classificação de relacionamentos Cardinalidade máxima pode ser usada para classificar relacionamentos binários Relacionamento binário é aquele cujas instâncias envolvem duas instâncias de entidades Relacionamentos binários n:n (muitos-para-muitos) 1:n (um-para-muitos) 1:1 (um-para-um) Relacionamento Ternário Relacionamento Ternário “1” na linha que liga o retângulo representativo da entidade DISTRIBUIDOR ao losango representativo do relacionamento expressa que cada par de ocorrências (cidade, produto) está associado a no máximo um distribuidor. Em outros termos, não há concorrência pela distribuição de um produto em uma cidade. Relacionamento Ternário Já os dois “n” expressam que: q A um par (cidade, distribuidor) podem estar associados muitos produtos, ou em outros termos, um distribuidor pode distribuir em uma cidade muitos produtos. q A um par (produto, distribuidor) podem estar associadas muitas cidades, ou em outros termos um distribuidor pode distribuir um produto em muitas cidades. Atributo Dado ou informação que é associado a cada ocorrência de uma entidade ou de um relacionamento Atributo com Cardinalidade Cardinalidade mínima atributo obrigatório (cardinalidade mínima “1”) cada entidade possui no mínimo um valor associado) atributo opcional (cardinalidade mínima “0”) Cardinalidade máxima atributo monovalorado (cardinalidade máxima “1”) cada entidade possui no máximo um valor associado) atributo multivalorado (cardinalidade máxima “n) Atributo com Cardinalidade Atributo em relacionamento ATUAÇÃO, possui um atributo, a função que um engenheiro exerce dentro de um projeto. Não pode ser considerada atributo de ENGENHEIRO, já que um engenheiro pode atuar em diversos projetos exercendo diferentes funções. Também, não é atributo de PROJETO, já que, em um projeto, podem atuar diversos engenheiros com funções diferentes. Atributo em relacionamento 1:n Este diagrama modela vendas em uma organização comercial. Algumas vendas são à vista, outras à prazo. Vendas à prazo são relacionadas a uma financeira, através do relacionamento FINANCIAMENTO. Os atributos nº de parcelas e taxa de juros são atributos do relacionamento. Atributo Identificador Cada entidade deve possuir um identificador identificador = conjunto propriedades de uma entidade (atributos e relacionamentos) cujos valores servem para distinguir uma ocorrência da entidade das demais ocorrências da mesma entidade Atributo Identificador Atributo código é identificador. Isso significa que cada pessoa possui um código diferente. Já os atributos nome e endereço não são identificadores – o mesmo nome (ou o mesmo endereço) pode ser associados a diferentes pessoas. Atributo Identificador Considera-se um almoxarifado de uma empresa de ferragens organizado como segue. Os produtos ficam armazenados em prateleiras. Estas prateleiras encontram-se em armários organizados em corredores. Os corredores são numerados sequencialmente a partir de um e as prateleiras são numeradas sequencialmente a partir de um dentro de um corredor. Assim, para identificar uma prateleira é necessário conhecer seu número e o número do corredor em que se encontra. Para cada prateleira desejasse saber sua capacidade em metros cúbicos. Entidade Fraca (Relacionamento Identificador) Este diagrama apresenta empregados de uma organização, relacionados com os seus dependentes para fins de imposto de renda. Cada dependente está relacionado a exatamente um empregado. Um dependente é identificado pelo empregado ao qual ele está relacionado e por um número de sequência que distingue os diferentes dependentes de um mesmo empregado. O relacionamento usado como identificador é indicado por uma linha mais densa. Entidade Fraca DEPENDENTE é uma entidade fraca. O termo “fraca” deriva-se do fato de a entidade somente existir quando relacionada a outra entidade e de usar como parte de seu identificador, entidades relacionadas. Cardinalidade de relacionamentos Propriedade importante de um relacionamento Quantas ocorrências de uma entidade podem estar associadas a uma determinada ocorrência de entidade através do relacionamento Chamada de cardinalidade de uma entidade em um relacionamento duas cardinalidades máxima mínima Classificação de relacionamentos Cardinalidade máxima pode ser usada para classificar relacionamentos binários Relacionamento binário é aquele cujas instâncias envolvem duas instâncias de entidades Relacionamentos binários n:n (muitos-para-muitos) 1:n (um-para-muitos) 1:1 (um-para-um) Relacionamento Ternário Relacionamento Ternário “1” na linha que liga o retângulo representativo da entidade DISTRIBUIDOR ao losango representativo do relacionamento expressa que cada par de ocorrências (cidade, produto) está associado a no máximo um distribuidor. Em outros termos, não há concorrência pela distribuição de um produto em uma cidade. Relacionamento Ternário Já os dois “n” expressam que: q A um par (cidade, distribuidor) podem estar associados muitos produtos, ou em outros termos, um distribuidor pode distribuir em uma cidade muitos produtos. q A um par (produto, distribuidor) podem estar associadas muitas cidades, ou em outros termos um distribuidor pode distribuir um produto em muitas cidades. Atributo Dado ou informação que é associado a cada ocorrência de uma entidade ou de um relacionamento Atributo com Cardinalidade Cardinalidade mínima atributo obrigatório (cardinalidade mínima “1”) cada entidade possui no mínimo um valor associado) atributo opcional (cardinalidade mínima “0”) Cardinalidade máxima atributo monovalorado (cardinalidade máxima “1”) cada entidade possui no máximo um valor associado) atributo multivalorado (cardinalidade máxima “n) Atributo com Cardinalidade Atributo em relacionamento ATUAÇÃO, possui um atributo, a função que um engenheiro exerce dentro de um projeto. Não pode ser considerada atributo de ENGENHEIRO, já que um engenheiro pode atuar em diversos projetos exercendo diferentes funções. Também, não é atributo de PROJETO, já que, em um projeto, podem atuar diversos engenheiros com funções diferentes. Atributo em relacionamento 1:n Este diagrama modela vendas em uma organização comercial. Algumas vendas são à vista, outras à prazo. Vendas à prazo são relacionadas a uma financeira, através do relacionamento FINANCIAMENTO. Os atributos nº de parcelas e taxa de juros são atributos do relacionamento. Atributo Identificador Cada entidade deve possuir um identificador identificador = conjunto propriedades de uma entidade (atributos e relacionamentos) cujos valores servem para distinguir uma ocorrência da entidade das demais ocorrências da mesma entidade Atributo Identificador Atributo código é identificador. Isso significa que cada pessoa possui um código diferente. Já os atributos nome e endereço não são identificadores – o mesmo nome (ou o mesmo endereço) pode ser associados a diferentes pessoas. Atributo Identificador Considera-se um almoxarifado de uma empresa de ferragens organizado como segue. Os produtos ficam armazenados em prateleiras. Estas prateleiras encontram-se em armários organizados em corredores. Os corredores são numerados sequencialmente a partir de um e as prateleiras são numeradas sequencialmente a partir de um dentro de um corredor. Assim, para identificar uma prateleira é necessário conhecer seu número e o número do corredor em que se encontra. Para cada prateleira desejasse saber sua capacidade em metros cúbicos. Entidade Fraca (Relacionamento Identificador) Este diagrama apresenta empregados de uma organização, relacionados com os seus dependentes para fins de imposto de renda. Cada dependente está relacionado a exatamente um empregado. Um dependente é identificado pelo empregado ao qual ele está relacionado e por um número de sequência que distingue os diferentes dependentes de um mesmo empregado. O relacionamento usado como identificador é indicado por uma linha mais densa. Entidade Fraca DEPENDENTE é uma entidade fraca. O termo “fraca” deriva-se do fato de a entidade somente existir quando relacionada a outra entidade e de usar como parte de seu identificador, entidades relacionadas. Generalização É um relacionamento de classificação entre um elemento mais geral e outro mais específico O elemento mais geral tem todas as características (atributos) que são comuns aos elementos específicos define herança O elemento mais geral é denominado entidade de nível superior (superclasse) e o mais específico de entidade de nível inferior (subclasse) As características do nível superior são herdadas no nível inferior Por isso o processo é conhecido como herança Relacionamento de Generalização generalização especialização Generalização e Especialização Utilizada quando ocorrer uma situação onde: Uma entidade possuir atributos que não fazem parte de todas as instâncias da entidade. Quando as instâncias de uma entidade se relacionarem de maneira diferente com outras entidades. Tipos: Definidas por Condição ou Definidas pelo Usuário Exclusivas ou Compartilhada Total ou Parcial Tipos de Generalização (Exclusiva) A ocorrência da entidade especializada é exclusiva, aparecendo em apenas uma das entidades especializadas Funcionario Motorista Secretaria E O Funcionário somente pode ser OU Motorista OU Secretaria, jamais ambos Tipos de Generalização (Compartilhada) Uma ocorrência da entidade genérica pode aparecer em múltiplas entidades especializadas Pessoa Professor Aluno Funcionario c A pessoa em uma universidade pode ser um professor (na graduação), ser um funcionário e ser um aluno (de doutorado) Tipos de Generalização (Total) Generalização Total: Para cada ocorrência da entidade genérica, existe sempre uma ocorrência das entidades especializadas. Tipos de Generalização (Parcial) Generalização Parcial: Nem toda ocorrência da entidade genérica possui uma ocorrência correspondente em uma entidade especializada. Generalização e Especialização em vários níveis Possível ter generalização/especialização em múltiplos níveis; Herança múltipla em que veiculo anfíbio herda de veiculo terrestre e veiculo aquático; Agregação Uma restrição do Modelo E-R é que não podemos expressar relacionamentos entre relacionamentos. Agregação é uma abstração através da qual relacionamentos são tratados como entidades de nível superior. ATENÇÃO: Agregação não é Relacionamento Ternário. Agregação - Exemplo Verificação do Modelo ER Verificação do Modelo ER Modelo deve ser correto Um modelo está correto quando não contém erros de modelagem, isto é, quando os conceitos de modelagem ER são corretamente empregados para modelar a realidade em questão. Pode-se distinguir entre dois tipos de erros, os erros sintáticos e os erros semânticos. Erros sintáticos ocorrem quando o modelo não respeita as regras de construção de um modelo ER. Exemplo: associar relacionamentos a outros relacionamentos. Erros semânticos ocorrem quando o modelo, apesar de obedecer as regras de construção de modelos ER (estar sintaticamente correto) reflete a realidade de forma inconsistente. Verificação do Modelo ER Modelo deve ser correto Exemplos de erros semânticos: Estabelecer associações incorretas. Um exemplo é associar a uma entidade um atributo que na realidade pertence a outra entidade. Por exemplo, em um modelo com entidades CLIENTE e FILIAL, associar a CLIENTE o nome da filial com o qual o cliente trabalha usualmente (nome de filial é um atributo de FILIAL). Usar uma entidade do modelo como atributo de outra entidade. Um exemplo seria ter, em um modelo, uma entidade BANCO e usar banco como atributo de uma outra entidade CLIENTE. Cada objeto da realidade modelada deve aparecer uma única vez no modelo ER. Verificação do Modelo ER Modelo deve ser correto Exemplos de erros semânticos: Usar o número incorreto de entidades em um relacionamento. Um exemplo é o de fundir em um único relacionamento ternário dois relacionamentos binários independentes. Modelos Equivalentes De maneira informal, diz-se que dois modelos são equivalentes, quando expressam o mesmo, ou seja quando modelam a mesma realidade. Para fins de projeto de BD, dois modelos ER são equivalentes, quando ambos geram o mesmo esquema de BD. Para entender perfeitamente este conceito de equivalência de modelos, o leitor deve conhecer as regras de tradução de modelo ER para modelo relacional. Modelos Equivalentes Relacionamento m:n versus uma entidade + 2 Relacionamentos 1:n. Modelos Equivalentes Relacionamento m:n da agregação versus uma entidade + 2 Relacionamentos 1:n. Modelos Equivalentes Atributo multi-valorado versus uma entidade. Lançamento pagamento” e “dependentes” possuem outras informações (atributos) ainda não representadas no modelo. Escolha de Modelagem Atributo versus entidade. Exemplo: em uma indústria de automóveis, como devemos registrar a cor de cada automóvel que sai da linha de produção? Alguns critérios para esta decisão são: Caso o objeto cuja modelagem está em discussão esteja vinculado a outros objetos (atributos, relacionamentos, entidades genéricas ou especializadas), o objeto deve ser modelado como entidade. Quando o conjunto de valores de um determinado objeto é fixo durante toda a vida do sistema ele pode ser modelado como atributo. Quando existem transações no sistema que alteram o conjunto de valores do objeto, o mesmo não deve ser modelado como atributo. Escolha de Modelagem Atributo versus generalização/especialização. Exemplo: a categoria funcional de cada empregado de uma empresa deve ser modelada como atributo da entidade ou através de uma especialização (cada categoria funcional corresponde a uma especialização da entidade empregado)? Alguns critérios para esta decisão são: Uma especialização deve ser usada quando sabe-se que as classes especializadas de entidades possuem propriedades (atributos, relacionamentos, generalizações, especializações) particulares. O sexo do empregado é melhor modelado como atributo de empregado, caso não existam propriedades particulares de homens e mulheres a modelar na realidade considerada. Práticas e heurísticas para Modelar Diagrama Entidade Relacionamento (DER) Modelo formal, preciso e não ambíguo; Objetivo: projetar um banco de dados, ou seja, não necessariamente introduzir todas as restrições; Descrição abstrata das estruturas do banco de dados relacional; Pode servir como insumo a uma ferramenta CASE na geração de bancos de dados relacionais; Todos os envolvidos devem ter perfeita compreensão; Modelos ER geralmente são subutilizados, sendo apenas utilizados para apresentar as ideias; Abordagem ER tem poder de expressão limitado: apresenta apenas algumas propriedades dos bancos de dados; Práticas e heurísticas para Modelagem Modelos diferentes podem ser equivalentes Quando expressam o mesmo, ou seja, modelam a mesma realidade; Para encontrar a equivalência deve-se ter o BD que foi projetado a partir do modelo; Verificação do modelo Modelo ER deve ser validado e verificado; controle de qualidade que procura garantir que o modelo usado para a construção do banco de dados gerará um bom produto; Um modelo, para ser considerado bom, deve preencher uma série de requisitos, como ser: completo ser correto não conter redundâncias. Verificação do Modelo Modelo deve ser correto não contém erros de modelagem; os conceitos de modelagem ER são corretamente empregados para modelar a realidade em questão; dois tipos de erros: Erros sintáticos : ocorrem quando o modelo não respeita as regras de construção de um modelo ER. Exemplos: associar atributos a atributos, o de associar relacionamentos a atributos, o de associar relacionamentos através de outros relacionamentos ou de especializar relacionamentos ou atributos. Erros semânticos: quando o modelo, apesar de obedecer as regras de construção de modelos ER (estar sintaticamente correto) reflete a realidade de forma inconsistente. Estabelecer associações incorretas. Usar uma entidade do modelo como atributo de outra entidade Usar o número incorreto de entidades em um relacionamento As regras de normalização de bases de dados relacionais apresentadas no próximo capítulo servem também para verificar a correção de modelos ER. Verificação do Modelo Modelo deve ser completo Deve fixar todas propriedades desejáveis do banco de dados. pode ser verificado por alguém que conhece profundamente o sistema a ser implementado. Verificar se todos os dados que devem ser obtidos do banco de dados estão presentes e se todas as transações de modificação do banco de dados podem ser executadas sobre o modelo. requisito é aparentemente conflitante com a falta de poder de expressão de modelos ER. Verificação do Modelo Modelo deve ser livre de redundâncias (relacionamentos redundantes) O relacionamento LOCALIZAÇÃO-FÁBR entre MÁQUINA e FÁBRICA é redundante. Um relacionamento é redundante, quando é possível eliminá-lo do diagrama ER, sem que haja perda de informações no banco de dados. O relacionamento LOCALIZAÇÃO-FÁBR, a associação entre entidades por ele expressa já está contida nos relacionamentos LOCALIZAÇÃODEPT e F-D Verificação do Modelo Modelo deve ser livre de redundâncias (atributos redundantes) O atributo código do departamento é redundante pois pode ser obtido através do acesso à entidade DEPARTAMENTO associada à entidade EMPREGADO através do relacionamento de LOTAÇÃO5. Já o atributo nº de empregados é redundante pois pode ser obtido através de um processo de contagem sobre o relacionamento LOTAÇÃO Obs: redundância controlada de dados (redundância de dados da qual programas e usuários têm conhecimento) devem também ser necessariamente evitada. Às vezes, construções redundantes em um banco de dados podem servir para aumentar a performance de operações de busca de informações no banco de dados, mas nem por isso devem aparecer no modelo conceitual do banco de dados. Verificação do Modelo Modelo deve refletir o aspecto temporal Ao iniciar a modelagem ER, a preocupação é obter um modelo que descreva os estados válidos e corretos do banco de dados. Atributos cujos valores modificam ao longo do tempo Verificação do Modelo Modelo deve refletir o aspecto temporal Ao iniciar a modelagem ER, a preocupação é obter um modelo que descreva os estados válidos e corretos do banco de dados. Relacionamentos que modificam ao longo do tempo Verificação do Modelo Consultas a dados referentes ao passado Muitas vezes as informações podem ser necessárias no futuro, realização de auditorias, tomadas de decisão; Planejar na modelagem quanto tempo os dados serão armazenados no banco de dados; Caso informações antigas fiquem no banco de dados, podem ser necessários atributos para indicar o status da informação, se atual ou antiga. Entidade isolada e entidade sem atributos Entidade isolada é uma entidade que não apresenta nenhum relacionamento com outras entidades. Em princípio, entidades isoladas não estão incorretas; Ocorrência de entidades isoladas em modelos na prática é rara e por isso deve ser investigada em detalhe, para verificar se não foram esquecidos relacionamentos. Entidade sem atributos Por que iriamos modelar sem atributos? Estratégia de Modelagem Fontes de informação: Descrição de dados existentes: Conhecido como Engenharia Reversa: obtém uma descrição a partir do produto pronto; Conhecimento das pessoas que conhecem o sistema: Não existem conhecimento dos dados; Estratégia de Modelagem Estratégias: Abordagem Top-Down: Modelagem Superficial (DER) Modelagem Detalhada Validação do Modelo Estratégia de Modelagem Abordagem inside-out Conceito importante vai adicionando novos conceitos Estratégia de Modelagem Abordagem bottom-up Engenharia Reversa Modelo Relacional Modelo Relacional Criado por Edgar Codd (matemático Britanico), nos anos 70; Desenvolveu o modelo de banco de dados relacional, quando era pesquisador no laboratório da IBM em San José; Em junho de 1970 publicou um artigo chamado "Relational Model of Data for Large Shared Data Banks" ("Modelo de dados relacional para grandes bancos de dados compartilhados") que foi publicado na Revista ACM ("Association for Computing Machinery") Vol. 13, No. 6, pp. 377–387. O modelo relacional chegou a ser implementado ainda em vida de Codd, pela própria IBM, num sistema de uso restrito chamado BS12; Utilizado nas empresas a partir de 1987. Conjunto de dados vistos segundo um conjunto de tabelas. Modelo Relacional Terminologia Tabela - Relação. Linha - Tupla. Coluna - Atributo. Tabela composta de linhas, colunas e chaves primárias e relacionadas através de chaves estrangeiras. Modelo Relacional Tabela: Conjunto não ordenado de linhas (tuplas), em que cada linha é composta de campos (valor de atributo) Cada campo é identificado por: Nome de campo (nome de atributo) Conjunto de campos das linhas de uma tabela que possuem o mesmo nome formam uma coluna. Modelo Relacional Comparação arquivo tradicional X Tabela banco de dados relacional Linhas da tabela não estão ordenadas, ordem de recuperação é arbitraria a menos que tenhamos uma ordenação explicita; SOLUÇÕES PARA RELACIONAMENTOS 1:N SOLUÇÕES PARA RELACIONAMENTOS 1:N (A entidade que tem cardinalidade máxima 1 é OBRIGATORIA) RELACIONAMENTO 1:N (A ENTIDADE TEM CARDINALIDADE MAXIMA 1 É OBRIGATÓRIA) DEPARTAMENTO LOTAÇÃO EMPREGADO (1,1) (?,n) Código Nome Nome Data lotação Código RELACIONAMENTO 1:N (A ENTIDADE TEM CARDINALIDADE MAXIMA 1 É OBRIGATÓRIA) ADIÇÃO DE COLUNAS Departamento (CodDept,Nome) Empregado (CodEmp,Nome,CodDept,DataLota) CodDept referencia Departamento DEPARTAMENTO LOTAÇÃO EMPREGADO (1,1) (0,n) Código Nome Nome Data lotação Código RELACIONAMENTO 1:N (A ENTIDADE TEM CARDINALIDADE MAXIMA 1 É OBRIGATÓRIA) TABELA PRÓPRIA Departamento (CodDept,Nome) Empregado (CodEmp,Nome, Lotacao(CodEmp,CodDept,DataLota) CodDept referencia Departamento CodEmp referencia Empregado DEPARTAMENTO LOTAÇÃO EMPREGADO (1,1) (0,n) Código Nome Nome Data lotação Código RELACIONAMENTO 1:N (A ENTIDADE TEM CARDINALIDADE MAXIMA 1 É OBRIGATÓRIA) DISCUSSÃO Fusão de tabelas Não se aplica Implicaria em redundância de dados de departamento, ou tabela aninhada Adição de colunas é melhor que tabela própria Menor número de chaves Menor número de junções Não há o problema de campos opcionais SOLUÇÕES PARA RELACIONAMENTOS 1:N (A entidade que tem cardinalidade máxima 1 é OPCIONAL) RELACIONAMENTO 1:N (A ENTIDADE TEM CARDINALIDADE MAXIMA 1 É OPCIONAL) FINANCEIRA FINACIAM VENDA (0,1) taxa de juros (?,n) nº de parcelas Código Nome Id Data RELACIONAMENTO 1:N (A ENTIDADE TEM CARDINALIDADE MAXIMA 1 É OPCIONAL) ADIÇÃO DE COLUNAS Financeira (CodFin,Nome) Venda (IdVend,Data,CodFin,NoParc,TxJuros) CodFin referencia Financeira FINANCEIRA FINACIAM VENDA (0,1) taxa de juros (0,n) nº de parcelas Código Nome Id Data RELACIONAMENTO 1:N (A ENTIDADE TEM CARDINALIDADE MAXIMA 1 É OPCIONAL) TABELA PROPRIA Financeira (CodFin,Nome) Venda (IdVend,Data) Fianciam (IdVend,CodFin,NoParc,TxJuros) IdVend referencia Venda CodFin referencia Financeira FINANCEIRA FINACIAM VENDA (0,1) taxa de juros (0,n) nº de parcelas Código Nome Id Data RELACIONAMENTO 1:N (A ENTIDADE TEM CARDINALIDADE MAXIMA 1 É OPCIONAL) DISCUSSÃO Implementação por tabela própria também é aceitável É melhor em relação a campos opcionais Perde em relação a junções e número de chaves QUADRO COMPARATIVO SOLUÇÕES PARA RELACIONAMENTOS N:N RELACIONAMENTO N:N ENGENHEIRO ATUAÇÃO PROJETO (0,n) (0,n) Código Nome Título Função Código RELACIONAMENTO N:N ENGENHEIRO ATUAÇÃO PROJETO (0,n) (0,n) Código Nome Título Função Código Engenheiro (CodEng,Nome) Projeto (CodProj,Título) Atuação (CodEng,CodProj,Função) CodEng referencia Engenheiro CodProj referencia Projeto SOLUÇÕES PARA RELACIONAMENTOS (GRAU MAIOR QUE 2) RELACIONAMENTO (GRAU MAIOR QUE 2) nome nome DISTRIBUIDOR CIDADE PRODUTO DISTRIBUIÇÃO (0,1) (0,n) (0,n) código código código nome data de início RELACIONAMENTO (GRAU MAIOR QUE 2) nome nome DISTRIBUIDOR CIDADE PRODUTO DISTRIBUIÇÃO (0,1) (0,n) (0,n) código código código nome data de início Não são definidas regras específicas O relacionamento é transformado em uma entidade São aplicadas regras de implementação relacionamentos binários RELACIONAMENTO (GRAU MAIOR QUE 2) DISTRIBUIDOR CIDADE PRODUTO (0,n) DISTRIBUIÇÃO (1,1) (1,1) (1,1) (0,n) (0,n) nome nome código código código nome data de início Produto (CodProd,Nome) Cidade (CodCid,Nome) Distribuidor (CodDistr,Nome) Distribuição (CodProd,CodDistr,CodCid,DataInicio) CodProd referencia Produto CodDistr referencia Distribuidor CodCid referencia Cidade SOLUÇÕES PARA GENERALIZAÇÃO / ESPECIALIZAÇÃO GENERALIZAÇÃO/ESPECIALIZAÇÃO Duas alternativas básicas uso de uma tabela para cada entidade uso de uma única tabela para toda hierarquia Outra alternativa (exótica) MENOS USO Subdivisão de entidade genérica GENERALIZAÇÃO/ESPECIALIZAÇÃO código nome código nome CREA EMPREGADO DEPARTAMENTO SECRETÁRIA ENGENHEIRO PROCESSADOR DE TEXTOS PROJETO DOMÍNIO PARTICIPAÇÃO LOTAÇÃO tipo de empregado nome carteira de habilitação CIC (1,1) (0,n) (1,n) (0,n) (0,n) (0,n) p RAMO DA ENGENHARIA (0,n) (1,1) MOTORISTA código código nome código nome GENERALIZAÇÃO/ESPECIALIZAÇÃO UMA TABELA POR HIERARQUIA Todas tabelas referentes às especializações são fundidas em uma única tabela Tabela contém: Chave primária correspondente ao identificador da entidade mais genérica Caso não exista, adicionar uma coluna Tipo Uma coluna para cada atributo da entidade genérica Colunas referentes aos relacionamentos dos quais participa a entidade genérica e que sejam implementados através da alternativa de adicionar colunas à tabela da entidade genérica Uma coluna para cada atributo de cada entidade especializada (opcional) Colunas referentes aos relacionamentos dos quais participa cada entidade especializada e que sejam implementados através da alternativa de adicionar colunas à tabela da entidade (campo opcional) GENERALIZAÇÃO/ESPECIALIZAÇÃO UMA TABELA POR HIERARQUIA Emp (CodigoEmp,Tipo,Nome,CIC,CodigoDept,CartHabil,CREA,CodigoRamo) CodigoDept referencia Depto CodigoRamo referencia Ramo Depto (CodigoDept, Nome) Ramo (CodigoRamo,Nome) ProcessTexto (CodigoProc,Nome) Dominio (CodigoEmp,CódigoProc) CodigoEmp referencia Emp CodigoProc referencia ProcessTexto Projeto (CodigoProj,Nome) Participação (CodigoEmp,CodigoProj) CodigoEmp referencia Emp CodigoProj referencia Projeto GENERALIZAÇÃO/ESPECIALIZAÇÃO UMA TABELA POR ENTIDADE ESPECIALIZADA Criar uma tabela para cada entidade que compõe a hierarquia Incluir a chave primária da tabela correspondente à entidade genérica, em cada tabela correspondente a uma entidade especializada GENERALIZAÇÃO/ESPECIALIZAÇÃO UMA TABELA POR ENTIDADE ESPECIALIZADA Emp (CódigoEmp,Tipo,Nome,CIC,CódigoDept) CódigoDept referencia Depto Motorista(CódigoEmp,CartHabil) CódigoEmp referencia Emp Engenheiro(CódigoEmp,CREA,CódigoRamo) CódigoEmp referencia Emp CódigoRamo referencia Ramo Depto (CódigoDept, Nome) Ramo (CódigoRamo,Nome) ProcessTexto (CódigoProc,Nome) Domínio (CódigoEmp,CódigoProc) CódigoEmp referencia Emp Código Proc referencia ProcessTexto Projeto (CódigoProj,Nome) Participação (CódigoEmp,CódigoProj) CódigoEmp referencia Engenheiro CódigoProj referencia Projeto GENERALIZAÇÃO/ESPECIALIZAÇÃO DISCUSSÃO Vantagens da implementação com tabela única Dados referentes à entidade genérica + dados referentes às especializações em uma única linha Minimiza junções Menor número de chaves Vantagens da implementação com uma tabela por entidade especializada Colunas opcionais apenas aquelas referentes a atributos que podem ser vazios do ponto de vista da aplicação. GENERALIZAÇÃO/ESPECIALIZAÇÃO SUBDIVISÃO DA ENTIDADE GENERICA Uma tabela para cada entidade especializada que não possua outra especialização (entidade folha da árvore) Tabela contém dados da entidade especializada + dados da entidade genérica GENERALIZAÇÃO/ESPECIALIZAÇÃO SUBDIVISÃO DA ENTIDADE GENERICA EmpOutros (CódigoEmp,Tipo,Nome,CIC,CódigoDept) CódigoDept referencia Depto Motorista(CódigoEmp, Nome,CIC,CódigoDept,CartHabil) CódigoDept referencia Depto Engenheiro(CódigoEmp, Nome,CIC,CódigoDept, CREA,CódigoRamo) CódigoDept referencia Depto CódigoRamo referencia Ramo Depto (CódigoDept, Nome) Ramo (CódigoRamo,Nome) ProcessTexto (CódigoProc,Nome) Domínio (CódigoEmp,CódigoProc) Código Proc referencia ProcessTexto Projeto (CódigoProj,Nome) Participação (CódigoEmp,CódigoProj) CódigoProj referencia Projeto GENERALIZAÇÃO/ESPECIALIZAÇÃO SUBDIVISÃO DA ENTIDADE GENERICA Desvantagem: Unicidade da chave primária não é garantida pelo SGBD deve ser garantida pela aplicação Não há como especificar ao SGBD restrições de integridade referenciais, que façam referência ao conjunto de empregados como um todo QUADRO RESUMO RELACIONAMENTO GRAU > 2 -> + relacionamento transformado em entidades e são utilizadas as regras dos relacionamentos binários. GENERALIZAÇÃO/ESPECIALIZAÇÃO -> ± uso de uma tabela para cada entidade ± uso de uma única tabela para toda hierarquia - Subdivisão de entidade genérica (exótica) Engenharia Reversa de Arquivos e normalização Sistemas raramente documentados, através de modelos conceituais Sistemas Legados Dados armazenados em arquivos de linguagem como BASIC, COBOL, MUMPS Bancos de dados da era pré-relacional IMS, ADABAS, DMS-II Como fazer manutenção de sistemas dessa forma ? Migrar o banco de dados Resposta: Engenharia Reversa DESCRIÇÃO DOS ARQUIVOS QUE COMPOEM O SISTEMA NORMALIZAÇÃO MODELO RELACIONAL NÃO NORMALIZADO DESCRIÇÃO DOS ARQUIVOS QUE COMPOEM O SISTEMA NORMALIZAÇÃO MODELO RELACIONAL NÃO NORMALIZADO INTEGRAÇÃO DE MODELOS MODELO RELACIONAL NORMALIZADO MODELO RELACIONAL NORMALIZADO TRANSFORMAÇÃO EM ER MODELO RELACIONAL INTEGRADO ELIMINAÇÃO DE REDUNDANCIAS DER DO SISTEMAS Engenharia Reversa de Arquivos e normalização 1º PASSO: REPRESENTACAO DA DESCRIÇÃO DE CADA ARQUIVO EXISTENTE NA FORME DE UM ESQUEMA DE TABELA RELACIONAAL NÃO-NORMALIZADA OBJETIVO: OBTER DESCRIÇOES INDEPENDENTE DO TIPO DE ARQUIVO QUE ESTA SENDO UTILIZADO. SAÍDA: MODELO RELACIONAL NÃO-NORMALIZADO. Engenharia Reversa de Arquivos e normalização 2º PASSO: NORMALIZAÇÃO DAS TABELAS RELACIONAIS OBJETIVOS: REAGRUPAR INFORMAÇÕES DE FORMA AA ELIMINAR REDUNDANCIAS E DADOS QUE POSSAM EXISTIR NOS ARQUIVOS; REAGRUPAR INFORMAÇÕES DE UMA FORMA QUE PERMITA A OBTENÇÃO DE UM MODELO ER. SAÍDA: ESQUEMA RELACIONAL DO BANCO DE DADOS DO SISTEMA. USANDO ENGENHARIA REVERSA É POSSIVEL OBTER O MODELO ER DO SISTEMA; Exemplo Fonte: Carlos Alberto Heuser Exemplo (Documento na Forma de Tabela não normalizada) Fonte: Carlos Alberto Heuser Exemplo (Modelo Relacional - desnormalizado) Proj (CodProj, Tipo, Descr, (CodEmp, Nome, Cat, Sal, DataIni, TempAI)) Tabelas aninhadas. Exemplo (Normalização) Conceito de forma normal: uma regra que deve ser obedecida por uma tabela para que ela seja considerada “bem projetada”. Cada forma normal deixa o modelo mais “rigido”. 192 SGBD + Banco de Dados Independência de dados Consistência de dados 193 Consistência de Dados SGBDRegras de Integridade Validade Completeza Consistência 194 Consistência de Dados O controle de consistência pode ser exercido: Pelo gerenciador; Pelos aplicativos; Pela própria construção do sistema. 195 Consistência de Dados Pela própria construção do sistema. Controlar a construção do sistema através da criação de tabelas segundo regras que garantam a manutenção de certas propriedades. As tabelas que atendem a um determinado conjunto de regras, diz-se estarem em uma determinada forma normal. 196 Formas Normais Primeira Forma Normal Uma relação está na 1a. forma normal quando todos os seus atributos são atômicos e monovalorados. Nome Idade DataMatrícula ? DataNasc São atômicos? 197 Formas Normais Primeira Forma Normal Uma relação está na 1a. forma normal quando todos os seus atributos são atômicos e monovalorados. Nome Idade DataMatrícula ! DataNasc São atômicos? DataNasc e DataMatrícula serão atributos atômicos se não forem utilizadas “partes” das datas em outras relações do Banco de Dados. 198 Formas Normais Primeira Forma Normal Atributos multivalorados 1) Quando a quantidade de valores é pequena e conhecida a priori; Substitui-se o atributo multivalorado por um conjunto de atributos de mesmo domínio, cada um representando a ocorrência de um valor. 199 Formas Normais Primeira Forma Normal Atributos multivalorados. 2) 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. 200 Formas Normais Primeira Forma Normal Atributos multivalorados Nome Idade DataNasc Telefone Quantos números de telefone? 201 Formas Normais Primeira Forma Normal Atributos multivalorados Nome Idade DataNasc Telefone Nome Idade DataNasc fone1 fone2 fone3 Se forem 3 números 202 Formas Normais Primeira Forma Normal Atributos multivalorados Nome Idade DataNasc Telefone Nome Idade DataNasc Nome Telefone Se forem muitos números 203 Formas Normais Dependências Funcionais Se o valor de um conjunto de atributos A permite descobrir o valor de um outro conjunto B, dizemos que A determina funcionalmente B, ou que B depende de A, e denotamos: A B 204 Formas Normais Dependência Funcional Parcial Se A for chave da relação e o valor de um subconjunto de atributos de A permite descobrir o valor de um outro conjunto B, dizemos que B possui dependência funcional parcial em relação a A. 205 Formas Normais Atributo Primo Todo atributo que pertence a uma chave candidata é denominado primo. O que é mesmo chave candidata? 206 Formas Normais Segunda Forma Normal Uma relação está na 2a. forma normal quando: está na 1a. F.N. e; todos os seus atributos que não são primos, não dependem parcialmente de qualquer chave candidata da relação. 207 Formas Normais Segunda Forma Normal Número Sigla Número -Horas Sala Sigla Número-Horas Número,Sigla Sala, Número-Horas 208 Formas Normais Segunda Forma Normal Número Sigla Número -Horas Sala Número Sigla Sala Sigla Número-Horas 209 Formas Normais Segunda Forma Normal Evita: Inconsistências devido a duplicidade de informações Perda de dados em operações de remoções / alteração na relação 210 Formas Normais Segunda Forma Normal Número Sigla Número-Horas Horário DC122 1 2 10:00 14:00 8:00 15:00 16:00 1 2 1 2 2 2 4 3 DC134 DC122 DC189 DC189 Sigla Número-Horas Número,Sigla Sala, Número-Horas Valores Inconsistentes 211 Formas Normais Segunda Forma Normal Número Sigla Número-Horas Horário DC122 1 2 10:00 14:00 8:00 15:00 16:00 1 2 1 2 2 2 4 3 DC134 DC122 DC189 DC189 Se não houver turmas de uma determinada disciplina em um semestre, perde-se a informação sobre o Número de Horas!!! 212 Formas Normais Terceira Forma Normal Uma relação está na 3a. Forma normal quando: Está na 2a. F.N. E; Todos os seus atributos não primos são dependentes não transitivos de uma chave candidata. Mas o que é Dependência Funcional Transitiva? 213 Formas Normais Terceira Forma Normal Seja a relação R(X, Y, A), A é transitivamente dependente de X, se existe Y tal que: Mas o que é Dependência Funcional Transitiva? X Y, Y não determina X YA A XY 214 Formas Normais Terceira Forma Normal Número Sigla Sala Prédio Número, Sigla Sala, Prédio Sala Prédio 215 Formas Normais Terceira Forma Normal Número Sigla Sala Prédio Número, Sigla Sala Sala Prédio Número Sigla Sala Sala Prédio 216 Formas Normais Terceira Forma Normal Evita: inconsistências devido a duplicidade de informações perda de dados em operações de remoções / alteração na relação 217 Formas Normais Terceira Forma Normal Valores Inconsistentes!!!!! Número, Sigla Sala, Prédio Sala Prédio Número Sigla Prédio Sala DC102 1 2 3 4 12 4 12 1 2 1 E1 C2 E1 C2 C2 DC155 DC102 DC104 DC155 218 Formas Normais Terceira Forma Normal Número, Sigla Sala, Prédio Sala Prédio Número Sigla Prédio Sala DC102 1 2 3 4 12 4 12 1 2 1 E1 C2 E1 C2 C2 DC155 DC102 DC104 DC155 Se não houver aula em uma determinada sala nesse semestre perde-se a informação sobre qual prédio contém a tal sala. Novembro de 2003 Profa. Marilde Santos 219 Formas Normais Terceira Forma Normal Uma relação está na 3a. Forma normal quando: Está na 2a. F.N. e; Todos os seus atributos não primos são dependentes não transitivos de uma chave candidata. Em outras palavras, uma relação está na 3FN se: para toda dependência funcional X A de R, X for superchave ou A for atributo primo 1FN - Primeira forma normal Tabela na 1FN quando não contém tabelas aninhadas. Construir uma única tabela com redundância de dados; ProjEmp (CodProj, Tipo, Descr, CodEmp, Nome, Cat, Sal, DataIni, TempAi) Construir uma tabela para cada tabela aninhada Proj (CodProj, Tipo, Descr) ProjEmp (CodProj, CodEmp, Nome, Cat, Sal, DataIni, TempAI 1FN - Primeira forma normal Em geral a Melhor alternativa é a primeira, porém tem de avaliar. Segunda alternativa: pode perder relações entre informações. Neste caso, tínhamos poucas tabelas aninhadas facilita a criação de outras tabelas. 1FN – Primeira Forma Normal (Decomposição de tabelas) 1º Passo: Criada uma tabela na 1FN referente a tabela não normalizada e que contem apenas as colunas com valores atômicos, sem tabelas aninhadas. Chave primaria é idêntica a chave da tabela não normalizada. 2º Passo: Cada tabela aninhada é criada uma tabela na 1º FN Chave primaria: são as chaves de cada uma das tabelas que estavam aninhadas. colunas: colunas da própria tabela aninhada; Atenção nas chaves primárias 1º Passo – Tomar como parte da chave primária da tabela na 1FN a chave primária da tabela não normalizada; 2º Passo – Verificar se esta chave é suficiente para identificar as linhas da tabela na 1FN Caso positivo esta será a chave primaria da tabela. Caso negativo, deve-se procurar as colunas que identificam unicamente as linhas da tabela (Tupla); SQL SQL – História No início dos anos 70, o trabalho produtivo do colega de pesquisa da IBM Dr. E. F. Codd levou ao desenvolvimento de um produto modelo de dado relacional chamado SEQUEL ou Linguagem de Consulta em Inglês Estruturado (Strutucred English Query Language). SEQUEL ultimamente se transformou em SQL ou Linguagem de Consulta Estruturada (em inglês: Structured Query Language). IBM, junto com outros fornecedores de banco de dados relacionais, queria um método padronizado para acessar e manipular dados em um banco de dados relacional. SQL – História Embora IBM tenha sido a primeira a desenvolver a teoria de banco de dados relacio- nal, a Oracle foi a primeira a comercializar a tecnologia. Através do tempo, SQL se provou popular o suficiente no mercado de trabalho para atrair a atenção do American National Standards Institute (ANSI), que lançou padrões para SQL em 1986, 1989, 1992, 1999, 2003 e 2006. E SQL - Classes de Instruções SQL Linguagem de manipulação de dados (DML), em inglês, Data Manipulation Language Fornece comandos de manipulação de dados específicos como SELECT, INSERT, UPDATE e DELETE. Linguagem de definição de dados (DDL), em inglês, Data Definition Language Contém comandos que lidam com a acessibilidade e manipulação de objetos de Banco de dados, incluindo CREATE e DROP Linguagem de Controle de dados (DCL), em inglês, Data Control Language Contém os comandos relacionados com permissões GRANT e REVOKE SQL - Dialetos Introduziram comandos procedurais para suportar a funcionalidade de uma linguagem de programação mais completa. Por exemplo, estas implementações procedural contêm comandos para lidar com erros, linguagem de controle de fluxo, comandos condicionais, comandos para manipular variáveis, suporte para arrays e muitas outras extensões. PL/SQL Encontrado no Oracle. PL/SQL significa Procedural Language/SQL e contém muitas similaridades com a linguagem Ada. Transact-SQL Usados por ambos Microsoft SQL Server e Sybase Adaptive Server como Microsoft e Sybase mudaram de uma plataforma comum que eles compartilhavam no inicio dos anos 90, suas implementações de Transact-SQL também divergiram. PL/pgSQL Dialeto e extensões SQL implementadas no PostgreSQL. As iniciais significam Procedural Language/PostgreSQL. SQL – Exemplo de Objetos Manipuláveis Database (Base de Dados) Table (Tabelas) Index (Indíces) View (Visões) Stored Procedures (Procedimentos) Triggers (Gatilhos) Domain (Domínios) Generator (Variáveis Globais) Exception (Excecões) User (Usuários) Role (Grupos de Usuários) Conjuntos de Comandos da Linguagem SQL A Linguagem SQL pode ser dividida em 5 conjuntos de comandos: Recuperação de dados: comando SELECT Linguagem de manipulação de dados (DML - Data Manipulation Language): comandos para inserções (INSERT), atualizações (UPDATE) e exclusões (DELETE) Linguagem de definição de dados (DDL - Data Definition Language): comandos para criação e manutenção de objetos do banco de dados: CREATE, ALTER, DROP, RENAME e TRUNCATE Linguagem para controle de transações: COMMIT, ROLLBACK e SAVEPOINT Linguagem para controle de acesso a dados: GRANT e REVOKE SQL Data Definition Language SQL - DDL (DDL) – Ferramentas fazem o trabalho da criação dos DDL CREATE ALTER DROP Linguagem de definição de dados (DDL) A linguagem de definição de dados permite a criação, manutenção e eliminação de objetos do banco de dados: tabelas visões índices seqüências sinônimos Convenções de Nomes Devem começar com uma letra Pode ter de 1 a 30 caracteres Pode conter somente A-Z, a-z, 0-9, _, $ e # Os nomes devem ser únicos por usuário Não podem ser utilizadas palavras reservadas (salvo se entre aspas) Tipos de Dados Básicos CHAR(tamanho): seqüência de caracteres de tamanho fixo VARCHAR(tamanho): seqüência de caracteres de tamanho variável NUMBER(total, decimais): valores numéricos DATE: data e hora Tipos de Dados para Caracteres CHAR(tamanho [BYTE | CHAR]): até 2000 bytes Alocação de bytes é o padrão, mas podem ser alocados CHARs, em formato Unicode NCHAR(tamanho): até 2000 bytes em alocação Unicode (2 ou 3 bytes por caracter) VARCHAR2 ou VARCHAR(tamanho [BYTE | CHAR]): até 4000 bytes NVARCHAR2: até 4000 bytes CLOB: até 232 – 1 bytes (4 GB) NCLOB: até 232 – 1 bytes (4 GB) em alocação Unicode LONG: até 231 – 1 bytes (2 GB), mantido para compatibilidade Tipos de Dados para Data e Tempo DATE: ano, mês, dia, hora, minuto, segundo, armazenados em 7 bytes INTERVAL DAY (precisão) TO SECOND (precisão): intervalos entre duas datas em dias, horas, minutos e segundos INTERVAL (precisão) YEAR TO MONTH: intervalo entre duas datas em anos e meses TIMESTAMP (precisão): valores de instantes de tempo com precisão de até 9 casas decimais nos segundos TIMESTAMP (precisão) WITH TIME ZONE: inclui a informação do fuso horário TIMESTAMP (precisão) WITH LOCAL TIME ZONE: recupera a informação ajustada ao fuso horário local Tipos de Dados Binários BLOB: até 232 – 1 bytes (4 GB) de dados binários armazenados no banco de dados BFILE: até 232 – 1 bytes (4 GB) de dados binários armazenados em arquivos externos RAW (tamanho): armazena até 2000 bytes, mantido para compatibilidade LONG RAW: até 231 – 1 bytes (2 GB) , mantido para compatibilidade Objeto Database Arquivo que contém a descrição dos dados, metadados, esquema do banco de dados; Contém todos os objetos criados; Contém os dados; Detém a segurança da estrutura completa; Create database nome_banco Objeto Domínio São tipos de dados criados para evitar redundâncias de tipos e facilitar a manutenção do Banco de Dados; CREATE DOMAIN d_logradouro VARCHAR(40); CREATE DOMAIN d_numero VARCHAR(6); CREATE DOMAIN d_cep VARCHAR(8); CREATE DOMAIN d_bairro VARCHAR(30); CREATE DOMAIN d_complemento VARCHAR(20); CREATE DOMAIN d_fone VARCHAR(10); CREATE DOMAIN d_cpf VARCHAR(11); CREATE DOMAIN d_rg VARCHAR(15); CREATE DOMAIN d_email VARCHAR(50); Objeto Domínio - CREATE CREATE DOMAIN d_logico VARCHAR(1) CHECK (VALUE IN (‘S’, ‘N’)); CREATE DOMAIN d_sexo VARCHAR(1) CHECK (VALUE IN (‘M’, ‘F’)); CREATE DOMAIN d_estado_civil VARCHAR(15) CHECK (VALUE IN (‘CASADO’, ‘SOLTEIRO’,‘DESQUITADO’, ‘SEPARADO’, ‘OUTROS’)); CREATE DOMAIN d_data_atual AS DATE DEFAULT 'NOW‘ NOT NULL; CREATE DOMAIN d_comentarios AS BYTEA NOT NULL; CREATE DATABASE CREATE DATABASE dbname; CREATE TABLE CREATE TABLE table_name ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... ); CREATE TABLE CONSTRAINTS CREATE TABLE table_name ( column_name1 data_type(size) constraint_name, column_name2 data_type(size) constraint_name, column_name3 data_type(size) constraint_name, .... ); NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK DEFAULT Constraint – NOT NULL NOT NULL A restrição de não nulo simplesmente especifica que uma coluna não deve assumir o valor nulo. Explicita Implicita CREATE TABLE distribuidora ( id integer CONSTRAINT id_nao_nulo NOT NULL, nome varchar(40) NOT NULL ); CREATE TABLE distribuidora ( id integer NOT NULL, nomevarchar(40) NOT NULL ); Constraint - Unique UNIQUE As restrições de unicidade assegurar que os dados contidos numa coluna ou um grupo de colunas é única no que diz respeito a todas as linhas da tabela . Explicita Implicita CREATE TABLE products ( product_no integer CONSTRAINT must_be_different UNIQUE, name text, price numeric ); CREATE TABLEproducts( product_nointeger, name text, price numeric, UNIQUE (product_no) ); CREATE TABLEproducts( product_nointeger UNIQUE, name text, pricenumeric ); Constraint - Check Uma restrição de verificação é o tipo mais genérico de restrição . Ele permite que você especificar que o valor de uma determinada coluna devem satisfazer uma expressão booleana ( valor de verdade ) . Explicita Implicita CREATE TABLE products ( product_no integer, name text, price numeric CONSTRAINT positive_price CHECK (price > 0) ); CREATE TABLEproducts( product_nointeger, name text, price numeric CHECK (price > 0) ); CREATE TABLE products ( product_nointeger UNIQUE, name text, pricenumeric ); Constraint – Primary Key PRIMARY KEY Tecnicamente, uma restrição de chave primária é simplesmente a combinação da restrição de unicidade com a restrição de não-nulo. Explicita Implicita CREATE TABLE products ( product_no integer CONSTRAINT NOME_CONSTRAINT PRIMARY KEY, name text, price numeric ); CREATE TABLEproducts( product_nointeger PRIMARY KEY, nametext, pricenumeric ); Constraint – Foreign Key FOREIGN KEY A restrição de chave estrangeira especifica que os valores em uma coluna ( ou um grupo de colunas) deve corresponder aos valores constantes de uma linha de outra tabela. Dizemos isto mantém a integridade referencial entre duas tabelas relacionadas . Explicita Implicita CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer CONSTRAINT NOME_CONSTRAINT REFERENCES products (product_no), quantity integer ); CREATE TABLEorders( order_idinteger PRIMARY KEY, product_nointeger REFERENCES products (product_no), quantityinteger ); Comando DROP TABLE Permite a eliminação de uma tabela: DROP TABLE nome_da_tabela [CASCADE CONSTRAINTS]; drop table tipos_telefones cascade constraints; Comando TRUNCATE TABLE Permite a eliminação de todas as linhas de uma tabela, de forma rápida e liberando o espaço alocado, sem possibilitar a execução de rollback: TRUNCATE TABLE nome_da_tabela; ALTER Adicionando nova coluna na tabela ALTER TABLE nome_tabela ADD nova_coluna char(50); Excluindo coluna na tabela ALTER TABLE distributors DROP COLUMN address RESTRICT; Modificando o tipo de dado de uma coluna ALTER TABLE nome_tabela ALTER nova_coluna VARCHAR(100); Renomeando uma coluna ALTER TABLE distributors RENAME COLUMN address TO city; Comando ALTER TABLE Permite a alteração de uma tabela ALTER TABLE nome_da_tabela [ADD definição de coluna,] [MODIFY definição de coluna,] [DROP COLUMN nome,] [RENAME COLUMN antigo TO novo,] [ADD definição de constraint,] [MODIFY CONSTRAINT definição de constraint,] [DROP CONSTRAINT nome,] [RENAME CONSTRAINT antigo TO novo,] [ENABLE | DISABLE constraint,] [RENAME TO novo_nome]; Exemplo alter table pedidos add valor_total number(10,2); alter table enderecos modify cod_endereco number(3); alter table autores drop column descricao; alter table clientes rename column cod_usuario to cod_cliente; alter table produtos add constraint chk_prod_prazo check(prazo between 3 and 30); Exemplo alter table produtos add constraint chk_prod_prazo check(prazo between 3 and 30); alter table produtos drop constraint chk_prod_importado; Exemplo alter table enderecos modify cod_endereco number(3); alter table produtos add constraint chk_prod_prazo check(prazo between 3 and 30); alter table usuarios disable constraint pk_usuarios; alter table usuarios enable constraint pk_usuarios; alter table pedidos_produtos rename to itens_pedidos; SQL Data Manipulation Language SQL - DML INSERT UPDATE DELETE SELECT INSERT Usado para adicionar novas linhas em uma tabela. Sintaxe: INSERT INTO NOME_DA_TABELA (COL1, COL2, ...) VALUES (VALOR1, VALOR2, ...) INSERT IMPLICITO INSERT INTO FILME VALUES (1, ‘MATRIX’, '1999-03-31', ‘Sci-fi', ‘136 minutos'); INSERT EXPLICITO INSERT INTO FILME (ID, TITULO, LANCAMENTO, DURACAO) VALUES (1, ‘MATRIX’, '1999-03-31', ‘Sci-fi', ‘136 minutos'); UPDATE Usado para modificar os valores das colunas de uma ou mais linhas. SINTAXE: UPDATE NOME_TABELA SET COL1 = VALOR1, COL2 = VALOR2, ... EXEMPLO: UPDATE CARGO SET SALARIO_MIN = 788; UPDATE CARGO SET SALARIO_MAX = SALARIO_MAX*1.1 WHERE NIVEL_GRADUACAO = `M`; DELETE Usado para remover uma ou mais linhas. DELETE FROM NOME_TABELA; EXEMPLO: DELETE FROM CARGO; DELETE FROM CARGO WHERE NIVEL_GRADUACAO = `P`; SELECT Sintaxe: SELECT coluna1, coluna2, ...., coluna n FROM nome_tabela WHERE condição Recuperando todas as colunas e linhas de uma tabela SELECT * FROM nome_tabela; Operadores Aritméticos (listados de acordo com a precedência) Operador Descrição / Dividir * Multiplicar - Subtrair + Adicionar Obs: Utiliza-se parênteses para criar a sua prioridade. EXEMPLO: CALCULA O SALARIO COM AUMENTO DE 10% SELECT SALARIO*1.1 FROM EMPREGADO; SELECT Operadores Lógicos básicos de comparação Operador Descrição = Igual > Maior do que >= Maior do que ou igual > Menor do que <= Menor do que ou igual != ou <> Diferente de SINTAXE: WHERE coluna operador valor EXEMPLO: SELECT NOME FROM EMPREGADO WHERE SALARIO >= 5000; SELECT NOME FROM EMPREGADO WHERE UF = ‘PE’; SELECT Operadores de comparação avançados Operador Descrição BETWEEN Entre dois valores inclusive IN Compara a qualquer um de uma lista de valores (conjunto) LIKE Compara a um padrão de caractere IS NULL Compara se a contem nulo SINTAXE: WHERE coluna operador valor SELECT BETWEEN Usado para recuperar linhas com base em uma FAIXA de valores. Ex: Recuperar o nome dos empregados cujo salário esteja entre 2500 e 5000. SELECT nome FROM EMPREGADO WHERE salario BETWEEN 2500 and 5000; IN Usado para recuperar linhas com base em uma LISTA de valores. Ex: Recuperar o nome dos empregados que moram em PE,PB,RN SELECT nome FROM EMPREGADO WHERE salario IN (‘PE’,’PB’,’RN’); SELECT LIKE Usado para executar pesquisas especiais quando você não sabe o valor exato que está pesquisando, para tanto, usa-se % que recupera qualquer sequencia de zero ou mais caracteres. Ex: Recuperar o nome dos empregados que iniciam por JO SELECT nome FROM EMPREGADO WHERE nome LIKE ‘JO%’; IS NULL Usado para executar pesquisas em colunas que não tiveram preenchimento, configurando valor nulo Ex: Recuperar o nome dos empregados que não possuem cargo SELECT nome FROM EMPREGADO WHERE cargo IS NULL; SELECT Operadores lógico Operador Descrição NOT Retorna verdadeiro(true) se a condição seguinte for falsa (false) AND Retorna verdadeiro (true) se as condições componentes forem verdadeiras (true) OR Retorna verdadeiro (true) se cada condição de componentes for verdadeira (true) Ex: Recuperar o nome dos empregados que NÃO moram em PE,PB,RN SELECT nome FROM EMPREGADO WHERE salario NOT IN (‘PE’,’PB’,’RN’); EXEMPLO Crie a tabela CARGO para o modelo acima, utilizando as seguintes regras: Id, nome, mínimo e maximo não poderão ser nulo; Id é uma chave primaria; MINIMO e MAXIMO possuem valor padrão de 500 e 1000 respectivamente. EXEMPLO (CRIAÇÃO DA TABELA) CREATE TABLE CARGO( ID INTEGER NOT NULL PRIMARY KEY, NOME VARCHAR(50) NOT NULL, MINIMO NUMERIC NOT NULL DEFAULT 500, MAXIMO NUMERIC NOT NULL DEFAULT 1000, ABREVIACAO CHAR(1) ) EXEMPLO Para a tabela criada insira os seguintes dados: Insira um cargo IMPLICITAMENTE com: Id: 1 Nome do cargo: Testador Minimo: 2000 Maximo: 5000 Abreviacao: T Insira um cargo EXPLICITAMENTE com: Id: 2 Nome do cargo: Arquiteto Minimo: 5000 Maximo: 10000 Abreviacao: A Insira um cargo com: Id: 3 Nome do cargo: Desenvolvedor Minimo: 4000 Maximo: 8000 Abreviacao: De Insira um cargo com: Id: 4 Nome do cargo: Redes Minimo: 4000 Maximo: 8000 Abreviacao: R EXEMPLO (RESOLUÇÃO) 1º INSERT INTO CARGO VALUES( 1, 'TESTADOR', 2000, 5000, 'T'); 2º INSERT INTO cargo(id, nome, minimo, maximo, abreviacao) VALUES (2, 'ARQUITETO', 5000, 10000, 'A'); SQL Funções de Grupo Funções de Grupo Funções de grupo operam sobre conjuntos de linhas. Elas retornam resultados baseados sobre um grupo de linhas, antes que um resultado por linha tenha retornado como uma função de linha única. Como padrão todas as linhas de um tabela são trilhadas como um grupo. A clausula GROUP BY da declaração do SELECT é usada para agrupar as linhas em menores grupos. Agrupamentos e função de grupo Aqrupando os Resultados da Querv Outro recurso do comando SELECT é a possibilidade de agruparmos nossos dados, utilizando a cláusula GROUP BY. Sintaxe: SELECT nome da coluna [ , nome da coluna] FROM nome da tabela WHERE condição GROUP BY expressão onde expressão especifica as colunas cujos valores determinam a base para o grupo de linhas; group by produz uma linha sumarizada para cada grupo de linhas selecionado. Após utilizarmos a cláusula GROUP BY dividir as linhas de uma tabela em um grupo menor. Funções de grupo devem ser usadas para resumir informações por cada grupo. Funções de Grupo AVG (x) Retorna o valor médio da coluna x. Exemplo: AVG (salario) Ignora os valores nulos. MAX (x) Retorna o valor máximo da coluna x. Exemplo: MAX (salario) Ignora os valores nulos. MIN (x) Retorna o valor mínimo da coluna x. Exemplo: MIN (salario) Ignora os valores nulos. Funções de Grupo SUM (x) Retorna a soma da coluna x. Exemplo: SUM (salario) Ignora os valores nulos. COUNT (x) Retorna o número de valores não nulos da coluna x. Exemplo: COUNT (perc_comissao) COUNT (* ) Retorna o número de linhas de uma tabela. Exemplo: COUNT (*) Considera os valores nulos. Distinct DISTINCT faz uma função de grupo considerar valores não duplicados; ALL considera todos os valores. Se omitida a consulta considera ALL como default; Restringindo dados agrupados A Cláusula HAVING A cláusula HAVING tem função semelhante a cláusula WHERE, que é ser o elemento de declaração do Join entre resultados agrupados. Quando temos um comando GROUP BY, o SQL checa se existe pelo menos uma função de agrupamento assim como um elemento agrupador dos dados, caso contrário retornará um erro. O elemento agrupador, por sua vez, tem que estar referenciado na cláusula GROUP BY e caso o resultado de uma função de agrupamento deva ser submetido a comparação, utiliza-se a cláusula HAVING. Junção de Tabelas Junção de Tabelas Um comando SELECT pode fazer uma consulta que traz dados de duas ou mais tabelas. Esse é um processo chamado de junção [join]. As tabelas têm uma coluna em comum que é usado para fazer as junções. Junção de Tabelas Uma delas é a tradicional, a segunda é a Sintaxe ANSI. Na sintaxe tradicional, na lista do FROM as duas (ou mais) tabelas são especificadas, separadas por vírgulas. Na cláusula WHERE deve haver uma condição ligando as duas, a condição de junção [join condition]. Na lista de colunas do SELECT podem ser incluídos colunas de qualquer uma das tabelas. Veja um exemplo: select departamento.nome, funcionario.nome from funcionário, departamento where funcionario.coddepartamento = departamento.coddepartamento Junção de Tabelas A