16.2. Lesson: Implementarea Modelului de Date¶

16.2.1. Instalare PostgreSQL¶

Pe Ubuntu:

sudo apt-get install postgresql-9.1

Veți obține un mesaj de genul ăsta:

[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]?

Apăsați Y și Enter apoi așteptați ca descărcarea și instalarea să se încheie.

16.2.2. Ajutor¶

PostgreSQL has very good online documentation.

16.2.3. Crearea unui utilizator pentru baza de date¶

Pe Ubuntu:

After the installation is complete, run this command to become the postgres user and then create a new database user:

sudo su - postgres

Introduceți parola când vi se solicită (aveți nevoie de drepturi 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

Introduceți o parolă când vi se solicită. Ar trebui să utilizați o parolă diferită pentru parola contului dumneavoastră.

Ce reprezintă aceste opțiuni?

-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. Verificați noul cont¶

psql -l

Ar trebui să returneze ceva de genul următor:

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. Crearea unei baze de date¶

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. Pornirea unei sesiuni către baza de date, din linia de comandă¶

Vă puteți conecta ușor la baza de date, procedând astfel:

psql address

Pentru a ieși din mediul bazei de date psql, tastați:

\q

Pentru ajutor în utilizarea liniei de comandă, tastați:

\?

Pentru ajutor în utilizarea comenzii SQL, tastați:

\help

Pentru a obține ajutor pentru o anumită comandă, tastați (de exemplu):

\help create table

See also the Psql cheat sheet - available online here.

16.2.7. Crearea Tabelelor SQL¶

Let’s start making some tables! We will use our ER Diagram as a guide. First, connect to the address db:

psql address

Apoi creați o tabelă a străzilor:

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

serial și varchar sunt tipuri de date. serial îi spune lui PostgreSQL să pornească o secvență (generator automat) pentru completarea automată a id pentru fiecare înregistrare nouă. varchar(50) îi spune lui PostgreSQL să creeze un câmp de caractere de lungime 50.

Veți remarca faptul că comanda se termină cu ; - toate comenzile SQL trebuie terminate în acest fel. Când apăsați Enter, psql va raporta ceva de genul:

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

Asta înseamnă că tabelul a fost creeat cu succes, având cheia primară streets_pkey care folosește streets.id.

Notă: Dacă apăsați enter fără a introduce ;, veți obține un prompt de tipul: address-#. Aceasta deoarece PG așteaptă să mai introduceți ceva. Introduceți ; pentru a executa comanda.

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)

După cum puteți vedea, tabela noastră este vidă, în mod curent.

16.2.8. Crearea Cheilor în SQL¶

Problema cu soluția noastră de mai sus este că baza de date nu știe că oamenii și străzile au o relație logică. Pentru a exprima această relație va trebui să definim o cheie externă care face legătura cu cheia primară a tabelului de străzi.

Sunt două moduri de a face asta:

• Adăugați cheia după crearea tabelului

• Definiți cheia la momentul creării tabelului

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);

Asta spune tabelului people că valoarea câmpurilor street_id trebuie să fie o valoare validă id din tabelul 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. Crearea de indecși în 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. Ștergerea Tabelelor în SQL¶

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

drop table streets;

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

drop table people;

16.2.11. Câteva cuvinte despre pgAdmin III¶

Prezentăm comenzile SQL de la promptul psql pentru că este un mod foarte util de a învăța despre bazele de date. Cu toate acestea, există metode mai rapide și mai ușoare de a face ce am prezentat. Instalați pgAdmin III și veți putea crea, șterge, modifica etc. tabele utilizănd operații ‘point and click’ într-un GUI.

Under Ubuntu, you can install it like this:

sudo apt-get install pgadmin3

pgAdmin III va fi acoperit mai detaliat în alt modul.

16.2.12. In Conclusion¶

Ați văzut cum să creați o bază de date complet nouă, pornind de la zero.

16.2.13. What’s Next?¶

În continuare veți învăța cum să folosiți DBMS-ul pentru adăguarea datelor.