Outdated version of the documentation. Find the latest one here.

15.2. Lesson: Implementando o Modelo de Dado

Agora que nós cobrimos toda a teoria, vamos criar um novo banco de dados. Esta base de dados será utilizada para nossos exercícios para as aulas , que seguirão depois.

The goal for this lesson: To install the required software and use it to implement our example database.

15.2.1. Instalar PostgreSQL

Nota

Embora fora do escopo deste documento , os usuários de Mac podem instalar PostgreSQL usando ` Homebrew <http://russbrooks.com/2010/11/25/install-postgresql-9-on-os-x> ` _ . Usuários do Windows podem usar o instalador gráfico localizado aqui: `< http://www.postgresql.org/download/windows/ > ` _ . Por favor, note que a documentação assumirá os usuários estão executando QGIS no Ubuntu .

Nos termos do Ubuntu:

sudo apt-get install postgresql-9.1

Você deverá receber uma mensagem como esta

[sudo] password for qgis:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
postgresql-client-9.1 postgresql-client-common postgresql-common
Suggested packages:
oidentd ident-server postgresql-doc-9.1
The following NEW packages will be installed:
postgresql-9.1 postgresql-client-9.1 postgresql-client-common postgresql-common
0 upgraded, 4 newly installed, 0 to remove and 5 not upgraded.
Need to get 5,012kB of archives.
After this operation, 19.0MB of additional disk space will be used.
Do you want to continue [Y/n]?

Pressione: kbd : Y e : kbd : ` Enter` e aguarde o download e instalação para terminar.

15.2.2. Ajuda

PostgreSQL tem muito boa documentação` on-line < http://www.postgresql.org/docs/9.1/static/index.html > ` .

15.2.3. Crie um usuário de banco de dados

Nos termos do Ubuntu:

Depois de concluída a instalação , execute este comando para tornar o usuário postgres e , em seguida, crie um novo usuário do banco de dados

sudo su - postgres

Digite o seu log normal e senha quando solicitado ( você precisa ter os direitos do sudo ) .

Agora, no prompt bash do usuário postgres , criar o usuário do banco de dados . Certifique-se o nome de usuário corresponde ao seu nome de login unix : isto vai fazer a sua vida muito mais fácil, como postgres será automaticamente autenticado quando você está logado como usuário

createuser -d -E -i -l -P -r -s qgis

Entre com a senha quando solicitado. Você deverá usar uma senha diferente para sua senha.

O que essas opções sugerem?

-d, --createdb     role can create new databases
-E, --encrypted    encrypt stored password
-i, --inherit      role inherits privileges of roles it is a member of (default)
-l, --login        role can login (default)
-P, --pwprompt     assign a password to new role
-r, --createrole   role can create new roles
-s, --superuser    role will be superuser

agora você deve deve deixar o ambiente do usuário postgres na tela de linha de comandos bash digitando

exit

15.2.4. Verifique a nova conta

psql -l

Deve retornar algo como isso:

Name      |  Owner   | Encoding | Collation  |   Ctype    |
----------+----------+----------+------------+------------+
postgres  | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 |
template0 | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 |
template1 | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 |
(3 rows)

Digite : kbd:’q’ para sair.

15.2.5. Criar um banco de dados

O comando :kbd:’createdb’ é usado para criar um novo banco de dados. Ele deve ser executado a partir do prompt shell bash

createdb address -O qgis

Você pode verificar a existência do seu novo banco de dados usando o seguinte comando:

psql -l

Que deve retornar algo como isso:

Name      |  Owner   | Encoding | Collation  |   Ctype    |   Access privileges
----------+----------+----------+------------+------------+-----------------------
address   | qgis     | UTF8     | en_ZA.utf8 | en_ZA.utf8 |
postgres  | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 |
template0 | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 | =c/postgres: postgres=CTc/postgres
template1 | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 | =c/postgres: postgres=CTc/postgres
(4 rows)

Digite : kbd:’q’ para sair.

15.2.6. Iniciar uma sessão de tela de linha de comandos de banco de dados

Você pode conectar facilmente seu banco de dados desta forma:

psql address

Para sair da tela de linha de comando do psql, digite:

\q

Para ajuda no uso da tela de linha de comandos, digite:

\?

Para ajuda no uso dos comandos sql , digite:

\help

Para obter ajuda de um comando específico, digite (por exemplo):

\help create table

Veja também para o Psql cheat sheet - disponível online aqui.

15.2.7. Faça tabelas em SQL

Vamos começar fazendo algumas tabelas! Nós usaremos nosso ER Diagram como guia. Primeiro conecte no endereço bd:

psql address

Então crie uma tabela :kbd:’streets’

create table streets (id serial not null primary key, name varchar(50));

serial and varchar are data types. serial tells PostgreSQL to start an integer sequence (auto-number) to populate the id automatically for every new record. varchar(50) tells PostgreSQL to create a character field of 50 characters in length.

Você vai notar que o comando termina com um : kbd : ; - todos os comandos SQL devem ser encerrados desta forma. Ao pressionar enter , psql irá relatar algo como isto

NOTICE:  CREATE TABLE will create implicit sequence "streets_id_seq" for
         serial column "streets.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "streets_pkey"
         for table "streets"
CREATE TABLE

Isto quer dizer que sua tabela foi criada com sucesso, com a chave primária :kdb:’streets_pkey’ usando :kbd:’streets.is’

Nota: Se você acertar retorno sem inserir um : kbd : ; , então você receberá um aviso como este: : kbd : ` adress- # ` . Isto porque PG está esperando que você digite mais. Enter: kbd : ; para executar o seu comando .

Para ver o esquema da sua tabela, você pode fazer isso:

\d streets

Que deve mostrar algo como isso:

Table "public.streets"
 Column |         Type          |            Modifiers
--------+-----------------------+--------------------------------------
 id     | integer               | not null default
        |                       | nextval('streets_id_seq'::regclass)
 name   | character varying(50) |
Indexes:
  "streets_pkey" PRIMARY KEY, btree (id)

Para visualizar o índicae, você deve fazer isso:

select * from streets;

Que deve mostrar algo como isso:

id | name
---+------
(0 rows)

Como você pode ver, sua tabela está atualemente vazia.

15.2.7.1. Try Yourself moderate

Use a abordagem mostrada acima para fazer uma tabela chamada pessoas:

Adicione campos como número de telefone, endereço de casa, nome, etc (estes não são todos os nomes válidos: troque para validá-los). Tenha certeza que pegou a tabela com ID coluna com o mesmo tipo de informação acima.

Check your results

15.2.8. Crie chaves no SQL

O problema com a nossa solução acima é que o banco de dados não sabe que as pessoas e as ruas têm uma relação lógica. Para expressar esta relação , temos que definir uma chave estrangeira que aponta para a chave primária da tabela de ruas .

../../../_images/er-people-streets.png

Existem duas formas de fazer isso:

  • Adicione a chave após a tabela ser criada

  • Defina uma chave no momento da criação da tabela

Nossa tabela foi criada, agora vamos fazer isso primeiramente:

alter table people
  add constraint people_streets_fk foreign key (street_id) references streets(id);

Que diz a tabela people que os seus campos street_id deve corresponder uma rua válida id da tabela streets.

A forma mais usual de criar uma restrição é fazer isso quando você cria uma tebela:

create table people (id serial not null primary key,
                     name varchar(50),
                     house_no int not null,
                     street_id int references streets(id) not null,
                     phone_no varchar null);

  \d people

Após adicionar uma restrição, nosso esquema de tabela deve parecer com isso:

Table "public.people"

  Column   |         Type          |            Modifiers
-----------+-----------------------+---------------------------------
 id        | integer               | not null default
           |                       | nextval('people_id_seq'::regclass)
 name      | character varying(50) |
 house_no  | integer               | not null
 street_id | integer               | not null
 phone_no  | character varying     |
Indexes:
  "people_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
  "people_streets_fk" FOREIGN KEY (id) REFERENCES streets(id)

15.2.9. Criar Indexes in SQL

Queremos buscas relâmpago em nomes de pessoas. Para fornecer para isso, podemos criar um índice na coluna nome da tabela pessoas

create index people_name_idx on people(name);

\d people

Que resulta em:

Table "public.people"

  Column   |         Type          |                      Modifiers
-----------+-----------------------+-----------------------------------
 id        | integer               | not null default nextval
           |                       | ('people_id_seq'::regclass)
 name      | character varying(50) |
 house_no  | integer               | not null
 street_id | integer               | not null
 phone_no  | character varying     |
Indexes:
 "people_pkey" PRIMARY KEY, btree (id)
 "people_name_idx" btree (name)    <-- new index added!
Foreign-key constraints:
 "people_streets_fk" FOREIGN KEY (id) REFERENCES streets(id)

15.2.10. Eliminaçãod e tabelas no SQL

Se você quiser se livrar de uma tabela você pode usar o :kbd: comando ‘drop`

drop table streets;

Nota

No nosso exemplo atual, o comando acima não iria funcionar. Por que não? : ref: ` Veja porque <banco de dados conceitos -5 >

drop table people;

Nota

Se você realmente fez entrar nesse comando e deixou arrastar a tabela people, agora seria um bom momento para reconstruí-la, como você vai precisar dela para os próximos exercícios.

15.2.11. Uma palavra sobre pgAdmin III

Estamos mostrando-lhe os comandos SQL do prompt de psql porque é uma forma muito útil para aprender sobre bancos de dados. No entanto, existem maneiras mais rápidas e mais fáceis de fazer muito do que estamos mostrando. Instale pgAdmin III e você pode criar, arrastar, etc alterar tabelas usando ‘apontar e clicar’ em operações na GUI.

Sob o Ubuntu, você pode instalar isso desta forma:

sudo apt-get install pgadmin3

pgAdmin III serão abordados em mais detalhes no outro módulo.

15.2.12. In Conclusion

Agora você já viu como criar um novo banco de dados, a partir do zero.

15.2.13. What’s Next?

Em seguida, você vai aprender como usar os DBMS para adicionar novos dados.