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

15.2. Lesson: Implementarea Modelului de Date

Acum, că am acoperit toată teoria, haideți să creăm o bază de date nouă. Această bază de date va fi utilizată în exercițiile noastre din lecțiile care vor urma.

Scopul acestei lecții: De a instala soft-ul necesar și de a-l utiliza la implementarea bazei de date exemplu.

15.2.1. Instalare PostgreSQL

Note

Deși în afara scopului acestui document, utilizatorii de Mac pot instala PostgreSQL folosind Homebrew. Utilizatorii Windows pot folosi instalatorul grafic situat aici: http://www.postgresql.org/download/windows/. Vă rugăm să rețineți că documentația presupune că utilizatorii rulează aplicația QGIS pe Ubuntu.

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.

15.2.2. Ajutor

PostgreSQL are o foarte bună documentație on-line.

15.2.3. Crearea unui utilizator pentru baza de date

Pe Ubuntu:

După finalizarea instalării, executați această comandă pentru a deveni utilizatorul postgres, și pentru a crea un nou utilizator pentru baza de date:

sudo su - postgres

Introduceți parola când vi se solicită (aveți nevoie de drepturi sudo).

Acum, la promptul utilizatorului postgres, creați utilizatorul pentru baza de date. Asigurați-vă că numele utilizatorului este același cu cel de autentificare unix: vă va face viața mult mai ușoară, pentru că postgres vă va autentifica automat atunci când sunteți autentificat ca acel utilizator:

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

Acum ar trebui să ieșiți din consola bash a utilizatorului postgres, tastând:

exit

15.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)

Tastați q pentru a ieși.

15.2.5. Crearea unei baze de date

Comanda createdb este utilizată pentru a crea o nouă bază de date. Ar trebui să o rulați din linia de comandă a utilitarului bash:

createdb address -O qgis

Puteți verifica existența noii baze de date, utilizând această comandă:

psql -l

Care ar trebui să returneze ceva de genul următor:

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)

Tastați q pentru a ieși.

15.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

Vedeți, de asemenea, Fișa de indicii Psql - disponibilă online aici.

15.2.7. Crearea Tabelelor SQL

Să adăugăm niște tabele! Vom folosi Diagrama noastră ER ca și ghid. Pentru început, să ne conectăm la baza de adrese:

psql address

Pentru a crea tabela streets:

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.

Pentru a vizualiza schema tabelelor dvs., puteți proceda astfel:

\d streets

Care ar trebui să arate ceva de genul următor:

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)

Pentru a vizualiza conținutul tabelelor dvs., puteți proceda astfel:

select * from streets;

Care ar trebui să arate ceva de genul următor:

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

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

15.2.7.1. Try Yourself moderate

Folosiți abordarea de mai sus pentru a crea un tabel denumit people:

Adăugați câmpuri ca număr de telefon, adresă de acasă, nume etc. (acestea nu sunt toate nume valide: schimbați-le pentru a deveni valide). Asigurați-vă că îi adăugați tabelului o coloană ID cu același tip de date ca și mai sus.

Verificați-vă rezultatele

15.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.

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

Sunt două moduri de a face asta:

  • Adăugați cheia după crearea tabelului

  • Definiți cheia la momentul creării tabelului

Tabelul nostru a fost deja creat, deci să alegem prima variantă:

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.

O formă mai obișnuită de a crea o constrângere este să o faceți la crearea tabelului:

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

După adăguarea constrângerii, schema noastră arată așa:

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. Crearea de indecși în SQL

Dorim căutări extrem de rapide după numele oamenilor. Pentru a obține asta vom crea un index pentru coloana de nume din tabelul oamenilor:

create index people_name_idx on people(name);

\d people

Ceea ce produce:

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. Ștergerea Tabelelor în SQL

Dacă doriți să scăpați de un tabel puteți folosi comanda drop:

drop table streets;

Note

În exemplul curent, comanda de mai sus nu va funcționa. De ce? Vezi

drop table people;

Note

Dacă ați introdus acea comandă și ați șters tabelul people, ar fi un moment bun să îl refaceți, deoarece îl veți folosi în exercițiile următoare.

15.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.

Pe Ubuntu, îl puteți instala așa:

sudo apt-get install pgadmin3

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

15.2.12. In Conclusion

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

15.2.13. What’s Next?

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