Aufgabe 04 - Views (detailliert)

Aufgabe 04 - Views

Rahmen & Hinweise

Datenbasis: Northwind (PostgreSQL) aus northwind.sql.
Thema: Views (normale Views, materialisierte Views), updatable Views, WITH CHECK OPTION, Sicherheit via GRANT über Views, Performance-Aspekte, UNION ALL-Sichten, typische Fallstricke.
Werkzeuge: CREATE VIEW, CREATE MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW [CONCURRENTLY], GRANT/REVOKE, Indizes.
Reihenfolge: Aufgaben sind von “Ha, easy!” → “Puhh…” sortiert.

Hinweis (Vorgriff): In Aufgabe 4 kommt GRANT/REVOKE vor. Das ist noch nicht Teil der aktuellen Lektion und dient als Ausblick. Die Aufgabe kann optional bzw. angeleitet durchgeführt werden. Auch ist INSTEAD OF-Trigger ein fortgeschrittenes Thema, das ggf. übersprungen werden kann.

1) Einstieg: Einfache Berichtssicht auf Bestellungen

Operator: Verstehen Kompetenzen: SQL-Select wiederverwenden; Spalten sinnvoll benennen; stabile Schnittstelle schaffen. Themen: Einfache Views, Namensgebung, SELECT-Syntax.

Erstellen Sie eine einfache View exercise04.order_basic mit den Spalten:

order_id, order_date, customer_id, company_name, employee_id

Hinweise:

Nutzen Sie sinnvolle Aliases (bsp. o.id AS order_id).
Testen Sie die View mit SELECT * FROM exercise04.order_basic LIMIT 10;.
Warum ist eine View eine gute “vertragliche Schnittstelle”? Erläutern Sie kurz.

2) Aggregierte Sicht: Umsatz pro Kunde

Operator: Anwenden Kompetenzen: Aggregationen sauber kapseln; Business-Logik in Views auslagern. Themen: Join + Aggregation; stabile Spaltenliste.

Erstellen Sie exercise04.customer_revenue mit:

customer_id, company_name, order_count, revenue_total

Hinweise:

Summieren Sie Positionen (UnitPrice * Quantity * (1 - Discount)).
Validieren Sie per SELECT die Top-5 Kunden nach Umsatz.

3) Updatable View mit Filter + `WITH CHECK OPTION`

Operator: Anwenden Kompetenzen: Updatable-View-Regeln verstehen; Datenqualität sichern. Themen: Updatable Views, WITH CHECK OPTION.

Ziel: Eine View, die nur Produkte einer gewählten Kategorie (bsp. Beverages) zeigt und nur Einfügungen/Änderungen erlaubt, die in diese Kategorie fallen.

Erstellen Sie exercise04.beverage_products auf products, gefiltert auf die Kategorie “Beverages”.
Fügen Sie WITH CHECK OPTION hinzu.
Testen Sie:
- INSERT eines Produktes mit Kategorie “Beverages” (erwartet: ok).
- INSERT eines Produktes mit anderer Kategorie (erwartet: Fehler/Verhinderung).

Erläutern Sie kurz, warum die View updatable ist und in welchen Fällen es scheitern würde.

4) Sicherheitskonzept: Zugriff nur über eine View (Vorgriff: `GRANT/REVOKE`)

Vorgriff auf spätere Lektionen: Diese Aufgabe nutzt GRANT/REVOKE, was im Kurs noch nicht behandelt wurde. Sie dient als Ausblick und kann optional bzw. angeleitet bearbeitet werden.

Operator: Analysieren Kompetenzen: Prinzip der geringsten Rechte; Rechte über Views delegieren. Themen: GRANT auf Views, Entkoppelung von Basistabellen.

Erstellen Sie eine View exercise04.customer_public mit nicht sensiblen Spalten (bsp. customer_id, company_name, country).
Entziehen Sie einem Demo-User demo_read alle SELECT-Rechte auf den Basistabellen und geben Sie stattdessen SELECT nur auf der View.
Verifizieren Sie das Verhalten für demo_read.

Begründen Sie, warum Views als Sicherheitsbarriere dienen können, und nennen Sie Grenzen.

5) Performance: Materialisierte View für teure Reports

Operator: Analysieren Kompetenzen: Arbeitslast erkennen; geeignete Speicherform wählen; Indizes setzen. Themen: CREATE MATERIALIZED VIEW, REFRESH, Indizes, Lese-Performance.

Erstellen Sie exercise04.mv_customer_revenue_monthly mit Spalten: customer_id, month (bsp. date_trunc('month', order_date)), revenue_total.
Füllen Sie die MV (falls nötig WITH NO DATA → anschließend REFRESH).
Legen Sie sinnvolle Indizes auf der MV an.
Vergleichen Sie Abfragezeiten zwischen der MV und einer äquivalenten On-the-fly-Aggregation.

Diskussion: Wann lohnt sich eine MV in Northwind? Nennen Sie zwei Kriterien.

6) `REFRESH MATERIALIZED VIEW CONCURRENTLY` und Unique Index

Operator: Bewerten Kompetenzen: Fehlermeldungen deuten; Korrekturmaßnahmen anwenden. Themen: REFRESH ... CONCURRENTLY, eindeutiger Index.

Versuchen Sie, REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.mv_customer_revenue_monthly; auszuführen.
Beheben Sie die Fehlermeldung durch Anlegen eines eindeutigen Indexes, der jede Zeile eindeutig identifiziert (bsp. (customer_id, month)).
Erneut REFRESH ... CONCURRENTLY ausführen und Ergebnis prüfen.

Erläutern Sie kurz, warum der eindeutige Index nötig ist.

7) `UNION ALL`-Sicht über Jahres-Partitionen

Operator: Erstellen Kompetenzen: Historische Daten logisch zusammenführen; konsistente Spalten liefern. Themen: Jahres-Tabellen, UNION ALL, stabile Schnittstellen.

Erstellen Views mit den Bestelldaten 1996, 1997 und 1998. Diese sollen als orders_1996, orders_1997, orders_1998 vorliegen.

Erstellen Sie dann die View exercise04.orders_all, die alle drei Jahres-Tabellen per UNION ALL zusammenführt.

Prüfen Sie, dass die Spaltenlisten kompatibel sind.
Begründen Sie, warum UNION ALL hier meist vorzuziehen ist (vs. UNION).

8) Fallstricke: `ORDER BY` in Views & verschachtelte Views

Operator: Analysieren Kompetenzen: SQL-Standard verstehen; Abfrageplanung antizipieren; technische Schulden vermeiden. Themen: Sortierung nur in äußerer Abfrage, View-Nesting, Wartbarkeit.

Legen Sie eine View mit ORDER BY an und zeigen Sie, dass SELECT * FROM view; keine garantierte Sortierung liefert.
Demonstrieren Sie, dass viele verschachtelte Views Performance und Verständlichkeit beeinträchtigen können.
Formulieren Sie eine bessere Alternative (CTEs oder konsolidierte View).

Kurzes Fazit: Wo gehört ORDER BY hin und warum? Wie halten Sie Views lesbar?

Abgabe

Dokumentation mit:
- SQL-Befehlen
- Begründungen (Sicherheit, Performance, Wartbarkeit)
- kurzen Messwerten (wo gefordert)

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