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 moderate

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.

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.

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

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?

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.