Bases de Dados


Departamento
de
Engenharia
Informática
2009/2010
Bases
de
Dados
1º
semestre
Lab
1:
Introdução
ao
ambiente
O
ficheiro
bank.sql
contém
um
conjunto
de
instruções
SQL
para
criar
a
base
de
dados
de
exemplo
ilustrada
na
Figura
1.
Figura
1.
Base
de
dados
de
exemplo
Para
criar
a
base
de
dados
é
necessário
criar
as
tabelas
e
carregar
os
registos
de
cada
tabela.
A
criação
das
tabelas
é
feita
com
recurso
à
instrução
CREATE
TABLE.
Por
exemplo,
a
tabela
de
clientes
pode
ser
criada
com
a
seguinte
instrução:
create table customer
(customer_name
varchar(255)
customer_street
varchar(255)
customer_city
varchar(255)
primary key(customer_name));
not null unique,
not null,
not null,
Esta
instrução
especifica
o
nome
da
tabela,
os
nomes
das
três
colunas,
o
tipo
de
cada
coluna,
e
ainda
restrições
tais
como
os
valores
não
poderem
ser
NULL
e
o
facto
da
chave
primária
da
tabela
ser
o
nome
do
cliente.
IST/DEI
Pág.
1
de
6
BD
Os
registos
de
cada
tabela
são
carregados
através
de
instruções
do
tipo
INSERT.
Por
exemplo:
insert into customer values ('Jones','Main','Harrison');
em
que
são
especificados,
respectivamente,
os
valores
de
cada
coluna
pela
mesma
ordem
em
que
estes
foram
definidos
aquando
da
criação
da
tabela1.
Esta
instrução
resulta
na
criação
de
um
novo
registo
na
tabela
de
clientes.
Note‐se
que
o
ficheiro
bank.sql
inclui
instruções
para
inserir
mais
registos
na
base
de
dados
do
que
aqueles
que
se
encontram
exemplificados
na
Figura
1.
Estes
registos
serão
usados
para
realizar
vários
testes
sobre
a
base
de
dados.
Em
aulas
de
laboratório
futuras,
iremos
utilizar
esta
base
de
dados
para
demonstrar
vários
dos
conceitos
da
disciplina.
Configurações
Para
começar
a
usar
o
sistema
Postgres
disponível
na
infra‐estrutura
do
IST,
deverá
proceder
aos
seguintes
passos:
1. Aceda
à
página
de
self‐service
do
CIIST:
https://ciist.ist.utl.pt/servicos/self_service/index.php
2. Active
os
serviços
shell,
web
e
cgi
3. Abra
uma
sessão
SSH
para
o
cluster
sigma.ist.utl.pt
(em
Windows
pode
usar
um
dos
clientes
“SSH
Secure
Shell”
ou
“PuTTY”;
em
Linux
deve
usar
o
comando
“ssh”)
4. Uma
vez
ligado
ao
cluster
sigma,
execute
o
comando:
psql_reset
5. Ligue‐se
ao
Postrgres
com
o
comando:
psql
‐h
db.ist.utl.pt
6. Use
a
password
dada
pelo
psql_reset
Utilização
da
linha
de
comando
Em
geral
todos
os
sistemas
de
gestão
de
base
de
dados
dispõem
de
uma
linha
de
comando
através
da
qual
é
possível
executar
instruções
SQL
e
também
outros
comandos
de
administração
e
manutenção
do
sistema.
7. Faça
download
do
ficheiro
bank.sql
que
acompanha
este
guia
de
laboratório.
1
Existem
outras
variantes
da
instrução
INSERT
com
as
quais
é
possível
especificar
os
valores
por
outra
ordem,
ou
especificar
apenas
alguns
dos
valores
pretendidos
deixando
os
restantes
a
NULL
ou
com
o
valor
por
omissão
(default
value,
que
neste
caso
não
foi
especificado)
IST/DEI
Pág.
2
de
6
BD
8. Usando
um
cliente
de
SFTP
(“WinSCP”
em
Windows;
“scp”
em
Linux)
coloque
o
ficheiro
bank.sql
num
directório
dentro
da
sua
conta
no
cluster
sigma.ist.utl.pt
9. Abra
uma
sessão
SSH
para
o
cluster
sigma.ist.utl.pt
(ou
utilize
a
sessão
anterior)
e
navegue
até
ao
directório
onde
guardou
o
ficheiro.
10. Invoque
o
programa
psql
com
o
comando:
psql
‐h
db.ist.utl.pt
11. Uma
vez
dentro
do
sistema,
utilize
o
comando
\h
para
obter
informação
sobre
todos
os
comandos
SQL
disponíveis.
Use
\q
para
sair.
12. Utilize
o
comando
\?
para
obter
informação
sobre
todos
os
comandos
de
administração
disponíveis.
13. Se
precisar
de
sair
do
programa,
pode
utilizar
a
qualquer
momento
o
comando:
\q
14. Na
lista
de
comandos
de
administração
terá
notado
a
existência
de
um
comando
nesta
forma:
\i
FILE
execute
commands
from
file
É
este
commando
que
vamos
utilizar
para
executar
as
instruções
no
ficheiro
bank.sql.
15. Para
se
ligar
à
sua
base
de
dados,
utilize
o
comando:
\c
istxxxxx
(onde
istxxxxx
é
o
seu
nome
de
utilizador).
Pretendem‐se
executar
as
instruções
do
ficheiro
bank.sql
na
sua
base
de
dados.
16. De
seguida,
execute
o
comando:
\i
bank.sql
O
Postgres
produz
algumas
mensagens
à
medida
que
executa
as
instruções
do
ficheiro.
17. Para
listar
as
tabelas
da
base
de
dados,
use
o
comando:
\d
18. Após
a
ligação
à
base
de
dados
pode
fazer
algumas
consultas,
nomeadamente:
• ver
a
lista
completa
de
clientes:
SELECT
*
FROM
customer;
• ver
a
lista
completa
de
contas:
SELECT
*
FROM
account;
Em
aulas
futuras
verá
como
obter
a
resposta
a
consultas
mais
complicadas.
19. O
tempo
que
o
sistema
demora
a
responder
a
algumas
consultas
é
um
factor
importante
quando
o
volume
de
dados
é
considerável.
Execute
o
comando:
\timing
IST/DEI
Pág.
3
de
6
BD
20. Repita
as
consultas
anteriores.
Passará
a
ver
o
tempo
que
o
sistema
demora
a
responder.
Obter
informação
sobre
o
esquema
de
uma
base
de
dados
Para
uma
base
de
dados
existente
no
sistema
mas
sobre
a
qual
não
haja
documentação,
é
possível
usar
instruções
especiais
para
obter
informação
sobre
os
artefactos
existentes
no
sistema
(bases
de
dados,
tabelas,
vistas,
funções,
etc.).
Geralmente
estes
mecanismos
são
proprietários
e
diferentes
para
cada
sistema.
No
Postgres
essas
funcionalidades
estão
disponíveis
através
do
comando
\d
e
variantes.
21. Utilize
o
comando
\l
(‘L’
pequeno)
para
obter
de
todas
as
bases
de
dados
existentes
no
sistema.
22. Para
se
ligar
à
sua
base
de
dados,
utilize
o
comando:
\c
istxxxxx
(onde
istxxxxx
é
o
seu
nome
de
utilizador)
23. Obtenha
informação
sobre
as
tabelas
de
clientes
e
contas:
\d
customer
\d
account
Confirme
que
esta
descrição
corresponde
às
especificações
com
que
essas
tabelas
foram
criadas.
24. Use
o
comando
\q
para
sair
do
sistema
e
voltar
ao
terminal.
Utilização
da
ferramenta
de
administração
A
linha
de
comando
é
geralmente
a
forma
de
ter
acesso
a
todas
as
funcionalidades
do
sistema.
No
entanto,
para
um
conjunto
limitado
de
tarefas
é
possível
recorrer
a
ferramentas
mais
simples.
O
sistema
Postgres
inclui
uma
ferramenta
de
administração
designada
pgAdmin
III.
25. No
PC
do
laboratório,
abra
a
aplicação
pgAdmin
III
(se
estiver
a
usar
Linux
pode
invocar
a
aplicação
pgAdmin
com
o
comando
“pgadmin3”)
26. Clique
no
botão
de
adicionar
ligação
e
escreva
db.ist.utl.pt
como
nome
e
sistema
anfitrião.
Especifique
também
o
nome
de
utilizador
e
password
(dada
por
psql_reset)
IST/DEI
Pág.
4
de
6
BD
27. Expanda
a
árvore
e
localize
a
sua
base
de
dados,
bem
como
as
tabelas,
que
foram
criadas
quando
executou
\i
bank.sql
na
linha
de
comando.
28. No
menu
de
ferramentas,
abra
a
ferramenta
“query
tool”
(“ferramenta
de
consulta”
se
o
sistema
estiver
em
português)
29. Escreva
as
mesmas
consultas
que
realizou
anteriormente
(p.ex.
SELECT
*
FROM
customer;)
Após
escrever
o
comando
SQL
use
o
botão
“executar
consulta”.
Observe
os
resultados.
30. Repita
as
mesmas
consultas
mas
agora
utilize
o
botão
“explicar
consulta”.
31. Repare
que
o
sistema
mostra
graficamente
o
plano
de
execução
que
seguiu
para
responder
à
consulta.
Para
perguntas
simples
como
esta,
foi
necessária
apenas
uma
operação
de
pesquisa
sequencial.
Mais
para
a
frente
na
disciplina
veremos
como
o
sistema
gera
planos
para
responder
a
perguntas
mais
complexas.
Gerar
o
script
de
uma
base
de
dados
A
base
de
dados
de
exemplo
foi
criada
com
recurso
a
um
script
(ficheiro
de
texto)
com
uma
série
de
instruções
SQL.
Durante
a
utilização
da
base
de
dados
serão
adicionados,
removidos
e
alterados
registos,
de
modo
que
o
estado
da
base
de
dados
é
o
resultado
das
operações
realizadas.
A
qualquer
momento
é
possível
gerar
um
script
quer
para
efeitos
de
backup,
quer
para
efeitos
de
replicação
da
base
de
dados
noutro
sistema.
Em
geral
existem
mecanismos
próprios
que
são
mais
adequados
para
esse
efeito,
mas
o
script
de
criação
de
uma
base
de
dados
é
útil
em
muitas
ocasiões.
32. Abra
uma
sessão
SSH
para
o
cluster
sigma.ist.utl.pt
(ou
utilize
a
sessão
anterior)
e
invoque
o
programa
pg_dump
com
o
comando:
pg_dump
‐h
db.ist.utl.pt
33. Verá
que
o
programa
produz
o
script
no
terminal.
Vamos
redireccionar
o
output
de
forma
a
guardar
o
script
num
ficheiro.
Invoque
novamente
o
programa
pg_dump
com
os
seguinte
parâmetros:
pg_dump
‐h
db.ist.utl.pt
>
backup.sql
onde
backup.sql
é
o
nome
do
ficheiro
que
será
criado.
IST/DEI
Pág.
5
de
6
BD
34. Para
ver
o
conteúdo
do
ficheiro
backup.sql
use
o
comando:
cat
backup.sql
35. Compare
o
conteúdo
de
backup.sql
com
bank.sql.
Alguns
elementos
não
existiam
no
ficheiro
original.
Contudo,
observe
que
apesar
de
usar
instruções
diferentes
este
script
criará
uma
base
de
dados
idêntica
à
original.
Lista
de
comandos
Postgres
utilizados
\h
\q
\?
\i
file
\c
database
\d
\timing
\l
\d
table
Obter
informação
sobre
todos
os
comandos
SQL
disponíveis.
Sair
da
linha
de
comando
do
Postgres.
Obter
informação
sobre
todos
os
comandos
de
administração.
Executar
os
comandos
contidos
no
ficheiro
indicado.
Ligar
à
base
de
dados
indicada.
Listas
as
tabelas
da
base
de
dados
actual.
Mostrar
o
tempo
de
execução
das
consultas.
Listar
todas
as
bases
de
dados
existentes
no
sistema.
Obter
informação
sobre
a
estrutura
de
uma
tabela.
IST/DEI
Pág.
6
de
6