Aufgabe 04 - Views (detailliert)

Aufgabe 04 - Views

Worum geht es?

In dieser Übung werden Views an der Northwind-Datenbank vertieft: einfache und aggregierte Views, updatable Views mit WITH CHECK OPTION, Materialized Views und Views als Sicherheitsbarriere (siehe Kapitel 2 - Views im Detail). Im Expertenteil geht es um Rechtevergabe über Views und typische Fallstricke.

Was Sie dafür brauchen

• Northwind wie in Aufgabe 01 importiert.
• Kapitel 2 - Views im Detail.
• CREATE VIEW, CREATE MATERIALIZED VIEW, REFRESH ... [CONCURRENTLY], GRANT/REVOKE.

Welche Kompetenzen Sie erwerben und zeigen

• Sie kapseln Abfragen als stabile View-Schnittstellen.
• Sie nutzen WITH CHECK OPTION und verstehen updatable Views.
• Sie legen Materialized Views mit Indizes an und aktualisieren sie.
• Sie setzen Views als Sicherheitsbarriere ein und kennen ihre Grenzen.

Pädagogische Einordnung

• Reproduktion: einfache und aggregierte Views erstellen (Teil A).
• Reorganisation und Transfer: updatable Views und Partitions-Sichten bauen (Teil B).
• Reflexion, Problemlösung und Urteilsbildung: Materialized Views, Rechte und Fallstricke beurteilen (Teile C und D).

Arbeitsaufträge

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

Positionswert durchgehend: unit_price * quantity * (1 - discount).

Teil A - Einfache und aggregierte Views

1. Eine View order_basic mit order_id, order_date, customer_id, company_name, employee_id. Mit SELECT * ... LIMIT 10 testen. In einem Satz erklären, warum eine View eine gute „vertragliche Schnittstelle” ist.
2. Eine View customer_revenue mit customer_id, company_name, order_count, revenue_total. Die Top 5 Kunden per SELECT prüfen.

Teil B - Updatable View und Partitions-Sicht

1. Eine View beverage_products auf products, gefiltert auf die Kategorie „Beverages”, mit WITH CHECK OPTION. Testen: ein Insert mit Kategorie „Beverages” (ok) und eines mit anderer Kategorie (abgelehnt). Erklären, warum die View updatable ist und wann das scheitert.
2. Views orders_1996, orders_1997, orders_1998 für die jeweiligen Jahre und eine View orders_all, die sie per UNION ALL zusammenführt. Begründen, warum UNION ALL hier UNION vorzuziehen ist.

Teil C - Materialized Views

1. Eine Materialized View mv_customer_revenue_monthly mit customer_id, month (date_trunc('month', order_date)), revenue_total. Sinnvolle Indizes anlegen und die Abfragezeit gegen eine gleichwertige On-the-fly-Aggregation vergleichen. Zwei Kriterien nennen, wann sich eine Materialized View lohnt.
2. REFRESH MATERIALIZED VIEW CONCURRENTLY versuchen, die Fehlermeldung durch einen eindeutigen Index (z. B. auf (customer_id, month)) beheben und erneut ausführen. Erklären, warum der eindeutige Index nötig ist.

Teil D - Expertenteil: Sicherheit und Fallstricke

1. View als Sicherheitsbarriere (Vorgriff auf Benutzer und Rechte): Eine View customer_public nur mit nicht sensiblen Spalten (customer_id, company_name, country). Einem Demo-Benutzer demo_read alle SELECT-Rechte auf den Basistabellen entziehen und stattdessen nur SELECT auf der View geben. Das Verhalten für demo_read prüfen. Begründen, warum Views als Sicherheitsbarriere dienen, und eine Grenze nennen (etwa dass die zugrunde liegende Logik weiterhin alle Zeilen liest).
2. ORDER BY in Views: Eine View mit ORDER BY anlegen und zeigen, dass SELECT * FROM view keine garantierte Sortierung liefert. Erklären, wohin ORDER BY gehört.
3. Verschachtelte Views: Demonstrieren, dass viele ineinander verschachtelte Views Verständlichkeit und Performance beeinträchtigen, und eine bessere Alternative formulieren (konsolidierte View oder CTE).
4. Fazit: In vier bis fünf Sätzen zusammenfassen, wann eine normale View, wann eine Materialized View und wann eine View als Sicherheitsschicht die richtige Wahl ist.

Wissenscheck

1. Warum ist eine View eine stabile Schnittstelle für Anwendungen?
2. Was garantiert WITH CHECK OPTION?
3. Warum braucht REFRESH ... CONCURRENTLY einen eindeutigen Index?
4. Wie kann eine View als Sicherheitsbarriere wirken, und wo ist ihre Grenze?
5. Wohin gehört ORDER BY, und warum nicht dauerhaft in die View?

Abgabe

• Eine Dokumentation mit allen SQL-Befehlen, Begründungen (Sicherheit, Performance, Wartbarkeit) und den Messwerten aus Teil C.

---

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