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;