15.2. Lesson: Implementierung des Datenmodells
Nachdem wir die ganze Theorie behandelt haben, wollen wir nun eine neue Datenbank erstellen. Diese Datenbank wird für unsere Übungen in den folgenden Lektionen verwendet.
Das Ziel dieser Lektion: Die Installation der benötigten Software und deren Verwendung, um unsere Beispieldatenbank zu erstellen.
15.2.1. Installation von PostgreSQL
Bemerkung
You can find PostgreSQL packages and installation instructions for your operating system at https://www.postgresql.org/download/. Please note that the documentation will assume users are running QGIS under Ubuntu.
Unter Ubuntu:
sudo apt install postgresql-9.1
Sie sollten folgende Nachricht erhalten:
[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]?
Drücken Sie Y und Enter und warten bis der Download und die Installation abgeschlossen sind.
15.2.2. Hilfe
PostgreSQL hat eine sehr gute online Dokumentation.
15.2.3. Erstellen eines Datenbankbenutzers
Unter Ubuntu:
Starten Sie nach dem Abschluss der Installation das folgende Kommando, um Postgres Nutzer zu werden. Erstellen Sie dann einen neuen Datenbanknutzer:
sudo su - postgres
Geben Sie bei der Abfrage Ihr normales login Passwort ein (Sie benötigen dazu sudo Rechte).
Erstellen Sie nun in der Postgres bash Eingabeaufforderung den Datenbanknutzer. Stellen Sie sicher, dass der Benutzername Ihrem Unix login Namen entspricht. Die Vereinfachung besteht darin, dass Postgres Sie automatisch authentifiziert, wenn Sie als dieser Nutzer eingeloggt sind:
createuser -d -E -i -l -P -r -s qgis
Geben Sie ein Passwort ein, wenn Sie danach gefragt werden. Sie sollten ein anderes Passwort als Ihr login Passwort verwenden.
Was bedeuten diese Optionen?
-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
Jetzt können Sie die Postgres Nutzer bash shell Umgebung durch folgende Eingabe verlassen:
exit
15.2.4. Überprüfen Sie das neue Konto:
psql -l
Die Ausgabe sollte ähnlich dem folgendem aussehen:
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)
Drücken sie zum Verlassen Q.
15.2.5. Eine Datenbank erstellen
Das Kommando createdb wird zum Erstellen einer neuen Datenbank verwendet. Es sollte von der Eingabeaufforderung der bash shell aus gestartet werden:
createdb address -O qgis
Sie können sich durch folgendes Kommando vergewissern, dass Ihre neue Datenbank erstellt wurde:
psql -l
Die Ausgabe sollte in etwa so aussehen:
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)
Drücken sie zum Verlassen Q.
15.2.6. Start einer Datenbank shell Sitzung
Sie können sich ganz einfach mit Ihrer Datenbank verbinden:
psql address
Geben Sie zum Verlassen der psql Datenbank shell folgendes ein:
\q
Hilfe zur shell erhalten Sie durch Eingabe von:
\?
Hilfe zur Benutzung von SQL Kommandos erhalten Sie durch:
\help
Um Hilfe für ein bestimmtes Kommando zu erhalten, geben Sie (beispielsweise) das Folgende ein:
\help create table
Siehe auch Psql cheat sheet.
15.2.7. Erstellen von Tabellen in SQL
Wir erstellen nun einige Tabellen! Dazu verwenden wir unser ER-Diagramm als Vorlage. Stellen Sie zuerst die Verbindung zur Adressdatenbank her:
psql address
Erstellen Sie dann eine Tabelle :Datei:`streets`:
create table streets (id serial not null primary key, name varchar(50));
serial und varchar sind Datentypen. serial teilt PostgreSQL mit, dass eine ganzzahlige Reihe (automatisch vergebene Zahlen) zur Füllung des Feldes id für jeden neuen Datensatz erstellt werden soll. varchar(50) teilt PostgreSQL mit, dass ein Zeichenkettenfeld mit einer Länge von 50 Zeichen erstellt werden soll.
Wir bemerken, dass das Kommando mit einem ; endet - alle SQL Kommandos sollten so enden. Wenn Sie Enter drücken, wird psql eine ähnliche Meldung wie folgt ausgeben:
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
Das bedeutet, dass Ihre Tabelle mit dem Primärschlüssel streets_pkey für streets.id erfolgreich erstellt wurde.
Achtung: Wenn Sie die Eingabe ohne ; beenden, erhalten Sie eine Meldung wie: address-#. Dies geschieht, weil PG noch eine weitere Eingabe erwartet. Geben Sie zum Abschließen des Kommandos ; ein.
Um die Tabelleneigenschaften anzuzeigen, können wir das Folgende eingeben:
\d streets
Als Ausgabe erhalten wir in etwa das Folgende:
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)
So zeigen Sie den Inhalt Ihrer Tabellen an:
select * from streets;
Als Ausgabe erhalten wir in etwa das Folgende:
id | name
---+------
(0 rows)
Wir sehen, dass unsere Tabelle momentan leer ist.
Try Yourself 
Erstellen Sie analog zum obigen Vorgehen eine Tabelle personen:
Fügen Sie Felder wie Telefonnummer, Adresse, Name usw. hinzu (achten Sie darauf, dass die Feldnamen gültig sind). Stellen Sie sicher, dass die Tabelle eine ID Spalte mit demselben Datentyp wie Beispiel oben erhält.
Answer
The SQL required to create the correct people table 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 );
The schema for the table (enter \\d people) looks like this:
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)
For illustration purposes, we have purposely omitted the fkey constraint.
15.2.8. Erstellen von Schlüsseln in SQL
Das Problem der obigen Lösung ist, dass die Datenbank nicht weiß, das Personen und Straßen in einer logischen Beziehung stehen. Um diese Beziehung auszudrücken, müssen wir einen Fremdschlüssel erstellen, der auf den Primärschlüssel der Tabelle streets zeigt.
Es gibt zwei Wege, dies zu erreichen:
Hinzufügen des Schlüssels nachdem die Datenbank erstellt wurde
Definition des Schlüssels bei Erstellung der Tabelle
Unsere Tabelle wurde schon erstellt, wir verwenden also den ersten Weg:
alter table people
add constraint people_streets_fk foreign key (street_id) references streets(id);
Damit teilen wir der Tabelle people mit, dass ihr Feld street_id einen gültigen Wert aus der Spalte id der Tabelle streets enthalten muss.
Es ist der gebräuchlichere Weg, eine Einschränkung bei der Erstellung einer Tabelle vorzugeben:
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
Nach Hinzufügen der Einschränkung sieht unser Tabellenschema wie folgt aus:
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. Indexe mit SQL erstellen
Wir möchten blitzschnelle Suchen nach Namen von Personen. Um dies zu erreichen, können wir einen Index über die Namensspalte unserer Tabelle der Personen erstellen:
create index people_name_idx on people(name);
\d people
Das Ergebnis ist:
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. Löschen von Tabellen mit SQL
Wenn man Tabellen los werden möchte, kann man das Kommando drop verwenden:
drop table streets;
In our current example, the above command would not work. Why not?
Answer
The reason the DROP command would not work in this case is because the people table has a Foreign Key constraint to the streets table. This means that dropping (or deleting) the streets table would leave the people table with references to non-existent streets data.
It is possible to ‚force‘ the streets table to be deleted by using the
CASCADE command, but this would also delete the people and any other table
which had a relationship to the streets table. Use with caution!
Wenn man dasselbe Kommando drop table mit der Tabelle people verwendenen würde, wäre es erfolgreich:
drop table people;
Bemerkung
Wenn Sie das Kommando eingegeben haben und die Tabelle people gelöscht wurde, ist es ein guter Zeitpunkt, um die Tabelle neu zu erstellen. Wir werden sie für die nächsten Übungen benötigen.
15.2.11. Ein Wort zu pgAdmin III
Wir zeigen die SQL Kommandos in der psql Kommandozeile, da dies ein sehr nützlicher Weg ist, um etwas über Datenbanken zu lernen. Es gibt aber auch schnellere und einfachere Wege, um eine Menge der hier gezeigten Dinge zu tun. Installieren Sie pgAdmin III und Sie können Tabellen mit Hilfe der Maus in einer grafischen Oberfläche erstellen, löschen oder ändern.
Unter Ubuntu installieren Sie es in etwa so:
sudo apt install pgadmin3
pgAdmin III wird in einem anderen Modul noch ausführlicher behandelt.
15.2.12. In Conclusion
Sie haben jetzt gesehen, wie man eine brandneue Datenbank komplett von null ausgehend erstellen kann.
15.2.13. What’s Next?
Als nächstes lernen Sie, wie man das DBMS nutzt, um neue Daten hinzuzufügen.