Structure Query Language (SQL)

Solutech Informática.
Introdução à Banco de Dados:
Página.: 1
Solutech Informática.
Banco de Dados:
Introdução
Devido a carência de literatura destinada ao ensino de Banco de Dados e SQL para estudantes, elaboramos a presente
apostila, que não possui o intento de esgotar tão abrangente volume de informações, servindo tão somente para
estabelecer um mínimo de conhecimentos destinados a introduzir o estudante no mundo dos Gerenciadores de Banco
de dados e da Linguagem SQL.
Banco de Dados
Todos nós sabemos existirem gigantescas bases de dados gerenciando nossas vidas. De fato sabemos que nossa conta
bancária faz parte de uma coleção imensa de contas bancárias de nosso banco. Nosso Título Eleitoral ou nosso
Cadastro de Pessoa Física, certamente estão armazenados em Bancos de Dados colossais. Sabemos também que
quando sacamos dinheiro no Caixa Eletrônico de nosso banco, nosso saldo e as movimentações existentes em nossa
conta bancária já estão à nossa disposição.
Nestas situações sabemos que existe uma necessidade em se realizar o armazenamento de uma série de informações
que não se encontram efetivamente isoladas umas das outras, ou seja, existe uma ampla gama de dados que se referem
a relacionamentos existentes entre as informações a serem manipuladas.
Estes Bancos de Dados, além de manterem todo este volume de dados organizado, também devem permitir
atualizações, inclusões e exclusões do volume de dados, sem nunca perder a consistência. E não podemos esquecer que
na maioria das vezes estaremos lidando com acessos concorrentes a várias tabelas(classe) de nosso banco de dados,
algumas vezes com mais de um acesso ao mesmo registro de uma mesma tabela!
Um Banco de Dados representará sempre aspectos do Mundo Real. Assim sendo uma Base de Dados (ou Banco de
Dados, ou ainda BD) é uma fonte de onde poderemos extrair uma vasta gama de informações. A forma mais comum de
interação Usuário e Banco de Dados, dá-se através de sistemas específicos que por sua vez acessam o volume de
informações geralmente através da linguagem SQL.
DBA - DBP
Os Administradores de Banco de Dados (DBA) são responsáveis pelo controle ao acesso aos dados e pela coordenação
da utilização do BD. Já os projetistas de Banco de Dados (DBP) são analistas que identificam os dados a serem
armazenados em um Banco de Dados e pela forma como estes serão representados.
Analistas e Programadores de Desenvolvimento
Os Analistas e Programadores de Desenvolvimento, criam sistemas que acessam os dados da forma necessária ao
Usuário Final, que é aquele que interage diretamente com o Banco de Dados.
SGBD – Sistemas Gerenciadores de Banco de Dados
Os SGBDs são sistemas responsáveis em armazenar dados e fornecer serviços capazes de manipulá-los.
Para armazenar os dados em um disco, os SGBDs possuem uma forma estruturada padrão de representar os registros e
campos, fornecendo serviços que permitem a criação e alteração dessas definições de dados.
Fornecem ainda, um mecanismo interno para manter os dados no disco e para saber onde está cada elemento em
particular. Também são responsabilidades dos SGBDs disponibilizar serviços para incluir, classificar, atualizar e
eliminar os dados armazenados.
Resumindo:
Um SGBD é uma coleção de programas que permitem ao usuário definir, construir e manipular Bases de Dados para as
mais diversas finalidades.
Página.: 2
Solutech Informática.
Considerações Finais
Atualmente, existe uma tendência de mercado em se dizer que qualquer problema será resolvido, caso a empresa
adquira um Banco de Dados. Naturalmente, em um ambiente com acesso constante ao Banco de Dados (acesso
concorrente, obviamente), onde a segurança seja de vital importância e que o desempenho da aplicação escrita estiver
comprometendo a empresa, considerando-se logicamente uma aplicação bem escrita, sem dúvida a aquisição de um
Banco de Dados poderá ser o primeiro passo na solução do problema.
Analogamente ao que ocorreu com o aparecimento das primeiras linguagens de programação voltadas ao Windows,
onde estas foram apresentadas como capazes de alavancar os negócios da empresa, e no geral causaram mais frustração
do que solução, a aquisição do Banco de Dados, pode gerar o mesmo tipo de problema.
É fundamental que a empresa candidata a utilizar um Banco de Dados, normalize-se totalmente, pois soluções “quebragalho”, típicas do ambiente que dispõe de um Gerenciador de Arquivo, tendem a ser impossíveis em um ambiente
estruturado sobre o Banco de Dados. Portanto, sob pena de se realizar um grande investimento, e não se colher fruto
algum, é muito conveniente, que a empresa antes de adquirir um Banco de Dados, passe por um processo de adaptação,
preferencialmente contando com pessoal especializado, geralmente consultores, que não tenham qualquer ligação com
fabricantes de Bancos de Dados.
Características Gerais de um SGBD
Os SGBD tem sete características operacionais elementares sempre observadas, que passaremos a listar:
Característica 1: Controle de Redundâncias- A redundância consiste no armazenamento de uma mesma informação
em locais diferentes, provocando inconsistências. Em um Banco de Dados as informações só se encontram
armazenadas em um único local, não existindo duplicação descontrolada dos dados. Quando existem replicações dos
dados, estas são decorrentes do processo de armazenagem típica do ambiente Cliente-Servidor, totalmente sob controle
do Banco de Dados.
Característica 2: Compartilhamento dos Dados- O SGBD deve incluir software de controle de concorrência ao
acesso dos dados, garantindo em qualquer tipo de situação a escrita/leitura de dados sem erros.
Característica 3: Controle de Acesso- O SGDB deve dispor de recursos que possibilitem selecionar a autoridade de
cada usuário. Assim um usuário poderá realizar qualquer tipo de acesso, outros poderão ler alguns dados e atualizar
outros e outros ainda poderão somente acessar um conjunto restrito de dados para escrita e leitura.
Característica 4: Interfaceamento- Um Banco de Dados deverá disponibilizar formas de acesso gráfico, em
linguagem natural, em SQL ou ainda via menus de acesso, não sendo uma "caixa-preta" somente sendo passível de ser
acessada por aplicações.
Característica 5: Esquematização- Um Banco de Dados deverá fornecer mecanismos que possibilitem a compreensão
do relacionamento existentes entre as tabelas e de sua eventual manutenção.
Característica 6: Controle de Integridade- Um Banco de Dados deverá impedir que aplicações ou acessos pelas
interfaces possam comprometer a integridade dos dados.
Característica 7: Backups- O SGBD deverá apresentar facilidade para recuperar falhas de hardware e software,
através da existência de arquivos de "pré-imagem" ou de outros recursos automáticos, exigindo minimamente a
intervenção de pessoal técnico.
Existe a possibilidade de encontramos Bancos de Dados que não satisfaçam completamente todas as características
acima, o que não o inválida como Banco de Dados. Na prática podemos encontrar situações onde a primeira
característica não seja importante, e as redundâncias podem ser aceitas em algumas situações sob controle da aplicação
(algo não muito recomendado, mas passível de aceitação).
Página.: 3
Solutech Informática.
A segunda característica (Compartilhamento dos Dados) pode ser desconsiderada principalmente em ambiente de
desenvolvimento, ou ainda em aplicações remotas.
O Controle de Acesso pode ser descartado em pequenas empresas, sendo que o aplicativo em questão, mais o software
de rede, podem facilmente se incumbir desta característica, no caso de pequenas empresas, com reduzido número de
pessoas na área operacional.
O Backup em tempo de execução, é uma característica sempre disponível, porém temos aplicações que invariavelmente
são comprometidas por falhas de hardware, e outras, que o mesmo tipo de falha não causa perda alguma de dados ou de
integridade. Então, cada Banco de Dados tem esta característica melhor ou pior implementada, cabendo ao
Administrador de Banco de Dados escolher aquele que lhe oferecer mais segurança.
Devemos ressaltar ainda, que podemos ter um Banco de Dados Modelo A, que respeite integralmente as regras básicas
e disponha de todas as características apresentadas, enquanto um Modelo B que apesar de respeitar as regras básicas,
não suporte uma ou outra característica desejável, mas tenha um desempenho excelente, enquanto o Modelo A seja
apenas razoável no quesito desempenho, nos levará seguramente a escolher o Modelo B como sendo o ganhador para
nossa instalação!
Isto ocorre pois, na prática, todo usuário deseja um tempo de resposta muito pequeno. O chamado “prazo de entrega”
muito comum em Bancos de Dados operando nos limites de sua capacidade, ou nos casos onde o hardware está muito
desatualizado, é fonte de inúmeros problemas para o pessoal de informática. Neste caso é melhor abrirmos mão de uma
Interface Amigável, de um Gerenciamento Automático de Backups ou ainda de outras características que não
julgarmos fundamentais, para nos livrarmos do problema de má performance do Banco de Dados.
A escolha do Banco de Dados da empresa, portanto é uma decisão muito delicada, na medida em que está irá acarretar
troca de aplicativos e troca de hardware. Os investimentos diretamente aplicados no Banco de Dados, costumam ser
infinitamente menores do que aqueles a serem aplicados na empresa, visando sua perfeita adequação ao novo SGBD.
Esta decisão, sempre que possível, deve ser tomada por especialistas em Banco de Dados, com profundos
conhecimentos de Análise de Sistemas, de Banco de Dados e de Software de Gerenciamento de Bases de Dados, de
forma a evitar que a empresa escolha um Banco de Dados inadequado aos seus propósitos, e que pouco tempo depois,
seja obrigada a perder todos investimento realizado em Software e Hardware.
Página.: 4
Solutech Informática.
Modelagem de Dados:
1 – Tabelas (Entidade) – Identifica o objeto de interesse do sistema e tem “vida própria”, ou seja, é a representação
abstrata de um objeto do mundo real sobre o qual desejamos guardar informações.
Ex: Clientes, Fornecedores, Alunos, Funcionários, Departamentos, etc.
2 – Campo(Atributo) – Informações que desejamos guardar sobre a instância da Entidade.
Ex: Matrícula do Aluno, Nome do Aluno, Endereço do Cliente, Sexo do Funcionário, etc.
3 – Registro – É um conjunto de Campos, ou seja, os elementos da entidade.
Ex: Cliente 10, Funcionário João, Aluno Pedro, etc.
4 – Chave ou identificador
É o atributo contido no Registro que o personaliza e individualiza.
4.1 – Chave Primária
É o atributo cujo valor identifica unicamente um Registro. Deve ter conteúdo reduzido e valor constante no
tempo.
Em uma entidade, devemos identificar entre seus atributos componentes, qual é aquele que o personaliza e
individualiza, apresentando um valor que não se altera com o decorrer do tempo. A este atributo, denominamos
Atributo Chave ou simplesmente Chave Primária.
O aspecto destacado e também básico para a definição da chave é o valor permanente que ela deve ter, pois
caso contrário causaria uma série de anomalias resultantes dessa sua inconstância, já que, além da sua manutenção,
propagaria alterações em todos os locais onde estivessem residentes.
4.2 – Chave Estrangeira
É quando um atributo de uma entidade é a chave primária de outra entidade.
Quando duas entidades se relacionam, normalmente a entidade dependente herda a chave da entidade origem,
residindo como um de seus atributos.
4.3 – Chave Composta ou Concatenada
É formada pelo grupamento de mais de um atributo.
Em alguns casos um único atributo não consegue personalizar a entidade em que está contido, tornando-se
impossível fixar a interdependência com os demais atributos. Quando isto acontece, é conveniente verificar se há outros
atributos que associados ao anterior, consigam fixar o conceito de dependência efetiva, nesse caso obtemos uma chave
composta, o que denominamos chave concatenada.
4.4 – Chave Secundária
É o atributo que não possui a propriedade de identificação única.
São aqueles que permitem a ordenação dos Registros dentro de uma tabela, facilitando as pesquisas à mesma.
5 – Relacionamento
É a associação, com um significado, entre duas ou mais Tabelas.
Página.: 5
Solutech Informática.
Processo de Normalização:
Modelagem de Dados é o processo utilizado para definir a estrutura do banco de dados através da distribuição dos
dados nas Tabelas. Existem maneiras diferentes de se definir o mesmo conjunto de dados, e uma boa modelagem de
dados pode facilitar bastante o desenvolvimento das aplicações. Vamos ressaltar aqui, dois pontos que devem ser
observados na construção do MODELO: o processo de Normalização e a propagação de chaves primárias.
NORMALIZAÇÃO
Técnica de Análise e Organização de dados que visa determinar a melhor composição para uma estrutura de dados
(Tabela). Os principais objetivos são: Eliminar anomalias que dificultam as operações com os dados, ou seja, facilitar a
manipulação do Banco de Dados. Minimizar as redundâncias e os conseqüentes riscos de inconsistências. Reduzir e
facilitar as manutenções.
É um processo passo a passo que consiste na simplificação dos atributos dentro de uma Classe(Tabela), aumentando o
entendimento do Modelo e contribuindo de forma considerável para a estabilidade da Base de Dados.
Sobre um modelo de dados normalizado podemos dizer que:
Possuem as mesmas informações que um não normalizado, só que estruturado de outra maneira.
É muito mais fácil compreender um modelo normalizado do que um não normalizado.
Não possui redundância de dados.
Suportam melhor modificações como inclusão de novos atributos e novas Classes (Tabelas).
NORMALIZAÇÃO DE DADOS
Consiste em definir o formato lógico adequado para as estruturas de dados identificados no projeto lógico do sistema,
com o objetivo de minimizar o espaço utilizado pelos dados e garantir a integridade e confiabilidade das informações.
A normalização é feita, através da análise dos dados que compõem as estruturas utilizando o conceito chamado
"Formas Normais (FN)". As FN são conjuntos de restrições nos quais os dados devem satisfazê-las. Exemplo, pode-se
dizer que a estrutura está na primeira forma normal (1FN), se os dados que a compõem satisfizerem as restrições
definidas para esta etapa.
A normalização completa dos dados é feita, seguindo as restrições das três formas normais existentes, sendo que a
passagem de uma FN para outra é feita tendo como base o resultado obtido na etapa anterior, ou seja, na FN anterior.
Para realizar a normalização dos dados, é primordial que seja definido um campo chave para a estrutura, campo este
que permitirá identificar os demais campos da estrutura.
Página.: 6
Solutech Informática.
Primeira Forma Normal (1FN)
Consiste em retirar da estrutura os elementos repetitivos, ou seja, aqueles dados que podem compor uma estrutura de
vetor. Podemos afirma que uma estrutura está normalizada na 1FN, se não possuir elementos repetitivos. Exemplo:
Estrutura original:
Arquivo de Notas Fiscais (Num. NF, Série, Data emissão, Cod. do Cliente, Nome do cliente, Endereço do cliente, CGC
do cliente, Relação das mercadorias vendidas (onde para cada mercadoria temos: Código da Mercadoria, Descrição da
Mercadoria, Quantidade vendida, Preço de venda e Total da venda desta mercadoria) e Total Geral da Nota)
Analisando a estrutura acima, observamos que existem várias mercadorias em uma única Nota Fiscal, sendo portanto
elementos repetitivos que deverão ser retirados.
Estrutura na primeira forma normal (1FN):
Arquivo de Notas Fiscais (Num. NF, Série, Data emissão, Código do Cliente, Nome Cliente, Endereço do cliente, CGC
do cliente e Total Geral da Nota)
Arquivo de Vendas (Num. NF, Código da Mercadoria, Descrição da Mercadoria, Quantidade vendida, Preço de venda
e Total da venda desta mercadoria)
Obs. Os campos sublinhados identificam as chaves das estruturas.
Como resultado desta etapa ocorre um desdobramento dos dados em duas estruturas, a saber:
- Primeira estrutura (Arquivo de Notas Fiscais): Dados que compõem a estrutura original, excluindo os elementos
repetitivos.
- Segundo estrutura (Arquivo de Vendas): Dados que compõem os elementos repetitivos da estrutura original, tendo
como chave o campo chave da estrutura original (Num. NF) e o campo chave da estrutura de repetição (Código da
Mercadoria).
Segunda Forma Normal (2FN)
Consiste em retirar das estruturas que possuem chaves compostas (campo chave sendo formado por mais de um
campo), os elementos que são funcionalmente dependente de parte da chave. Podemos afirmar que uma estrutura está
na 2FN, se ela estiver na 1FN e não possuir campos que são funcionalmente dependente de parte da chave. Exemplo:
Estrutura na primeira forma normal (1FN):
Arquivo de Notas Fiscais (Num. NF, Série, Data emissão, Código do Cliente, Nome do cliente, Endereço do cliente,
CGC do cliente e Total Geral da Nota)
Arquivo de Vendas (Num. NF, Código da Mercadoria, Descrição da Mercadoria, Quantidade vendida, Preço de venda
e Total da venda desta mercadoria)
Estrutura na segunda forma normal (2FN):
Arquivo de Notas Fiscais (Num. NF, Série, Data emissão, Código do Cliente, Nome do cliente, Endereço do cliente,
CGC do cliente e Total Geral da Nota)
Arquivo de Vendas (Num. NF, Código da Mercadoria, Quantidade vendida e Total da venda desta mercadoria)
Arquivo de Mercadorias (Código da Mercadoria, Descrição da Mercadoria, Preço de venda)
Como resultado desta etapa, houve um desdobramento do arquivo de Vendas (o arquivo de Notas Fiscais, não foi
alterado, por não possuir chave composta) em duas estruturas a saber:
- Primeira estrutura (Arquivo de Vendas): Contém os elementos originais, sendo excluídos os dados que são
dependentes apenas do campo Código da Mercadoria.
- Segundo estrutura (Arquivo de Mercadorias): Contém os elementos que são identificados apenas pelo Código da
Mercadoria, ou seja, independentemente da Nota Fiscal, a descrição e o preço de venda serão constantes.
Página.: 7
Solutech Informática.
Terceira Forma Normal (3FN)
Consiste em retirar das estruturas os campos que são funcionalmente dependentes de outros campos que não são
chaves. Podemos afirmar que uma estrutura está na 3FN, se ela estiver na 2FN e não possuir campos dependentes de
outros campos não chaves. Exemplo:
Estrutura na segunda forma normal (2FN):
Arquivo de Notas Fiscais (Num. NF, Série, Data emissão, Código do Cliente, Nome do cliente, Endereço do cliente,
CGC do cliente e Total Geral da Nota)
Arquivo de Vendas (Num. NF, Código da Mercadoria, Quantidade vendida e Total da venda desta mercadoria)
Arquivo de Mercadorias (Código da Mercadoria, Descrição da Mercadoria, Preço de venda)
Estrutura na terceira forma normal (3FN):
Arquivo de Notas Fiscais (Num. NF, Série, Data emissão, Código do Cliente e Total Geral da Nota)
Arquivo de Vendas (Num. NF, Código da Mercadoria, Quantidade vendida e Total da venda desta mercadoria)
Arquivo de Mercadorias (Código da Mercadoria, Descrição da Mercadoria, Preço de venda)
Arquivo de Clientes (Código do Cliente, Nome do cliente, Endereço do cliente e CGC do cliente)
Como resultado desta etapa, houve um desdobramento do arquivo de Notas Fiscais, por ser o único que possuía campos
que não eram dependentes da chave principal (Num. NF), uma vez que independente da Nota Fiscal, o Nome, Endereço
e CGC do cliente são inalterados. Este procedimento permite evitar inconsistência nos dados dos arquivos e
economizar espaço por eliminar o armazenamento freqüente e repetidas vezes destes dados. A cada nota fiscal
comprada pelo cliente, haverá o armazenamento destes dados e poderá ocorrer divergência entre eles.
As estruturas alteradas foram pelos motivos, a saber:
- Primeira estrutura (Arquivo de Notas Fiscais): Contém os elementos originais, sendo excluído os dados que são
dependentes apenas do campo Código do Cliente (informações referentes ao cliente).
- Segundo estrutura (Arquivo de Clientes): Contém os elementos que são identificados apenas pelo Código do Cliente,
ou seja, independente da Nota Fiscal, o Nome, Endereço e CGC dos clientes serão constantes.
Após a normalização, as estruturas dos dados estão projetadas para eliminar as inconsistências e redundâncias dos
dados, eliminando desta forma qualquer problema de atualização e operacionalização do sistema. A versão final dos
dados poderá sofrer alguma alteração, para atender as necessidades específicas do sistema, a critério do analista de
desenvolvimento durante o projeto físico do sistema.
Página.: 8
Solutech Informática.
Exemplo:
Entidade (Tabela) Não Normalizada
Pedido
Num_Pedido
Data_Pedido
Valor_Pedido
Cod_Produto
Nome_Produto
Preco_Produto
Qtd_Item
Valor_Item
Cod_Cliente
Nome_Cliente
CPF_Cliente
Grupo Repetitivo
1ª FN
ITEM_PEDIDO
PEDIDO
Num_Pedido
Data_Pedido
Valor_Pedido
Cod_Cliente
Nome_Cliente
CPF_Cliente
Num_Pedido
Cod_Produto
Nome_Produto
Preco_Produto
Qtd_Item
Valor_Item
2ª FN
PEDIDO
ITEM_PEDIDO
Num_Pedido
Num_Pedido
Cod_Produto
Data_Pedido
Valor_Pedido
Cod_Cliente
Nome_Cliente
CPF_Cliente
Qtd_Item
Valor_Item
PRODUTO
Cod_Produto
Nome_Produto
Preco_Produto
3ª FN
PEDIDO
Num_Pedido
Data_Pedido
CodCliente
Valor_Pedido
ITEM_PEDIDO
PRODUTO
Num_Pedido
Cod_Produto
Cod_Produto
Nome_Produto
Qtd_Item
Valor_Item
Preco_Produto
Página.: 9
CLIENTE
Cod_Cliente
Nome_Cliente
CPF_Cliente
Solutech Informática.
Exercício.
Passando pela 1ªFN, 2ªFN e 3ªFN , normalize os formulários abaixo.
A)
Pedido de Compra
ABC Sistemas S/A
Pedido N.º 12345
Prazo de Entrega 10/04/2002
Cliente: 0427 - O picareta do Software e do Hardware S/A
Endereço: Rua dos aflitos S/N
Bairro: Centro
Município: Volta Redonda
UF: RJ
CGC: 29-928-269/0001 - 43
Inscrição Estadual: 80-652-444
Código Produto
2131
12,0
4505
Total:
Unidade
Embalagem
Cx.
Cx.
Quantidade
3
5
UX
10
Descrição
10cm. 1ª via
Disq. 1.4 MB
Preço
Unitário
1500
16,00
Preço
Total
45,00
80,00
Rolo de fio
3,00
30,00
155,00
B)
Ficha de Cadastro de Funcionário
Matrícula:
Nome:
Data de Admissão:
/
Cargo - Código:
Departamento – Código:
/
.
Nome:
Nome:
Dados dos Dependentes
Nome: ______________
Nome: ______________
Nome: ______________
Data Nascimento:
/
/ . Idade: ______
Data Nascimento:
/
/ . Idade: ______
Data Nascimento: ____/____/___ Idade: ______
Total de Dependentes:_____________
Página.: 10
Solutech Informática.
C)
Requisição de Material
Departamento – Código:
Nome:
Código Material
Nome
Quantidade
0093
Fita para Impressora 5
0038
Resma papel
2
Totais
7
Funcionário requisitante – Matrícula 1136 Assinatura: ACL
Gerente - Matrícula 0841 Assinatura: EDN
N.º: 0716
Data: 15/06/95
Preço
4,00
15,00
50,00
d)
Controle de dependentes:
Uma empresa deseja fazer o controle informatizado de seus funcionários e respectivos dependentes. O sistema a ser
definido deverá emitir os seguintes relatórios/consultas.
Funcionários
Dependentes por Funcionários
Dependentes por sexo
Dependentes ordenado por idade
Obs: Criar as entidades, Atributos, Chaves, Relacionamentos.
e)
Empresa de Informática:
Em uma empresa prestadora de serviços de informática necessita-se de um sistema para controlar a atuação dos seus
funcionários nos diversos projetos. O Sistema a ser criado deverá permitir as seguintes Consultas/Relatórios:
Projetos
Analistas
Programadores
Analistas por projetos
Programador por Projeto
Projeto em andamento
Projeto por linguagem
Definir: (Entidade, Atributos, Chaves, relacionamentos)
Obs: Cada Analista pode atuar em vários projetos.
Cada Programador só atua em 1 projeto.
Página.: 11
Solutech Informática.
f)
Clínica:
Em uma clínica precisa-se criar um sistema de controlar consultas, devido a grande demanda. Deseja-se que o sistema a
ser elaborado forneça os seguintes relatórios e/ou consultas:
Médicos;
Pacientes;
Médico por especialidade;
Pacientes por médico;
Consultas por data/horário;
Pacientes por especialidade;
Consultas efetivadas ou não.
g)
Vinícola:
Uma distribuidora de vinhos, que compra das vinícolas e revende vinhos para seus clientes, em função de aumento do
suas vendas, viu-se na necessidade de informatizar o seu controle de estoque de vinhos. Para isto, deverá ser
desenvolvido um sistema que possibilite as seguintes consultas/relatórios.
Vinhos
Vinícolas
Vinhos por tipo
Vinhos por vinícola
Vinhos por safra
Quantidade de estoque de vinhos
Obs. Cada vinho é fornecido por uma vinícola.
Página.: 12
Solutech Informática.
Linguagem SQL:
Página.: 13
Solutech Informática.
Structure Query Language (SQL)
1.Para acesso a um banco de dados relacional, é necessário uma linguagem. A SQL é uma linguagem
usada pela maioria dos bancos de dados relacionais.
2.É uma linguagem baseada no inglês, fácil de escrever, ler e entender.
3.SQL tem como características, a economia de tempo, flexibilidade
bancos de dados.
e segurança na manutenção de
4.Existem pequenas diferenças da linguagem em alguns bancos. No VisualFox por exemplo, quando o
comando ocupa mais de uma linha, devemos colocar ponto e vírgula no final de cada linha, e no final do
comando somente um enter.
No oracle, ao contrário, devemos colocar um ponto e vírgula somente no final do comando, mesmo que
este ocupe mais de uma linha.
5.Os comandos existentes na linguagem SQL esta dividida em 3 grupos:
1 – Comandos DML(Linguagem de Manipulação de Dados) – Conjunto de comandos responsáveis
pela consulta, e atualizações dos dados armazenados no Banco de Dados.
2 – Comandos DDL(Linguagem de Definição de Dados) – Conjunto de comandos responsáveis
pela criação, alteração e deleção das estruturas da tabelas e índices de um sistema.
3 – Comandos DCL(Linguagem de Controle de Dados)
Página.: 14
Solutech Informática.
1 - Comandos DML – Linguagem de Manipulação de Dados.
1.1 – SELECT:
Objetivo: Selecionar um conjunto de registros em uma ou mais tabelas que atenda a uma determinada condição
definida pelo comando.
Sintaxe:
SELECT ALL / * FROM <nome-tabela> [, <nome-tabela>]
DISTINCT
WHERE <condição>
GROUP BY <nome-coluna>
HAVING <condição>
ORDER BY <nome-campo> ASC/DESC
onde:
a) nome-tabela - Representa o nome da(s) tabela(s) que contem as colunas que serão selecionadas ou que serão
utilizadas para a execução da consulta.
b) condição - Representa a condição para a seleção dos registros. Esta seleção poderá resultar em um ou vários
registros.
c) nome-coluna - Representa a(s) coluna(s) cujos resultados são agrupados para atender à consulta.
d) ALL / * - Opção default. Seleciona todos os campos da Tabela(s) em questão.
e) DISTINCT - Opção que mostra os valores obtidos na seleção eliminando as duplicidades.
f) WHERE - Especifica o critério de seleção dos registros nas tabelas especificadas.
g) GROUP BY - Especifica o(s) campo(s) que serão agrupados para atender a consulta.
h) HAVING - Especifica uma condição para seleção de um grupo de dados. Esta opção só é utilizada combinada com a
opção GROUP BY.
i) ORDER BY - Esta opção quando utilizada apresenta o resultado da consulta ordenado de forma crescente ou
decrescente pelos campos definidos.
Algumas funções utilizadas no comando Select.
a) COUNT(*)
Objetivo: Retorna a quantidade de registros existentes no campo especificado. Quando a opção * é utilizada o resultado
é a quantidade de registros existentes. Quando é referenciado o nome de um campo retorna a quantidade de valores
existentes na coluna.
b) SUM (<nome-campo>)
Objetivo: Retorna a soma dos valores existentes no campo especificado. Quando a opção DISTINCT é utilizada são
consideradas apenas os diferentes valores existentes no campo.
c) AVG (<nome-campo>)
Objetivo: Retorna a média dos valores existentes no campo especificado. Quando a opção DISTINCT é utilizada são
consideradas apenas os diferentes valores existentes no campo.
d) MAX (<nome-campo>)
Objetivo: Retorna o maior valor existente no campo especificado.
e) MIN (<nome-campo>)
Objetivo: Retorna o menor valor existente no campo especificado.
f) UPPER (<nome-campo>)
Objetivo: A função Upper converte caracteres minúsculos em maiúsculos. Contudo, não funciona para determinados
conjuntos de caracteres, como é o caso dos símbolos da língua portuguesa.
Página.: 15
Solutech Informática.
Veja a Tabela CLIENTE Abaixo:
COD
001
002
003
004
005
006
NOME
FABIANA
SUELI
ALESSANDRA
WAGNER
RIVA
REGINA
ENDERECO
RUA SERGIPE, 233
RUA DA BAHIA, 1234
AV. AFONSO PENA, 600
AV. PAULISTA, 200
RUA MARIANA, 428
AV. CRISTOVAO COLOMBO, 4000
TELEFONE
222 5534
339 1100
465 2211
211 1213
550 1133
600 2299
CIDADE
BELO HORIZONTE
BELO HORIZONTE
BELO HORIZONTE
SÃO PAULO
FORTALEZA
PORTO ALEGRE
TELEFONE
222 5534
339 1100
465 2211
211 1213
550 1133
600 2299
CIDADE
BELO HORIZONTE
BELO HORIZONTE
BELO HORIZONTE
SÃO PAULO
FORTALEZA
PORTO ALEGRE
TELEFONE
222 5534
339 1100
465 2211
CIDADE
BELO HORIZONTE
BELO HORIZONTE
BELO HORIZONTE
Usando o comando Select:
O comando Select, seleciona os dados especificados da tabela.
Quando você usa *, selecionará todos os campos da tabela.
Você pode selecionar campos determinados também.
Select * from Cliente;
Resultado:
COD NOME
001 FABIANA
002 SUELI
003 ALESSANDRA
004 WAGNER
005 RIVA
006 REGINA
ENDERECO
RUA SERGIPE, 233
RUA DA BAHIA, 1234
AV. AFONSO PENA, 600
AV. PAULISTA, 200
RUA MARIANA, 428
AV. CRISTOVAOCOLOMBO, 4000
Selecionando alguns itens:
Select nome, endereço
from Cliente;
Resultado:
NOME
FABIANA
SUELI
ALESSANDRA
WAGNER
RIVA
REGINA
ENDERECO
RUA SERGIPE, 233
RUA DA BAHIA, 1234
AV. AFONSO PENA, 600
AV. PAULISTA, 200
RUA MARIANA, 428
AV. CRISTOVAO COLOMBO, 4000
Usando o comando Where:
O comando Where, coloca condições na sua Query.
Select * from Cliente
where cidade = “BELO HORIZONTE”;
Resultado:
COD NOME
001 FABIANA
002 SUELI
003 ALESSANDRA
ENDERECO
RUA SERGIPE, 233
RUA DA BAHIA, 1234
AV. AFONSO PENA, 600
Página.: 16
Solutech Informática.
Usando o comando Distinct:
O comando Distinct, elimina as linhas duplicadas da sua query:
Select distinct(cidade)
from Cliente;
Resultado:
CIDADE
---------------------------------------BELO HORIZONTE
SÃO PAULO
FORTALEZA
PORTO ALEGRE
Observe que, embora nós temos 3 pessoas de Belo Horizonte, a query mostrou somente uma vez o nome
da cidade.
Usando o comando Order by:
O comando Order by, ordena a sua tabela:
Select * from Cliente
Order by nome;
Resultado:
COD NOME
003 ALESSANDRA
001 FABIANA
006 REGINA
005 RIVA
002 SUELI
004 WAGNER
ENDERECO
AV. AFONSO PENA, 600
RUA SERGIPE, 233
AV. CRISTOVAO COLOMBO, 4000
RUA MARIANA, 428
RUA DA BAHIA, 1234
AV. PAULISTA, 200
TELEFONE
465 2211
222 5534
600 2299
550 1133
339 1100
211 1213
CIDADE
BELO HORIZONTE
BELO HORIZONTE
PORTO ALEGRE
FORTALEZA
BELO HORIZONTE
SAO PAULO
TELEFONE
465 2211
222 5534
339 1100
CIDADE
BELO HORIZONTE
BELO HORIZONTE
BELO HORIZONTE
Nós podemos incluir uma condição em nosso select:
Select * from Cliente
where cidade = "BELO HORIZONTE"
order by nome;
Resultado:
COD NOME
003 ALESSANDRA
001 FABIANA
002 SUELI
ENDERECO
AV. AFONSO PENA, 600
RUA SERGIPE, 233
RUA DA BAHIA, 1234
Viu? A query selecionou em ordem alfabética, somente os registros com cidade = “BELO HORIZONTE”.
Você pode também fazer a query em ordem descendente:
Select * from Cliente
where cidade = "BELO HORIZONTE"
order by nome desc;
Resultado:
COD NOME
002 SUELI
001 FABIANA
003 ALESSANDRA
ENDERECO
RUA DA BAHIA, 1234
RUA SERGIPE, 233
AV. AFONSO PENA, 600
Página.: 17
TELEFONE
339 1100
222 5534
465 2211
CIDADE
BELO HORIZONTE
BELO HORIZONTE
BELO HORIZONTE
Solutech Informática.
Usando o comando Group by:
O comando Group by, agrupa os seus dados:
Select CIDADE, COUNT(*)
from Cliente
GROUP by CIDADE;
Resultado:
CIDADE
BELO HORIZONTE
FORTALEZA
PORTO ALEGRE
SAO PAULO
COUNT(*)
3
1
1
1
A query informou o número de pessoas por cidade e em ordem alfabética.
Veja a Tabela Saldo do Cliente (SaldoCliente) abaixo:
CODIGO
001
002
003
004
005
006
VALOR
10
20
15
10
25
10
Usando a função Count():
A função Count(), conta todos os registros de uma tabela de acordo com a condição:
Select Count(*)
from SaldoCliente;
Resultado:
Count(*)
---------------------------6
A query informou a quantidade de registros existentes na tabela de SaldoCliente.
Usando a função Sum():
A função Sum(), soma uma coluna da sua tabela:
Select SUM(VALOR) from SaldoCliente;
Resultado:
sum(valor)
---------------------------90
A query informou a soma da coluna valor.
Página.: 18
Solutech Informática.
Usando a função Max():
A função Max(), seleciona o maior valor de uma coluna:
Select MAX(VALOR)
from SaldoCliente;
Resultado:
max(valor)
---------------------------25
A query informou o valor máximo da coluna valor.
Usando a função Min():
A função Min(), seleciona o menor valor de uma coluna:
Select MIN(VALOR)
from SaldoCliente;
Resultado:
min(valor)
---------------------------10
A query informou o valor mínimo da coluna valor.
Usando a função Avg():
A função Avg, seleciona a média de uma coluna:
Select AVG(VALOR)
from SaldoCliente;
Resultado:
avg(valor)
---------------------------15
A query informou a média da coluna valor.
Página.: 19
Solutech Informática.
Veja as Tabelas abaixo:
CLIENTE :
CODIGO
001
002
003
004
005
006
NOME
FABIANA
SUELI
ALESSANDRA
WAGNER
RIVA
REGINA
END_CLI:
CODIGO
001
002
003
004
005
006
ENDERECO
RUA SERGIPE, 233
RUA DA BAHIA, 1234
AV. AFONSO PENA, 600
AV. PAULISTA, 200
RUA MARIANA, 428
AV. CRISTOVAO COLOMBO, 4000
TELEFONE
222 5534
339 1100
465 2211
211 1213
550 1133
600 2299
CIDADE
BELO HORIZONTE
BELO HORIZONTE
BELO HORIZONTE
SAO PAULO
FORTALEZA
PORTO ALEGRE
SALDOCLIENTE:
CODIGO
VALOR
001
10
002
20
003
15
004
10
005
25
006
10
Usando Join:
Um join é usado quando uma query necessita de dados de mais de uma tabela.
O resultado é um subconjunto cartesiano das tabelas. As tabelas acima, estão relacionadas pelo código.
a) Vamos listar o Nome de cada Cliente com seu respectivo Saldo:
Select cliente.nome, saldocliente.valor
from cliente, saldocliente
where cliente.codigo = saldocliente.codigo;
Resultado:
NOME
VALOR
FABIANA
10
SUELI
20
ALESSANDRA
15
WAGNER
10
RIVA
25
REGINA
10
Lembre-se que você deve usar sempre a cláusula where em um join.
Na query anterior, você poderia colocar apelidos nas tabelas:
Select c.nome, s.valor
from cliente c, saldocliente s
where c.codigo = s.codigo;
Página.: 20
Solutech Informática.
O resultado é o mesmo.
b) Vamos selecionar o nome, endereco e saldo das pessoas:
Select cliente.nome, saldocliente.valor, end_cli.endereco, end_cli.cidade
from cliente, saldocliente, end_cli
where cliente.codigo = saldocliente.codigo and
cliente.codigo = end_cli.codigo;
Resultado:
NOME
VALOR
FABIANA
10
SUELI
20
ALESSANDRA
15
WAGNER
10
RIVA
25
REGINA
10
ENDERECO
RUA SERGIPE, 233
RUA DA BAHIA, 1234
AV. AFONSO PENA, 600
AV. PAULISTA, 200
RUA MARIANA, 428
AV. CRISTOVAO COLOMBO, 4000
Usando o comando Union
Union é a combinação de mais de um select para obter um resultado desejado.
A quantidade de colunas dever ser a mesma em todos os select's.
Vamos fazer uma correção nos saldos, de acordo com a cidade:
Select c.nome, (s.saldo*1.10), e.endereco, e.cidade
from cliente c, saldocliente s, end_cli e
where c.codido = s.codigo
and c.codigo = e.codigo;
and e.cidade = 'BELO HORIZONTE'
union
select c.nome, (s.saldo*1.05), e.endereco, e.cidade
from cliente c, saldocliente s, end_cli e
where c.codido = s.codigo
and c.codigo = e.codigo;
and e.cidade = 'SAO PAULO';
E o resultado será:
NOME
SALDO
FABIANA
11
SUELI
22
ALESSANDRA 16.5
WAGNER
10.5
ENDERECO
RUA SERGIPE, 233
RUA DA BAHIA, 1234
AV. AFONSO PENA, 600
AV. PAULISTA, 200
Veja a Tabela CONTA_CORRENTE abaixo:
CODIGO
10
20
30
40
50
60
NOME
VANESSA
NILSON
RACHEL
CALVIN
RAINER
CHRISTIAN
SALDO
100,00
100,00
150,00
200,00
200,00
150,00
Página.: 21
CIDADE
BELO HORIZONTE
BELO HORIZONTE
BELO HORIZONTE
SAO PAULO
CIDADE
BELO HORIZONTE
BELO HORIZONTE
BELO HORIZONTE
SAO PAULO
FORTALEZA
PORTO ALEGRE
Solutech Informática.
Usando Aliases (Apelidos)
Um alias e criado quando usamos um calculo em uma query ou selecionamos um valor mínimo ou máximo
de uma coluna, ou mesmo concatenamos colunas.
Vamos calcular 1/4 do saldo da tabela acima:
Select nome, saldo/4 "CORRECAO"
from conta_corrente;
E o resultado sera:
NOME
VANESSA
NILSON
RACHEL
CALVIN
RAINER
CHRISTIAN
CORRECAO
25,00
25,00
37,50
50,00
50,00
37,50
O nome da coluna "CORRECAO" foi criado com um alias, caso nos não tivéssemos colocado este alias, a
coluna sairia com o nome "SALDO/4":
Select nome,saldo/4
from conta_corrente;
E o resultado será:
NOME
VANESSA
NILSON
RACHEL
CALVIN
RAINER
CHRISTIAN
SALDO/4
25,00
25,00
37,50
50,00
50,00
37,50
Usando Operadores de Concatenação
Usamos operadores de concatenação, quando queremos criar uma coluna a partir de 2 ou mais colunas de
uma query.
Vamos apresentar um resultado de uma query:
Select codigo|| ' - ' ||nome, saldo
from conta_corrente;
E o resultado será:
CODIGO||' - '||NOME
10 – VANESSA
20 – NISON
30 – RACHEL
40 – CALVIN
50 – RAINER
60 – CHRISTIAN
SALDO
100,00
100,00
150,00
200,00
200,00
150,00
Podemos também criar um alias para a coluna de concatenação:
Página.: 22
Solutech Informática.
Select codigo||' - '||nome "CORRENTISTA", saldo
from conta_corrente;
E o resultado será:
CORRENTISTA
10 – VENESSA
20 – NILSON
30 – RACHEL
40 – CALVIN
50 – RAINER
60 – CHRSITIAN
SALDO
100,00
100,00
150,00
200,00
200,00
150,00
Veja a Tabela CONTA_CORRENTE abaixo:
CODIGO
10
20
30
40
50
60
NOME
VANESSA
NILSON
RACHEL
CALVIN
RAINER
CHRISTIAN
SALDO
100,00
100,00
150,00
200,00
Tratamento de Valores Nulos:
Se determinada coluna não tem um valor, este valor e denominado nulo. Quando uma operação aritmética
e feita com um valor nulo, o resultado e sempre nulo.
Select * from conta_corrente
Where Saldo is Null;
E o resultado será:
CODIGO
40
60
NOME
CALVIN
CHRISTIAN
SALDO
Select * from conta_corrente
Where Saldo is not Null;
E o resultado será:
CODIGO
10
20
30
50
NOME
VANESSA
NILSON
RACHEL
RAINER
SALDO
100,00
100,00
150,00
200,00
Página.: 23
Solutech Informática.
Veja a Tabela CONTA_CORRENTE abaixo:
CODIGO
10
20
30
40
50
60
NOME
VANESSA
NILSON
RACHEL
CALVIN
RAINER
CHRISTIAN
SALDO
100,00
100,00
150,00
200,00
200,00
120,00
Operador Between:
O Operador between, seleciona dados que estão entre uma determinada condição.
Vejamos a seleção abaixo:
Select codigo, nome, saldo
from conta_corrente
where saldo between 50 and 150;
E o resultado será:
CODIGO
10
20
30
60
NOME
VANESSA
NILSON
RACHEL
CHRISTIAN
SALDO
100,00
100,00
150,00
120,00
Na utilização do between o menor valor da comparação deve vir antes.
Veja a Tabela CADASTRO abaixo:
CODIGO
10
20
30
40
50
60
NOME
VANESSA
NILSON
RACHEL
CALVIN
RAINER
CHRISTIAN
ESTADO
MG
MG
MG
RS
SP
SP
CIDADE
BELO HORIZONTE
OURO PRETO
BELO HORIZONTE
PORTO ALEGRE
SAO PAULO
SAO PAULO
Operador IN:
O Operador IN pode ser um resultado de uma subquery ou resultado de valores determinados:
Usando uma subquery:
Select codigo, nome, estado, cidade
from cadastro
where cidade in (select cidade from cadastro where estado = 'MG');
E o resultado será:
CODIGO
NOME
10
VANESSA
20
NILSON
30
RACHEL
ESTADO
MG
MG
MG
CIDADE
BELO HORIZONTE
OURO PRETO
BELO HORIZONTE
Página.: 24
Solutech Informática.
Definindo valores:
Select codigo, nome, estado, cidade
from cadastro
where estado in ('MG','RS');
E o resultado será:
CODIGO
NOME
10
VANESSA
20
NILSON
30
RACHEL
40
CALVIN
ESTADO
MG
MG
MG
RS
CIDADE
BELO HORIZONTE
OURO PRETO
BELO HORIZONTE
PORTO ALEGRE
1.2 - INSERT
Objetivo: Incluir um novo registro em uma tabela do Banco de Dados.
Sintaxe:
INSERT INTO <nome-tabela> [(<nome-coluna>, [<nome-coluna>], [...])]
VALUES (<relação dos valores a serem incluídos>)
onde:
a) nome-tabela - Representa o nome da tabela onde será incluída o registro.
b) nome-coluna - Representa o nome da(s) coluna(s) que terão conteúdo no momento da operação de inclusão.
Obs.: Este comando pode ser executado de duas maneiras:
1) Quando todos os campos da tabela terão conteúdo - Neste caso não é necessário especificar as colunas, entretanto a
relação dos valores a serem incluídos deverão obedecer a mesma seqüência da definição da tabela.
2) Quando apenas parte dos campos da tabela terão conteúdo - Neste caso devem ser especificadas todas as colunas que
terão conteúdo e os valores relacionados deverão obedecer esta seqüência. Para os campos que não tem conteúdo
especificado será preenchido o valor NULL.
Insert into Cliente
values (007, “CARLOS”, ”RUA ICARAI, 890”, ” 339 1212”, ”BELO HORIZONTE”);
Você verá o resultado, fazendo um select:
Select * from Cliente;
COD
001
002
003
004
005
006
007
NOME
FABIANA
SUELI
ALESSANDRA
WAGNER
RIVA
REGINA
CARLOS
ENDERECO
RUA SERGIPE, 233
RUA DA BAHIA, 1234
AV. AFONSO PENA, 600
AV. PAULISTA, 200
RUA MARIANA, 428
AV. CRISTOVAO COLOMBO, 4000
AV. ICARAI, 890
Página.: 25
TELEFONE
222 5534
339 1100
465 2211
211 1213
550 1133
600 2299
339 1212
CIDADE
BELO HORIZONTE
BELO HORIZONTE
BELO HORIZONTE
SAO PAULO
FORTALEZA
PORTO ALEGRE
BELO HORIZONTE
Solutech Informática.
1.3 - UPDATE
Objetivo: Atualiza os dados de um ou um grupo de registros em uma tabela do Banco de Dados.
Sintaxe:
UPDATE <nome-tabela>
SET <nome-coluna> = <novo conteúdo para o campo>
[<nome-coluna> = <novo conteúdo para o campo>]
WHERE <condição>
onde:
a) nome-tabela - Representa o nome da tabela cujo conteúdo será alterado.
b) nome-coluna - Representa o nome da(s) coluna(s) terão seus conteúdos alterados com o novo valor especificado.
c) condição - Representa a condição para a seleção dos registros que serão atualizados. Este seleção poderá resultar em
um ou vários registros. Neste caso a alteração irá ocorrer em todos os registros selecionados.
Update Cliente
set nome = “JOSE”;
Faça um select, e você obterá o seguinte resultado:
COD
001
002
003
004
005
006
007
NOME
JOSE
JOSE
JOSE
JOSE
JOSE
JOSE
JOSE
ENDERECO
RUA SERGIPE, 233
RUA DA BAHIA, 1234
AV. AFONSO PENA, 600
AV. PAULISTA, 200
RUA MARIANA, 428
AV. CRISTOVAO COLOMBO, 4000
AV. ICARAI, 890
TELEFONE
222 5534
339 1100
465 2211
211 1213
550 1133
600 2299
339 1212
CIDADE
BELO HORIZONTE
BELO HORIZONTE
BELO HORIZONTE
SAO PAULO
FORTALEZA
PORTO ALEGRE
BELO HORIZONTE
Observe que se você não define o registro que deseja alterar, a query mudará todos os nomes para JOSE.
Para você alterar somente um registro, você deverá incluir uma condição:
Update Cliente
set nome = "JOSE" where nome = "RIVA";
Faça um select, e você obterá o seguinte resultado:
COD
001
002
003
004
005
006
007
NOME
FABIANA
SUELI
ALESSANDRA
WAGNER
JOSE
REGINA
CARLOS
ENDERECO
RUA SERGIPE, 233
RUA DA BAHIA, 1234
AV. AFONSO PENA, 600
AV. PAULISTA, 200
RUA MARIANA, 428
AV. CRISTOVAO COLOMBO, 4000
AV. ICARAI, 890
Página.: 26
TELEFONE
222 5534
339 1100
465 2211
211 1213
550 1133
600 2299
339 1212
CIDADE
BELO HORIZONTE
BELO HORIZONTE
BELO HORIZONTE
SAO PAULO
FORTALEZA
PORTO ALEGRE
BELO HORIZONTE
Solutech Informática.
1.4 – DELETE:
Objetivo: Deletar um ou um grupo de registros em uma tabela do Banco de Dados.
Sintaxe:
DELETE FROM <nome-tabela>
WHERE <condição>
onde:
a) nome-tabela - Representa o nome da tabela cujos registros serão deletados.
b) condição - Representa a condição para a deleção dos registros. Este seleção poderá resultar em um ou vários
registros. Neste caso a operação irá ocorrer em todos os registros selecionados.
Delete from Cliente;
Se você fizer um select, verá que a sua tabela está vazia.
Caso você queira excluir um registro específico, da mesma forma que o comando update, você deverá
incluir uma condição:
Delete from Cliente
where nome = ‘FABIANA’;
Faca um select, e você obterá o seguinte resultado:
COD
002
003
004
005
006
007
NOME
SUELI
ALESSANDRA
WAGNER
JOSE
REGINA
CARLOS
ENDERECO
RUA DA BAHIA, 1234
AV. AFONSO PENA, 600
AV. PAULISTA, 200
RUA MARIANA, 428
AV. CRISTOVAO COLOMBO, 4000
AV. ICARAI, 890
Página.: 27
TELEFONE
339 1100
465 2211
211 1213
550 1133
600 2299
339 1212
CIDADE
BELO HORIZONTE
BELO HORIZONTE
SAO PAULO
FORTALEZA
PORTO ALEGRE
BELO HORIZONTE
Solutech Informática.
2 - Comandos DDL – Linguagem de Definição de Dados.
2.1 – CREATE TABLE:
Objetivo: Criar a estrutura de uma tabela(arquivo) definido as colunas (campos) e as chaves primárias e estrangeiras
existentes.
Sintaxe:
CREATE TABLE <nome-tabela>
(<nome-coluna> , <tipo-do-dado> [NOT NULL]
PRIMARY KEY (nome-coluna-chave)
FOREIGN KEY (nome-coluna-chave-estrangeira) REFERENCES (nome-tabela-pai)
ON DELETE [RESTRICT]
[CASCADE]
onde:
a) nome-tabela - Representa o nome da tabela que será criada.
b) nome-coluna - Representa o nome da coluna que será criada. A definição das colunas de uma tabela é feita
relacionando-as uma após a outra.
c) tipo-do-dado - Cláusula que define o tipo e tamanho dos campos definidos para a tabela. Os tipos de dados mais
comuns serão definidos mais à frente.
d) NOT NULL - Exige o preenchimento do campo, ou seja, no momento da inclusão é obrigatório que possua um
conteúdo.
e) PRIMARY KEY (nome-coluna-chave) - Definir para o banco de dados a coluna que será a chave primária da tabela.
Caso ela tenha mais de um coluna como chave, elas deverão ser relacionadas entre os parênteses.
f) FOREIGN KEY (nome-coluna-chave-estrangeira) REFERENCES (nome-tabela-pai) - Definir para o banco de dados
as colunas que são chaves estrangeiras, ou seja, os campos que são chaves primárias de outras tabelas. Na opção
REFERENCES deve ser especificado a tabela na qual a coluna é a chave primária.
g) ON DELETE - Esta opção especifica os procedimentos que devem ser feitos pelo SGBD quando houver uma
exclusão de um registro na tabela pai quando existe um registro correspondente nas tabelas filhas. As opções
disponíveis são:
g.1) RESTRICT - Opção default. Esta opção não permite a exclusão na tabela pai de um registro cuja chave primária
exista em alguma tabela filha.
g.2) CASCADE - Esta opção realiza a exclusão em todas as tabelas filhas que possua o valor da chave que será
excluída na tabela pai.
Tipos de dados mais comuns:
1) Numéricos:
- Smallint - Armazena valores numéricos, em dois bytes binários, compreendidos entre o intervalo -32768 a +32767.
- Integer - Armazena valores numéricos, em quatro bytes binários, compreendidos entre o intervalo -2147483648 a
+2147483647
- Decimal(n,m) - Armazena valores numéricos com no máximo 15 dígitos. Nesta opção deve ser definida a quantidade
de dígitos inteiros (n) e casas decimais (m) existentes no campo.
- Double Precision – Utilizado para representar valores de ponto flutuante com precisão de 15 digitos. Tem tamabho de
64 bits e consegue armazenar valores no seguinte intervalo:
2.225 x 10^-308 a 1.797 x 10^308 ( ^ : Significa “Elevado a”)
- Float - Armazena valores de ponto flutuante com precisão de 7 dígitos. Ocupa 32 bits e armazena valores no
intervalo:
1.175 x 10 ^-38 a 3.402 x 10^38
2) Alfanuméricos:
- Varchar (n) / Char(n) - Utilizado para armazenar caracteres de 1 a 32767 bytes.
3) Data e Hora:
- Date - Definir um campo que irá armazenar datas.
- Time - Definir um campo que irá armazenar de horas.
Página.: 28
Solutech Informática.
2.2 – ALTER TABLE:
Objetivo: Alterar a estrutura de uma tabela(arquivo) acrescentando, retirando e alterando nomes, formatos das colunas
e a integridade referencial definidas em uma determinada tabela.
Sintaxe:
ALTER TABLE <nome-tabela>
DROP <nome-coluna>
ADD <nome-coluna> <tipo-do-dado> [NOT NULL]
RENAME <nome-coluna> <novo-nome-coluna>
RENAME TABLE <novo-nome-tabela>
MODIFY <nome-coluna> <tipo-do-dado> [NOT NULL]
ADD PRIMARY KEY <nome-coluna>
DROP PRIMARY KEY <nome-coluna>
ADD FOREIGN KEY (nome-coluna-chave-estrangeira) REFERENCES (nome-tabela-pai)
ON DELETE [RESTRICT]
[CASCADE]
DROP FOREIGN KEY (nome-coluna-chave-estrangeira) REFERENCES (nome-tabela-pai)
onde:
a) nome-tabela - Representa o nome da tabela que será atualizada.
b) nome-coluna - Representa o nome da coluna que será criada.
c) tipo-do-dado - Cláusula que define o tipo e tamanho dos campos definidos para a tabela.
d) DROP <nome-coluna> - Realiza a retirada da coluna especificada na estrutura da tabela.
e) ADD <nome-coluna> <tipo-do-dado> - Realiza a inclusão da coluna especificada na estrutura da tabela. Na coluna
correspondente a este campo nos registros já existentes será preenchido o valor NULL (Nulo). A definição NOT NULL
é semelhantes à do comando CREATE TABLE.
f) RENAME <nome-coluna> <novo-nome-coluna> - Realiza a troca do nome da coluna especificada.
g) RENAME TABLE <novo-nome-tabela> - Realiza a troca do nome da tabela especificada.
h) MODIFY <nome-coluna> <tipo-do-dado> - Permite a alteração na característica da coluna especificada.
Opções:
Além das existentes na opção ADD (NOT NULL e NOT NULL WITH DEFAULT), temos a opção NULL que altera a
característica do campo passando a permitir o preenchimento com o valor Nulo.
i) ADD PRIMARY KEY <nome-coluna> - Esta opção é utilizada quando é acrescido um novo campo como chave
primária da tabela.
j) DROP PRIMARY KEY <nome-coluna> - Esta opção é utilizada quando é retirado um campo como chave primária
da tabela.
l) ADD FOREIGN KEY <nome-coluna> - Esta opção é utilizada quando é acrescido um novo campo sendo ele uma
chave estrangeira.
l) DROP FOREIGN KEY <nome-coluna> - Esta opção é utilizada quando é retirado uma chave estrangeira da estrutura
da tabela.
2.3 – DROP TABLE:
Objetivo: Deletar a estrutura e os dados existentes em uma tabela. Após a execução deste comando estarão deletados
todos dados, estrutura e índices de acessos que estejam a ela associados.
Sintaxe:
DROP TABLE <nome-tabela>
onde:
a) nome-tabela - Representa o nome da tabela que será deletada.
Página.: 29
Solutech Informática.
Banco de Dados InterBase v.6.0:
Página.: 30
Solutech Informática.
INTRODUÇÃO:
O Interbase é um sistema gerenciador de bancos de dados (SGBD). Mais especificamente, é um sistema
gerenciador de bancos de dados relacionais (SGBDR). Essa afirmação nos diz que ele incorpora conceitos subjacentes
ao modelo de dados relacional. Os sistemas de gerenciamento de bancos de dados foram concebidos para gerenciar
grandes quantidades de informação e consistem de uma coleção de dados inter-relacionados e de um conjunto de
programas adaptados para manipular esses dados.
Naturalmente, o armazenamento e recuperação de dados antecedem à existência dos sistemas gerenciadores de
bancos de dados. Entretanto, os sistemas convencionais de armazenamento e recuperação de dados, que podemos
definir como "sistemas de processamento de arquivos" apresentam um conjunto de vantagens que vão, desde
redundância e inconsistência de dados a problemas de segurança. Os sistemas gerenciadores de bancos de dados têm a
capacidade de fornecer aos usuários uma visão abstrata dos dados: ou seja, são capazes de esconder como os dados são
armazenados ou mantidos. Além dessa capacidade de abstração, os sistemas gerenciadores de banco de dados permitem
que os dados sejam recuperados de forma rápida e eficiente.
Modelo de Dados:
Para descrever a estrutura de um banco de dados, surgiram os modelos de dados, ferramentas conceituais
destinadas a descrever os dados, as suas restrições e os relacionamentos existentes entre os dados. Diversos modelos de
dados surgiram ao longo do tempo. Entre os modelos baseados em objetos, o Modelo Entidade-Relacionamento (MER)
é, sem dúvida, o mais importante e o mais utilizado. Essencialmente, o Modelo Entidade-Relacionamento enxerga o
mundo real como uma coleção de objetos básicos (entidades) e dos relacionamentos (relações) existentes entre esses
objetivos. Mais adiante, teremos a oportunidade de lidar com um Modelo Entidade-Relacionamento no qual
basearemos os nossos exercícios práticos.
Entendendo o Termo Relacional:
Todos os dados de um banco relacional são armazenados e exibidos em forma de tabelas, que são estruturas
definidas por linhas (entidades) e colunas (atributos). Há uma correspondência direta entre o conceito de tabela e o
conceito matemático de relação.
Uma linha deve distinguir-se das outras através de uma característica que a identifique de forma unívoca. Às
vezes, torna-se necessário utilizar mais de uma característica parra individualizar uma linha. A característica (coluna)
que identifica a linha recebe o nome de atributo identificador ou chave primária. Assim, uma chave primária pode
referir-se a uma coluna ou a um grupo de colunas.
Os sistemas relacionais têm a capacidade de selecionar dados de determinadas colunas, de algumas linhas e de
diversas tabelas ao mesmo tempo. Além disso, os dados obtidos de uma tabela formam uma nova tabela, de tal sorte
que essa nova tabela pode ser manipulada de forma idêntica à tabela original.
Embora as tabelas sejam os objetos essenciais dos bancos de dados, existem outros objetos igualmente
importantes como: índices, visões, sinônimos, usuários, procedimentos, funções etc.
Open Source:
O Interbase 6 é agora um produto de código-fonte aberto e pode ser utilizado gratuitamente, de acordo com as
regras estabelecidas na INTERBASE PUBLIC LICENSE. Você pode obter o Interbase 6 no site da Interbase Software
Corporation (ISC).
www.interbase.com
Página.: 31
Solutech Informática.
Plataformas:
O Interbase pode rolar em diferente plataformas, incluindo Windows 95/98/NT.
Requerimento Para Windows:
Para atuar convenientemente no ambiente Microsoft, o Interbase estabelece os seguintes requerimentos:
- Sistemas Operacionais: Windows NT 4.0 com Service Pack 5, Windows 95 com Service Pack 1 ou Windows 98;
- Memória: Mínimo de 16 megabytes, recomendando-se 64 para servidores;
- Processadores: 486 DX2 com 66 MHz no mínimo; Pentium 100 MHz, ou superior, para servidores multiclientes;
- Compiladores: Microsoft Visual C++ 4.2 ou Borland C++ Builder 3.0.
Sobre os Recursos:
Suporte SQL:
O InterBase enquadra-se nos requerimentos do padrão SQL-92, suportando integridade referencial declarativa
com operações em cascata, atualização de visões e junções externas. Servidores InterBase disponibilizam bibliotecas
que suportam o desenvolvimento de aplicações com instruções SQL embutidas e aplicações com instruções SQL
dinâmicas (DSQL).
Acesso Multiusuário:
O InterBase permite que várias aplicações-clientes tenham acesso simultâneo a um mesmo banco de dados. As
aplicações também podem ter acesso a vários bancos de dados ao mesmo tempo. Gatilhos podem ser disparados para
notificar as aplicações-cliente sobre eventos ocorridos no banco de dados como inclusão e exclusão de dados.
Gerenciamento de Transações:
O servidor InterBase está estruturado em um modelo de transação. As transações permitem que instruções
enviadas ao banco de dados ocorram em blocos com a característica especial de "tudo ou nada". Significa dizer que um
conjunto de instruções pode ser tratado como uma única instrução, com possibilidade de ser executada completamente
(sucesso) ou de não ser executada (insucesso).
O gerenciamento de transações do InterBase habilita aplicações-clientes a iniciarem múltiplas transações
simultâneas. Há um completo e explícito controle sobre as transações apresentadas ao servidor. As transações podem
ser isoladas de modo a não serem afetadas por alterações realizadas por transações concorrentes.
Arquitetura MultiGeracional:
O InterBase oferece uma arquitetura multigeracional, ou seja, o servidor armazena múltiplas versões de cada
registro, quando necessário, de forma que as transações sempre tenham uma visão consistente dos dados.
Criando novas versões de um registro, o InterBase permite que todos os clientes (usuários) possam ler aquele
registro, mesmo que um outro usuário o esteja atualizando naquele exato momento.
Página.: 32
Solutech Informática.
Nível de Bloqueio:
Sistemas multiusuários estão sujeitos ao que chamamos de concorrência. Eventualmente, vários usuários
tentarão atualizar as mesmas informações ao mesmo tempo. Comumente, duas estratégias são utilizadas para lidar com
o acesso concorrente aos mesmos dados. Uma delas, denominada de bloqueio pessimista é utilizada por bancos locais,
como o Paradox, por exemplo; a outra, implementada pela maioria dos servidores SQL, é o que chamamos de bloqueio
otimista.
Dialetos:
Se você conhece versões anteriores do InterBase, é importante anotar que o InterBase 6 introduz o conceito de
dialetos. Esse dialetos permitem que você utilize as novas características do banco ou permaneça com as características
antigas, por questões de compatibilidade.
O Dialeto (Dialect 1) garante compatibilidade com bases de dados criadas com versões anteriores. O Dialeto
(Dialect 2) é apenas um modo de diagnóstico e provavelmente você não vai precisar utilizá-lo. Por fim, O dialeto 3
(Dialect 3) permite acesso total às novas características do banco.
O InterBase Server:
Como o nome sugere, o InterBase Server é o servidor InterBase.
O InterBase Guardian:
Como usuário, dificilmente você terá que interagir diretamente com o InterBase Guardian. Você pode
enxergá-lo como um "processo invisível", cuja finalidade é tentar manter ativo o InterBase Server quando este é
submetido a interrupções anormais em seu funcionamento.
No Windows NT, algumas características estão disponíveis para o InterBase Guardian:
Pode ser iniciado automaticamente, como um serviço (padrão);
Pode ser iniciado manualmente, como um serviço;
Pode ser desabilitado e, nesse caso, o servidor pode rodar como um serviço (ou aplicação), iniciando automática ou
manualmente.
No Windows 95/98, as características disponíveis são semelhantes:
Pode ser iniciado automaticamente, como uma aplicação (padrão);
Pode ser iniciado manualmente, como uma aplicação;
Pode ser desabilitado e, nesse caso, o servidor pode rodar de forma independente como uma aplicação, iniciada
automática ou manualmente.
Página.: 33
Solutech Informática.
A Ferramenta IBConsole:
A IBConsole, prioritariamente, nos habilita a:
Administrar a segurança do servidor;
Fazer backup de bancos de dados e restaurar backups;
Analisar estatísticas sobre servidores e bancos de dados;
Verificar a integridade dos bancos de dados.
Há cinco elementos na composição da janela da IBConsole: uma barra de menus e uma barra de ferramentas
(na parte superior), uma janela de visualização (em árvore) dos servidores, bancos de dados (à esquerda), uma janela
de trabalho (à direita) e uma barra de estado (na parte inferior).
A interface da IBConsole não oferece dificuldades de compreensão. Vamos fazer uma síntese das opções existentes na
barra de menus.
Menu Console
Permite sair da ferramenta.
Menu View
Permite configurar a forma de visualização dos dados.
Menu Server
Permite registrar (desabilitar) servidores, estabelecer conexões com servidores, diagnosticar conexões, gerenciar
usuários, adicionar e remover certificados e visualizar arquivos de log e as propriedades dos servidores.
Menu Database
Permite registrar (desabilitar) bancos de dados, estabelecer conexões com bancos de dados, criar e destruir bancos de
dados, realizar tarefas de manutenção(cópias de segurança, restauração etc.), visualizar e recuperar metadados,
visualizar usuários conectados e as propriedades dos bancos de dados.
Menu Tools
Permite configurar ferramentas de trabalho e executar a ferramenta Interactive SQL.
Menu Windows
Permite visualizar as janelas abertas pela IBConsole e a alternância entre elas.
Menu Help
Permite acesso a arquivos de ajuda da IBConsole e do servidor InterBase.
Ao comentarmos as opções do menu Tools, citamos a ferramenta Interactive SQL. Essa ferramenta terá uma
importância fundamental no decorrer desta apostila, posto que será utilizada exaustivamente para digitarmos os
comandos Sql para criação e manutenção das tabelas.
Você tem uma visão da interface Interactive SQL. Há cinco áreas vistas de cima para baixo: 1- uma barra de menus, 2uma barra de ferramentas, 3- área de comandos, 4- uma área de resultados e 5- uma barra de estado.
A área de comandos torna-se a mais importante, uma vez que nela podemos escrever instruções SQL, digitando
diretamente, ou submeter roteiros (scripts) com instruções SQL.
Página.: 34
Solutech Informática.
O Administrador do InterBase:
Como toda grande estrutura, o InterBase possui um administrador central: um usuário com poderes ilimitados,
capaz de realizar todas as tarefas disponíveis no banco. A identificação e a senha desse ser supremo é:
SYSDBA (identificação)
masterkey (senha, com letras minúsculas)
Somente o administrador do sistema - ou um usuário a quem foram delegados os mesmos poderes do
administrador - pode realizar todas as operações possíveis no InterBase. Para os propósitos atuais, vamos utilizar a
senha padrão "masterkey", que é de domínio público. Se você utilizar o InterBase em aplicações profissionais,
naturalmente utilizará uma senha mais apropriada.
O Banco de Dados EMPLOYEE.GDB:
Registrar servidores de bancos de dados é primeira atitude a tomar se deseja utilizar a ferramenta Ibconsole.
Como o banco de dados de exemplo EMPLOYEE.GDB será utilizado em nossos exemplos, vamos registrá-lo no
servidor local.
Se você der dois cliques no ícone "Local Server", na área à esquerda, ou na ação "Login", na área à direita,
será convidado a se identificar para o servidor.
Após fornecer a senha do administrador ("masterkey"), acione a opção de menu DataBase Register. Você
deverá fornecer as informações necessárias: o caminho completo do banco e o apelido do banco (EMPLOYEE.GDB).
Quando terminar o registro do banco, você o terá disponível para conexão. A conexão pode ser feita tanto no
lado esquerdo quanto no direito (ação "Connect").
Finalmente, se você fizer a conexão correta com o banco, você pode visualizar todos os seus objetos, e realizar
diversas ações sobre eles. Estamos prontos agora para levar adiante a tarefa de compreender melhor o servidor
InterBase.
No lado direito da IBConsole, você pode localizar a ação View Metadata (Visualizar metadados). Sugerimos
que você execute essa ação e guarde o resultado em um arquivo chamado, por exemplo, employee.sql. Se quiser,
imprima o arquivo. No decorrer dos próximos capítulos, sempre vamos nos referir a informações constantes no
arquivo. Ter o arquivo impresso vai auxiliá-lo a identificar de onde as informações foram extraídas. Entretanto, saiba
que a listagem do arquivo é razoavelmente longa.
CRIANDO BANCOS DE DADOS
Introdução:
Um banco de dados InterBase é uma coleção de objetos que armazenam e manipulam dados. Diferentemente
de bancos convencionais, como o Paradox, por exemplo, um banco de dados InterBase apresenta-se como um único
arquivo. Os objetos neles contidos como: tabelas, índices, visões, domínios etc., não são "visíveis a olho nu". Para
enxergá-los, é necessário utilizar elementos de recuperação e visualização adequados. A ferramenta IBConsole, por
exemplo, permite que tenhamos uma visão geral dos servidores e dos objetos dos bancos de dados InterBase.
Servidor é o local onde os dados são armazenados e disponíveis. Há servidores locais e servidores remotos:
estes últimos são os mais utilizados atualmente, embora servidores locais tenham o seu lugar de destaque na maioria
das corporações. Na verdade, servidores locais e servidores remotos têm uma convivência pacífica, realizando missões
específicas nas empresas.
Página.: 35
Solutech Informática.
Criar um banco de dados não é uma tarefa meramente física. Há toda uma estrutura lógica envolvida no
processo. Um banco de dados deve ser desenhado para guardar de forma coerente e precisa as informações necessárias
aos negócios dos usuários. Os detalhes do negócio precisam estar sintonizados com as definições contidas nos objetivos
do banco. Esses objetos "modelam" a realidade do usuário.
Nomeando Arquivos e Objetos:
Os nomes de bancos de dados no InterBase assumem a extensão gdb. Isso não é uma exigência e você pode utilizar-se
de extensões que lhe pareçam mais oportunas.
Como o servidor InterBase roda em diversas plataformas, uma boa prática seria nomear os bancos de dados
com o modelo padrão "8 ponto 3" (oito caracteres para o nome do arquivo e três caracteres para a extensão), como
propõe o exemplo:
employee.gdb
As regras para nomear os objetos do Interbase são:
Podem ser usados os caracteres A...Z, a...z, 0...9, cifrão ($) e sublinhado (_);
Os nomes devem começar com um caractere alfabético: A ...Z ou a ...z;
Os nomes em geral limitam-se a 31 caracteres. Nomes de restrições limitam-se a 27 caracteres. Nomes de eventos
devem ter no máximo 15 caracteres.
Entendendoa Criação do Banco de Dados EMPLOYEE.GDB
A instrução SQL CREATE DATABASE nos possibilita criar bancos de dados. Com relação ao banco de
dados EMPLOYEE.GDB.
Alterações relativas a banco de dados são realizadas com a instrução ALTER DATABASE. Eliminar banco de
dados, exige a instrução DROP DATABASE.
Tipo de Dados:
Antes que você possa criar um domínio, deve conhecer os tipos de dados que um banco de dados oferece. O
InterBase suporta os tipos de dados que vamos descrever a seguir:
BLOB (BINARY LARGE OBJECT)
De tamanho variável, permite armazenar diversos tipos de dados como: gráficos, textos e vozes digitalizadas.
Uma variável desse tipo de dado é armazenada e recuperada em segmentos limitados a 64K. Quando se define uma
coluna baseada nesse tipo de dado, deve-se indicar o subtipo desejado. Há sete subtipos (de 0 a 6), como mostramos a
seguir. Os subtipos 0 e 1 são os mais utilizados:
Subtipos:
0 - Dados binários ou de tipo indeterminado
1 - Texto
2 - Representação Binária de Linguagem
3 - Lista de Controle de Acesso
4 - Reservado para uso no futuro
5 - Descrição de metadados da tabela corrente
6 - Descrição de transações que terminam de forma irregular
Página.: 36
Solutech Informática.
CHAR(N), CHARACTER(N)
Utilizado para armazenar caracteres de 1 a 32767 bytes. Colunas definidas para esse tipo de dado têm tamanho
fixo definido por n.
DATE
Com tamanho de 64 bits, esse tipo de dado consegue armazenar datas no intervalo:
01/01/100 A . D. a 29/02/32768 A.D.
DECIMAL(PRECISÃO, ESCALA)
Tem tamanho variável (16, 32 ou 64 bits) e armazena números decimais. A precisão vai de 1 a 18 (pelo
menos) e a escala vai de 0 a 18. Uma coluna definida como DECIMAL(10,3), por exemplo, teria o seguinte formato:
PPPPPPP.EEE
DOUBLE PRECISION
Utilizado para representar valores de ponto flutuante com precisão de 15 dígitos. Tem tamanho de 64 bits e
consegue armazenar valores no seguinte intervalo:
2.2225 x 10^-308 a 1.797 x 10^308 (^significa "elevado a")
FLOAT
Representa valores de ponto flutuante com precisão de 7 dígitos. Ocupa 32 bits e armazena valores no
intervalo:
1.175 x 10^-38 a 3.402 x 10^38 (^significa "elevado a")
INTEGER
Implementando com tamanho de 32 bits, representa valores inteiros na faixa:
-2,147,483,648 a 2,147,483,647
NUMERIC(PRECISÃO, ESCALA)
Pode Ter tamanho variável ou16, 32 e 64 bits. Armazena números decimais com precisão entre 1 e 18
(exatamente) e a escala entre 0 e 18. Compare esse tipo de dado com DECIMAL e veja que existe diferença no
conceito de precisão. Uma coluna definida como NUMERIC(10,3), por exemplo, teria seguinte formato:
PPPPPPP.EEE
SMALLINT
Implementando com tamanho de 16 bits, armazena inteiros no intervalo:
-32,768 a 32,767
TIME
Esse tipo de dado tem tamanho de 64 bits, armazena valores de tempo na faixa:
0:00 AM a 23:59:9999 PM
Página.: 37
Solutech Informática.
TIMESTAMP
Inclui informações de data (date) e tempo (time). Com tamanho de 64 bits, guarda valores no intervalo:
01/01/100 A . D. a 29/02/32768 A . D.
VARCHAR(N), CHAR VARYNG(N), CHARACTER VARYNG(N)
Utilizado para armazenar caracteres de 1 a 32767 bytes. Colunas definidas para esse tipo de dado têm tamanho
variável definido por n.
Observações Sobre DATE, TIME e TIMESTANP:
Nas versões anteriores do InterBase, o tipo de dado DATE continha informações sobre data e tempo. Esse tipo
de dado foi substituídos pelos tipos DATE, TIME e TIMESTAMP ou Dialeto 3.
No Dialeto 1, apenas TIMESTAMP está disponível, o que significa dizer que esse tipo eqüivale ao tipo DATE
das versões anteriores. Quando restaura backups de bancos de dados, construídos com versões antigas, para bancos de
dados implementados com a versão 6, todas as colunas e domínios com o tipo DATE são automaticamente traduzidos
para TIMESTAMP.
Portanto, nos Dialetos 1 e 3, os tipos TIMESTAMP são equivalentes. No Dialeto 3, porém, você pode contar
com os novos tipos DATE e TIME.
Os operadores funcionais CURRENT_DATE, CURRENT_TIME e CURRENT_TIMESTAMP retornam
valores de data e tempo coerentes com as configurações atuais do sistema operacional, sem que seja necessário fazer
conversões do tipo (CAST) sobre os operadores TODAY e NOW.
Criando Tabelas:
Introdução:
Neste capítulo, vamos investigar como as entidades e os relacionamentos são criados fisicamente no InterBase.
Basicamente, estudaremos detalhadamente as instruções SQL utilizadas para criar as tabelas.
As tabelas são os objetos básicos (as estrelas) de um banco de dados e servem ao propósito de guardar os
dados relevantes para o negócio. As instruções SQL utilizadas na definição de tabelas são CREATE TABLE (para
criar), ALTER TABLE (para alterar) e DROP TABLE (para eliminar). Vamos ver como foram criadas ou alteradas as
tabelas do banco EMPLOYEE.GDB.
AS TABELAS DO BANCO EMPLOYEE.GDB
A TABELA COUNTRY (PAÍS)
/* Table: COUNTRY, Owner BUILDER */
CREATE TABLE COUNTRY (
COUNTRY
VARCHAR (15) NOT NULL,
CURRENC
VARCHAR (10) NOT NULL,
PRIMARY KEY (COUNTRY)
);
Página.: 38
Solutech Informática.
A criação da tabela COUNTRY é bastante simples. Apenas dois atributos, COUNTRY (País) e CURRENCY
(Moeda), são definidos para a tabela. A chave primária (PRIMARY KEY) está baseada na coluna COUNTRY.
Observe que os dois atributos da tabela estão definidos como "NOT NULL". Significa dizer que esses
atributos sempre deverão apresentar um valor qualquer em determinado instante. Em linguagem mais simples, as
colunas nunca poderão estar "vazias".
O InterBase sempre cria um índice para a coluna ou colunas definidas como chave primária. Sempre que uma
coluna fizer parte da chave primária, ela deve ser definida como NOT NULL ou o InterBase rejeitará a instrução com
uma mensagem de erro.
Embora o InterBase aceite que você defina um índice secundário em uma coluna definida como NULL, evite
utilizar essa prática. Qualquer coluna que faça parte de um índice, seja ele primário ou secundário, deve ser definida
como NOT NULL. O InterBase não reconhece a condição NULL para uma coluna. Assim, para definir uma coluna
como NULL, basta não declarar nada. Imagine que você quisesse definir a coluna CURRENCY como NULL. A
instrução correta seria:
CREATE TABLE COUNTRY(
COUNTRY
CURRENCY
PRIMARY KEY (COUNTRY)
);
VARCHAR(15) NOT NULL,
VARCHAR(10),
A TABELA CUSTOMER (CLIENTE)
/* Table:CUSTOMER, Owner BUILDER */
CREATE TABLE CUSTOMER (
CUST_NO
INTEGER NOT NULL,
CUSTOMER
VARCHAR(25) NOT NULL,
CONTACT_FIRST
VARCHAR(15),
CONTACT_LAST
VARCHAR(20),
PHONE_NO
VARCHAR(20),
ADDRESS_LINE1
VARCHAR(15),
ADDRESS_LINE2
VARCHAR(15),
CITY
VARCHAR(25),
STATE_PROVINCE
VARCHAR(15),
COUNTRY
VARCHAR(15),
POSTAL_CODE
VARCHAR(12),
ON_HOLD
CHAR(1) DEFAULT NULL,
PRIMARY KEY (CUST_NO)
);
ALTER TABLE CUSTOMER ADD FOREIGN KEY (COUNTRY) PREFERENCES COUNTRY
(COUNTRY);
Observe que a definição da coluna ON_HOLD apresenta a cláusula DEFAULT. Esta cláusula é utilizada
quando desejamos que o banco de dados crie um valor padrão quando um valor não for explicitamente definido para
uma coluna. No caso específico, a coluna ON_HOLD receberá o valor NULL se nenhum valor a ela for atribuído.
Claro que você poderia definir um valor diferente de NULL, como "S" ou "N", por exemplo.
Veja que há uma instrução de alteração de tabela (ALTER TABLE). Essa instrução cria uma integridade
referencial para a coluna COUNTRY. De acordo com a instrução, a coluna COUNTRY da tabela CUSTOMER é uma
chave estrangeira (FOREIGN KEY), ou seja, um ponteiro para a coluna COUNTRY da tabela COUNTRY. Assim,
qualquer valor atribuído para a coluna COUNTRY, em CUSTOMER, deverá ser um valor existente na coluna
COUNTRY da tabela COUNTRY.
Página.: 39
Solutech Informática.
TABELA EMPLOYEE (EMPREGADO)
/* Table: EMPLOYEE, Owner BUILDER */
CREATE TABLE EMPLOYEE (
EMP_NO
SMALLINT NOT NULL,
FIRST_NAME
VARCHAR(15)
LAST_NAME
VARCHAR(20)
PHONE_EXT
VARCHAR (4),
HIRE_DATE
TIMESTAMP DEFAULT 'NOW' NOT NULL,
DEPT_NO
CHAR(3) NOT NULL,
JOB_CODE
VARCHAR(05) NOT NULL,
JOB_GRADE
SMALLINT NOT NULL,
JOB_COUNTRY
VARCHAR(15),
SALARY
NUMERIC(15,2) NOT NULL,
FULL_NAME
COMPUTED BY (last_name | | ', ' | | fist_name),
PRIMARY KEY (EMP_NO),
FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO);
);
Duas coisas interessantes na criação da tabela EMPLOYEE. Veja que a coluna HIRE_DATE foi definida com
o valor padrão "NOW". Assim, quando uma data não for atribuída à coluna, ela receberá a data corrente (obtida no
sistema operacional). Por sua vez, a coluna FULL_NAME é definida com um valor calculado (COMPUTED BY). De
acordo com a definição da coluna, ela sempre receberá uma combinação de valores atribuídos às colunas
LAST_NAME e FIRST_NAME. O símbolo | | indica concatenação.
Você pode enxergar
FULL_NAME COMPUTED BY (last_name || ‘,’ || first_name)
como
FULL_NAME := last_name + ‘,’ + first_name
Se os valores das colunas FIRST_NAME e LAST_NAME fossem, respectivamente “Ivan” e “Mecenas”, o valor
atribuído à FULL_NAME seria: “Mecenas, Ivan”
A TABELA EMPLOYEE_PROJECT (EMPREGADO-PROJETO)
/* Table: EMPLOYEE_PROJECT, Owner BUILDER */
CREATE TABLE EMPLOYEE_PROJECT (
EMP_NO
SMALLINT NOT NULL,
PROJ_ID
CHAR(5) NOT NULL,
PRIMARY KEY (EMP_NO, PROJ_ID)
);
ALTER TABLE EMPLOYEE_PROJECT ADD FOREIGN KEY (EMP_NO)
REFERENCES EMPLOYEE (EMP_NO);
ALTER TABLE EMPLOYEE_PROJECT ADD FOREIGN KEY (PROJ_ID)
REFERENCES PROJECT (PROJ_ID);
A tabela EMPLOYEE_PROJECT existe para resolver o relacionamento muitos para muitos (N:N) existente
entre as tabelas EMPLOYEE e PROJECT. Perceba que os dois atributos da tabela são ponteiros (chaves estrangeiras)
para as tabelas primitivas. A tabela EMPLOYEE_PROJECT representa um tipo de entidade conhecida como "entidade
associativa".
Página.: 40
Solutech Informática.
TABELA PROJECT (PROJETO)
/* Table: PROJECT, Owner BUILDER */
CREATE TABLE PROJECT (
PROJ_ID
CHAR(5) NOT NULL,
PROJ_NAME
VARCHAR(20) NOT NULL,
PROJ_DESC
BLOB SUB_TYPE 1 SEGMENT SIZE 800,
TEAM_LEADER
SMALLINT NOT NULL,
PRODUCT
VARCHAR(12),
UNIQUE(PROJ_NAME),
PRIMARY KEY (PROJ_ID)
);
ALTER TABLE PROJECT ADD FOREIGN KEY (TEAM_LEADER) REFERENCES EMPLOYEE
(EMP_NO);
Abordando Índices:
Índices são mecanismos utilizados para recuperar registros de forma mais rápida, em resposta a certas
condições de pesquisa.
Em linhas mais amplas, índices são tabelas especiais que guardam informações que funcionam como ponteiros
para localização das linhas de uma tabela.
Índices não são objetos relevantes para a definição de bancos de dados. Contudo, não podemos evitar que
sejam utilizados. Mesmo que você não defina índices explicitamente, o banco de dados criará os índices de que
necessita
Podemos criar um índice para uma coluna apenas (índice simples) ou para um grupo de colunas (índice
composto). Índices criados com a condição UNIQUE garante que na coluna (ou colunas) indexada não haverá linhas
duplicadas.
Índices são úteis para recuperação rápida de dados, mas não devem ser criados de forma abusiva, sem uma
análise criteriosa. Além de consumirem espaço em disco, torna as operações de inclusão, alteração ou exclusão mais
lentas; em muitos casos, muito mais lentas. A razão disso é que, cada vez que os dados referentes a colunas indexadas
são alterados, os índices são também atualizados.
Os índices podem ser criados diretamente, através do comando CREATE INDEX, ou criados automaticamente
pelo sistema como parte da instrução CREATE TABLE. O InterBase cria índices automaticamente em colunas
definidas com as restrições PRIMARY KEY (Chave primária), FOREIGN KEY (Chave estrangeira) e UNIQUE
(Único). Os índices criados sobre as restrições PRIMARY KEY e FOREIGN KEY preservam a integridade referencial
dos bancos de dados.
O InterBase permite que sejam criados até 64 índices por tabela. A sintaxe da instrução CREATE INDEX é
dada abaixo:
create [unique] [ascending] [descending]
index on <tabela> (<coluna1>, ..., <coluna n>)
As palavras ascending e descending podem ser abreviadas para asc e desc.
Índices podem ser alterados pela instrução ALTER INDEX e destruídos pela instrução DROP INDEX.
Página.: 41
Solutech Informática.
Operações de Manutenção:
Introdução:
Neste capítulo vamos abordar aspectos fundamentais ligados a manutenção de bancos de dados: cópias de
segurança (backup) e restauração de dados (restore). Há inúmeras tarefas de manutenção realizáveis em bancos de
dados que não vamos tratar aqui. Essas tarefas incluem, por exemplo, manutenção de tabelas, manutenção de índices,
manutenção de procedimentos, manutenção de gatilhos etc. Essas operações dizem respeito mais ao dia-a-dia de
Administradores de Bancos de Dados (DBA) e não de desenvolvedores.
Cópias de segurança e restauração, entretanto, têm lugar destacado entre as tarefas de manutenção de bancos
de dados e interessam de perto a todos os profissionais que lidam com eles.
Profissionais de informática, ou usuários comuns, estão acostumados a fazer cópias de segurança dos seus
arquivos. O nome é incisivo: "segurança". Quando fazemos backup estamos contando com a possibilidade de que os
dados originais sofram panes muitas das vezes irreparáveis.
Um backup, como você sabe, nada mais é do que a cópia de um arquivo guardada como segurança em disco
rígido ou outro meio de armazenamento. Normalmente não é uma boa medida fazer cópias de segurança no mesmo
disco em que se encontram os arquivos originais.
Os sistemas operacionais oferecem facilidades para a realização de backups. Na verdade, você poderia
simplesmente fazer uma cópia do seu banco de dados no seu diretório A para o diretório B. O InterBase, entretanto,
oferece algumas vantagens quando você se utiliza de suas próprias ferramentas de cópia e restauração de dados.
Mostrar esses processos e as vantagens associadas é o objetivo dos tópicos que veremos adiante.
Backup e Restore no InterBase:
Quando você realiza backup e restore com as ferramentas supridas pelo InterBase, algumas vantagens estão
incluídas. Entre outras você pode:
- Melhorar o desempenho do banco de dados, excluindo versões antigas de registros e balancear (equilibrar) arquivos
de índice;
- Reduzir o tamanho do banco de dados, ao recuperar espaços perdidos por registros excluídos e compactar os dados
remanescentes;
- Realizar mudanças na estrutura do banco de dados, como aumentar ou diminuir o tamanho das páginas de dados;
- Criar cópias independentes de plataforma.
REALIZANDO BACKUP:
A operação de backup pode ser realizada enquanto outros usuários estão tendo acesso ao banco de dados.
Entretanto, esteja ciente que quaisquer alterações realizadas pelos usuários após o início do backup não serão
registradas, isto é, não serão salvas. Portanto, não poderão ser recuperadas.
Via de regra, operações de backup devem ser feitas sem haver a concorrência de usuários. Contudo, nem
sempre essa decisão pode ser tomada facilmente, e muitas cópias de segurança são realizadas sem a completeza dos
dados. Isso não é nada de desastroso. Afinal, imediatamente após a realização de um backup, novas alterações são
introduzidas pelos usuários. Dificilmente teremos cópias que sejam imagens perfeitas da situação atual do banco de
dados.
A frequência com que operações de backup se efetivem depende muito das regras de negócio e do
comportamento do banco de dados. Como regra básica, siga a seguinte: se o banco de dados é alterado freqüentemente,
faça cópias de segurança freqüentemente.
Página.: 42
Solutech Informática.
Uma das coisa que você vai notar com relação aos arquivos de backup realizados pelo InterBase, é que eles
ocupam muito menos espaço que os arquivos originais. Arquivos de backup, por exemplo, não incluem arquivos de
índice, apenas suas definições. Os arquivos de índice são recriados quando você realiza a operação inversa: restauração
dos dados (restore).
Vamos utilizar a ferramenta IBConsole para realizar uma cópia de segurança do banco de dados de exemplo
EMPLOYEE.GDB.
Na operação de backup, algumas informações devem ser fornecidas e um conjunto de opções é oferecido.
Quanto às informações, o nosso teste de backup utilizará as seguintes:
Database
Server = Local Server
Alias = employee.gdb
Backup File(s)
Server = Local Server
Alias = employee_2000_08_28
Filename(s)
C:\temp\employee.gbk
Size (bytes) = 2048
A informação Database indica o servidor e o banco de dados que sofrerão a ação. A informação Backup File(s)
fornece o nome do servidor e o apelido do arquivo de backup. Se o apelido não existir ele será criado pelo processo.
Demos o apelido employee_2000_08_28 ao arquivo de backup para indicar que se trata de uma cópia do dia
28/08/2000.
A informação Filename(s) indica o nome do arquivo de backup e o tamanho da página de dados. O arquivo
employee.gbk, portanto, será criado no diretório c:\temp e, posteriormente, poderá ser referenciado pelo apelido
(employee_2000_08_28). Imaginamos que você tem um diretório c:\temp em sua máquina. Utilizamos a extensão
padrão (gbk) para arquivos de backup do InterBase; isso não é obrigatório.
Para criar o backup clique na opção do menu “DataBase\Maintenance\Backup/Restore\Backup”
Observe a caixa de diálogo Database Backup. No lado esquerdo da caixa, estão as informações a que nos
referimos anteriormente. No lado direito as opções que vamos comentar em seguida.
Há sete opções disponíveis na caixa de diálogo Database Backup:
Vamos Comentar as mais importantes:
Format
Transportable ou Non-transportable
Escolhemos a opção transportable. Com isso, poderemos recuperar o backup feito em uma plataforma
Windows, por exemplo, para uma plataforma Unix.
Metadata Only
True ou False
Escolhemos a opção False (falso). A opção True (verdadeiro) faria backup apenas dos metadados do banco.
Com a opção False, todo o conteúdo do banco será salvo. A opção True é muito utilizada quando se quer guardar
apenas uma cópia dos metadados do banco. Esta cópia permite recriar um banco de dados idêntico, porém vazio. Em
muitas situações na vida profissional você precisará fazer isso.
Página.: 43
Solutech Informática.
Garbage Collection
True ou False
Escolhemos a opção True. Assim serão descartadas todas as versões obsoletas de registros, otimizando o
tamanho do banco de dados. Você pode entender Garbage Collection como "sobras de registros". A opção False pode
ser utilizada nos casos em que você precisar de uma cópia idêntica do banco.
Transaction in Limbo
Process ou Ignore
Escolhemos a opção Process. Esta opção manterá as transações pendentes na cópia de segurança. Com a
opção Ignore, todas as transações pendentes seriam descartadas. Transações pendentes derivam de falhas no processo
two-phase commit (commit em duas fases). A opção Ignore, portanto, sempre considera a versão mais recente dos
registros.
Checksums
Process ou Ignore
Escolhemos a opção Process, para que o processo de backup analise, página a página, a integridade dos dados.
Convert to Tables
True ou False
Escolhemos a opção False, isto é não haverá conversão de arquivos externos em tabelas internas. A opção
True faria a conversão.
Verbose Output
None, To Screen ou To File
Escolhemos a opção To Screen, para usar a tela como saída (passo a passo) do andamento do processo. Essa
opção torna a operação mais lenta, mas nos permite ver o que está acontecendo. Com a opção To File, poderíamos
direcionar o resultado do processo para um arquivo qualquer. A opção None inibe o monitoramento da operação.
Quando você seleciona o botão OK da caixa de diálogo Database Backup, com a opção Verbose Output
assinalada para To Screen, você poderá acompanhar todo o processo na tela do seu computador. Ao terminar a
operação, uma mensagem lhe dirá que o backup foi concluído e você terá disponíveis todas as informações do
processo.
Vimos que uma operação de backup necessita de um conjunto de opções. Embora algumas dessas opções
possam parecer supérfluas, esteja certo de que elas poderão ser extremamente necessárias em situações reais
relacionadas com banco de dados. As opções que selecionamos são, possivelmente, as que você mais utilizará no seu
dia-a-dia. Não se esqueça, portanto, de avaliar cada situação de backup para decidir sobre as opções a serem
assinaladas.
REALIZANDO RESTORE:
Cópias de segurança existem para, se for o caso, serem recuperadas. Nem sempre uma cópia de segurança tem
de ser restaurada por problemas existentes nos dados originais. Operações de restauração são de modo freqüente
utilizadas para “enxugar” o banco de dados ou balancear índices.
Vamos realizar uma operação de restauração de dados com base no arquivo enployee.gbk visto no tópico
precedente.
Para executar o restore, clique na opção do menu “DataBase\Maintenance\Backup/Restore\Restore”
Página.: 44
Solutech Informática.
Quando fizemos a operação de backup, criamos o alias employee_2000_08_28 para a cópia do banco de dados
gravada com o nome “c:\temp\employee.gbk”
O alias torna mais rápido o processo de fornecer as informações necessárias à restauração dos dados.
Na opção Alias, selecionamos o Alias employee_2000_08_28 e as informações relativas ao arquivo se
tornarão disponíveis no lado esquerdo da caixa de diálogo DataBase Restore. Vamos então comentar as opções
existentes no lado direito da caixa de diálogo.
Há oito opções disponíveis para a operação de Restore.
Vamos Comentar as mais importantes:
Overwrite
True ou False
Escolhemos True, par que a operação sobrescreva o arquivo de banco de dados existente. Essa opção não deve
ser utilizada quando usuários estiverem usando o banco de dados. Devemos Ter muito cuidado na escolha dessa opção.
Commit After Each Table
True ou False
Escolhemos False. Com essa opção os metadadis são inteiramente recuperados e, em seguida, os dados são
restaurados. A opção True obrigaria o InterBase a recuperar os metadados e os dados de cada tabela e confirmar a
operação (commit) tabela por tabela.
Deactive Indexes
True ou False
Escolhemos False. Desta forma, os arquivos de índices serão recriados ao fim do processo de recuperação dos
dados. A opção True normalmente é utilizada quando há suspeita de que os dados estão inconsistentes (valores
duplicados em colunas índices).
Verbose Output
None, To Screen ou To File
Escolhemos a opção To Screen, para usar a tela como saída (passo a passo) do andamento do processo. Essa
opção torna a operação mais lenta, mas nos permite ver o que está acontecendo. Com a opção To File, poderíamos
direcionar o resultado do processo para um arquivo qualquer. A opção None inibe o monitoramento da operação.
Gerenciando Usuários:
Introdução:
Quando a instalação do Interbase é completa, um bando de dados de segurança, denominado isc4.gdb, é
disponibilizado no diretório padrão “C:\Arquivos de programas\Borland\Interbase”. Para que se possa utilizar um
servidor InterBase, cada indivíduo (usuário) deve apresentar um registro no bando de dados de segurança com seus
dados de cadastrais.
O Nome do usuário (User Name) deve ter um máximo de 31 caracteres. A senha (Password) tem um limite de
32 caracteres. Fique atento ao foto de que a senha é um dado case-sensitive (letras maiúsculas são diferentes de letras
minúsculas), o que não acontece com o nome do usuário.
Embora a senha possa ter até 32 caracteres, apenas os oito primeiros são significativos (considerados pelo
servidor). Nas conexões via Rede, a senha sofre um adequado processo de criptografia no momento da transição.
Página.: 45
Solutech Informática.
Criando Visões:
Introdução:
Uma visão é uma tabela virtual: uma tabela resultante de uma consulta efetuada sobre uma ou mais tabelas.
Uma vez definidas, as visões podem receber o mesmo tratamento dado a tabelas normais. Um ponto inicial a
considerar envolve a compreensão de que uma visão não replica dados, ou seja, não gera uma cópia dos dados
armazenados em outras tabelas. Quando os dados das tabelas primárias são atualizados, as visões automaticamente
refletem as mudanças.
Formas de Criação de Visões:
Uma visão pode ser criada de diversas maneiras como você verá a partir das informações que daremos a
seguir. Você notará que utilizaremos duas importantes cláusulas para criar visões: CREATE VIEW e SELECT.
Considere a Tabela :
CARGO
Cod_Cargo
Nome_Cargo
Minimo_Salario
Maximo_Salario
Lingua_requerida
1 - Você poderia criar a visão baseada num grupo específico de colunas, como mostra a instrução abaixo:
Create View Limite_Salario as
Select Cod_Salario, Minimo_Salario, Maximo_Salario
From CARGO
Como se percebe, a visão Limite_Salario recebeu um numero reduzido de colunas. Três para sermos exatos:
Cod_Cargo
Minimo_Salario
Maximo_Salario
2 – Conjunto específico de linhas de uma tabela:
Essa Forma de criar visões baseia-se em reduzir o número de linhas acessíveis em vez de um número de
colunas.
Create View Baixo_Salario as
Select *
From CARGO
Where Maximo_Salario < 15000
Obs: Não estamos limitados a criar visões que derivem apenas de uma tabela. Duas ou mais tabelas podem ser reunidas
(Join) para definir visões mais complexas.
Eliminando Visões:
Assim como podemos criar visões, também podemos destruí-las. O InterBase utiliza o comando padrão DROP
VIEW para eliminar visões. Se você, por exemplo, quisesse eliminar a visão Baixo_Salario, faria assim:
Drop View Baixo_Salario
Obs: Você não pode alterar uma Visão de forma direta. Para ser alterada, uma visão precisa ser destruída e depois
recriada.
Página.: 46