Importante

A tradução é um esforço comunitário você pode contribuir. Esta página está atualmente traduzida em 81.48%.

15.2. Lesson: Implementing the Data Model

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.

A meta para esta lição: Instalar o software necessário e usá-lo para implementar o nosso banco de dados de exemplo .

15.2.1. Instalar PostgreSQL

Nota

Você pode encontrar pacotes PostgreSQL e instruções de instalação para seu sistema operacional em https://www.postgresql.org/download/. Observe que a documentação assumirá que os usuários estão executando o QGIS no Ubuntu.

Nos termos do Ubuntu:

sudo apt install postgresql-9.1

Você deveria ver uma mensagem assim:

[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 Y e Enter e aguarde o download e instalação terminar.

15.2.2. Ajuda

PostgreSQL tem uma documentação online muito boa.

15.2.3. Crie um usuário de banco de dados

Nos termos do Ubuntu:

Após a conclusão da instalação, execute este comando para se tornar o usuário do postgres e 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 do bash do usuário do postgres, crie o usuário do banco de dados. Verifique se o nome de usuário corresponde ao seu nome de usuário unix: isso facilitará sua vida, pois o postgres o autenticará automaticamente quando você estiver logado como esse 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 deixar o ambiente do shell bash do usuário do postgres 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 Q para sair.

15.2.5. Criar um banco de dados

O comando createdb é usado para criar um novo banco de dados. Deve ser executado no prompt do bash shell:

createdb address -O qgis

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

psql -l

Que deve retornar algo como:

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 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 a folha de dicas Psql.

15.2.7. Faça tabelas em SQL

Vamos começar a fazer algumas tabelas! Usaremos nosso diagrama de ER como guia. Primeiro, conecte-se ao endereço db:

psql address

Then create a streets table:

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.

You will notice that the command ends with a ; - all SQL commands should be terminated this way. When you press Enter, psql will report something like this:

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

That means your table was created successfully, with a primary key streets_pkey using streets.id.

Note: If you hit return without entering a ;, then you will get a prompt like this: address-#. This is because PostgreSQL is expecting you to enter more. Enter ; to run your command.

Para visualizar seu esquema de tabela, você pode fazer isso:

\d streets

Que deveria mostrar algo como isto:

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 conteúdo da sua tabela, você pode fazer o seguinte:

select * from streets;

Que deveria mostrar algo como isto:

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

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

Try Yourself: ★★☆

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

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.

Responda

O SQL necessário para criar a tabela de pessoas correta é:

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

O esquema para a tabela (digite \\d pessoas) fica assim:

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)

Para fins de ilustração, omitimos propositalmente a restrição fkey.

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 já foi criada, então vamos fazê-lo da primeira maneira:

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

That tells the people table that its street_id fields must match a valid street id from the streets table.

A maneira mais comum de criar uma restrição é fazer isso quando você cria a tabela:

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 a inclusão da restrição, nosso esquema de tabela fica assim agora:

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 Índices em SQL

Queremos pesquisas rápidas em nomes de pessoas. Para isso, podemos criar um índice na coluna de nome da nossa tabela de pessoas:

create index people_name_idx on people(name);

\d people

O 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. Descartando tabelas em SQL

If you want to get rid of a table you can use the DROP command:

drop table streets;

Em nosso exemplo atual, o comando acima não funcionaria. Por que não?

Responda

The reason the DROP command would not work in this case is because the people table has a Foreign Key constraint to the streets table. This means that dropping (or deleting) the streets table would leave the people table with references to non-existent streets data.

It is possible to ‘force’ the streets table to be deleted by using the CASCADE command, but this would also delete the people and any other table which had a relationship to the streets table. Use with caution!

If you used the same drop table command on the people table, it would be successful:

drop table people;

Nota

If you actually did enter that command and dropped the people table, now would be a good time to rebuild it, as you will need it in the next exercises.

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ê poderá criar, descartar, alterar etc, tabelas usando ‘apontar e clicar’ em operações com uma GUI.

No Ubuntu, você pode instalar assim:

sudo apt install pgadmin3

O pgAdmin III será abordado em mais detalhes em 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.