Aufgabe 15 - Datenbankseitige Programmierung

Exercise 15 - Datenbankseitige Programmierung

Die Northwind GmbH betreibt ihre Warenwirtschaft bisher vollständig in einer JavaScript-Applikation. Nun soll das System um einen Webshop und eine mobile App erweitert werden. Die Geschäftsleitung stellt fest:

“Wenn jede Applikation eigene Berechnungen und Validierungen implementiert, werden die Daten früher oder später inkonsistent. Kritische Regeln gehören in die Datenbank – nicht in den Applikationscode.”

Ihre Aufgabe ist es, drei zentrale Bereiche der Geschäftslogik direkt in PostgreSQL zu implementieren:

1. Funktionen für wiederkehrende Berechnungen
2. Stored Procedures für mehrstufige Operationen mit Validierung
3. Trigger zur automatischen Durchsetzung von Geschäftsregeln

Alle Aufgaben bauen auf der Northwind-Datenbank auf, die bereits in Ihrem Docker-Setup läuft.

---

Vorbereitung

Verschaffen Sie sich zunächst einen Überblick über die relevanten Northwind-Tabellen:

-- Welche Tabellen existieren?
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

-- Struktur der für diese Übung wichtigen Tabellen prüfen
\d orders
\d order_details
\d products
\d customers

Halten Sie fest:

• Welche Spalten hat order_details? Welche Datentypen?
• Wie sind orders und order_details verknüpft?
• Welche Spalten beschreiben den Preis und die Menge in order_details?

---

Auftrag 1 - Funktionen: Berechnungslogik zentralisieren

1.1 Theorie – Warum Funktionen?

Beantworten Sie schriftlich, bevor Sie mit den Aufgaben beginnen:

1. Was ist der Unterschied zwischen einer Funktion (FUNCTION) und einer Prozedur (PROCEDURE) in PostgreSQL?
2. Wann verwendet man LANGUAGE sql statt LANGUAGE plpgsql?
3. Was bedeutet CREATE OR REPLACE FUNCTION? Warum ist das in der Entwicklung praktisch?

1.2 Aufgaben

Aufgabe A – Rabattberechnung

In order_details gibt es eine Spalte discount (ein Wert zwischen 0 und 1, z. B. 0.10 für 10 % Rabatt).

Erstellen Sie eine Funktion calculate_line_total, die für eine Bestellzeile den Gesamtbetrag nach Abzug des Rabatts berechnet:

line_total = unit_price * quantity * (1 - discount)

• Eingabeparameter: unit_price NUMERIC, quantity INT, discount NUMERIC
• Rückgabetyp: NUMERIC(12,2)
• Runden Sie auf 2 Nachkommastellen.
• Verwenden Sie LANGUAGE sql (keine PL/pgSQL-Blöcke nötig).

Testen Sie Ihre Funktion:

SELECT calculate_line_total(14.40, 12, 0.10);  -- Erwartetes Ergebnis: 155.52
SELECT calculate_line_total(9.80, 5, 0.00);    -- Erwartetes Ergebnis: 49.00
SELECT calculate_line_total(34.80, 1, 0.25);   -- Erwartetes Ergebnis: 26.10

Aufgabe B – Gesamtumsatz einer Bestellung

Erstellen Sie eine Funktion get_order_total, die für eine gegebene order_id den Gesamtumsatz der Bestellung berechnet (Summe aller Zeilentotals, gerundet auf 2 Stellen).

• Eingabeparameter: p_order_id INT
• Rückgabetyp: NUMERIC(12,2)
• Verwenden Sie Ihre Funktion calculate_line_total aus Aufgabe A.
• Wenn die order_id nicht existiert, soll 0.00 zurückgegeben werden.

Testen Sie:

SELECT get_order_total(10248);
SELECT get_order_total(10249);
SELECT get_order_total(99999);  -- Nicht vorhandene Order → 0.00

Vergleichen Sie Ihre Ergebnisse mit einer manuellen Berechnung:

SELECT
    order_id,
    SUM(ROUND(unit_price * quantity * (1 - discount), 2)) AS manual_total
FROM order_details
WHERE order_id = 10248
GROUP BY order_id;

Aufgabe C – Top-Produkte eines Lieferanten

Erstellen Sie eine Funktion get_top_products_by_supplier, die für einen gegebenen Lieferanten (supplier_id) die am häufigsten bestellten Produkte zurückgibt.

• Eingabeparameter: p_supplier_id INT
• Rückgabetyp: TABLE(product_id INT, product_name TEXT, total_quantity BIGINT)
• Die Ergebnisse sollen absteigend nach total_quantity sortiert sein.
• Verwenden Sie LANGUAGE sql.

Testen Sie:

SELECT * FROM get_top_products_by_supplier(1);
SELECT * FROM get_top_products_by_supplier(2);

1.3 Reflexion

• Warum ist calculate_line_total sinnvoller als die Formel direkt in jede Query zu schreiben? Nennen Sie ein konkretes Szenario, in dem eine zentrale Funktion einen Fehler verhindert hätte.
• Was passiert, wenn Sie get_order_total mit einer ungültigen order_id aufrufen und kein Standardwert definiert wäre? Warum ist defensives Programmieren hier wichtig?

---

Auftrag 2 - Stored Procedures: Lagerbestand verwalten

2.1 Theorie

Beantworten Sie schriftlich:

1. Was ist der Unterschied zwischen CALL my_procedure() und SELECT my_function()? Wann verwendet man welche Variante?
2. Was passiert, wenn innerhalb einer Procedure eine RAISE EXCEPTION ausgelöst wird? Wird die Änderung trotzdem gespeichert?
3. Warum ist es sinnvoll, Validierungslogik (z. B. „Bestand darf nicht negativ werden”) in der Procedure statt im Applikationscode zu implementieren?

2.2 Aufgaben

Aufgabe A – Lagerbestand anpassen

In der Northwind-Datenbank hat die Tabelle products eine Spalte units_in_stock.

Erstellen Sie eine Procedure adjust_stock, die den Lagerbestand eines Produkts um einen gegebenen Wert verändert:

• Parameter: p_product_id INT, p_quantity_change INT (positiv = Zugang, negativ = Abgang)
• Der Bestand darf nie unter 0 fallen → RAISE EXCEPTION mit aussagekräftiger Fehlermeldung
• Das Produkt muss existieren → RAISE EXCEPTION wenn nicht gefunden
• Protokollieren Sie jede Änderung in einer neuen Tabelle stock_log:

CREATE TABLE IF NOT EXISTS stock_log (
    log_id      BIGSERIAL PRIMARY KEY,
    product_id  INT NOT NULL,
    change_qty  INT NOT NULL,
    new_stock   INT NOT NULL,
    changed_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    changed_by  TEXT NOT NULL DEFAULT CURRENT_USER
);

Testen Sie:

-- Gültiger Abgang
CALL adjust_stock(1, -5);

-- Gültiger Zugang
CALL adjust_stock(1, 100);

-- Fehlerfall: Produkt nicht vorhanden
CALL adjust_stock(99999, 10);

-- Fehlerfall: Bestand würde negativ werden (hängt vom aktuellen Bestand ab)
CALL adjust_stock(1, -999999);

Überprüfen Sie nach den Aufrufen:

SELECT * FROM stock_log ORDER BY changed_at DESC LIMIT 10;

Aufgabe B – Nachvollziehbarkeit testen

Rufen Sie adjust_stock fünfmal mit verschiedenen Produkten und Mengen auf. Stellen Sie anschließend sicher, dass stock_log alle Änderungen korrekt aufzeichnet:

SELECT
    sl.log_id,
    p.product_name,
    sl.change_qty,
    sl.new_stock,
    sl.changed_at,
    sl.changed_by
FROM stock_log sl
JOIN products p ON sl.product_id = p.product_id
ORDER BY sl.changed_at DESC;

2.3 Reflexion

• Welchen Vorteil hat die Procedure gegenüber einem direkten UPDATE products SET units_in_stock = ...? Nennen Sie mindestens 2 Vorteile.
• Warum ist es wichtig, dass die Procedure das Produkt mit NOT FOUND prüft, bevor sie den Bestand ändert? Was wäre die Konsequenz ohne diese Prüfung?

---

Auftrag 3 - Trigger: Automatische Geschäftsregeln

3.1 Theorie

Beantworten Sie schriftlich:

1. Was ist der Unterschied zwischen einem BEFORE-Trigger und einem AFTER-Trigger? Wann verwendet man welchen?
2. Was enthalten die Variablen NEW und OLD bei einem UPDATE-Trigger?
3. Warum gibt eine Trigger-Funktion TRIGGER zurück und nicht den eigentlichen Datentyp der Zeile?

3.2 Aufgaben

Aufgabe A – BEFORE UPDATE: Automatischer Zeitstempel

Die Tabelle products soll einen automatischen updated_at-Zeitstempel erhalten.

1. Fügen Sie der Tabelle eine neue Spalte hinzu:

ALTER TABLE products
ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ DEFAULT NOW();

2. Erstellen Sie eine Trigger-Funktion set_products_updated_at() und einen zugehörigen Trigger trg_products_updated_at, der bei jedem UPDATE auf products die Spalte updated_at auf NOW() setzt.

3. Testen Sie:

-- Vor dem Update
SELECT product_id, product_name, updated_at FROM products WHERE product_id = 1;

-- Update durchführen
UPDATE products SET unit_price = unit_price + 0.01 WHERE product_id = 1;

-- Nach dem Update
SELECT product_id, product_name, updated_at FROM products WHERE product_id = 1;

Was erwarten Sie: updated_at soll sich geändert haben, obwohl Sie es im UPDATE-Statement nicht explizit gesetzt haben.

Aufgabe B – BEFORE INSERT: Geschäftsregel durchsetzen

Northwind hat folgende Geschäftsregel:

“Ein Produkt darf nicht zu einem Preis unter 0.01 EUR verkauft werden. Außerdem darf ein einzelner Rabatt in order_details niemals 50 % übersteigen.”

Erstellen Sie eine Trigger-Funktion validate_order_detail() und einen BEFORE INSERT OR UPDATE-Trigger trg_validate_order_detail auf der Tabelle order_details, der:

• Eine Exception auslöst, wenn unit_price < 0.01
• Eine Exception auslöst, wenn discount > 0.50
• Eine Exception auslöst, wenn quantity <= 0

Testen Sie mit:

-- Sollte klappen
INSERT INTO order_details (order_id, product_id, unit_price, quantity, discount)
VALUES (10248, 72, 34.80, 2, 0.10);

-- Sollte fehlschlagen: Preis zu niedrig
INSERT INTO order_details (order_id, product_id, unit_price, quantity, discount)
VALUES (10248, 73, 0.00, 1, 0.00);

-- Sollte fehlschlagen: Rabatt zu hoch
INSERT INTO order_details (order_id, product_id, unit_price, quantity, discount)
VALUES (10248, 74, 10.00, 1, 0.75);

-- Sollte fehlschlagen: Menge ungültig
INSERT INTO order_details (order_id, product_id, unit_price, quantity, discount)
VALUES (10248, 75, 5.00, 0, 0.00);

Aufgabe C – AFTER Trigger: Audit-Log für Preisänderungen

Produktpreise sind geschäftskritisch. Jede Änderung am unit_price eines Produkts soll automatisch protokolliert werden.

1. Erstellen Sie eine Audit-Tabelle:

CREATE TABLE IF NOT EXISTS product_price_audit (
    audit_id     BIGSERIAL PRIMARY KEY,
    product_id   INT NOT NULL,
    old_price    NUMERIC(10,2),
    new_price    NUMERIC(10,2),
    changed_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    changed_by   TEXT NOT NULL DEFAULT CURRENT_USER
);

2. Erstellen Sie eine Trigger-Funktion audit_product_price_change() und einen AFTER UPDATE-Trigger trg_product_price_audit auf products.

   • Der Trigger soll nur dann feuern, wenn sich unit_price tatsächlich geändert hat (nutzen Sie OLD.unit_price <> NEW.unit_price).
   • Protokollieren Sie old_price, new_price, changed_at, und changed_by.

3. Testen Sie:

-- Zwei Preisänderungen
UPDATE products SET unit_price = 20.00 WHERE product_id = 1;
UPDATE products SET unit_price = 21.50 WHERE product_id = 1;

-- Update ohne Preisänderung (soll NICHT protokolliert werden)
UPDATE products SET units_in_stock = units_in_stock WHERE product_id = 1;

-- Audit-Log prüfen
SELECT
    ppa.audit_id,
    p.product_name,
    ppa.old_price,
    ppa.new_price,
    ppa.changed_at,
    ppa.changed_by
FROM product_price_audit ppa
JOIN products p ON ppa.product_id = p.product_id
ORDER BY ppa.changed_at DESC;

Was erwarten Sie: Genau 2 Einträge für Produkt 1, kein Eintrag für das Update ohne Preisänderung.

3.3 Reflexion

• Warum verwendet man in Aufgabe C OLD.unit_price <> NEW.unit_price statt immer zu protokollieren? Was wäre der Nachteil der einfacheren Variante?
• Welche der drei Trigger-Varianten (BEFORE/AFTER, Validierung/Zeitstempel/Audit) würden Sie in einem echten Produktionssystem als unverzichtbar einstufen? Begründen Sie Ihre Wahl.
• Warum eignet sich ein BEFORE-Trigger besser zur Validierung als ein AFTER-Trigger? Was ist der entscheidende Unterschied im Zeitpunkt der Ausführung?

---

Auftrag 4 - Zusammenführung und Analyse

4.1 Überblick über erstellte Objekte

Listen Sie alle von Ihnen erstellten Funktionen, Prozeduren und Trigger auf:

-- Alle eigenen Funktionen und Procedures
SELECT routine_name, routine_type, data_type AS return_type
FROM information_schema.routines
WHERE routine_schema = 'public'
ORDER BY routine_type, routine_name;

-- Alle Trigger
SELECT trigger_name, event_manipulation, event_object_table, action_timing
FROM information_schema.triggers
WHERE trigger_schema = 'public'
ORDER BY event_object_table, trigger_name;

Dokumentieren Sie:

• Welche Objekte haben Sie erstellt?
• Auf welchen Tabellen liegen welche Trigger?

4.2 Abschlussfrage: Wann gehört Logik in die Datenbank?

Beantworten Sie schriftlich (mindestens 5 Sätze):

Das Northwind-Entwicklungsteam diskutiert: Der neue Junior-Entwickler besteht darauf, alle Validierungen und Berechnungen im JavaScript-Backend zu implementieren, weil “SQL-Code schwerer zu testen ist”. Der Senior-DBA widerspricht: “Kritische Datenintegrität gehört in die Datenbank.”

Nehmen Sie Stellung:

• In welchen Fällen hat der Junior-Entwickler recht?
• In welchen Fällen hat der DBA recht?
• Wie würden Sie die Logik in diesem konkreten Übungsbeispiel aufteilen?

---

Abgabe

Erstellen Sie ein Word- oder PDF-Dokument mit:

• Screenshots der SQL-Definitionen (Functions, Procedures, Trigger-Funktionen)
• Screenshots der Testaufrufe mit sichtbaren Ergebnissen bzw. Fehlermeldungen
• Den schriftlichen Antworten auf alle Reflexions- und Theoriefragen
• Dem abschließenden Überblick über alle erstellten Datenbankobjekte (Auftrag 4.1)