Aufgabe 1: Sichten in SQL (10P)

Abgabe-Deadline: 16.12.2020 11:00 im Moodle

Auf https://data.deutschebahn.com/dataset/data-stationsdaten finden Sie Datensätze mit deutschen Bahnhöfen.

1. Laden Sie sich den neusten Datensatz im CSV-Format herunter.
2. Betrachten Sie die Datei in einem Texteditor, welche Spalten gibt es?
3. Erstellen Sie in Ihrer PostgreSQL-Datenbank eine Tabelle "bahnhoefe" mit den passenden Spalten und wählen Sie sinnvolle Datentypen und Primärschlüssel.
4. Benutzen Sie die Import-Funktion von DBeaver, um die CSV-Datei in Ihre Tabelle zu importieren (Rechtsklick auf die Tabelle - Daten importieren).
5. Erstellen Sie eine View "hbf" mit dem Spalten "bhfnr", "bundesland", "station" und "kategorie", die alle Bahnhöfe enthält, die auf "Hbf" oder "Hauptbahnhof" enden. Die Kategorie ist lediglich eine Umbenennung der Spalte Kat. Vst.

Aufgabe 2: SQL-Anfragen, Materialisierte Sichten

1. Erstellen Sie eine weitere View: "grosser_hbf", die auf der Sicht "hbf" basiert, aber nur die Bahnhöfe der Kategorie 1 enthält. Schreiben Sie am Ende des CREATE VIEW-Kommandos: WITH LOCAL CHECK OPTION. Dadurch wird beim Einfügen in die View das WHERE-Prädikat überprüft.
2. Formulieren Sie nun folgende Anfragen auf den Views:
   1. Finden Sie den Regensburger Hauptbahnhof
   2. Fügen Sie in die hbf-Sicht einen neuen Bahnhof ein: "Teststadt Nord" in Hessen mit der ID 9999 und Kategorie 5.
   3. Finden Sie mit einer SELECT-Anfrage auf der hbf-Sicht den neu eingefügten Bahnhof?
   4. Löschen Sie den Bahnhof 9999 wieder aus der bahnhoefe-Tabelle
   5. Versuchen Sie das INSERT aus b) nun auf der grosser_hbf-Sicht
   6. Passen Sie in dem INSERT-Kommando die Bahnhofskategorie so an, dass es auf der grosser_hbf-Sicht funktioniert.
   7. Finden Sie mit einer SELECT-Abfrage auf der grosser_hbf-Sicht den neu eingefügten Bahnhof?
   8. Droppen Sie die grosser_hbf-Sicht und erstellen Sie sie erneut, jedoch so, dass wirklich nur Bahnhöfe in diese Sicht eingefügt werden können, die auch in der View sichtbar sind: WITH CASCADED CHECK OPTION
3. Erstellen Sie eine materialisierte Sicht "Bahnhof_Statistik", die zu jedem Bundesland die Anzahl der Bahnhöfe zeigt. Wie viele Bahnhöfe zeigt diese Sicht in Hessen an? Löschen Sie Ihren Bahnhof 9999 und schauen Sie wieder in die materialiserte Sicht. Führen Sie ein REFRESH aus.
4. Stellen Sie abschließend noch folgende Anfragen auf der Tabelle "bahnhoefe": (siehe nächste Seite)
1. Geben Sie alle Bundesländer aus (keine Duplikate).
2. In welchen Städten gibt es mehr als 10 Bahnhöfe?
3. Welche Bahnhöfe haben die gleiche Kategorie (Kat. Vst.) wie "Ulm Hbf"?
4. Geben Sie zu jedem Bundesland aus, wie viele Bahnhöfe die dort befindlichen Städte im Durchschnitt besitzen.