Bases de Dados 1º semestre

 Departamento de Engenharia Informática 2010/2011 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 Postgres 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õe 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 15. É este commando que vamos utilizar para executar as instruções no ficheiro bank.sql. Execute o comando: \i bank.sql O Postgres produz algumas mensagens à medida que executa as instruções do ficheiro. 16. Para listar as tabelas da base de dados, use o comando: \d 17. 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. 18. 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 19. Repita as consultas anteriores. Passará a ver o tempo que o sistema demora a responder. IST/DEI Pág. 3 de 6 BD 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 as respectivas tabelas. Geralmente estes mecanismos são proprietários e diferentes para cada sistema. No Postgres estas funcionalidades estão disponíveis através do comando \d e variantes. 20. Utilize o comando \l (‘L’ pequeno) para obter informação de todas as bases de dados existentes no sistema. 21. Para se ligar à sua base de dados, utilize o comando: \c istxxxxx (onde istxxxxx é o seu nome de utilizador) 22. Obtenha informação sobre as tabelas de clientes e contas: \d customer \d account Confirme que a estrutura dessas tabelas está de acordo com as instruções que foram dadas no ficheiro bank.sql. 23. 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 certas tarefas é possível recorrer a outras ferramentas. O sistema Postgres inclui uma ferramenta de administração designada por pgAdmin III. 24. 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”) 25. 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) 26. 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. 27. No menu de ferramentas, abra a ferramenta “query tool” (“ferramenta de consulta” se o sistema estiver em português) IST/DEI Pág. 4 de 6 BD 28. 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. 29. Repita as mesmas consultas mas agora utilize o botão “explicar consulta”. 30. 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. Utilização de schemas No sistema Postgres uma base de dados pode conter vários schemas e cada schema pode conter várias tabelas, conforme ilustrado na figura seguinte. Sistema Base de Schema Tabela Postgres Dados
Em particular, no sistema Postgres disponível em db.ist.utl.pt a base de dados tem um nome idêntico ao nome de utilizador no cluster sigma (istxxxxxx). Além disso, as tabelas são criadas num schema público (public). Os dados contidos nas tabelas não ficam públicos, mas é possível a outros utilizadores ficar a saber que tabelas existem e as respectivas colunas e tipos de dados. Sistema Base de Schema
Tabela (public)
Postgres Dados (istxxxxxx) Para evitar que outros utilizadores possam obter informação sobre as tabelas, é necessário criar as tabelas num schema diferente do public. Recomenda‐se que o novo schema a criar tenha um nome idêntico ao nome de utilizador (istxxxxxx), pois deste modo o sistema Postgres conseguirá encontrar as tabelas automaticamente. Assim, o objectivo é criar um novo schema e mover as tabelas para esse novo schema. Sistema Base de Schema
Tabela (istxxxxxx)
Postgres Dados (istxxxxxx) IST/DEI Pág. 5 de 6 BD 31. Abra uma sessão SSH para o cluster sigma.ist.utl.pt e ligue‐se ao Postgres com o comando: psql ‐h db.ist.utl.pt 32. Execute o comando: CREATE SCHEMA istxxxxxx; em que istxxxxxx é o seu nome de utilizador. 33. Mova as tabelas para o novo schema com os seguintes comandos: ALTER TABLE customer SET SCHEMA istxxxxxx; ALTER TABLE branch SET SCHEMA istxxxxxx; ALTER TABLE account SET SCHEMA istxxxxxx; ALTER TABLE depositor SET SCHEMA istxxxxxx; ALTER TABLE loan SET SCHEMA istxxxxxx; ALTER TABLE borrower SET SCHEMA istxxxxxx; em que istxxxxxx é o seu nome de utilizador. 34. Abra a aplicação pgAdmin III e confirme que as tabelas se encontram agora debaixo do novo schema. Neste momento já não há tabelas públicas. No sistema Postgres, a forma completa para identificar uma tabela é: database.schema.table Para tabelas na base de dados a que o utilizador está ligado, é possível usar apenas: schema.table E se o utilizador especificar apenas table (como faremos habitualmente) então o sistema tentará localizar a tabela $user.table (em que $user é o nome de utilizador) ou, se esta não existir, a tabela public.table (em que public é o schema público). Resumo dos 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