Aula01-Banco de Dados

5/8/2018 Aula01-Banco de Dados - slidepdf.com

http://slidepdf.com/reader/full/aula01-banco-de-dados 1/245

1

Banco de Dados I

Pitágoras Martins



2

Banco de Dados

Introdução

Histórico de Banco de

Dados Dado x Informação

Análise de Dados

Projeto Lógico

Projeto Físico

Lista de Eventos

Compartilhamento deDados x Arquivos

Isolados Conceitos de Banco de

Dados

Arquiteturas de SGBD

Níveis de Abstração SGBD x SGA



3

Banco de Dados

Características de umBanco de Dados

Usuários do Banco deDados Implicações no

Esquema de Banco deDados

O que ocorre quandonão se usa um SGBD

Modelo de Dados,Esquemas e Instânciasde Dados

Arquitetura do DBMS Independência de

Dados

Interfaces O ambiente do Banco

de Dados



4

Banco de dados

Tipos de SGBD

Níveis de Modelagem

Modelo Entidade ± Relacionamento

Modelagem de Dados

Comunicação deDados

SGBD Relacional

Diagrama de Entidade- Relacionamento

Ferramenta deModelagem

Definição de Dados

Domínios de Relações

Chaves Primárias

Chaves Estrangeiras



5

Banco de dados

Regras de Integridade

Dependências

Funcionais Normalizações

Linguagem deDefinição de Dados

Linguagem deManipulação de Dados



6

Referências

Fundamentals of Database System ± Elmarsi/Navathe

Sistemas de Banco de Dados ± C.J.Date Sistemas de Banco de Dados - Sylberchartz



7

Histórico do Banco de Dados

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



8


Desenvolvimento de software ± 1976: Projeto Estruturado (Yordon /

Constantine, Michael Jackson, Warnier ± Orr)� Foco: ao nível de problema

� Objetivos:

± Modularidade

± Padronização do projeto



9


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



10


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



11


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



12

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

ProcessamentoDado Informação



13

Estratégico

Análise de Dados

Nível de Decisão Tipo de Informação

Tático

Operacional

Projeções

ComparaçõesAD-HOC

ComparaçõesResumos

Informações detalhadasInformações Precisas



14

Análise de Dados

AS = Análise de Dados + Análise de Funções

Por onde começar ?

TFunções

Dados

Uns preferem pela ³AD´ outros preferem pela³AF´, mas todos já concordam: deve-se fazer Análise de Dados ³AD´´



15

Análise de Dados

Ato de Construir modelos conceituais dedados, através deabstrações, a partir domundo real, buscandosimplificação rumo àimplementação física

Conceitual

Lógico

Físico

Mundo Real



16

Análise de Sistemas

Análise de Dados (DER)

Análise de Funções (DFD)

Análise de Sistemas

+



17

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

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

Projeto Lógico

� Declaração de Objetivos (DO)� Lista de Eventos (LE)

� Diagrama de Entidade relacionamento (DER)� Dicionário de Dados (DD)� Especificação da Lógica do Processo (ELP)



20

Projeto Físico

� Projeto Detalhado de:� Saídas

� Arquivos� Entradas� Controles no sistema

� Especificação de Programas



21

Declaração de Objetivos (DO)

Descrever de forma sucinta e objetiva, em uma página, afinalidade do sistema.

Ex: O sistema de Compra e Venda de Mercadorias temcomo objetivos automatizar de forma integrada asatividades de:

� Compras de mercadorias� Vendas de mercadorias

� Controle de estoque� Contabilização



22

Lista de Eventos

Descrever todos os eventos que ocorrem,ocorreram ou poderão ocorrer no sistema,

classificando-os em três tipos:

± Orientados ao fluxo

± Temporais

± Controle



23

Lista de Eventos

Eventos orientados a fluxo de dados

Ex:

± Cliente envia pedido

± Cliente faz pagamento

± Cliente devolve mercadoria

± Fornecedor envia mercadorias

± Fornecedor envia fatura

± Departamento de vendas define política de vendas



24

Lista de Eventos

Eventos temporais

Ex: ± 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

Lista de Eventos

Eventos de Controle

Ex:

± Ao atingir o ponto de ressuprimento, emitir pedido ao fornecedor

± Ao exceder o limite de crédito, bloquear ocliente por excesso



26

Diagrama de Entidade

Relacionamento (DER)

Trabalha

Aloca

TemPossui

Chefia

Dependentes

Departamentos

Funcionários FunPro Projetos



27

Especificação da Lógica do

ProcessoVL_PMED = VL_AREA / VL_TESSe VL_PMED < 24

VL_PMED = (2 * VL_AREA) / (VL_PMED + 36)Se VL_PMED > = 24 E VL_PMED < 36VL_TEST = (2 * VL_PMED) / (VL_PMED + 36) * VL_TESSe VL_PMED > = 36VL_TEST = ((1,8 * VL_PMED) / (VL_PMED + 94)) + ((1,8 * VL_PMED) / (2,6 * VL_PMED + 36)) * VL_TESObs: VL_PMED - Valor da profundidade média do terreno

VL_TES - Valor da testada realVL_TEST - Valor da testada fictícia do terreno



28

Compartilhamento de Dados x

Arquivos Isolados Arquivos convencionais servem a sistemas específicos

(isolados) com pouco ou nenhum compartilhamentocom 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



29


Arquivos Isolados 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ísicados dados nos arquivos: alteração no lay-out doregistro significa alteração nos programas



30


Arquivos Isolados

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

Arquivos Isolados



31


Arquivos Isolados Sistemas Integrados

± Dados de uma mesma área são armazenados em

um mesmo conjunto de arquivos ± Um sistema grava saída em arquivo em meiomagnético a ser usado como entrada por outrosistema

± Alteração no layout de arquivos compartilhadoimplica alteração em programas de mais de umsistema



32


Arquivos Isolados

nome, endereço, cpf-cgc, dt_nasc

Conta Corrente

num_atual_cotas, dt_nasc


Arquivos Integrados

Num_conta, sld_atual, sld_medio, cpd-cgc



33


Arquivos Isolados Sistemas que usam SGBD

± Integração de dados não apenas por área, mas por todaa Empresa

± Dados da empresa compartilhados por todos ossistemas

± Sistemas ³enxergam´ ps dados independentemente desua distribuição pelos arquivos

± Programas de aplicação ignoram detalhes dearmazenamento/recuperação de dados ± Sem redundância de dados não há inconsistência



34


Arquivos Isolados

nome, endereço, cpf-cgc, dt_nasc

Conta Corrente

num_atual_cotas, cpf-cgc


Sistemas que usam SGBD

Num_conta, sld_atual, sld_medio, cpd-cgcSGBD



35

Conceitos de Banco de Dados

CHU- 1983

± Um banco de dados é um conjunto de arquivos

relacionados entre si. DATE - 1985

± Um banco de dados é uma coleção de dadosoperacionais armazenados usados pelossistemas de aplicação de uma determinadaorganização.



36

Conceitos de Banco de Dados

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 dadosoperacionais usados pelos sistemas aplicativosde uma empresa.



37

Dados Operacionais

Projetos

FornecedoresProdutos Departamentos

Funcionários

Fornece Lota

Aloca



38

Um Sistema de BD se compõe

de: Dados: em um ou mais arquivos

Metadados: dicionário de dados

Software: SGBD (interface entre usuários edados)

Hardware: discos

Usuários: DBA, Programadores deAplicação, Usuários Finais



39


de: SGBD

± dá suporte ao acesso dos usuários aos dados

± esconde do usuário detalhes de armazenamentodos dados

Programador de Aplicação

± codifica programas batch ou on-line paramanter o DB



40


de: 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/sintonizar/reorganizar o BD



41


de: Usuário Final

± Acessa o BD via:

� query language� programas de aplicação



42

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)



43

Bancos de Dados x Linguagens

DDL - Linguagem de definição de dados(create, drop, alter...)

DML - Linguagem de manipulação dedados (insert, delete, update, ...)

DCL - Linguagem de controle de acesso aos

dados (grant, revoke, ...) QL - Linguagem de consulta (select)



44

Por que usar SGBD ?

BD = Dados + Metadados

Um sistema de Banco de Dados contém, além

dos dados normais, uma descrição completadesses dados num dicionário de dados(catálogo)



45

Qual a finalidade de usar DD ?

Abstração, ou seja, deixar o usuário tratar osdados em alto nível, enquanto o SGBD usa

o DD para tratar os dados fisicamente(baixo nível)



46

Por que usar SGBD ?

Abstração de dados ± Um SGBD permite uma representação

conceitual dos dados evitando preocupaçãocom 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



47

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



48

Por que usar SGBD ?

Segurança contra acesso indevido ± Diferentes visões asseguram confidencialidade

Múltiplas Interfaces ± Acesso via menu, linguagem natural, query

language, linguagem de programação



49

Por que usar SGBD ?

Relacionamentos complexos ± Uma entidade pode se relacionar com outras de

forma binária, ternária, ... Restrições de integridade

± Armazenadas no BD ³enxugam´ os programasde aplicação



50

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çãode programas

Recuperação do BD ± Feito através de check-points



51

Arquiteturas de SGBD Arquitetura de 3 níveis

(Visões) ± Visões de usuário

± Visão total do BD

± Visão doarmazenamento

...



52


(Esquemas) ± Sub-esquemas

± Esquema Conceitual

± Esquema Físico

...



53


(CODASYL - 71)(Conference on Data System and

Language) ± Visão

± Conceitual

± Físico

...



54


(ANSI/SPARC 75)( American. Nacional Standart

Institute / System Planning and RequirementsCommitee)

± Externo

± Conceitual

± Externo

...



55

Níveis de Abstração

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



56


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



57


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 modeloconceitual

± Administrador de Banco de Dados: DBA



58

SGBD x SGA

SGBD - software responsável pela definiçãoe manutenção de um BD

SGA - conjunto de rotinas do sistemaoperacional responsável pelo sistema dearquivos (SA) e respectivos métodos de

acesso SA - arquivos convencionais (sequenciais,

indexados, randômicos) e suas operações



59

SGBD x SGA

MáquinaMáquina

Mundo realMundo real

MER MER SGBDOOSGBDOORelacionalRelacional

RedeRedeHierárquicaHierárquica

SGBD EspecíficoSGBD EspecíficoSGASGA



60

SGBD X SGA

SGBD

SGA

Alto nívelAlto nível

Baixo nívelBaixo nível



61

Características do BD

É mantido um único local (repositório) dedados, onde todos os usuário têm acesso

O BD contém além dos dados de tabelasuma tabela que guarda a descrição de todasas demais (Dicionário de dados)

Isolamento entre os programas e dados eabstração de dados



62

Características do BD

Suporta múltiplas visões dos dados

Compartilha dados

Processa várias transações de usuário aomesmo tempo



63

Usuários do BD

Administradores do BD

Projetistas de BD

usuários finais ± Casual

± Ingênuos

± Sofisticados ± Isolados



64

Usuários do BD

Analistas de Sistemas

Programadores

Projetistas de DBMS Desenvolvedores de ferramentas

Operadores

Pessoal de manutenção



65

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 derelacionamentos entre dados



66

Objetivos de um SGBD

Garantir restrições de integridade

Backup e Recuperação



67

Implicações no uso de um SGBD

Garantir padrões

Redução no tempo de desenvolvimento

Flexibilidade Disponibilidade de informações on-line



68

Desvantagens em usar um SGBD

Alto investimento em hardware, software etreinamento

Modelo geral de definição e processamentode dados

Excesso de segurança, controle de

concorrência, recuperação e funções deintegridade



69

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 bancode dados

Um modelo de dados se compõe de tipos deobjetos, relacionamentos entre objetos erestrições de objetos



70

Modelo de Dados

As operações básicas fazem parte damaioria dos modelos de dados

Ferramenta usada para descrever a estruturado BD

O modelo de dados se compõem de:

± Estruturas de dados ± Operações ± Restrições de integridade



71

Modelo de Dados

Os modelos de dados se dividem em trêsníveis:

± Conceitual (alto nível) ± Lógico

± Físico (baixo nível)



72

Modelo de Dados

Conceitual: Descreve dados que estão próximos daforma 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 dadosao nível de registro bem próximo da

implementação física ± Ex: Relacional, Rede e Hierárquico



73

Modelo de Dados

Físico: Descreve as estruturas de dados aonível de armazenamento físico dos dados



74

Mundo real

Lógico

Físico

Conceitual

Fase de Projeto do BD

RelacionalRede

Hierárquico

SGBD específico

SGA

Semântico/MER

Modelo de Dados



75

Conceitual

Entidades: representa um objeto do mundoreal ou conceito

Atributos: propriedades que descrevem umaentidade

Relacionamentos: interações entre entidades



76

ProjetosFuncionários

Cod. ProjetoNome ProjetoValor Projeto

Cod. FuncionárioNome FuncionárioEndereço Funcionário

Projetos têm Funcionários

Um exemplo



77

Lógico

São usados na maioria dos DBMS, incluindo ostrê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



78

Físico

Descrevem como os dados são armazenadosno computador através da representação da

informação tal como formato, ordem eacesso aos registros



79

Esquemas e Instâncias

Esquema: Descrição gráfica ou textual doBD 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 algunsaspectos do BD



80

Esquemas e Instâncias

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.



81

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 dadosfísicos e descreve detalhes de armazenamento e

caminhos para BD.



82

Arquitetura do BD

± Nível conceitual: descreve a estrutura de todo oBD para os usuários. Esconde os detalhes asestruturas de armazenamento físico e se

concentra na descrição de entidades, tipos dedados, relacionamentos, operações de usuáriose restrições. Um modelo de dados de ato nívelou modelo de implementação pode ser usadoneste nível



83

Arquitetura do BD

± Nível externo: descreve a parte do BD a umgrupo de usuários (visões) e esconde o restantedo BD. Um modelo de alto nível ou modelo de

implementação é usado neste nível



84

Visão externa1

Esquema Interno

...

Esquema Conceitual

Armazenamento do BD

Usuários Finais

Visão externa2



85

Mapeamento de dados

Processo de transformar requisições deusuários feitas no nível externo aos níveis

mais internos e retornar uma resposta aousuário



86

Independência de dados

Capacidade de fazer modificações no bancode 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



87

Independência Lógica

Capacidade de modificar o esquemaconceitual sem modificar o esquema

externo ou programas de aplicação



88

Independência Física

Capacidade de modificar o esquema internosem modificar o esquema externo ou

programas de aplicação (Reorganização dearquivos)



89

Linguagens do BD

DDL - Linguagem de Definição de Dados

SDL - Linguagem de Definição de

Armazenamento VDL - Linguagem de Definição de Visão

DML - Linguagem de Manipulação de

Dados



90

DDL

Usada pelo DBA ou por qualquer projetistado BD

Serve para definir os esquemas interno econceitual quando a arquitetura é de 2 níveis

Define a construção do esquema e adescrição do esquema de armazenamento



91

SDL

Usada para especificar o esquema interno

O mapeamento entre os dois esquemas pode

ser feito com a SDL ou a DDL



92

VDL

Usada para especificar as visões e faz omapeamento com o esquema conceitual



93

DML

Usada para manipular o banco de dadoscomo 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



94

Interfaces

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



95

O ambiente do BD

Catalogo

SO

Gerenciador de acesso a disco Compilador de DDL

Processador do BD

Compilador de consultas Précompilador

Compilador de DML



96

Utilitários do BD

Carga de arquivos existentes

Backup/Recovery

Reorganização de arquivos Monitoramento de performance

Software de comunicação



97

Tipos de SGBD

Hierárquico

Rede

Relacional Objeto-Relacional

Orientado-Objeto

Outros



98

Hierárquico Lógico

Entidades implementadas como segmentose relacionamentos como ponteiros

Restrições de integridade: ± exceto a raiz qual registro deve ter um pai

Operações: get first, get next, ...



99

Hierárquico Físico

Dados armazenados usando estruturasembutidas

Usuário vê um conjunto de árvores Ligações explicitas via ponteiros no nível

físico

Navegação através de ponteiros e deresponsabilidade do programador

Tem dependência de dados



100

Rede Lógico

Entidades como coleções de registros erelacionamentos 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, ...



101

Rede Físico

Dados organizados através de uma rede denó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



102

Relacional Lógico

Entidades como tabelas e relacionamentoscomo 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 deveexistir um valor de PK em outra tabela

Operações: seleção, projeção, junção, ...



103

Relacional Físico

Dados armazenados de forma tabular

Usuário vê um conjunto de tabelas

Ligações implícitas compartilhando chavesnum nível lógico

Navegação automática: otimizadores de

consultas



104

Classificação de BD

MER RELACIONAL REDE HIERARQUICO

Entidade Tabelas Grafos Árvores

Entidade Linhas Registro Segmento

RelacionamentoImplícito (pares

de colunas)Explícito

(ponteiros)Explícito

(ponteiros) Atributos Coluna Campo Campo



105


A

B

E

C

D

MER

1 A

3 C 1 4 D 2

2 B 1

5 E 2 3

RELACIONAL



106


A

B

E

C

D

MER REDE

A

B

E

C

D



107


A

B

E

C

D

MER HIERÁRQUICO

A

B

E

C

D E



108

SGBD¶s Hierárquicos

IMS (IBM)

SYSTEM 2000 (SAS)

TDMS (BURROUGHS) MARS VI (CDC)

RFMS (Univ. Texas)



109

SGBD¶s Rede

IDS II (Bull)

DMS II (UNISYS)

IDMS ( CULLINAME) DMS 1100 (UNIVAC)

DBMS-11 (DEC)

TOTAL (CINCON) ADABAS (Software AG)



110

SGBD¶s Relacional

ORACLE

INGRES

DB2

INFORMIX

RDB

PROGRESS SQL SERVER



111

Por que modelagem de dados ?

Com o crescente uso de SGBD¶s em face danecessidade 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 emvários arquivos



112


Aplicações desenvolvidas com ³L4G´dependem de uma rígida administração de

dados - cada usuário projeta os dados de seuuso específico sem tomar conhecimento dasnecessidades dos demais usuários

Principal virtude dos modelos de daods:

estabilidade



113


Enquanto modela dados o analistaconcentra-se nesta tarefa ³esquecendo´

software e SGBD Os relacionamentos representam a obtenção

de respostas a certas necessidades deinformação por parte do usuário



114


Modelo prévio de dados é um excelenteinstrumento 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 projetoconceitual de BD



115

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, ...



116

MER

Relacionamento - associação entreelementos de conjuntos de entidades

Relacionamento Binário - é um par ordenado (e1, e2), onde e1 e e2 sãorespectivamente elementos dos conjuntosdas entidades E1 e E2

Alunos DisciplinasCursa

Nota PeríodoMat Nom Cod Des



117

MER

Conjunto de Relacionamentos - Grupo derelacionamentos do mesmo tipo; no caso

binário, conjunto de pares ordenados C(a,b)onde a �A e d � D

Grau de um relacionamento - número deconjuntos de entidades envolvidos no

relacionamento ± Ex: unário, binário. ternário, ...



118

MER

Classe ou cardinalidade dosrelacionamentos - razão ente as quantidadese entidades com que cada conjunto participado relacionamento ± Ex: Binários - 1:1 1:n m:n

Ternários - 1:m:n m:n: p



119

MER

Funcionários

Projetos

Chefia

Hor_trab

Dt_posse

Dt_aloc

Trabalha

Departamentos

Chefia

Chefia



120

MER

Atributos ± São características de entidades ou de

relacionamentos

± São funções que levam um ponto do conjuntode entidades ou do relacionamento a um pontode conjuntos de valores

F M

Funcionários 100200Matricula

Sexo



121

MER

Relacionamento unário ou recursivo ouauto-relacionamento

Pessoas

casa

11

MaridoEsposa

Papel da entidade

Produtos

compõe

nm

É componenteÉ composto



122

MER

Itens

Pedidos

RequisiçõesP_R_Im 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



123

MER

Professores

Disciplinas

AlunosP_A_D1 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



124

MER

Sistemas

Funcionários

AtividadesExecutam p

q

Funcionários

n

Nmf Mat Nms

Cds

Cdc Nmc

Cda

Nma

MER



125

MER

Entidade Fraca ± Sua existência depende de outra

± Seus atributos, em geral, são insuficientes paradeterminar uma chave primária

Funcionários DependentesDepende

1 n

MER



126

MER

Chave primária ± Conjunto de atributos que identificam uma

única entidade

± Ex: matrícula, cod_produto, cod_cli, CPF, ...

MER



127

MER

Cardinalidade dos relacionamentos ± Um relacionamento pode ter a cardinalidade

alterada com o tempo ou conforme asnecessidades de informação por parte dousuário

± Ex: Casamento entre pessoas

Atual (1h x 1m) ... 1 : 1Atual (1h x 1Poligamiam) ... 1 : n

Histórico de todos os casamentos ... M : n

MER



128

MER

Itens

Lojas

Vende

n

1

Itens

Lojas

Vende

n

m

Loja única Abertura de novas lojas

Cardinalidade dos casamentos

MER



129

MER

Cardinalidade dos relacionamentos

Departamentos FuncionáriosAloca1 n

Departamentos FuncionáriosAlocam n

Qual a alocação atual do funcionário ?

Data

Um funcionário pode estar lotado simultaneamente em maisde um departamento: devia haver um atributo tipo percentualde dedicação do funcionário ao departamento

Em quais departamentos esteve ou está lotado o funcionário ?

MER



130

MER

Análise Sentencial ± Substantivos (sujeitos/objetos) - entidades

± Verbos (predicados) - relacionamentos

Casas PessoasMora

TemCarros

MER



131

MER

Análise Sentencial ± Adjetivos - atributo de entidades

± Advérbios - atributo de relacionamentos

Pessoas Possui Carros

Nome Profissão Nome ProfissãoDt_aquisição

José, advogado, possui um volks azul desde 1987

MER



132

MER

Análise Sentencial ± Entidade tem atributos

± Ex:

nota fiscal tem série, data, númerocarro tem marca, cor, modelo

± Atributo de entidade é valor

± Ex: o número da nota fiscal é 1234

a idade da pessoa é 36

E t õ d MER



133

Extensões do MER

Atributos compostos - formados por maisde um atributo

Atributos multivalorados - levam umaentidade a mais de um ponto do conjunto devalores

Clientes

Fones

Endereço

Logradouro CEP

Apto Num Nome

E t õ d MER



134

Extensões do MER

Parcialidade / Totalidade ± Nem todo funcionário gerencia um

departamento (parcial)

± Todo departamento é gerenciado por umfuncionário (total)

Funcionários Gerencia Departamentos1 1

E t õ d MER



135

Extensões do MER

Relacionamentos totais e parciais

Pessoas

Nasce

Cidades

1 1

Mora

Casa

1 1

n 1 Nome Dt_nasc Nome Pop

E t õ d MER



136

Extensões do MER

Cardinalidade dos relacionamentos comvalores máximo e mínimo de entidades quese associam

Alunos Cursa Disciplinas(1,6)

NomeMat

(0,40)

Nota Período Cod Des

E tensões do MER



137

Extensões do MER

Generalização x Especialização

Clientes

PJPF

Cod

Nome

Cod

CGC

Cod

CPF Sexo Est_civil

Ender

Tipo

Capital Mes_in Nu_meses_ex

As subcategorias (subtipos) herdam os atributos da categoria supertipo)

Extensões do MER



138

Extensões do MER

Hierarquia de generalização - uma categoriade entidades C é uma generalização de umconjunto de entidades S1, S2, ..., Sn,chamadas subcategorias de C, se cadaconjunto de entidades Si for um sub-conjunto de C e cada instância de C for uma

instância de um e somente um dosconjuntos de entidades S1, S2, ..., Sn

Extensões do MER



139

Extensões do MER

Agregação

Médicos

Prescreve

PacientesAtendem n

Exames

Consultas

Um exame é prescrito por um médico para um paciente

Extensões do MER



140

Extensões do MER

Agregação - é uma abstração que:

± permite que relacionamentos sejam tratadoscomo entidades de nível mais alto

± permite modelar um relacionamento entre umaentidade e um relacionamento (este geralmentem: n)

Extensões do MER



141

Extensões do MER

Chave primária - conjunto de atributos queidentificam uma única entidade ± Ex: CDD, mat, matf + num

Chave candidata - conjunto de atributos que podem ser chave primária ± Ex: mat, RG, CDD

Departamentos DependentesFuncionários

CDD Nmd Mat Nmf Sexo RG Matf Nud NmdSal

CoDep

Extensões do MER



142

Extensões do MER

Chave secundária ou alternada - conjunto deatributos que identificam um grupo deentidades ± Ex: sal, sexo, ...



CoDep

Extensões do MER



143

Extensões do MER

Chave estrangeira - conjunto de atributosque são chaves primárias em outro conjuntode entidades ± Ex: codDep, matf, ...



CoDep

Extensões do MER



144

Extensões do MER

Chave estrangeira ou surr o gate key- númeroseqüencial sem qualquer semântica,introduzido para ser chave primária de umconjunto de entidades ± Ex: nud, mat, ...



CoDep

Extensões do MER



145

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, nemtotalmente)

Mapeamento MER X DER



146

Mapeamento MER X DER

Funcionários Projetos

Mat Nmd Cdp NmpCdpDt_aloc

Funcionários Projetos

Mat Nmd Cdp Nmp

Aloca

Dt_aloca

n 1

Chave primária do lado 1 vai para o lado nAtributos do relacionamento vão para o lado n

MER X DER



147

MER X DER

Alunos Disciplinas

Cda Nma Cdd NmdCdpDt_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 chaveprimária da entidade intermediária

Nota

Al-diCddCda

MER X DER



148

MER X DER

Médicos Hospitais

CdmNmm Cdm Nmm

Médicos Hospitais

CdmNmm Cdh Nmh

Gerencia

Dt_posse

1 1

� Atributos do relacionamento vão para a entidade quetem participação total no relacionamento� Chave primária da entidade do lado parcial vai para aentidade que participa totalmente

CdmDt_posse

MER X DER



149

MER X DER

Funcionários

Mat

Nom

Chefia

Chefe 1

nSubordinado

Funcionários

Mat NomMatchefe

MER X DER



150

MER X DER

Peças

Cod

des

Compõe

Componentes m

nCompostos

Funcionários

Mat Nom Cd-composto

Qtd

Compõe

Cd-componente

Qtd

MER X



151

MER X ...

Funcionários

Mat NomGerencia

Dt_posse

11

Dt_aloc

DepartamentosAloca

CursaChefia

1 nCursosn m

n 1

Cdd Des

Cdct Nmc

X DER



152

... X DER

Funcionários

Mat Nom

Dt_posse Mat-ger

Departamentos

Cursos

Cdd Des

Cdct Nmc

Cursa

CdctMat Dt_cur

Mat-ch

Chefe Subordinado

Dt_aloc

Cdd

MER X DER



153

MER X DER

Modelo Físico

Modelo Lógico

Modelo Conceitual

MundoReal

Modelo Relacional .....

DER .....................................MER......................................

MER X DER



154

MER X DER

MER - os atributos de uma entidade nãodevem aparecer em outra

DER - os atributos de uma entidade podemaparecer em outra para estabelecer orelacionamento

MER X DER



155

MER X DER

A BR 1 1

AR CA AA CB AB

A B

AR

CA AA CB AB

CB

A BR 1 N

AR CA AA CB AB

A B

AR

CA AA CB AB

CA

A BR M N

AR AA CB AB

A B

CA AA CB ABCAABAR

CA CB

MER X DER



156

MER X DER

Relacionamento M x N se transforma em 2 relacionamentos 1 : n

Funcionários DepartamentosLotações

Mat CddCddNom Mat NmdDat

100 Ari 100 Adm Jan/98 Ven Vendas200 Ana100 Ven Mar/99 Pro Produção300 Eva 200 Ven Out/00 Adm Adminst

300 Pro Mai/01

MER X DER



157

MER X DER

A

C

1

B11

CA AATipo A

CB

CA AATipo

ACABCA CAAB AC

MER X DER



158

MER X DER

A

C

MB

N

CA AA

CCAC

R

P CB AB

A

C

B

CA AA

CCAC

CB AB

R

AR CA CC CB AR

MER X DER



159

MER X DER

A

C

MB

N

CA AA

CCAC

R

P CB AB

AR

S

QAS

A

C

B

CA AA

CCAC

CB AB

AS

R

CA CB AR

SCACCCB

Modelagem com ferramentaCAS



160

CASE Metodologia IDE1X

± Desenvolvida pela US Air Force

± Usada por vários órgãos do governo americano

± Representa relacionamentos via chavescompartilhadas

± Usada por grandes empresas de diversas áreas

Modelagem com ferramentaCASE



161

CASE Metodologia IDE1X...

± Trata chaves como o modelo relacional

± A chave primária (PK) do lado 1 vai para olado 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 dachave primária no lado N

Modelo Relacional



162

Modelo Relacional

Baseia-se na teoria dos conjuntos

Os elos são implícitos

O usuário vê o banco de dados como umconjunto de tabelas

Mat Nome Dep

100 Ana Ven200 Edu Pro300 Ari Adm400 Eva |Mar

Funcionários Nome da tabela

Atributos

Tuplas

Valor de atributoDomínios

Modelo Relacional



163

Modelo Relacional

Esquema da relação (tabela): funcionários(mat, nom, dep)

Relação - subconjunto do produtocartesiano dos domínios R � D1 x D2 x ...X Dn

100200

300

400

Ana

EduEva

Ari

Adm

MarPro

Ven

NomesMatrículas Departamentos

Modelo Relacional



164

ode o e ac o a

Uma relação pode ser representada sobforma 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



165

Um relação pode ser representada sobforma de tabela onde: ... ± Domínio - conjunto de valores do atributo

± Grau de uma relação - número de atributos datupla da relação

± Chave primária - um ou mais atributos que

identificam uma única linha ± Ex: aluna (mat, nom, dt_nasc, sexo)

Normalização



166

ç

MER

Modelo Lógico

Arquivos(Top-Down)

MundoReal

Modelo Lógico

Normalização

Arquivos(Botton-Up)

MundoReal

Normalização



167

ç

Simplificação de relações a fim de que osalgoritmos de atualização de dados sejammais simples e mais claros

Como sub-produto: eliminação daredundância de dados

Resultado das pesquisas de E. F. Codd

Normalização



168

ç

A matrícula de uma 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: N omedepende funcionalmente de matrícula

Normalização



169

ç

Dependência Funcional

Func (mat, nome, sal, CPF)

Mat NomeMat SalMat CPF

CPF SalCPF NomeCPF Mat

Mat

CPF

Sal

Nome

Normalização



170

ç

Dependência Funcional Completa:

± Um conjunto de atributos B ed R écompletamente dependente funcionalmente de

outro conjunto de atributos A de R, se Bdepende funcionalmente de A como um todo

Normalização



171

ç

Tarefa (mat, nom, nu_proj, nm_proj, horas-trab)

Mat, nu_proj horas-trab

Mat nome

Mat Nm_proj Horas-trabNu_projNom

Normalização



172

ç

A chave primária deve:

± Identificar uma única tupla

± Conter o mínimo de atributos

Após a escolha da chave, verificar : ± A chave pode ser simplificada pela retirada de

algum atributo ?

± Algum componente da chave tem valor indefinido ?

Normalização



173

ç

Quais as chaves das relações abaixo ?

Pedido (nu_ped, nu_cliente, dt_ped, vl_ped)His-aluno (mat, nom, disciplina, nota,dt_entrada_univ)

Venda (nu_ped, nu_cli, nu_nu_vendedor,cd_item, qt_venda, preço_unit, vl_item)

Normalização



174

ç

Objetivo = FNBC = Forma Normal deBoyce-Codd = todas as DF vêm do fato³chavep tudo´

Formalmente, R está na FNBC se em todaDF não trivial para R, na forma X p A, X éuma superchave

Normalização



175

ç

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

Exemplo de problemas



176

p p

Bebedores(Nome, Endereço, CervBoas,Fabr,CervFavorita)

DFs ± Nomep Endereço

± Nomep CervFavorita

± CervBoasp Fabr ± ??? São redundantes, pois pode-se descobrí-los a partir das DF

± Quais anomalias de atualização é de retirada ?

Nome Endereço CervBoas Fabr CervFavorita

Jane Papicu Bud A.B. Cerpa ExtraJane ??? Cerpa Extra Cerpa ???

Flavio Centro Bud ??? Bud

Exemplo de Problemas



177

Bebedores(Nome, Endereço, CervBoas, Fabr,CervFavorita)

DFs

± Nomep Endereço ± Nomep CervFavorita ± CervBoasp Fabr

Cada uma das DF dadas viola a FNBC

± a chave = {Nome, CervBoas} ± o lado esquerdo das DF dadas é subconjunto próprio dachave

± Nem nome, nem CervBoas é superchave

Outro exemplo



178

Cervejas(Nome, Fabr, EndFabr)

DFs ± Nomep Fabr

± Fabr p EndFabr, logo Nomep EndFabr

a única chave é nome ± Fabr p EndFabr viola a FNBC, pois o lado

esquerdo não é nenhuma superchave

Exemplo



179

Bebedores(Nome, Endereço, CervBoas, Fabr, CervFavorita) DFs

± Nomep Endereço ± Nomep CervFavorita ± CervBoas p Fabr

relações decompostas ± Bebedores1(Nome, Endereço,CervFavorita) ± Bebedores2(Nome,CervBoas,Fabr)

DF projetadas (após muito trabalho) ± Para Bebedores1(Nome,Endereço,CervFavorita)

� Nomep Endereço� Nomep CervFavorita

± Para Bebedores2(Nome,CervBoas,Fabr))� CervBoas p Fabr

Exemplo



180

e p o

Bebedores1(Nome, Endereço, CervBoas, Fabr, CervFavorita)

DFs

± Nomep Endereço

± Nomep CervFavoritaBebedores2(Nome,CervBoas,Fabr)

± CervBoasp Fabr

Alguma violação da FNBC ?

± Em Bebedores 2 a chave é {Nome,CervBoas}eCervBoasp Fabr viola a FNBC

Decompor Bebedores2

Exemplo



181

Bebedores2(Nome, CervBoas, Fabr)DFs

± CervBoasp Fabr

Decompor Bebedores 2

± Expandir lado direito: nada

± Decompor � Bebedores3(CervBoas,Fabr)

� Bebedores4(Nome,CervBoas)

Relações resultantes estão todas na FNBC ± Bebedores1(Nome, Endereço,CervFavorita)

� Bebedores3(CervBoas,Fabr)

� Bebedores4(Nome,CervBoas)

1 FN



182

Força a organização dos dados como estruturas³planas´ sem grupos repetidos ± Todo componente de toda tupla é um valor atômico

2 FN Força a decomposição dos esquemas na !FN de

maneira que a chave primária inteira é necessária

para identificar todos os atributos da relação ± não pode haver uma DF não-trivial cujo lado esquerdo

seja um subconjunto próprio de uma chave

3r Forma Normal



183

Uma situação que causa problemas ± Se fizer uma decomposição, não poderá verificar as DF

nas relações decompostas ± Se não fizer a decomposição, violará a FNBC

De forma abstrata: AB p C e Cp B ± Ex: Peça Cidadep Teatro e Teatrop Cidade ± Ex: Rua Cidadep CEP e CEP p Cidade

Chaves: {A,B} e {A,C} mas C p B tem o ladoesquerdo uma não-superchave (violando FNBC) ± sugere a decomposição em AC e BC ± Mas não poderá checar a DF AB p C nessas relações

3r FN



184

Exemplo: R(Rua, Cidade,CEP) ± Rua Cidade p CEP e CEP p Cidade

Rua CEPWashinton Soares 60810Washinton Soares 60811

Cidade CEPFortaleza 60810Fortaleza 60811

Cidade Rua CEPFortaleza Washinton Soares 60810

Fortaleza Washinton Soares 60811

Junção

3r FN - Definição



185

Uma relação R está na 3FN se e somente se para toda DF não trivial da forma X p A ± X é uma superchave

ou ± A é membro de alguma chave

FNBC



186

Elimina redundâncias e anomalias de atualização e retirada ± O lado esquerdo de toda DF é uma superchave para a relação

4 FN



187

Reconhece e separa atributos multivaloradosconstituindo uma chave primária composta

Provavelmente, você irá realizar isso

intuitivamente reconhecendo gruposindependentes que se repetem. Contudo, as formasnormais anteriores não estabelecem explicitamenteesta regra. ± O lado esquerdo de toda dependência multivalorada é

uma superchave para a relação ± Exemplo do problema: Nome p Rua Cidade

4 FN



188

Como decompor uma relação para relaçõesna 4FN ? ± Seja a DM X p Y, onde X não é uma

superchave para a relação R ± As relações decompostas são:

� R1(X,Y)

� R 2

(X, outros atributos)

Outras Formas Normais



189

São complexas e não-intuitivas

Não são úteis na prática para o projetorelacional



190

Scripts Oracle

Tabela



191

± Unidade básica de armazenamento da base dedados, formada por colunas e linhas (tuplas)

Criando Tabelas ± Criando a tabela dept o.



192

SQL> CREATE TABLE depto2 (depto_num NUMBER(2),3 depto_nom VARCHAR2(14),4 depto_loc VARCHAR2(13));

Table created.Table created.

p

± 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



193

± 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.Table dropped.

Truncando uma Tabela



194

± Remove todas as linhas da tabela liberando oespaço ocupado

Essa operação não pode ser desfeita

SQL> TRUNCATE TABLE departamento;Table truncated.Table truncated.

Alterando uma Tabela



195

± Altera a estrutura de uma tabela

Essa operação não pode ser desfeita

SQL> Alter TABLE departamentoalter column nome varchar(20);Table altered.Table altered.

SQL> Alter TABLE departamentodrop column nome;Table altered.Table altered.

SQL> Alter TABLE departamento

add nome varchar(20);Table altered.Table altered.

O Comando INSERT



± Adicione linhas a uma tabela utilizando o comandoINSERT.

± 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 cadacoluna 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 aspassimples.

SQL> INSERT INTO depto (depto_num, depto_nome, depto_loc)2 VALUES (50, 'DESENVOLVIMENO', µRECIFE');

1 row created.1 row created.

Inserindo Linhas com Nulls



SQL> INSERT INTO depto (depto_num, depto_nome )2 VALUES (60, µREC HUMANOS');

1 row created.1 row created.

SQL> INSERT INTO depto

2 VALUES (70, µJURIDICO', NULL);1 row created.1 row created.

± Implicitamente: omita o nome da coluna dalista de colunas.

± Explicitamente: especifique o valor

NULL.

O Comando UPDATE



± Modificando linhas existentes com o comandoUPDATE.

± Modifique mais de uma linha por vez especificandouma condição na cláusula WHERE.

± Não esqueça o COMMIT

UPDATE table

SET column = value [, column = value]

[W HERE condition];

Atualizando linhas em umatabela



abe a ± 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 emp2 SET depto_num = 20

3 W HERE enum = 7782;1 row updated.1 row updated.

SQL> UPDATE emp2 SET depto_num = 20;

14 rows updated.14 rows updated.

Eliminando Linhas de umaTabela



± Linhas específicas podem ser eliminadasutilizando a cláusula WHERE.

± Todas as linhas da tabela são eliminadas se a

cláusula WHERE for omitida.

SQL> DELETE FROM depto

2 W HERE depto_nome = 'DESENVOLVIMENTO';1 row deleted.1 row deleted.

SQL> DELETE FROM depto;4 rows deleted.4 rows deleted.

O Comando SELECT



202

SELECT [DISTINCT] {*, column [alias],...}FROM table;

± SELECT identifica as colunas ± FROM identifica as tabelas

Selecionando linhas



203

SQL> SELECT *2 FROM depto;

SQL> SELECT depto_num, depto_loc

2 FROM depto;

SQL> SELECT enome, sal, sal+3002 FROM emp;

SQL> SELECT enome, sal, 12*sal+1002 FROM emp;

Definindo Alias para ColunasSQL> SELECT enome AS nome, sal AS salario



204

Q ,

2 FROM emp;

NOME SALARIO

------------- ---------

...

SQL> SELECT enome "Nome",2 sal*12 ³Salario Anual"3 FROM emp;

Nome Salario Anual

------------- -------------

...

Eliminando Linhas Duplicadas



205

SQL> SELECT DISTINCT depto_num 2 FROM emp;

DEPTO_NUM ---------

102030

± DISTINCT

Utilizando a Cláusula WHERE



SQL> SELECT enome, cargo, depto_num 2 FROM emp3 W HERE cargo='CAIXA';

ENOME CARGO DEPTO_NUM ---------- --------- ---------RONALDO CAIXA 30 MANUEL CAIXA 20PAULO CAIXA 20LUCIANO CAIXA 10

Operadores de Comparação



Operador

=

>

>=<

<=

<>

Significado

Igual a

Maior que

Maior ou igual aMenor 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 valoresSatisfaz um padrão de caracteres

É um valor nulo (null )

Operador BETWEEN



ENOME SAL

---------- --------- MARIA 1250SERGIO 1500 MATHEUS 1250PAULO 1100LUCIANO 1300

SQL> SELECT enome, sal2 FROM emp3 W HERE sal BET W EEN 1000 AND 1500;

limite

inferior

limite

superior

Operador IN



SQL> SELECT enum, enome, sal, ger2 FROM emp3 W HERE ger IN (7902, 7566, 7788);

ENUM ENOME SAL GER --------- ---------- --------- ---------

7902 JOSE 3000 75667369 MANUEL 800 79027788 FABIO 3000 75667876 PAULO 1100 7788

Operador LIKE



SQL> SELECT enome2 FROM emp3 W HERE enome LIKE µM%';

± Utilize o operador LIKE para realizar pesquisas por padrões (wildcards).

� % substitui zero ou mais caracteres

� _ substitui um único caracter

Operador IS NULL



SQL> SELECT enome, ger

2 FROM emp3 W HERE ger IS NULL;

ENOME GER ---------- ---------

CARLOS

± 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 umdos componentes for TRUE

Retorna TRUE se a condição for

FALSE (vise-versa)

Operador NOT



SQL> SELECT enome, cargo2 FROM emp3 W HERE 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, dtinicio2 FROM emp3 ORDER BY dtinicio DESC;

ENOME CARGO DEPTO_NUM DTINICIO---------- --------- --------- ---------PAULO CAIXA 20 12-JAN-83FABIO ANALISTA 20 09-DEC-82LUCIANO CAIXA 10 23-JAN-82RONALDO CAIXA 30 03-DEC-81JOSE ANALISTA 20 03-DEC-81CARLOS PRESIDENTE 10 17-NOV-81 MARIA VENDEDOR 30 28-SEP-81...14 rows selected.

Joins



± Utilize uma junção para consultar dados de mais deuma 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 mesmosnomes.

SELECT table1.column, table2.column

FROM table1, table2

W HERE 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 W HERE 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 W HERE 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.



EMPEMP

³o maior salário³o maior salárioda tabela EMP´da tabela EMP´

DEPTO_NUM SAL--------- ---------

10 245010 500010 130020 80020 110020 300020 300020 297530 1600

30 285030 125030 95030 150030 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

[W HERE condition]

[ORDER BY column];

Funções AVG e SUM



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 emp4 W HERE cargo LIKE µVEND%';

± Utilize o AVG e SUM apenas para dadosnuméricos

Funções MIN e MAX



SQL> SELECT MIN(dtinicio), MAX(dtinicio)2 FROM emp;

MIN(DTINI MAX(DTINI--------- ---------17-DEZ-80 12-JAN-83

± Utilize MIN e MAX para qualquer tipo de dado

Função COUNT



COUNT(*)---------

6

SQL> SELECT COUNT(*)2 FROM emp3 W HERE depto_num = 30;

± COUNT(*) retorna o número de linhas natabela

Função COUNT



COUNT(COMIS)------------

4

SQL> SELECT COUNT(comis)2 FROM emp3 W HERE depto_num = 30;

± COUNT(coluna) retorna o número de linhasnão nulas da tabela

Criando Grupos de DadosEMPEMP



EMPEMP

³média salarial³média salarialpor por

departamento´departamento´

2916.66672916.6667

21752175

1566.66671566.6667

DEPTO_NUM SAL--------- ---------

10 245010 500010 130020 800

20 110020 300020 300020 297530 160030 285030 1250

30 95030 150030 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 pequenosgrupos usando a cláusula GROUP BY.

SELECT column, group_function(column)

FROM table

[W HERE condition][GROUP BY group_by_expression]

[ORDER BY column];

A Cláusula GROUP BY



SQL> SELECT AVG(sal)2 FROM emp3 GROUP BY depto_num;

± Colunas utilizadas em funções de grupo não precisam estar listadas no GROUP BY.

AVG(SAL)---------2916.6667

21751566.6667

Utilizando GROUP BY emMúltiplas Colunas



SQL> SELECT depto_num, cargo, sum(sal)2 FROM emp3 GROUP BY depto_num, cargo;

DEPTO_NUM CARGO SUM(SAL)--------- --------- ---------

10 CAIXA 130010 GERENTE 245010 PRESIDENTE 5000

20 ANALISTA 600020 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_listFROM table

W HERE expr operator

(SELECT select_listFROM table);

Utilizando uma Subquery



2975SQL> SELECT enome2 FROM emp3 W HERE sal > 4 (SELECT sal5 FROM emp6 W HERE enum=7566);

ENOME----------CARLOSJOSEFABIO

Regras para Subqueries



± Utilize subqueries entre parênteses.

± As subqueries vêem sempre à direita dooperador de comparação.

± Não utiliza a cláusula ORDER BY emsubqueries.

± Uma subquery retorna uam tabela sobre a qual pode-se realizar qualquer uma das operações

vista anteriormente.

Stored Procedures



232

Procedimantos que permanecemarmazenados no banco, de formacompilada.

Servem para executar alguma computaçãoquando invocados

Sintaxe



233

CREATE OR REPLACE PROCEDURE NOME( NOME TIPO[,NOME TIPO] )IS BEGIN

[DECLARE] <CORPO> COMMIT;

EXCEPTION W HEN OTHERS THEN

<CORPO> END NOME;/

Sintaxe (exemplo)



234

CREATE OR REPLACE PROCEDURE AJUSTE( VALOR REAL, CAD INTEGER )IS BEGIN

UPDATE acf_EMPREGADO SET salario=salario +salario*VALOR W HERE cadastro=CAD;

COMMIT;

EXCEPTION W HEN OTHERS THENINSERT INTO acf_ERROS values(SYSDATE,'Erro na

execucao de ajuste');END AJUSTE;/

Execução



235

EXECUTE AJUSTE(0.1, 21);

O procedimento é executado. Caso algum erro ocorra,então a tabela de erros será atualizada.

Exemplo



236

CREATE OR REPLACE PROCEDURE Lista_Func(codDepto llma_funcionarios.depto_func%TYPE)

IS BEGIN

DECLARE

--cursor para guardar os funcionariosCURSOR c_diario_func is

SELECT llma_funcionarios.cod_func,

llma_funcionarios.nome_func,

FROM llma_funcionarios

WHERE depto_func = codDepto;

--declaracao das variaveis do cursorv_cod_func llma_funcionarios.cod_func%TYPE;

v_nome_func llma_funcionarios.nome_func%TYPE;

Exemplo (cont)--inicio da procedure

BEGIN

open c_diario_func;



dbms_output.put_line('** Lista do Diario dos Funcionarios **');

loop fetch c_diario_func into v_cod_func, v_nome_func;

dbms_output.put_line('Codigo do Funcionario : ' || v_cod_func);

dbms_output.put_line('Nome Funcionario : ' || v_nome_func);

dbms_output.put_line('');dbms_output.put_line('---------------------------------');

dbms_output.put_line('');

exit when c_diario_func%NOTFOUND;

end loop;close c_diario_func;

END;

--excecoes

exceptionwhen no_data_found then

dbms_output.put_line('Nenhuma registro foi encontrado');when others then

dbms_output.put_line('Erro desconhecido: ' || to_char(sqlcode));

END Lista_Func;

/

Procedures



238

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 ocomando show_errors do SQL Plus.

Triggers



239

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



240

CREATE OR REPLACE TRIGGER NOMECONDICAO DE ATIVACAOBEGIN

<CORPO> END;/

A CONDICAO DE ATIVACAO pode ser montada a partir deexpressões lógicas:

BEFORE DELETE ON NOME_TABELA

ou

AFTER DELETE OR INSERT OR UPDATE ON NOME_TABELA

Sintaxe (exemplo)



241

CREATE OR REPLACE TRIGGER LOG_FUNCIONARIOBEFORE DELETE OR INSERT OR UPDATE ON acf_EMPREGADOBEGININSERT INTO acf_LOG_funcionario

VALUES(SYSDATE,'Tabela modificada');END;/

Sintaxe (exemplo)CREATE OR REPLACE TRIGGER t_verifica_data_contratosBEFORE insert on llma_diario for each row



DECLARE

datainicial llma_contratos.dataini%TYPE;

BEGIN

SELECT to_char(dataini,'dd/mm/yyyy') into datainicial

FROM llma_contratos

WHERE cod_con = :new.cod_con;

--faz a condicao se a data ta no periodo

IF ((:new.datahoraini < datainicial)

or (:new.datahoraini > datafinal)) then

raise_application_error(-20500, 'Data Inicio tem que esta no

periodo de: '|| to_char(datainicial,'dd/mm/yyyy') || ' a ' ||

to_char(datafinal,'dd/mm/yyyy'));END IF;

END;

/

Sintaxe (exemplo)



CREATE or REPLACE TRIGGER TG_DataColetaInvalidaBEFORE INSERT ON xcoleta FOR EACH ROW

BEGINIF :NEW.data < sysdate THEN

RAISE_APPLICATION_ERROR ('-20000', 'Data já passou');

END IF;END;

/

Sintaxe (exemplo)

CREATE or REPLACE TRIGGER TG_AlteraItemColeta

AFTER insert or update or delete on xitemcoleta FOR EACH ROW

BEGIN

IF DELETING THENUPDATE XCOLETA SET PESO = (PESO - :OLD.PESO),

QUANTIDADE (QUANTIDADE :OLD QUANTIDADE)



QUANTIDADE = (QUANTIDADE - :OLD.QUANTIDADE),VOLUME = (VOLUME - :OLD.VOLUME)

WHERE COLETA = :OLD.COLETA;

ELSIF INSERTING THEN

UPDATE XCOLETA

SET PESO = (PESO + :NEW.PESO),

QUANTIDADE = (QUANTIDADE + :NEW.QUANTIDADE),VOLUME = (VOLUME + :NEW.VOLUME)

WHERE COLETA = :NEW.COLETA;

ELSIF UPDATING THEN

UPDATE XCOLETA

SET PESO = (PESO + :NEW.PESO - :OLD.PESO),

QUANTIDADE=(QUANTIDADE+ :NEW.QUANTIDADE- :OLD.QUANTIDADE),VOLUME = (VOLUME + :NEW.VOLUME - :OLD.VOLUME)

WHERE COLETA = :OLD.COLETA;END IF;END;

/

Triggers

i l i l



Triggers são salvos em um arquivo .sql ecompiladas no Oracle com o comando@caminho_completo do SQL Plus

Caso ocorra algum erro de compilação o trigger

não funciona corretamente Erros de compilação podem ser vistos com o

comando show_errors do SQL Plus.

Documents

Aula01-Banco de Dados