15.1. Lesson: Introducere în Baze de date

Înainte de a utiliza PostgreSQL, să ne asigurăm de terenul nostru prin acoperirea teoriei generale a bazelor de date. Nu va fi nevoie să introduceți codul exemplificat; acesta este prezent doar în scopuri ilustrative.

Scopul acestei lecții: De a înțelege conceptele fundamentale ale bazelor de date.

15.1.1. Ce este o bază de date?

O bază de date constă într-o colecție organizată de date, pentru una sau mai multe utilizări, de obicei în formă digitală. - Wikipedia

Un sistem de management al bazelor de date (DBMS) este format din software care operează bazele de date, oferind depozitare, acces, securitate, backup și alte facilități. - Wikipedia

15.1.2. Tabele

În bazele de date tradiționale și în bazele de date tip fișier, o tabelă este un set de elemente de date (valori) care este organizat utilând un model de coloane verticale (care sunt identificate prin numele lor) și de rânduri orizontale. O tableă are un număr specificat de coloane, dar poate avea oricâte rânduri. Fiecare rând este identificat prin valorile unui anumit subset de coloane care a fost identificat ca o potențială cheie. - Wikipedia

 id | name  | age
----+-------+-----
  1 | Tim   |  20
  2 | Horst |  88
(2 rows)

În bazele de date SQL, o tabelă este, de asemenea, cunoscută ca relație.

15.1.3. Coloane / Câmpuri

O coloană este un set de valori de date având un anume tip simplu, câte una pentru fiecare rând din tabel. Coloanele funizează structura pe baza căreia se compune fiecare rând. Termenul de câmp este utilizat interschimbabil cu coloană, deși mulți consideră că este mai corect să se utilizeze câmp (sau valoare a câmpului) când este vorba de elementul care există la intersecția dintre o coloană și un rând. - Wikipedia

O coloană:

| name  |
+-------+
| Tim   |
| Horst |

Un câmp:

| Horst |

15.1.4. Înregistrări

O înregistrare reprezintă informația stocată într-un rând din tabelă. Fiecare înregistrare va avea câte un câmp pentru fiecare dintre coloanele tabelei.

2 | Horst |  88  <-- one record

15.1.5. Tipuri de date

Tipurile de date restrâng tipurile de informații care pot fi stocate într-o coloană. - Tim and Horst

Există mai multe feluri de tipuri de date. Să ne concentrăm pe cele mai comune:

• String - to store free-form text data

• Integer - to store whole numbers

• Real - to store decimal numbers

• Date - to store Horst’s birthday so no one forgets

• Boolean - to store simple true/false values

You can tell the database to allow you to also store nothing in a field. If there is nothing in a field, then the field content is referred to as a «null» value:

insert into person (age) values (40);

select * from person;

Rezultat:

id | name  | age
---+-------+-----
 1 | Tim   |  20
 2 | Horst |  88
 4 |       |  40  <-- null for name
(3 rows)

There are many more datatypes you can use - check the PostgreSQL manual!

15.1.6. Modelarea unei Baze de Date cu Adrese

Să folosim un studiu de caz simplu, pentru a vedea cum este construită o bază de date. Dorim să creăm o bază de date cu adrese.

Try Yourself

Notați proprietățile care alcătuiesc o adresă simplă și pe care am dori să le stocăm în baza noastră de date.

Answer

For our theoretical address table, we might want to store the following properties:

House Number
Street Name
Suburb Name
City Name
Postcode
Country

When creating the table to represent an address object, we would create columns to represent each of these properties and we would name them with SQL-compliant and possibly shortened names:

house_number
street_name
suburb
city
postcode
country

Structura Adresei

Propietățile care descriu o adresă sunt coloanele. Tipul de informație stocat în fiecare coloană este tipul de date al acesteia. În secțiunea următoare vom analiza tabela noastră conceptuală de adrese pentru a vedea cum o putem înbunătăți!

15.1.7. Teoria Bazelor de Date

Procesul de creare a unei baze de date presupune crearea unui model al lumii reale; luând concepte din lumea reală și reprezentându-le, ca entități, în baza de date.

15.1.8. Normalizarea

Un concept de bază al bazelor de date este evitarea duplicării / redundanței datelor. Procesul eliminării redundanței dintr-o bază de date este numit Normalizare.

Normalizarea este o metodă sistematică de garantare că structura bazei de date este potrivită pentru interogări de uz general și nu prezintă anumite caracterisitici - anomalii de inserare, modificare sau ștergere - care ar putea duce la pierderea integrității datelor. - Wikipedia

Există diferite tipuri de «forme» de normalizare.

Let’s take a look at a simple example:

Table "public.people"

Column    |          Type          |                Modifiers
----------+------------------------+------------------------------------
 id       | integer                | not null default
          |                        | nextval('people_id_seq'::regclass)
          |                        |
 name     | character varying(50)  |
 address  | character varying(200) | not null
 phone_no | character varying      |
Indexes:
 "people_pkey" PRIMARY KEY, btree (id)

select * from people;

id |     name      |           address           |  phone_no
---+---------------+-----------------------------+-------------
 1 | Tim Sutton    | 3 Buirski Plein, Swellendam | 071 123 123
 2 | Horst Duester | 4 Avenue du Roix, Geneva    | 072 121 122
(2 rows)

Imaginați-vă că aveți mulți prieteni cu același nume de stradă sau oraș. Fiecare dintre aceste date sunt duplicate, consumă spațiu. Mai rău, dacă un nume de oraș se schimbă, trebuie să depuneți mult efort pentru a actualiza baza de date.

15.1.9. Try Yourself

Reproiectați tabela people de mai sus pentru a reduce duplicarea și pentru a normaliza structura de date.

You can read more about database normalisation here

Answer

The major problem with the people table is that there is a single address field which contains a person’s entire address. Thinking about our theoretical address table earlier in this lesson, we know that an address is made up of many different properties. By storing all these properties in one field, we make it much harder to update and query our data. We therefore need to split the address field into the various properties. This would give us a table which has the following structure:

id |     name      | house_no |  street_name   |    city    |   phone_no
 --+---------------+----------+----------------+------------+-----------------
 1 | Tim Sutton    |     3    | Buirski Plein  | Swellendam | 071 123 123
 2 | Horst Duester |     4    | Avenue du Roix | Geneva     | 072 121 122

In the next section, you will learn about Foreign Key relationships which could be used in this example to further improve our database’s structure.

15.1.10. Indecși

Un index în baza de date este o structură de date care îmbunătățește viteza operațiilor de extragere de date dintr-o tabelă a bazei de date. - Wikipedia

Imagine you are reading a textbook and looking for the explanation of a concept - and the textbook has no index! You will have to start reading at one cover and work your way through the entire book until you find the information you need. The index at the back of a book helps you to jump quickly to the page with the relevant information:

create index person_name_idx on people (name);

Now searches on name will be faster:

Table "public.people"

Column   |          Type          |               Modifiers
----------+------------------------+-------------------------------------
 id       | integer                | not null default
          |                        | nextval('people_id_seq'::regclass)
          |                        |
 name     | character varying(50)  |
 address  | character varying(200) | not null
 phone_no | character varying      |
Indexes:
 "people_pkey" PRIMARY KEY, btree (id)
 "person_name_idx" btree (name)

15.1.11. Secvențe

O secvență este un generator de numere unice. Este utilizat în mod normal pentru a creea un identificator unic pentru o coloană a unei tabele.

In this example, id is a sequence - the number is incremented each time a record is added to the table:

id |     name     |           address           |  phone_no
---+--------------+-----------------------------+-------------
 1 | Tim Sutton   | 3 Buirski Plein, Swellendam | 071 123 123
 2 | Horst Duster | 4 Avenue du Roix, Geneva    | 072 121 122

15.1.12. Diagrama Relațiilor dintre Entități

Într-o bază de date normalizată, există în mod uzual multe relații (tabele). Diagrama relațiilor între entități (Diagrama ER) este utilizată pentru stabilirea dependențelor logice între relații. Să examinăm tabela noastră nenormalizată people, utilizată anterior în cadrul lecției:

select * from people;

 id |     name     |           address           |  phone_no
----+--------------+-----------------------------+-------------
 1  | Tim Sutton   | 3 Buirski Plein, Swellendam | 071 123 123
 2  | Horst Duster | 4 Avenue du Roix, Geneva    | 072 121 122
(2 rows)

With a little work we can split it into two tables, removing the need to repeat the street name for individuals who live in the same street:

select * from streets;

 id |     name
----+--------------
 1  | Plein Street
(1 row)

and:

select * from people;

 id |     name     | house_no | street_id |  phone_no
----+--------------+----------+-----------+-------------
  1 | Horst Duster |        4 |         1 | 072 121 122
(1 row)

Putem apoi lega cele două tabele utilizând «keys» streets.id și people.streets_id.

Dacă desenăm o Diagramă ER pentru aceste două tabele ar arăta cam așa:

Diagrama ER ne ajută să exprimăm relații «unul la mulți». În acest caz simbolul săgeată spune că pe o stradă pot locui mai mulți oameni.

Try Yourself

Modelul nostru people are încă niște probleme de normalizare - încercați să îl normalizați în continuare și ilustrați-vă ideile printr-o Diagramă ER.

Answer

Our people table currently looks like this:

id |     name     | house_no | street_id |  phone_no
---+--------------+----------+-----------+-------------
 1 | Horst Duster |        4 |         1 | 072 121 122

The street_id column represents a «one to many» relationship between the people object and the related street object, which is in the streets table.

One way to further normalise the table is to split the name field into first_name and last_name:

id | first_name | last_name  | house_no | street_id |  phone_no
---+------------+------------+----------+-----------+------------
 1 |    Horst   |   Duster   |     4    |     1     | 072 121 122

We can also create separate tables for the town or city name and country, linking them to our people table via «one to many» relationships:

id | first_name | last_name | house_no | street_id | town_id | country_id
---+------------+-----------+----------+-----------+---------+------------
 1 |    Horst   |   Duster  |     4    |     1     |    2    |     1

An ER Diagram to represent this would look like this:

15.1.13. Constrângeri, Chei Primare și Chei Externe

O constrîngere într-o bază de date este utilizată pentru a garanta că o relație se potrivește cu viziunea celui care a modelat baza de date despre cum ar trebui stocate datele. De exemplu o constrângere pentru codul poștal ar putea garanta că numărul trebuie să se afle între 1000 și 9999.

O cheie Primară este compusă din unul sau mai multe câmpuri care fac o înregistrare unică. În mod uzual cheia primară se numește id și este o secvență.

O cheie Externă este utilizată pentru a face legătura unei înregistrări cu o altă tabelă (folosind cheia primară a acelui tabel).

În Diagramele ER, legăturile dintre tabele sunt în mod normal bazate pe chei Externe legate de chei Primare.

If we look at our people example, the table definition shows that the street column is a foreign key that references the primary key on the streets table:

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_street_id_fkey" FOREIGN KEY (street_id) REFERENCES streets(id)

15.1.14. Tranzacții

La adăugarea, modificarea sau ștergerea datelor într-o bază de date, este important ca de fiecare dată baza de dată să rămână într-o stare bună în cazul în care ceva nu merge bine. Cele mai multe baze de date pun la dispoziție o facilitate numită tranzacție. Tranzacțiile permit crearea unui moment de revenire la care vă puteți întoarce dacă modificările bazei de date nu au funcționat conform planului.

Să considerăm un scenariu în care aveți un sistem contabil. Trebuie să transferați fonduri dintr-un cont și să le adăugați în altul. Secvența de pași ar fi:

• eliminați R20 din Joe

• adăugați R20 la Anne

Dacă ceva nu merge bine în cadrul procesului (ex. pană de curent), tranzacția va reveni.

15.1.15. In Conclusion

Bazele de date permit administrarea datelor într-un mod structurat utilizând structuri de cod simple.

15.1.16. What’s Next?

Acum că am văzut cum funcționează teoretic bazele de date, să creăm o bază de date nouă pentru a implementa partea teoretică prezentată.