Baixe o app para aproveitar ainda mais
Prévia do material em texto
1 Banco de Dados I Danielle Filgueiras 2 Banco de Dados O que é BD ? Conceitos de BD Histórico de Banco de Dados Composição de um Sistema de BD Linguagens de BD O que é um SGBD ? Objetivos do SGBD Níveis de Abstração de SGBD Usuários do SGBD Interfaces do SGBD Tipos de SGBD SGBD x SGA Modelo de Dados 3 Banco de Dados Arquitetura do BD Níveis de Modelagem Modelo Entidade – Relacionamento Extensões do MER Mapeamento MER x DER Modelagem com ferramenta CASE Modelo Relacional Normalização Desnormalização Fragmentação de dados Scripts 4 Referências Fundamentals of Database System – Elmarsi/Navathe Introdução a Sistemas de Banco de Dados – C.J.Date Sistemas de Banco de Dados - Sylberchartz 5 O que é BD ? 6 O que é BD ? 7 Conceitos de Banco de Dados 8 Conceitos de Banco de Dados (cont.) CHU- 1983 Um banco de dados é um conjunto de arquivos relacionados entre si. DATE - 1985 Um banco de dados é uma coleção de dados operacionais armazenados usados pelos sistemas de aplicação de uma determinada organização. 9 Conceitos de Banco de Dados (cont.) ELMARSI & NAVATHE - 1999 Um banco de dados é uma coleção de dados relacionados, não isolados. ENGLES Um banco de dados é uma coleção de dados operacionais usados pelos sistemas aplicativos de uma empresa. 10 Conceitos de Bancos de Dados Um BD representa aspectos do mundo real Um BD é projetado, construindo e os dados são nele armazenados com algum propósito Um BD é definido e mantido por uma coleção de programas (SGBD) Sistema de Bando de Dados = Dados + Programas (SBD = BD + SGBD) 11 Conceitos de Banco de Dados (cont.) Dado X Informação Informação é um ou mais dados processados, tratados, lapidados Dado: A 4 L 8 L 3 D 5 I 7 E 1 E 8 N Informação: D A N I E L L E 4 8 8 3 8 7 5 Processamento Dado Informação 12 Estratégico Conceitos de Banco de Dados (cont.) Tipo de Informação Tático Operacional Projeções Comparações AD-HOC Comparações Resumos Informações detalhadas Informações Precisas Nível de Decisão 13 AS = Análise de Dados + Análise de Funções Conceitos de Banco de Dados (cont.) Análise de Dados T Funções Dados Uns preferem pela “AD” outros preferem pela “AF”, mas todos já concordam: deve-se fazer Análise de Dados “AD”” Por onde começar ? 14 Conceitos de Banco de Dados (cont.) Análise de Dados Ato de Construir modelos conceituais de dados, através de abstrações, a partir do mundo real, buscando simplificação rumo à implementação física Conceitual Lógico Físico Mundo Real 15 Conceitos de Banco de Dados (cont.) Análise de Sistemas Análise de Dados (DER) Análise de Funções (DFD) Análise de Sistemas + 16 Conceitos de Banco de Dados (cont.) Análise de Dados Projeto Conceitual: Objetivo: Definição do problema, não da solução Dados: eliminação de redundância de dados Funções: Como descrever o problema do cliente ? 17 Conceitos de Banco de Dados (cont.) Análise de Dados Projeto Lógico: Dados: O que armazena e qual a estrutura ? Funções: Que informações o sistema deve fornecer ? Que dados o sistema deve receber e de quem ? Que transformações os dado devem sofrer para produzir as informações desejadas ? 18 Conceitos de Banco de Dados (cont.) Análise de Dados Projeto Físico: Dados: Como e onde armazenar os dados ? Funções: Como apresentar as saídas ? Como fornecer os dados ao sistema ? Como e quando executar os processos ? 19 Conceitos de Banco de Dados (cont.) Projeto de Banco de Dados Projeto Conceitual Modelagem de dados para descrever o problema, normalmente manual. Projeto Lógico Modelagem de dados com ferramenta CASE, não necessariamente precisa ser uma cópia do modelo conceitual. Definição de restrições de integridade. Projeto Físico Definição do armazenamento 20 Conceitos de Banco de Dados (cont.) Projeto Conceitual Declaração de Objetivos (DO) Lista de Eventos (LE) Modelo de Entidade relacionamento (MER) 21 Conceitos de Banco de Dados (cont.) Declaração de Objetivos (DO) Descrever de forma sucinta e objetiva, em uma página, a finalidade do sistema. Ex: O sistema de Compra e Venda de Mercadorias tem como objetivos automatizar de forma integrada as atividades de: Compras de mercadorias Vendas de mercadorias Controle de estoque Contabilização 22 Conceitos de Banco de Dados (cont.) Lista de Eventos Descreve todos os eventos que ocorrem, ocorreram ou poderão ocorrer no sistema, classificando-os em três tipos: Orientados ao fluxo Temporais Controle 23 Conceitos de Banco de Dados (cont.) Lista de Eventos Eventos orientados a fluxo de dados Cliente envia pedido Cliente faz pagamento Cliente devolve mercadoria Fornecedor envia mercadorias Fornecedor envia fatura Departamento de vendas define política de vendas 24 Conceitos de Banco de Dados (cont.) Lista de Eventos Eventos temporais O relatório de estatística de vendas deve amanhecer pronto às segundas-feiras Os lançamentos contábeis devem estar disponíveis para a Contabilidade no dia 01 de cada mês 25 Conceitos de Banco de Dados (cont.) Lista de Eventos Eventos de Controle Ao atingir o ponto de ressuprimento, emitir pedido ao fornecedor Ao exceder o limite de crédito, bloquear o cliente por excesso 26 Conceitos de Banco de Dados (cont.) Modelo de Entidade-Relacionamento Entidade: Um objeto do mundo real Atributo: Característica de uma entidade ou relacionamento Relacionamento: Associação entre entidades Alunos Disciplinas Cursa Nota Período Mat Nom Cod Des 27 Conceitos de Banco de Dados (cont.) Projeto Lógico Diagrama de Entidade relacionamento (DER) Dicionário de Dados (DD) 28 Conceitos de Banco de Dados (cont.) Diagrama de Entidade-Relacionamento Uma evolução do Modelo de Entidade- Relacionamento Alunos Disciplinas Nota Período Mat Nom Cod Des Cursa Mat Cod 29 Conceitos de Banco de Dados (cont.) Dicionário de Dados Uma visão organizada por linhas com tabelas e colunas do Diagrama de Entidade- Relacionamento Atributo Nulo ? Tamanho Cod_cli Não 8 Nom_cli Não 30 End_cli Não 40 Fone_cli Sim 9 Tabela Clientes 30 Conceitos de Banco de Dados BD = Dados + Metadados Um sistema de Banco de Dados contém, além dos dados normais, uma descrição completa desses dados num dicionário de dados (catálogo) Metadados ou Dicionário de Dados Abstração, ou seja, deixar o usuário tratar os dados em alto nível, enquanto o SGBD usa o DD para tratar os dados fisicamente (baixo nível) 31 Conceitos de Banco de Dados (cont.) Projeto Físico Projeto Detalhado de: Saídas Arquivos Entradas Controles no sistema Especificação de Programas 32 Histórico do Banco de Dados 33 Histórico do Banco de Dados (cont.) Desenvolvimento de software Até 1972: Construção Artística de Software (Artesanal) 1972: Programação Estruturada (Dijkstra) Foco: ao nível de programa Objetivos: Melhora a forma Facilita a leitura e manutenção Controla a complexidade Torna a programação mais científica 34 Histórico do Banco de Dados (cont.) Desenvolvimento de software 1976: Projeto Estruturado (Yordon / Constantine, Michael Jackson, Warnier – Orr) Foco: ao nível de problema Objetivos: Modularidade Padronização do projeto 35 Histórico do Banco de Dados (cont.) Desenvolvimento desoftware 1977: Análise Estruturada (Chris Ganentine, Tom de Marco) Foco: ao nível de sistema Objetivos: Desenvolver o modelo lógico Obter especificação funcional 36 Histórico do Banco de Dados (cont.) Desenvolvimento de software 1979: Análise de Dados Foco: ao nível de empresa Objetivos: Definir requisitos Sistema de informação gerencial Normalização Modelagem de Dados 37 Histórico do Banco de Dados (cont.) Desenvolvimento de software Anos 80: Técnicas Automatizadas Geradores de tela, de programa, de aplicação LG4, Query Language, CASE Engenharia da Informação Anos 90: Orientação a Objeto, Cliente / Servidor 38 Histórico de Banco de Dados (cont.) Arquivos Convencionais Arquivos convencionais servem a sistemas específicos (isolados) com pouco ou nenhum compartilhamento com outros sistemas difícil obtenção de informações gerenciais Técnicas de bancos de dados ressaltam o dado como um recurso importante na empresa, devendo ser: compartilhado, controlado de forma centralizada para se saber quem onde, e com que finalidade, o dado é usado 39 Histórico de Banco de Dados (cont.) Sistemas Isolados Aplicativos independentes, tanto em relação aos dados quanto as saídas Cada aplicação cuida apenas de seus arquivos, ignorando possíveis repetições Possível inconsistência de dados Programas levam em conta distribuição física dos dados nos arquivos: alteração no lay-out do registro significa alteração nos programas 40 Histórico de Banco de Dados (cont.) Num_conta, nome, endereço, sld_atual, sld_medio, cpf-cgc, dt_nasc Conta Corrente Cpf-cgc, nome, endereço, num_atual_cotas, dt_nasc Fundos de Investimentos Sistemas Isolados 41 Histórico de Banco de Dados (cont.) Sistemas Integrados Dados de uma mesma área são armazenados em um mesmo conjunto de arquivos Um sistema grava saída em arquivo em meio magnético a ser usado como entrada por outro sistema Alteração no layout de arquivos compartilhado implica alteração em programas de mais de um sistema 42 Histórico de Banco de Dados (cont.) nome, endereço, cpf-cgc, dt_nasc Conta Corrente num_atual_cotas, dt_nasc Fundos de Investimentos Sistemas Integrados Num_conta, sld_atual, sld_medio, cpd-cgc 43 Histórico de Banco de Dados (cont.) Sistemas que usam SGBD Integração de dados não apenas por área, mas por toda a Empresa Dados da empresa compartilhados por todos os sistemas Sistemas “enxergam” os dados independentemente de sua distribuição pelos arquivos Programas de aplicação ignoram detalhes de armazenamento/recuperação de dados Sem redundância de dados não há inconsistência 44 Histórico de Banco de Dados (cont.) nome, endereço, cpf-cgc, dt_nasc Conta Corrente num_atual_cotas, cpf-cgc Fundos de Investimentos Sistemas que usam SGBD Num_conta, sld_atual, sld_medio, cpd-cgc SGBD 45 Composição de um Sistema de Banco de Dados 46 Composição de um Sistema de Banco de Dados Dados: em um ou mais arquivos Metadados: dicionário de dados Software: SGBD (interface entre usuários e dados) Hardware: discos Usuários: DBA, Programadores de Aplicação, Usuários Finais 47 Composição de um Sistema de Banco de Dados (cont.) SGBD dá suporte ao acesso dos usuários aos dados esconde do usuário detalhes de armazenamento dos dados Programador de Aplicação codifica programas batch ou on-line para manter o DB 48 Composição de um Sistema de Banco de Dados (cont.) DBA - Administrador de BD define: que dados manter no BD estrutura que deve ter o BD estratégias de acesso que esquema de backup adotar procedimentos de autorização como monitorar/sincronizar/reorganizar o BD 49 Composição de um Sistema de Banco de Dados (cont.) Usuário Final Acessa o BD via: query language programas de aplicação 50 Linguagens de Bando de Dados 51 Linguagens de Bancos de Dados DDL - Linguagem de definição de dados (create, drop, alter...) DML - Linguagem de manipulação de dados (insert, delete, update, ...) DCL - Linguagem de controle de acesso aos dados (grant, revoke, ...) QL - Linguagem de consulta (select) 52 Linguagens de Bancos de Dados (cont.) SDL - Linguagem de definição de armazenamento VDL - Linguagem de definição de visões 53 DDL Usada pelo DBA ou por qualquer projetista do BD Serve para definir os esquemas interno e conceitual quando a arquitetura é de 2 níveis Define a construção do esquema e a descrição do esquema de armazenamento 54 SDL Usada para especificar o esquema interno O mapeamento entre os dois esquemas pode ser feito com a SDL ou a DDL 55 VDL Usada para especificar as visões e faz o mapeamento com o esquema conceitual 56 DML Usada para manipular o banco de dados como inserir, apagar e alterar dados Alto nível ou não procedural Usada no terminal ou dentro de uma linguagem de programação Baixo nível ou procedural Usada dentro de uma linguagem de programação 57 O que é um SGBD ? 58 O que é um SGBD ? Sistema Gerenciador de Banco de Dados Software que administra o banco de dados e o dicionário de dados, garantindo as restrições de integridade, segurança e consistência do BD. 59 O que é um SGBD ? (cont.) Por que usar SGBD ? Abstração de dados Um SGBD permite uma representação conceitual dos dados evitando preocupação com detalhes de armazenamento de dados Suporte a múltiplas visões de dados Cada usuário acessa apenas o que lhe é dado direito de acessar 60 O que é um SGBD ? (cont.) Por que usar SGBD ? Controle de redundância Sem repetição de dados não há inconsistência Compartilhamento dos dados Acesso multi-usuário com controle de concorrência garantido Segurança contra acesso indevido Diferentes visões asseguram confidencialidade 61 O que é um SGBD ? (cont.) Por que usar SGBD ? Múltiplas Interfaces Acesso via menu, linguagem natural, query language, linguagem de programação Relacionamentos complexos Uma entidade pode se relacionar com outras de forma binária, ternária, ... Restrições de integridade Armazenadas no BD “enxugam” os programas de aplicação 62 O que é um SGBD ? (cont.) Por que usar SGBD ? Independência de dados A definição das estruturas de dados é armazenada fora dos programas de aplicação: alteração de estruturas dispensa recompilação de programas Recuperação do BD Feito através de check-points 63 Objetivos de um SGBD Controlar redundância Restringir o acesso Armazenamento persistente Inferência do BD usando regras de dedução Fornecer múltiplas interfaces Representação complexa de relacionamentos entre dados 64 Objetivos do SGBD (cont.) Garantir restrições de integridade Backup e Recuperação 65 Objetivos do SGBD (cont.) Vantagens no uso do SGBD Garantir padrões Redução no tempo de desenvolvimento Flexibilidade Disponibilidade de informações on-line 66 Objetivos do SGBD (cont.) Desvantagens no uso do SGBD Alto investimento em hardware, software e treinamento Modelo geral de definição e processamento de dados Excesso de segurança, controle de concorrência, recuperação e funções de integridade 67 Níveis de abstração de SGBD 68 Níveis de abstração de SGBD Arquitetura de 3 níveis (Visões) Visões de usuário Visão total do BD Visão do armazenamento ... 69 Níveis de abstração de SGBD (cont.) Arquitetura de 3 níveis (Esquemas) Sub-esquemas Esquema Conceitual Esquema Físico ... 70 Níveis de abstração de SGBD (cont.) Arquiteturade 3 níveis (CODASYL - 71) (Conference on Data System and Language) Visão Conceitual Físico ... 71 Níveis de abstração de SGBD (cont.) Arquitetura de 3 níveis (ANSI/SPARC 75) (American. Nacional Standart Institute / System Planning and Requirements Commitee) Externo Conceitual Interno ... 72 Níveis de abstração de SGBD (cont.) Nível Externo Visão de um subconjunto do nível conceitual (visão que um aplicativo tem dos dados) Visão que cada usuário tem do sistema Administrador da aplicação: usuário 73 Níveis de abstração de SGBD (cont.) Nível Conceitual Visão conceitual e global dos dados, representando o mundo real Consiste de entidades, relacionamentos e atributos Deve envolver todos os objetos do sistema de informação Administrador de dados: AD 74 Níveis de abstração de SGBD (cont.) Nível Interno Visão dos diversos registros armazenados em um banco de dados Alteração no nível interno (estrutura de armazenamento) não deve afetar o modelo conceitual Administrador de Banco de Dados: DBA 75 Usuários do SGBD 76 Usuários do SGBD Administradores do BD Projetistas de BD usuários finais Casual Ingênuos Sofisticados Isolados 77 Usuários do SGBD (cont.) Analistas de Sistemas Programadores Desenvolvedores de ferramentas Operadores Pessoal de manutenção 78 Interfaces do SGBD Interfaces baseadas em menus Interfaces gráficas Interfaces baseadas em formulários Interfaces de linguagem natural Interfaces para usuários sem experiência Interfaces para DBA 79 Utilitários do SGBD Carga de arquivos existentes Backup/Recovery Reorganização de arquivos Monitoramento de performance Software de comunicação 80 Tipos de SGBD Hierárquico Rede Relacional Objeto-Relacional Orientado-Objeto Outros 81 Tipos de SGBD: Hierárquico Lógico Entidades implementadas como segmentos e relacionamentos como ponteiros Restrições de integridade: exceto a raiz qual registro deve ter um pai Operações: get first, get next, ... 82 Tipos de SGBD: Hierárquico Físico (cont.) Dados armazenados usando estruturas embutidas Usuário vê um conjunto de árvores Ligações explicitas via ponteiros no nível físico Navegação através de ponteiros e de responsabilidade do programador Tem dependência de dados 83 Tipos de SGBD: Rede Lógico (cont.) Entidades como coleções de registros e relacionamentos como ligações pai-filho Restrições de integridade: relacionamento m:n não pode ser representado por um único tipo de ligação Operações: get, find, store, ... 84 Tipos de SGBD: Rede Físico (cont.) Dados organizados através de uma rede de nós e links Usuário vê um conjunto de grafos Ligações explicitas via ponteiros no nível físico Navegação através de ponteiros e de responsabilidade do programador Tem dependência de dados 85 Tipos de SGBD: Relacional Lógico (cont.) Entidades como tabelas e relacionamentos como colunas em pares de tabelas Restrições de integridade: componentes de PK não pode ser nulo para cada valor de FK em uma tabela deve existir um valor de PK em outra tabela Operações: seleção, projeção, junção, ... 86 Tipos de SGBD: Relacional Físico (cont.) Dados armazenados de forma tabular Usuário vê um conjunto de tabelas Ligações implícitas compartilhando chaves num nível lógico Navegação automática: otimizadores de consultas 87 Tipos de SGBD: Classificação de BD (cont.) MER RELACIONAL REDE HIERARQUICO Entidade Tabelas Grafos Árvores Entidade Linhas Registro Segmento Relacionamento Implícito (pares de colunas) Explícito (ponteiros) Explícito (ponteiros) Atributos Coluna Campo Campo 88 Tipos de SGBD: Classificação de BD (cont.) A B E C D MER 1 A 3 C 1 4 D 2 2 B 1 5 E 2 3 RELACIONAL 89 Tipos de SGBD: Classificação de BD (cont.) A B E C D MER REDE A B E C D 90 Tipos de SGBD: Classificação de BD (cont.) A B E C D MER HIERÁRQUICO A B E C D E 91 Tipos de SGBD: SGBD’s Hierárquicos (cont.) IMS (IBM) SYSTEM 2000 (SAS) TDMS (BURROUGHS) MARS VI (CDC) RFMS (Univ. Texas) 92 Tipos de SGBD: SGBD’s Rede (cont.) IDS II (Bull) DMS II (UNISYS) IDMS ( CULLINAME) DMS 1100 (UNIVAC) DBMS-11 (DEC) TOTAL (CINCON) ADABAS (Software AG) 93 Tipos de SGBD: SGBD’s Relacionais (cont.) ORACLE INGRES DB2 INFORMIX RDB PROGRES SQL SERVER 94 SGBD x SGA 95 SGBD x SGA SGBD - software responsável pela definição e manutenção de um BD SGA - conjunto de rotinas do sistema operacional responsável pelo sistema de arquivos (SA) e respectivos métodos de acesso SA - arquivos convencionais (sequenciais, indexados, randômicos) e suas operações 96 SGBD x SGA Máquina Mundo real MER SGBDOO Relacional Rede Hierárquica SGBD Específico SGA 97 SGBD X SGA SGBD SGA Alto nível Baixo nível 98 Modelo de Dados 99 Modelo de Dados É a principal ferramenta para fornecer abstração de dados É um conjunto de conceitos que pode ser usado para descriminar a estrutura do banco de dados Um modelo de dados se compõe de tipos de objetos, relacionamentos entre objetos e restrições de objetos 100 Modelo de Dados (Cont.) As operações básicas fazem parte da maioria dos modelos de dados Ferramenta usada para descrever a estrutura do BD O modelo de dados se compõem de: Estruturas de dados Operações Restrições de integridade 101 Modelo de Dados (Cont.) Os modelos de dados se dividem em três níveis: Conceitual (alto nível) Lógico Físico (baixo nível) 102 Modelo de Dados (Cont.) Conceitual: Descreve dados que estão próximos da forma como muitos usuários vêem os dados, muito próximos do mundo real. Ex: Modelo Semântico e MER Lógico: Nível intermediário que descreve dados ao nível de registro bem próximo da implementação física Ex: Relacional, Rede e Hierárquico 103 Modelo de Dados (Cont.) Físico: Descreve as estruturas de dados ao nível de armazenamento físico dos dados 104 Mundo real Lógico Físico Conceitual Fase de Projeto do BD Relacional Rede Hierárquico SGBD específico SGA Semântico/MER Modelo de Dados 105 Modelo de Dados: conceitual (cont.) Entidades: representa um objeto do mundo real ou conceito Atributos: propriedades que descrevem uma entidade Relacionamentos: interações entre entidades 106 Projetos Funcionários Cod. Projeto Nome Projeto Valor Projeto Cod. Funcionário Nome Funcionário Endereço Funcionário Projetos têm Funcionários Modelo de Dados: Conceitual (cont.) 107 Modelo de Dados: Lógico (cont.) São usados na maioria dos DBMS, incluindo os três tipos de modelos usados Relacional Rede hierárquico Representam dados usando estruturas de registros São por vezes chamados de modelos de dados baseados em registros 108 Modelo de Dados: Físico (cont.) Descrevem como os dados são armazenados no computador através da representação da informação tal como formato, ordem e acesso aos registros 109 Modelo de Dados (cont.) Esquema: Descrição gráfica ou textual do BD conforme o modelo de dados. Ele é especificado durante o projeto do BD e não é modificado com freqüência. Ex: FUNC (mat, nome, sal, dep) DEPA (dep, descr) DEPE (mat, seq, nomd, sexd) Diagrama de Esquema: Mostra alguns aspectos do BD 110 Modelo de Dados (cont.) Instância: Descrição de uma abstração do bancode dados. São os dados estão no BD em determinado momento. Pode ser modificado com freqüência. 111 Arquitetura do BD 112 Arquitetura do BD Usaremos a arquitetura de três níveis O objetivo é separar as aplicações de usuário da parte física do BD Nível interno: armazenamento físico da estrutura do BD. Usa a modelagem de dados físicos e descreve detalhes de armazenamento e caminhos para BD. 113 Arquitetura do BD (cont.) Nível conceitual: descreve a estrutura de todo o BD para os usuários. Esconde os detalhes as estruturas de armazenamento físico e se concentra na descrição de entidades, tipos de dados, relacionamentos, operações de usuários e restrições. Um modelo de dados de ato nível ou modelo de implementação pode ser usado neste nível 114 Arquitetura do BD (cont.) Nível externo: descreve a parte do BD a um grupo de usuários (visões) e esconde o restante do BD. Um modelo de alto nível ou modelo de implementação é usado neste nível 115 Visão externa1 Esquema Interno ... Esquema Conceitual Armazenamento do BD Usuários Finais Visão externa2 116 Arquitetura do BD (cont.) Mapeamento de Dados Processo de transformar requisições de usuários feitas no nível externo aos níveis mais internos e retornar uma resposta ao usuário Divide-se em: Lógico Físico 117 Arquitetura do BD (cont.) Independência de Dados Capacidade de fazer modificações no banco de dados em um dos níveis sem afetar o nível mais alto Independência lógica dos dados Independência física dos dados 118 Arquitetura do BD (cont.) Independência Lógica Capacidade de modificar o esquema conceitual sem modificar o esquema externo ou programas de aplicação Independência Física Capacidade de modificar o esquema interno sem modificar o esquema externo ou programas de aplicação (Reorganização de arquivos) 119 Por que modelagem de dados ? 120 Por que modelagem de dados ? Com o crescente uso de SGBD’s em face da necessidade de aplicações cada vez mais complexas, é inevitável esta abordagem Antes, as aplicações usavam um número muito reduzido de arquivos convencionais Sistemas antigos (aplicações isoladas) primavam pela repetição de dados em vários arquivos 121 Por que modelagem de dados ? (cont.) Aplicações desenvolvidas com “L4G” dependem de uma rígida administração de dados - cada usuário projeta os dados de seu uso específico sem tomar conhecimento das necessidades dos demais usuários Principal virtude dos modelos de dados: estabilidade 122 Por que modelagem de dados ? (cont.) Enquanto modela dados o analista concentra-se nesta tarefa “esquecendo” software e SGBD Os relacionamentos representam a obtenção de respostas a certas necessidades de informação por parte do usuário 123 Por que modelagem de dados ? (cont.) Modelo prévio de dados é um excelente instrumento de documentação do mundo real, em tempo de entrevista Levanta e documenta a parte mais estável de uma aplicação: os dados Excelente ferramenta gráfica de projeto conceitual de BD 124 MER 125 MER Proposto por Peter S. Chen - 1976 Entidade Algo sobre que dados são armazenados Representação abstrata de algo do mundo real Ex: Aluno, cliente, nota fiscal, ... Conjunto de entidades Grupo de entidades com características semelhantes Ex: Alunos, clientes, notas fiscais, ... 126 MER (cont.) Relacionamento - associação entre entidades Relacionamento Binário - é um par ordenado (e1, e2), onde e1 e e2 são respectivamente as entidades E1 e E2 Alunos Disciplinas Cursa Nota Período Mat Nom Cod Des 127 MER (cont.) Grau de um relacionamento - número de conjuntos de entidades envolvidos no relacionamento Ex: unário, binário. ternário, ... 128 MER (cont.) Classe ou cardinalidade dos relacionamentos - razão ente as quantidades com que cada conjunto de entidades participa do relacionamento Ex: Binários - 1:1 1:n m:n Ternários - 1:m:n m:n:p 129 MER (cont.) Funcionários Projetos Chefia Hor_trab Dt_posse Dt_aloc Trabalha Departamentos Chefia Chefia 130 MER (cont.) Atributos São características de entidades ou de relacionamentos São funções que levam um ponto do conjunto de entidades ou do relacionamento a um ponto de conjuntos de valores F M Funcionários 100 200 Matricula Sexo 131 MER (cont.) Relacionamento unário ou recursivo ou auto-relacionamento Pessoas casa 1 1 Marido Esposa Papel da entidade Produtos compõe n m É componente É composto 132 MER (cont.) Itens Pedidos Requisições P_R_I m n p - Para um item e para um pedido correspondem n requisições - Para uma requisição e um pedido correspondem m itens - Para uma requisição e um item correspondem p pedidos 133 MER (cont.) Professores Disciplinas Alunos P_A_D 1 n m - Um aluno cursa uma disciplina com um professor - Um professor ministra uma disciplina para n alunos - Um professor ministra para um aluno m disciplinas 134 MER (cont.) Sistemas Funcionários Atividades Executa m p q Funcionários n Nmf Mat Nms Cds Cdc Nmc Cda Nma 135 MER (cont.) Chave primária Conjunto de atributos que identificam uma única entidade Ex: matrícula, cod_produto, cod_cli, CPF, ... Restrições Só pode haver uma por entidade Não pode ser nula Deve identificar apenas um elemento da entidade Deve ser a menor possível Valor não muda ao longo da vida da entidade 136 MER (cont.) Chave estrangeira Conjunto de atributos que fazem associação entre esta entidade e outra que contenha esta chave como primária. Exemplo: Alunos Disciplinas Nota Período Mat Nom Cod Des Cursa Mat Cod 137 Extensões do MER Chave candidata - conjunto de atributos que podem ser chave primária Ex: mat, RG, CDD Chave secundária ou alternada - conjunto de atributos que identificam um grupo de entidades Ex: sal, sexo, ... 138 Extensões do MER Chave artificial ou surrogate key Número seqüencial sem qualquer semântica, introduzido para ser chave primária de um conjunto de entidades Ex: nud, mat, ... Departamentos Dependentes Funcionários CDD Nmd Mat Nmf Sexo RG Matf Nud Nmd Sal CoDep 139 Extensões do MER Como escolher a chave primária Deve ser a menor possível Valor não muda ao longo da vida da entidade Não pode ser NULL (nem parcial, nem totalmente) 140 MER (cont.) Entidade Fraca Sua existência depende de outra Seus atributos, em geral, são insuficientes para determinar uma chave primária Funcionários Dependentes Depende 1 n 141 MER (cont.) Cardinalidade dos relacionamentos Um relacionamento pode ter a cardinalidade alterada com o tempo ou conforme as necessidades de informação por parte do usuário Ex: Casamento entre pessoas Atual (1h x 1m) ... 1 : 1 Atual (1h x 1Poligamiam) ... 1 : n Histórico de todos os casamentos ... M : n 142 MER (cont.) Passos para identificar a cardinalidade Passo 1: Identificar a cardinalidade entre Cliente Pedido Passo 2: Identificar a cardinalidade entre Pedido Cliente Passo 3: Identificar a cardinalidade máxima para cada entidade 143 MER (cont.) Cliente Pedido 1 n Passo 1 Cliente Pedido n n Passo 2 Cliente Pedido n n Passo 3 n 1 144 MER (cont.) Itens Lojas Vende n 1 Itens Lojas Vende n m Loja única Abertura de novas lojas Cardinalidade dos casamentos 145 MER (cont.) Cardinalidade dos relacionamentos Departamentos Funcionários Aloca 1 n Departamentos Funcionários Aloca m n Qual a alocação atual do funcionário ? DataUm funcionário pode estar lotado simultaneamente em mais de um departamento: devia haver um atributo tipo percentual de dedicação do funcionário ao departamento Em quais departamentos esteve ou está lotado o funcionário ? 146 MER (cont.) Análise Sentencial Substantivos (sujeitos/objetos) - entidades Verbos (predicados) - relacionamentos Casas Pessoas Mora Tem Carros 147 MER (cont.) Análise Sentencial Adjetivos - atributo de entidades Advérbios - atributo de relacionamentos Pessoas Possui Carros Nome Profissão Nome Profissão Dt_aquisição José, advogado, possui um volks azul desde 1987 148 MER (cont.) Análise Sentencial Entidade tem atributos Ex: nota fiscal tem série, data, número carro tem marca, cor, modelo Atributo de entidade é valor Ex: o número da nota fiscal é 1234 a idade da pessoa é 36 149 Extensões do MER 150 Extensões do MER Atributos compostos - formados por mais de um atributo Atributos multivalorados - levam uma entidade a mais de um ponto do conjunto de valores Clientes Fones Endereço Logradouro CEP Apto Num Nome 151 Extensões do MER (cont.) Parcialidade / Totalidade Nem todo funcionário gerencia um departamento (parcial) Todo departamento é gerenciado por um funcionário (total) Funcionários Gerencia Departamentos 1 1 152 Extensões do MER (cont.) Relacionamentos totais e parciais Pessoas Nasce Cidades 1 1 Mora Casa 1 1 n 1 Nome Dt_nasc Nome Pop 153 Extensões do MER (cont.) Cardinalidade dos relacionamentos com valores máximo e mínimo de entidades que se associam Alunos Cursa Disciplinas (1,6) Nome Mat (0,40) Nota Período Cod Des 154 Extensões do MER (cont.) Generalização x Especialização Clientes PJ PF Cod Nome Cod CGC Cod CPF Sexo Est_civil Ender Tipo Capital Mes_in Nu_meses_ex 155 Extensões do MER (cont.) Generalização/Especialização As subcategorias (subtipos) herdam os atributos da categoria supertipo. As subcategorias detém a mesma chave primária da categoria supertipo 156 Extensões do MER (cont.) Agregação Médicos Prescreve Pacientes Atende m n Exames Consultas Um exame é prescrito por um médico para um paciente 157 Extensões do MER (cont.) Agregação - é uma abstração que: permite que relacionamentos sejam tratados como entidades de nível mais alto permite modelar um relacionamento entre uma entidade e um relacionamento (este geralmente m: n) A agregação origina uma nova entidade 158 Mapeamento MER x DER 159 Mapeamento MER X DER Funcionários Projetos Mat Nmd Cdp Nmp Cdp Dt_aloc Funcionários Projetos Mat Nmd Cdp Nmp Aloca Dt_aloca n 1 Chave primária do lado 1 vai para o lado n Atributos do relacionamento vão para o lado n 160 Mapeamento MER X DER (cont.) Alunos Disciplinas Cda Nma Cdd Nmd Cdp Dt_aloc Alunos Disciplinas Cda Nma Cdd Nmd Cursa Período m n • Quebra o relacionamento m:n em dois 1:n • Atributos do relac. ficam na entidade intermediária • Chaves primárias das entidades fazem parte da chave primária da entidade intermediária Nota Al-di Cdd Cda 161 Mapeamento MER X DER (cont.) Médicos Hospitais Cdm Nmm Cdm Nmm Médicos Hospitais Cdm Nmm Cdh Nmh Gerencia Dt_posse 1 1 • Atributos do relacionamento vão para a entidade que tem participação total no relacionamento • Chave primária da entidade do lado parcial vai para a entidade que participa totalmente Cdm Dt_posse 162 Mapeamento MER X DER (cont.) Funcionários Mat Nom Chefia Chefe 1 n Subordinado Funcionários Mat Nom Matchefe 163 Mapeamento MER X DER (cont.) Peças Cod des Compõe Componentes m n Compostos Funcionários Mat Nom Cd-composto Qtd Compõe Cd-componente Qtd 164 Mapeamento MER X DER (cont.) Funcionários Mat Nom Gerencia Dt_posse 1 1 Dt_aloc Departamentos Aloca Cursa Chefia 1 n Cursos n m n 1 Cdd Des Cdct Nmc 165 Mapeamento MER X DER (cont.) Funcionários Mat Nom Dt_posse Mat-ger Departamentos Cursos Cdd Des Cdct Nmc Cursa Cdct Mat Dt_cur Mat-ch Chefe Subordinado Dt_aloc Cdd 166 Mapeamento MER X DER (cont.) Modelo Físico Modelo Lógico Modelo Conceitual Mundo Real Modelo Relacional ..... DER ..................................... MER...................................... 167 Mapeamento MER X DER (cont.) MER - os atributos de uma entidade não devem aparecer em outra DER - os atributos de uma entidade podem aparecer em outra para estabelecer o relacionamento 168 Mapeamento MER X DER (cont.) A B R 1 1 AR CA AA CB AB A B AR CA AA CB AB CB A B R 1 N AR CA AA CB AB A B AR CA AA CB AB CA A B R M N AR AA CB AB A B CA AA CB AB CA AB AR CA CB 169 Mapeamento MER X DER (cont.) Relacionamento M x N se transforma em 2 relacionamentos 1 : n Funcionários Departamentos Lotações Mat Cdd Cdd Nom Mat Nmd Dat 100 Ari 100 Adm Jan/98 Ven Vendas 200 Ana 100 Ven Mar/99 Pro Produção 300 Eva 200 Ven Out/00 Adm Adminst 300 Pro Mai/01 170 Mapeamento MER X DER (cont.) A C 1 B 1 1 CA AA Tipo A C B CA AA Tipo AC AB CA CA AB AC 171 Mapeamento MER X DER (cont.) A C M B N CA AA CC AC R P CB AB A C B CA AA CC AC CB AB R AR CA CC CB AR 172 Mapeamento MER X DER (cont.) A C M B N CA AA CC AC R P CB AB AR S Q AS A C B CA AA CC AC CB AB AS R CA CB AR S CA CC CB 173 Modelagem com ferramenta CASE 174 Modelagem com ferramenta CASE Metodologia IDE1X Desenvolvida pela US Air Force Usada por vários órgãos do governo americano Representa relacionamentos via chaves compartilhadas Usada por grandes empresas de diversas áreas 175 Modelagem com ferramenta CASE (cont.) Metodologia IDE1X... Trata chaves como o modelo relacional A chave primária (PK) do lado 1 vai para o lado N Relacionamento não identificador (linha cheia): chave estrangeira fará parte da PK no lado N Relacionamento não identificador (linha pontilhada): chave estrangeira não fará parte da chave primária no lado N 176 Modelo Relacional 177 Modelo Relacional Baseia-se na teoria dos conjuntos Os elos são implícitos O usuário vê o banco de dados como um conjunto de tabelas Mat Nome Dep 100 Ana Ven 200 Edu Pro 300 Ari Adm 400 Eva |Mar Funcionários Nome da tabela Atributos Tuplas Valor de atributo Domínios 178 Modelo Relacional (cont.) Esquema da relação (tabela): funcionários (mat, nom, dep) Relação - subconjunto do produto cartesiano dos domínios R D1 x D2 x ... X Dn 100 200 300 400 Ana Edu Eva Ari Adm Mar Pro Ven Nomes Matrículas Departamentos 179 Modelo Relacional (cont.) Uma relação pode ser representada sob forma de tabela, onde cada coluna representa um atributo cada linha representa uma ocorrência de: uma entidade um relacionamento Linha (tupla) é um conjunto de atributos 180 Modelo Relacional (cont.) Um relação pode ser representada sob forma de tabela onde: ... Domínio - conjunto de valores do atributo Grau de uma relação - número de atributos da tupla da relação Chave primária - um ou mais atributos que identificam uma única linha Ex: aluna (mat, nom, dt_nasc, sexo) 181 Normalização 182 Normalização MER Modelo Lógico Arquivos (Top-Down) Mundo Real Modelo Lógico Normalização Arquivos (Botton-Up) Mundo Real 183 Normalização (cont.) Simplificação de relações a fim de que os algoritmos de atualização de dados sejam mais simples e mais claros Comosub-produto: eliminação da redundância de dados Resultado das pesquisas de E. F. Codd 184 Normalização (cont.) Dependência Funcional A matrícula de um funcionário determina funcionalmente o nome bem como o salário do funcionário Matrícula Nome Matrícula Salário Se A determina funcionalmente B, então B depende funcionalmente de A, logo: Nome depende funcionalmente de matrícula 185 Normalização (cont.) Dependência Funcional Func (mat, nome, sal, CPF) Mat Nome Mat Sal Mat CPF CPF Sal CPF Nome CPF Mat Mat CPF Sal Nome 186 Normalização (cont.) Dependência Funcional Tarefa (mat, nom, nu_proj, nm_proj, horas-trab) Mat, nu_proj horas-trab Mat nome Mat Nm_proj Horas-trab Nu_proj Nom 187 Normalização (cont.) Porque ? Garante a falta de redundância decorrente das DF Garante a falta da anomalia de atualização uma ocorrência de um fato é atualizada, e não todas Garante a falta da anomalia de retirada um fato válido é permitido quando a tupla é deletada 188 Normalização (cont.) Existem 6 formas normais Analisaremos aqui apenas as 4 primeiras formas normais e a forma normal de Boyce-Codd 189 Normalização (cont.) 1FN Força a organização dos dados como estruturas “planas” sem grupos repetidos Todo componente de toda tupla é um valor atômico Exemplo: Carros(Chassi, nome, cor, ano, cod_marca, descrição_marca) Grupo 1: Dados sobre o carro Grupo 2: Dados sobre a marca Solução Carros(chassi,nome,cor,ano) Marcas(cod_marca,descrição_marca) 190 Normalização (cont.) 2FN Todos os atributos não chave devem depender de toda a chave Exemplo: Projeto (Matricula, nome, cod_projeto, nome_projeto, horas_trabalhadas) Matricula nome Cod_projeto nome_projeto Matricula, cod_projeto horas_trabalhadas Solução Projeto(Cod_projeto,nome_projeto) Funcionário(Matricula, nome) Proj_func(Matricula, cod_proj, horas_trabalhadas) Normalização (cont.) 3FN Não permite dependencia circular Ex: Teatro(cod,nome,peça) Solução Teatro(cod,nome) Cliente(cod, nome, logradouro, CEP) Solução Cliente(cod,nome,logradouro) CEPs(numero, CEP) 192 Normalização (cont.) 4FN Reconhece e separa atributos multivalorados constituindo uma chave primária composta Exemplo: Cliente(codigo, nome, telefones) Telefones pode ser fixo, fax, celular, residencial, comercial, etc. Solução Cliente(codigo,nome) Telefones(numero,telefone) 193 Normalização (cont.) FNBC Elimina redundâncias e anomalias de atualização e retirada Exemplo: Venda(Cod_produto, valor_unitário, quantidade, valor_total) Valor total é calculado a partir do valor unitário * a quantidade Solução Venda(cod_produto,valor_unitario,quantidade) 194 Desnormalização 195 Desnormalização Vantagens Melhorar o desempenho das consultas Quando necessita retornar muitas vezes resultados já calculados Criação de históricos Quando já tiver pré-determinado os tipos de valores de uma tabela. 196 Desnormalização (cont.) Desvantagens Vulnerabilidade ao surgimento de anomalias quando ocorre manipulação Ameaça a integridade dos dados Quando a consulta precisa de dados separados. Nesse caso, se as tabelas estão desnormalizadas, a leitura das informações não necessárias ocasiona maior tempo de processamento. 197 Fragmentação Desnormalização 198 Scripts Oracle 199 Tabela Unidade básica de armazenamento da base de dados, formada por colunas e linhas (tuplas) 200 Criando Tabelas SQL> CREATE TABLE depto 2 (depto_num NUMBER(2), 3 depto_nom VARCHAR2(14), 4 depto_loc VARCHAR2(13)); Table created. Criando a tabela depto. – Listando a estrutura da tabela criada. SQL> DESCRIBE depto Name Null? Type --------------------------- -------- --------- DEPTO_NUM NOT NULL NUMBER(2) DEPTO_NOME VARCHAR2(14) DEPTO_LOC VARCHAR2(13) 201 Descartando uma Tabela Todos os dados e a estrutura da tabela são destruídos. Qualquer transação pendente é encerrada. Todos os índices são descartados. Essa operação não pode ser desfeita. SQL> DROP TABLE depto30; Table dropped. 202 Truncando uma Tabela Remove todas as linhas da tabela liberando o espaço ocupado Essa operação não pode ser desfeita SQL> TRUNCATE TABLE departamento; Table truncated. 203 Alterando uma Tabela Altera a estrutura de uma tabela Essa operação não pode ser desfeita SQL> Alter TABLE departamento alter column nome varchar(20); Table altered. SQL> Alter TABLE departamento drop column nome; Table altered. SQL> Alter TABLE departamento add nome varchar(20); Table altered. O Comando INSERT Adicione linhas a uma tabela utilizando o comando INSERT. O comando INSERT insere apenas uma linha por vez. Não esqueça o COMMIT INSERT INTO table [(column [, column...])] VALUES (value [, value...]); Inserindo Novas Linhas Insira uma nova linha informando os valores para cada coluna da tabela. Liste os valores na mesma ordem das colunas na tabela. Opcionalmente, liste as colunas na cláusula do INSERT. Strings e datas devem ser informando entre aspas simples. SQL> INSERT INTO depto (depto_num, depto_nome, depto_loc) 2 VALUES (50, 'DESENVOLVIMENO', ‘RECIFE'); 1 row created. SQL> INSERT INTO depto (depto_num, depto_nome ) 2 VALUES (60, ‘REC HUMANOS'); 1 row created. SQL> INSERT INTO depto 2 VALUES (70, ‘JURIDICO', NULL); 1 row created. Inserindo Linhas com Nulls Implicitamente: omita o nome da coluna da lista de colunas. – Explicitamente: especifique o valor NULL. O Comando UPDATE Modificando linhas existentes com o comando UPDATE. Modifique mais de uma linha por vez especificando uma condição na cláusula WHERE. Não esqueça o COMMIT UPDATE table SET column = value [, column = value] [WHERE condition]; Atualizando linhas em uma tabela Linhas específicas podem ser modificadas utilizando a cláusula WHERE. Todas as linhas da tabela são modificadas se a cláusula WHERE for omitida. SQL> UPDATE emp 2 SET depto_num = 20 3 WHERE enum = 7782; 1 row updated. SQL> UPDATE emp 2 SET depto_num = 20; 14 rows updated. Eliminando Linhas de uma Tabela Linhas específicas podem ser eliminadas utilizando a cláusula WHERE. Todas as linhas da tabela são eliminadas se a cláusula WHERE for omitida. SQL> DELETE FROM depto 2 WHERE depto_nome = 'DESENVOLVIMENTO'; 1 row deleted. SQL> DELETE FROM depto; 4 rows deleted. 210 O Comando SELECT SELECT [DISTINCT] {*, column [alias],...} FROM table; SELECT identifica as colunas FROM identifica as tabelas 211 Selecionando linhas SQL> SELECT * 2 FROM depto; SQL> SELECT depto_num, depto_loc 2 FROM depto; SQL> SELECT enome, sal, sal+300 2 FROM emp; SQL> SELECT enome, sal, 12*sal+100 2 FROM emp; 212 Definindo Alias para Colunas SQL> SELECT enome AS nome, sal AS salario 2 FROM emp; NOME SALARIO ------------- --------- ... SQL> SELECT enome "Nome", 2 sal*12 “Salario Anual" 3 FROM emp; Nome SalarioAnual ------------- ------------- ... 213 Eliminando Linhas Duplicadas SQL> SELECT DISTINCT depto_num 2 FROM emp; DEPTO_NUM --------- 10 20 30 DISTINCT Utilizando a Cláusula WHERE SQL> SELECT enome, cargo, depto_num 2 FROM emp 3 WHERE cargo='CAIXA'; ENOME CARGO DEPTO_NUM ---------- --------- --------- RONALDO CAIXA 30 MANUEL CAIXA 20 PAULO CAIXA 20 LUCIANO CAIXA 10 Operadores de Comparação Operador = > >= < <= <> Significado Igual a Maior que Maior ou igual a Menor que Menor ou igual a Diferente de Outros Operadores Operador BETWEEN ...AND... IN(lista) LIKE IS NULL Significado Entre dois valores (inclusive) Satisfaz uma lista de valores Satisfaz um padrão de caracteres É um valor nulo (null) Operador BETWEEN ENOME SAL ---------- --------- MARIA 1250 SERGIO 1500 MATHEUS 1250 PAULO 1100 LUCIANO 1300 SQL> SELECT enome, sal 2 FROM emp 3 WHERE sal BETWEEN 1000 AND 1500; limite inferior limite superior Operador IN SQL> SELECT enum, enome, sal, ger 2 FROM emp 3 WHERE ger IN (7902, 7566, 7788); ENUM ENOME SAL GER --------- ---------- --------- --------- 7902 JOSE 3000 7566 7369 MANUEL 800 7902 7788 FABIO 3000 7566 7876 PAULO 1100 7788 SQL> SELECT enome 2 FROM emp 3 WHERE enome LIKE ‘M%'; Operador LIKE Utilize o operador LIKE para realizar pesquisas por padrões (wildcards). % substitui zero ou mais caracteres _ substitui um único caracter SQL> SELECT enome, ger 2 FROM emp 3 WHERE ger IS NULL; ENOME GER ---------- --------- CARLOS Operador IS NULL Testando valores nulos (null) Operadores Lógicos Operador AND OR NOT Significado Retorna TRUE se a condição de ambos os componentes for TRUE Retorna TRUE se a condição de um dos componentes for TRUE Retorna TRUE se a condição for FALSE (vise-versa) Operador NOT SQL> SELECT enome, cargo 2 FROM emp 3 WHERE cargo NOT IN('CAIXA','GERENTE','ANALISTA'); ENOME CARGO ---------- --------- CARLOS PRESIDENTE MARIA VENDEDOR CELSO VENDEDOR SERGIO VENDEDOR MATHEUS VENDEDOR Cláusula ORDER BY SQL> SELECT enome, cargo, depto_num, dtinicio 2 FROM emp 3 ORDER BY dtinicio DESC; ENOME CARGO DEPTO_NUM DTINICIO ---------- --------- --------- --------- PAULO CAIXA 20 12-JAN-83 FABIO ANALISTA 20 09-DEC-82 LUCIANO CAIXA 10 23-JAN-82 RONALDO CAIXA 30 03-DEC-81 JOSE ANALISTA 20 03-DEC-81 CARLOS PRESIDENTE 10 17-NOV-81 MARIA VENDEDOR 30 28-SEP-81 ... 14 rows selected. Joins Utilize uma junção para consultar dados de mais de uma tabela. Especifique a condição de junção na cláusula WHERE. Informe o nome da tabela junto com o nome da coluna, se tabelas diferentes possuírem colunas com os mesmos nomes. SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2; Criando Alias para Tabelas SQL> SELECT emp.enum, emp.enome, emp.depto_num, 2 depto.depto_num, depto.depto_loc 3 FROM emp, depto 4 WHERE emp.depto_num = depto.depto_num; SQL> SELECT e.enum, e.enome, e.depto_num, 2 d.depto_num, d.depto_loc 3 FROM emp e, depto d 4 WHERE e.depto_num = d.depto_num; O que são Funções de Grupo? Funções de grupo operam em conjuntos de linhas, produzindo um resultado por grupo. EMP “o maior salário da tabela EMP” DEPTO_NUM SAL --------- --------- 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 MAX(SAL) --------- 5000 AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL) -------- --------- --------- --------- 1400 1600 1250 5600 SQL> SELECT AVG(sal), MAX(sal), 2 MIN(sal), SUM(sal) 3 FROM emp 4 WHERE cargo LIKE ‘VEND%'; Funções AVG e SUM Utilize o AVG e SUM apenas para dados numéricos SQL> SELECT MIN(dtinicio), MAX(dtinicio) 2 FROM emp; MIN(DTINI MAX(DTINI --------- --------- 17-DEZ-80 12-JAN-83 Funções MIN e MAX Utilize MIN e MAX para qualquer tipo de dado COUNT(*) --------- 6 SQL> SELECT COUNT(*) 2 FROM emp 3 WHERE depto_num = 30; Função COUNT COUNT(*) retorna o número de linhas na tabela COUNT(COMIS) ------------ 4 SQL> SELECT COUNT(comis) 2 FROM emp 3 WHERE depto_num = 30; Função COUNT COUNT(coluna) retorna o número de linhas não nulas da tabela Criando Grupos de Dados EMP “média salarial por departamento” 2916.6667 2175 1566.6667 DEPTO_NUM SAL --------- --------- 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 DEPTO_NUM AVG(SAL) --------- --------- 10 2916.6667 20 2175 30 1566.6667 Criando Grupos de Dados: A Cláusula GROUP BY Divida as linhas de uma tabela em pequenos grupos usando a cláusula GROUP BY. SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]; SQL> SELECT AVG(sal) 2 FROM emp 3 GROUP BY depto_num; Colunas utilizadas em funções de grupo não precisam estar listadas no GROUP BY. AVG(SAL) --------- 2916.6667 2175 1566.6667 A Cláusula GROUP BY SQL> SELECT depto_num, cargo, sum(sal) 2 FROM emp 3 GROUP BY depto_num, cargo; Utilizando GROUP BY em Múltiplas Colunas DEPTO_NUM CARGO SUM(SAL) --------- --------- --------- 10 CAIXA 1300 10 GERENTE 2450 10 PRESIDENTE 5000 20 ANALISTA 6000 20 CAIXA 1900 ... 9 rows selected. Subqueries A subquery (inner query) geralmente é executada antes da consulta principal. O resultado da subquery é, então, avaliado pelo da query principal (outer query). SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table); 2975 SQL> SELECT enome 2 FROM emp 3 WHERE sal > 4 (SELECT sal 5 FROM emp 6 WHERE enum=7566); Utilizando uma Subquery ENOME ---------- CARLOS JOSE FABIO Regras para Subqueries Utilize subqueries entre parênteses. As subqueries vêem sempre à direita do operador de comparação. Não utiliza a cláusula ORDER BY em subqueries. Uma subquery retorna uam tabela sobre a qual pode-se realizar qualquer uma das operações vista anteriormente. 240 Stored Procedures Procedimantos que permanecem armazenados no banco, de forma compilada. Servem para executar alguma computaçãoquando invocados 241 Sintaxe CREATE OR REPLACE PROCEDURE NOME ( NOME TIPO[,NOME TIPO] ) IS BEGIN [DECLARE] <CORPO> COMMIT; EXCEPTION WHEN OTHERS THEN <CORPO> END NOME; / 242 Sintaxe (exemplo) CREATE OR REPLACE PROCEDURE AJUSTE (VALOR REAL, CAD INTEGER) IS BEGIN UPDATE acf_EMPREGADO SET salario=salario + salario*VALOR WHERE cadastro=CAD; COMMIT; EXCEPTION WHEN OTHERS THEN INSERT INTO acf_ERROS values(SYSDATE,'Erro na execucao de ajuste'); END AJUSTE; / 243 Execução EXECUTE AJUSTE(0.1, 21); O procedimento é executado. Caso algum erro ocorra, então a tabela de erros será atualizada. 244 Exemplo CREATE OR REPLACE PROCEDURE Lista_Func (codDepto llma_funcionarios.depto_func%TYPE) IS BEGIN DECLARE --cursor para guardar os funcionarios CURSOR c_diario_func is SELECT llma_funcionarios.cod_func, llma_funcionarios.nome_func, FROM llma_funcionarios WHERE depto_func = codDepto; --declaracao das variaveis do cursor v_cod_func llma_funcionarios.cod_func%TYPE; v_nome_func llma_funcionarios.nome_func%TYPE; 245 Procedures Prodecures não retornam valores A partir de uma procedure pode-se chamar outras procedures Procedures são salvas em um arquivo .sql e compiladas no Oracle com o comando @caminho_completo do SQL Plus Caso ocorra algum erro de compilação a procedure não funciona corretamente Erros de compilação podem ser vistos com o comando show_errors do SQL Plus. 246 Triggers Procedimantos especiais guardados no banco de forma compilada Acionados automaticamente pelo banco quando sua condição de ativação for veradeira 247 Sintaxe CREATE OR REPLACE TRIGGER NOME CONDICAO DE ATIVACAO BEGIN <CORPO> END; / A CONDICAO DE ATIVACAO pode ser montada a partir de expressões lógicas: BEFORE DELETE ON NOME_TABELA ou AFTER DELETE OR INSERT OR UPDATE ON NOME_TABELA 248 Sintaxe (exemplo) CREATE OR REPLACE TRIGGER LOG_FUNCIONARIO BEFORE DELETE OR INSERT OR UPDATE ON acf_EMPREGADO BEGIN INSERT INTO acf_LOG_funcionario VALUES(SYSDATE,'Tabela modificada'); END; / 249 Triggers Triggers são salvas em um arquivo .sql e compiladas no Oracle com o comando @caminho_completo do SQL Plus Caso ocorra algum erro de compilação a trigger não funciona corretamente Erros de compilação podem ser vistos com o comando show_errors do SQL Plus. Gatilhos podem ser utilizados para implementar regras de negócio
Compartilhar