15.2. Lesson: Het gegevensmodel implementeren

Nu we alle theorie hebben behandeld, laten we eens een nieuwe database maken. Deze database zal worden gebruikt voor onze oefeningen voor de lessen die hierna volgen.

Het doel voor deze les: De vereiste software installeren en die gebruiken om onze voorbeelddatabase te implementeren.

15.2.1. PostgreSQL installeren

Notitie

U vindt pakketten voor PostgreSQL en instructies voor het installeren voor uw besturingssysteem op https://www.postgresql.org/download/. Onthoud dat de documentatie er van uitgaat dat gebruikers QGIS uitvoeren onder Ubuntu.

Onder Ubuntu:

sudo apt install postgresql-9.1

U zou een bericht als dit moeten krijgen:

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

Druk op Y en Enter en wacht tot het downloaden en installeren is voltooid.

15.2.2. Hulp

PostgreSQL heeft hele goede online documentatie.

15.2.3. Een gebruiker voor de database aanmaken

Onder Ubuntu:

Als de installatie is voltooid, voer deze opdracht uit om de gebruiker postgres te worden en dan een nieuwe gebruiker voor de database te maken:

sudo su - postgres

Type uw normale wachtwoord voor inloggen in als daar naar gevraagd wordt (u moet rechten hebben voor sudo).

Nu, bij de bash prompt van de gebruiker postgres, maak de gebruiker voor de database aan. Zorg er voor dat de naam van de gebruiker overeenkomt met uw inlognaam voor unix : het zal uw leven veel eenvoudiger maken, omdat Postgres u automatisch zal authenticeren wanneer u ingelogd bent als die gebruiker:

createuser -d -E -i -l -P -r -s qgis

Voer een wachtwoord in als daar naar gevraagd wordt. U zou een ander wachtwoord moeten gebruiken dan uw wachtwoord om in te loggen.

Wat betekenen deze opties?

-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

Nu zou u de omgeving van de bash shell van de gebruiker Postgres moeten verlaten door te typen:

exit

15.2.4. Het nieuwe account verifiëren

psql -l

Zou iets terug moeten geven als dit:

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)

Typ Q om af te sluiten.

15.2.5. Een database maken

De opdracht createdb wordt gebrukt om een nieuwe database te maken. Het zou moeten worden uitgevoerd vanaf de bash shell prompt:

createdb address -O qgis

U kunt het bestaan van uw nieuwe database verifiëren met behulp van deze opdracht:

psql -l

Wat iets zoals dit zou moeten teruggeven:

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)

Typ Q om af te sluiten.

15.2.6. Een database shell-sessie beginnen

U kunt uw database eenvoudig verbinden door dit:

psql address

Verlaten van de psql database shell, typ:

\q

Voor hulp in het gebruiken van de shell, type:

\?

Voor hulp bij het gebruiken van SQL-opdrachten, typ:

\help

Hulp krijgen over een specifieke opdracht, typ (bijvoorbeeld):

\help create table

Bekijk ook het Psql cheat sheet.

15.2.7. Tabellen in SQL maken

Laten we beginnen met het maken van enkele tabellen! We zullen ons ER Diagram als gids gebruiken. Verbind eerst met de db address:

psql address

Maak dan een tabel streets:

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

serial en varchar zijn datatypen. serial vertelt PostgreSQL om een reeks van een geheel getal te starten (automatisch nummeren) om id automatisch te vullen voor elk nieuw record. varchar(50) vertelt PostgreSQL om een veld van 50 tekens in lengte te maken.

Het zal u opvallen dat elke opdracht eindigt met een ; - alle opdrachten voor SQL zouden op deze manier moeten worden beëindigd. Wanneer u op Enter drukt, zal psql iets rapporteren als dit:

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

Dat betekent dat uw tabel met succes werd gemaakt, met een primaire sleutel streets_pkey met behulp van streets.id.

Opmerking: Als u op Return drukte zonder een ; in te voeren, dan zult u een prompt zoals deze krijgen: address-#. Dit omdat PG van u meer invoer verwacht. Voer ; in om uw opdracht uit te voeren.

U kunt dit doen om uw schema van de tabel te bekijken:

\d streets

Wat er ongeveer uit zou moeten zien zoals dit:

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)

U kunt dit doen om de inhoud van uw tabel te bekijken:

select * from streets;

Wat er ongeveer uit zou moeten zien zoals dit:

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

Zoals u kunt zien is onze tabel momenteel leeg.

Try Yourself moderate

Gebruik de hierboven weergegeven benadering om een tabel, genaamd people, te maken:

Voeg velden toe zoals telefoonnummer, adres, naam, etc. Zorg dat de velden een geldige veldnaam krijgen (geen spaties). Geef de tabel een kolom ID met hetzelfde datatype als hierboven.

15.2.8. Sleutels maken in SQL

Het probleem met onze oplossing hierboven is dat de database niet weet dat er een logische relatie bestaat tussen people en streets. We moeten, om deze relatie aan te geven, een vreemde sleutel definiëren die verwijst naar de primaire sleutel van de tabel streets.

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

Er zijn twee manieren om dat te doen:

  • De sleutel toevoegen nadat de tabel is gemaakt

  • De sleutel definiëren bij het maken van de tabel

Onze tabel is al gemaakt, dus doen we het op de eerste manier:

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

Dat vertelt de tabel people dat zijn velden street_id moeten overeenkomen met een geldige id voor street uit de tabel streets.

De meest gebruikte manier om een beperking toe te voegen is om dat te doen wanneer u de tabel maakt:

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

Na het toevoegen van de beperking ziet ons schema voor de tabel er nu als volgt uit:

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. Indexen in SQL maken

We willen zoekacties met de snelheid van het licht op namen van mensen. We kunnen een index op de kolom name van onze tabel people maken om dat mogelijk te maken:

create index people_name_idx on people(name);

\d people

Wat resulteert 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)

15.2.10. Tabellen in SQL verwijderen

Als u een tabel wilt verwijderen kunt u gebruk maken van de opdracht drop:

drop table streets;

In ons huidige voorbeeld zou de bovenstaande opdracht niet werken. Waarom niet?

Als u dezelfde opdracht drop table zou gebruiken op de tabel people, zou die met succes worden uitgevoerd:

drop table people;

Notitie

Als u echt die opdracht invoerde en de tabel people verwijderde, zou nu een goed moment zijn om hem opnieuw te bouwen, omdat u het in de volgende oefeningen nodig heeft.

15.2.11. Een woord over pgAdmin III

SQL opdrachten van psql worden gebruikt omdat dit een goede manier is om te leren werken met databases. Er zijn echter eenvoudigere en mogelijk snellere manieren om deze SQL opdrachten uit te voeren, bijvoorbeeld met pgAdmin III. Dit is een grafische tool waarmee tabellen kunnen worden gemaakt, verwijderd en gewijzigd met behulp van slepen en klikken.

Onder Ubuntu kunt u het op deze manier installeren:

sudo apt install pgadmin3

pgAdmin III zal meer detail worden behandeld in een andere module.

15.2.12. In Conclusion

U heeft nu gezien hoe u een nagelnieuwe database maakt, geheel vanaf niets beginnend.

15.2.13. What’s Next?

Vervolgens zult u leren de DBMS te gebruiken om nieuwe gegevens toe te voegen.