Wichtig
Übersetzen ist eine Gemeinschaftsleistung Sie können mitmachen. Diese Seite ist aktuell zu 77.78% übersetzt.
15.2. Lesson: Implementing the Data Model
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
Then create a streets table:
create table streets (id serial not null primary key, name varchar(50));
serial and varchar are data types. serial tells
PostgreSQL to start an integer sequence (auto-number) to populate the id
automatically for every new record. varchar(50) tells PostgreSQL to
create a character field of 50 characters in length.
You will notice that the command ends with a ; - all SQL commands should
be terminated this way. When you press Enter, psql will report something
like this:
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
That means your table was created successfully, with a primary key streets_pkey
using streets.id.
Note: If you hit return without entering a ;, then you will get a prompt like
this: address-#. This is because PostgreSQL is expecting you to enter more. Enter
; to run your command.
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.
Antwort
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);
That tells the people table that its street_id fields must match
a valid street id from the streets table.
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
If you want to get rid of a table you can use the DROP command:
drop table streets;
In our current example, the above command would not work. Why not?
Antwort
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!
If you used the same drop table command on the people table, it would
be successful:
drop table people;
Bemerkung
If you actually did enter that command and dropped the people
table, now would be a good time to rebuild it, as you will need it in the
next exercises.
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. Zusammenfassung
Sie haben jetzt gesehen, wie man eine brandneue Datenbank komplett von null ausgehend erstellen kann.
15.2.13. Was kommt als Nächstes?
Als nächstes lernen Sie, wie man das DBMS nutzt, um neue Daten hinzuzufügen.