5. 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 Fließkommazahl 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.