Artigo sobre banco de dados

Seminário S2i:
Banco de Dados - MySQL e PostgreSQL
Marcelo Moraes Minasi 0013021-4
Florianópolis, 26 de abril de 2004.
Lista de Tabelas
1
2
3
4
5
6
7
Tabela do fornecedor. . . . . . . . . . . . . . . . . .
Tabela das peças. . . . . . . . . . . . . . . . . . . . .
Tabela do relacionamento entre fornecedores e peças.
Dependências: um caso errado e outro correto. . . .
Tabela do fornecedor. . . . . . . . . . . . . . . . . .
Tabela da variável SEGUNDA e FP. . . . . . . . . .
Tabela da variável FC e CS. . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
4
4
5
8
9
10
11
Componentes de um sistema de banco de dados. . . . . . . . . . . . . .
Relacionamento como entidade. . . . . . . . . . . . . . . . . . . . . . . .
Primeira ilustração da arquitetura de três nı́veis. . . . . . . . . . . . . .
Segunda ilustração da arquitetura de três nı́veis. . . . . . . . . . . . . .
Tela após a simples execução de um cliente SQL e seu status. . . . . . .
Benchmark de banco de dados: páginas da web retornadas por segundo.
Benchmark de banco de dados: as respostas mais rápidas. . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
3
4
7
7
16
19
20
O banco de dados de fornecedores e peças (definição dos dados). . . . . . . . .
Exemplo de domı́nios usando DOMAIN. . . . . . . . . . . . . . . . . . . . . . .
Exemplo de falha de verificação de tipo em domı́nios. . . . . . . . . . . . . . . .
Exemplo de restrição usando SELECT. . . . . . . . . . . . . . . . . . . . . . .
Exemplo de projeção usando SELECT. . . . . . . . . . . . . . . . . . . . . . . .
Exemplo de projeção simplificada usando SELECT. . . . . . . . . . . . . . . .
Exemplo de junção usando SELECT. . . . . . . . . . . . . . . . . . . . . . . . .
Exemplo de INSERT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Exemplo de UPDATE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Exemplo de DELETE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Exemplo de CREATE VIEW. . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Exemplo de uma consulta sobre uma VIEW. . . . . . . . . . . . . . . . . . . .
Exemplo de eliminação de registro na VIEW e suas implicações na tabela real.
Exemplo de SQL Embutida com API para Java. . . . . . . . . . . . . . . . . .
Exemplo de SQL Embutida com API para C++. . . . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
6
12
13
13
13
14
14
15
15
16
16
17
17
21
23
Lista de Figuras
1
2
3
4
5
6
7
Códigos
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sumário
1 Introdução
1.1 Instalação . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3
3
2 Conceituação básica
3
3 Projeto
3.1 Arquitetura . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.2 Formas normais . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5
5
5
4 SQL
4.1 Operações de definição . . . . . . . .
4.2 Operações de manipulação de dados
4.3 Operações de Atualização . . . . . .
4.4 Sumário das instruções SQL . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
11
12
13
13
14
5 Exemplo
15
6 APIs - Application Program Interfaces
6.1 Java . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
6.2 C++ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
17
18
18
7 Estudo comparativo entre MySQL e PostgreSQL
18
1
Introdução
Esse documento foi fortemente baseado na referência [C. 00]. Tenta-se dar uma boa base
dos conceitos iniciais envolvendo bando de dados e do padrão SQL, mostrando-se, no final,
um exemplo, uma simples comparação (benchmark ) envolvendo alguns sistemas de bancos de
dados e algumas APIs - application program interface. Um interessante trabalho posterior seria
algumas ferramentas gráficas para construção de um banco de dados e uma melhor comparação
de alguns sistemas de bancos de dados, como MySQL, PostreSQL, etc.
1.1
Instalação
Assume-se aqui que o leitor conseguiu instalar perfeitamente um servidor de banco de dados
onde possa criar bancos e modificá-los, como também executar buscas exemplificadas nesse
documento através de query da SQL.
Como a instalação varia conforme o sistema operacional, indica-se aqui apenas alguns ponteiros para páginas de instalação de dois dos mais usados programas para sistemas de gerenciamento
de banco de dados: MySQL [MySb] na referência [MySa] e PostgreSQL em [Pos].
Outra referência interessante para instalação do MySQL [MySb] é: http://www.devshed.com
/c/a/MySQL/MySQL-Installation-and-Configuration/.
Após a instalação, algumas rotinas ainda precisam ser executadas, para maiores detalhes,
veja o ponteiro: http://dev.mysql.com/doc/mysql/en/Unix post-installation.html.
2
Conceituação básica
Sistema de Banco de Dados é simplesmente um sistema computadorizado de armazenamento
de registros, composto basicamente por quatro partes: dados (banco de dados), software (sistema
de gerenciamento do banco de dados), hardware (armazenamento fı́sico) e usuários (programadores de aplicação, usuários finais e DBA - Administrador). Tais registros são as linhas de uma
tabela, cujas colunas são os dados. Essa tabela é um arquivo computadorizado.
Figura 1: Componentes de um sistema de banco de dados.
Um banco de dados é uma coleção de dados persistentes (não que eles durem para sempre,
mas diferem de entradas, saı́das, etc.) utilizada pelos sistemas de aplicação.
2
CONCEITUAÇÃO BÁSICA
4
Sua utilização possui as seguintes vantagens: compartilhamento de dados, redundância controlada (reduzida), inconsistência controlada, suporte a transações (operações atômicas: tudo ou
nada, ou atualiza todas as tabelas necessárias, ou nenhuma; que em conjunto com o log implica
consistência), integridade (restrições de integridade, por exemplo, idade ≤ 0), maior segurança
(senhas), aplicações independente de dados, etc.
Uma tabela (variável de relação) contém os dados relativos a um conjunto de entidades
idênticas (livro, tema, leitor e requisição). Cada linha (tuplo) caracteriza uma entidade desse
conjunto. Cada coluna (atributo) representa uma caracterı́stica dessa entidade.
Cada tabela apresenta um identificador único, chamado de chave primária. Nem sempre
é vantagem acrescentar mais um campo designado a ser essa chave (como uma numeração
seqüencial e cı́clica), pois essa pode ser limitada pelo tamanho do tipo de dado desse campo.
Assim, ela pode ser constituı́da de vários dados agrupados da tabela. Por exemplo, o BD da
Eletrosul apresenta 3.000.000.000 de registros mensais!
As entidades são composta por registros, que são instâncias daquele tipo, por exemplo: a
entidade carro pode possuir os registros carroA, carroB, Monza etc. Além de entidades, normalmente num banco de dados, haverá relacionamentos entre entidades, que segundo o paradigma
relacional, também são representadas em tabelas. Observe a figura [2]. Isso será melhor explicado mais abaixo.
A seguir, apresenta-se alguns conceitos básicos no contexto de banco de dados:
Chave primária é um identificador único para cada tuplo de uma tabela. Cada tabela deve ter
uma chave primária. Uma chave primária pode ser constituı́da por um ou mais atributos.
Chave estrangeira é a utilização da chave primária numa outra tabela para se poder criar um
relacionamento.
Transação é uma unidade lógica de trabalho (atômicas).
Figura 2: Relacionamento como entidade.
#Fornecedor
1
2
NomeF
A
B
Tabela 1: Tabela do fornecedor.
#Peça
1
2
NomeP
Parafuso
Prego
Tabela 2: Tabela das peças.
Bancos de dados relacionais são sistemas de banco de dados baseados em uma fundamentação
normal (teórica) chamada de modelo relacional de dados, que apresenta três aspectos:
• Aspecto estrutural: os dados são representados por tabelas, e só tabelas - propriedade de
fechamento: a saı́da de uma operação é também uma tabela, o que garante que a saı́da de
uma operação pode ser a entrada de outra.
#Peça
1
2
#Fornecedor
1
2
Qtdade
2000
3000
Tabela 3: Tabela do relacionamento entre fornecedores e peças.
• Aspecto de integridade: essas tabelas satisfazem a certas condições de integridade;
• Aspecto manipulativo: os operadores disponı́vies para manipular tabelas derivam umas de
outras - os três mais importantes são:
Restrição (DEPTOs nos quais ORÇAMENTO > R$8 milhões),
Projeção (DEPTOs sobre #DEPTO, ORÇAMENTO) e
Junção (DEPTOs e EMPREGADOs sobre #DEPTO).
Aqueles registros devem sofrer algum tipo de operação em algum momento, como:
• Acrescentar novos arquivos (CREATE TABLE);
• Inserir novos dados em arquivos (INSERT);
• Buscar (SELECT), alterar (UPDATE) e eliminar dados em arquivos existentes (DELETE).
Esses exemplos são todos expressos em uma “linguagem” chamada SQL. Ela será melhor
explicada mais abaixo [4].
3
Projeto
3.1
Arquitetura
Geralmente, o projeto de um BD se dá em três nı́veis, como observado nas figuras [3] e [4]:
1. Interno (fı́sico): como os dados são fisicamente armazenados (número de bytes de cada
campo, etc.);
2. Externo (lógico do usuário): como os dados são vistos por cada usuário individualmente.
CREATE VIEW teste AS (EMP WHERE SALÁRIO > 3K) { #EMP, NOMEEMP,
SALÁRIO } ;
3. Conceitual (lógico comunitário): intermediário entre os dois.
Na concepção de um BD, tanto a Linguagem de definição de dados (DDL), como ilustrado no
código [1], como a linguagem de manipulação de dados (DML) são transparentes para o usuário.
De um ponto de vista de mais alto nı́vel, pode ser considerado como uma arquitetura cliente/servidor.
3.2
Formas normais
O assunto de normalização é apenas uma formalização de uma idéia simples e muito usada
na prática. Idéia que consiste em fazer um projeto de banco de dados seguindo o paradigma
“um fato em um lugar”, isto é, evitar redundância. Além disso, a normalização nos ajuda a
estruturar o banco de dados de forma a tornar mais fácil as atualizações de uma única tupla do
que seria caso esse banco não estivesse normalizado.
3
PROJETO
6
1
2
3
4
TYPE #F . . . ;
TYPE NOME . . . ;
TYPE #P . . . ;
TYPE QTD . . . ;
6 VAR F BASE RELATION
7
{ #F
#F o r n e c e d o r ,
8
NOME NomeF }
9
PRIMARY KEY { # F o r n e c e d o r e s } ;
11 VAR P BASE RELATION
12
{ #P
#Peca ,
13
NOME NomeP }
14
PRIMARY KEY { # Peca } ;
16 VAR FP BASE RELATION
17
{ #F
#F o r n e c e d o r ,
18
#P
#Peca ,
19
QTD
Qtdade }
20
PRIMARY KEY {# F o r n e c e d o r , # Peca }
21
FOREIGN KEY { # F o r n e c e d o r } REFERENCES F
22
FOREIGN KEY { # Peca }
REFERENCES P ;
Código 1: O banco de dados de fornecedores e peças (definição dos dados).
Algumas variáveis poderiam, mesmo estando normalizadas - no sentido do parágrafo anterior
- possuı́rem propriedades indesejáveis. É justamente nesse sentido que vêm os princı́pios de
normalização avançada, ou formas normais. Esses princı́pios nos permitem reconhecer esses
casos e substituir essas variáveis por outras mais desejáveis de algum modo.
Dizemos que uma variável está em uma forma normal se ela satisfaz a um certo conjunto
prescrito de condições. Por exemplo, dizemos que uma variável de relação - que modela um
relacionamento - está na segunda forma normal (2FN) se, e somente se, ela está em 1FN e
também satisfaz uma outra determinada condição, descrita mais abaixo em 3.2.
Numerosas formas normais foram definidas por volta de 1972. As três primeiras por Codd em
[E. 72]. Mais tarde, Boyce e Codd definiram também uma “outra terceira” forma normal, mais
abrangente, conhecida como forma normal de Boyce/Codd (FNBC). Subsequentemente,
Fagin definiu ainda a quarta e a quinta formas normais. A referência [E. 72] definiu também
um procedimento de normalização, através do qual uma variável de relação que está em
alguma forma normal especı́fica pode ser substituı́da por um conjunto de variáveis de relação
em alguma forma mais desejada. Esse procedimento é reversı́vel, o que significa que o processo
de normalização preserva informações.
Embora existam outras formas normais além dessas seis, esse documento abrangerá apenas
as três primeiras, visto que a implementação de um banco de dados projetado de forma que
suas variáveis estejam na 3FN é uma boa solução de compromisso entre a complexidade dos
procedimentos de normalização subsequentes e o nı́vel de caracterı́sticas desejadas obtidas (baixa
redundância, integridade, etc.).
O processo de normalização consiste em decompor uma variável de relação em outras
variáveis mais desejadas. Esse processo deve ser necessariamente ser perdas, i. e. reversı́vel.
3
PROJETO
7
Figura 3: Primeira ilustração da arquitetura de três nı́veis.
Figura 4: Segunda ilustração da arquitetura de três nı́veis.
Para que uma decomposição1 sem perdas seja realizada, e assim seja possı́vel recompor2 o
conjunto de informações, precisa-se respeitar o conceito de dependência funcional descrito a
seguir.
Para entender melhor a importância do conceito de dependência funcional, imagine o
seguinte problema: se R1 e R2 são projeções de alguma variável de relação R, e se R1 e R2 em
conjunto incluem todos os atributos de R, que condições devem ser satisfeitas para garantir que
a junção de R1 e R2 nos dará de volta a variável de relação original R? Ver exemplo na tabela
[4] abaixo. É aqui que entram as dependências funcionais.
Seja R uma variável de relação, e sejam X e Y subconjuntos arbitrários do conjunto de
atributos de R. Então, dizemos que Y é funcionalmente dependente de X – em sı́mbolos, X →
Y, (”X seta Y”), se e somente se em todo valor válido de R, cada valor X tem associado a ele
exatamente um valor Y. Em outras palavras, em todo valor possı́vel válido de R, sempre que
1
2
O operador da decomposição na álgebra relacional é, na verdade, o de projeção.
O operador da recomposição na álgebra relacional é, na verdade, o de junção.
3
PROJETO
8
#Fornecedor
F3
F5
#Fornecedor
Caso correto
F3
F5
Caso errado
#Fornecedor
F3
F5
STATUS
30
30
STATUS
30
30
STATUS
30
30
CIDADE
Paris
Atenas
#Fornecedor
F3
F5
STATUS
30
30
CIDADE
Paris
Atenas
CIDADE
Paris
Atenas
Tabela 4: Dependências: um caso errado e outro correto.
duas tuplas concordam sobre seu valor X, elas concordam também sobre seu valor Y.
Todo conjunto de DFs – dependências funcionais – é equivalente a pelo menos um conjunto
irredutı́vel. Se I é um conjunto irredutı́vel equivalente a S, a imposição das DFs em I imporá
automaticamente as DFs em S. Defini-se um conjunto S de DFs como irredutı́vel se e somente
se:
1. O lado direito (o dependente) de cada DF em S contém apenas um atributo;
2. O lado esquerdo (o determinante) de cada DF em S é por sua vez irredutı́vel - significando
que nenhum atributo pode ser descartado do determinante sem converter S em algum
conjunto não equivalente a S;
3. Nenhuma DF em S pode ser descartada de S sem converter S em em algum conjunto não
equivalente a S.
Existem Diagramas DF que representam convenientemente essas dependências. Isso é interessante porque as DFs possuem uma noção semântica, cuja interpretação só depende do
projetista. Por exemplo, #F → CIDADE significa que cada fornecedor está localizado em exatamente uma cidade.
Apenas para se ter uma noção do ponto que se deseja atingir, descreve-se informalmente o
que seria a terceira forma normal (3FN).
Terceira forma normal: uma variável está na 3FN se e somente se os atributos não-chaves
(qualquer atributo que não participa da chave-primária da variável de relação) são:
• Mutuamente independentes, i. e., se nenhum deles é funcionalmente dependentes de qualquer combinação dos outros;
• Irredutivelmente dependentes da chave primária.
O fato de dois ou mais atributos serem mutuamente independentes quer dizer que cada um
deles pode ser atualizado independentemente dos demais.
Descreve-se a seguir o processo de normalização. Antes, a definição da primeira forma
normal:
Primeira forma normal: uma variável de relação está em 1FN se, e somente se, em todo
valor válido dessa variável de relação, cada tupla contém exatamente um valor para cada
atributo.
3
PROJETO
9
Por exemplo:
PRIMEIRA { #F, STATUS, #P, QDE, CIDADE }
PRIMARY KEY { #F, #P } ;
#F
F1
F1
F1
F1
F1
F1
F2
F2
F3
F4
F4
F4
STATUS
20
20
20
20
20
20
10
10
10
20
20
20
CIDADE
Londres
Londres
Londres
Londres
Londres
Londres
Paris
Paris
Paris
Londres
Londres
Londres
#P
P1
P2
P3
P4
P5
P6
P1
P2
P2
P2
P4
P5
QDE
300
200
400
200
100
100
300
400
200
200
300
400
Tabela 5: Tabela do fornecedor.
A principal desvantagem é a quantidade excessiva de redundâncias, que são também chamadas de anomalias de atualização. No exemplo acima, não dá para inserir a informação de que
um determinado fornecedor está numa cidade especı́fica até que ele efetivamente forneça pelo
menos uma peça. E se eliminarmos uma tupla da tabela acima, eliminaremos talvez muito mais
informações que gostarı́amos.
Para solucionar, utiliza-se o processo de normalização, que informalmente pode ser entendido
agora como uma processo de desempacotamento: inserir informações logicamente isoladas em
variáveis de relações separadas.
Assim, a solução para esses problemas seria:
SEGUNDA { #F, STATUS, CIDADE }
PRIMARY KEY { #F } ;
e
FP{ #F, #P, QDE }
PRIMARY KEY { #F, #P }
FOREIGN KEY { #F } REFERENCES SEGUNDA;
Assim, deve ficar claro que essa estrutura resolve todos os problemas com operações de
atualização descritos anteriormente.
Segunda forma normal: uma variável está na 2FN se e somente se ela está na 1FN e todo
atributo não-chave é irredutivelmente dependente da chave primária.
Mas a estrutura SEGUNDA ainda sofre pela falta de independência mútua entre seus atributos não-chaves. Mais detalhadamente, a dependência de STATUS sobre #F, embora seja
funcional e, de fato, irredutı́vel, é transitiva (através de CIDADE): cada valor de #F determina uma CIDADE e esta, por sua vez, determina o valor de STATUS. Dependências transitivas
levam também a anomalias de atualização. Mais uma vez a solução é desempacotar, transformar
a variável SEGUNDA em:
3
PROJETO
10
#F
F1
F2
SEGUNDA
F3
F4
F5
#F
F1
F1
F1
F1
F1
F1
FP
F2
F2
F2
F3
F4
F4
F4
STATUS
20
10
10
20
30
#P
P1
P2
P3
P4
P5
P6
P1
P2
P3
P2
P2
P4
P5
CIDADE
Londres
Paris
Paris
Londres
Atenas
QDE
Tabela 6: Tabela da variável SEGUNDA e FP.
FC { #F, CIDADE }
PRIMARY KEY { #F }
FOREIGN KEY { CIDADE } REFERENCES CS ;
e
CS{ CIDADE, STATUS }
PRIMARY KEY { CIDADE } ;
Terceira forma normal: uma variável está na 3FN se e somente se ela está em 2FN e todo
atributo não-chave é dependente de forma não transitiva da chave primária.
Uma observação importante a ser feita é que o nı́vel de normalização de uma variável de
uma relação dada é uma questão de semântica, não apenas uma questão de valores de dados
que essa variável de relação possa conter em algum momento particular. Para resumir, pode-se
dizer que para se chegar ao nı́vel da 3FN, deve-se realizar duas operações:
1. Dada a variável de relação R como esta: (Para reduzir dependências, redundâncias triviais)
R { A, B, C, D } PRIMARY KEY { A, B } ;
Deve-se substituir R por suas duas projeções R1 e R2:
#F
F1
F2
FC
F3
F4
F5
CIDADE
Londres
Paris
Paris
Londres
Atenas
CIDADE
Atenas
CS
Londres
Paris
STATUS
30
20
10
Tabela 7: Tabela da variável FC e CS.
R1 { A, D } PRIMARY KEY{ A, D } ;
R2 { A, B, C } PRIMARY KEY { A, B } FOREIGN KEY { A } REFERENCES R1 ;
2. Dada a variável de relação R como esta: (Para reduzir dependências transitivas, e.g. A →
B e B → C)
R { A, B, C } PRIMARY KEY { A } ;
Deve-se substituir R por suas duas projeções R1 e R2:
R1 { B, C } PRIMARY KEY{ B } ;
R2 { A, B } PRIMARY KEY { A } FOREIGN KEY { B } REFERENCES R1 ;
4
SQL
A SQL é uma linguagem padrão para interação com banco de dados relacionais. Originalmente, o nome ”SQL”significava Structured Query Language (Linguagem de Consulta Estruturada) e se pronunciava ”sequel”. Porém, agora a linguagem se transformou num padrão, e o seu
nome agora é apenas um nome - não é oficialmente uma abreviatura para alguma coisa - e a
pronúncia pendeu para ”esse-quê-éle”. Seu nome oficial é International Standard Database
Language SQL (1992), largamente referenciada na literatura por SQL/92 ou SQL2, que foi
a grande revisão do padrão proposto segundo o padrão SQL.
Posteriormente, teve-se um desenvolvimento da SQL2 para a SQL3, no que concerne ao
suporte a objetos. Mais informações a respeito do suporte a objetos podem ser obtidas no
seguinte ponteiro: http://www.objs.com/x3h7/sql3.htm.
Conceitualmente, SQL é um padrão relacional, i.e., não-procedural (nı́vel de abstração maior
que C++, por exemplo): não se indica como mas o quê se quer. A tarefa de ”como” executar é
definida pelo otimizador do SGBD.
Um tutorial de SQL pode ser obtido em: http://www.devshed.com/c/a/MySQL/A-TechnicalTour-of-MySQL/. Uma simples referência de quais diretivas são padronizadas e suas sintaxes
pode ser obtidas facilmente em http://www.1keydata.com/sql/sql.html.
4
SQL
12
Uma referência excelente para que deseja saber mais sobre SQL é o ponteiro http://sqlzoo.net/.
Ele possui uma ferramenta interativa para se contruir declarações SQL e testá-las sobre diferentes
bancos de dados.
4.1
Operações de definição
Na SQL3 é possı́vel definir-se domı́nios próprios do usuário, na SQL2 não. Aqui, os domı́nios
serão considerados como tipos, mas uma discussão um pouco mais profunda mostra que eles estão
longe de serem a mesma coisa, como mostra o capı́tulo 4 de [C. 00]. Os tipos internos permitidos
pela SQL são:
• CHARACTER [ VARYING ] (n);
• BIT [ VARYING ] (n);
• NUMERIC (p,q);
• DECIMAL (p,q);
• INTEGER;
• SMALLINT;
• FLOAT (p);
• DATE;
• TIME;
• TIMESTAMP;
• INTERVAL.
Como dito anteriormente, os domı́nios em SQL não são tipos verdadeiros. Em SQL, eles
servem apenas para permitir que um tipo embutido, já definido, receba um nome que possa ser
usado como abreviação por várias colunas em diversas definições de tabelas. Um exemplo de
domı́nios podem ser vistos no código [2].
1 CREATE DOMAIN t i p o F # CHAR( 5 ) ;
2 CREATE DOMAIN t i p o P # CHAR( 6 ) ;
4 CREATE TABLE F ( t i p o F # F # , . . . ) ;
5 CREATE TABLE P ( t i p o P # P # , . . . ) ;
6 CREATE TABLE FP ( t i p o F # F#, t i p o P # P # , . . . ) ;
Código 2: Exemplo de domı́nios usando DOMAIN.
Como domı́nio não constitui uma tipagem forte e, portanto, não existe uma verificação de
tipo verdadeira, exige-se muito cuidado ao usá-la. Por exemplo, dadas as definições do código [2],
a operação de SQL descrita no código [3] não falhará em nenhuma verificação de tipo, embora
logicamente devesse falhar.
Pode-se criar uma base de dados no sistema de gerenciamento de banco de dados através da
instrução CREATE DATABASE nome bd . Ela cria uma base da dados vazia. A partir daı́, a
4
SQL
13
1 SELECT ∗
2 FROM FP
3 WHERE F# = P# ;
Código 3: Exemplo de falha de verificação de tipo em domı́nios.
instrução necessária para se criar tabelas, já mostrada acima pela facilidade em seu uso e pelo
seu caráter auto-explicativo, é CREATE TABLE nome (tipoColuna1 nomeColuna1, tipoColuna2
nomeColuna2, ) . Essa instrução pode ser passada com mais parâmetros. Para mais informações,
consulte [MyS04].
Depois de criada uma tabela, pode-se alterá-la através da instrução ALTERTABLE nome
ADD tipoColuna nomeColuna . Ela inclui uma coluna à uma tabela já existente. Para remover
um coluna, basta trocar o ADD por DROP, que será visto mais adiante.
Finalmente, o último dos mais importantes comandos de definição de dados, é o DROP.
Essa instrução permite excluir base de dados DROP DATABASE nome bd ou mesmo tabelas
DROP TABLE nome inteiras. Para a exclusão de simples registros, usa-se DELETE, que será
explicado mais adiante.
4.2
Operações de manipulação de dados
Como dito anteriormente, os três principais aspectos manipulativos de um banco de dados
relacional são: restrição, projeção e junção. Essas três operações podem ser implementadas pela
instrução SELECT. Abaixo tem-se alguns exemplos que comprovam essa idéia.
1 SELECT #F, #P , QDE
2 FROM
FP
3 WHERE QDE < 1 5 0 ;
Código 4: Exemplo de restrição usando SELECT.
1 SELECT F#, NomeF
2 FROM
F ;
Código 5: Exemplo de projeção usando SELECT.
Note que o código [5] acima pode ser simplificado como o código [6] abaixo.
Obs.: Às vezes pode ser necessário o uso de nomes qualificados para tirar a ambiguidade
de referências à colunas, por exemplo: P.P#, FP.P#. Inclusive o ’*’ pode ser qualificado, como
em ’F.*’.
4.3
Operações de Atualização
As principais operações de atualização definidas pela SQL são a inserção (INSERT), atualização (UPDATE) e eliminação (DELETE) de registros. É interessante notar que a eliminação
de tabelas possui uma diretiva especial (DROP) já mencionada anteriormente em 4.1.
O exemplo [8] abaixo pressupões que já exista uma tabela com o nome temp, com duas colunas, P# e PESO. Essa instrução insere nessa tabela números de peças e pesos correspondentes
a todas as peças vermelhas.
1 SELECT ∗
2 FROM
F ;
Código 6: Exemplo de projeção simplificada usando SELECT.
1 SELECT F . F#, P#, NomeF
2 FROM
F , FP
3 WHERE F . F# = FP . F# ;
Código 7: Exemplo de junção usando SELECT.
O próximo exemplo, código [9], atualiza o status de tidis is fornecedores em Paris, duplicandoo.
A instrução DELETE, no exemplo 10, elimina todas as remessas correspondentes à peça P2.
Já foi citado em [3.1] que pode-se criar visões no projeto de um banco de dados. Elas
representam o nı́vel mais externo da arquitetura de um banco. Em SQL pode-se criar visões
a partir da diretiva CREATE VIEW. A partir daı́, essa visão é tratada exatamente como uma
tabela, mas que não está implementada fisicamente. Um exemplo de como criar um visão e
de uma consulta de SQL sobre essa visão pode ser observada abaixo nos códigos [11] e [12]
respectivamente.
É interessante notar que qualquer alteração sobre uma VIEW, afetará diretamente a tabelea
correspondente que está fisicamente implementada. Imagine a seguinte visão criada como mostra
o código [13]. A operação de exclusão executada na linha 6, é o mesmo que executar a operação
da linha 8 sobre a tabela “pai” da visão.
4.4
Sumário das instruções SQL
As instruções mais comuns usadas em SQL e mencionadas anteriormente nesse documento
são: CREATE DOMAIN, CREATE TABLE, CREATE DATABASE, CREATE VIEW, ALTER
DOMAIN, ALTER TABLE, ALTER VIEW, INSERT, UPDATE, DELETE, DROP DOMAIN,
DROP TABLE, DROP VIEW, DROP DATABASE.
Segue abaixo uma relação resumida e simplificada das palavras reservadas definidas pelo
padrão SQL.
Do padrão SQL2 de 1992, dentre outras mais comuns, tem-se: AFTER, ALIAS, ASYNC, BEFORE, BOOLEAN, BREADTH, COMPLETION, CALL, CYCLE, DATA, BETWEEN, BIT,
BIT LENGTH, BOTH, CASCADE, CASCADED, CASE, CAST, CATALOG, CHAR LENGTH,
CHARACTER LENGTH, COALESCE, COLLATE, COLLATION, COLUMN, CONNECT,
CONNECTION, CONSTRAINT, CONSTRAINTS, CONVERT, CORRESPONDING, CROSS,
CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP, CURRENT USER, DATE,
DAY, DEALLOCATE, DEFERRABLE, DEFERRED, DESCRIBE, DEPTH, DICTIONARY,
EACH, ELSEIF, EQUALS, GENERAL, IF, IGNORE, LEAVE, DESCRIPTOR, DIAGNOSTICS, DISCONNECT, DOMAIN, DROP, ELSE, END-EXEC, EXCEPT, EXCEPTION, EXECUTE, EXTERNAL, EXTRACT, FALSE, FIRST, FULL, GET, GLOBAL, HOUR, IDENTITY, IMMEDIATE, INITIALLY, INNER, INPUT, INSENSITIVE, INTERSECT, INTERVAL, ISOLATION, JOIN, LAST, LEADING, LEFT, LEVEL, LOCAL, LOWER, MATCH,
MINUTE, MONTH, NAMES, NATIONAL, LESS, LIMIT, LOOP, MODIFY, NEW, NONE,
OBJECT, OFF, OID, OLD, NATURAL, NCHAR, NEXT, NO, NULLIF, OCTET LENGTH,
ONLY, OUTER, OUTPUT, OPERATION, OPERATORS, OTHERS, PARAMETERS, PENDANT, PREORDER, PRIVATE, OVERLAPS, PAD, PARTIAL, POSITION, PREPARE, PRE-
1 INSERT
2 INTO temp (
3
SELECT
4
FROM
5
WHERE
P#, PESO )
P#, PESO
P
COR = ’ Vermelha ’ ;
Código 8: Exemplo de INSERT.
1 UPDATE F
2 SET STATUS = STATUS ∗ 2
3
WHERE CIDADE = ’ P a r i s ’ ;
Código 9: Exemplo de UPDATE.
SERVE, PRIOR, READ, PROTECTED, RECURSIVE, REF, REFERENCING, REPLACE,
RESIGNAL, RETURN, RELATIVE, RESTRICT, REVOKE, RIGHT, ROWS, SCROLL, SECOND, SESSION, RETURNS, ROLE, ROUTINE, ROW, SAVEPOINT, SEARCH, SENSITIVE, SEQUENCE, SESSION USER, SIZE, SPACE, SQLSTATE, SUBSTRING, SYSTEM USER,
SIGNAL, SIMILAR, SQLEXCEPTION, SQLWARNING, STRUCTURE, TEST, THERE, TEMPORARY, THEN, TIME, TIMESTAMP, TIMEZONE HOUR, TIMEZONE MINUTE, TRAILING, TRANSACTION, TRANSLATE, TRANSLATION, TRIM, TRUE, UNKNOWN, TRIGGER, TYPE, UNDER, VARIABLE, VIRTUAL, VISIBLE, WAIT, WHILE, UPPER, USAGE,
USING, VALUE, VARCHAR, VARYING, WHEN, WRITE, YEAR, WITHOUT, ABSOLUTE,
ACTION, ADD, ALLOCATE, ALTER, ARE, ASSERTION, AT, ZONE.
Do padrão SQL3 de 1998, dentre outras mais comuns, tem-se: ACTION, ACTOR, AFTER, ALIAS, ASYNC, ATTRIBUTES, BEFORE, BOOLEAN, BREADTH, COMPLETION,
CURRENT PATH, CYCLE, DATA, DEPTH, DESTROY, DICTIONARY, EACH, ELEMENT,
ELSEIF, EQUALS, FACTOR, GENERAL, HOLD, IGNORE, INSTEAD, LESS, LIMIT, LIST,
MODIFY, NEW, NEW TABLE, NO, NONE, OFF, OID, OLD, OLD TABLE, OPERATION,
OPERATOR, OPERATORS, PARAMETERS, PATH, PENDANT, POSTFIX, PREFIX, PREORDER, PRIVATE, PROTECTED, RECURSIVE, REFERENCING, REPLACE, ROLE, ROUTINE, ROW, SAVEPOINT, SEARCH, SENSITIVE, SEQUENCE, SESSION, SIMILAR, SPACE,
SQLEXCEPTION, SQLWARNING, START, STATE, STRUCTURE, SYMBOL, TERM, TEST,
THERE, TRIGGER, TYPE, UNDER, VARIABLE, VIRTUAL, VISIBLE, WAIT, WITHOUT,
CALL, DO, ELSEIF, EXCEPTION, IF, LEAVE, LOOP, OTHERS, RESIGNAL, RETURN,
RETURNS, SIGNAL, TUPLE, WHILE.
5
Exemplo
Apenas algumas definições interessantes:
Nome do servidor:
Nome do cliente:
mysqld ou mysqld.exe;
myslq ou mysql.exe.
Um exemplo de um comando tı́pico para iniciar o cliente é:
c:\mysql\bin\mysql -h nome.do.host -u nomeDoUsuario -p nomeDaBaseDeDados
Onde:
5
EXEMPLO
16
1 DELETE
2 FROM FP
3 WHERE P# = ’ P2 ’ ;
Código 10: Exemplo de DELETE.
1 CRETE VIEW b o m f o r n e c e d o r
2
AS SELECT F#, STATUS , CIDADE
3
FROM F
4
WHERE STATUS > 1 5 ;
Código 11: Exemplo de CREATE VIEW.
nome.do.host é o endereço do computador que está rodando o servidor;
nomeDoUsuario é o nome do usuário;
nomeDaBaseDeDados é o nome da base de dados que será usada;
-p é a opção que exige um prompt para a senha do usuário.
De um modo mais simple ainda, basta o comando: mysql},para que se tenha a seguinte
conexão observada na figura [5]. Note que a conexão se fez, por padrão, com os seguinte atributos:
usuário, minasi - quem executou o programa; host, como nenhum argumento foi passado na
chamada do programa, localhost; dentre outros.
Figura 5: Tela após a simples execução de um cliente SQL e seu status.
A partir do prompt fornecido pelo programa cliente, pode-se fazer qualquer solicitação SQL,
criar-se novas tabelas, base de dados (note que aı́ é necessário que se tenha permissão para
tal), assim como eliminá-las, fazer consultas, inserções, atualizações e etc. Além disso, pode-se
visualizar o banco de dados através de alguns comandos bem úteis:
1 SELECT F#, STATUS
2 FROM b o m f o r n e c e d o r
3 WHERE CIDADE = ’ Londres ’ ;
Código 12: Exemplo de uma consulta sobre uma VIEW.
1 CREATE VIEW t e s t e
2
AS SELECT #EMP, Nome Emp , S a l a r i o
3
FROM EMP
4
WHERE S a l a r i o > 3K ;
6 DELETE FROM t e s t e WHERE S a l a r i o < 5K ;
8 DELETE FROM EMP WHERE ( S a l a r i o > 3K && S a l a r i o < 5K ) ;
Código 13: Exemplo de eliminação de registro na VIEW e suas implicações na tabela real.
show databases; mostra todas as bases de dados do servidor que se tenha acesso. O comando
mysqlshow funciona da mesma forma só que este é executado no shell padrão, não no do
cliente.
use baseDeDadosX; configura a base de dados a ser utilizada. Normalmente, após a instalação, existem duas pré-definidas: mysql - que possui as permissões, usuários, etc. - e a
test - base para testes que qualquer usuário pode alterar.
show tables; mostra todas as tabelas da dase de dados configurada para uso. Note que antes é
necessário que se execute a instrução usebaseDeDadosX; . O comando mysqlshowbaseDeDadosX
funciona da mesma forma, mas é executado no shell padrão.
describe tabelaX; descreve como são cada campo de uma tabela, quais são seus tipos, seus
tamanhos, quais campos compõem a chave-primária, etc.
help mostra a ajuda.
Um outro comando, o mysqladmin, chama um programa diferente do cliente SQL. Esse
programa é usado para administrar vários aspectos do servidor de banco de dados MySQL.
Informações mais detalhadas a esse respeito podem ser obtidas em: http://dev.mysql.com/techresources/articles/mysql intro.html#SECTION0005000000.
Para finalizar, mostra-se abaixo dois exemplos de consultas SQL: a primeira executada na
prompt do cliente mysql, enquanto que a segunda é executada como um comando normal no
shell padrão.
• SELECT* FROM tabelaX;
• mysql-e ”SELECT * FROM tabelaX”baseDeDadosY
6
APIs - Application Program Interfaces
As APIs, ou Interfaces para Programas de Aplicação, permite que instruções SQL estejam
embutidas dentro de programas em linguagens comuns de programação, como C++, Java, PHP,
etc. Por isso mesmo, as APIs são também referenciadas como SQL Embutida.
Existem também referências para SQL Dinâmica, que nada mais é do que um conjunto
de recursos embutidos de SQL que se destinam a oferecer suporte à construção de aplicações
generalizadas, on-line e possivelmente interativas.
6.1
Java
Destinado ao fim anteriormente exposto, existe em Java um pacote chamado java.sql que permite executar as funcionalidades da SQL Embutida. Segundo a própria página de documentação
do pacote, “ele fornece uma API para acesso e processamento dos dados gravados em uma
fonte de dados (usualmente um banco de dados relacional) usando a linguagem de programação
JavaTM .” Para acessar essa página, basta seguir o ponteiro http://java.sun.com/j2se/1.4.2/docs/api/index.html.
Um exemplo interessante de como executar uma query em JavaTM é mostrado abaixo no código
[14]. O exemplo foi obtido do ponteiro http://www.ils.unc.edu/ lindgren/190/mysql-jdbc/.
Ele possui um inconveniente que é a utilização de um JDBC3 driver muito especı́fico - da Terrance Zellars - para a comunicação com o banco de dados. Um JDBC driver GPL fornecido pela
MySQL AB, e portanto mais confiável, é o MySQL Connector/J. Ele é o driver JDBC oficial para
o MySQL. Mais informações sobre o driver pode ser obtida em http://www.mysql.com/products/connector/j/.
Pode-se baixá-lo gratuitamente em http://dev.mysql.com/downloads/connector/j/3.0.html
6.2
C++
Programas escritos na linguagem C++ que necessitem das funcionalidades da SQL embutida
podem utilizar-se da API fornecida no ponteiro http://mysqlcppapi.sourceforge.net/.
Um exemplo da utilização dessa API pode ser observado no código [6.2] abaixo.
7
Estudo comparativo entre MySQL e PostgreSQL
Como dito na introdução desse documento, uma análise mais profunda comparando os bancos
de dados disponı́veis no mercado se faz bastante necessária. Principalmente entre os dois mais
comuns, MySQL [MySb] e PostgreSQL [Pos], e o considerado o mais eficiente, o Oracle [Ora].
Assim, apenas para ilustrar como a performance do MySQL, que foi a base deste documento,
é relativamente boa quando comparada com o Oracle 9i, mostra-se as figuras [6] e [7] abaixo.
Referências
[C. 00]
C. J. Date. Introdução a Sistemas de Bancos de Dados. Editora Campus, tradução da
sétima edição americana edition, 2000. 1, 4.1
[E. 72]
E. F. Codd. ”further normalization of the data base relational model”. Data Base
Systems, Courant Computer Science Symposia Series 6, 1972. 3.2
[MySa] Instalação do mysql. ’Internet’, http://dev.mysql.com/doc/#Installing. 1.1
[MySb] Mysql. ’Internet’, http://www.mysql.org/. 1.1, 7
[MyS04] MySQL
A.B.
MySQL
Reference
Manual.
MySQL
http://dev.mysql.com/get/Downloads/Manual/manualA.B,
a4.pdf/from/http://www.linorg.usp.br/mysql/, 1997-2004. 4.1
3
JDBC
(Java
Database
Connectivity),
de
acrodo
com
a
JavaSofts
em
http://www.javasoft.com/products/jdbc/overview.html, ”A API JDBC define classes Java para representar conexões com banco de bados, declarações SQL, etc..”
REFERÊNCIAS
19
Fonte: eWeek em ’Server Databases Clash’
Figura 6: Benchmark de banco de dados: páginas da web retornadas por segundo.
[Ora]
Oracle. ’Internet’, http://www.oracle.com/database/. 7
[Pos]
Postgresql. ’Internet’, http://www.postgresql.org/. 1.1, 7
REFERÊNCIAS
20
Fonte: eWeek em ’Server Databases Clash’
Figura 7: Benchmark de banco de dados: as respostas mais rápidas.
REFERÊNCIAS
21
1 import j a v a . s q l . ∗ ;
2 import twz1 . j d b c . mysql . ∗ ;
4 public c l a s s TestQuery {
6
7
public TestQuery ( ) {
}
9
public s t a t i c void main ( S t r i n g a r g s [ ] ) {
11
12
13
14
15
S t r i n g u r l=” j d b c : z1MySQL :
/ / l u n a . o i t . unc . edu /CES? u s e r=alexadmin ” ;
Connection con ;
S t r i n g query = ”SELECT ∗ FROM a l e x c o u r s e ” ;
Statement stmt ;
17
18
19
20
21
22
23
try {
C l a s s . forName ( ” twz1 . j d b c . mysql . j d b c M y s q l D r i v e r ” ) ;
}
catch ( j a v a . l a n g . ClassNotFoundException e ) {
System . e r r . p r i n t ( ” ClassNotFoundException : ” ) ;
System . e r r . p r i n t l n ( e . getMessage ( ) ) ;
}
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44 }
try {
System . out . p r i n t l n ( ” Trying t o c o n n e c t . . . ” ) ;
con = DriverManager . g e t C o n n e c t i o n ( u r l , ” alexadmin ” , ”x” ) ;
System . out . p r i n t l n ( ” c o n n e c t e d ! ” ) ;
stmt = con . c r e a t e S t a t e m e n t ( ) ;
R e s u l t S e t r e s u l t = stmt . executeQuery ( query ) ;
while ( r e s u l t . next ( ) ) {
S t r i n g name = r e s u l t . g e t S t r i n g ( 1 )
+ ” ” + result . getString (2);
System . out . p r i n t l n ( name ) ;
}
stmt . c l o s e ( ) ;
con . c l o s e ( ) ;
}
catch ( SQLException ex ) {
System . e r r . p r i n t ( ” SQLException : ” ) ;
System . e r r . p r i n t l n ( ex . getMessage ( ) ) ;
}
}
Código 14: Exemplo de SQL Embutida com API para Java.
REFERÊNCIAS
22
1 #include < mysqlcppapi / mysqlcppapi . h>
2 #include < i o s t r e a m>
3 #include < iomanip>
5
6
7
8
9
10
11
12
13
14
15
int main ( ) {
// The f u l l fo r m a t f o r t h e Connection c o n s t r u c t o r i s
// Connection ( c c h a r ∗ db , c c h a r ∗ h o s t =””,
//
c c h a r ∗ u s e r =””, c c h a r ∗ passwd =””)
// You may need t o s p e c i f y some o f them i f t h e d a t a b a s e i s not on
// t h e l o c a l machine or your d a t a b a s e username i s not t h e same as
// your l o g i n name , e t c . .
try {
mysqlcppapi : : Connection con ;
con . c o n n e c t ( ) ;
con . s e l e c t d a t a b a s e ( ” m y s q l c p p d a t a ” ) ;
17
18
mysqlcppapi : : Query query = con . c r e a t e Q u e r y ( ) ;
// This c r e a t e s a q u e r y o b j e c t t h a t i s bound t o con .
20
21
22
query << ” s e l e c t ∗ from s t o c k ” ;
// You can w r i t e t o t h e q u e r y o b j e c t l i k e you would any o t h e r
// ostrem
24
25
mysqlcppapi : : R e s u l t S t o r e r e s = query . s t o r e ( ) ;
// Query : : s t o r e ( ) e x e c u t e s t h e q u e r y and r e t u r n s t h e r e s u l t s
27
28
29
c o u t << ” Query : ” << query . p r e v i e w () << e n d l ;
// Query : : p r e v i e w ( ) s i m p l y r e t u r n s a s t r i n g w i t h t h e c u r r e n t
// q u e r y s t r i n g i n i t .
31
c o u t << ” Records Found : ” << r e s . s i z e () << e n d l << e n d l ;
33
34
35
36
37
38
39
cout . s e t f ( i o s : : l e f t ) ;
c o u t << setw (17) << ” Item ”
<< setw ( 4 ) << ”Num”
<< setw ( 7 ) << ” Weight ”
<< setw ( 7 ) << ” P r i c e ”
<< ” Date ” << e n d l
<< e n d l ;
41
42
43
44
45
46
47
48
49
50
51
52
53
// The R e s u l t S t o r e c l a s s has a read−o n l y Random Access
// I t e r a t o r
f o r ( mysqlcppapi : : R e s u l t S t o r e : : i t e r a t o r i = r e s . b e g i n ( ) ;
i ! = r e s . end ( ) ; i ++)
{
mysqlcppapi : : Row row = ∗ i ;
c o u t << setw (17) << row [ 0 ]
<< setw ( 4 ) << row [ 1 ]
<< setw ( 7 ) << row [ ” w e i g h t ” ]
// you can use e i t h e r t h e i n d e x number or column
// name when r e t r i e v i n g t h e colume d a t a as
// d e m o n s t r a t e d above .
<< setw ( 7 ) << row [ 3 ]
REFERÊNCIAS
23
53
54
55
56
57
58
59
60
<< row [ 4 ] < < e n d l ;
}
}
catch ( mysqlcppapi : : ex BadQuery& e r )
{
// h a n d l e any c o n n e c t i o n or q u e r y e r r o r s t h a t may come up
c e r r << ” E r r o r : ” << e r . what () << e n d l ;
return − 1 ;
62
63
64
65
66
67
68
69
70
71
72
73 }
}
catch ( mysqlcppapi : : ex BadConversion & e r )
{
// we s t i l l need t o c a t c h bad c o n v e r s i o n s i n c a s e s o m e t h i n g
// g o e s wrong when t h e d a t a i s c o n v e r t e d i n t o s t o c k
c e r r << ” E r r o r : T r i e d t o c o n v e r t \” ”
<< e r . g e t D a t a () << ” \” t o a \” ”
<< e r . get TypeName () << ” \ ” . ” << e n d l ;
return − 1 ;
}
return 0 ;
Código 15: Exemplo de SQL Embutida com API para C++.