Aufgabe 18 - SQL Views (Virtuelle Tabellen)

Worum geht es?

In dieser Übung erstellen Sie Views - gespeicherte Abfragen, die wie virtuelle Tabellen verwendet werden (siehe Kapitel 10 - SQL Views). Sie nutzen Views zur Vereinfachung von Joins, zur Datenkapselung und für mehr Sicherheit.

In dieser Übung üben Sie:

Verstehen: das Konzept der View als gespeicherte Abfrage erfassen und von physischen Tabellen abgrenzen.
Erstellen: einfache und komplexe Views (mit Joins und Aggregation) in PostgreSQL anlegen.
Anwenden: den Nutzen von Views für Datenkapselung und Sicherheit praktisch einsetzen.
Hinterfragen: die Einschränkungen von DML-Operationen auf Views (z. B. WITH CHECK OPTION) beurteilen.

Szenario: Erweiterte Schulverwaltung

Um die Daten der bestehenden Tabellen (students, teachers, classrooms) effizienter zu nutzen, möchte die Schulleitung verschiedene “Sichten” (Views) einrichten. Ziel ist es, dass das Sekretariat nicht jedes Mal komplizierte Joins schreiben muss.

CREATE TABLE students (
    student_id   INT PRIMARY KEY,
    first_name   VARCHAR(50),
    last_name    VARCHAR(50),
    birth_date   DATE,
    class_name   VARCHAR(10)
);

CREATE TABLE teachers (
    teacher_id   INT PRIMARY KEY,
    teacher_name VARCHAR(50),
    class_name   VARCHAR(10)
);

CREATE TABLE classrooms (
    room_id      VARCHAR(10) PRIMARY KEY,
    class_name   VARCHAR(10),
    max_seats    INT
);

INSERT INTO students VALUES
(1, 'Alice', 'Adelson', '2010-05-15', '4A'),
(2, 'Bob', 'Burger', '2011-02-20', '4B'),
(3, 'Charlie', 'Check', '2012-11-30', '4A'),
(4, 'Daisy', 'Duck', '2013-01-10', '3A'),
(10, 'Felix', 'Fix', '2010-12-17', '4A');

INSERT INTO teachers VALUES
(101, 'Maurhart', '4A'),
(102, 'Gabriel', '4B'),
(103, 'Steindl', '3A'),
(104, 'Müller', '2A');

INSERT INTO classrooms VALUES
('R101', '4A',  2),
('R102', '4B', 30),
('R201', '3A', 25);

Arbeitsaufträge (SQL-Abfragen)

Aufgabe 1: Die “Klassenliste” (Einfacher Join)

Erstellen Sie eine View namens view_klassenliste. Diese soll für jeden Schüler folgende Informationen übersichtlich zusammenfassen:

Vorname und Nachname des Schülers.
Den Namen der Klasse.
Den Namen des zuständigen Klassenlehrers.

Anwendung: Testen Sie die View anschließend mit einem einfachen SELECT * FROM view_klassenliste;.

Aufgabe 2: Datenschutz-Sicht (Security Aspect)

Die Schulbibliothek benötigt Zugriff auf Schülerdaten, darf aber aus Datenschutzgründen das Geburtsdatum nicht sehen.

Erstellen Sie eine View view_bibliothek_schueler.
Die View soll nur student_id, first_name, last_name und class_name enthalten.
Zusatz: Warum erhöht diese View die Sicherheit, selbst wenn die Benutzer Leserechte auf die View haben?

Aufgabe 3: Kapazitäts-Monitor (Aggregierte View)

Die Schulleitung benötigt eine Übersicht über die Auslastung der Räume.

Erstellen Sie eine View view_raumauslastung.
Die View soll den Klassennamen, die aktuelle Anzahl der Schüler in dieser Klasse und die max_seats des zugeordneten Raums (aus der Tabelle classrooms) anzeigen.

Aufgabe 4: Das “Updatable View” Paradoxon

In PostgreSQL können einfache Views oft automatisch aktualisiert werden.

Vorbereitung: Erstellen Sie zunächst eine View view_student_stats, die mittels GROUP BY die Anzahl der Schüler pro Klasse zählt.
Herausforderung: Versuchen Sie, über diese View den Namen einer Klasse zu ändern.
Problemstellung: Warum verweigert PostgreSQL hier den Dienst?

Aufgabe 5: “Nested Views” & Performance-Falle

Erstellen Sie eine View view_top_auslastung, die auf der in Aufgabe 3 erstellten View view_raumauslastung basiert. Diese neue Sicht soll nur die Räume anzeigen, deren Belegung über 90 % der max_seats liegt.

Herausforderung: Diskutieren Sie die potenziellen Gefahren von “Views auf Views” (verschachtelte Views).
Analyse: Was passiert technisch im Hintergrund bezüglich der Query Execution, wenn die Basistabellen sehr groß werden? Warum könnte ein direkter Join auf den Basistabellen hier performanter sein?

Aufgabe 6: Die “Invisible Row” Problematik (WITH CHECK OPTION)

Erstellen Sie eine View view_oberstufe, die nur Schüler der Klassen ‘4A’ und ‘4B’ anzeigt.

Szenario: Ein Benutzer führt einen UPDATE-Befehl über die View aus und ändert die Klasse eines Schülers von ‘4A’ auf ‘3A’.
Das Phänomen: Der Datensatz verschwindet plötzlich aus der Sicht des Benutzers, obwohl der Befehl erfolgreich war.
Lösung: Implementieren Sie die View erneut mit der WITH CHECK OPTION. Erklären Sie, wie diese Klausel verhindert, dass Daten so modifiziert werden, dass sie nicht mehr den Filterkriterien der View entsprechen.

Abgabe & Kriterien

Abgabe der Datei aufgabe18_views.sql mit allen CREATE VIEW- und Test-Anweisungen.
Alle Befehle müssen auf einer PostgreSQL-Datenbank fehlerfrei ausführbar sein.
Kurze schriftliche Antworten auf die Diskussionsfragen (Aufgaben 2, 4, 5, 6).

Wissens-Check

Frage 1: Belegt eine View physischen Speicherplatz auf der Festplatte für die darin enthaltenen Daten? Begründen Sie Ihre Antwort.
Frage 2: Sie haben eine View, die einen GROUP BY Befehl nutzt, um die Anzahl der Schüler pro Klasse zu zählen. Können Sie über diese View einen neuen Schüler mittels INSERT hinzufügen? Warum (nicht)?
Frage 3: Was ist der Vorteil der WITH CHECK OPTION beim Erstellen einer View, die Filter (z. B. WHERE active = TRUE) verwendet?

HTL Villach, Schuljahr 2025-2026,
https://www.htl-villach.at