Aufgabe 18 - SQL Views (Virtuelle Tabellen)

Aufgabe 18 - SQL Views (Virtuelle Tabellen)

Worum geht es?

In dieser Übung werden Views angelegt, also gespeicherte Abfragen, die wie virtuelle Tabellen verwendet werden (siehe Kapitel 10 - SQL Views). Sie vereinfachen Joins, kapseln Daten und erhöhen die Sicherheit. Im Expertenteil geht es um WITH CHECK OPTION und materialisierte Views.

Was Sie dafür brauchen

• Kapitel 10 - SQL Views.
• Ein PostgreSQL-Server und ein SQL-Editor.

Welche Kompetenzen Sie erwerben und zeigen

• Sie legen einfache und aggregierte Views an und grenzen sie von Tabellen ab.
• Sie nutzen Views zur Datenkapselung und für den Datenschutz.
• Sie verstehen die Grenzen von DML auf Views und WITH CHECK OPTION.
• Sie unterscheiden Views von Materialized Views und wägen ab.

Pädagogische Einordnung

• Reproduktion: einfache Views erstellen (Teil A).
• Reorganisation und Transfer: aggregierte und verschachtelte Views bauen (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: WITH CHECK OPTION und Materialized Views einsetzen und beurteilen (Teil D).

Arbeitsaufträge

Die Übung ist auf etwa zwei Stunden ausgelegt. Teil D ist der Expertenteil.

Schema und Testdaten

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,'Miller','2A');
INSERT INTO classrooms VALUES ('R101','4A',2),('R102','4B',30),('R201','3A',25);

Teil A - Einfache Views

1. Klassenliste: Eine View view_klassenliste mit Vor- und Nachname des Schülers, Klassenname und Name der zuständigen Lehrkraft (Join students–teachers). Mit SELECT * FROM view_klassenliste; testen.
2. Datenschutz-Sicht: Eine View view_bibliothek_schueler nur mit student_id, first_name, last_name, class_name (ohne Geburtsdatum). In einem Satz begründen, warum diese View die Sicherheit erhöht.

Teil B - Aggregierte View

1. Raumauslastung: Eine View view_raumauslastung mit Klassenname, aktueller Schüleranzahl der Klasse und max_seats des zugeordneten Raums.

Teil C - Grenzen und Verschachtelung

1. Nicht aktualisierbare View: Eine View view_student_stats, die per GROUP BY die Schüleranzahl je Klasse zählt. Versuchen, über diese View einen Klassennamen zu ändern, und erklären, warum PostgreSQL das ablehnt.
2. View auf View: Eine View view_top_auslastung, die auf view_raumauslastung aufbaut und nur Räume mit Belegung über 90 % der max_seats zeigt. In zwei bis drei Sätzen die Gefahren verschachtelter Views und die Auswirkung auf die Query-Ausführung diskutieren.

Teil D - Expertenteil: WITH CHECK OPTION und Materialized View

1. Verschwindende Zeile: Eine View view_oberstufe anlegen, die nur Schüler der Klassen '4A' und '4B' zeigt. Über die View die Klasse eines '4A'-Schülers auf '3A' ändern und beobachten, dass die Zeile aus der View verschwindet, obwohl das UPDATE erfolgreich war.

2. Absichern: Die View erneut mit WITH CHECK OPTION anlegen und zeigen, dass dasselbe UPDATE nun abgelehnt wird. In zwei Sätzen erklären, was diese Klausel garantiert.

   CREATE OR REPLACE VIEW view_oberstufe AS
     SELECT * FROM students WHERE class_name IN ('4A','4B')
     WITH CHECK OPTION;

3. Materialized View: view_raumauslastung zusätzlich als Materialized View mv_raumauslastung anlegen. Einen neuen Schüler in '4A' einfügen und zeigen, dass die normale View sofort den neuen Wert liefert, die Materialized View aber erst nach REFRESH MATERIALIZED VIEW mv_raumauslastung.

4. Beurteilen: In vier bis fünf Sätzen gegenüberstellen: Wann lohnt sich eine Materialized View (Vorteil: schnelle Abfragen auf teuren Aggregaten), und was ist der Preis (Nachteil: Daten sind bis zum Refresh veraltet, brauchen Speicher)? Nennen Sie ein Szenario aus dem Schulbetrieb für jede der beiden Varianten.

Wissenscheck

1. Belegt eine (normale) View physischen Speicher für ihre Daten? Begründen Sie.
2. Warum kann man über eine View mit GROUP BY keinen neuen Schüler einfügen?
3. Was garantiert WITH CHECK OPTION?
4. Worin unterscheiden sich eine View und eine Materialized View?
5. Nennen Sie je ein Szenario, in dem eine normale bzw. eine Materialized View die bessere Wahl ist.

Abgabe

• Die Datei aufgabe18_views.sql mit allen CREATE VIEW-, CREATE MATERIALIZED VIEW- und Testanweisungen, fehlerfrei ausführbar.
• Kurze schriftliche Antworten auf die Diskussionsfragen der Teile A, C und D.

---

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