I
n
h
a
l
t

Workshop PostgreSQL 9

SQL
Universum
Erde
Flughafen
Flugbetrieb
Fluggäste
Tower
Fluggesellschaft
Reisebüro

Workshop PostgreSQL 9

Relationale Datenbanken sind heute eine der gebräuchlichsten Formen der elektronischen Datenspeicherung. Grundlegendes Wissen über die Datenbanksprache SQL ist deshab ein unerlässlicher Bestandteil der Arbeit mit relationalen Datenbanken. In diesem Workshop werden die Möglichkeiten von SQL anhand der aktuellen Version des freien Datenbanksystems PostgreSQL 9 vorgestellt. Es wird eine Beispieldatenbank airport erstellt. Dabei werden die Datenbankobjekte Tabellen, Datentypen, Sichten, Funktionen, Trigger, ebenso wie die Datenbankoperationen Einfügen, Ändern, Löschen und Abfragen von Datensätzen besprochen. Der Workshop richtet sich an Programmierer und Interessierte mit geringen oder keinen Vorkenntnissen in SQL. Die Kenntniss einer Programmiersprache ist vorteilhaft.

SQL

SQL, die Structured Querying Language, dient zur Definition, Administration und Verwaltung relationaler Datenbanken. Diese werden zumeist von relationalen Datenbanksystemen bereitgestellt. Die Datenbanksprache SQL ist standardisiert. PostgreSQL 9 setzt weitestgehend den SQL-Standard ANSI-SQL:2008 um. Jedes Datenbanksystem wie z.B. wie Oracle, DB2, PostgreSQL, Maria DB, MSSQLServer nutzt einen eigenen SQL Dialekt. Da ein Dialekt Erweiterungen und Einschränkungen zu SQL enthält, sind die SQL-Ausdrücke dieses Workshops nicht ohne weiteres auf andere Datenbanksysteme übertragbar.

SQL ist eine stark typisierte Sprache und unterscheidet nicht zwischen Klein- und Großschreibung. Schlüsselwörter werden jedoch zumeist groß geschrieben, Datentypen und Tabellennamen klein. SQL ist entsprechend ihrer Aufgaben in mehrere Teile unterteilt: DDL, die Data Definition Language, dient der Definition von Datenbankobjekten wie z.B. Tabellen, Sichten (Views), Triggern, Indizes, Integritätsbedingungen (Constraints) und Datentypen. DML, die Data Manipulation Language, dient dem Einfügen, dem Ändern und dem Entfernen von Datensätzen. DCL, die Data Control Language, dient der Zugriffskontrolle von relationalen Datenbanken. Dies umfasst die Nutzer- und Rechteverwaltung sowie die reguläre Abwicklung von Datenbankoperationen mittels Transaktionen.

Universum

Falls noch nicht installiert kann die aktuelle Version von PostgreSQL von der Webseite des Projektes heruntergeladen werden: http://www.postgresql.org/download/. Dort finden sich auch Angaben zur Installation des freien Datenbanksystems.

Das Universum, in dem wir uns bewegen wollen, ist unsere Datenbank. Zunächst erstellen wir aber einen Benutzer airport, als Eigentümer der zu erstellenden Datenbank airport. Diese wie alle weiteren Operationen können wir für PostgreSQL unter Verwendung des Schnittstellenprogramms psql vornehmen. Dazu melden wir uns als Superuser postgres an der Vorlagendatenbank template1 an.

$ su postgres # linux
$ psql template1 postgres

Den Benutzer erstellen wir mit dem DML-Befehl CREATE ROLE.

CREATE ROLE airport
  LOGIN PASSWORD 'flight';

Der Benutzer airport kann sich nun am Datenbanksystem mit dem Passwort flight anmelden. Um den Benutzer wieder zu entfernen verwenden wir den DML-Befehl DROP ROLE.

DROP ROLE airport;

Zur Erstellung der Datenbank airport verwenden wir den DML-Befehl CREATE DATABASE.

CREATE DATABASE airport
  WITH OWNER=airport
  ENCODING='utf8'
  TEMPLATE=template0;

Die Datenbank airport gehört dem Datenbankbenutzer airport. Als Zeichensatz verwenden wir utf-8. Die Datenbank wird durch Kopieren der leeren Vorlagendatenbank template0 erzeugt. Zum Löschen der Datenbank wird der DML-Befehlt DROP DATABASE verwendet.

DROP DATABASE airport;

Jede existierende Datenbank kann als Vorlage einer neuen Datenbank genommen werden. Jedoch geht der Bezug von Ur- zu Abbild nach dem Erstellen der Datenbank verloren. Nun können wir uns als Benutzer airport an der Datenbank airport anmelden und loslegen. Zum Beenden von psql verwenden wir \q.

\q
$ psql airport airport

Alle Datenbankobjekte, die wir im Folgenden anlegen werden, werden dem Benutzer airport zugewiesen.

Erde

Wir konstruieren zunächst ein Bild unseres Flughafens, in dem wir die Daten sammeln, die wir zum Betrieb eines Flughafens benötigen. Dieser Prozess wird Modellierung genannt. Wir verwenden keine speziellen Verfahren, sondern konzentrieren uns auf die Umsetzung mittels SQL.

Da unser Flughafen auf der Erde liegen soll benötigen, wir ein Bild unserer Erde. Wir gehen von einer Kugel aus und definieren für diese Längen- und Breitengrade. Dazu wählen wir eine Äquatorebene senkrecht zur Drehrichtung der Erde. Als Pole bezeichnen wir die Durchstoßpunkte der Drehachse. Wir wählen die Bezeichnungen Nord- und Südpol. Wir zählen Breitengrade als Winkel zwischen Drehachse und Äquatorebene in nördlicher Richtung positiv (90° Nordpol) und in südlicher negativ (-90° Südpol). Die Längengrade werden in östlicher Richtung gezählt, von 0° bis 360°. Die Definition von Breiten- (latitude) und Längengraden (longitude) erreichen wir, in dem wir die Datentypen latitude und longitude anlegen. Dazu verwenden wir den DDL-Befehl CREATE DOMAIN.

CREATE DOMAIN latitude float
  CHECK (VALUE BETWEEN -90.0 AND 90.0);

CREATE DOMAIN longitude float
  CHECK (VALUE BETWEEN 0.0 AND 360.0);

Beide Datentypen sind vom Basistyp float. Der Datentyp float beschreibt eine Fliesskommazahl. Der Wertebereich der Datentypen geben wir jeweils durch eine CHECK-Integritätsbedingung an. Wir fassen nun noch beide Datentypen in einem Datentypen zusammen, den wir mittels des DDL-Befehls CREATE TYPE erstellen.

CREATE TYPE location AS (
  latitude latitude,
  longitude longitude
);

Der Datentyp location ist aus den Komponenten mit den Namen latitude und longitude zusammengesetzt. Als Datentypen der jeweiligen Komponente verwenden wir die gleichnamige Domain. Wir verwenden Domainen, da die Typdefinition keine CHECK-Bedingung bietet. Wir überprüfen die Eigenschaften des Datentyps location durch den DML-Befehl SELECT.

SELECT '(90.0, 360.0)'::location;

 location
----------
 (90,360)
(1 row)

Dem Befehl SELECT folgt eine untypisierte Zeichenkette. Der doppelte Doppelpunkt konvertiert die Zeichenkette, begrenzt durch eingestrichene Anführungszeichen, in den Datentypen location. Diese Form der Umwandlung wird explizit genannt, da der Zieldatentyp, location, angegeben ist. Bei impliziten Umwandlungen ergibt sich z.B. der Datentyp aus dem Datentyp des Datenbankfeldes, in das ein Wert eingefügt werden soll. Die Zeichenkette wird als Liste interpretiert. Der erste Wert wird entsprechend der Definition des zusammengesetzten Datentyps location in die Komponente latitude, der zweite in die Komponente longitude aufgenommen. SQL-Anweisungen werden stets durch ein Semikolon abgeschlossen. Wir überprüfen nun die Festlegung der Wertebereiche durch Verletzung einer CHECK-Integritätsbedingung.

SELECT '(90.1, 0.0)'::location;

FEHLER:  Wert für Domäne latitude verletzt Check-Constraint »latitude_check«

Offensichtlich ist die Integritätsbedingung für die Komponente latitude verletzt. Zur Maskierung von Anführungszeichen in Zeichenketten verwenden wir das Anführungszeichen zweimal.

SELECT 'Zeichenkette mit maskiertem ''-Zeichen';

               ?column?
---------------------------------------
 Zeichenkette mit maskiertem '-Zeichen
(1 row)

Zur Verbindung von Zeichenketten untereinander verwenden wir den Verbindungsoperator ||.

SELECT 'erste Zeichenkette '||'zweite Zeichenkette';

                ?column?
----------------------------------------
 erste Zeichenkette zweite Zeichenkette
(1 row)

Der Verbindungsoperator ist für viele Datentypen definiert. So können wir z.B. ein Feld ganzer Zahlen um eine weitere Zahl damit ergänzen.

SELECT '{1,2,3,4,5,6}'::integer[] || 7::integer;

    ?column?
-----------------
 {1,2,3,4,5,6,7}
(1 row)

Die zunächst untypisierte Zeichenkette wird in ein Feld, [], von ganzen Zahlen umgewandelt und anschließend um die einzelne, ganze Zahl erweitert. Die doppelten Anführungszeichen verwenden wir um Namen anzugeben, die nicht ohne weiteres dem SQL-Standard entsprechen. Im Folgenden wird das Schlüsselwort SELECT als Bezeichner für die aktuelle Datumsangabe genutzt.

SELECT now() as "SELECT";

           SELECT
----------------------------
 2011-04-27 15:59:05.433+02
(1 row)

Kommentare für eine Zeile werden durch -- eingeleitet, mehrzeilige mit /* und */ begrenzt.

-- einzeiliger Kommentar endet am Zeilenende
/* mehrzeiliger
  Kommentar */

Da Flüge häufig international sind, unterteilen wir die Erde in Länder. Die Speicherung von Datensätzen erfolgt in relationalen Datenbanksystemen durch Tabellen. Tabellen bestehen aus einer Menge von Zeilen. Jede Zeile besteht aus einer Reihe von Feldern, in die Daten eines Datensatzes eingetragen werden können. Vor Nutzung einer Tabelle muss diese zunächst erzeugt werden.

CREATE TABLE countries (
 country_id char(2),
 name varchar(256) NOT NULL,
 population integer
);

Der DDL-Befehl CREATE TABLE erzeugt die Tabelle countries. Die Länder besitzen einen zweistelligen, eindeutigen Ländercode und einen Namen, der bis zu 256 Zeichen umfassen kann. Zudem wird die Bevölkerungszahl als ganze Zahl erhoben. Um diese Eigenschaften zu speichern werden Felder definiert. Ein Feld wird mindestens durch einen Namen und einen Datentyp beschrieben.

Über den DML-Befehl INSERT INTO fügen wir nun Datensätze, die Länder repräsentieren, zwecks Speicherung in die Tabelle countries ein.

INSERT INTO countries (country_id, name) VALUES ('AU', 'Australia');
INSERT INTO countries (country_id, name) VALUES ('AT', 'Austria');
INSERT INTO countries (country_id, name) VALUES ('CH', 'Switzerland');
INSERT INTO countries (country_id, name) VALUES ('DE', 'Germany');
INSERT INTO countries (country_id, name) VALUES ('ES', 'Spain');
INSERT INTO countries (country_id, name) VALUES ('US', 'United States');

Nach dem Befehl INSERT INTO folgt der Name der Tabelle, countries. In der Folgenden Liste werden die Felder genannt, in die Werte eingefügt werden sollen. In diese Felder wird der Wert aus der Liste nach dem Schlüsselwort VALUES gespeichert, der in der Liste an der gleichen Position wie der Name des Feldes steht. Pro Anweisung wird eine Zeile, die einen Datensatz aufnimmt, in die Tabelle eingefügt. In alle Felder, für die kein Wert eingefügt wird, wird entweder ein Initialisierungswert (Default) oder der Wert NULL für undefiniert eingefügt. Die Konvertierung der untypisierten Zeichenketten aus den INSERT-Anweisungen geschieht implizit. Um das Einfügen zu überprüfen fragen wir nun alle Datensätze aus der Tabelle countries ab.

SELECT * FROM countries;

 country_id |     name      | population
------------+---------------+------------
 AU         | Australia     | NULL
 AT         | Austria       | NULL
 CH         | Switzerland   | NULL
 DE         | Germany       | NULL
 ES         | Spain         | NULL
 US         | United States | NULL
(6 rows)

Dies erreichen wir durch Verwendung des DML-Befehls SELECT. Der Stern-Operator wählt alle Felder aus. Dem Schlüsselwort FROM folgt hier der Name der Tabelle, aus denen Datensätze abgefragt werden sollen. Wie wir sehen ist zu jedem Datensatz ein Ländercode, country_id, gegeben. Jeder Ländercode kommt zudem nur einmal vor.

Da jede Tabelle theoretisch einen Primärschlüssel enthalten muss, der einen Datensatz eineindeutig ausweist, vereinbaren wir das Feld country_id nun als Primärschlüssel der Tabelle. Dazu fügen wir die folgende Integritätsbedingung mittels des DDL-Befehls ALTER TABLE der Tabelle countries hinzu.

ALTER TABLE countries ADD CONSTRAINT pk_country_id PRIMARY KEY (country_id);

Das Schlüsselwort PRIMARY KEY weist das Feld country_id nun als Primärschlüssel aus. Die Werte eines Primärschlüssels sind immer definiert und eindeutig. Die Anzahl der Elemente der Menge der Primärschlüssel entspricht also der Anzahl der Datensätze. Würden wir einen weiteren Datensatz mit einem schon vorhandenen Ländercode einfügen wollen, würde diese Anweisung als Fehler zurückgewiesen. Offensichtlich ist im Folgenden die Integritätsbedingung pk_country verletzt

INSERT INTO countries (country_id, name) VALUES ('US', 'United States');

FEHLER:  doppelter Schlüsselwert verletzt Unique-Constkraint »pk_country_id«
DETAIL:  Schlüssel »(country_id)=(US)« existiert bereits.

Die NOT NULL-Bedingung in der Definition des Feld name ist dagegen optional. Sie erzwingt stets die Angabe eines Namens. Würde statt dessen der Datenbankwert NULL, so würde die Datenbankoperation als fehlerhaft zurückgewiesen. Um dies zu überprüfen fügen wir einen Datensatz mittels des DDL-Befehls INSERT INTO in die Tabelle countries ein. Offensichtlich ist im Folgenden die Integritätsbedingung NOT NULL für das Feld Name verletzt.

INSERT INTO countries (country_id, name) VALUES ('DE', NULL);

FEHLER:  NULL-Wert in Spalte »name« verletzt Not-Null-Constraint

Für die Bevölkerungszahl wird in der SELECT-Anweisung oben stets ein NULL-Wert ausgegeben, da beim Einfügen der Datensätze keine Angaben über die Bevölkerung mit aufgenommen wurden. Um dies nachzuholen verändern wir die Datensätze mit dem DML-Befehl UPDATE

UPDATE countries SET population = 80000000 WHERE country_id = 'DE';

Diese Anweisung setzt die Bevölkerungszahl für die Datensätze auf 80000000, die die WHERE-Bedingung erfüllen. In diesem Fall wird der Ländercode von Deutschland gefordert. Wir betrachten das Ergebnis.

SELECT * FROM countries;

 country_id |     name      | population
------------+---------------+------------
 AU         | Australia     |   NULL
 AT         | Austria       |   NULL
 CH         | Switzerland   |   NULL
 ES         | Spain         |   NULL
 US         | United States |   NULL
 DE         | Germany       |   80000000
(6 rows)

Wie wir sehen wird der veränderte Datensatz als letztes ausgegeben und enthält den Wert der UPDATE-Anweisung. Die Veränderung der Ausgabeposition liegt an der Speicherung des Datensatzes innerhalb des Datenbanksystems PostgreSQL. Intern wurde ein Tupel, das den neuen Datensatz für DE enthält, an die Liste der bestehenden Tupel einer Tabelle angehängt. Das Tupel, das vorher den aktuellen Wert des Datensatzes darstellte, wurde als veraltet markiert. Diese Eigenschaft von PostgreSQL legt die Verwendung eines Sortierverfahrens vor Ausgabe der Datensätze nahe.

Die Daten oder Tupel einer Tabelle werden jeweils in Datenbankblöcken der Größe 8kb (Vorzugswert) gespeichert. Für eine UPDATE-Anweisung wird das Tupel bestenfalls in dem gleichen Datenbankblock gespeichert, in dem der Datensatz zunächst eingefügt wurde. Überschreitet die durch INSERT- und UPDATE-Anweisungen hinzu gespeicherte Menge von Daten die Größe eines Datenbankblockes, so wird ein neuer Datenbankblock erzeugt.

SHOW block_size;

 block_size
------------
 8192
(1 row)

Zum Löschen eines Datensatzes wird das dazugehörige Tupel zunächst als veraltet markiert. Die veralteten Tupel können durch einen VACUUM-Befehl entfernt werden.

VACUUM VERBOSE COUNTRIES;

Zunächst ergänzen wir die Bevölkerungszahlen für die anderen Länder, bis auf den Datensatz der Schweiz.

UPDATE countries SET population = 21000000 WHERE country_id = 'AU';
UPDATE countries SET population = 8000000 WHERE country_id = 'AT';
UPDATE countries SET population = 40000000 WHERE country_id = 'ES';
UPDATE countries SET population = 300000000 WHERE country_id = 'US';

Wir betrachten nun die Länder nach der Bevölkerungszahl. Die Länder sollen nach der Bevölkerungszahl abnehmend aufgeführt werden.

SELECT * FROM countries ORDER BY population DESC;

 country_id |     name      | population
------------+---------------+------------
 CH         | Switzerland   |     NULL
 US         | United States |  300000000
 DE         | Germany       |   80000000
 ES         | Spain         |   40000000
 AU         | Australia     |   21000000
 AT         | Austria       |    8000000
(6 rows)

Zur Sortierung der Datensätze verwenden wir die ORDER BY-Bedingung. Im Beispiel oben geben wir das Feld population als Sortierkriterium an. Der Zusatz DESC (descendending) bedingt eine absteigende Sortierung. Die Angabe ASC (ascendending) ordnet die Datensätze dagegen aufsteigend. Wie wir sehen wird der Datensatz für die Schweiz an erster Stelle aufgeführt. Wir können diesen Datensatz ausschließen, indem wir die SELECT-Anweisung mit einer WHERE-Bedingung verfeinern.

SELECT * FROM countries WHERE country_id <> 'CH' ORDER BY population DESC;

 country_id |     name      | population
------------+---------------+------------
 US         | United States |  300000000
 DE         | Germany       |   80000000
 ES         | Spain         |   40000000
 AU         | Australia     |   21000000
 AT         | Austria       |    8000000
(5 rows)

Die WHERE-Bedingung fordert hier einen Ländercode ungleich dem der Schweiz. Wir können aber auch den Datensatz mittels des DDL-Befehls DELETE für die Schweiz löschen.

DELETE FROM countries WHERE country_id = 'CH';

Hier sorgt die WHERE-Bedingung für die Auswahl des Datensatzes der Schweiz. Alternativ könnten wir alle Datensätze löschen, deren Bevölkerungszahl den Wert NULL aufweisen. Davon wäre allein der Datensatz der Schweiz betroffen.

DELETE FROM countries WHERE population IS NULL;

Anstelle des Vergleichsoperators = wird zum Auffinden von NULL-Werten der Ausdruck IS NULL oder inversIS NOT NULL für alle nicht NULL-Werte verwendet. Wir wollen nun die Differenz zwischen allen Datensätzen und denen, deren Bevölkerungszahl den Wert NULL aufweist, ermitteln. Gelöschte Datensätze werden in PostgreSQL zunächst nicht pyhsikalisch gelöscht. Sie werden zunächst nur markiert. Erst durch Anwendung des VACUUM-Befehls werden sie physikalisch gelöscht.

SELECT count(*) - count(population) FROM countries;

 ?column?
----------
        0
(1 row)

Die Aggregat-Funktion count mit dem Argument * zählt alle Zeilen, Datensätze, der Tabelle countries, während mit dem Argument population alle Datensätze gezählt werden, für die der Wert von population ungleich NULL ist. Da die Differenz 0 ist, können wir nun fordern, dass die Bevölkerungszahl stets einen Wert ungleich NULL hat. Dazu erweitern wir die Definition des Feldes population in der Tabelle countries um eine NOT NULL-Bedingung. Wir verwenden den DDL-Befehl ALTER TABLE.

ALTER TABLE countries ALTER population SET NOT NULL;

Wir überprüfen die Wirkung der NOT NULL-Bedingug, in dem wir den Datensatz der Schweiz erneut einfügen.

INSERT INTO countries (country_id, name) VALUES ('CH', 'Switzerland');

FEHLER:  NULL-Wert in Spalte »population« verletzt Not-Null-Constraint

Die Ausdrücke der oben benutzten WHERE-Bedingungen werden jeweils zu einem Wahrheitswert hin ausgewertet. Teilsausdrücke können mittels der Boolschen Algebra miteinander verknüpft werden. Neben Und (AND)- und Oder (OR)-Verknüpfungen ist die Verwendung von Klammern erlaubt. Erst wenn der gesamte Ausdruck wahr ist, wird der Datensatz in die Ergebnismenge aufgenommen. Die WHERE-Bedingung wirkt als Filter auf die gelesenen Datensätze einer Tabelle.

SELECT *
FROM countries
WHERE ( country_id IN ('DE', 'ES') AND population > 40000000 )
  OR name LIKE 'United%';

 country_id |     name      | population
------------+---------------+------------
 DE         | Germany       |   80000000
 US         | United States |  300000000
(2 rows)

In der SELECT-Anweisung oben ist der Ausdruck der WHERE-Bedingung dann wahr, wenn entweder der Name des Landes mit der Zeichenkette United beginnt oder die Länderkennung in der Menge der Länderkennungen von Deutschland und Spanien enthalten ist sowie die Bevölkerungszahl größer 40000000 ist. Der Bereichsoperator IN verweist den Wert der country_id auf die Aufzählung der Ländercodes. Die AND-Verknüpfung fordert, dass gleichzeitig die Einwohnerzahl des Landes größer als 40000000 ist. Beides zusammen trifft lediglich auf Deutschland zu. Die Klammern gruppieren beide Teilausdrücke. Der Ausdruck name LIKE 'United%' passt auf alle Ländernamen, die mit United beginnen und beliebig fortgesetzt werden oder nicht. Das Prozentzeichen agiert hier als sogenanntes don't care.

Flughafen

Da ein Flughafen allein keinen Sinn macht, wenn unsere Passagiere reisen wollen, benötigen wir natürlich mehrere Flughäfen, die als Abflug- oder Ankunftsort in Frage kommen.

CREATE TABLE airports (
 airport_id char(3) NOT NULL, 
 name varchar(256) NOT NULL,
 location location NOT NULL,
 country char(2) NOT NULL,
 open boolean DEFAULT TRUE,
 CONSTRAINT pk_airport_id PRIMARY KEY (airport_id),
 CONSTRAINT fk_country FOREIGN KEY (country) REFERENCES countries (country_id)
);

Der DDL-Befehl CREATE TABLE erzeugt die Tabelle airports, in der die Liste bekannter Flughäfen aufgenommen wird. Als Primärschlüssel, aiport_id, verwenden wir den von der internationalen Luftfahrtindustrie festgelegten IATA Code für Flughäfen. Die Ausweisung als Primärschlüssel erfolgt durch die Angabe.

CONSTRAINT pk_airport_id PRIMARY KEY (airport_id)

Wir speichern zudem die Lage auf der Erde, das Land und die Erreichbarkeit des Flughafens. Die Erreichbarkeit des Flughafens wird durch das Feld open dargestellt. Diese hat den Datentyp boolean. Der Wahrheitswert boolean kann die Werte wahr (true) oder falsch (false)sowie den Datenbankwert NULL annehmen. Die Angabe DEFAULT TRUE in der Definition des Feldes open sorgt dafür, dass der Wert true anstelle NULL eingefügt wird.

Um nun Flughäfen in die Tabelle airports einzufügen nutzen wir den DDL-Befehl INSERT INTO.

INSERT INTO airports (airport_id, name, location, country) VALUES ('TXL', 'Berlin - Tegel Airport', '(52.55, 13.28)', 'DE');
INSERT INTO airports (airport_id, name, location, country) VALUES ('LAX', 'Los Angeles Airport', '(33.93, 241.59)', 'US');
INSERT INTO airports (airport_id, name, location, country) VALUES ('PMI', 'Palma De Mallorca Airport', '(39.54, 2.73)', 'ES');
INSERT INTO airports (airport_id, name, location, country) VALUES ('GRZ', 'Graz Airport', '(47.15, 15.43)', 'AT');
INSERT INTO airports (airport_id, name, location, country) VALUES ('FRA', 'Frankfurt Airport', '(50.01, 8.53)', 'DE');
INSERT INTO airports (airport_id, name, location, country) VALUES ('SYD', 'Sydney International Airport', '(-33.93, 151.16)', 'AU');

Wir vergewissern uns der eingefügten Datensätze durch eine SELECT-Anweisung.

SELECT * FROM airports ORDER BY airport_id ASC;

 airport_id |             name             |    location     | country | open
------------+------------------------------+-----------------+---------+------
 FRA        | Frankfurt Airport            | (50.01,8.53)    | DE      | t
 GRZ        | Graz Airport                 | (47.15,15.43)   | AT      | t
 LAX        | Los Angeles Airport          | (33.93,241.59)  | US      | t
 PMI        | Palma De Mallorca Airport    | (39.54,2.73)    | ES      | t
 SYD        | Sydney International Airport | (-33.93,151.16) | AU      | t
 TXL        | Berlin - Tegel Airport       | (52.55,13.28)   | DE      | t
(6 rows)

Um eine Verknüpfung zwischen Flughäfen und Ländern herzustellen, werden im Feld country nur Werte, die als Primärschlüssel im Feld country_id der Tabelle countries in Frage enthalten sind, erlaubt. Die Einschränkung auf Werte aus der Tabelle countries wird durch Vereinbarung des Fremdschlüssels mit den Namen fk_country erreicht.

CONSTRAINT fk_country FOREIGN KEY (country) REFERENCES countries (country_id)

Dieser identifiziert jeden Wert des Feldes country mit einem Wert aus dem Feld country_id der Tabelle countries. Der Datentyp des Fremdschlüssels in Tabelle airports ist vom Typ char(2) genau wie der Primärschlüssel in der Tabelle countries. Um die Wirkung des Fremdschlüssels zu überprüfen fügen wir einen Flughafen in die Tabelle airports ein, der auf ein Land verweist, dass nicht (mehr) in der Tabelle countries vorkommt.

INSERT INTO airports (airport_id, name, location, country) VALUES ('GVA', 'Geneva International Airport', '( 46.23, 6.10)', 'CH');

FEHLER:  Einfügen oder Aktualisieren in Tabelle »airports« verletzt Fremdschlüssel-Constraint »fk_country«
DETAIL:  Schlüssel (country)=(CH) ist nicht in Tabelle »countries« vorhanden.

Die Verwendung von Fremdschlüsseln ist üblich in relationalen Datenbanken. Sie müssen nicht notwendigerweise durch eine Integritätsbedingung vom Typ FOREIGN KEY ausgedrückt werden. Da jedes Datum nur einmal gespeichert werden soll (Vermeidung von Datenredundanzen), treten Fremdschlüssel in Datensätzen immer da auf, wo Daten mehrfach gespeichert werden. Deshalb werden sinniger Weise Datensätze nur für Einheiten gebildet, die unabhängig von anderen beschrieben werden können. Sollen Einheiten Eigenschaften anderer Einheiten enthalten, so kommt ein Fremdschlüssel zum Einsatz. Im Gegensatz zu Primärschlüsseln können Fremdschlüssel aber den Wert NULL annehmen.

Datensätze, die durch Fremdschlüssel auf Datensätze in anderen Tabelle verweisen, lassen sich durch Verwendung des DML-Befehls SELECT mittels eines Verbundes (Join) wieder zusammenführen.

SELECT airport_id AS "iata code",
  airports.name AS airport,
  country_id AS "country code",
  countries.name AS country
FROM airports
  JOIN countries ON (country=country_id);

 iata code |           airport            | country code |    country
-----------+------------------------------+--------------+---------------
 FRA       | Frankfurt Airport            | DE           | Germany
 TXL       | Berlin - Tegel Airport       | DE           | Germany
 SYD       | Sydney International Airport | AU           | Australia
 GRZ       | Graz Airport                 | AT           | Austria
 PMI       | Palma De Mallorca Airport    | ES           | Spain
 LAX       | Los Angeles Airport          | US           | United States
(6 rows)

Der Verbund, Schlüsselwort JOIN, verbindet die Tabellen airports und countries. Die Bedingung für die Vereinigung der Datensätze folgt hier dem Schlüsselwort ON. Der Wert country aus der Tabelle airports soll dem Wert country_id aus der Tabelle countries entsprechen. Zum gleichen Wert des Ländercodes werden die gelesenen Datensätze aus der Tabelle airports um die Datensätze aus der Tabelle countries ergänzt. Ein Datensatz eines Flughafens wird jeweils zum gleichen Ländercode um den Datensatz des Landes ergänzt.

Anstelle des *-Operators zur Auswahl aller Felder der verbundenen Datensätze verwendet die SELECT-Anweisung eine Auswahlliste. Diese benennt die zu selektierenden Felder. Da das Feld name in beiden Tabellen vorkommt, geschieht die Adressierung unter Zuhilfenahme der Tabellennamen: airports.name bzw. countries.name. Zur Trennung von Tabelle und Feldname wird die "."-Notation verwendet, bekannt aus der objektorientierten Programmierung.

Zur Adressierung der Komponenten des zusammengesetzten Datentyps location verwenden wir ebenfalls die "."-Notation. Wir berechnen die mittlere Lage aller Flughäfen durch Verwendung der Aggregatfunktion avg (average).

SELECT avg((location).latitude) AS latitude,
  avg((location).longitude) AS longitude
FROM airports;

   latitude   | longitude
--------------+-----------
 31.54        |     72.12
(1 row)

Zum Zugriff auf die Komponenten müssen wir den Namen des Feldes in Klammern setzen, da sonst der Feldname location hier wie oben als Tabellenname interpretiert werden würde. Die Funktion avg bildet den arithmetischen Mittelwert über alle Werte der angegeben Komponenten des Feldes location. Auch diese SELECT-Anweisung benutzt Aliase zur Feldbezeichnung in der Ausgabe. Dem Schlüsselwort AS folgen die Bezeichner latitude oder longitude für die Ausgabe. Aliase werden aber auch häufig innerhalb von SQL-Anweisungen als Abkürzungen verwendet. Des Weiteren kommt die "."-Notation bei der Auflösung von Datenbankobjekten nach Datenbankschemen zum Einsatz. Datenbankobjekte können verschiedenen Schemen zugeordnet werden.

CREATE SCHEMA public
  AUTHORIZATION postgres;

Ein Datenbankschema wird mit dem DDL-Befehl CREATE SCHEMA angelegt. Ohne Angabe eines Schemas werden die Datenbankobjekte einem primären Schema, zumeist public, zugeordnet. Das Schema public existiert in einer leeren PostgreSQL Datenbank. Zur Demonstration der Verwendung eines Schemanamens wählen wir durch die folgende SELECT-Anweisung alle Ländercodes aus der Tabelle airports einmalig aus.

SELECT DISTINCT country
FROM public.airports
ORDER BY country;

 country
---------
 AT
 DE
 ES
 AU
 US
(5 rows)

Anstelle des Tabellennamens airports verwenden wir den schemagebundenen Namen der Tabelle: public.ariports. Die Tabelle wird ohne Schema-Angabe gefunden, da das Schema public im Suchpfad der Schemen liegt. Dieses überprüfen wir durch folgende Anweisung.

SHOW search_path;

  search_path
----------------
 "$user",public
(1 row)

Zur einmaligen Auswahl des Ländercodes nutzen wir die DISTINCT-Bedingung. Obwohl in Deutschland zwei Flughäfen liegen, wird der Ländercode für Deutschland nur einmalig ausgegeben. Ohne Angabe der Sortierreihenfolge sortiert die ORDER BY-Bedingung aufsteigend (ASC). Wegen dem DISTINCT-Bedingung darf die ORDER BY-Bedingung jedoch nur das Feld country enthalten. Zum Entfernen eines Schemas verwenden wir den DDL-Befehl DROP SCHEMA.

DROP SCHEMA public;

Flugbetrieb

Der Flugbetrieb auf dem Flughafen wird durch Fluglinien unterhalten. Diese werden in der Tabelle airlines gespeichert.

CREATE TABLE airlines (
 airline_id char(2) NOT NULL,
 name varchar(256) NOT NULL,
 airport_id char(3) NOT NULL,
 CONSTRAINT pk_airline_id PRIMARY KEY (airline_id),
 CONSTRAINT fk_airport_id FOREIGN KEY (airport_id) REFERENCES airports (airport_id)
);

Als Primärschlüssel verwenden wir hier den zweistelligen IATA Code für Fluglinien. Wir speichern zudem einen Namen und den Stammflughafen der Fluglinie. Dieser Flughafen verweist über den Fremdschlüssel airport_id auf die Tabelle airports.

INSERT INTO airlines (airline_id, name, airport_id) VALUES ('LH', 'Lufthansa', 'FRA');
INSERT INTO airlines (airline_id, name, airport_id) VALUES ('AB', 'Air Berlin', 'TXL');
INSERT INTO airlines (airline_id, name, airport_id) VALUES ('IB', 'Iberia Airlines', 'PMI');

Jede Fluggesellschaft bietet eine Vielzahl von Flügen an. Diese werden in der Tabelle flights gespeichert.

CREATE TABLE flights (
 flight_number varchar,
 airline_id char(2) NOT NULL,
 "from" char(3) NOT NULL,
 departure timestamp(0) without time zone NOT NULL,
 "to" char(3) NOT NULL,
 arrival timestamp(0) without time zone NOT NULL,
 seats integer NOT NULL CHECK ( seats > 0),
 price numeric(6, 2) DEFAULT 0.0,
 CONSTRAINT pk_flight_number PRIMARY KEY (flight_number),
 CONSTRAINT fk_airlind_id FOREIGN KEY (airline_id) REFERENCES airlines (airline_id),
 CONSTRAINT fk_from FOREIGN KEY ("from") REFERENCES airports (airport_id),
 CONSTRAINT fk_to FOREIGN KEY ("to") REFERENCES airports (airport_id)
);

In dieser Tabelle wird der Primärschlüssel durch das Feld flight_number gegeben. Die Tabelle verwendet drei Fremdschlüssel: airline_id verweist auf die Fluglinie, from auf den Abflugs-, to auf den Ankunftsflughafen.

Die Felder departure und arrival geben die Zeit des Abfluges sowie der Ankunft - beides zur Ortszeit - wieder. Der Datentyp timestamp gibt ein Datum mit einer Uhrzeit an, inkl. Sekunden und Millisekunden. Die Angabe without time zone verzichtet auf die Verwendung von Zeitzonen. Der Datentyp timestamp kann parametrisiert werden: timezone(n). Der Parameter n gibt den Anteil von Millisekunden wieder, die gespeichert werden sollen. Die Angabe 0 unterdrückt die Speicherung von Millisekunden.

SELECT now()::timestamp(0) without time zone, now()::timestamp with time zone;
         now         |            now
---------------------+----------------------------
 2011-04-18 09:55:46 | 2011-04-18 09:55:46.191+02
(1 row)

Die SELECT-Anweisung wandelt die aktuelle Zeitangabe aus dem Funktionsaufruf now() in einen Wert vom Datentyp timestamp um. In der ersten Spalte werden Zeitzone und Millisekunden unterdrückt. Die Angabe +02 in der zweiten Spalte gibt die Zeitzone an.

Das Feld seats beziffert die Zahl der Plätze für Passagiere auf einem Flug. Auf allen Flügen gibt es nur eine Klasse. Darum ist die Unterscheidung der Sitze nach Klassen nicht notwendig. Für die Anzahl der Sitze wird eine Mindestzahl von einem Sitz gefordert. Dies wird durch die Angabe der Integritätsbedingung CHECK ( seats > 0 ) sicher gestellt.

Zudem wird der Preis des Fluges als Fliesskommazahl mit zwei Nachkommastellen gespeichert. Das Feld price ist vom Datentyp numeric. Im Gegensatz zum Datenyp float speichert der Datentyp numeric exakte Werte. Der Datentyp float nähert die Menge der reellen Zahlen durch eine Teilmenge der ganzer Zahlen. Eben wie der Datentyp timestamp können die Datentypen numeric und float parametrisiert werden: numeric(p, n). Der Parameter p gibt die Genauigkeit in Form der zu speichernden Stellen an, der Parameter n die Anzahl, die davon auf Nachkommastellen entfallen.

SELECT 1234567890.987654321::float, 1234567890.987654321::numeric;
      float8      |       numeric
------------------+----------------------
 1234567890.98765 | 1234567890.987654321
(1 row)

Wie wir sehen wird die Zahl im Falle von float nur genähert, während für numeric die gleiche Zahl exakt wiedergegeben wird. Da Beträge von Währungen stets exakt gespeichert werden sollen verwenden wir den Datentyp numeric für den Preis des Fluges.

INSERT INTO flights (flight_number, airline_id, "from", departure, "to", arrival, seats, price) VALUES ('AB 8550', 'AB', 'TXL', '2011-05-01 17:15', 'GRZ', '2011-05-01 18:35', 150, 105.00);
INSERT INTO flights (flight_number, airline_id, "from", departure, "to", arrival, seats, price) VALUES ('AB 8510', 'AB', 'GRZ', '2011-05-08 19:25', 'TXL', '2011-05-08 20:40', 150, 105.00);
INSERT INTO flights (flight_number, airline_id, "from", departure, "to", arrival, seats, price) VALUES ('LH 456', 'LH', 'FRA', '2011-05-01 09:55', 'LAX', '2011-05-01 12:35', 150, 650.00);
INSERT INTO flights (flight_number, airline_id, "from", departure, "to", arrival, seats, price) VALUES ('LH 457', 'LH', 'LAX', '2011-06-01 14:55', 'FRA', '2011-06-02 10:35', 150, 650.00);
INSERT INTO flights (flight_number, airline_id, "from", departure, "to", arrival, seats, price) VALUES ('AB 9172', 'AB', 'TXL', '2011-04-28 06:00', 'PMI', '2011-04-28 08:35', 150, 105.00);
INSERT INTO flights (flight_number, airline_id, "from", departure, "to", arrival, seats, price) VALUES ('AB 9143', 'AB', 'PMI', '2011-07-22 21:30', 'TXL', '2011-07-22 23:45', 150, 84.03);

Um die Einträge in den beiden Tabellen airlines und flights zu überprüfen wollen wir mittels einer SELECT-Anweisung die Anzahl der Flüge pro Fluglinie zählen.

SELECT name,
 count(*)
FROM flights
 JOIN airlines USING (airline_id)
GROUP BY name
ORDER BY count(*) DESC;

    name    | count
------------+-------
 Air Berlin |     4
 Lufthansa  |     2
(2 rows)

Diese SELECT-Anweisung verbindet die zwei Tabellen airlines und flights mittels eines Verbundes. Die gelesenen Datensätze aus der Tabelle flights werden jeweils um den gelesenen Datensatz aus der Tabelle airlines ergänzt, der den Wert des Fremdschlüssel, airlinge_id, als Primärschlüssel verwendet. Die JOIN-Bedingung ist über die USING-Bedingung gegeben.

Die Ergebnismenge des Verbundes wird mittels der GROUP BY-Bedingung nach den Namen der Fluglinie gruppiert, d.h. Datensätze zu gleichen Namen werden der gleichen Gruppe zugeordnet. Für jede Gruppe wird die Anzahl der Gruppenmitglieder mittels der Aggregat-Funktion count gezählt.

So wie wir Verbunde bis eben eingesetzt haben liefert sie nur Resultate, wenn der Wert des Primärschlüssels auch in der verbundenen Tabelle als Fremdschlüssel verwendet wird. Wenn wir jedoch eine Übersicht aller Primärschlüssel erhalten wollen, müssen wir einen anderen Verbundtyp verwenden. Wir wollen eine Übersicht der Flüge aller Fluglinien geben, auch wenn sie keine Flüge anbietet. Dazu verfeinern wir die letzte SELECT-Anweisung mittels eines LEFT JOIN.

SELECT name,
 count(flight_number)
FROM airlines
 LEFT JOIN flights USING (airline_id)
GROUP BY name
ORDER BY count(*) DESC;

      name       | count
-----------------+-------
 Air Berlin      |     4
 Lufthansa       |     2
 Iberia Airlines |     0
(3 rows)

Wir fragen alle Fluglinien ab und ergänzen diese um die Angaben von allen Flügen. Dazu wird eine Kopie eines Datensatzes aus der Tabelle airlines um jeweils einen Datensatz aus der Tabelle flights ergänzt und in die Ergebnismenge des Verbundes aufgenommen. Im Falle der Fluglinie, die keinen Flug anbietet, werden die Felder für die Tabelle flights mit NULL Werten belegt. Vor dem Auszählen der Ergebnismenge wird diese noch nach den Namen der Fluglinie gruppiert. Die Aggregatfunktion count zählt den Primärschlüssel der Tabelle flights, flight_number, nun für jede Fluglinie. Da für die Fluglinie, die keine Flüge anbietet, ein NULL Wert im Feld flight_number steht, wird hier eine 0 gezählt.

Wir nennen im Folgenden die linke Tabelle eines LEFT JOIN die treibende Tabelle. Für einen RIGHT JOIN nennen wir die rechte Tabelle die treibende Tabelle. Mittels eines FULL JOINs werden beide Tabellen zu treibenden Tabellen. Verbunde verbinden aber nicht nur Tabellen, sondern auch Ergebnismengen.

SELECT color.column1 AS col,
  painted.column1 AS pid
FROM (VALUES ('blue', 200), ('green', 300), ('red', 600)) AS color
  JOIN (VALUES(1, 'blue'), (2, 'blue'), (3, 'green')) AS painted
  ON (color.column1 = painted.column2);

JOIN             LEFT JOIN        RIGHT JOIN       FULL JOIN
  col  | pid       col  | pid       col  | pid       col  | pid
-------+-----    -------+-----    -------+-----    -------+-----
 blue  |   2      blue  |   2      blue  |   1      blue  |   1
 blue  |   1      blue  |   1      blue  |   2      blue  |   2
 green |   3      green |   3      green |   3      green |   3
(3 rows)          red   | NULL    (3 rows)          red   | NULL
                 (4 rows)                          (4 rows)

In dieser SELECT-Anweisung werden über die VALUES-Bedingung zwei Ergebnismengen gebildet und mittels eines Verbundes zusammengeführt. Für die Ergebnisse oben werden die vier beschriebenen Verbundtypen eingesetzt. Für den JOIN, eigentlich INNER JOIN, wird der Primärschlüssel red nicht als Fremdschlüssel verwendet. Darum fehlt ein Eintrag in der Ergebnismenge. Für den LEFT JOIN, eigentlich LEFT OUTER JOIN genannt, wird der Eintrag für red in jedem Falle aus der treibenden Tabelle color in die Ergebnismenge übernommen und um einen NULL Wert für kein bemaltes Objekt ergänzt. Für den RIGHT JOIN, RIGHT OUTER JOIN, fehlt der Eintrag für red in der treibenden Tabelle painted und kann so nicht in die Ergebnismenge aufgenommen werden. Für den FULL JOIN, FULL OUTER JOIN, sorgt die treibende Tabelle color für die Aufnahme des Eintrages red in die Ergebnismenge.

Fluggäste

Fluggäste werden durch Personen repräsentiert, die an verschiedenen Flügen teilnehmen können. Diese Abstraktion der Fluggäste vermeidet Mehrfachspeicherungen von Personendaten. Daten, die mehrfach gespeichert werden können immer zu Inkonsistenzen der Daten führen. Wird bei der Änderung eines Datenwertes eine Mehrfachspeicherung Kopie vergessen, so wird dadurch ein neuer Datensatz erzeugt, der durch die ausgebliebene Änderung bereits veraltet ist.

Bevor wir eine Tabelle zur Verwaltung von Personen anlegen, definieren wir noch einen Datentypen, der die Anrede der Person gewährleistet.

CREATE TYPE address AS ENUM ('MS', 'MR');

Der DML-Befehl CREATE TYPE erzeugt den Datentyp address als Aufzählung (enumaration). Eine Variable vom Typ address kann entweder den Wert MS (Miss) oder MR (Mister) annehmen.

CREATE TABLE persons (
 person_id serial,
 address address,
 prename varchar(128) NOT NULL,
 surname varchar(128)NOT NULL,
 nationality char(2) NOT NULL,
 date_of_birth date NOT NULL,
 passport_number varchar(256) NOT NULL,
 CONSTRAINT pk_person_id PRIMARY KEY (person_id),
 CONSTRAINT fk_nationality FOREIGN KEY (nationality) REFERENCES countries (country_id)
);

Als Primärschlüssel verwenden wir hier den Surrogatschlüssel person_id vom Datentyp serial. Surrogatschlüssel werden der Einfachheit halber häufig Tabellen hinzugefügt. Sie versehen jeden Datensatz mit einer eindeutigen Zahl. Da diese Zahl nur zum internen Gebrauch gedacht ist, muss der Fall einer Änderung eines Primärschlüssel meist nicht überdacht werden. Außerdem lassen sich dadurch auch aus mehreren Feldern zusammengesetzte Primärschlüssel vermeiden. Zudem speichert die Tabelle persons noch die Anrede, den Vor-, Nachnamen, die Nationalität, das Geburtsdatum und die Passnummer einer Person. Das Geburtsdatum ist vom Datentyp date. Dieser Datentyp umfasst keinen Zeitanteil. Die Nationalität wird über den Fremdschlüssel nationality ausgedrückt.

Die Definition eines Datenbankfeldes vom PostgreSQL eigenen Datentyp serial hat einen Nebeneffekt. Zugleich wird ein Zähler (Sequenz) angelegt, dessen aktueller Wert in das Feld übertragen werden kann. Dazu wird der DDL-Befehl CREATE SEQUENCE verwendet.

INSERT INTO persons (address, prename, surname, nationality, date_of_birth, passport_number) VALUES ('MR', 'Andreas', 'Meier', 'DE', '01.01.1970', 'M7020100');
INSERT INTO persons (address, prename, surname, nationality, date_of_birth, passport_number) VALUES ('MS', 'Manina', 'Lehmann', 'DE', '23.12.1950', 'F5037945');
INSERT INTO persons (address, prename, surname, nationality, date_of_birth, passport_number) VALUES ('MS', 'Anja', 'Pfeiffer', 'DE', '05.06.1969', 'F6948129');
INSERT INTO persons (address, prename, surname, nationality, date_of_birth, passport_number) VALUES ('MS', 'Alexandra', 'Hofer', 'AT', '12.11.1974', 'A00G221');

Eine Eigenschaft des Datentyps serial besteht darin, dass der aktuelle Wert des Zählers in das Feld von diesem Typ eingefügt wird, sofern kein Wert durch eine INSERT INTO-Anweisung gegeben ist. Anschließend wird der Zähler erhöht. Um die automatische Belegung des Feldes person_id zu überprüfen fragen wir über folgende SELECT-Anweisung alle Flughäfen ab.

SELECT * FROM persons;

 person_id | address |  prename  | surname  | nationality | date_of_birth | passport_number
-----------+---------+-----------+----------+-------------+---------------+-----------------
         1 | MR      | Andreas   | Meier    | DE          | 1970-01-01    | M7020100
         2 | MS      | Manina    | Lehmann  | DE          | 1950-12-23    | F5037945
         3 | MS      | Anja      | Pfeiffer | DE          | 1969-06-05    | F6948129
         4 | MS      | Alexandra | Hofer    | AT          | 1974-11-12    | A00G221
(4 rows)

Zur Teilnahme an einem Flug werden die Personen als Fluggäste aufgeführt. Dies geschieht durch die Tabelle passangers.

CREATE TABLE passangers (
 flight_number varchar NOT NULL,
 person_id integer NOT NULL,
 checkin boolean DEFAULT false,
 CONSTRAINT pk_flight_number_person_id PRIMARY KEY (flight_number, person_id),
 CONSTRAINT fk_flight_number FOREIGN KEY (flight_number) REFERENCES flights (flight_number),
 CONSTRAINT fk_person_id FOREIGN KEY (person_id) REFERENCES persons (person_id)
);

Da Personen nur einmal an einem Flug teilnehmen dürfen, ist die Angabe aus Flugnummer und Person eindeutig. Wir verwenden beide Felder zusammen als zusammengesetzten Primärschlüssel.

CONSTRAINT pk_flight_number_person_id PRIMARY KEY (flight_number, person_id)

Neben den Fremdschlüsseln flight_number und person_id enthält die Tabelle passangers noch das Feld checkin vom Datentyp boolean. Dieses Feld gibt an, ob ein Passagier bereits eingecheckt ist. Beim Anlegen des Datensatzes ist der Passagier auf keinen Fall eingecheckt. Deshalb verwenden wir den Initialisierungswert false.

INSERT INTO passangers (flight_number, person_id) VALUES ('AB 8510', 1);
INSERT INTO passangers (flight_number, person_id) VALUES ('AB 8550', 1);
INSERT INTO passangers (flight_number, person_id) VALUES ('AB 8510', 4);
INSERT INTO passangers (flight_number, person_id) VALUES ('LH 456', 1);
INSERT INTO passangers (flight_number, person_id) VALUES ('LH 456', 2);
INSERT INTO passangers (flight_number, person_id) VALUES ('LH 456', 3);
INSERT INTO passangers (flight_number, person_id) VALUES ('LH 456', 4);
INSERT INTO passangers (flight_number, person_id) VALUES ('LH 457', 1);
INSERT INTO passangers (flight_number, person_id) VALUES ('LH 457', 3);
INSERT INTO passangers (flight_number, person_id) VALUES ('LH 457', 4);
INSERT INTO passangers (flight_number, person_id) VALUES ('AB 9172', 1);
INSERT INTO passangers (flight_number, person_id) VALUES ('AB 9172', 2);
INSERT INTO passangers (flight_number, person_id) VALUES ('AB 9172', 4);
INSERT INTO passangers (flight_number, person_id) VALUES ('AB 9143', 1);
INSERT INTO passangers (flight_number, person_id) VALUES ('AB 9143', 2);
INSERT INTO passangers (flight_number, person_id) VALUES ('AB 9143', 4);

Um die eingefügten Daten in beiden Tabellen zu überprüfen verwenden wir wiederum eine SELECT-Anweisung, die uns die Anzahl von Fluggästen nach Ländern auszählen soll.

SELECT co.name AS country,
 count(person_id) AS total
FROM countries co
 LEFT JOIN persons ps ON (ps.nationality = co.country_id)
 LEFT JOIN passangers pg USING (person_id)
GROUP BY co.name
HAVING count(person_id) > 0
ORDER BY total DESC;

 country | total
---------+-------
 Germany |    11
 Austria |     5
(2 rows)

In dieser SELECT-Anweisung werden drei Tabellen miteinander verbunden. Zunächst werden die Tabellen countries und persons über die JOIN-Bedingung ON (ps.nationality = co.country_id) mittels eines LEFT JOINes verbunden. Diese Ergebnismenge wird zudem über einen LEFT JOIN mit der Tabelle passangers mittels der JOIN-Bedingung USING (person_id) verbunden. Die so gebildete Ergebnismenge wird durch die GROUP BY-Bedingung nach den Namen der Länder gruppiert und ausgezählt. Wiederum zählt die Aggregatfunktion count ein Primärschlüsselfeld, person_id, so dass sich für Länder ohne Fluggäste eine 0 ergibt. Die HAVING-Bedingung schließt nun wiederum diese Länder aus. Die HAVING-Bedingung kann nur in Kombination mit der GROUP BY-Bedingung eingesetzt werden. Sie dient der Filterung der gruppierten Ergebnismenge.

Tower

Im Tower unseres Flughafens wird die Einhaltung der Daten aus der Datenbank im Flugverkehr überwacht. Dazu werden die Daten gesondert aufbereitet. Eine Möglichkeit bietet SQL durch Verwendung von Sichten (Views). Eine Sicht ist eine gespeicherte SELECT-Anweisung, die bei der Abfrage erneut ausgeführt wird. Es gibt auch noch materialisierte Sichten, in denen die Ergebnisse einer Sicht wie in einer Tabelle vorgehalten werden - für PostgreSQL derzeit aber nicht. Folgende Sicht gibt die Übersicht über die Abflüge und Ankünfte innerhalb der nächsten Stunde.

CREATE VIEW timetable AS
  SELECT 'departure' AS dir, "from" AS airport, airline_id AS airline, "from", departure, "to", arrival FROM flights WHERE departure BETWEEN now() AND now()+ interval '1 hour'
  UNION
  SELECT 'arrival' AS dir, "to" AS ariport, airline_id AS airline, "from", departure, "to", arrival FROM flights WHERE arrival BETWEEN now() AND now() + interval '1 hour';

Eine Sicht wird mit dem DDL-Befehl CREATE VIEW erstellt. Dem Befehl folgt der Name, timetable. Nach dem Schlüsselwort AS ist hier die Vereinigungsmenge zweier SELECT-Anweisungen mittels des Operators UNION angegeben. Dazu werden zunächst beide SELECT-Anweisungen einzeln ausgeführt. Es ergeben sich zwei Ergebnismengen, die vereinigt werden. Die Auswahl der Flüge für die nächste Stunde geschieht durch die WHERE-Bedingungen. Der BETWEEN-Operator gibt ein Zeitintervall vor. Die untere Schranke ist der Zeitpunkt der Ausführung der SELECT-Anweisung - zu diesem wird die Funktion now() aufgerufen. Die obere Schranke ist ebenfalls der Zeitpunkt der Ausführung der SELECT-Anweisung zzgl. des Zeitintervalls von einer Stunde. Zur Markierung der ein- und abgehenden Flüge werden die Flüge im Feld dir mit departure oder arrival markiert.

Zum Löschen der Sicht wird der Befehl DROP VIEW verwendet.

DROP VIEW timetable;

Sichten können wie Tabellen abgefragt werden. Um die Abflüge und Ankünfte eines Flughafens zu betrachten werden nur Abflüge und Ankünfte an dem Flughafen mit der airport_id = 'TXL' betrachtet.

SELECT * FROM timetable WHERE airport = 'TXL';

 direction | local_airport | flight_number | airline_id | from | departure | to | arrival | seats | price
-----------+---------------+---------------+------------+------+-----------+----+---------+-------+-------
(0 rows)

Damit die Ergebnismenge nicht leer bleibt, definieren wir eine Funktion, die alle Abflüge der Tabelle flights in die nächste Stunde legt und die Ankünfte entsprechend der Flugzeit anpasst.

CREATE FUNCTION update_flights()
RETURNS void
AS
$$
  UPDATE flights
  SET departure = current_timestamp + (departure - date_trunc('hour', departure)),
        arrival = current_timestamp + (departure - date_trunc('hour', departure))
                  + (arrival - departure);
$$ LANGUAGE sql;

Funktionen werden in PostgreSQL mittels des DDL-Befehls CREATE FUNCTION erstellt und permanent im Datenbanksystem gespeichert. Darum werden die Funktionen häufig auch als Stored Procedures bezeichnet (s. Oracle). Bei Bedarf werden die Funktionen "compiliert". Dem Namen der Funktion, update_flights, folgt die Liste der Funktionsparameter. Die Funktion erwartet keine Aufrufparameter. Da die Funktion keine Ergebnismenge zurückgibt, wird nach dem Schlüsselwort RETURNS void angegeben. Der Rumpf der Funktion kann für PostgreSQL in verschiedenen Sprachen implementiert werden: SQL, PL/pgSQL, PL/Tcl, PL/Perl und PL/Python. Der Rumpf der Funktion wird durch die Zeichenfolgen $$ (dollar quoted string) begrenzt. Er enthält im Falle der Funktion update_flights eine einzelne SQL-Anweisung. Wir vereinbaren als Sprache SQL. Dazu notieren wir LANGUAGE sql am Ende der Definition.

Die UPDATE-Anweisung im Rumpf der Funktion setzt den Abflug eines jeden Fluges auf die aktuelle Zeitangabe, current_timestamp. Zu diesem Wert wird der Minuten-Anteil aus dem ursprünglichen Abflug hinzuaddiert. Dazu wird die Abflugszeit jeweils auf die begonnene Stunde abgerundet. Dies geschieht durch die Funktion date_trunc mit der Angabe hour im ersten Argument. Der Minutenanteil ergibt sich nun aus der Differenz der Abflugszeit mit der abgerundeten Abflugszeit. Für die neue Ankunftszeit wird zu dieser Angabe noch die Flugzeit hinzuaddiert. Wir führen die Funktion mittels einer SELECT-Anweisung aus.

SELECT update_flights();
 update_flights
----------------

(1 row)

Anschließend fragen wir nochmals die Sicht timetable für einen Flughafen ab.

SELECT * FROM timetable WHERE airport = 'TXL';

    dir    | airport | airline | from |      departure      | to  |       arrival
-----------+---------+---------+------+---------------------+-----+---------------------
 departure | TXL     | AB      | TXL  | 2011-04-27 18:09:19 | GRZ | 2011-04-27 19:29:19
 departure | TXL     | AB      | TXL  | 2011-04-27 18:54:19 | PMI | 2011-04-27 21:29:19
(2 rows)

Zum Löschen der Funktion verwenden wir den DDL-Befehl DROP FUNCTION.

DROP FUNCTION update_flights();

Zur Betankung der Flugzeuge müssen die geflogenen Distanzen zwischen Start und Ziel bekannt sein. Unsere Erde haben wir der Einfachheit halber als Kugel mit einem Radius von 6730 km beschrieben. Die kürzeste Distanz zwischen zwei Punkten auf der Kugeloberfläche wird durch einen Teil eines Großkreisbogens beschrieben (Orthodrom). Orthodrome werden in unserem Beispiel als Routen genutzt. Zur Berechnung der Länge einer Route definieren wir folgende Funktion.

CREATE FUNCTION orthodromic_distance(location, location)
RETURNS float
AS
$$
DECLARE
  v_from ALIAS FOR $1;
  v_to ALIAS FOR $2;
  v_result float;
BEGIN
  IF ($1=$2) THEN
    v_result := 0.0;
  ELSE  
     v_result := acos(
             sin(radians(v_from.latitude))*sin(radians(v_to.latitude))
            +cos(radians(v_from.latitude))*cos(radians(v_to.latitude))
              *cos(radians(v_to.longitude)-radians(v_from.longitude))
            )*6370.0;
  END IF;

  RETURN v_result;
END;
$$ LANGUAGE plpgsql;

Zur Definition der Funktion verwenden wir wiederum den DDL-Befehls CREATE FUNCTION. Dem Namen der Funktion, orthodromic_distance, folgt die Liste der Funktionsparamenter. Die Funktion erwartet zwei Ortsangaben vom Typ location. Der Rückgabewert, nach Schlüsselwort RETURNS, ist eine reelle Zahl vom Typ float. Der Rumpf der Funktion ist in der PostgreSQL eigenen prozeduralen Sprache PL/pgSQL (Procedural Language pgSQL) implementiert.

Sind die beiden Ortsangaben aus dem Funktionsaufruf orthodromic_distance identisch, so wird als Distanz 0.0 zurückgegeben. Der Vergleichsoperator = vergleicht in diesem Fall komponentenweise. Andernfalls wird über die Formel im ELSE-Zweig der IF-Anweisung die Länge des Orthodroms in Kilometern berechnet und zurückgegeben. Die Funktion radians rechnet die Grad-Zahl in Bogenmass um. Die Parameter aus dem Funktionsaufruf werden im DECLARE-Block durch den Aliasmechanismus in lokale Variablen übersetzt. Die Werte aus dem Funktionsaufruf werden über $1 und $2, entsprechend ihrer Position in der Parameterliste, dereferenziert. Zum Entfernen der Funktion wird der DDL-Befehl DROP FUNCTION genutzt.

DROP FUNCTION orthodromic_distance(location, location);

Da PostgreSQL die Überladung von Funktionen erlaubt, ist die Angabe der Parameterliste erforderlich. Um eine Übersicht über die Abstände zwischen allen Flughäfen zu generieren, erstellen wir folgende Sicht unter Zuhilfenahme eines Kartesischen Produktes.

CREATE VIEW airports_distance AS
  SELECT a.airport_id AS "from", b.airport_id AS "to", orthodromic_distance(a.location, b.location) AS distance
    FROM airports a, airports b;

Die SELECT-Anweisung verbindet die Tabelle airports mit sich selber, genannt SELF JOIN, ohne eine JOIN-Bedingung zu nutzen. Deshalb wird jedem Datensatz der Tabelle airports genau einmal jeder Datensatz aus der gleichen Tabelle zugeordnet. Ist n die Dimension der Tabelle (Anzahl von Zeilen), so wird die Ergebnismenge n*n Zeilen umfassen.

SELECT * FROM airports_distance LIMIT 10;

 from | to  |     distance
------+-----+------------------
 TXL  | TXL |                0
 TXL  | LAX | 9319.42892823799
 TXL  | PMI | 1655.85872296913
 TXL  | GRZ | 619.751073066838
 TXL  | FRA | 434.422849092028
 TXL  | SYD | 16100.8622060376
 LAX  | TXL | 9319.42892823799
 LAX  | LAX |                0
 LAX  | PMI | 9850.19095355263
 LAX  | GRZ | 9888.90382700642
(10 rows)

Um die Größe der Abfrage einzuschränken verwendet die SELECT-Anweisung die Angabe LIMIT 10. Die Ausgabe wird auf 10 Datensätze beschränkt. Zur Berechnung des Abstandes wird für je zwei Ortsangaben aus einem Datensatz die Funtion orthodromic_distance aufgerufen. Wir wollen nun den Tankplan für den nächsten Tag berechnen. Zunächst definieren wir einen Datentypen, der den Rückgabewert der Funktion beschreibt.

CREATE TYPE fuel AS (
  flight_number varchar,
  "from" char(3),
  "to" char(3),
  fuel float
);

PL/pgSQL bietet bekannte Sprachkonstrukte einer Programmiersprache, wie Verzweigungen, Schleifen, Variablen, sowie die Verwendung datenbankspezifischer Konstrukte wie Cursor. PL/pgSQL ermöglicht Rekursionen sowie die Ausführung dynamischen SQLs mittels des Befehls EXECUTE. PL/pgSQL Programmtexte werden in Blöcke unterteilt. In einem DECLARE-Block können lokale Variablen deklariert werden, die im Folgenden BEGIN END-Block sichtbar sind. Zur Fehlerbehandlung bietet PL/pgSQL die Behandlung von exceptions an.

CREATE FUNCTION add_fuel(v_airport char, v_limit float, v_miles float)
RETURNS SETOF fuel
AS
$$
DECLARE
  v_total float := 0.0;
  v_rec record;
BEGIN
  BEGIN
    FOR v_rec IN SELECT flight_number, "from", "to", v_miles*orthodromic_distance("from_airport".location, "to_airport".location) AS fuel
               FROM flights JOIN airports "from_airport" ON ("from" = airport_id)
               JOIN airports "to_airport" ON ("to" = to_airport.airport_id)
               WHERE "from" = v_airport
                 AND departure BETWEEN current_date AND current_date + interval '1 day' 
    LOOP
      v_total := v_total + v_rec.fuel;    
      IF v_total > v_limit THEN
       RAISE WARNING 'order new fuel for flight %', v_rec.flight_number;
      END IF;
      RETURN NEXT v_rec;
    END LOOP;
  EXCEPTION
    WHEN others THEN
      RAISE WARNING 'error detected: % (%)', SQLERRM, SQLSTATE;
  END;
  IF v_total > v_limit THEN
    RAISE WARNING 'fuel limit reach, order % galons of kerosine', (v_total - v_limit);
  END IF;

  RETURN;
END;
$$ LANGUAGE plpgsql;

Diese Funktion summiert den erforderlichen Kerosinverbrauch an einem Flughafen für den nächsten Tag mittels einer FOR-Schleife. Die Funktion erwartet als Parameter die Kennung des Flughafens, die gelagerte Kerosinmenge und den Kerosinverbrauch pro geflogenen Flugkilometer. In der Parameterliste dieser Funktion werden explizit Namen für die Parameter vereinbart. Die Dereferenzierung mittels der Bezeichner $1, $2, etc. entfällt deswegen. Als Rückgabewert verwendet die Funktion eine Menge, SETOF, Werte vom Datentyp fuel.

Die FOR-Schleife erstreckt sich über die Ergebnismenge der SQL-Anweisung nach dem Schlüsselwort IN. Der Block innerhalb der FOR-Schleife wird für jeden Datensatz der Ergebnismenge einmalig ausgeführt. Dazu werden die Felder eines Datensatzes in Komponenten der record-Variable v_rec überführt. Der PL/pgSQL Datentyp record ist flexibel und kann während der Laufzeit mit weiteren Komponenten versehen werden.

Den Variablennamen ist der Präfix "v_" vorangestellt, um eine Überschneidung mit den Feldnamen von Tabellen oder Sichten zu vermeiden. Dies wird offenkundig für die Parametrisierung der SELECT-Anweisung. Dort wird in der SQL-Anweisung einfach der Wert der Variablen v_airport in dem Ausdruck WHERE "from" = v_airport zur Ausführung der Anweisung ersetzt.

Die Variable v_total summiert den Kerosinverbrauch aller Flüge. Ist die am Flughafen gelagerte Menge an Kerosin, v_limit, überschritten, so wird eine Warnung für die Flüge ausgegeben, für die voraussichtlich kein Kerosin mehr vorhanden ist. Jeder Datensatz wird mit einer RETURN NEXT-Anweisung zunächst in einen internen Stapelspeicher abgelegt. Die Funktion wird erst mittels RETURN, ohne Argument, beendet. Danach wird die Ergebnismenge zurückgegeben.

Jeder Fehler, der im Block der FOR-Schleife auftritt, wird als Warnung gemeldet. Dies geschieht im exception-Block für die Fehlerkategorie, others (alle Fehler) durch Verwendung des Befehles RAISE. Der Wert der Fehlermeldung und des Fehlercodes werden aus den im Exception Block definierten Variablen SQLEERM und SQLSTATE ausgelesen und in die Zeichenkette 'error detected: % (%)' für die Prozentzeichen eingesetzt. Das Niveau der RAISE-Anweisung wird auf WARNING gesetzt.

SELECT * FROM add_fuel('TXL', 100000.00, 50.00);

HINWEIS:  order new fuel for flight AB 9172
WARNUNG:  fuel limit reach, order 13780 galons of kerosine
 flight_number | from | to  |       fuel
---------------+------+-----+------------------
 AB 8550       | TXL  | GRZ | 30987.5536533419
 AB 9172       | TXL  | PMI | 82792.9361484566
(2 rows)

Zur Berechnung der von den Fluggesellschaften einzuziehenden Steuern verwendet die Betreibergesellschaft eine Funktion, die die Steuern pro Fluggesellschaft berechnet.

CREATE FUNCTION taxes(char, char, float)
RETURNS TABLE(airline varchar, tax float, total bigint)
AS
$$
  SELECT al."name",
   coalesce(count(*), 0.0)*coalesce($3, 0.0) AS tax,
   count(flight_number) AS total
  FROM airlines al
   LEFT JOIN flights USING (airline_id)
   LEFT JOIN passangers USING (flight_number)
  WHERE airline_id = $2 
    AND airport_id = $1
  GROUP BY al.name;
$$ LANGUAGE sql;

Die Funktion taxes erwartet als Parameter die airport_id des Flughafens, die airline_id der Fluggesellschaft sowie die Höhe der Steuern pro Fluggast. Als Rückgabewert wird eine Tabelle mit den Feldern Name der Fluggesellschaft, einzuziehende Steuer und Anzahl der Fluggäste vereinbart. Dazu verwendet die RETURNS-Angabe der Funktion folgende Tabellendefinition.

TABLE(airline varchar, tax double precision, total bigint)

Die Tabellendefinition können wir als anonyme Typdefinition begreifen, denn auch Tabellen und Sichten weist PostgreSQL intern einen Datentypen mit einen Namen des jeweiligen Datenbankobjektes zu. So kann ein Tabellenname auch als Datentyp einer Variable in PL/pgSQL genutzt werden. Dieser Zeilentyp (row type) ist aus Komponenten, deren Name und Datentypen, der Tabellendefinition entsprechend zusammengesetzt.

DECLARE
  v_flight flights;
BEGIN

  v_flight.flight_number := 'AB 0001';
  v_flight.airline_id := 'AB';

END

Die Funktion coalesce verhindert die Verwendung des NULL-Wertes in den arithmetischen Ausdrücken. Sie liefert den ersten Parameterwert aus ihrem Aufruf, der vom Wert NULL verschieden ist.

SELECT coalesece(NULL, 0.0) AS "NOT NULL";

 "NOT NULL"
----------
      0.0
(1 row)

Im folgendem berechnen wir die Steuern für eine Fluggesellschaft am Flughafen Tegel.

SELECT * FROM taxes('TXL', 'AB', 4.99);

  airline   |  tax  | total
------------+-------+-------
 Air Berlin | 44.91 |     9
(1 row)

Fluggesellschaft

Zur Planung ihres Flugbetriebes werten die Fluggesellschaften ebenfalls die Daten der Datenbank aus. Zur Übersicht der Auslastung ihrer Flüge benutzt die Fluggesellschaft folgende Sicht.

CREATE VIEW booking AS
  SELECT airline_id,
    flight_number, 
    seats,
    count(*) AS booked,
   (seats-count(*)) AS free,
   round((count(*)::float/seats)*100) AS "average (%)"
  FROM airlines
    LEFT JOIN flights USING (airline_id)
    LEFT JOIN passangers USING (flight_number)
  WHERE airline_id = 'AB'
  GROUP BY airline_id, flight_number, seats;

Diese Sicht verbindet die drei Tabellen airlines, flights und passangers. Die Ergebnismenge wird nach Kennung der Fluglinie, Flugnummer und Sitzzahl gruppiert. Die Angabe der Sitzzahl erfolgt, um damit zu rechnen. Dieses Feld könnte in der Auswahlliste der SELECT-Anweisung sonst nicht verwendet werden! Es findet durch dieses Feld keine weitere Unterteilung der Gruppen statt, da für jeden Datensatz eines Fluges die Sitzzahl gleich ist. Die WHERE-Bedingung schränkt die Sicht auf die betrachtende Fluggesellschaft ein. Die Sicht zählt die gebuchten Plätze und errechnet die freien Plätze sowie die prozentuale Auslastung des Fluges.

SELECT * FROM booking;

 airline_id | flight_number | seats | booked | free | average (%)
------------+---------------+-------+--------+------+------------
 AB         | AB 8510       |   150 |      2 |  148 |          1
 AB         | AB 9172       |   150 |      3 |  147 |          2
 AB         | AB 9143       |   150 |      3 |  147 |          2
 AB         | AB 8550       |   150 |      1 |  149 |          1
(4 rows)

Für die Planung zukünftiger Produkte ist für die Fluggesellschaft das Passagieraufkommen an allen Flughäfen interessant. Dazu benutzt die Fluggesellschaft folgende Sicht top100_airports.

CREATE VIEW top100_airports AS
 SELECT airport_id,
   "name",
   sum(cnt) AS total
  FROM (
    SELECT airport_id,
     "name",
      count(flight_number) AS cnt
    FROM airports
      JOIN flights ON (airport_id="from")
      JOIN passangers USING (flight_number)
    GROUP BY airport_id, "name"
    UNION
    SELECT airport_id,
      "name",
      count(flight_number) AS cnt
    FROM airports
      JOIN flights ON (airport_id="to")
      JOIN passangers USING (flight_number)
    GROUP BY airport_id, "name"
  ) "inner-join"
GROUP BY airport_id, "name"
ORDER BY total DESC
LIMIT 100;

Diese Sicht enthält eine in Klammern gesetzte Unterabfrage (subselect), die mit den Namen inner-join benannt ist. Diese Unterabfrage ist aus zwei SELECT-Anweisungen zusammengesetzt. Die Ergebnismengen der SELECT-Anweisungen werden durch den UNION-Operator zusammengefasst. Die erste SELECT-Anweisung der Unteranfrage zählt die Passagiere, die von einem Flughafen abfliegen. Dazu werden die Flughäfen mit dem Feld from aus der Tabelle flights verbunden, nach Flughafen gruppiert und mittels count ausgezählt. In der zweiten SELECT-Anweisung der Unterabfrage werden die ankommenden Passagiere gezählt, in dem die Flughäfen mit dem Feld to aus der Tabelle flights verbunden werden. Die Ergebnismenge wird ebenfalls gruppiert und ausgezählt. Wir betrachten die Vereinigungsmenge der beiden SELECT-Anweisungen als Ergebnis der Unterabfrage.

 airport_id |           name            | cnt
------------+---------------------------+-----
 TXL        | Berlin - Tegel Airport    |   5
 TXL        | Berlin - Tegel Airport    |   4
 FRA        | Frankfurt Airport         |   4
 FRA        | Frankfurt Airport         |   3
 GRZ        | Graz Airport              |   1
 GRZ        | Graz Airport              |   2
 LAX        | Los Angeles Airport       |   4
 LAX        | Los Angeles Airport       |   3
 PMI        | Palma De Mallorca Airport |   3
(9 rows)

Nun werden für jeden Flughafen die Zahlen der ankommenden und abfliegenden Passagiere mittels der Aggregat-Funktion sum addiert. Die so gebildete Ergebnismenge wird noch nach dem Passagieraufkommen mittels der ORDER BY-Bedingung absteigend sortiert. SQL kennt neben dem Vereinigungsoperator UNION noch weitere Mengenoperatoren; INTERSECT (Schnittmenge) und EXCEPT (Differenzmenge).

SELECT * FROM ( VALUES (1,2), (3,4) ) AS M1
INTERSECT
SELECT * FROM ( VALUES (3,4) ) AS M2
-- (3,4)
UNION
SELECT * FROM ( VALUES (1,2) ) AS M4
-- (3,4) (1,2)
EXCEPT
SELECT * FROM ( VALUES (1,2) ) AS M3;

 column1 | column2
---------+---------
       3 |       4
(1 row)

Die Operation UNION ALL und INTERSECT ALL schließen Dubletten mit ein.

SELECT * FROM top100_airports;

 airport_id |           name            | total
------------+---------------------------+-------
 TXL        | Berlin - Tegel Airport    |     9
 LAX        | Los Angeles Airport       |     7
 FRA        | Frankfurt Airport         |     7
 PMI        | Palma De Mallorca Airport |     6
 GRZ        | Graz Airport              |     3
(5 rows)

Reisebüro

Das Reisebüro ermittelt für einen Fluggast die möglichen Flüge und fügt bei einer Buchung Einträge in die Tabellen persons und passangers hinzu. Damit ein Flug nicht überbucht wird, darf die Anzahl der Sitzplätze in einer Maschine nicht überschritten werden. Um dies zu gewährleisten wird ein Trigger eingesetzt, der Überbuchungen als Fehler ausweist. Ein Trigger ruft vor oder nach einer Datenbankoperation, z.B. INSERT, UPDATE, DELETE oder sogar TRUNCATE, eine sogenannte Triggerfunktion auf. Die Triggerfunktion check_booking soll im Folgenden die Überbuchung eines Fluges verhindern. Dazu wird die Anzahl der gebuchten Plätze vor dem Einfügen eines neuen Datensatzes in die Tabelle passangers überprüft.

CREATE FUNCTION check_booking()
RETURNS trigger
AS
$$
DECLARE
  v_seats integer;
  v_booked integer;
BEGIN
  -- places available read into vseats
  SELECT INTO v_seats seats FROM flights WHERE flight_number=NEW.flight_number;
  IF NOT FOUND
  THEN
    RAISE EXCEPTION 'flight_number "%" NOT existing', NEW.flight_number;
  END IF;
  -- places booked read into v_booked
  SELECT INTO v_booked count(*) FROM passangers WHERE flight_number=NEW.flight_number;
  IF v_seats <= v_booked
  THEN
    RAISE EXCEPTION 'Flight "%" just booked out', NEW.flight_number;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Die Triggerfunktion wird ebenfalls über den DDL-Befehl CREATE FUNCTION angelegt. Der Rückgabewert ist vom Datentyp trigger. Die Parameterliste der Funktion ist leer. Der Funktionsrumpf ist in PL/pgSQL implementiert. Nach DECLARE werden die lokalen Variablen v_seats und v_booked vom Typ integerdefiniert. Diese Trigger Funktion soll für jeden neu einzufügenden Datensatz in die Tabelle passangers aufgerufen werden. Die neu einzufügenden Werte sind in diesem Fall in der Variable NEW abgelegt. Die einzelnen Felder werden mittels der "."-Notation adressiert. Die Werte aus der zurückgegebenen Variablen NEW sollen in die Tabelle passanger eingefügt werden. Falls die Flugnummer nicht existiert oder die Platznummer die Anzahl der Sitze übersteigt, wird mittels der RAISE-Anweisungen ein Fehler ausgelöst. Die Variablen v_seats und v_booked werden durch Verwendung der INTO-Bedingung innerhalb einer SELECT-Anweisung mit Werten belegt. Dazu wird der folgende Ausdruck aus der Auswahlliste der SELECT-Anweisung genutzt.

Nun definieren wir den Trigger mittels des DDL-Befehl CREATE TRIGGER.

CREATE TRIGGER check_place_number
  BEFORE INSERT ON passangers FOR EACH ROW EXECUTE PROCEDURE check_booking();

Bevor ein Datensatz in die Tabelle passangers eingefügt wird, wird nun für diesen Datensatz die Funktion check_place_number ausgeführt. Die Werte des Datensatzes finden sich entsprechend in der Variable NEW. Trigger sind wichtige Ergänzungen der Integritätsbedingungen und können im praktischen Einsatz viel Arbeit ersparen. Um das Kundenverhalten für Umbuchungen oder Stornierungen von Flügen aufzuzeichnen verwendet das Reisebüro eine Historie. Diese wird in der Tabelle booking_history geführt.

CREATE TABLE booking_history (
 booking_id serial,
 booking_parent integer,
 flight_number varchar NOT NULL,
 person_id integer NOT NULL,
 createdate timestamp(0) without time zone DEFAULT now(),
 action varchar CHECK ( action IN ('booked', 'rebooked', 'canceld') ),
 CONSTRAINT booking_history_pk PRIMARY KEY (booking_id),
 CONSTRAINT booking_parent_fk FOREIGN KEY (booking_parent) REFERENCES booking_history (booking_id)
);

Die Historie wird über einen Trigger belegt. Zunächst definieren wir die dazugehörige Trigger Funktion booking_history.

CREATE FUNCTION booking_history()
RETURNS TRIGGER
AS
$$
DECLARE
  v_booking_parent integer;
BEGIN
  CASE TG_OP 
    WHEN 'INSERT' THEN
      INSERT INTO booking_history (flight_number, person_id, action) VALUES (NEW.flight_number, NEW.person_id, 'booked'); 
    WHEN 'UPDATE' THEN
      SELECT max(booking_id) INTO v_booking_parent FROM booking_history WHERE flight_number = OLD.flight_number AND person_id = OLD.person_id;
      INSERT INTO booking_history (booking_parent, flight_number, person_id, action) VALUES (v_booking_parent, NEW.flight_number, NEW.person_id, 'rebooked'); 
    WHEN 'DELETE' THEN
      SELECT max(booking_id) INTO v_booking_parent FROM booking_history WHERE flight_number = OLD.flight_number AND person_id = OLD.person_id;
      INSERT INTO booking_history (booking_parent, flight_number, person_id, action) VALUES (v_booking_parent, OLD.flight_number, OLD.person_id, 'canceld'); 
    ELSE
      RAISE EXCEPTION 'operation not defined: %', TG_OP;
  END CASE;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Diese Funktion soll für die Operationen INSERT, UPDATE und DELETE auf der Tabelle flights einen entsprechenden Eintrag in die Historie einfügen. Die drei Operationen werden durch die CASE-Anweisung unterschieden. Der Wert der Variable TG_OP enthält die die Trigger Funktion auslösende Datenbankoperation. Vor dem Hinzufügen eines Datensatzes in die Historie wird für UPDATE und DELETE der letzte Schritt in der Historie ermittelt. Dieser wird aus den aktuellen Werten des schon existierenden Datensatzes in der Variable OLD rekonstruiert. Dazu wird der letzte Eintrag zur aktuellen Flugnummer und Personenkennung in der Tabelle booking_history gesucht. Die Triggerfunktion soll nach der Operation auf der Tabelle passangers aufgerufen werden.

CREATE TRIGGER booking_history
   AFTER INSERT OR UPDATE OR DELETE ON passangers FOR EACH ROW EXECUTE PROCEDURE booking_history();

Wir untersuchen die Historie durch folgende Zusatzbuchung, die einmal umgebucht und storniert wird.

INSERT INTO passangers (flight_number, person_id) VALUES ('LH 457', 2);
UPDATE passangers SET flight_number='AB 8510' WHERE flight_number='LH 457' AND person_id = 2;
DELETE FROM passangers WHERE flight_number='AB 8510' AND person_id=2;

SELECT * FROM booking_history;

 booking_id | booking | flight_number | person_id |     createdate      |  action
------------+---------+---------------+-----------+---------------------+----------
          1 |         | LH 457        |         2 | 2011-04-25 16:31:37 | booked
          2 |       1 | AB 8510       |         2 | 2011-04-25 16:32:08 | rebooked
          3 |       2 | AB 8510       |         2 | 2011-04-25 16:32:35 | canceld
(3 rows)

In diesem Workshop haben wir uns Grundlagen in der Datenbanksprache SQL erworben. Wir haben Tabellen mit Datentypen erstellt, die wir für die entsprechende Aufgabenstellung zuvor definiert haben. Wir haben implizite und explizite Konvertierungsmechanismen kennengelernt, genau wie wir die Bedeutung der wichtigsten Integritätsbedinungen studiert haben. Zudem haben wir uns mit der Speicherung von Daten innerhalb von PostgreSQL beschäftigt. Das Einfügen, INSERT, Verändern, UPDATE, sowie das Löschen, DELETE, von Daten wurde von uns behandelt, genau so wie wir die Bedeutung von Schemen kennen gelernt haben.

Wir haben immer wieder den DML-Befehl SELECT genutzt und eine Vielzahl von Operatoren, Aggregatfunktionen und Bedinungen angewendet. Dieses Wissen haben wir für die Erstellung von Sichten und Funktionen genutzt. Mit dem Wissen über Funktionen konnten wir mittels Triggerfunktionen Trigger definieren. Weitere Themen wie Indexe, Transaktionen, die Verwendung von Cursorn, die Rechteverwaltung und das Zugriffsmangement waren jedoch nicht Gegenstand dieses Workshops. Diese Themen, genau wie die Optimierung von Datenbankabfragen und des Datenbanksystems sind zu komplex, um sie hier zu behandeln. In diesen Zusammenhängen wird dann auch über die Auflösung der Prinzipien gesprochen werden, die wir hier kennen gelernt haben.