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 
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.
Antwoord
De vereiste SQL om de juiste tabel people te maken is:
create table people (id serial not null primary key,
name varchar(50),
house_no int not null,
street_id int not null,
phone_no varchar null );
Het schema voor de tabel (voer in \\d people) ziet er zo 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)
Ter illustratie hebben we met opzet de beperking fkey weggelaten.
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.
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?
Antwoord
De reden dat de opdracht DROP in dit geval niet zou werken is omdat de tabel people een beperking Foreign Key heeft voor de tabel streets. Dit betekent dat het verwijderen (of deleten) van de tabel streets de tabel people zou achterlaten met verwijzingen naar niet bestaande gegevens van streets.
Het is mogelijk om te ‘forceren’ dat de tabel streets wordt verwijderd door de opdracht CASCADE te gebruiken, maar dat zou ook de tabel people en elke andere tabel die een relatie heeft met de tabel streets verwijderen. Gebruik het met voorzichtigheid!
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.