This is a file preview. Join to view the original file.
*
Banco de Dados I
Danielle Filgueiras
*
*
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
*
*
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
*
*
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
*
*
O que é BD ?
*
*
O que é BD ?
*
*
Conceitos de Banco de Dados
*
*
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.
*
*
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.
*
*
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)
*
*
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
*
*
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
*
*
AS = Análise de Dados + Análise de Funções
Conceitos de Banco de Dados (cont.)
Análise de 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 ?
*
*
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
*
*
Conceitos de Banco de Dados (cont.)
Análise de Sistemas
*
*
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 ?
*
*
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 ?
*
*
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 ?
*
*
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
*
*
Conceitos de Banco de Dados (cont.)
Projeto Conceitual
Declaração de Objetivos (DO)
Lista de Eventos (LE)
Modelo de Entidade relacionamento (MER)
*
*
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
*
*
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
*
*
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
*
*
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
*
*
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
*
*
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
*
*
Conceitos de Banco de Dados (cont.)
Projeto Lógico
Diagrama de Entidade relacionamento (DER)
Dicionário de Dados (DD)
*
*
Conceitos de Banco de Dados (cont.)
Diagrama de Entidade-Relacionamento
Uma evolução do Modelo de Entidade-Relacionamento
*
*
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
Tabela Clientes
*
*
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)
*
*
Conceitos de Banco de Dados (cont.)
Projeto Físico
Projeto Detalhado de:
Saídas
Arquivos
Entradas
Controles no sistema
Especificação de Programas
*
*
Histórico do Banco de Dados
*
*
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
*
*
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
*
*
Histórico do Banco de Dados (cont.)
Desenvolvimento de software
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
*
*
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
*
*
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
*
*
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
*
*
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
*
*
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
*
*
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
*
*
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
*
*
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
*
*
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
*
*
Composição de um Sistema de Banco de Dados
*
*
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
*
*
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
*
*
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
*
*
Composição de um Sistema de Banco de Dados (cont.)
Usuário Final
Acessa o BD via:
query language
programas de aplicação
*
*
Linguagens de Bando de Dados
*
*
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)
*
*
Linguagens de Bancos de Dados (cont.)
SDL - Linguagem de definição de armazenamento
VDL - Linguagem de definição de visões
*
*
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
*
*
SDL
Usada para especificar o esquema interno
O mapeamento entre os dois esquemas pode ser feito com a SDL ou a DDL
*
*
VDL
Usada para especificar as visões e faz o mapeamento com o esquema conceitual
*
*
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
*
*
O que é um SGBD ?
*
*
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.
*
*
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
*
*
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
*
*
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
*
*
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
*
*
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
*
*
Objetivos do SGBD (cont.)
Garantir restrições de integridade
Backup e Recuperação
*
*
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
*
*
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
*
*
Níveis de abstração de SGBD
*
*
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
...
*
*
Níveis de abstração de SGBD (cont.)
Arquitetura de 3 níveis (Esquemas)
Sub-esquemas
Esquema Conceitual
Esquema Físico
...
*
*
Níveis de abstração de SGBD (cont.)
Arquitetura de 3 níveis (CODASYL - 71)
(Conference on Data System and Language)
Visão
Conceitual
Físico
...
*
*
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
...
*
*
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
*
*
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
*
*
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
*
*
Usuários do SGBD
*
*
Usuários do SGBD
Administradores
do BD
Projetistas de BD
usuários finais
Casual
Ingênuos
Sofisticados
Isolados
*
*
Usuários do SGBD (cont.)
Analistas de Sistemas
Programadores
Desenvolvedores de ferramentas
Operadores
Pessoal de manutenção
*
*
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
*
*
Utilitários do SGBD
Carga de arquivos existentes
Backup/Recovery
Reorganização de arquivos
Monitoramento de performance
Software de comunicação
*
*
Tipos de SGBD
Hierárquico
Rede
Relacional
Objeto-Relacional
Orientado-Objeto
Outros
*
*
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, ...
*
*
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
*
*
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, ...
*
*
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
*
*
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, ...
*
*
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
*
*
Tipos de SGBD: Classificação de BD (cont.)
Plan1
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
Plan2
Plan3
*
*
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
*
*
Tipos de SGBD: Classificação de BD (cont.)
A
B
E
C
D
MER
REDE
A
B
E
C
D
*
*
Tipos de SGBD: Classificação de BD (cont.)
A
B
E
C
D
MER
HIERÁRQUICO
A
B
E
C
D
E
*
*
Tipos de SGBD: SGBD’s Hierárquicos (cont.)
IMS (IBM)
SYSTEM 2000 (SAS)
TDMS (BURROUGHS)
MARS VI (CDC)
RFMS (Univ. Texas)
*
*
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)
*
*
Tipos de SGBD: SGBD’s Relacionais (cont.)
ORACLE
INGRES
DB2
INFORMIX
RDB
PROGRES
SQL SERVER
*
*
SGBD x SGA
*
*
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
*
*
SGBD x SGA
Máquina
Mundo real
MER
SGBDOO
Relacional
Rede
Hierárquica
SGBD Específico
SGA
tempo
*
*
SGBD X SGA
SGBD
SGA
Alto nível
Baixo nível
*
*
Modelo de Dados
*
*
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
*
*
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
*
*
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)
*
*
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
*
*
Modelo de Dados (Cont.)
Físico: Descreve as estruturas de dados ao nível de armazenamento físico dos dados
*
*
Lógico
Físico
Conceitual
Fase de Projeto do BD
Relacional
Rede
Hierárquico
SGBD específico
SGA
Semântico/MER
Modelo de Dados
*
*
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
*
*
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.)
*
*
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
*
*
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
*
*
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
*
*
Modelo de Dados (cont.)
Instância: Descrição de uma abstração do banco de dados. São os dados estão no BD em determinado momento. Pode ser modificado com freqüência.
*
*
Arquitetura do BD
*
*
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.
*
*
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
*
*
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
*
*
Visão externa1
Esquema Interno
...
Esquema Conceitual
Armazenamento do BD
Usuários Finais
Visão externa2
*
*
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
*
*
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
*
*
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)
*
*
Por que modelagem de dados ?
*
*
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
*
*
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
*
*
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
*
*
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
*
*
MER
*
*
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, ...
*
*
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
*
*
MER (cont.)
Grau de um relacionamento - número de conjuntos de entidades envolvidos no relacionamento
Ex: unário, binário. ternário, ...
*
*
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
*
*
MER (cont.)
Funcionários
Projetos
Chefia
Hor_trab
Dt_posse
Dt_aloc
Trabalha
Departamentos
Chefia
Chefia
*
*
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
*
*
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
*
*
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
*
*
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
*
*
MER (cont.)
*
*
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
*
*
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
*
*
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, ...
*
*
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
*
*
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)
*
*
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
*
*
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
*
*
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
*
*
MER (cont.)
Cliente
Pedido
1
n
Passo 1
Cliente
Pedido
n
n
Passo 2
Cliente
Pedido
n
n
Passo 3
n
1
*
*
MER (cont.)
Itens
Lojas
Vende
n
1
Itens
Lojas
Vende
n
m
Loja única
Abertura de novas lojas
Cardinalidade dos casamentos
*
*
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 ?
Data
Um 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 ?
*
*
MER (cont.)
Análise Sentencial
Substantivos (sujeitos/objetos) - entidades
Verbos (predicados) - relacionamentos
Casas
Pessoas
Mora
Tem
Carros
*
*
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
*
*
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
*
*
Extensões do MER
*
*
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
*
*
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
*
*
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
*
*
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
*
*
Extensões do MER (cont.)
Generalização x Especialização
*
*
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
*
*
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
*
*
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
*
*
Mapeamento MER x DER
*
*
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
*
*
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
*
*
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
*
*
Mapeamento MER X DER (cont.)
Funcionários
Mat
Nom
Chefia
Chefe
1
n
Subordinado
Funcionários
Mat
Nom
Matchefe
*
*
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
*
*
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
*
*
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
*
*
Mapeamento MER X DER (cont.)
Modelo Físico
Modelo Lógico
Modelo Conceitual
Mundo
Real
Modelo Relacional .....
DER .....................................
MER......................................
*
*
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
*
*
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
*
*
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
*
*
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
*
*
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
*
*
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
*
*
Modelagem com ferramenta CASE
*
*
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
*
*
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
*
*
Modelo Relacional
*
*
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
*
*
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
*
*
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
*
*
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)
*
*
Normalização
*
*
Normalização
MER
Modelo Lógico
Arquivos
(Top-Down)
Mundo
Real
Modelo Lógico
Normalização
Arquivos
(Botton-Up)
Mundo
Real
*
*
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
Como sub-produto: eliminação da redundância de dados
Resultado das pesquisas de E. F. Codd
*
*
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
*
*
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
*
*
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
*
*
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
*
*
Normalização (cont.)
Existem 6 formas normais
Analisaremos aqui apenas as 4 primeiras formas normais e a forma normal de Boyce-Codd
*
*
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)
*
*
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)
*
*
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)
*
*
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)
*
*
Desnormalização
*
*
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.
*
*
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.
*
*
Fragmentação
Desnormalização
*
Scripts Oracle
*
*
Tabela
Unidade básica de armazenamento da base de dados, formada por colunas e linhas (tuplas)
*
*
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)
*
*
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.
*
*
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.
*
*
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.
*
*
O Comando SELECT
SELECT [DISTINCT] {*, column [alias],...}
FROM table;
SELECT identifica as colunas
FROM identifica as tabelas
*
*
Selecionando linhas
SQL> SELECT *
2 FROM depto;
*
*
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;
*
*
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;
*
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
*
Tipos de Funções de Grupo
AVG (média)
COUNT (contagem)
MAX (máximo)
MIN (mínimo)
STDDEV
SUM (soma)
VARIANCE (variação)
*
Utilizando Funções de Grupo
SELECT column, group_function(column)
FROM table
[WHERE condition]
[ORDER BY column];
*
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”
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);
*
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.
*
*
Stored Procedures
Procedimantos que permanecem armazenados no banco, de forma compilada.
Servem para executar alguma computação quando invocados
*
*
Sintaxe
CREATE OR REPLACE PROCEDURE NOME
( NOME TIPO[,NOME TIPO] )
IS BEGIN
[DECLARE]
<CORPO>
COMMIT;
EXCEPTION
WHEN OTHERS THEN
<CORPO>
END NOME;
/
*
*
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;
/
*
*
Execução
EXECUTE AJUSTE(0.1, 21);
O procedimento é executado. Caso algum erro ocorra, então a tabela de erros será atualizada.
*
*
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;
*
*
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.
*
*
Triggers
Procedimantos especiais guardados no banco de forma compilada
Acionados automaticamente pelo banco quando sua condição de ativação for veradeira
*
*
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
*
*
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;
/
*
*
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
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
Database Objects
An Oracle database can contain multiple data structures. Each structure should be outlined in the database design so that it can be created during the build stage of database development.
Table: Stores data
View: Subset of data from one or more tables
Sequence: Generates primary key values
Index: Improves the performance of some queries
Synonym: Gives alternative names to objects
Oracle8 Table Structures
Tables can be created at any time, even while users are using the database.
You do not need to specify the size of any table. The size is ultimately defined by the amount of space allocated to the database as a whole. It is important, however, to estimate how much space a table will use over time.
Table structure can be modified online.
Note: More database objects are available but are not covered in this course.
Class Management Note
Tables can have up to 1000 columns and must conform to standard database object naming conventions. Column definitions can be omitted when using the AS subquery clause. Tables are created without data unless a query is specified. Rows are usually added by using INSERT statements.
*
Creating Tables
The example above creates the DEPT table, with three columns¾namely, DEPTNO, DNAME, and LOC. It further confirms the creation of the table by issuing the DESCRIBE command.
Since creating a table is a DDL statement, an automatic commit takes place when this statement is executed.
*
Dropping a Table
The DROP TABLE statement removes the definition of an Oracle8 table. When you drop a table, the database loses all the data in the table and all the indexes associated with it.
Syntax
where: table is the name of the table.
Guidelines
All data is deleted from the table.
Any views or synonyms will remain but are invalid.
Any pending transactions are committed.
Only the creator of the table or a user with the DROP ANY TABLE privilege can remove a table.
The DROP TABLE statement, once executed, is irreversible. The Oracle Server does not question the action when you issue the DROP TABLE statement. If you own that table or have a high-level privilege, then the table is immediately removed. All DDL statements issue a commit, therefore making the transaction permanent.
DROP TABLE table;
*
Truncating a Table
Another DDL statement is the TRUNCATE TABLE statement, which is used to remove all rows from a table and to release the storage space used by that table. When using the TRUNCATE TABLE statement, you cannot rollback row removal.
Syntax
TRUNCATE TABLE table;
where: table is the name of the table.
You must be the owner of the table or have DELETE TABLE system privileges to truncate a table.
The DELETE statement can also remove all rows from a table, but it does not release storage space.
*
Truncating a Table
Another DDL statement is the TRUNCATE TABLE statement, which is used to remove all rows from a table and to release the storage space used by that table. When using the TRUNCATE TABLE statement, you cannot rollback row removal.
Syntax
TRUNCATE TABLE table;
where: table is the name of the table.
You must be the owner of the table or have DELETE TABLE system privileges to truncate a table.
The DELETE statement can also remove all rows from a table, but it does not release storage space.
*
Adding a New Row to a Table (continued)
You can add new rows to a table by issuing the INSERT statement.
In the syntax:
table is the name of the table.
column is the name of the column in the table to populate.
value is the corresponding value for the column.
Note: This statement with the VALUES clause adds only one row at a time to a table.
*
Adding a New Row to a Table (continued)
Because you can insert a new row that contains values for each column, the column list is not required in the INSERT clause. However, if you do not use the column list, the values must be listed according to the default order of the columns in the table.
SQL> DESCRIBE dept
Name Null? Type
------------------------------- -------- ------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
For clarity, use the column list in the INSERT clause. Enclose character and date values within single quotation marks; do not enclose numeric values within single quotation marks.
*
Methods for Inserting Null Values
Be sure that the targeted column allows null values by verifying the Null? status from the SQL*Plus DESCRIBE command.
The Oracle Server automatically enforces all datatypes, data ranges, and data integrity constraints. Any column that is not listed explicitly obtains a null value in the new row.
Class Management Note
Common errors that can occur during user input:
Mandatory value missing for a NOT NULL column
Duplicate value violates uniqueness constraint
Foreign key constraint violated
CHECK constraint violated
Datatype mismatch
Value too wide to fit in column
*
Updating Rows
You can modify existing rows by using the UPDATE statement.
In the above syntax:
table is the name of the table.
column is the name of the column in the table to populate.
value is the corresponding value or subquery for the column.
condition identifies the rows to be updated and is composed of column names expressions, constants, subqueries, and comparison operators.
Confirm the update operation by querying the table to display the updated rows.
For more information, see Oracle Server SQL Reference, Release 8.0, “UPDATE.”
Note: In general, use the primary key to identify a single row. Using other columns may unexpectedly cause several rows to be updated. For example, identifying a single row in the EMP table by name is dangerous because more than one employee may have the same name.
Class Management Note
Demo: l9sel.sql, l9upd.sql
Purpose: To illustrate displaying the initial state of data, and performing updates and viewing results.
*
Updating Rows (continued)
The UPDATE statement modifies specific row(s), if the WHERE clause is specified. The example above transfers employee 7782 (Clark) to department 20.
If you omit the WHERE clause, all the rows in the table are modified.
Note: The EMPLOYEE table has the same data as the EMP table.
SQL> SELECT ename, deptno
2 FROM employee;
ENAME DEPTNO
---------- ---------
KING 20
BLAKE 20
CLARK 20
JONES 20
MARTIN 20
ALLEN 20
TURNER 20
...
14 rows selected.
*
Deleting Rows (continued)
You can delete specific row(s) by specifying the WHERE clause in the DELETE statement. The example above deletes the DEVELOPMENT department from the DEPARTMENT table. You can confirm the delete operation by displaying the deleted rows by using the SELECT statement.
Example
Remove all employees who started after January 1, 1997.
If you omit the WHERE clause, all rows in the table will be deleted. The second example on the slide deletes all the rows from the DEPARTMENT table because no WHERE clause had been specified.
Note: The DEPARTMENT table has the same data as the DEPT table.
SQL> SELECT *
2 FROM department
3 WHERE dname = 'DEVELOPMENT';
no rows selected.
SQL> DELETE FROM emp
2 WHERE hiredate > TO_DATE('01.01.97', 'DD.MM.YY');
1 row deleted.
*
Basic SELECT Statement
In its simplest form, a SELECT statement must include the following:
A SELECT clause, which specifies the columns to be displayed
A FROM clause, which specifies the table containing the columns listed in the SELECT clause
In the syntax:
SELECT is a list of one or more columns.
DISTINCT suppresses duplicates.
* selects all columns.
column selects the named column.
alias gives selected columns different headings.
FROM table specifies the table containing the columns.
Note: Throughout this course, the words: keyword, clause, and statement would be used.
A keyword refers to an individual SQL element. For example, SELECT and FROM are keywords.
A clause is a part of an SQL statement. For example, SELECT empno, ename, ... is a clause.
A statement is a combination of two or more clauses. For example, SELECT * FROM emp is a SQL statement.
*
Selecting All Columns, All Rows
You can display all columns of data in a table by following the SELECT keyword with an asterisk (*). In the example on the slide, the department table contains three columns: DEPTNO, DNAME, and LOC. The table contains four rows, one for each department.
You can also display all columns in the table by listing all the columns after the SELECT keyword. For example, the following SQL statement, like the example on the slide, displays all columns and all rows of the DEPT table:
Class Management Note
Let the students know that details of all the tables are given in Appendix B.
SQL> SELECT deptno, dname, loc
2 FROM dept;
*
Column Aliases (continued)
The first example displays the name and the monthly salary of all the employees. Notice that the optional AS keyword has been used before the column alias name. The result of the query would be the same whether the AS keyword is used or not. Also notice that the SQL statement has the column aliases, name and salary in lowercase, whereas the result of the query displays the column headings in uppercase. As mentioned in the last slide, column headings appear in uppercase by default.
The second example displays the name and annual salary of all the employees. Because Annual Salary contains spaces, it has been enclosed in double quotation marks. Notice that the column heading in the output is exactly the same as the column alias.
Class Management Note
Point out the optional AS keyword in the first example and the double quotation marks in the second example. Also show that the aliases always appear in uppercase, unless enclosed within double quotation marks.
*
Duplicate Rows (continued)
To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT keyword. In the example above, the EMP table actually contains fourteen rows but there are only three unique department numbers in the table.
You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns, and the result represents a distinct combination of the columns.
DEPTNO JOB
------ ---------
10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
...
9 rows selected.
SQL> SELECT DISTINCT deptno, job
2 FROM emp;
*
Using the WHERE clause
In the example, the SELECT statement retrieves the name, job title, and the department number of all employees whose job title is CLERK.
Note that the job title CLERK has been specified in uppercase to ensure that the match is made with the job column in the EMP table. Character strings are case sensitive.
Class Management Note
Snippet: Processing a Query
*
Comparison Operators
Comparison operators are used in conditions that compare one expression to another. They are used in the WHERE clause in the following format:
Syntax
… WHERE expr operator value
Examples
… WHERE hiredate='01-JAN-95'
… WHERE sal>=1500
… WHERE ename='SMITH'
Class Management Note
Remind students that the expr cannot be an alias.
*
*
The BETWEEN Operator
You can display rows based on a range of values using the BETWEEN operator. The range that you specify contains a lower range and an upper range.
The SELECT statement above returns rows from the EMP table for any employee whose salary is between $1000 and $1500.
Values specified with the BETWEEN operator are inclusive. You must specify the lower limit first.
Class Management Note
Emphasize that the values specified with the BETWEEN operator in the example are inclusive. Point out that Turner who earns $1500 (higher limit) is included in the output.
Demo: l2betw.sql
Purpose: To illustrate using the BETWEEN operator.
*
The IN Operator
To test for values in a specified list, use the IN operator.
The above example displays employee number, name, salary, and manager’s employee number of all the employees whose manager’s employee number is 7902, 7566, or 7788.
The IN operator can be used with any datatype. The following example returns a row from the EMP table for any employee whose name is included in the list of names in the WHERE clause.
SQL> SELECT empno, ename, mgr, deptno
2 FROM emp
3 WHERE ename IN ('FORD' , 'ALLEN');
If characters or dates are used in the list, they must be enclosed in single quotation marks ('').
Class Management Note
Demo: l2in.sql
Purpose: To illustrate using the IN operator.
*
The LIKE Operator
You may not always know the exact value to search for. You can select rows that match a character pattern by using the LIKE operator. The character pattern matching operation is referred to as a wildcard search. Two symbols can be used to construct the search string.
The SELECT statement above returns the employee name from the EMP table for any employee whose name begins with an “S.” Note the uppercase “S.” Names beginning with an “s” will not be returned.
The LIKE operator can be used as a shortcut for some BETWEEN comparisons. The following example displays names and hiredates of all employees who joined between January 1981 and December 1981.
SQL> SELECT ename, hiredate
2 FROM emp
3 WHERE hiredate LIKE '%81';
*
The IS NULL Operator
The IS NULL operator tests for values that are null. A null value means the value is unavailable, unassigned, unknown, or inapplicable. Therefore, you cannot test with (=) because a null value cannot be equal or unequal to any value. The example above retrieves the name and manager of all employees who do not have a manager.
For example, to display name, job title, and commission for all employees who are not entitled to get a commission, use the following SQL statement:
ENAME JOB COMM
-------- ----------- ------
KING PRESIDENT
BLAKE MANAGER
CLARK MANAGER
...
*
Logical Operators
A logical operator combines the result of two component conditions to produce a single result based on them or to invert the result of a single condition. Three logical operators are available in SQL:
AND
OR
NOT
All the examples so far have specified only one condition in the WHERE clause. You can use several conditions in one WHERE clause using the AND and OR operators.
*
The NOT Operator
The example above displays name and job title of all the employees whose job title is not CLERK, MANAGER, or ANALYST.
NOT Truth Table
The following table shows the result of applying the NOT operator to a condition:
Note: The NOT operator can also be used with other SQL operators such as BETWEEN, LIKE, and NULL.
... WHERE NOT job IN ('CLERK', 'ANALYST')
... WHERE sal NOT BETWEEN 1000 AND 1500
... WHERE ename NOT LIKE '%A%'
... WHERE comm IS NOT NULL
*
Default Ordering of Data
The default sort order is ascending:
Numeric values are displayed with the lowest values first¾for example, 1–999.
Date values are displayed with the earliest value first¾for example, 01-JAN-92 before 01-JAN-95.
Character values are displayed in alphabetical order¾for example, A first and Z last.
Null values are displayed last for ascending sequences and first for descending sequences.
Reversing the Default Order
To reverse the order in which rows are displayed, specify the keyword DESC
after the column name in the ORDER BY clause. The example above sorts the result by the most recently hired employee.
*
Defining Joins
When data from more than one table in the database is required, a join condition is used. Rows in one table can be joined to rows in another table according to common values existing in corresponding columns, that is usually, primary and foreign key columns.
To display data from two or more related tables, write a simple join condition in the WHERE clause. In the syntax:
table.column denotes the table and column from which data is retrieved.
table1.column1 = is the condition that joins (or relates) the tables together. table2.column2
Guidelines
When writing a SELECT statement that joins tables, precede the column name with the table name for clarity and to enhance database access.
If the same column name appears in more than one table, the column name must be prefixed with the table name.
To join n tables together, you need a minimum of (n-1) join conditions. Therefore, to join four tables, a minimum of three joins are required. This rule may not apply if your table has a concatenated primary key, in which case more than one column is required to uniquely identify each row.
For more information, see
Oracle Server SQL Reference Manual, Release 8.0, “SELECT.”
*
*
Group Functions
Unlike single-row functions, group functions operate on sets of rows to give one result per group. These sets may be the whole table or the table split into groups.
*
*
Guidelines for Using Group Functions
DISTINCT makes the function consider only nonduplicate values; ALL makes it consider every value including duplicates. The default is ALL and therefore does not need to be specified.
The datatypes for the arguments may be CHAR, VARCHAR2, NUMBER, or DATE where expr is listed.
All group functions except COUNT(*) ignore null values. To substitute a value for null values, use the NVL function.
Class Management Note
Stress the use of DISTINCT and group functions ignoring null values. ALL is the default and is very rarely specified.
*
Group Functions
You can use AVG, SUM, MIN, and MAX functions against columns that can store numeric data. The example above displays the average, highest, lowest, and sum of monthly salaries for all salesmen.
*
Group Functions (continued)
You can use MAX and MIN functions for any datatype. The example above displays the most junior and most senior employee.
The example below displays the employee name that is first and the employee name that is the last in an alphabetized list of all employees.
Note: AVG, SUM, VARIANCE, and STDDEV functions can be used only with numeric datatypes.
SQL> SELECT MIN(ename), MAX(ename)
2 FROM emp;
MIN(ENAME) MAX(ENAME)
---------- ----------
ADAMS WARD
*
The COUNT Function
The COUNT function has two formats:
COUNT(*)
COUNT(expr)
COUNT(*) returns the number of rows in a table, including duplicate rows and rows containing null values.
In contrast, COUNT(expr) returns the number of nonnull rows in the column identified by expr.
The example above displays the number of employees in department 30.
Class Management Note
Demo: l5count1.sql, l5count2.sql
Purpose: To illustrate using the COUNT(*) and COUNT(expr) functions.
*
SQL> SELECT COUNT(deptno)
2 FROM emp;
COUNT(DEPTNO)
-------------
14
COUNT(DISTINCT(DEPTNO))
-----------------------
3
*
Groups of Data
Until now, all group functions have treated the table as one large group of information. At times, you need to divide the table of information into smaller groups. This can be done by using the GROUP BY clause.
*
The GROUP BY Clause
You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group.
In the syntax:
group_by_expression specifies columns whose values determine the basis for grouping rows.
Guidelines
If you include a group function in a SELECT clause, you cannot select individual results as well unless the individual column appears in the GROUP BY clause. You will receive an error message if you fail to include the column list.
Using a WHERE clause, you can preexclude rows before dividing them into groups.
You must include the columns in the GROUP BY clause.
You cannot use the column alias in the GROUP BY clause.
By default, rows are sorted by ascending order of the columns included in the GROUP BY list. You can override this by using the ORDER BY clause.
*
The GROUP BY Clause (continued)
The GROUP BY column does not have to be in the SELECT clause. For example, the above SELECT statement displays the average salaries for each department without displaying the respective department numbers. However, without the department numbers, the results do not look meaningful.
You can use the group function in the ORDER BY clause.
Class Management Note
Demonstrate the query with and without the DEPTNO in the SELECT statement.
DEPTNO AVG(SAL)
---------- ------------
30 1566.6667
20 2175
10 2916.6667
*
Groups Within Groups (continued)
You can return summary results for groups and subgroups by listing more than one GROUP BY column. You can determine the default sort order of the results by the order of the columns in the GROUP BY clause. Here is how the SELECT statement above, containing a GROUP BY clause, is evaluated:
The SELECT clause specifies the column to be retrieved:
Department number in the EMP table
Job title in the EMP table
The sum of all the salaries in the group you specified in the GROUP BY clause
The FROM clause specifies the tables that the database must access: the EMP table.
The GROUP BY clause specifies how you must group the rows:
First, the rows are grouped by department number.
Second, within the department number groups, the rows are grouped by job title.
So the SUM function is being applied to the salary column for all job titles within each department number group.
*
Subqueries
A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself.
You can place the subquery in a number of SQL clauses:
WHERE clause
HAVING clause
FROM clause
In the syntax:
operator includes a comparison operator such as >, =, or IN
Note: Comparison operators fall into two classes: single-row operators (>, =, >=, <, <>, <=) and multiple-row operators (IN, ANY, ALL).
The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally executes first, and its output is used to complete the query condition for the main or outer query.
Class Management Note
Additionally, subqueries can be placed in the CREATE VIEW statement, CREATE TABLE statement, UPDATE clause, INTO clause of an INSERT statement, and SET clause of an UPDATE statement.
*
Using a Subquery
In the slide, the inner query determines the salary of employee 7566. The outer query takes the result of the inner query and uses this result to display all the employees who earn more than this amount.
Class Management Note
Execute the subquery (inner query) on its own first to show the value that the subquery returns. Then execute the outer query using the result returned by the inner query. Finally, execute the entire query (containing the subquery) and show that the result is the same.
*
Guidelines for Using Subqueries
A subquery must be enclosed in parentheses.
A subquery must appear on the right side of the comparison operator.
Subqueries cannot contain an ORDER BY clause. You can have only one ORDER BY clause for a SELECT statement, and if specified
it must be the last clause in the main SELECT statement.
Two classes of comparison operators are used in subqueries: single-row operators and multiple-row operators.
Class Management Note
A subquery can execute multiple times in correlated subqueries, which are not included in this course. Students may ask how many subqueries can be written. The Oracle Server imposes no limit on the number of subqueries. The limit is related to the buffer size that the query uses.
