Workshop PostgreSQL 9

6. 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 Surrogat-Schlüssel person_id vom Datentyp serial. Surrogat-Schlü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üssel, 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.