4. 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;