16.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.
A meta para esta lição: Instalar o software necessário e usá-lo para implementar o nosso banco de dados de exemplo .
16.2.1. Instalar PostgreSQL¶
Nota
Although outside the scope of this document, Mac users can install PostgreSQL using Homebrew. Windows users can use the graphical installer. Please note that the documentation will assume users are running QGIS under Ubuntu.
Nos termos do Ubuntu:
sudo apt install postgresql-9.1
You should get a message like this:
[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.
16.2.3. Crie um usuário de banco de dados¶
Nos termos do Ubuntu:
After the installation is complete, run this command to become the postgres user and then create a new database user:
sudo su - postgres
Digite o seu log normal e senha quando solicitado ( você precisa ter os direitos do sudo ) .
Now, at the postgres user’s bash prompt, create the database user. Make sure the user name matches your unix login name: it will make your life much easier, as postgres will automatically authenticate you when you are logged in as that user:
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
Now you should leave the postgres user’s bash shell environment by typing:
exit
16.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)
Type Q to exit.
16.2.5. Criar um banco de dados¶
The createdb command is used to create a new database. It should be run
from the bash shell prompt:
createdb address -O qgis
You can verify the existence of your new database by using this command:
psql -l
Which should return something like this:
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)
Type Q to exit.
16.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
See also the Psql cheat sheet.
16.2.7. Faça tabelas em SQL¶
Let’s start making some tables! We will use our ER Diagram as a guide. First, connect to the address db:
psql address
Then create a streets table:
create table streets (id serial not null primary key, name varchar(50));
serial e varchar são tipos de dados. serial diz para o PostgreSQL iniciar uma sequência inteira (auto-número) para preencher o id automaticamente para cada novo registro. :kbd:` varchar(50)` diz para o PostgreSQL criar um campo de caracteres de 50 caracteres de comprimento.
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
Isto quer dizer que sua tabela foi criada com sucesso, com a chave primária streets_pkey usando streets.id.
Nota: Se você apertar “enter” sem inserir ;, então você receberá um aviso como este: address-#. Isto porque o PG está esperando que você digite mais. Digite ; para executar o seu comando .
To view your table schema, you can do this:
\d streets
Which should show something like this:
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)
To view your table contents, you can do this:
select * from streets;
Which should show something like this:
id | name
---+------
(0 rows)
Como você pode ver, sua tabela, no momento, está vazia.
16.2.7.1. 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.
16.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 .
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
Our table has already been created, so let’s do it the first way:
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.
The more usual way to create a constraint is to do it when you create the table:
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
After adding the constraint, our table schema looks like this now:
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)
16.2.9. Criar Índices em SQL¶
We want lightning fast searches on peoples names. To provide for this, we can create an index on the name column of our people table:
create index people_name_idx on people(name);
\d people
Which results in:
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)
16.2.10. Descartando tabelas em SQL¶
If you want to get rid of a table you can use the drop command:
drop table streets;
Nota
No nosso exemplo atual, o comando acima não iria funcionar. Por que não? : ref:Veja porque <database-concepts-5>
If you used the same drop table command on the people table, it would be successful:
drop table people;
Nota
Se você realmente entrou com esse comando e descartou a tabela people, agora seria um bom momento para reconstruí-la, já que você vai precisar dela para os próximos exercícios.
16.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.
Under Ubuntu, you can install it like this:
sudo apt install pgadmin3
O pgAdmin III será abordado em mais detalhes em outro módulo.
16.2.12. In Conclusion¶
Agora você já viu como criar um novo banco de dados, a partir do zero.
16.2.13. What’s Next?¶
Em seguida, você vai aprender como usar os DBMS para adicionar novos dados.