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.