1 1. BANCOS DE DADOS......................................................

1.
BANCOS DE DADOS ................................................................................4
1.1. Banco de Dados (BD)...................................................................................4
1.2. Sistema de Gerência de Banco de Dados (SGBD)....................................4
1.2.1.Processamento de Dados sem Banco de Dados....................................5
1.2.2.Processamento de dados com uso de SGBD.........................................5
1.2.3.Principais Componentes de um SGBD..................................................6
1.2.4.Funções de um SGBD.............................................................................6
1.3. Fluxo Operacional de Dados e Controle.....................................................8
1.4. Abstração de Dados....................................................................................10
1.5. Modelos de Bancos de Dados....................................................................11
1.6. Independência de Dados.............................................................................11
1.7. Especificação CODASYL para arquitetura BD.......................................12
1.8. Especificação ANSI/X3/SPARC para arquitetura BD............................12
1.9. Funções relacionadas ao SGBD ................................................................14
1.9.1.Administrador de Dados .......................................................................14
1.9.2.Administrador de Banco de Dados......................................................14
1.10.Arquiteturas para uso do SGBD ................................................................15
1.10.1.Mono-usuário.......................................................................................15
1.10.2.Multi-Usuário com Processamento Central ......................................15
1.10.3.Arquitetura Cliente/Servidor ..............................................................15
1.11.Fases do Projeto de BD ..............................................................................16
1.11.1.Construir o Modelo Conceitual.........................................................16
1.11.2.Construir o Modelo Lógico ................................................................16
1.11.3.Construir o Modelo Físico..................................................................16
1.11.4.Avaliar o Modelo Físico .....................................................................16
1.11.5.Implementar o BD...............................................................................16
2.
MODELAGEM DE DADOS....................................................................17
2.1. Conceitos .....................................................................................................17
2.2. Requisitos para Modelagem de Dados......................................................17
2.3. Modelos Conceituais ..................................................................................17
2.4. Modelos Lógicos.........................................................................................18
2.4.1.Modelo Relacional.................................................................................18
2.4.2.Modelo de Rede .....................................................................................19
2.4.3.Modelo Hierárquico ..............................................................................20
2.5. Modelo de Dados Físico.............................................................................23
1
3.
MODELO ENTIDADE-RELACIONAMENTO (M.E.R.)....................23
3.1. Introdução ....................................................................................................23
3.2. Entidade .......................................................................................................23
3.3. Relacionamento...........................................................................................24
3.3.1.Auto-relacionamento.............................................................................25
3.3.2.Cardinalidade de Relacionamentos......................................................25
3.3.3.Cardinalidade Máxima..........................................................................26
3.3.4.Classificação de Relacionamentos Binários .......................................27
3.3.5.Relacionamento ternário .......................................................................30
3.3.6.Cardinalidade mínima ...........................................................................30
3.4. Notações Alternativas.................................................................................31
3.5. Atributo ........................................................................................................32
3.5.1.Domínio..................................................................................................32
3.5.2.Tipos de Atributos.................................................................................32
3.5.3.Atributo de Relacionamento.................................................................33
3.5.4.Identificador de Entidades ....................................................................33
3.5.5.Relacionamento Identificador (Entidade Fraca).................................34
3.5.6.Identificador de Relacionamentos........................................................34
3.6. Generalização/Especialização....................................................................35
3.7. Entidade Associativa (Agregação) ............................................................37
3.8. Relacionamento Mutuamente Exclusivo ..................................................38
4.
O MODELO RELACIONAL....................................................................39
4.1. Características das Tabelas - Modelo Relacional ...................................40
4.2. Conceitos Básicos .......................................................................................41
4.2.1.Chave primária : (primary key) ............................................................41
4.2.2.Chave estrangeira : (foreign key).........................................................41
4.2.3.Chave candidata ou alternativa ............................................................41
4.3. Normalização...............................................................................................42
4.3.1.Ilustração de um sistema a ser normalizado .......................................43
4.3.2.Análise de Dependência Funcional......................................................46
4.3.3.Formas Normais.....................................................................................50
4.3.4.Roteiro Prático para Normalização......................................................50
4.3.5.Exemplo de Normalização....................................................................52
4.4. Transposição D.E.R para D.T.R (Diagrama de Tabelas Relacionais)...56
4.4.1.Simbologia adotada no modelo relacional ..........................................56
4.4.2.Análise da Entidade no D.E.R..............................................................57
4.4.3.Análise de Relacionamento ..................................................................57
2
4.5. Restrições de integridade no modelo Relacional .....................................65
4.5.1.Integridade Lógica.................................................................................65
4.5.2.Integridade Física ..................................................................................65
4.6.Linguagens Relacionais................................................................................80
4.7.SQL (Structured Query Language) .............................................................85
4.7.1.DDL (Data Definition Language .........................................................86
4.7.2.DML (Data Manipulation Language).................................................93
4.7.3.DCL (Data Control Language).......................................................... 107
4.7.4.Transaction Control............................................................................ 110
4.7.5.Restrições de Integridade usando Stored Procedures e Triggers... 113
5.
EXERCÍCIOS:......................................................................................... 127
5.1.Exercicios de Modelagem de Dados......................................................... 127
5.2.Exercícios de Normalização:..................................................................... 130
5.3.Exercícios de SQL...................................................................................... 133
5.4.Execícios de Algebra Relacional .............................................................. 138
6.
BIBLIOGRAFIA............................... .................................................142
3
1. BANCOS DE DADOS
1.1. B ANCO DE D ADOS (BD)
Um Banco de Dados (BD) pode ser definido como uma coleção de
dados interrelacionados, armazenados de forma centralizada ou distribuída,
com redundância controlada, para servir a uma ou mais aplicações.
1.2. SISTEMA DE GERÊNCIA DE B ANCO DE D ADOS (SGBD)
Conjunto de software para gerenciar (definir, criar, modificar,
usar) um BD e garantir a integridade e segurança dos dados. O SGBD é a
interface entre os programas de aplicação e o BD. Em inglês é denominado
DataBase Management System (DBMS).
4
1.2.1. P ROCESSAMENTO DE D ADOS SEM B ANCO DE D ADOS
Dados de diferentes aplicações não estão integrados, pois são projetados
para atender a uma aplicação específica.
Problemas da falta de integração de dados:
O mesmo objeto da realidade é múltiplas vezes representado na base de
dados. Exemplo: dados de um produto em uma indústria.
Redundância não controlada de dados: Não há gerência automática da
redundância, o que leva a inconsistência dos dados devido a
redigitação de informações
Dificuldade de extração de informações: os dados são projetados para
atender aplicações especificas gerando dificuldades para o
cruzamento de informações
Dados pouco confiáveis e de baixa disponibilidade
1.2.2. P ROCESSAMENTO DE DADOS COM USO DE SGBD
Os dados usados por uma comunidade de usuários são integrados no
Banco de Dados. Cada informação é armazenada uma única vez, sendo que as
eventuais redundâncias são controladas pelo sistema em computador, ficando
transparentes para os usuários.
5
1.2.3. P RINCIPAIS C OMPONENTES DE UM SGBD
Dicionário de dados (Data Dictionary):
Descreve os dados e suas relações em forma conceitual e independente de
seu envolvimento nas diversas aplicações. Fornece referências cruzadas entre
os dados e as aplicações.
Linguagem de definição de dados (DDL - Data Definition Language):
Descreve os dados que estão armazenados no BD. As descrições dos dados
são guardadas em um “meta banco de dados”.
Linguagem de acesso (DML - Data Manipulation Language):
Usada para escrever as instruções que trabalham sobre a base de dados,
permitindo o acesso e atualização dos dados pelos programas de aplicação.
Linguagem de consulta (QUERY):
Permite que o usuário final, com poucos conhecimentos técnicos, possa
obter de forma simples, informações do BD.
Utilitários administrativos:
Programas auxiliares para carregar, reorganizar, adicionar, modificar a
descrição do BD, obter cópias de reserva e recuperar a integridade física em
caso de acidentes.
1.2.4. F UNÇÕES DE UM SGBD
Um princípio básico em BD determina que cada item de dado deveria
ser capturado apenas uma vez e então armazenado, de modo que possa tornar
disponível para atender a qualquer necessidade de acesso qualquer momento.
6
Alguns pontos importantes são:
Independência dos dados: O SGBD deve oferecer isolamento das
aplicações em relação aos dados. Esta característica permite
modificar o modelo de dados do BD sem necessidade de reescrever
ou recompilar todos os programas que estão prontos. As definições
dos dados e os relacionamentos entre os dados são separados dos
códigos os programas.
Facilidade uso/desempenho: Embora o SGBD trabalhe com estruturas
de dados complexas, os arquivos devem ser projetados para atender a
diferentes necessidades, permitindo desenvolver aplicações melhores,
mais seguras e mais rapidamente. Deve possui comandos poderosos
em sua linguagem de acesso.
Integridade dos dados: O SGBD deve garantir a integridade dos dados,
através da implementação de restrições adequadas. Isto significa que
os dados devem ser precisos e válidos.
Redundância dos dados: O SGBD deve manter a redundância de dados
sob controle, ou seja, ainda que existam diversas representações do
mesmo dado, do ponto de vista do usuário é como se existisse uma
única representação.
Segurança e privacidade dos dados: O SGBD deve assegurar que estes
só poderão ser acessados ou modificados por usuários autorizados.
Rápida recuperação após falha: Os dados são de importância vital e
não podem ser perdidos. Assim, o SGBD deve implementar sistemas
de tolerância a falhas, tais como estrutura automática de recover e
uso do conceito de transação.
Uso compartilhado: O BD pode ser acessado concorrentemente por
múltiplos usuários.
Controle do espaço de armazenamento: O SGBD deve manter
controle das áreas de disco ocupadas, evitando a ocorrência de falhas
por falta de espaço de armazenamento.
7
1.3. F LUXO OPERACIONAL DE D ADOS E CONTROLE
1
PROGRAMA DE
APLICACAO
AREA LOCAL
DO PROGRAMA
10
S.G.B.D
5
9
AREA DE
ENTRADA/SAIDA
2
6
8
4
SISTEMA
OPERACIONAL
ESQUEMAS,
DICIONARIO
DE DADOS,
DIRETORIOS
BASE
DADOS
3
7
FLUXO DE DADOS
FLUXO DE CONTROLE
8
1 - O programa do usuário comunica-se com o SGBD utilizando DML
pedindo a leitura de um registro lógico.
2 - O SGBD emite um comando para o S.O. para leitura dos
esquemas (META DADOS).
3 - O S.O. acessa os esquemas.
4 - Os Meta-dados são transferidos para área de E/S do SGBD.
5 - O SGBD consulta os Meta-dados para saber como traduzir o
comando do usuário.
6 - O SGBD emite os comandos para que o S.O. leia os
registros físicos necessários.
7 - O S.O. acessa a base de dados.
8 - Os registros físicos são transferidos para área de E/S do
SGBD.
9 - O SGBD seleciona os dados necessários para formar o(s)
registro(s) lógico(s) pedidos pelo usuário, se necessário faz a
transformação, e coloca o registro lógico na área do
usuário/aplicação.
10 - O SGBD envia ao programa de aplicação um código indicando fim
de comando.
9
1.4. ABSTRAÇÃO DE D ADOS
Um propósito central de um SGBD é proporcionar aos usuários uma
visão abstrata dos dados, isto é, o sistema esconde certos detalhes de como os
dados são armazenados ou mantidos. No entanto, os dados precisam ser
recuperados eficientemente.
A preocupação com a eficiência leva a concepção de estruturas de
dados complexas para representação dos dados no BD. Porém, uma vez que
SGBD são freqüentemente usados por pessoas sem treinamento na área de
computação, esta complexidade precisa ser escondida dos usuários. Isto é
conseguido definindo-se diversos níveis de abstração pelos quais o BD pode
ser visto:
NÍVEL FÍSICO: É o nível mais baixo de abstração, no qual se descreve
como os dados são armazenados. Estruturas complexas, de baixo
nível, são descritas em detalhe.
NÍVEL CONCEITUAL: É o nível que descreve quais os dados são
realmente armazenados no BD e quais os relacionamentos existentes
entre eles. Este nível descreve o BD como um pequeno número de
estruturas relativamente simples. Muito embora a implementação de
estruturas simples no nível conceitual possa envolver estruturas
complexas no nível físico, o usuário do nível conceitual não
precisa saber disto.
NÍVEL VISÃO: Este é o nível mais alto de abstração, no qual se expõe
apenas parte do BD. Na maioria das vezes os usuários não estão
preocupados com todas as informações do BD e sim com apenas
parte delas (Visões dos Usuários)
10
1.5. MODELOS DE B ANCOS DE D ADOS
Um modelo de (banco de) dados é uma descrição dos tipos de
informações que estão armazenadas em um banco de dados, ou seja, é a
descrição formal da estrutura de BD.
Estes modelos podem ser escritos em linguagens textuais ou linguagens
gráficas. Cada apresentação do modelo é denominado “esquema de banco de
dados”.
Se tomarmos como exemplo uma indústria, o modelo de dados deve
mostrar que são armazenadas informações sobre produtos, tais como código,
descrição e preço. Porém o modelo de dados não vai informar quais produtos
estão armazenados no Banco de Dados.
No projeto de um banco de dados, geralmente são considerados 3
modelos: conceitual, lógico e físico.
Modelo conceitual: É uma descrição mais abstrata da base de dados.
Não contém detalhes de implementação e é independente do tipo de
SGBD usado. É o ponto de partida para o projeto da base de dados.
Modelo Lógico: É a descrição da base de dados conforme é vista pelos
usuário do SGBD (programadores e aplicações). É dependente do tipo
de SGBD escolhido, mas não contém detalhes da implementação
(uma vez que o SGBD oferece abstração e independência de dados).
Modelo físico (interno): Descrição de como a base de dados é
armazenada internamente. Geralmente só é alterada para ajuste de
desempenho. A tendência dos produtos modernos é ocultar cada vez
mais os detalhes físicos de implementação.
1.6. INDEPENDÊNCIA DE D ADOS
Independência de dados a nível físico: a capacidade de se modificar o modelo
físico, sem precisar reescrever os programas de aplicação.
Independência dados a nível lógico: a capacidade de se modificar o esquema
lógico, sem a necessidade de reescrever os programas de aplicação.
Modificações no nível lógico são necessárias sempre que a estrutura lógica
do BD for alterada. Em alguns casos a recompilação pode ser requerida.
11
1.7. ESPECIFICAÇÃO CODASYL PARA ARQUITETURA BD
Primeira especificação padrão de BD conhecida, no papel 1960,
implementada 1971 - Modelo DBTG-CODASYL(Data Base Task Group –
Conference on Data Systems and Languages)
Introduziu o conceito de SCHEMA ( descrição completa do BD) e SUBSCHEMA (descrição do dados que são conhecidos por um ou mais programas
de aplicação)
1.8. ESPECIFICAÇÃO ANSI/X3/SPARC PARA ARQUITETURA BD
Proposta de modelo de referência para arquiteturas de BD.
Teve início em 1972 (Relatório provisório ) e terminou em 1983 (
Relatório final ).
Abordagem proposta por um grupo de trabalho estabelecido pelo
SPARC(Standard
Planning
and
Requeriments
Committe)
do
ANSI/X3(American National Standards Committe on Computers and
Information Processing).
Os objetivos do grupo de estudo, foram os de determinar as áreas, se
existentes, de tecnologia de BD onde fosse apropriada uma atividade de
padronização, e de produzir um conjunto de recomendações para ações em
cada uma dessas áreas.
Trabalhando sobre estes objetivos, o grupo verificou que os
INTERFACES seriam os únicos aspectos do SBD possivelmente passíveis de
serem padronizados.
12
Grupo sugere três esquemas:
APLICACAO 1
VISAO
USUARIO
VISAO GLOBAL
(de toda empresa)
ESQUEMA EXTERNO 1
APLICACAO 2
ESQUEMA EXTERNO 2
ESQUEMA
CONCEITUAL
APLICACAO 3
ESQUEMA EXTERNO 3
S.G.B.D.
ESQUEMA INTERNO
VISAO IMPLEMENTACAO
(fisica)
BD
13
1.9. F UNÇÕES RELACIONADAS AO SGBD
1.9.1. ADMINISTRADOR DE D ADOS
Gerenciar o dado como um recurso da empresa.
Planejar, desenvolver e divulgar as bases de dados da empresa.
Permitir a descentralização dos processos, mas manter centralizado os dados.
Permitir fácil e rápido acesso as informações a partir dos dados armazenados
O grande objetivo de administrador de dados é permitir que vários usuários
compartilhem os mesmos dados. Deste modo, os dados não pertencem a
nenhum sistema ou usuário de forma específica, e sim, à organização como
um todo. Assim, o administrador de dados se preocupa basicamente com a
organização dos dados e não com o seu armazenamento.
1.9.2. ADMINISTRADOR DE B ANCO DE D ADOS
O DBA (DataBase Administrator) é pessoa ou grupo de pessoas
responsável pelo controle do SGBD. São tarefas do DBA:
Responsabilidade pelos modelos lógico e físico (definindo a estrutura de
armazenamento)
Coordenar o acesso ao SGBD (usuários e senhas)
Definir a estratégia de backup
Melhorar o desempenho do SGBD
Manter o dicionário de dados
14
1.10. ARQUITETURAS PARA USO DO SGBD
1.10.1. MONO-USUÁRIO
BD está no mesmo computador que as aplicações
Não há múltiplos usuários
Recuperação geralmente através de backup
Típico de computadores pessoais
1.10.2. MULTI -U SUÁRIO COM P ROCESSAMENTO CENTRAL
BD está no mesmo computador que as aplicações
Múltiplos usuários acessando através de terminais
Típico de ambientes com mainframe
1.10.3. ARQUITETURA CLIENTE/S ERVIDOR
Multi-usuário
Servidor dedicado ao Banco de Dados, executando o SGBD
As estações clientes executam apenas as aplicações
Tráfego na rede é menor
Arquitetura atualmente em uso
15
1.11. F ASES DO P ROJETO DE BD
1.11.1. CONSTRUIR O MODELO CONCEITUAL
Modelo de alto nível, independente da implementação
Etapa de levantamento de dados
Uso de uma técnica de modelagem de dados
Abstração do ambiente de hardware/software
1.11.2. CONSTRUIR O MODELO L ÓGICO
Modelo implementável, dependente do tipo de SGBD a ser usado
Considera as necessidades de processamento
Considera as características e restrições do SGBD
Etapa de normalização dos dados
1.11.3. CONSTRUIR O MODELO F ÍSICO
Modelo implementável, com métodos de acesso e estrutura física
Considera necessidades de desempenho
Considera as características e restrições do SGBD
Dependente das características de hardware/software
1.11.4. A VALIAR O MODELO F ÍSICO
Avaliar o desempenho das aplicações
Avaliar os caminhos de acesso aos dados e estruturas utilizadas
1.11.5. IMPLEMENTAR O BD
Etapa de carga (load) dos dados
Gerar as interfaces com outras aplicações
16
2.
MODELAGEM DE DADOS
2.1. CONCEITOS
Abstração: processo mental através do qual selecionamos determinadas
propriedades ou características dos objetos e excluímos outras, consideradas
menos relevantes para o problema que esta sendo analisado.
Modelo: é uma abstração, uma representação simplificada, de uma parcela do
mundo real, composta por objetos reais.
Modelagem: atividade através da qual se cria um modelo.
Modelo de dados: Um modelo de dados é uma descrição das informações que
devem ser armazenadas em um banco de dados, ou seja, é a descrição
formal da estrutura de BD (descrição dos dados, dos relacionamentos entre
os dados, da semântica e das restrições impostas aos dados).
2.2. REQUISITOS PARA MODELAGEM DE D ADOS
Entender a realidade em questão, identificando os objetos que compõe a parte
da realidade que vai ser modelada..
Representar formalmente a realidade analisada, construindo um modelo de
dados.
Estruturar o modelo obtido e adequá-lo ao SGBD a ser usado, transformando o
modelo conceitual em modelo lógico.
2.3. MODELOS C ONCEITUAIS
São usados para descrição de dados no nível conceitual. Proporcionam
grande capacidade de estruturação e permitem a especificação de restrições de
dados de forma explícita. Exemplos:
Modelo Entidade-Relacionamento (M.E.R.)
Modelo de Semântica de dados
Modelo Infológico
Modelos Orientados para Objetos (OO)
17
2.4. MODELOS LÓGICOS
São usados na descrição dos dados no nível lógico. Em contraste com
modelos conceituais, esses modelos são usados para especificar tanto a
estrutura lógica global do BD como uma descrição em alto nível da
implementação.
2.4.1. MODELO RELACIONAL
Um BD relacional possui apenas um tipo de construção, a tabela. Uma
tabela é composta por linhas (tuplas) e colunas (atributos). Os relacionamentos
entre os dados também são representados ou por tabelas, ou através da
reprodução dos valores de atributos.
Idéias básicas Edward F. Codd , laboratório pesquisas da IBM em 1970
Exemplo : Considere o BD composto de clientes e contas.
NOME
José
Juca
Juca
Carlos
Carlos
RUA
Rua A
Rua B
Rua B
Rua C
Rua C
Nº CONTA
40
30
38
45
CIDADE
JF
RJ
RJ
SP
SP
Nº CONTA
40
30
38 *
45
38 *
SALDO
1.000,00
2.000,00
2.500,00
3500,00
* compartilham a mesma conta, devem ser sócios
18
2.4.2. MODELO DE R EDE
O BD em rede é um grafo, onde os nós representam os registros e os
arcos representam os relacionamentos entre os registros, através de ligações
pai-filho. Diferente do modelo hierárquico, um registro pode possuir diversos
registros pai.
Origem na linguagem de programação Cobol, Primeiro SGBD comercial
IDS (Integrated Data Store) projetado para a General Eletric na década de 60.
Extensão : DBTG-CODASYL(Data Base Task Group – Conference on
Data Systems and Languages) , 1º especificação padrão de BD em 1971.
Exemplos : TOTAL, IDMS, ADABAS
JOSE
RUA A
JF
40
1.000,00
JUCA
RUA B
RJ
30
2.000,00
CARLOS
RUA C
SP
38
2.500,00
45
3.500,00
19
2.4.3. MODELO HIERÁRQUICO
Um BD hierárquico é uma coleção de árvores de registros. Os registros
são usados para representar os dados e ponteiros são usados para representar o
relacionamento entre os dados, numa ligação do tipo pai-filho. A restrição é
que um determinado registro somente pode possuir um registro pai.
Exemplo : 1º SGBD da IBM IMS (Information Management System),
DMS2 SGBD da Unisys.
JOSÉ RUA A JF
JUCA RUA B RJ
CARLOS RUA C SP
40 1.000,00
30 2.000,00
38 2.500,00
38 2.500,00
45 3.500,00
20
EXEMPLOS DOS MODELOS
A) MODELO RELACIONAL :
FORNECEDOR ( fabricante )
RAZÃO
Unisys
Elebra
Flex Disk
IBM
Microlab
TECNOLOGIA
Propria
Control Data
Shugart
Propria
Ampex
PEÇA (disco)
CODIGO
410
416
421
427
TIPO
Flexível
Rígido
Rígido
Rígido
ACIONAMENTO
Step Motor
Voice Coil Linear
Step Motor
Voice Coil Rotatório
FORNECIMENTO
RAZAO
Unisys
Elebra
Elebra
Flex Disk
Flex Disk
IBM
Microlab
CODIGO
416
416
410
421
427
416
427
PREÇO
6.360,00
2.480,00
230,00
250,00
900,00
5.808,00
1.100,00
21
B) MODELO REDE
UNISYS
PROP.
ELEBRA
6.360,00
410
CON.DATA
FLEX DISK
230,00
250,00
2.480,00
FLEXIVEL
STEP M
416
RIGIDO
VOICE
SHUGART
IBM PROP.
900,00
421
RIGIDO
MICROLAB
5.808,00
STEP M
427
RIGIDO
AMPEX
1.100,00
VC DAT
C) MODELO HIERÁRQUICO
416
410
FLEXIVEL
RIGIDO
STEP M
UNISYS
PROPRIA
ELEBRA
ELEBRA
VOICE COIL LINEAR
C. DATA
230,00
421
RIGIDO
SHUGART
RIGIDO
2.420,00
PROPRIA
5.808,00
VOICE COIL ROTATORIO
STEP M
F DISK
F DISK
C. DATA
IBM
427
6.380,00
SHUGART
900,00
250,00
MICROLAB
AMPEX
1.100,00
22
2.5. MODELO DE D ADOS F ÍSICO
Usados para descrever os dados em seu nível mais baixo. Capturam os
aspectos de implementação do SGBD.
3.
MODELO ENTIDADE-RELACIONAMENTO (M.E.R.)
3.1. INTRODUÇÃO
Apresentado por Peter Chen, em 1976
É a técnica mais difundida para construir modelos conceituais de bases de
dados
É o padrão para modelagem conceitual, tendo sofrido diversas extensões
Está baseado na percepção de uma realidade constituída por um grupo básico
de objetos chamados ENTIDADES e por RELACIONAMENTOS entre
estas entidades
Seu objetivo é definir um modelo de alto nível independente de implementação
O modelo é representado graficamente por um Diagrama de EntidadeRelacionamento (DER), que é simples e fácil de ser entendido por usuários
não técnicos
Conceitos centrais do MER: entidade, relacionamento, atributo,
generalização/especialização, agregação (entidade associativa)
3.2. ENTIDADE
Conjunto de objetos da realidade modelada sobre os quais deseja-se manter
informações no Banco de Dados
Uma entidade pode representar objetos concretos da realidade (pessoas,
automóveis, material, nota fiscal) quanto objetos abstratos (departamentos,
disciplinas, cidades)
A entidade se refere a um conjunto de objetos; para se referir a um objeto em
particular é usado o termo instância (ou ocorrência)
No DER, uma entidade é representada através de um retângulo que contém o
nome da entidade
PESSOA
DEPARTAMENTO
23
3.3. RELACIONAMENTO
É toda associação entre entidades, sobre a qual deseja-se manter informações
no Banco de Dados.
Os relacionamentos representam fatos ou situações da realidade, onde as
entidades interagem de alguma forma
Um dado por si só não faz uma informação, pois não tem sentido próprio; é
necessário que haja uma associação de dados para que a informação seja
obtida.
Exemplos:
Fornecimento: entre as entidades FORNECEDOR e MATERIAL
Matrícula: entre as entidades ALUNO e DISCIPLINA
Financiamento: entre as entidades PROJETO e AGENTE
FINANCEIRO
No DER, os relacionamentos são representados por losangos, ligados às
entidades que participam do relacionamento
DEPARTAMENTO
LOTAÇÃO
EMPREGADO
Diagrama de ocorrências de relacionamentos:
24
3.3.1. AUTO- RELACIONAMENTO
Relacionamento entre ocorrências da mesma entidade.
PESSOA
marid
o
esposa
CASAMENTO
Diagrama de ocorrências no auto-relacionamento:
O papel da entidade no relacionamento indica a função que uma
ocorrência de uma entidade cumpre em uma ocorrência de um relacionamento.
3.3.2. C ARDINALIDADE DE R ELACIONAMENTOS
A cardinalidade de uma entidade em um relacionamento expressa o
número de instâncias da entidade que podem ser associadas a uma determinada
instância da entidade relacionada.
Devem ser consideradas duas cardinalidades:
Cardinalidade mínima de uma entidade é o número mínimo de instâncias
da entidade associada que devem se relacionar com uma instância da
entidade em questão.
Cardinalidade máxima de uma entidade é o número máximo de
instâncias da entidade associada que devem se relacionar com uma
instância da entidade em questão.
25
3.3.3. C ARDINALIDADE MÁXIMA
No projeto para BD relacional (como neste curso) não é necessário
distinguir as cardinalidades que sejam maiores que 1. Assim, são usados
apenas as cardinalidades máximas 1 e n (muitos).
26
3.3.4. CLASSIFICAÇÃO DE RELACIONAMENTOS B INÁRIOS
A cardinalidade máxima é usada para classificar os relacionamentos
binários (aqueles que envolvem duas entidades).
a) Relacionamentos 1:1 (um-para-um)
Uma instância da entidade “A” está associada com no máximo uma instância
da entidade “B”.
Uma instância da entidade “B” está associada com no máximo uma instância
da entidade “A”.
A
B
A1
B1
A2
B2
A3
B3
27
b) Relacionamentos 1:N (um-para-muitos)
. Uma instância da entidade "A" esta associada a qualquer número de
instâncias da entidade "B".
. Uma instância da entidade "B", todavia, pose estar associada a no
máximo uma instância da entidade "A"
A1
B1
A2
B2
B3
B4
28
c) Relacionamentos N:N (muitos-para-muitos)
Uma instância da entidade "A" esta associada a qualquer número
instâncias da entidades "B". Uma instância da entidade "B" esta associada a
qualquer número de instância da entidades "A"
A
B
A1
B1
A2
B2
A3
B3
A4
B4
29
3.3.5. RELACIONAMENTO TERNÁRIO
É o relacionamento formado pela associação de três entidades
Cardinalidade em relacionamentos ternários:
3.3.6. C ARDINALIDADE MÍNIMA
A cardinalidade mínima é usada para indicar o tipo de participação da
entidade em um relacionamento. Esta participação pode ser:
Parcial ou Opcional: quando uma ocorrência da entidade pode ou não
participar de determinado relacionamento; é indicado pela
cardinalidade mínima = 0 (zero).
Total ou Obrigatória: quando todas as ocorrências de uma entidade
devem participar de determinado relacionamento; é indicado pela
cardinalidade mínima > 0 (zero).
30
Exemplos:
CLIENTE
1
N
REALIZ
A
PEDIDO
Um cliente pode fazer pedidos ou não, mas todos os pedidos devem
estar associados a um cliente.
DEPTO
1
N
ALOCA
EMPREGADO
Todos os departamentos devem possuir pelo menos um empregado
alocado, e todos os empregados devem estar alocados em um departamento.
DEPTO
1
N
ALOCA
EMPREGADO
10
Parcialidade mínima: para um departamento ser criado, devem existem
pelo menos 10 empregados alocados.
3.4. NOTAÇÕES A LTERNATIVAS
Notação Santucci/MERISE: semântica participativa
DEPTO
(0,
N)
ALOCA
(1,
1)
ALOCA
N
EMPREGADO
Notação Setzer: semântica associativa
1
DEPTO
EMPREGADO
Notação Heuser: semântica associativa
(1
DEPTO
(0,N
ALOCA
EMPREGADO
31
3.5. A TRIBUTO
É um dado que é associado a cada ocorrência de uma entidade ou
relacionamento.
Os atributos não possuem existência própria ou independente - estão sempre
associados a uma entidade ou relacionamento
Exemplos:
Funcionário: Matrícula, Nome, Endereço
Material: Código, Descrição
Financiamento: Valor total, Meses
Fornecedor: Nome, Endereço
3.5.1. DOMÍNIO
É o conjunto de valores válidos que um atributo pode assumir.
Ex: Estado civil: solteiro, casado, divorciado, viúvo
3.5.2. TIPOS DE A TRIBUTOS
a) Opcional/Mandatório
Opcional: o atributo pode possuir um valor nulo (vazio). Ex: número de
telefone
Mandatório: o atributo deve possuir um valor válido, não nulo. Ex:
nome do cliente
b) Monovalorado/Multivalorado
Monovalorado: o atributo assume um único valor dentro do domínio.
Ex: data de nascimento
Multivalorado: o atributo pode assumir um número qualquer de valores
dentro do domínio. Ex: Telefone para contato
32
c) Atômico/Composto
Atômico: o atributo não pode ser decomposto em outros atributos. Ex:
Idade
Composto: o atributo é composto por mais de um atributo. Ex: Endereço
3.5.3. A TRIBUTO DE RELACIONAMENTO
Assim como as entidades, os relacionamentos também podem possuir
atributos.
3.5.4. IDENTIFICADOR DE E NTIDADES
Conjunto de atributos que tem a propriedade de identificar univocamente cada
ocorrência de uma entidade
Toda entidade deve possuir um identificador
O identificador deve ser mínimo, único, monovalorado e mandatório
33
3.5.5. RELACIONAMENTO IDENTIFICADOR (ENTIDADE F RACA)
Existem casos em que uma entidade não pode ser identificada apenas
com seus próprios atributos, mas necessita de atributos de outras entidades com
as quais se relaciona. Este relacionamento é denominado Relacionamento
Identificador. Alguns autores denominam uma entidade nesta situação de
Entidade Fraca.
3.5.6. IDENTIFICADOR DE R ELACIONAMENTOS
Uma ocorrência de relacionamento diferencia-se das demais pelas
ocorrências das entidades que participam do relacionamento. No exemplo
No exemplo, uma ocorrência de ALOCAÇÃO é identificada pela
ocorrência de Engenheiro e pela ocorrência de Projeto. Ou seja, para cada par
(engenheiro, projeto) há no máximo um relacionamento de alocação.
Em certos casos, será necessário o uso de atributos identificadores de
relacionamentos. Por exemplo:
Como o mesmo médico pode consultar o mesmo paciente em diversas
ocasiões, é necessário o uso de um atributo que diferencie uma consulta da
outra.
34
3.6. GENERALIZAÇÃO/E SPECIALIZAÇÃO
A generalização é um processo de abstração em que vários tipos de entidade
são agrupados em uma única entidade genérica, que mantém as propriedades
comuns
A especialização é o processo inverso, ou seja, novas entidades especializadas
são criadas, com atributos que acrescentam detalhes à entidade genérica
existente
A entidade genérica é denominada superclasse e as entidades especializadas
são as subclasses. A superclasse armazena os dados gerais de uma entidade,
as subclasses armazenam os dados particulares
Este conceito está associado à idéia de herança de propriedades. Isto significa
que as subclasses possuem, além de seus próprios atributos, os atributos da
superclasse correspondente.
Usada quando é necessário caracterizar entidades com atributos próprios ou
participação em relacionamentos específicos
35
Uma generalização/especialização pode ser total ou parcial:
É total quando, para cada ocorrência da entidade genérica, existe sempre
uma ocorrência em uma das entidades especializadas.
É parcial quando nem toda ocorrência da entidade genérica possui uma
ocorrência correspondente em uma entidade especializada.
36
3.7. ENTIDADE A SSOCIATIVA (AGREGAÇÃO)
O uso desta abstração é necessário quando um relacionamento deve ser
representado como uma entidade no modelo conceitual. Isto ocorre quando é
necessário estabelecer um relacionamento entre uma entidade e um
relacionamento.
Para atender a esta situação foi criado o conceito de Entidade Associativa ou
Agregação. A agregação é simplesmente um relacionamento que passa a ser
tratado como entidade.
Considerando o exemplo
Se for necessário adicionar a informação que, a cada consulta um ou
mais medicamentos podem ser prescritos ao paciente, será necessário criar uma
nova entidade (MEDICAMENTO). Esta entidade deve se relacionar com as
consultas, mas CONSULTA é um relacionamento. Deve ser criada então uma
entidade associativa.
37
Outra forma alternativa de se representar a entidade associativa é
3.8. RELACIONAMENTO MUTUAMENTE E XCLUSIVO
Neste tipo de relacionamento uma ocorrência de um entidade pode estar
associada com ocorrências de outras entidades, mas não simultaneamente.
AVIÃO
TRANSPORTE
CARGA
TRANSPOR
TE
PASSAGEIRO
38
4.
O MODELO RELACIONAL
Foi introduzido pelo pesquisador da IBM Edward F. Codd, 1970.
Duas características marcantes, razões de sucesso :
. estrutura de dados simples e uniforme
. fundamentação teórica sólida
É o modelo que opera com os dados organizados como um conjunto de
relações.
O modelo de dados Relacional representa o banco de dados com uma coleção
de tabelas
Representação tabular :
Toda relação pode ser vista como uma tabela, onde cada linha é uma tupla e em
cada coluna estão valores de um mesmo domínio.
Exemplo :
FORNECIMENTO
FORNECEDOR
1
2
4
PEÇA
2
3
1
PROJETO
5
7
1
QUANTIDADE
18
25
4
Relação = tabela
Tupla = linha
Atributo = coluna
39
4.1. C ARACTERÍSTICAS DAS TABELAS - MODELO R ELACIONAL
a) Cada Tabela tem um nome único através do qual ela é referenciada.
b) Cada Tabela contém um número fixo de colunas(grau tabela).
c) Não existem linhas iguais.
d) A ordem das linhas é irrelevante(identificação não é feita pela
localização, mas sim pelo valor da chave primária).
e) Cada coluna tem um nome único(diferente das demais colunas).
f) A ordem das colunas é irrelevante(a coluna é identificada pelo seu nome).
g) Cada coluna contém valores atômicos(não são permitidos grupos de
valores).
h) Cada valor de coluna é extraido de um determinado DOMÍNIO(conjunto
de valores possíveis).
i) Duas ou mais colunas podem ser definidas sobre um mesmo Domínio.
j) Operações sobre colunas diferentes exigem que as colunas pertençam ao
mesmo Domínio,
k) Conexões entre Tabelas somente serão expressas através de valores das
colunas(Chave Estrangeira).
40
4.2. CONCEITOS B ÁSICOS
4.2.1. CHAVE PRIMÁRIA : (PRIMARY KEY)
É um atributo(coluna) ou uma combinação de atributos cujos valores
distinguem uma linha das demais dentro de uma tabela.
NUMFUNC
Chave primária
NOMEFUNC
CPFFUNC
DEPTOFUNC
4.2.2. CHAVE ESTRANGEIRA : (FOREIGN KEY)
É um atributo ou uma combinação de atributos, cujos valores aparecem
necessariamente na chave primária de uma tabela.
A chave estrangeira é o mecanismo que permite a implementação de
relacionamentos(navegabilidade) em um banco de dados relacional.
NUMFUNC
Chave primária
NOMEFUNC
DEPTO
Chave primária
NOMEDPTO
CPFFUNC
DEPTOFUNC
Chave estrangeira
4.2.3. CHAVE CANDIDATA OU ALTERNATIVA
Em alguns casos, mais de um atributo ou combinações de atributos podem
servir para distinguir uma linha das demais. Um dos atributos (ou combinação
de atributos) é escolhido como chave primária, os demais atributos (ou
combinações) são denominados chaves CANDIDATAS
NUMFUNC
Chave primária
NOMEFUNC
CPFFUNC
Chave candidata
DEPTOFUNC
Chave estrangeira
41
4.3. NORMALIZAÇÃO
O que é ?
É o processo formal de exame e agrupamento de dados numa forma capaz
de suportar melhor as mudanças futuras, minimizando o impacto destas
mudanças sobre a base de dados.
Segundo Edward F. Codd , normalização é um processo sistemático e
reversível, que consiste em substituir um determinado conjunto de relações por
sucessivos conjuntos nos quais as relações tenham uma estrutura mais simples
e regular.
Principais objetivos :
Reduzir as redundâncias
Reduzir a necessidade de reestruturar as relações quando novos tipos de
dados são introduzidos
Escopo :
A partir deste processo pode-se, gradativamente, substituir um conjunto
de entidades e relacionamentos por um outro, o qual se apresenta “purificado”
em relação as anomalias de (inclusão, alteração, exclusão)
Conclusão :
Durante a Modelagem Conceitual poderemos estar trabalhando sobre
estruturas não normalizadas, pois o objetivo deste modelo é com a
representação semântica da realidade da empresa em primeiro lugar.
Nossa proposta é que seja feita uma revisão a nível de transposição do
DER para o DTR, verificando as regras de normalização antes da transposição
entre os modelos Conceitual e Lógico da realidade modelada.
42
4.3.1. ILUSTRAÇÃO D E UM S ISTEMA A S ER N ORMALIZADO
PEDIDO(Num-Ped, Data-Ped, Num-Cli, Nome-Cli, End-Cli
((Cod-Prod, Nome-Prod, Qtde-Ped,Preço-Prod, Total-Prod)), Total-Ped)
(
Pedido
) Dentro dos parenteses estão os Ítens de dados que constituem o
((
)) Os parenteses duplos envolvem os atributos do item da tupla do
Pedido. Esses (( )) são utilizados para indicar que mais do que um Pedido de
linha pode compor um Pedido:
O Ítem de linha do Pedido é chamado de ‘GRUPO DE REPETIÇÃO’
Num Data- Num Nome End-Cli
-Ped Ped
-Cli -Cli
Produtos
Cod- NomeProd Prod
10
Banana
20
Maça
30
Laranja
100
1/3/99 100
João
Rua A, 19
200
2/4/99 100
João
Rua A, 19
20
40
300
3/5/99 200
Júlio
Rua B, 19
10
50
400
4/7/99 300
Carlos Rua C, 20
10
Total
Qtde- Preço- Total- -Ped
Ped Prod prod
10
0,10
1,00
15
1,00 15,00
20
0,20
4,00
20,00
Maça 20
1,00 20,00
Mamão 10
0,50
5,00
25,00
Banana 20
0,10
2,00
Pêra
10
0,50
5,00
7,00
Banana 10
0,10
1,00 1,00
43
Nesta Estrutura
O que acontece se:
- O Cliente mudar o endereço
Estes problemas ocorrem na vida real
Devemos analisar também a redundância, um mesmo Cliente cada
vez que fizer um pedido vamos guardar (nome-cli, end-cli).
Anomalias de armazenamento.
1 – Inclusão :
Só é possível incluir um novo Cliente a partir de um pedido.
Se nosso sistema fosse, única e exclusivamente baseado na tabela
apresentada até o momento, não poderíamos cadastrar um novo Cliente em
nossa tabela, a menos que surgisse um pedido para ele.
2 – Exclusão :
Se houver a exclusão do Pedido número 400, toda a informação do
Cliente Carlos será perdida.
Neste caso, podemos perceber que o fato de um pedido conter, em sua
estrutura, os dados do Cliente, vinculados diretamente a sua existência, pode
nos levar simplesmente, perder esses dados quando um pedido for excluído.
3 – Alteração :
Se algum dado do Cliente 100 mudar, teremos que efetuar esta operação
em várias linhas da tabela.
Neste caso será necessário processar a alteração em cada uma das linhas
de cada um dos pedidos pertencentes a esse cliente
44
Um analista experiente, intuitivamente separaria os vários
atributos do Pedido em arquivos(TABELAS) distintos.
CLIENTE
PEDIDO
ITEM-PEDIDO
PRODUTO
A Normalização realiza formalmente esta separação dos atributos
em registros normalizados(CLIENTE, PEDIDO, ITEM-PEDIDO, PRODUTO)
baseado na Dependência existente entre cada atributo e sua chave primária.
Ela consegue essa separação de ENTIDADES baseada não na
intuição(como acontece com um analista de sistemas experiente), mas através
de uma metodologia formal, que não requer experiência anterior com
computadores.
45
4.3.2. ANÁLISE DE D EPENDÊNCIA F UNCIONAL
Técnica de normalização adotada em nosso curso.
Dependência Funcional :
O atributo B é funcionalmente dependente do atributo A se, em qualquer
instante, um valor em A determina, de modo único, o valor correspondente em
B, na mesma relação.
Exemplo:
EMPREGADO
#Num-Emp
Nome-Emp
Vlr-Sal-Emp
O Nome-Emp é funcionalmente dependente do Num-Emp, pelo fato de cada
Num-Emp está associado sempre ao mesmo Nome-Emp.
Para denotar esta dependência funcional, usa-se uma expressão na forma
Num-Emp à Nome-Emp. A expressão denota que a coluna Nome-Emp
depende funcionalmente da coluna Num-Emp. Diz-se que a coluna Num-Emp
é o determinante da dependência Funcional.
De forma geral, o determinante de uma dependência funcional pode ser um
conjunto de colunas e não somente uma coluna como na definição acima.
46
Propõe três tipos de dependências entre os atributos de uma tabela.
a) Dependência Funcional Total:
Os atributos de uma tabela tem que depender da chave primária e somente da
chave primária.
Um atributo C é totalmente funcionalmente dependente da chave primária
composta pelo atributos A e B , quando for funcionalmente dependente de A
e B e não dependente funcionalmente de qualquer parte da chave primária.
Exemplo :
ALOCAÇÃO
# Num-Emp
# Cod-Proj
Qtde-horas-trab
- A quantidade de horas trabalhadas num projeto não é funcionalmente
dependente do cod-proj, porque não significa o total de horas do projeto e
não é funcionalmente dependente da matrícula do funcionário, porque não
significa o total de horas trabalhadas pelo empregado.
- A quantidade de horas trabalhadas é determinada, de modo único, pela
composição da matrícula do empregado e do código do projeto, porque
significa a quantidade de
horas trabalhadas por empregado em um
determinado projeto.
b) Dependência Funcional Parcial :
O atributo C é parcialmente funcionalmente dependente da chave primária
composta pelos atributos A e B quando for funcionalmente dependente de
A ou B e não de ambos A e B
# Cod-mat
# Cod-forn
Nom-forn
Prc-mat
O atributo nom-forn é funcionalmente dependente somente do atributo
cod-forn. O nome do fornecedor é determinado, de modo único pelo código
do fornecedor, independente dos materiais que são fornecidos.
47
A dependência funcional parcial ocorre quando a chave primária da
relação é composta e se constitui numa anomalia que se deve ser evitada.
A solução para o problema da dependência parcial consiste na criação
de uma nova relação, que será composta pelo atributo ou atributos que
dependem de parte da chave e a chave que determine, de modo único estes
atributos
# Cod-mat
# Cod-forn
# Cod-forn
Nom-forn
Prc-mat
c) Dependência Funcional Transitiva
- O atributo C é dependente funcional transitivo de A se C é funcionalmente
dependente de B e B funcionalmente dependente de A, na mesma relação.
# Num-emp
DFT
D
F
T
D
F
Nom-emp
A
Data-adm-emp
B
C
Cod-proj
Data-term-proj
DF
DF
DF
O atributo data-term-proj é funcionalmente dependente do atributo
cod-proj, que por sua vez é funcionalmente dependente do atributo Numemp. Então data-term-proj é dependente transitivo de Num-emp.
A dependência funcional transitiva constitui numa anomalia que deve ser
evitada.
A solução para o problema da D.F.T. consiste na criação de uma nova
relação que será composta pelo atributo ou atributos que são dependentes
funcionais transitivos tendo como chave primária o atributo que determina a
transitividade.
# Num-emp
Nom-emp
Data-adm-emp
# Cod-proj
Data-term-proj
Cod-proj
48
Resultado da análise da dependência Funcional:
Uma relação estará normalizada segundo a análise da dependência funcional,
quando possuir uma única chave primária, todos os atributos não chaves
forem totalmente funcionalmente dependentes da chave primária e
independentes entre si, ou seja, após a eliminação da dependência funcional
parcial e transitiva, caso exista.
49
4.3.3. F ORMAS N ORMAIS:
1a Forma Normal :
Uma relação estará na 1a FN se não houver atributo representando
agrupamento e nem atributo repetitivo(multivalorado).
2a Forma Normal :
Uma relação estará na 2a FN, se e somente se, estiver na 1a FN e os seus
atributos não chaves forem dependentes funcionais completos da chave
primária.
3a Forma Normal :
Uma relação estará na 3a FN, se e somente se, estiver na 2 FN e todos os
seus atributos não chaves forem dependentes não transitivos da chave primária.
4.3.4. ROTEIRO P RÁTICO PARA NORMALIZAÇÃO:
A)TRANSFORMAÇÃO DE RELAÇÕES NÃO NORMALIZADAS EM
RELAÇOES NA 1ª FN.
- Escolher uma chave primária para a relação
- Separar da relação os atributos(ou grupos) repetitivos, transformando a
relação em outras duas relações.
. Uma delas contendo o grupo repetitivo e que terá como chave a
combinação da chave primária da relação não normalizada e uma chave (ou +)
escolhida(s) entre os atributos repetitivos. (Regra Geral)
. Outra que permanece com a chave original e os atributos restantes.
- Transformar atributos COMPOSTOS em atributos ATÔMICOS
50
B)TRANSFORMAÇÃO DAS RELAÇÕES EM 1ª FN PARA RELAÇÕES
NA 2ª FN.
- Examinar as relações com chave primária composta e verificar se todos os
atributos dependem funcionalmente de toda a chave ou apenas de parte dela.
- Os atributos que dependem de parte da chave, formam uma nova relação, cuja
chave primária é a parte da chave da relação em 1ª FN da qual dependem.
- Apenas os atributos que dependem totalmente da chave composta
permanecem na relação original.
C) TRANSFORMAÇÃO DAS RELAÇÕES EM 2ª FN PARA RELAÇÕES
EM 3ª FN.
- Examinar as dependências funcionais entre todos os atributos das relações em
2ª FN.
- Aqueles atributos que dependem de outro atributo da relação, que não a sua
chave, vão constituir uma nova relação cuja chave é o atributo do qual
dependem.
ATENÇÃO : Esta chave continua como atributo na tabela Base pois é o
elo de ligação entre ambas.
51
4.3.5.EXEMPLO DE NORMALIZAÇÃO:
ENTIDADE
ATRIBUTO
Num-Ped
Data-Ped
Num-Cli
Nome-Cli
End-Cli
Cod-Prod
Nome-Prod
Qtde-Ped
Preço-Prod
Total-Prod
Total-Ped
PEDIDO
X
\
\
\
\
(\)
(\)
(\)
(\)
(\)
\
52
1ª FN – Ëliminar atributos multivalorados e atributos representam agrupamento
ENTIDADE
ATRIBUTO
Num-ped
Data-Ped
Num-Cli
Nome-Cli
Nome-log
Numero-log
Cidade-log
Estado-log
Cep-log
Cod-Prod
Nome-Prod
Qtde-Ped
Preço-Prod
Total-Prod
Total-Ped
PEDIDO ITEM PED
X
\
\
\
\
\
\
\
\
X
X
\
\
\
\
\
53
2 ª FN – Eliminar D.F.P
ENTIDADE
ATRIBUTO
Num-Ped
Data-Ped
Num-Cli
Nome-Cli
Nome-Log
Numero-Log
Cidade-Log
Estado-Log
Cep-Log
Cod-Log
Nome-Prod
Qtde-Ped
Preço-Prod
Total-Prod
Total-Ped
PEDIDO
ITEM PED PRODUTO
X
\
\
\
\
\
\
\
\
X
X
X
\
\
\
\
\
54
3 ª FN – Eliminar D.F.T
- Redundância deve ser evitada. Não devo guardar o que posso
calcular(Cuidado - carroça)
ENTIDADE
ATRIBUTO
Num-Ped
Data-Ped
Num-Cli
Nome-Cli
Nome-Log
Numero-Log
Cidade-Log
Estado-Log
Cep-Log
Cod-Prod
Nome-Prod
Qtde-Ped
Preço-Prod
Total-Prod
Total-Ped
PEDIDO
ITEM PED
X
\
\
X
PRODUTO
CLIENTE
X
\
\
\
\
\
\
X
X
\
\
\
55
4.4. TRANSPOSIÇÃO D.E.R PARA D.T.R
RELACIONAIS)
(D IAGRAMA DE TABELAS
4.4.1. SIMBOLOGIA ADOTADA NO MODELO RELACIONAL
. James Martin
um opcional
um obrigatório
vários
.
Bachman
Notação de setas
1:1
1:N
N:N
56
4.4.2. ANÁLISE DA ENTIDADE NO D.E.R
Toda Entidade vai virar uma Tabela no D.T.R
4.4.3. ANÁLISE DE R ELACIONAMENTO
As ligações entre as tabelas assumem um papel importante, pois ‚
através delas que são representados os relacionamentos do modelo relacional.
Regra Geral :
Toda vez que um relacionamento tiver atributo, este relacionamento
vai ser representado por uma Tabela
Representação do Relacionamento no D.T.R
. Relacionamento vira Tabela
. Relacionamento vai ser representado por uma Chave Estrangeira
4.4.3.1 – Mapeamento Relacionamento 1 p/ 1
A) - As duas relações possuem a mesma chave primária.
Há uma forte razão para unir as duas relações em uma só. Combinam-se
os atributos permanecendo uma única chave primária.
PRODUTO
ESTOQUE-PROD
#COD-PROD
DESC-PROD
PRC-UNIT
#COD-PROD
QTDE-EST
DATA-ULT-MOV
PRODUTO
#COD-PROD
DESC-PROD
PRC-UNIT
QTDE-EST
DATA-ULT-MOV
57
B) - As duas relações possuem diferentes chaves primárias
B.1) - Pelo menos uma das entidades possue participação total no
relacionamento. O atributo Num-emp foi transposto para a relação
departamento, com o objetivo de representar a restrição de que todo
departamento possui um gerente que é empregado da empresa.
1
DEPTO
CHEFIA
1
EMPREGADO
DEPTO
EMPREGADO
#COD-DEPTO
NOME-DEPTO
NUM-EMP
#NUM-EMP
NOME-EMP
B.2) - Ambas entidades possuem participação parcial no relacionamento
Define-se uma terceira relação correspondendo ao relacionamento.
HOMEM
1
CASAMENTO
1
MULHER
HOMEM
MULHER
#CPF-H
NOME-H
#CPF-M
NOME-M
HOMEM
CASAMENTO
MULHER
#CPF-H
NOME-H
#CPF-H
#CPF-M
DATA-CAS
#CPF-M
NOME-M
58
4.4.3.2 – Mapeamento Relacionamento 1 p/ N
A) - A entidade do lado 1 possui participação total no relacionamento.
A chave primária da relação do lado "um" é parte integrante da relação do
lado muitos.
1
CLIENTE
N
FAZ
PEDIDO
CLIENTE
PEDIDO
#COD-CLI
NOME-CLI
CGC-CLI
#NRO-PED
DATA-PED
COD-CLI
B) - A entidade do lado um possui participação parcial no
relacionamento. Define-se
uma terceira
relação correspondendo ao
relacionamento.
HOMEM
1
CASAMENTO
N
MULHER
HOMEM
MULHER
#CPF-H
NOME-H
#CPF-M
NOME-M
HOMEM
CASAMENTO
MULHER
#CPF-H
NOME-H
#CPF-H
#CPF-M
DATA-CAS
#CPF-M
NOME-M
59
4.4.3.3 – Mapeamento Relacionamento N p/ N
- Um relacionamento N:N sempre pode ser resolvido em dois relacionamentos
1:N. Uma relação de interseção deverá ser implementada.
N
N
FORNECEDOR
FORNECIMENTO
MATERIAL
FORNECEDOR
FORNECIMENTO
MATERIAL
#COD-FORN
NOME-FORN
#COD-FORN
#COD-MAT
QTDE
#COD-MAT
DESC-MAT
4.4.3.4 – Mapeamento Relacionamento Múltiplo
FORNECEDOR
N
FORNECIMENTO
N
MATERIAL
N
PROJETO
FORNECEDOR
FORNECIMENTO
MATERIAL
#COD-FORN
NOME-FORN
#COD-FORN
#COD-MAT
#COD-PROJ
QTDE
#COD-MAT
DESC-MAT
PROJETO
#COD-PROJ
NOME-PROJ
60
4.4.3.5 – Mapeamento Agregação
MEDICO
N
ATENDE
N
PACIENTE
N
SOLICITA
N
EXAME
MEDICO
ATENDE
PACIENTE
#COD-MED
NOME-MED
#COD-MED
#COD-PAC
DATA-CONS
#COD-PAC
NOME-PAC
SOLICTA
#COD-MED
#COD-PAC
#COD-EXAME
RESULTADO-EX
EXAME
#COD-EXAME
DESC-EXAME
61
4.4.3.6 – Mapeamento Auto Relacionamento
DISCIPLINA
N
N
PRE-REQUISITO
DISCIPLINA
#COD-DISC
NOME-DISC
PRE-REQUISITO
#COD-DISC-P
#COD-DISC-S
DATA-PRE
62
4.4.3.7 – Mapeamento Hierarquia de Classe
COD-CLI
CLIENTE
NOME-CLI
TIPO
FISCAL
CPF
PESSOA
FÍSICA
CGC
PESSOA
JURÍDICA
63
a) Mapear em uma única Relação
CLIENTE
# COD-CLI
NOME-CLI
CPF-CLI /CGC-CLI
b) Mapear nas Subclasses as relações
PESSOA FÍSICA
# COD-CLI
NOME-CLI
CPF-CLI
PESSOA JURÍDICA
# COD-CLI
NOME-CLI
CGC-CLI
c) Mapear como Relações distintas
CLIENTE
# COD-CLI
NOME-CLI
PESSOA FÍSICA
# COD-CLI
CPF-CLI
PESSOA JURÍDICA
# COD-CLI
CGC-CLI
64
4.5. RESTRIÇÕES DE INTEGRIDADE NO MODELO R ELACIONAL
4.5.1. INTEGRIDADE L ÓGICA
. Conjunto de regras que existem para o modelo de dados, assim como um
conjunto de regras de negócio, que regem a manipulação do BD, de forma a
não ferir nenhuma destas regras estabelecidas
4.5.2. INTEGRIDADE F ÍSICA
. Conjunto de procedimentos operacionais que garantem a integridade do BD,
mesmo em situações de falha de algum componente do ambiente onde o BD é
manipulado
4.5.1- INTEGRIDADE LÓGICA
a) Restrição de Chave
Uma relação deve ter pelo menos uma chave
b) Restrição de Integridade de Entidade
Nenhum valor da chave primária de uma relação pode ser nula
c) Restrição de Integridade de Referência
A chave estrangeira deve ter correspondência com a chave primária em outra
tabela ou ser nula
d) Restrição de Integridade Semântica ou Regras do Negócio
São regras ditadas pelo negócio e não são mapeadas pelo M.E.R por se tratar
de condições especiais
EX: . Valor mínimo de depósito para abertura de uma conta R$10.000,00
. Conta corrente sem movimento a 180 dias será encerrada.
Podem ser cumpridas e implementadas pelos SGBDs Relacionais, através do
mecanismo de Regras ou gatilhos (Triggers), hoje existentes no SQL
65
4.5.1.1 - INTEGRIDADE REFERENCIAL DE INSERÇÃO
1 - Respeitar as cardinalidades mínimas
2 - Antes de INSERIR uma nova linha em uma tabela que contem um valor de
chave estrangeira, é necessário que já exista uma linha em uma tabela com este
valor de chave primária.
Caso contrário, a operação de INSERÇÃO deve ser rejeitada ou uma linha
com a chave primária deverá ser inserida na respectiva tabela.
DEPARTAMENTO
NUMDESCRIÇÃO
DEPTO
100
R.H
200
COBRANÇA
300
INFORMÁTICA
FUNCIONÁRIO
NUMNOME
FUNC
9999
LUIZ
8888
VERA
9898
ALBERTO
NUMDEPTO
100
300
200
4.5.1.2 - INTEGRIDADE REFERENCIAL DELEÇÃO
. Quando uma linha de uma tabela é deletada então:
a) Todas as ocorrências de chave estrangeira desta chave primária também
devem ser deletadas (CASCATA)
b) Os valores de chave estrangeira devem ser atualizados para nulo(SET
NULL)
c) A operação de deleção pode ser rejeitada, se existir uma ocorrência de chave
estrangeira da chave primária a ser deletada. (RESTRITA)
66
4.5.1.3 - INTEGRIDADE REFERENCIAL ATUALIZAÇÃO:
. Se uma chave primária é atualizada, então
a) Mudar para nulas todas as ocorrências existentes de chave estrangeira como
antigo valor
b)Mudar todas as ocorrências de chave estrangeira do antigo valor para o novo
valor
c)Rejeitar a atualização
67
SISTEMAS DE BANCOS DE DADOS
FORMULÁRIOS PARA OS MODELOS CONCEITUAL E LÓGICO DE BANCO DE DADOS
Este texto visa apresentar os formulários usados para documentação do projeto de
Banco de Dados.
Dentro do projeto pretende-se abordar duas fases básicas:
- O Modelo Conceitual: um modelo de alto nível, independente da implementação. O
principal objetivo desta fase é uma produção de uma descrição formal dos dados levantados a
partir dos requisitos dos usuários. O modelo adotado é o Modelo de Entidades e
Relacionamentos (MER), estendido com o conceito de abstração de dados. Nesta fase é
gerado um Diagrama de Entedidades e Relacionamentos (DER).
- O Modelo Lógico: um modelo implementável, que seja processável por determinada
classe de SGBD. O modelo adotado é o Modelo Relacional. Nesta fase do projeto é gerado um
Diagrama de Tabelas Relacionais (DTR), derivado do DER da fase anterior. Durante o projeto
lógico, deverão ser levadas em consideração as necessidades de processamento, a
normalização dos dados e as restrições de integridade das tabelas.
A documentação do projeto de dados constará então de 3 formulários:
a) O Modelo Conceitual de Dados (Anexo 1), composto pelo Diagrama de Entidades e
Relacionamentos (DER) - entidades, relacionamentos, cardinalidade, participação das
entidades
nos
relacionamentos,
abstrações
de
dados
(agregação,
generalização/especialização).
b) O Modelo Relacional (Anexo 2), composto pelo Diagrama de Tabelas Relacionais
(DTR), com a identificação das tabelas e das ligações entre as mesmas.
c) A Descrição da Tabela (Anexo 3), sendo usado um formulário para cada tabela,
composto pela descrição dos dados da tabela e as restrições aplicáveis. As restrições para
garantir a integridade dos dados serão consideradas sob 3 aspectos:
- Integridade de chave:
Em cada tabela será definida uma chave primária, com valores não-nulos.
- Restrição de existência:
Devem ser analisadas as restrições no caso de inclusão de uma nova tupla
ou de alteração de uma tupla existente.
Deve ser mantida a integridade referencial, no caso de tabelas que
possuam chaves estrangeiras.
- Restrição de persistência:
Devem ser analisadas as restrições no caso de exclusão de uma tupla, a
fim de ser mantida a integridade referencial.
3 situações podem ser consideradas:
a) Remoção em CASCATA: propaga a remoção ocorrida em uma tabela
para as outras tabelas relacionadas através de uma chave estrangeira.
b) Bloqueio total (Regra Restrita): a remoção não é permitida se a tupla é
referenciada por outra tabela através de uma chave estrangeira; caso contrário a
remoçao é permitida.
c) Nulificação (Regra SET NULL): a remoção de uma tupla que é
referenciada por outra tabela, através de uma chave estrangeira, implica em
atribuir valores nulos para estas chaves estrangeiras.
68
Anexo 1
CES
Modelo de Dados
Diagrama de Entidades e Relacionamentos - DER
Nome Sistema
Data
69
Anexo 2
CES
Modelo de Dados
Diagrama de Tabelas Relacionais - DTR
Nome Sistema
Data
70
Anexo 3
Modelo de Dados
Descrição de Tabela- DT
CES
Nome Sistema
Nome da
Tabela
Data
Código da
Tabela
Descrição Sumária da Tabela
Composição da Tabela
Nome do Elemento de Dado
Tipo
Códigos para o Tipo de Elemento de Dado:
CP - Chave Primária
CS - Chave Secundária
PO - Preenchimento Obrigatório
CE - Chave Estrangeira
Restrições de Integridade da Tabela
Código da
Tabela
Relacionada
XX
Código do
Restrições em relação à Tabela Relacionada
Relacionamento
YY
Inclusão:
Alteração:
Exclusão:
71
Exercício Padrão – Seção Eleitoral
EXERCÍCIO - UMA SEÇÃO ELEITORAL
A narrativa a seguir descreve o funcionamento de uma seção eleitoral durante uma eleição:
Um eleitor fornece a sua identificação e esta é validada. Se for um eleitor válido ele recebe
autorização para votar. Um eleitor válido é aquele cadastrado na seção, identificado pelo número de seu
Título de elitor (Num_Tit_Ele). Para cada eleitor existem informações armazenadas: nome (Nome_Ele),
Data de nascimento (Data_Nasc_Ele), Endereço(End_Ele), Zona Eleitoral(Num_Zona_Ele) e Seção
Eleitoral(Num-Seção_Ele).
Os votos recebidos são armazenados, sendo gerado um comprovante de votação, entregue ao
eleitor. O voto é uma associação entre um eleitor com os candidatos. Cada voto tem uma data (Data_Vot)
associada e é válido quando o candidato citado é válido. Os candidatos são identificados através de sua
inscrição na Justiça Eleitoral (Num_Insc_Cand), além de seu nome (Nome_Cand) e partido ao qual se
filia (Partido_Cand).
Se o eleitor não comparecer à seção para votar, ele pode justificar-se, enviando um documento à
Justiça Eleitoral. Se a justificativa é aceita, ela é registrada, sendo gerado um comprovante de
justificativa, enviado ao eleitor. As justificativas são registradas através de um número de identificação,
além de informações sobre o eleitor e do local de origem. Existem eleitores que não comparecem à
votação e que, ainda assim, não justificam sua abstenção.
Baseie-se na narrativa apresentada para fazer:
a)
b)
c)
Um diagrama de fluxo de dados - DFD;
Um diagrama de entidades e relacionamentos - DER;
Transponha o DER para o Modelo Relacional, usando as Regras de Mapeamento.
72
Diagrama de Fluxo de dados
ELEITOR
ELEITORES
ELEITOR NÃO
AUTORIZADO
IDENTIFICAÇÃO
ELEITOR
1
VALIDAR
ELEITOR
AUTORIZAÇÃO
CANDIDATOS
2
REGISTRAR
VOTO
VOTO
COMPROVANTE
VOTO
VOTOS
CONFIRMAÇÃO
VOTO
3
GERAR
CONFIRMAÇÃO
VOTO
ELEITORES
ELEITOR
JUSTIFICATIVA
4
VALIADAR
JUSTIFICATIVA
JUSTIFICATIVA
NAO ACEITA
JUSTIFICATIVA
ACEITA
COMPROVANTE
JUSTIFICATIVA
CONFIRMAÇÃO
JUSTIFICATIVA
JUSTIFICATIVAS
5
REGISTRAR
JUSTIFICATIVA
6
GERAR
CONFIRMAÇÃP
JUSTIFICATIVA
73
Diagrama de Entidade e Relacionamento
1
ELEITOR
1
FAZ
JUSTIFICATIVA
N
VOTA
N
CANDIDATO
74
Diagrama de Tabelas Relacionais
T1-ELEITOR
T4-JUSTIF
R1
R2
T2-VOTA
R3
T3-CANDIDATO
75
CES
Nome da
Tabela
Modelo de Dados
Descrição de Tabela- DT
Nome Sistema Data
Eleição
MMM/AA
Eleitor
Código da
Tabela
T1
Descrição Sumária da Tabela
Cadastro dos eleitores válidos
Composição da Tabela
Nome do Elemento de Dado
Num-Tit-Ele
Nome-Ele
Data-Nasc-Ele
End-Ele
Num-Zona-Ele
Num-Seção-Ele
Restrições de Integridade da Tabela
Código da
Tabela
Relacionada
Código do
Tipo
CP,PO
PO
PO
PO
PO
PO
Códigos para o Tipo de Elemento de Dado:
CP - Chave Primária
CS - Chave Secundária
PO - Preenchimento Obrigatório
CE - Chave Estrangeira
Restrições em relação à Tabela Relacionada [ I - Inclusão A - Alteração
E - Exclusão]
Relacionamento
T2
R2
I : Sem restrições
A : Não é permitida a alteração da CP
E : Restrita
T4
R1
I : Sem restrições
A : Não é permitida a alteração da CP
E : Restrita
76
CES
Nome da
Tabela
Modelo de Dados
Descrição de Tabela- DT
Nome Sistema Data
Eleição
MMM/AA
Voto
Código da
Tabela
T2
Descrição Sumária da Tabela
Votos realizados pelos eleitores
Composição da Tabela
Nome do Elemento de Dado
Num-Tit-Ele
Num-Insc-Cand
Data-Voto
Tipo
CP,CE,PO
CP,CE,PO
PO
Códigos para o Tipo de Elemento de Dado:
CP - Chave Primária
CS - Chave Secundária
PO - Preenchimento Obrigatório
CE - Chave Estrangeira
Restrições de Integridade da Tabela
Código da
Tabela
Relacionada
Código do
Restrições em relação à Tabela Relacionada
Alteração
E - Exclusão]
[ I - Inclusão
A-
Relacionamento
T1
R2
I : O eleitor deve estar cadastrado e não deve possuir nenhuma
justificativa
A : Não é permitida a alteração das CP
E : Sem restrições
T3
R3
I : O candidato deve estar cadastrado
A : Não é permitida a alteração das CP
E : Sem restrições
77
CES
Nome da
Tabela
Modelo de Dados
Descrição de Tabela- DT
Nome Sistema Data
Eleição
MMM/AA
Candidato
Código da
Tabela
T3
Descrição Sumária da Tabela
Candidatos cadastrados para a eleição
Composição da Tabela
Nome do Elemento de Dado
Num-Insc-Cand
Nome-Cand
Partido-Cand
Tipo
CP,PO
PO
PO
Códigos para o Tipo de Elemento de Dado:
CP - Chave Primária
CS - Chave Secundária
PO - Preenchimento Obrigatório
CE - Chave Estrangeira
Restrições de Integridade da Tabela
Código da
Tabela
Relacionada
T2
Código do
Restrições em relação à Tabela Relacionada
Alteração
E – Exclusão]
[ I - Inclusão
A-
Relacionamento
R3
I : Sem restrições
A : Não é permitida a alteração da CP
E : Restrita
78
CES
Nome da
Tabela
Modelo de Dados
Descrição de Tabela- DT
Nome Sistema Data
Eleição
MMM/AA
Justif
Código da
Tabela
T4
Descrição Sumária da Tabela
Justificativas apresentadas pelos eleitores ausentes à eleição
Composição da Tabela
Nome do Elemento de Dado
Num-Justif
Local-Justif
Data-Justif
Motivo-Justif
Num-Tit-Ele
Tipo
CP,PO
PO
PO
PO
CE,PO
Códigos para o Tipo de Elemento de Dado:
CP - Chave Primária
CS - Chave Secundária
PO - Preenchimento Obrigatório
CE - Chave Estrangeira
Restrições de Integridade da Tabela
Código da
Tabela
Relacionada
T1
Código do
Restrições em relação à Tabela Relacionada
Alteração
E - Exclusão]
[ I - Inclusão
A-
Relacionamento
R1
I : O eleitor deve estar cadastrado e não deve possuir nenhum voto
A : Não é permitida a alteração das chaves CP ou CE
E : Sem restrições
79
4.6.LINGUAGENS R ELACIONAIS
- FORMAIS
- COMERCIAIS
ÁLGEBRA
CÁLCULO
TUPLAS
DOMÍNIO
SQL
QUEL (Linguagem Consulta) INGRES (1976)
QBE (Query by Example) – IBM (1975)
HISTÓRICO
. 1970: Edward F. Codd
Artigo Modelo Relacional de Dados para grandes BD compartilhados
1º protótipo de um SGBD relacional. SYSTEM/R
. 1974/1975 : Criada a Linguagem SEQUEL
. 1975: QBE(Qurey by Example) - IBM
. 1976/1977: Versão SEQUEL/2 (alterado SQL)
. 1976: Criada a Linguagem QUEL - INGRES
. 1978/1979: ORACLE (Oracle Corporation)
. 1981: Diversos fabricantes lançam produtos baseados no SQL
. 1982: Criação comitê na ANSI para proposta padrão
. 1983: DB2 (IBM)
. 1986: O padrão ANSI SQL é utilizado – SQL 1
. 1988: DB2 versão 2 (IBM)
SQL 1: Padrão original não havia cláusula para especificar chave; modificado
em 1989
SQL 2: aprovado em 1992: implementa conexão cliente/servidor
SQL 3: em fase de aprovação; implementa o Modelo Orientado a Objeto
80
4.6.1 - ÁLGEBRA RELACIONAL
Matemáticamente falando, uma tabela (relação) é um conjunto , um conjunto
de linhas.
No modelo relacional temos o B.D. representado como uma coleção de tabelas,
quando queremos manipular ( recuperar ) dados em geral o resultado nos é
apresentado como uma tabela, derivada de alguma forma de outras tabelas.
A álgebra relacional é um conjunto de operações e relações.
4.6.1.1 - Operações tradicionais
- union
- intersection
- diference
- cartesian
4.6.1.2- Operações especiais
- project
- select
- join
- divide
81
4.6.1 - ÁLGEBRA RELACIONAL
Operadores SQL possuem equivalentes diretos em álgebra
Um S.G.B.D. para ser considerado completamente relacional tem que suportar:
- B.D.R. ( conceito domínio, chave, ...)
- Uma linguagem que seja pelo menos tão potente quanto a álgebra.
4.6.1.1 - Operações Tradicionais
. UNION
R1 union R2 giving R3
R1
COD
NOME CIDADE
S1
JOAO
S2
JOSE
R2
COD
NOME CIDADE
RJ
S1
JOAO
RJ
S3
BETO
R3
COD
NOME CIDADE
RJ
S1
JOAO
RJ
SP
S2
JOSE
RJ
S3
BETO
SP
. INTERSECTION
R1 intersection R2 giving R4
R4
COD
NOME CIDADE
S1
JOAO
RJ
. DIFERENCE
R1 diference R2 giving R5
R5
COD
NOME CIDADE
S2
JOSE
RJ
82
4.6.1.1 - Operações Tradicionais
. CARTESIAN
R6 cartesian R7 giving R8
R6
A
B
A1
A2
R7
C
D
B1
C1
B2
C2
R8
A
B
C
D
D1
A1
B1
C1
D1
D2
A1
B1
C2
D2
A2
B2
C1
D1
A2
B2
C2
D2
4.6.2.2- Operações Especiais
. PROJECT
Project R1 over Cod giving R9
R1
COD
NOME
R9
COD
F1
JOSE
F1
F2
JOAO
F2
F3
MARIA
F3
F4
PEDRO
F4
. SELECT
Select R10 where salario > 5.000 giving R11
R10 COD DEPTO SALARIO
F1
D1
1.000
F2
D2
4.000
F3
D1
6.000
R11 COD
F3
DEPTO SALARIO
D1
6.000
83
4.6.2.2- Operações Especiais
. DIVIDE
Divide R12 by R13 over Cod giving R14
R12 COD B
R13 B
R14 COD
A1
B1
B2
A3
A2
B1
B3
A7
A3
B2
A7
B2
A2
B3
A3
B3
A7
B3
JOIN NATURAL
O JOIN na verdade duplica a coluna que ‚ passada como argumento. (
Nós adotamos que não )
R15 A
B
C
D
R16 A
E
F
S1
ZE
20
RJ
S1
5
6
S2
JO
10
SP
S2
10
7
S3
15
8
R17 A
B
C
D
E
F
S1
ZE
20
RJ
5
6
S2
JO
10
SP
10
7
!
!
A
S1
S2
Join R15 and R16 over A giving R17
Estamos trabalhando com JUNÇÃO baseada em igualdade de valores
(EQUI-JOIN). Mas poderíamos ter JUNÇÃO "maior que", JUNÇÃO "não
igual", etc...
Uma EQUI-JOIN com uma das colunas idênticas eliminadas chama-se
JOIN NATURAL.
84
4.7.SQL (S TRUCTURED QUERY L ANGUAGE)
Mais que uma linguagem de consulta, oferece funções para DEFINIÇÃO,
MANIPULAÇAO e CONTROLE dos dados de um Banco de dados.
DDL (Data Definition Language)
- CREATE
- ALTER
- DROP
: criação de novas estruturas
: alteração de estruturas
: remoção de estruturas
DML (Data Manipulation Language)
- INSERT
- DELETE
- UPDATE
- SELECT
: Inserção de registros
: deleção de registros
: atualização de registros
: Seleção de registros
DCL (Data Control Language)
- GRANT
- REVOKE
: concessão de privilégios a tabelas e visões
: revogação de privilégios a tabelas e visões
Transaction Control
- COMMIT
: efetiva uma alteração no banco de dados
- ROLLBACK : desfaz uma alteração antes de ser efetivada no banco
- SAVEPOINT : permite uma subdivisão lógica de uma transação longa
Restrições de integridade usando
-.STORED PROCEDURES
-.TRIGGERS
85
Dicionário de Dados (Catálogo)
. É um BD de sistema, que pode ser consultado por meio de comandos
SELECT da SQL, contendo:
.informações sobre as tabelas básicas
.as visões
.os direitos de acesso
.as identificações dos usuários, etc
. Sua forma exata é uma característica de cada sistema e não da SQL
4.7.1 - DDL (D ATA DEFINITION L ANGUAGE)
a)CREATE
a-1) CREATE TABLE nome_tabela
(nome_coluna tipo [(tamanho)] [restrição_coluna],
nome_coluna tipo [(tamanho)] [restrição_coluna],
[restrição_tabela] );
.restrição: É um mecanismo pelo qual você limita ou restringe o tipo de dado
que uma coluna pode armazenar.
.restrição_coluna: referencia somente uma coluna, aceitando todos os tipos de
restrições.
[CONSTRAINT nome_restrição] tipo_restrição
.restrição_tabela: referencia uma ou mais colunas. Só não aceita o tipo NOT
NULL.
[CONSTRAINT nome_restrição] tipo_restrição (coluna, ...)
Tipos de restrições
[NOT] NULL :
Indica se a coluna pode ou não receber valores nulos. O default é NULL
UNIQUE :
Indica que a coluna ou combinação de colunas não pode ter valores
repetidos.
86
PRIMARY KEY :
Indica que a coluna ou combinação de colunas forma a chave primária.
Chave Estrangeira
REFERENCES nome_tabela_pai(nome_coluna_pai)
[ON DELETE CASCADE]
Usada a nível de coluna, indica que a coluna é uma chave estrangeira.
FOREIGN KEY(nome_coluna_filho)
REFERENCES nome_tabela_pai(nome_coluna_pai)
[ON DELETE CASCADE]
Usada a nível de tabela, indica que a coluna ou combinação de colunas é
uma chave estrangeira.
ON DELETE CASCADE
Indica quando uma linha na tabela_pai é deletada haverá uma deleção das
linhas correspondentes (chave estrangeira) na tabela_filho.
Obs : O default na ausência da clausula ON DELETE CASCADE é
RESTRICT.
.Exist e ai nda opç ão no padr ão SQ L/2 ON DE LE TE SE T NU LL e
ON UPD ATE C ASC ADE .
CHECK
Não permite que valores que violem a condição estabelecida sejam gravados na
coluna.
Tipos de dados permitidos
CHAR(n): Tipo de dado caracter de tamanho fixo.
VARCHAR(n): Tipo de dado caracter de tamanho variável, sendo sempre
definido seu tamanho máximo(n).
NUMBER(n): Tipo numérico.
NUMBER(p,q): Tipo numérico de ponto decimal (p : posições sendo q: casas
decimais).
DATE: Tipo data
87
Exemplos
a) Restrições a nível de Tabela
CREATE TABLE depto (
num_depto
NUMBER(2),
nome_depto
VARCHAR(15),
local_depto
VARCHAR(15),
CONSTRAINT depto_PK
PRIMARY KEY (num_depto),
CONSTRAINT depto_nome_depto_UK
UNIQUE (nome_depto)
);
CREATE TABLE emp (
num_emp
NUMBER(6),
nome_emp
VARCHAR(30),
salario_emp
NUMBER(7,2),
sexo_emp
CHAR(1),
cargo_emp
VARCHAR(30),
num_depto
NUMBER(7) NOT NULL,
CONSTRAINT emp_PK
PRIMARY KEY (num_emp),
CONSTRAINT sexo_emp_CK
CHECK (sexo_emp in (‘M’, ‘F’)),
CONSTRAINT emp_num_depto_FK
FOREIGN KEY (num_depto)
REFERENCES depto (num_depto)
ON DELETE CASCADE
);
88
b) Restrições a nível de coluna
CREATE TABLE depto (
num_depto
NUMBER(2) PRIMARY KEY,
nome_depto
VARCHAR(15) UNIQUE KEY,
local_depto
VARCHAR(15)
);
CREATE TABLE emp (
num_emp
NUMBER(6) PRIMARY KEY,
nome_emp
VARCHAR(30),
salario_emp
NUMBER(7,2),
sexo_emp
CHAR(1) CHECK ( sexo_emp in (‘M’, ‘F’)),
cargo_emp
VARCHAR(30),
num_depto
NUMBER(7) NOT NULL REFERENCES
depto(num_depto) ON DELETE CASCADE
);
89
a-2) CREATE [UNIQUE] INDEX nome_índice
ON nome_tabela (nome_coluna1, nome_coluna2, ...);
Sugestões criação índices:
. Colunas usadas frequentemente na cláusula WHERE
. FOREIGN KEYS pois estão geralmente envolvidas em JOINS
. PRIMARY KEYS e UNIKE KEYS (normalmente o Sistema cria
automaticamente um UNIQUE INDEX).
Exemplo:
CREATE INDEX emp_nome_emp_idx
ON emp (nome_emp);
Observações:
1 – índices não podem ser alterados; devem ser removidos (com DROP) e
recriados
2 – A decisão de se usar ou não um índice em resposta a uma solicitação
específica de dado não é tomada pelo usuário mas sim pelo sistema
90
b)ALTER
Comando usado para alterar a estrutura de uma tabela:
. adicionando ou alterando colunas.
. inserindo ou removendo restrições
b.1) Adicionando ou modificando colunas de uma tabela
ALTER TABLE nome_tabela
[ ADD (nome_coluna tipo[(tamanho)],...)]
[ MODIFY (nome_coluna tipo[(tamanho)],...)]
Exemplo:
ALTER TABLE emp
ADD (data_nasc_emp date);
ALTER TABLE emp
MODIFY (nome_emp(60) NOT NULL);
b.2) Adicionando ou removendo uma restrição de uma tabela
ALTER TABLE nome_tabela
[ ADD restrição_tabela]
[ DROP PRIMARY KEY | UNIQUE (nome_coluna) |
CONSTRAINT nome_restrição [CASCADE] ];
Exemplo:
ALTER TABLE depto
ADD CONSTRAINT depto_local_depto_UK
UNIQUE (local_depto);
ALTER TABLE depto
DROP PRIMARY KEY CASCADE;
Neste exemplo o comando remove a restrição PRIMARY KEY na tabela
Depto e remove a restrição FOREIGN KEY associada na tabela Emp.
Obs : Aqui estamos removendo apenas as constraints associadas as
tabelas e não os registros de fato.
91
b.3) Habilitando e desabilitando uma restrição em uma tabela
ALTER TABLE nome_tabela
ENABLE | DISABLE nome_restrição [CASCADE];
Exemplo :
ALTER TABLE depto
ENABLE CONTRAINT depto_local_depto_uk;
c)DROP
Para excluir uma tabela ou índice
c.1) Excluir uma tabela, onde os índices também são excluídos
DROP TABLE nome_tabela [CASCADE CONSTRAINTS];
Exemplo:
DROP TABLE emp;
DROP TABLE depto CASCADE CONSTRAINTS;
Neste exemplo o comando exclui a tabela depto e remove todas as
restrições FOREIGN KEY que fazem referência a PRIMARY KEY desta
tabela.
Obs : Aqui CASCADE CONSTRAINTS desfaz apenas as restrições
associadas à chave primária e não excluiu os registros associados pelas chaves
estrangeiras.
c.2) Exclui um índice
DROP INDEX nome_indice
Exemplo :
DROP INDEX emp_nome_emp_idx
92
4.7.2.-DML (DATA MANIPULATION L ANGUAGE)
a) INSERT
a-1) Adicionar novas linhas em uma tabela
INSERT INTO nome_tabela [(nome_coluna1 [,nome_coluna2 ...] )]
VALUES ( valor1 [, valor2 ...]);
Exemplo:
INSERT INTO depto
VALUES (100, ‘INFORMATICA’, ‘JUIZ DE FORA’);
INSERT INTO emp (Num_Emp, Nome_Emp, Sexo, Num_Depto)
VALUES (1313, ‘TEREZA CRISTINA’, ‘F’, 100);
a-2) Copiando linhas de uma outra tabela:
INSERT INTO nome_tabela [(nome_coluna1 [, nome_coluna2...])]
Subquery;
Exemplo:
CREATE TABLE gerente
num_emp
NUMBER(6) PRIMARY KEY,
nome_emp
VARCHAR(30);
INSERT INTO gerente
Select num_emp, nome_emp
From emp
Where cargo_emp = ‘GERENTE’;
93
b)DELETE
DELETE FROM nome_tabela
[WHERE condição] ;
Exemplo:
DELETE FROM depto
WHERE local_depto = ‘JUIZ DE FORA’;
DELETE FROM depto;
Deleta todas as linhas da tabela se for omitida WHERE
94
c)UPDATE
c.1) Atualizar linhas de uma tabela
UPDATE nome_tabela
SET nome_coluna = valor [, nome_coluna = valor]
[WHERE condição];
Exemplo
UPDATE emp
SET nome_emp = ‘JAIR BATISTA’ , sexo_emp = ‘M’
WHERE num_emp = 1313;
Obs : Todas as linhas de uma tabela são atualizadas se você omitir a cláusula
WHERE.
c.2) Atualizar linhas a partir de uma Subquery
UPDATE nome_tabela
SET (nome_coluna, nome_coluna ...) =
(SELECT nome_coluna, nome_coluna, ...
FROM nome_tabela
WHERE condição);
Exemplo:
UPDATE emp
SET (cargo_emp, num_depto) =
(SELECT cargo_emp, num_depto
FROM emp
WHERE num_emp = 1313)
WHERE num_emp = 1320;
95
d)SELECT
Comando usado para fazer consultas as bases de dados
d.1) Forma Básica:
SELECT [DISTINCT] nome_coluna [,nome_coluna...] FROM nome_tabela
Seleção de colunas específicas : Basta relacionar as colunas desejadas
Exemplo:
SELECT num_emp, nome_emp
FROM emp;
Seleção de todas as colunas : Substituir os nome das colunas por *
Exemplo:
SELECT *
FROM emp;
Evitando duplicações: Usar a palavra DISTINCT na cláusula SELECT
Exemplo:
SELECT DISTINCT nome_emp, cargo_emp
FROM emp;
Usando Pseudônimos: Uma consulta SQL normalmente usa o nome da coluna
como cabeçalho; é possível definir um pseudônimo para a coluna que
aparecera no cabeçalho
Exemplo:
SELECT num_emp “Numero do Empregado”
FROM emp;
96
d.2) Uso cláusula WHERE
Usada para filtrar um subconjunto de linhas de uma tabela
SELECT nome_colunas
FROM nome_tabela
[WHERE condição]
Operadores:
=
igual a
<>
diferente de
>
maior que
<
menor que
Between ... and...
entre dois valores
In(lista)
qualquer valor da lista
Like
corresponde a um gabarito
% : corresponde a uma seqüência de zero ou mais caracteres
- : corresponde a um caracter
IS NULL
é valor nulo
Obs : Os quatro últimos podem ser negados através do operador NOT:
. NOT BETWEEN, NOT IN , NOT LIKE, IS NOT NULL
Conjunção de Condições: várias condições podem ser conectadas na cláusula
WHERE usando o operador AND
Exemplo:
WHERE nome_cargo = ‘GERENTE’ AND num_depto > 1000;
Disjunção de Condições : usando operador OR
Exemplo:
WHERE num_depto = 1313 OR local_depto LIKE ‘_J %’;
Obs: _ :primeira posição, J: segunda posição
97
d.3 ) Uso Cláusula ORDER BY
Ordenando o resultado de uma consulta
SELECT nome_colunas
FROM nome_tabela
[WHERE condição]
[ORDER BY {nome_coluna, ...} [ASC|DESC]]
Exemplo:
ORDER BY nome_emp
d.4) Junção de Tabelas
As linhas de uma tabela podem ser combinadas(JOIN) às linhas de outra tabela
através de valores comuns em colunas correspondentes.
Exemplo:
SELECT emp_name, depto_name
FROM emp e, depto d
WHERE e.num_depto = d.num_depto;
d.5) Produto Cartesiano
Todas as linhas da primeira tabela são combinadas com todas as linhas da
segunda tabela.
Ocorre na omissão da cláusula WHERE.
Exemplo:
Tabela EMP com 14 registros
Tabela DEPTO com 4 registros
SELECT nome_emp, nome_depto
FROM emp, depto;
98
d.6) Funções de Manipulação de Valores
Operadores aritméticos
+
*
/
soma
subtração
multipllicação
divisão
Funções Numéricas
SIGN(número) : -1 se negativo, 1 se positivo
MOD(dividendo, divisor) retorna o resto da divisão
ROUND (número, [n_casas]) retorna o numero arrendondado com n casas
TRUNC(número,[n_casas] retorna o número truncado com n casas
Funções Alfanuméricas
CHR(número) retorna o caracter cujo código ASCII seja igual ao número
Ex: CHAR(75) à k
LOWER(string) retorna a string toda em letra minuscula
Ex: LOWER(EXEMPLO FUNC) à exemplo func
UPPER(string) retorna a string toda em letra maiscula
Ex: UPPER(exemplo func) à EXEMPLO FUNC
LENGH(sring) retorna o tamanho da string
Funções de Grupo
Retornam um valor para um grupo de linhas
SELECT função_grupo(nome_coluna)
FROM nome_tabela
[WHERE condição]
[ORDER BY nome_coluna]
99
Obs: Se a cláusula SELECT contiver funções de grupo, o resultado será
somente uma linha. Assim na cláusula SELECT não podem aparecer resultados
individuais junto com expressões que contenham funções de grupo. Neste caso
deveríamos usar GROUP BY
Exemplo : SELECT nome-emp, AVG(salario_emp)
FROM emp WHERE num_depto = 30;
COUNT(* | [DISTINCT] nome_coluna) : Retorna a quantidade de linhas do
grupo
COUNT(*) : inclui linhas duplicatas e linhas que contenham valores NULL.
COUNT(nome_coluna) : retorna o número de linhas com valores NOT NULL
para a coluna(expressão) especificada.
COUNT(DISTINCT nome_coluna) : retorna o número de linhas com valores
distintos.
MAX([DISTINCT] expressão) : Retorna o valor máximo dessa expressão ou
coluna dentro do grupo
MIN([DISTINCT] expressão) : Retorna o valor mínimo dessa expressão ou
coluna dentro do grupo
SUM([DISTINCT] expressão) : Retorna o somatório da expressão de todas as
linhas do grupo
AVG([DISTINCT] expressão) : Retorna a média aritmética de todas as linhas.
Valores NULL são ignorados.
AVG(NVL(nome_coluna,0)) : considera como zero os NULL na média
aritmética.
100
d.7) Uso da Cláusula GROUP BY
Usada para dividir as linhas de uma tabela em grupos menores.
O SQL recupera cada grupo de linhas de acordo com os valores da(s)
expressão(ões) especificada(s) na cláusula GROUP BY.
SELECT nome_colunas , função_de_grupo(nome_coluna)
FROM nome_tabela
[WHERE condição]
[GROUP BY exp1, exp2...]
A cláusula GROUP BY deverá vir sempre após a cláusula WHERE (ou após a
cláusula FROM quando não existir WHERE)
Quando a cláusula GROUP BY é utilizada é possível combinar resultados
individuais com funções de grupo na cláusula SELECT.
Quando a cláusula GROUP BY é utilizada, é possível combinar resultados
individuais com funções de grupo na cláusula SELECT, desde que aqueles
resultados individuais sejam usados no GROUP BY.
Exemplo
SELECT num_ depto, COUNT(nome_emp)
FROM emp
GROUP BY num_depto;
Observação:
Quando estiver usando o GROUP BY certifique-se que todas as colunas não
usadas na função de grupo estejam incluídas na clausula GROUP BY
Usando a cláusula WHERE você pode selecionar linhas antes de agrupar.
Usando GROUP BY para múltiplas colunas:
SELECT num_depto, cargo_emp, SUM(salario_emp)
FROM emp
GROUP BY num_depto, cargo_emp;
101
d.8) Uso da Cláusula GROUP BY com HAVING
A cláusula WHERE não pode ser usada para restringir funções de grupo.
Exemplo:
SELECT num_depto, AVG(salario_emp)
FROM emp
WHERE AVG(salario_emp) > 2000
GROUP BY num_depto;
Os grupos definidos pela cláusula GROUP BY podem ser filtrados pela
cláusula HAVING.
Exemplo:
SELECT num_depto, AVG(salario_emp)
FROM emp
GROUP BY num_depto
HAVING AVG(salario_emp) > 2000
SELECT nome_colunas , função_de_grupo(nome_coluna)
FROM nome_tabela
[WHERE condição]
[GROUP BY exp1, exp2...]
[HAVING função_de_grupo(nome_coluna)]
[ORDER BY nome_coluna]
Exemplo
SELECT cargo_emp, SUM(salario_emp)
FROM emp
WHERE cargo_emp NOT LIKE ‘GEREN%’
GROUP BY cargo_emp
HAVING SUM(salario_emp) > 5000
ORDER BY SUM(salario_emp);
102
d.9) Uso de Subqueries
Subqueries
São comandos SELECT que são utilizados em condições de cláusulas
WHERE ou HAVING para prover resultados que são utilizados para completar
a consulta principal.
Exemplo
SELECT nome_emp, cargo_emp
FROM emp
WHERE cargo_emp = (
SELECT cargo_emp
FROM emp
WHERE nome_emp = ‘JONES’);
Operadores ANY e ALL
Quando a subquery retornar mais de um valor, os operadores ANY e ALL
podem ser utilizados para compatibilizar o resultado da subquery com o tipo do
operador de comparação.
Exemplo
salario_emp > ANY <subquery>
Essa condição será verdadeira quando salario_emp for maior que qualquer um
dos resultados da query.
salario_emp < ANY <subquery>
Essa condição será verdadeira quando salario_emp for menor que qualquer um
dos resultados da query.
salario_emp > ALL <subquery>
Essa condição será verdadeira quando salario_emp for maior que todos os
resultados da subquery.
salario_emp < ALL <subquery>
Essa condição será verdadeira quando salario_emp for menor que todos os
resultados da subquery.
103
Exemplo
SELECT num_emp, nome_emp, cargo_emp
FROM emp
WHERE salario_emp < ANY (
SELECT salario_emp
FROM emp
WHERE cargo_emp = ‘GERENTE’);
Operadores IN e NOT IN
É igual para qualquer membro da lista
... WHERE cargo_emp IN (
SELECT cargo_emp
FROM emp
WHERE nome_emp LIKE ‘A%’);
SELECT nome_emp, salario_emp
FROM emp
WHERE salario_emp IN (800, 950, 13000);
Operadores de Conjuntos
Como o resultado de um query é um conjunto de linhas você pode realizar
operações de conjuntos entre queries:
UNION : União entre os resultados das queries;
INTERSECT : Interseção entre os resultados das queries;
MINUS : Subtração entre os resultados das queries.
104
Exemplo
SELECT nome_emp, cargo_emp, salario_emp
FROM emp
WHERE salario_emp IN(
SELECT salario_emp
FROM emp
WHERE nome_emp = ‘CARLOS’
UNION
SELECT salario_emp
FROM emp
WHERE nome_emp = ‘MARIO’);
Operador EXIST e NOT EXIST
EXIST: retorna “verdadeiro” se uma determinada subquery retornar ao menos
uma linha e “falso” caso contrário
NOT EXIST: retorna o resultado contrário do EXIST.
105
d.10) Criação e Uso de Visões
OBJETIVO
Restringir acesso à certas porções dos dados por questões de segurança. Pré definir
certas consultas definindo tabelas virtuais que poderão ser utilizadas por outras
consultas.
VISÃO
Pode ser vista como uma tabela virtual, isto é, uma tabela que realmente não existe
como tal, mas sim como derivação de uma ou + tabelas básicas.
Uma definição da visão fica armazenada no dicionário, esta definição mostra como
ela é derivada das tabelas básicas.
CRIAÇÃO
CREATE VIEW nome_visão [(nome_coluna [, nome_coluna..])]
AS <SELECT ...>
Obs : Na cláusula AS a Subquerie não pode conter : ORDER BY
Exemplo
A)
CREATE VIEW emp_visao
AS SELECT num_emp, nome_emp, cargo_emp
FROM emp WHERE cod_depto = 20;
B) Depois de criada uma visão, ela estará disponível no Dicionário de Dados;
até este ponto a instrução SELECT não foi executada.
SELECT * FROM emp_visao;
REMOÇÃO
DROP VIEW <nome visão>
A definição será removida do Dicionário de Dados.
106
4.7.3. DCL (D ATA CONTROL L ANGUAGE)
Responsável pela segurança das informações no Banco de dados
Informando ao SGBD, quais operações que um usuário terá sobre uma
determinada tabela do BD
Quando um usuário cria seus objetos(tabelas, visões, procedures,...) diz-se que ele é
o OWNER(dono) destes objetos.
O conjunto de todos os objetos que pertencem a determinado usuário é chamado de
ESQUEMA deste usuário.
Para acessar os objetos de outro esquema( de outro usuário ) basta prefixar o nome
do objeto com o nome do usuário que o criou ( seu owner).
Exemplo
SELECT * FROM CARLOS.emp;
Você pode evitar a repetição da prefixação de uma tabela de outro usuário criando
um sinônimo para ela:
CREATE SYNONYM emp1
FOR CARLOS.emp;
SELECT * FROM emp1;
a) Criação de objetos
a.1) Criação um usuário (CREATE USER)
CREATE USER user
IDENTIFIED BY password;
Exemplo:
CREATE USER carlos
IDENTIFIED BY solrac;
ALTER USER carlos
IDENTIFIED BY newsenha;
O DBA cria o usuário e concede privilégios ao usuário que determinam o que este
usuário pode fazer a nível de Banco de dados
107
b) Concessão de privilégios (GRANT)
b.1) Concessão de privilégios de sistema
GRANT privilégio[, privilégio...]
TO usuário[, usuário..]
privilégios: . CREATE SESSION, CREATE TABLE, CREATE SEQUENCE,
CREATE VIEW, CREATE PROCEDURES.
Exemplo :
GRANT create table, create view
TO carlos;
b.2) Concessão de privilégios sob objetos
O OWNER tem todos os privilégios de um objeto.
GRANT <lista_privilégios>
ON objeto
TO {<lista de usuários> | grupo | PUBLIC }
[WITH GRANT OPTION];
108
lista_privilégios
SELECT
INSERT
UPDATE
DELETE
ALTER
INDEX
ALL
A opção WITH GRANT OPTION : permite que o usuário que está recebendo
o privilégio, possa dar GRANT neste objeto a outros usuários.
Exemplo
GRANT SELECT, INSERT
ON depto
TO julio, mario;
c) Revogação de privilégios
REVOKE <lista_privilégios>
ON objeto
FROM {<lista_usuarios> | grupo | PUBLIC };
OBS : Privilégios concedidos a outros com a opção WITH GRANT OPTION
também serão revogados.
Exemplo
REVOKE SELECT, INSERT
ON depto
FROM mario;
109
4.7.4 - TRANSACTION C ONTROL
A linguagem SQL possui também comandos que permitem o controle do
resultado de uma transação.
Uma transação é uma seqüência ATÔMICA de operações no BD que constitui
a unidade lógica de trabalho dos SGBDs.
Exemplo
Transferencia de dinheiro entre duas contas.
Conta A = 1000 e Conta B = 2000 .
Consistência Somatório Contas A e B igual 3000.
Transação de Transferência A à B
Passo 1 da transação
Read(A,a)
a:= a-100
Write(A,a)
Passo 2 da transação
Read(B,b)
B:= b+100
Write(B,b)
Imagine o que aconteceria se o primeiro passo fosse executado com sucesso,
mas por algum motivo não fosse possível completar o segundo passo. O
dinheiro da conta A haveria desaparecido e a consistência do BD estaria
comprometida.
O esquema de transações garante que isso não acontecerá, pois enquanto o
COMMIT não for executado, as atualizações não serão efetivadas. No caso de
uma falha, o primeiro passo seria desfeito.
110
a) COMMIT
Sempre que um comando COMMIT é executado :
. A transação em andamento é implicitamente terminada.
. As alterações realizadas pelas transações são tornadas permanentes e
irreversíveis.
Os possíveis bloqueios que a transação mantinha são liberados.
Apaga todos os savepoints.
b) ROLLBACK [to SAVEPOINT point_name]
Sempre que um comando ROLLBACK é executado:
. Encerra a transação
. As alterações realizadas pela transação em andamento são desfeitas
como se nunca tivessem ocorrido.
. Os possíveis bloqueios que a transação mantinha são desfeitos.
. Apaga todos os SAVEPOINTs.
Se você executar comando ROLLBACK TO SAVEPOINT :
. Desfaz todas as alterações até o SavePoint especificado.
. Apaga todos os SavePoints criados após o especificado e mantém os
demais.
c)SAVEPOINT point_name
Em muitas implementações de SQL, existe o comando SAVEPOINT para
permitir a subdivisão lógica de transações longas.
111
Exemplo
INSERT
UPDATE
INSERT
DELETE
|<----------------------------------transação------------------------------------------- >|
. Commit
. Savepoint A
. Savepoint B
< -------------------RollBack to Savepoint B
< ---------------------------------------------------------------------RollBack to Savepoint A
< --------------------------------------------------------------------------------------------RollBack
Commit : Termina a corrente transação tornando permanentes as alterações.
Savepoint : Marca um Savepoint na corrente transação
RoolBack : Termina a corrente transação descartando todas as alterações não
efetivadas.
RollBack to Savepoint name : descarta as alterações não efetivadas até o
savepoint indicado
112
4.7.5 – RESTRIÇÕES DE INTEGRIDADE USANDO S TORED P ROCEDURES E
TRIGGERS
. Conforme foi mostrado anteriormente as restrições de integridade são
especificadas de forma declarativa no momento da definição dos dados
(CREATE) ou depois (ALTER). SQL/92.
.Uma outra forma de especificar restrições de integridade é a forma
procedimental, através de TRIGGERS e STORED PROCEDURES.
. A maioria dos SGBDs relacionais de ponta disponíveis no mercado suporta
este tipo de regras ativas mas infelizmente a SQL/92 não previu a sua
padronização.
4.7.5.1 - STORED PROCEDURES
113
.Quando uma aplicação solicita a execução de uma QUERY, todo o texto da
mesma é enviado pela rede ao servidor onde será finalmente compilado e
executado.
. Stored Procedures são na verdade uma seqüência de comandos SQL,
armazenados no Dicionário de Dados (DD), agrupados de forma que executar a
stored procedure é executá-los todos seqüencialmente no servidor
. Podem ser também usadas para implementar regras de negócios que não
podem ser especificadas declarativamente na descrição das tabelas.
- Criando uma Stored Procedured
CREATE PROCEDURE <Nome_procedure>
[(argumento [IN|OUT|IN OUT] tipo)]
AS Comandos_SQL;
IN :
argumento é de entrada
OUT :
argumento é de saída
IN OUT argumento é utilizado como entrada na chamada da procedure e
como saída após sua execução.
114
a) Exemplo de procedure sem parâmetro
Criar uma procedure que aumente o salário dos empregados em 10%
CREATE PROCEDURE aumento_salario
AS
BEGIN
UPDATE emp
SET salario_emp = salario_emp * 1.1;
END;
EXEC aumento_salario;
b)Exemplo de procedure com parâmetro de input:
Criar uma procedure para aumentar os salários de apenas um departamento e
com um percentual diferente
CREATE PROCEDURE aum_sal_depto
(var_depto IN number, percentual IN number)
AS
BEGIN
UPDATE emp
SET salario_emp = salario_emp *(1+percentual/100)
WHERE num_depto = var_depto;
END;
EXEC aum_sal_depto(30,21)
Estabelecendo aumento de 21 por cento para o depto 30
- Excluindo uma Stored Procedure
DROP PROCEDURE <nome_procedure>
115
4.7.5.2. TRIGGERS
.São tipos especiais de Stored Procedures que são executados automaticamente
pelo servidor quando um comando INSERT, UPDATE ou DELETE é
executado em uma tabela ou visão.
.Permite especificar regras de negócios do tipo EVENTO-CONDIÇÃOAÇÃO, em que o SGBD detecta a ocorrência de um EVENTO, avalia uma
CONDIÇÃO no banco de dados e, se esta for satisfeita, executa uma AÇÃO
predeterminada.
116
Criando uma Trigger
CREATE [OR REPLACE] TRIGGER <nome_da_trigger>
{BEFORE | AFTER }
{DELETE | INSERT | UPDATE [OF coluna [,coluna]...]}
[OR { DELETE | INSERT | UPDATE [OF coluna [,coluna]...]} ]
ON {nome_tabela}
[ [REFERENCING { OLD [AS] old | NEW [AS] new}...]
FOR EACH {ROW | STATMENT}[WHEN (condição)] ]
Comandos SQL
BEFORE : o trigger é disparado antes de executar o comando associado ao
trigger (quando for necessário fazer algum pré-processamento antes do
comando)
AFTER: o trigger é disparado depois de executar o comando associado ao
trigger
DELETE : o trigger é associado ao comando DELETE
INSERT : : o trigger é associado ao comando INSERT
UPDATE : o trigger é associado ao comando UPDATE. UPDATE OF associa
o trigger a colunas específicas; caso contrário, o trigger será associado à
alteração de qualquer coluna
STATMENT
Este trigger é disparado apenas uma vez.
Exemplo:Se um comando UPDATE atualizar 15 linhas, os comandos contidos
no trigger serão executados apenas uma vez, e não a cada linha processada.
ROW
Esse trigger tem os seus comandos executados para todas as linhas que sejam
afetadas pelo comando que gerou o acionamento do trigger
Dentro de um trigger do tipo ROW_LEVEL é possível acessar o valor de um
campo de uma linha. Normalmente é necessário preceder o nome da coluna
com o sufixo :new ou :old, pois em determinado instante pode-se obter tanto o
valor antigo como o valor novo do campo.
117
No comando INSERT, os valores dos campos que serão gravados deverão ser
precedidos pelo sufixo :new
No comando DELETE os valores dos campos da linha que está sendo
processada devem ser precedidos do sufixo :old
No comando UPDATE, o valor original que será gravado é acessado com o
sufixo :old; os novos valores que serão gravados devem ser precedidos do
sufixo :new
WHEN
Só é valida para FOR EACH ROW
Especifica a restrição do trigger, onde a condição é avaliada para cada linha e
se a condição for verdadeira a expressão SQL associada ao trigger é executada.
118
a)Exemplo
Regra: Nenhum funcionário pode ganhar mais que oito mil reais.
Além de ativar o trigger de inclusão (INSERT), evitando que um novo
funcionário tenha um salário superior ao limite, as rotinas de atualização
(UPDATE) devem ser verificadas para evitar que os salários dos funcionários
existentes sejam alterados para valores não permitidos.
CREATE TRIGGER testa_salario
BEFORE INSERT OR UPDATE OF salario_emp
ON emp
FOR EACH ROW
BEGIN
IF :NEW.salario_emp > 8000 THEN
raise_application_error (-20000, ‘valor incorreto’);
END IF
END;
Disparo do gatilho
UPDATE emp
SET salario_emp = 30000
WHERE num-emp = 2500;
Saída do comando:
Update emp
ERRO na linha 1
ORA-20000 : valor incorreto
ORA-04088 : erro durante a execução do trigger ‘testa_salario’
119
b)Exemplo
Após incluir um registro na tabela emp vamos replicar este registro na tabela
empdup.
CREATE TRIGGER repins_emp
AFTER INSERT ON emp
FOR EACH ROW
BEGIN
INSERT INTO empdup
VALUES
(:NEW.num_emp, :NEW.nome_emp, :NEW.salario_emp,
:NEW.sexo_emp, :NEW.cargo_emp, : NEW.num_depto);
END;
Quando inserir na tabela emp inserir também na tabela repins_emp
INSERT INTO emp
(1313, ‘CARLOS’, 4000,00 ,’M’, ‘GERENTE’, 30);
c)Exemplo
Após deletar um registro na tabela emp vamos deletar este registro na tabela
empdup.
CREATE TRIGGER repdel_emp
AFTER DELETE ON emp
FOR EACH ROW
BEGIN
DELETE FROM empdup
WHERE num_emp = :old.num_emp;
END;
120
d)Exemplo
Regra: Não é permitido aumentar o limite de crédito em mais de 50%.
CREATE TRIGGER T_monitora_limite
BEFORE UPDATE OF limite_credito
ON conta
REFERENCING OLD AS antes, NEW AS depois
FOR EACH ROW
BEGIN
IF :depois.limite_credito > 1,5 * :antes.limite_credito THEN
raise_application_error (-20001, ‘Aumento inválido);
END IF
END;
.Esta característica da SQL é fundamental em sistemas Cliente-Servidor.
.Uma regra de negócios global, válida para todo o banco de dados, pode ser
especificada no servidor, não havendo a necessidade de ser replicada em todos
os programas de aplicação que potencialmente possam violar a regra.
.Isto confere modularidade na especificação de regras, o que implica em
facilidade de manutenção, já que a regra é especificada apenas uma vez e
automaticamente executada no servidor.
Ativando/desativando um trigger
ALTER TRIGGER <nome-trigger> ENABLE/DISABLE;
Removendo um trigger
DROP TRIGGER <nome_trigger>;
121
Exercício:
Suponhamos que exista uma regra no Banco de Dados BANCO: Quando uma
registro de PESSOA é excluído, seu CPF, nome e endereço devem ser
gravados num tabela de histórico EX_CLIENTES.
Com uso de STORED POCEDURE
CREATE PROCEDURE P_exclui_pessoa
(X_CPF IN VARCHAR)
AS
X_Nome VARCHAR;
X_End
VARCHAR;
BEGIN
SELECT nome, endereço INTO X_nome , X_end
FROM pessoa WHERE CPF = X_CPF;
INSERT INTO ex_cliente VALUE (X_CPF, X_nome, X_end);
DELETE FROM pessoa WHERE CPF = X_CPF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN raise_application_error(-20002, ‘CPF inválido’);
END exclui_pessoa;
Sendo assim qualquer exclusão de PESSOA deve ser feito através do
procedimento P_exclui_pessoa.
Com uso do TRIGGER T_exclui_pessoa
CREATE TRIGGER T_exclui_pessoa
BEFORE DELETE
ON pessoa
FOR EACH ROW
BEGIN
INSERT INTO ex_cliente
VALUES (:old.CPF, :old.nome, :old.endereco);
END T_exclui_pessoa;
A diferença entre P-exclui_pessoa e T_exclui_pessoa é que o trigger executa a
regra automaticamente toda vez que um registro de PESSOA é excluído,
enquanto o “procedure” requer uma chamada explícita do usuário ou de uma
aplicação.
122
4.7.6 - SQL EMBUTIDA
As instruções da SQL EMBUTIDA são prefixadas por um sinal $(para que
possa ser facilmente reconhecida pelo pré compilador) e as instruções SQL
podem referenciar variáveis do programa utilizando o prefixo %
Observação:
Estes prefixos podem variar de linguagem para linguagem.
1 - A instrução $SELECT inclui uma clausula INTO especificando variáveis as
quais são atribuídos valores recuperados do BD.
2 - A maioria das instruções SQL pode ser embutida em linguagens
hospedeiras(COBOL, PASCAL, C, ...) de uma forma bastante direta (isto é,
com apenas algumas alterações sobre sua sintaxe)
Entretanto a instrução SQL SELECT faz com que seja retornada uma tabela ao
usuário(dai, conceito CURSOR: mecanismo que permite acesso aos registros
do conjunto 1 por 1)
* Definição
LET <cursor nome> BE <expressão>
* Ativação
OPEN <cursor nome>
Posiciona antes do 1º registro que satisfaça a condição
* Avanço
FETCH <cursor nome>
Avança e extrai campos para INTO
* Desativação
CLOSE <cursor nome>
@ SQLCODE :
Status da pesquisa :
0
ok!
+100
problema
123
Exemplo 1
BD Empresa Bancária
Type Nome = String[25];
Var Aumento : Integer
Continua : Char
D
: Record
Anome, Cnome
:Nome;
Conta
:String[07];
Saldo
:Real;
end;
C
: Record
Cnome
Rua
Cidade
:Nome;
:String[20];
:String[15];
end;
124
A) Segmento de programa que lê o Nome de um Cliente e imprime seu
endereço.
$ DECLARE CLIENTE TABLE
(Nome-Cliente
:Char[50] NOT NULL,
Rua-Cliente
:Char[10],
Cidade-Cliente
:Char[10]);
C1: Continua := ‘S’;
WHILE Continua = ‘S’ DO
BEGIN
WRITELN(‘Entre com o nome do Cliente’);
READLN(C.Cnome);
$ SELECT Rua-Cliente, Cidade-Cliente
INTO %C.Rua, %C.Cidade
FROM CLIENTE
WHERE Nome-Cliente = %C.Cnome;
WRITELN(C.Rua, C.Cidade);
WRITELN(‘Mais Nomes de Clientes (S/N)’);
READLN(Continua);
END;
125
B) Porem, uma consulta SQL pode recuperar várias tuplas.
O que fazer?
Seja um Segmento de programa que lê um nome de agência e lista os Clientes
que tem depósito nesta agência.
Este segmento também lê uma quantidade utilizada para aumentar o saldo de
cada um dos clientes acima.
$ DECLARE DEPOSITO TABLE
(Nome-Agencia
:Char[10] NOT NULL,
Numero-Conta
:Char[08],
Nome-Cliente
:Char[30] NOT NULL,
Saldo
: Real);
C2 : WRITELN(‘Entre com o Nome da Agencia : ‘);
READLN(D.Anome);
$ LET DEPCUR BE
$ SELECT Numero-Conta, Nome-Cliente,Saldo
FROM DEPOSITO
WHERE Nome-Agencia = % D.Anome
FOR UPDATE OF Saldo;
$ OPEN DEPCUR;
$ FETCH DEPCUR INTO %D.Conta, %D.Cnome,%D.Saldo;
WHILE SQLCODE = 0 DO
BEGIN
WRITELN(‘NomeEmpregado’,D.Cnome,D.Conta);
WRITELN(‘Entre com o aumento :’);
READLN(Aumento);
$ UPDATE DEPOSITO SET Saldo=Saldo+%Aumento
WHERE CURRENT OF DEPCUR;
$ FETCH DEPCUR INTO %D.Conta,%D.Cnome,%D.Saldo;
END;
$ CLOSE DEPCUR;
126
5.
– EXERCÍCIOS:
5.1. – EXERCICIOS DE MODELAGEM DE DADOS
5.1.1.Projetos
Este texto descreve uma Empresa de Projetos de grande porte, envolvendo diversos
projetos como Engenharia, Urbanismo, Transporte. A Empresa é organizada em Deptos.
Cada Depto coordena (é responsável) por vários projetos e um projeto é coordenado
obrigatoriamente por um único Depto.
Cada Depto tem um Empregado que o gerencia. Um empregado deve pertencer
obrigatoriamente a um Depto, mas pode estar alocado à vários Projetos.
5.1.2.Loja
Uma loja especializada em computadores resolveu automatizar seus procedimentos de
venda e aluguel de computadores. Através de entrevistas com seu diretor, gerente e
funcionários, observou o seguinte:
Os clientes da loja podem alugar ou comprar computadores.
Se o cliente faz opção por alugar então obrigatoriamente tem que fazer um contrato de
manutenção para dar cobertura ao computador que está sendo alugado.
Sabe-se ainda que :
Dado um cliente e um Contrato este par pode estar associado a várias máquinas.
Dado uma máquina de um determinado contrato este par pertence a várias máquinas.
Dado um Cliente e uma máquina este par pertence a um único contrato.
5.1.3.A Universidade Milenium
Os diversos institutos da Universidade Milenium estão organizados em Departamentos.
Cada departamento possui um corpo docente e um dos professores é o Chefe do
Departamento.
Um Departamento é responsável pelo ensino de diversas disciplinas. Cada disciplina
pode ser lecionada por vários professores. Um professor pode lecionar mais de uma
disciplina.
Os alunos cursam as disciplinas de acordo com os pré-requisitos já alcançados. Os
alunos podem optar com qual professor ele cursará determinada disciplina.
A Universidade mantém, para cada aluno, um Histórico Escolar, que relaciona as
disciplinas que ele já cursou, com as respectivas notas e a freqüência.
5.1.4.Controle de Projetos
Uma Empresa manufatureira funciona num esquema de Projeto, nos quais são alocados
seus empregados com um certo percentual de dedicação.
Administrativamente, os empregados estão lotados em departamentos e podem gerenciar
um ou mais projetos que são gerenciados por um único empregado.
As Peças utilizadas nos projetos são armazenadas nos vários armazéns.
A Empresa mantém um controle do fornecimento Efetivo de Peças feito aos projetos
pelos fornecedores, e um controle de fornecimento Potencial de Peças de cada
um
dos seus fornecedores.
Deve-se controlar a composição das peças, onde uma peça pode ser simples ou composta.
As peças compostas são montagens de peças simples. Cada peça simples pode ser
utilizada para compor várias peças compostas.
127
5.1.5. Empresa do ramo de alimentação
Deseja-se controlar as principais atividades de uma empresa do ramo de alimentação,
que possui várias lojas de varejo e vários armazéns para guardar seus produtos. Estes
armazéns são especializados (por exemplo, frigorífico) de maneira que um produto só
pode ser armazenado em um único armazém e um armazém pode armazenar vários produtos.
As lojas podem emitir vários pedidos, sendo que um pedido deve pertencer
obrigatoriamente a uma loja. Um Pedido é composto de vários produtos e um produto
pode fazer parte de vários pedidos.
Para entregar os pedidos a empresa conta com uma frota de caminhões dos mais variados
tipos. Um caminhão pode atender a vários pedidos, e um pedido pode ser atendido por
mais de caminhão (por exemplo, no caso em que pedido não caiba em um único caminhão).
Observe que o sistema deve ser capaz de informar quais os produtos de determinado
pedido estão em determinado caminhão. O sistema deve permitir ainda que existam
pedidos que não sejam atendidos por nenhum caminhão.
Cada caminhão tem um obrigatoriamente um funcionário que é o responsável pelo mesmo,
e um Funcionário pode ser responsável por mais de um caminhão.
5.1.6.Restaurante
Deseja-se desenvolver um Sistema de Controle das principais atividades de um
restaurante, atendendo às seguintes considerações:
Os
Clientes
novos
deverão
ser
cadastrados
pelo
sistema
para
efeito
de
correspondências futuras, sendo necessário armazenar os dados pessoais. Sabe-se que
cada cliente pode fazer vários pedidos, ou nenhum, e um pedido sempre estará
associado a um único cliente.
Um pedido está associado obrigatoriamente a vários itens de cardápio. Cada item do
cardápio pode estar associado a vários pedidos ou nenhum, sendo necessário armazenar
quais itens foram pedidos, a quantidade de cada um e a data do pedido, a fim de que a
conta, com o valor total, possa ser gerada no final do atendimento.
Cada item do cardápio possui um código, um nome, um tipo (indicando se é bebida ou
comida), uma descrição detalhada e um preço unitário.
Cada pedido está obrigatoriamente associado a uma mesa, sendo possível associar
vários pedidos a uma mesma mesa. Cada mesa é atendida por um único garçon, que pode
atender a várias mesas. O número de identificação do garçon também deve constar na
conta a ser gerada.
5.1.7.A cadeia de Hotéis Imperador
A cadeia de Hotéis Imperador possui diversos hotéis situados nas principais capitais.
Cada hotel possui vários tipos de apartamento (simples, luxo, suite, etc.) e um
apartamento, naturalmente, pertence a um único hotel.
Toda vez que um cliente se hospeda, é necessário que ele informe o número da Carteira
de Identidade ou Passaporte, endereço, data de nascimento e o sexo. Para controle
interno, o hotel também registra o número do quarto alocado e a data da hospedagem.
Qualquer hotel da cadeia deve ser capaz de responder imediatamente a um pedido de
reserva (efetivando-a ou negando-a). A data em que foi feita a reserva deve ser
registrada.
O hóspede pode utilizar os diversos serviços do hotel (lavanderia, sauna, etc.),
pagando a conta apenas no check-out. Os serviços oferecidos por toda a cadeia de
hotéis são padronizados.
128
5.1.8.Modelo para uma biblioteca
Uma Empresa possui uma Biblioteca para uso exclusivo dos seus empregados que podem
levar emprestado um número qualquer de exemplares, e fazer solicitações de empréstimo
(RESERVA) quando não houver exemplar disponível.
Os Livros são classificados em Categorias e em Subcategorias. Eles devem pertencer a
uma única categoria Principal e podem pertencer a várias Categorias Secundárias.
Quando um Livro possui vários Autores um deles e referido como Autor Principal e os
outros como Co-Autores.
129
5.1 – EXERCÍCIOS DE NORMALIZAÇÃO:
5.2.1 - PEDIDOS(#Num-pedido, data-Pedido, Num-Cliente, Nome-Cliente,
End-Cliente, ((Cod-Produto, nome-Produto, Preço-Unitário, QtdePedida,Valor-Total-Item)),Valor-Total-Pedido)
5.2.2 - CONTRATO(#Num-contrato, Cod-Cliente, Nome-Cliente, CPFCliente, Dt-inic-contrato, Dt-term-contrato, ((Num-prestação, Valor-Prestação,
Dt-Venc-prest)), Valor-Total-Contrato)
5.2.3 - EMPREGADO(#Cod-Empregado, Nome-Empregado, TítuloEmpregado, ((Cod-Curso,Nome-Curso, data-início-Curso, Resultado-Curso)))
5.2.4 - PEÇA-ESTOCADA(#Cod-Peça, #Cod-Armazem, Qtde-Estocada,
Tel-armazem)
5.2.5 - QUADRO-PESSOAL
#Cod-Orgão
Nome-Orgão
CARGO N vezes
Cod-Cargo
Nome-Cargo
Número-Vagas
FUNCIONÁRIO N vezes
Matricula-Emp
Nome-Emp
Data-Posse
130
5.2.6 - DADOS-EMPREGADO
#Matricula
Nome
Endereço
Código-Cargo-Atual
Nome-Cargo-Atual
CURSOS N vezes
Cod-Curso
Nome-Curso
Data-Conclusão
Nota-Final
HABILIDADES N vezes
Cod-Habilidade
Nome-Habilidade
Grau-Habilitação
Data-Admissão
Codigo-Orgão-Lotação
Nome-Orgão-Lotação
5.2.7 – PROJETO
#Cod-Proj
Tipo-Proj
Desc-Proj
EMPREGADO N VEZES
Cod-Empregado
Nome-Emp
Cat-Emp
Sal-Emp
Data-ini-Proj
5.2.8 – ARQ_CANDIDATO
#Cod-Curso
Nome-Curso
Num-Vagas-Curso
CANDIDATO N VEZES
Cod-Cand
Nome-Cand
Escore-Cand
131
5.2.9 – ARQ_ALUNO
#Cod-Aluno
Nome-Aluno
CURSO N VEZES
Cod-Curso
Nome-Curso
Sem-Ingresso
DISCIPLINA N VEZES
Cod-Disciplina
Nome-Disciplina
SEMESTRE-CURSADO N VEZES
Sem-Disc-Cursada
Nota-Disc
132
5.2 – EXERCÍCIOS DE SQL.
FORNECEDOR
Cod_forn
F1
F2
F3
F4
F5
Nome_forn
PAVAN
ABC
TUCANO
MATIASE
RODOPAZ
Status_forn
20
10
30
20
30
Cidade_forn
JUIZ DE FORA
RIO DE JANEIRO
RIO DE JANEIRO
JUIZ DE FORA
SÃO PAULO
PEÇA
Cod_peca
P1
P2
P3
P4
P5
P6
Nome_peca
PARAFUSO
PORCA
ARRUELA
PREGO
CANO
FIO
Cor_peca
PRETA
AZUL
BRANCA
PRETA
VERDE
AZUL
EMBARQUE
Cod_Forn
F1
F1
F1
F1
F1
F1
F2
F2
F3
F4
F4
F4
Cod_peca
P1
P2
P3
P4
P5
P6
P1
P2
P2
P2
P4
P5
Qtde
300
200
400
200
100
100
300
400
200
200
300
400
133
0) Criar as tabelas.
CREATE TABLE fornecedor (
cod_forn
CHAR(02),
nome_forn
VARCHAR(50) UNIQUE KEY,
status_forn
NUMBER(02) NOT NULL,
cidade_forn
VARCHAR(30),
CONSTRAINT fornecedor_PK
PRIMARY KEY (cod_forn)
);
CREATE TABLE peca (
cod_peca
CHAR(02) PRIMARY KEY,
nome_peca
VARCHAR(50) UNIQUE KEY,
cor_peca
NUMER(02) NOT NULL,
);
CREATE TABLE embarque (
cod_forn
CHAR(02),
cod_peca
CHAR(02),
qtde
NUMBER(05) NOT NULL,
CONSTRAINT embarque_PK
PRIMARY KEY (cod_forn,cod_peca),
CONSTRAINT cod_forn_embarque_FK
FOREIGN KEY (cod_forn)
REFERENCES fornecedor(cod_forn),
CONSTRAINT cod_peca_embarque_FK
FOREIGN KEY (cod_peca)
REFERENCES peca(cod_peca)
);
134
1) Obtenha o Código de todas as peças fornecidas
SELECT DISTINCT cod_peca
FROM embarque;
2) Obtenha o código dos fornecedores do RIO DE JANEIRO com status > 20
SELECT cod_forn
FROM fornecedor
WHERE cidade = ‘RIO DE JANEIRO’ and status_forn > 20;
3) Obtenha o nome dos fornecedores que fornecem a peça P2
SELECT DISTINCT nome_forn
FROM fornecedor F, embarque E
WHERE F.cod_forn = E. cod_forn and
E.cod_peca = ‘P2’;
4) ORDER BY
Obtenha o código e o status dos fornecedores do RIO DE JANEIRO, por ordem
descendente de Status
SELECT cod_forn,status_forn
FROM fornecedor
WHERE cidade = ‘RIO DE JANEIRO’
ORDER BY status_forn DESC;
135
5). GROUP BY
Para cada Peça fornecida, obtenha o número da Peça e a quantidade total fornecida
daquela Peça.
SELECT cod_peca, SUM(Qtde )
FROM embarque
GROUP BY cod_peca;
Agrupa a tabela por Grupos, de forma que o:
1º Grupo contenha as linhas da Peça P1, o
2º Grupo contenha as linhas da Peça P2, e
assim sucessivamente
A Clausula SELECT é então aplicada a cada Grupo da tabela particionada.
6) HAVING
Obtenha os cod_peca das Peças, para todas Peças fornecidas por mais de um
fornecedor
SELECT cod_peca
FROM embarque
GROUP BY cod_peca
HAVING COUNT(*) > 1;
Funciona para Grupos da mesma forma que WHERE funciona para linha(Se
HAVING for especificado, GROUP BY também tem que ser especificado).
136
7)COUNT
Obtenha o número total de fornecedores que estão efetivamente fornecendo Peças.
SELECT COUNT(DISTINCT cod_forn)
FROM embarque;
8) LIKE
Obtenha todas as Peças, com nome começando com a letra C
SELECT cod_peca
FROM peca
WHERE nome_peca LIKE ‘C%’;
9) IN
Obtenha o nome dos fornecedores que fornecem a Peça P2 .
SELECT nome_forn
FROM fornecedor
WHERE cod_forn IN
(SELECT cod_forn
FROM embarque E
WHERE E.cod_peca = ‘P2’);
137
5.3 - EXECÍCIOS DE ALGEBRA RELACIONAL
5.4.1 - Considere as seguintes relações
FORN
#FN Fnome
1313 Pavan
1320 ABC
1330 DEF
1350 Gil
1360 Visart
FORNECIMENTO
#FN
1313
1313
1320
1320
1360
1313
Fcidade
J. Fora
T.Rios
J. Fora
J. Fora
R.Janeiro
PEÇA
#PN
P1
P2
P2
P3
P3
P4
#PN
P1
P2
P3
P4
Pnome Pcor
pia
branca
vaso bege
sifão branca
fio
preto
QTDE
25
20
25
20
25
20
A) Quais os nomes dos fornecedores que fornecem pelo
menos 1 peça.
a.1) PROJECT FORNECIMENTO OVER FN GIVING R1
a.2) JOIN FORN AND R1 OVER FN GIVING R2
a.3) PROJECT R2 OVER Fnome GIVING R3
PRINT R3
a.1)R1
FN
1313
1320
1360
a.3)R3
Fnome
Pavan
ABC
Visart
a.2) R2
FN
1313
1320
1360
Fnome
Pavan
ABC
Visart
Fcidade
J. Fora
T. Rios
R. Janeiro
138
B) Quais os nomes de todos os fornecedores
PROJECT FORN OVER Fnome GIVING ARQ
C) Encontre o nome da cidade do fornecedor 1360
SELECT FORN WHERE FN = 1360 GIVING R1
PROJECT R1 OVER Fcidade GIVING R2
D)
Encontre o nome dos fornecedores de J. Fora
SELECT FORN WHERE Fcidade=‘J. Fora’ giving R1
PROJECT R1 OVER Fnome GIVING R2
E)
Encontre o nome das Peças fornecidas pelo
fornecedor 1313
F)
Obtenha o nome dos fornecedores que fornecem
a peça P2
139
5.4..2 Considere as seguintes relações
ALUNO
#Matr
9516001
9516002
9516003
9616001
9616002
APROVADO
Nome
Jose
Juca
Joao
Pedro
Carlos
#Matr
9516001
9516001
9516002
9516002
9516002
9516003
DISC#Codigo Nome
ADMP
Administ.
LTPIV
B.D
TAPII
Projeto
#Codigo
LTPIV
TAPII
ADMP
LTPIV
TAPII
ADMP
Nota
55,0
60,0
80,0
90,0
90,0
70,0
Falta
2
4
0
1
2
2
A) Obter o nome dos alunos que já cursaram todas as
diciplinas.
140
5.4.3 - Considere as seguintes relações
DEPTO
#DN
1313
1320
1350
1360
Dnome
Informática
Pessoal
Material
Contábil
EMPREGADO #EN
11310
11530
11540
11560
11570
11600
11620
11630
11650
11700
11750
Enome
Carlos
Adriana
Léo
João
Júlio
Pedro
Alyne
Juca
Mário
Ricardo
Geraldo
Dlocal
RJ
SP
SP
JF
Dchefe
11310
11560
11600
11650
Esalário
5.000,00
3.000,00
3.500,00
5.000,00
3.000,00
5.000,00
3.000,00
8.000,00
5.000,00
3.000,00
7.000,00
DN
1313
1313
1313
1320
1320
1350
1350
1350
1360
1360
1360
A)Obter o nome dos empregados que ganham mais do que
seus chefes.
141
6.
BIBLIOGRAFIA
1 – ELMASRI, Ramez e NAVATHE, Shamkant B. Fundamentals of
Database System. Third Edition. Ed. Addison-Wesley, 2000.
2 – KORTH, H. F. e SILBERSCHATZ, A. Sistemas de Banco de
Dados. Terceira edição Ed. McGraw Hill, 1999.
3 – HEUSER, Carlos Alberto.Projeto de Banco de Dados. Porto
Alegre. Ed. Sagra Luzzato, 1998.
4 -DATE, C. J. Introdução a Sistemas de Banco de Dados, Rio de
Janeiro, Ed. Campus, 1986.
5 - STEZER, Valdemar W.. Banco de Dados, São Paulo, Ed. Edgar
Blucher, 1986.
6 - COUGO, Paulo. Modelagem Conceitual, Rio de Janeiro, Ed.
Campus,1997.
7 - BARBIERI, Carlos. Modelagem de Dados, Rio de Janeiro, Ed. IBPI
Press,1994.
8 - MACHADO, Felipe Nery R. Projeto de Banco de Dados uma
visão prática, São Paulo, Ed. Érica, 1995.
9 - CERÍOLA, Vicente Osvaldo. Banco de dados relacional e
distribuído, São Paulo, LTC, 1995.
10 - CHEN, Peter. Gerenciando Banco de Dados. A abordagem
entidade e Relacionamento, São Paulo, Ed. McGraw Hill, 1990
11 – Apostila Curso oracle(OR8) : SQL and PL/SQL, Volumes 1 e 2.
Oracle Corporation, 1998
12 – Ramalho, José Antônio. Oracle 8i-Série Ramalho , São Paulo,
1999.
13 – SUNDERRAMAN, Rajshekhar. Oracle Programming, Geórgia
Ed. Addison-Wesley, 1999
142