Übungsaufgaben zu Datenbankkonzepten: Transaktionen, Integrität, Trigger, Stored Procedures und Index

Je Aufgabe 1 Punkt, zum Beispiel:

• Integrität sichern: nur zulässige, widerspruchsfreie Daten speichern (Schlüssel, Wertebereiche, Fremdschlüssel prüfen)

• Transaktionen verwalten: zusammengehörige Änderungen ganz oder gar nicht ausführen (COMMIT/ROLLBACK)

• Mehrbenutzerbetrieb synchronisieren: gleichzeitige Zugriffe über Sperren steuern

• Zugriffskontrolle: Benutzer, Rollen und Rechte verwalten

• Datensicherung und Wiederherstellung nach Ausfällen (Transaktionsprotokoll, Backup)

• Datenkatalog (Data Dictionary) mit der Beschreibung aller Tabellen und Spalten führen

• Benutzersichten (Views) bereitstellen

• Abfragen optimieren (Indizes, Ausführungsplan)

• Trigger und Stored Procedures ausführen

Je Eigenschaft 2 Punkte (1 Punkt Beschreibung, 1 Punkt Bezug zur Bestellung):

START TRANSACTION;

INSERT INTO bestellung (bestellung_id, kunde_id, bestellt_am, status, zahlungsart,
                        rechnungsadresse_id, lieferadresse_id)
VALUES (9, 3, '2026-10-19 10:05:00', 'offen', 'PayPal', 3, 4);

INSERT INTO bestellposition (bestellung_id, pos_nr, artikel_id, menge, einzelpreis_netto)
VALUES (9, 1, 7, 2,  68.07),
       (9, 2, 8, 1, 133.61);

UPDATE artikel SET lagerbestand = lagerbestand - 2 WHERE artikel_id = 7;
UPDATE artikel SET lagerbestand = lagerbestand - 1 WHERE artikel_id = 8;

COMMIT;

Bei einem Fehler sendet die Shop-Software statt COMMIT die Anweisung ROLLBACK;. Alle Änderungen seit START TRANSACTION werden dann zurückgenommen.

Bewertung: START TRANSACTION (oder BEGIN) und COMMIT 1 Punkt, Bestellkopf 1 Punkt, zwei Positionen 1 Punkt, Lagerbestände mit lagerbestand = lagerbestand - menge 2 Punkte (1 Punkt, wenn feste Werte wie SET lagerbestand = 46 geschrieben werden), ROLLBACK 1 Punkt. Statt der festen Zeit ist NOW() richtig. Die bestellung_id darf weggelassen werden (AUTO_INCREMENT); dann braucht die Software für die Positionen LAST_INSERT_ID().

Nach dem COMMIT hat das Lichtset noch 46 Stück, die Bremse 8 Stück – sie liegt (wie schon vorher) unter dem Mindestbestand von 10.

Tabelle 2 Punkte (je zwei richtige Zeilen 1 Punkt).

Erläuterung (3 Punkte): Im Autocommit-Modus ist jede Anweisung eine eigene Transaktion und wird sofort festgeschrieben. Die Bestellung 10 steht deshalb unvollständig in der Datenbank: mit einer Position, ohne die zweite, und der Lagerbestand des verkauften Lichtsets ist nicht verringert. Lager und Bestellungen passen nicht mehr zusammen; die Bestellung würde so bearbeitet und berechnet. Genutzt werden müsste die Atomarität (Atomicity): Alle vier Anweisungen gehören in eine Transaktion, die bei einem Fehler mit ROLLBACK vollständig zurückgenommen wird. Dann wäre der Zustand wie vor dem Import, und die Bestellung könnte nach Korrektur der Artikelnummer neu übertragen werden. (Auch eine Antwort, die auf die Konsistenz abstellt, ist richtig, wenn sie begründet, dass der Zustand danach widersprüchlich ist.)

Je Zeile 1 Punkt:

Referentielle Integrität (2 Punkte): Zwischen artikel (1-Seite, Elterntabelle) und bestellposition (n-Seite, Kindtabelle mit dem Fremdschlüssel artikel_id) ist festgelegt, dass jeder Wert im Fremdschlüssel bestellposition.artikel_id als Primärschlüsselwert in artikel vorhanden sein muss. Das DBMS stellt so sicher, dass die Beziehungen zwischen den Datensätzen gültig bleiben: Eine Position kann nur für einen vorhandenen Artikel angelegt werden, und ein Artikel, auf den Positionen verweisen, kann nicht gelöscht (oder in seiner artikel_id geändert) werden.

Ursache der Fehlermeldung (2 Punkte): Anweisung (3) will eine Position mit artikel_id = 12 einfügen. In der Tabelle artikel gibt es keinen Datensatz mit dieser ID. Die neue Kindzeile hätte also keinen Elterndatensatz; MariaDB weist das INSERT deshalb mit Fehler 1452 („Cannot add or update a child row: a foreign key constraint fails“) ab. Die Anweisung ist syntaktisch richtig – der Fehler liegt in den Daten (falsche Artikelnummer am Telefon oder Artikel noch nicht angelegt).

Ursache (2 Punkte): Die Kundin 99 ist Elterndatensatz der Bestellung 99, denn bestellung.kunde_id ist ein Fremdschlüssel auf kunde (Constraint fk_best_kunde, ohne ON DELETE-Regel, also RESTRICT). Würde die Kundin gelöscht, verwiese die Bestellung auf eine nicht mehr vorhandene Kundin; die referentielle Integrität wäre verletzt. Deshalb weist MariaDB das Löschen ab. Die DELETE-Anweisung selbst ist korrekt.

Reihenfolge (2 Punkte): zuerst die abhängigen Datensätze (Kinder), dann die Elterndatensätze:

START TRANSACTION;
DELETE FROM bestellung WHERE bestellung_id = 99;  -- Positionen werden per ON DELETE CASCADE mitgelöscht
DELETE FROM kunde      WHERE kunde_id = 99;       -- Adresse 99 wird per ON DELETE CASCADE mitgelöscht
COMMIT;

Begründung: Die Bestellung verweist auf die Kundin und auf die Adresse 99 (rechnungsadresse_id, lieferadresse_id, beide RESTRICT). Sie muss deshalb vor Kundin und Adresse gelöscht werden. Ihre Positionen hängen über ON DELETE CASCADE an der Bestellung und verschwinden automatisch. Danach verweist nichts mehr auf die Kundin; beim Löschen der Kundin wird die Adresse per CASCADE mitgelöscht. Gleichwertig ist eine Lösung, die Positionen, Bestellung, Adresse und Kundin einzeln in dieser Reihenfolge löscht. Ein vorheriges DELETE FROM adresse WHERE kunde_id = 99; schlägt dagegen fehl, solange die Bestellung existiert.

Je Zeile 1 Punkt:

Weil die Anweisung bei einem Fehler vollständig zurückgenommen wird, bleibt bei Bestellung 6 auch die Position erhalten.

Nein (1 Punkt für die Entscheidung, 2 Punkte für die Begründung). Mit CASCADE würden beim Löschen des Kunden auch alle seine Bestellungen (und darüber ihre Positionen) gelöscht – sofern nicht Rechnungen und Versanddaten das verhindern; bei Herrn Kühn verhindert die Rechnung zu Bestellung 2 das Löschen ohnehin. Bestellungen und Rechnungen sind aber Geschäftsunterlagen, die nach Handels- und Steuerrecht aufbewahrt werden müssen und für Umsatzauswertungen gebraucht werden; zudem ist Rechnung RE-2026-00412 noch offen (Mahnstufe 1). Eine automatische Löschweitergabe könnte versehentlich große Datenmengen vernichten. RESTRICT ist hier richtig. Dem Löschwunsch wird stattdessen nachgekommen, indem nicht mehr benötigte Daten (Newsletter, Telefonnummer, nicht mehr gebrauchte Adressen) gelöscht und die übrigen bis zum Ende der Aufbewahrungsfrist gesperrt und danach anonymisiert werden (Art. 17 Abs. 3 lit. b DSGVO erlaubt die Aufbewahrung zur Erfüllung rechtlicher Pflichten).

Eine begründete Entscheidung für CASCADE ist nicht vertretbar; eine begründete Entscheidung für SET NULL scheitert daran, dass bestellung.kunde_id NOT NULL ist (Teilpunkte für die Begründung möglich).

Ein Trigger ist eine besondere Art einer gespeicherten Prozedur, die zu einer Tabelle gehört und automatisch vom DBMS ausgeführt wird, wenn auf dieser Tabelle ein bestimmtes Ereignis eintritt (INSERT, UPDATE oder DELETE), wahlweise vor (BEFORE) oder nach (AFTER) der Änderung (1 Punkt). Er wird nicht von einer Anwendung aufgerufen und eignet sich, um Regeln zu prüfen, abgeleitete Werte nachzuführen oder Änderungen zu protokollieren (1 Punkt).

DELIMITER //
CREATE TRIGGER trg_bestellposition_lager
AFTER INSERT ON bestellposition
FOR EACH ROW
BEGIN
  UPDATE artikel
  SET    lagerbestand = lagerbestand - NEW.menge
  WHERE  artikel_id = NEW.artikel_id
    AND  konfigurierbar = FALSE;
END //
DELIMITER ;

Bewertung: CREATE TRIGGER … AFTER INSERT ON bestellposition FOR EACH ROW 2 Punkte, UPDATE mit NEW.menge und NEW.artikel_id 1 Punkt, Bedingung für konfigurierbare Artikel 1 Punkt. Da der Rumpf nur aus einer Anweisung besteht, sind BEGIN … END und DELIMITER nicht zwingend. Auch ein BEFORE INSERT-Trigger ist richtig; AFTER hat den Vorteil, dass der Bestand nur geändert wird, wenn die Position tatsächlich gespeichert werden konnte.

Lagerbestände (1 Punkt): Der Trigger läuft für jede eingefügte Zeile einmal. Lichtset 48 → 46, Bremse 9 → 8, Rahmen nach Maß bleibt bei 0 (konfigurierbar). Da der Trigger in derselben Transaktion läuft wie das INSERT, werden die Bestandsänderungen bei einem ROLLBACK der Bestellung ebenfalls zurückgenommen.

DELIMITER //
CREATE TRIGGER trg_bestellung_storno
AFTER UPDATE ON bestellung
FOR EACH ROW
BEGIN
  IF NEW.status = 'storniert' AND OLD.status <> 'storniert' THEN
    UPDATE artikel a
    JOIN   bestellposition p ON p.artikel_id = a.artikel_id
    SET    a.lagerbestand = a.lagerbestand + p.menge
    WHERE  p.bestellung_id = NEW.bestellung_id
      AND  a.konfigurierbar = FALSE;
  END IF;
END //
DELIMITER ;

Bewertung: AFTER UPDATE ON bestellung FOR EACH ROW 1 Punkt, Bedingung mit NEW.status und OLD.status 2 Punkte, Rückbuchung über die Positionen der Bestellung 1 Punkt. Eine Lösung mit Unterabfrage ist ebenso richtig:

UPDATE artikel a
SET    a.lagerbestand = a.lagerbestand +
         (SELECT SUM(p.menge) FROM bestellposition p
          WHERE p.bestellung_id = NEW.bestellung_id AND p.artikel_id = a.artikel_id)
WHERE  a.konfigurierbar = FALSE
  AND  a.artikel_id IN (SELECT artikel_id FROM bestellposition
                        WHERE bestellung_id = NEW.bestellung_id);

Lagerbestand (1 Punkt): Beim ersten Speichern wechselt der Status von offen zu storniert; die 2 Bremsen werden zurückgebucht: 9 → 11. Beim zweiten Speichern ist OLD.status bereits storniert, die Bedingung ist falsch, der Bestand bleibt bei 11. Ohne die Prüfung von OLD.status würde doppelt zurückgebucht (13). Der Trigger ändert die Tabelle artikel, nicht die auslösende Tabelle bestellung – das ist in MariaDB erlaubt.

Ein Index ist eine zusätzliche, sortierte Zugriffsstruktur (in InnoDB ein B-Baum) über die Werte einer oder mehrerer Spalten – ähnlich dem Stichwortverzeichnis eines Buchs (1 Punkt). Er dient dazu, die Suche nach Datensätzen (WHERE), das Verknüpfen (JOIN) und das Sortieren (ORDER BY) zu beschleunigen, weil das DBMS nicht mehr jede Zeile der Tabelle lesen muss; das bringt vor allem bei großen Datenmengen deutliche Geschwindigkeitsvorteile (1 Punkt).

CREATE INDEX idx_bestellung_bestellt_am ON bestellung (bestellt_am);

Gleichwertig: ALTER TABLE bestellung ADD INDEX idx_bestellung_bestellt_am (bestellt_am);

Bewertung: CREATE INDEX mit Namen 1 Punkt, ON bestellung (bestellt_am) 1 Punkt.

Je Grund 1 Punkt:

• Jeder Index muss bei jedem INSERT, UPDATE und DELETE mitgepflegt werden. Schreibvorgänge – im Shop jede Bestellung – werden dadurch langsamer, und es werden mehr Sperren gehalten.

• Jeder Index belegt zusätzlichen Speicherplatz (und Platz im Arbeitsspeicher-Puffer, Zeit bei der Datensicherung).

• Viele Indizes nützen nichts, weil Abfragen sie nicht verwenden; für Primärschlüssel, UNIQUE-Spalten und Fremdschlüssel legt MariaDB ohnehin automatisch einen Index an.

Spalte ohne lohnenden eigenen Index (1 Punkt): zum Beispiel status oder zahlungsart (nur wenige verschiedene Werte, ein Index grenzt die Suche kaum ein), gutscheincode (wird kaum gesucht). Nicht richtig sind bestellung_id, kunde_id, rechnungsadresse_id, lieferadresse_id – sie haben bereits automatisch einen Index.

Bewertung: gelesene Werte 1 Punkt, Spalte freie_plaetze 1 Punkt, Spalte Buchungen 1 Punkt, Ergebnis (2 Buchungen für 1 Platz, Bestand 0 statt −1 bzw. statt Ablehnung) 1 Punkt.

Problem (2 Punkte): Beide Sitzungen lesen den Wert 1, bevor die andere ihn verändert hat, und entscheiden auf Grundlage eines veralteten Werts. Sitzung B überschreibt in t6 den Wert von A mit einem selbst berechneten Wert, statt die Änderung von A zu berücksichtigen (Lost Update, verlorenes Update). Ergebnis: Für einen Platz gibt es zwei bestätigte Buchungen – das Angebot ist überbucht, eine Person steht morgen früh vergeblich am Haltepunkt. Die Datenbank zeigt 0 freie Plätze und erkennt den Fehler nicht.

ACID-Eigenschaft (1 Punkt): Isolation – die beiden gleichzeitig laufenden Vorgänge beeinflussen sich gegenseitig; nacheinander ausgeführt hätte B den Wert 0 gelesen und nicht gebucht. (Zusätzlich fehlt die Atomarität, weil die drei Schritte nicht in einer Transaktion stehen; als einzige Nennung reicht sie nicht.)

Lösung mit bedingtem UPDATE (4 Punkte):

START TRANSACTION;

UPDATE angebot
SET    freie_plaetze = freie_plaetze - 1
WHERE  angebot_id = 4
  AND  freie_plaetze > 0
  AND  status = 'offen';

-- Backend prüft die Anzahl geänderter Zeilen (ROW_COUNT() bzw. Rückgabewert von executeUpdate):
--   1 -> weiter mit INSERT und COMMIT
--   0 -> ROLLBACK; Meldung „Angebot ausgebucht“

INSERT INTO buchung (angebot_id, mitfahrer_id, status, angefragt_am, beantwortet_am)
VALUES (4, 3, 'bestaetigt', NOW(), NOW());

COMMIT;

Gleichwertig ist eine Lösung mit sperrendem Lesen:

START TRANSACTION;
SELECT freie_plaetze FROM angebot WHERE angebot_id = 4 FOR UPDATE;
-- nur wenn der gelesene Wert > 0 ist:
INSERT INTO buchung (angebot_id, mitfahrer_id, status, angefragt_am, beantwortet_am)
VALUES (4, 3, 'bestaetigt', NOW(), NOW());
UPDATE angebot SET freie_plaetze = freie_plaetze - 1 WHERE angebot_id = 4;
COMMIT;      -- bzw. ROLLBACK, wenn kein Platz frei ist

Bewertung: Transaktion mit START TRANSACTION/COMMIT (und ROLLBACK im Fehlerfall) 1 Punkt, Sperre durch FOR UPDATE oder bedingtes UPDATE 2 Punkte, Rechnen in der Datenbank (freie_plaetze - 1) statt Zurückschreiben eines Werts 1 Punkt.

Erläuterung (2 Punkte): Das UPDATE (bzw. SELECT … FOR UPDATE) setzt eine Schreibsperre auf die Zeile von Angebot 4, die bis zum COMMIT gehalten wird. Lukas’ Sitzung muss an derselben Stelle warten. Nach Oksanas COMMIT liest sie den aktuellen, festgeschriebenen Wert 0: Das bedingte UPDATE ändert keine Zeile (ROW_COUNT() = 0), bzw. das SELECT … FOR UPDATE liefert 0 – das Backend bricht mit ROLLBACK ab und meldet „ausgebucht“. Die Transaktionen laufen dadurch an dieser Stelle nacheinander ab.

Eine Transaktion ohne Sperre (nur START TRANSACTION … COMMIT um den alten Entwurf) reicht nicht: Das einfache SELECT liest in MariaDB einen Schnappschuss, beide lesen weiterhin 1 (höchstens 3 Punkte).

Ablauf und Begriff (2 Punkte): In t1 sperrt A die Buchung 6, in t2 sperrt B das Angebot 4. In t3 will A das Angebot 4 ändern und wartet auf die Sperre von B. In t4 will B alle Buchungen zu Angebot 4 ändern, darunter Buchung 6, und wartet auf die Sperre von A. Beide Transaktionen warten gegenseitig auf eine Sperre, die die jeweils andere hält – keine kann jemals weiterarbeiten. Diese gegenseitige Blockade heißt Deadlock (Verklemmung).

Reaktion von MariaDB (1 Punkt): InnoDB erkennt den Zyklus, wählt eine Transaktion als „Opfer“, bricht sie mit Fehler 1213 ab und nimmt sie vollständig zurück. Die andere erhält die Sperre und läuft weiter. Das Backend muss den Fehler abfangen und die abgebrochene Transaktion wiederholen.

Vermeidung (1 Punkt), zum Beispiel: Alle Abläufe sperren die Tabellen bzw. Datensätze in derselben Reihenfolge (immer zuerst das Angebot, dann die Buchungen – A müsste also zuerst angebot ändern). Außerdem helfen kurze Transaktionen und eine automatische Wiederholung nach Fehler 1213.

Eine Stored Procedure (gespeicherte Prozedur) ist eine benannte Folge von SQL-Anweisungen, die im Datenbankmanagementsystem gespeichert ist (1 Punkt). Clients rufen sie mit einem einzigen Befehl (CALL name(…)) auf; sie kann Parameter übernehmen und zurückgeben, Variablen, Verzweigungen, Schleifen und Fehlerbehandlung enthalten und so ganze Abläufe einschließlich Transaktionen auf dem Datenbankserver ausführen (1 Punkt).

Je Vorteil 1 Punkt, zum Beispiel:

• Die Buchungslogik liegt an einer Stelle; Web-App, Android- und iOS-App verhalten sich gleich und können die Prüfungen (Einwilligung, eigenes Angebot, freie Plätze) nicht „vergessen“.

• Transaktion und Sperre sind fest eingebaut; ein Fehler im App-Code kann keine Überbuchung mehr verursachen.

• Weniger Netzwerkverkehr: ein Aufruf statt mehrerer Einzelanweisungen; die Sperre wird nur kurz gehalten.

• Sicherheit und Datenschutz: Das App-Konto erhält nur das Recht EXECUTE auf die Prozedur, aber keine direkten Schreibrechte auf buchung und angebot und keinen Lesezugriff auf Geburtsdaten; Parameter werden nicht als SQL-Text zusammengesetzt (Schutz vor SQL-Injection).

Je Zeile 1 Punkt:

  -- (1)
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_ergebnis = 'Fehler: nichts gebucht';
  END;

  -- (2)
  START TRANSACTION;

  -- (3)
  COMMIT;

Der Handler muss nach den Variablen deklariert werden (MariaDB verlangt die Reihenfolge Variablen – Handler – Anweisungen). EXIT beendet die Prozedur nach dem Handler; CONTINUE wäre falsch, weil die Prozedur sonst nach dem Fehler weiterliefe und am Ende COMMIT ausführen würde. Wer den Fehler zusätzlich an das Backend weitergeben möchte, ergänzt RESIGNAL; (dann wird p_ergebnis allerdings nicht zurückgegeben). Das COMMIT in (3) beendet die Transaktion auch in den Zweigen ohne Buchung und gibt die Sperre auf das Angebot frei.

Je Zeile 1 Punkt:

Aufruf 4 zeigt die Atomarität: Ohne Transaktion und Handler wäre ein Platz „verschwunden“, ohne dass jemand gebucht hat.

CALL sofortbuchung(4, 4, @e);
SELECT @e;

(1 Punkt) Ergebnis (1 Punkt): bestaetigt; Lukas (20) erhält eine neue Buchung, Angebot 4 hat danach noch 1 freien Platz und bleibt offen. Aus Java würde die Prozedur über ein CallableStatement ({call sofortbuchung(?, ?, ?)}) mit registriertem OUT-Parameter aufgerufen.

DELIMITER //
CREATE TRIGGER trg_angebot_kosten
BEFORE INSERT ON angebot
FOR EACH ROW
BEGIN
  DECLARE v_km DECIMAL(5,1);

  SELECT entfernung_km INTO v_km
  FROM   haltepunkt
  WHERE  haltepunkt_id = NEW.haltepunkt_id;

  IF NEW.kostenbeitrag_eur > v_km * 0.10 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Kostenbeitrag über 0,10 EUR je km';
  END IF;
END //
DELIMITER ;

Bewertung: BEFORE INSERT ON angebot FOR EACH ROW 1 Punkt, Entfernung des Haltepunkts über NEW.haltepunkt_id ermitteln 1 Punkt, Vergleich mit NEW.kostenbeitrag_eur 1 Punkt, Abweisen mit SIGNAL 1 Punkt. Statt der Variablen ist eine Unterabfrage im IF richtig. Ein AFTER-Trigger mit SIGNAL würde das INSERT zwar auch zurücknehmen; BEFORE ist aber der übliche Zeitpunkt für Prüfungen.

Ergebnis (1 Punkt): Höchstbetrag ab Bad Salzuflen: 19,4 km · 0,10 €/km = 1,94 €. 2,00 € liegen darüber; der Trigger löst den Fehler ERROR 1644 (45000): Kostenbeitrag über 0,10 EUR je km aus, das Angebot wird nicht gespeichert. Mit 1,90 € würde es gespeichert.

• Ein CHECK-Constraint kann nur Werte der eigenen Zeile prüfen. Die Entfernung steht aber in der Tabelle haltepunkt; Unterabfragen sind in CHECK nicht erlaubt (1 Punkt).

• Ein Trigger wird nur durch künftige Ereignisse ausgelöst. Angebot 5 wurde vor dem Anlegen des Triggers gespeichert und wird nicht nachträglich geprüft (1 Punkt).

• Zusätzlich nötig (1 Punkt): ein entsprechender BEFORE UPDATE-Trigger, damit ein Angebot nicht nachträglich auf einen zu hohen Betrag geändert werden kann (auch ein Wechsel des Haltepunkts), und eine einmalige Bereinigung des Bestands, z. B. UPDATE angebot a JOIN haltepunkt h ON h.haltepunkt_id = a.haltepunkt_id SET a.kostenbeitrag_eur = ROUND(h.entfernung_km * 0.10, 2) WHERE a.kostenbeitrag_eur > h.entfernung_km * 0.10; (Angebot 5 → 1,94 €) nach Rücksprache mit Niklas Fischer.

Erläuterung (2 Punkte): Der Trigger wird durch ein UPDATE auf angebot ausgelöst und versucht selbst, dieselbe Tabelle angebot mit UPDATE zu ändern. Das ist in MariaDB verboten, weil die auslösende Anweisung die Tabelle gerade bearbeitet (und der Trigger sich sonst selbst immer wieder auslösen könnte). Der Fehler bricht die auslösende Anweisung ab – in der Prozedur greift der Handler, deshalb scheitert jede Sofortbuchung.

Korrektur (2 Punkte): Werte der gerade geänderten Zeile werden in einem BEFORE-Trigger über NEW gesetzt:

DROP TRIGGER trg_angebot_voll;

DELIMITER //
CREATE TRIGGER trg_angebot_voll
BEFORE UPDATE ON angebot
FOR EACH ROW
BEGIN
  IF NEW.freie_plaetze = 0 AND NEW.status = 'offen' THEN
    SET NEW.status = 'voll';
  END IF;
END //
DELIMITER ;

Bewertung: BEFORE UPDATE 1 Punkt, SET NEW.status = 'voll' mit Bedingung 1 Punkt. Die Bedingung NEW.status = 'offen' verhindert, dass ein abgesagtes Angebot ohne freie Plätze wieder als voll erscheint. Sinnvolle Ergänzung: Wird ein Platz durch Stornierung wieder frei, ELSEIF NEW.freie_plaetze > 0 AND OLD.status = 'voll' AND NEW.status = 'voll' THEN SET NEW.status = 'offen';. Die zweite UPDATE-Anweisung in der Prozedur kann danach entfallen.

Je Zeile 1 Punkt: Durability (Dauerhaftigkeit) – Atomicity (Atomarität) – Isolation – Consistency (Konsistenz).

• 1. Vor dem COMMIT: Die Transaktion war nicht abgeschlossen. Beim Neustart nimmt InnoDB alle ihre Änderungen zurück (Atomarität; mit Hilfe des Undo Log). Weder die Buchung noch die Verringerung der Plätze sind vorhanden; die App hat keine Bestätigung erhalten, die Person muss erneut buchen (1 Punkt).
• 2. Nach dem COMMIT: Die Transaktion war bestätigt. Buchung und verringerte Platzzahl sind nach dem Neustart vorhanden, auch wenn sie im Moment des Absturzes noch nicht in die eigentlichen Datendateien geschrieben waren (Dauerhaftigkeit; 1 Punkt).
• Umsetzung (1 Punkt): MariaDB schreibt jede Änderung zuerst in ein Transaktionsprotokoll (Redo Log) und gilt ein COMMIT erst als erfolgreich, wenn dieser Eintrag auf dem Datenträger steht (Standard innodb_flush_log_at_trx_commit = 1). Beim Neustart werden bestätigte Transaktionen aus dem Protokoll wiederhergestellt und unbestätigte zurückgenommen (Crash Recovery).

Je Merkmal 2 Punkte für die Erläuterung und 1 Punkt für den Bezug zur Situation:

• Indexierung: Ein Index ist eine zusätzliche, sortierte Zugriffsstruktur (meist B-Baum) über eine oder mehrere Spalten. Das DBMS findet damit Datensätze, ohne die ganze Tabelle zu lesen; Suchen, Verknüpfen und Sortieren werden bei großen Tabellen deutlich schneller. Leistungsfähige Systeme bieten mehrere Indexarten (z. B. zusammengesetzte, Volltext- oder räumliche Indizes) und einen Optimierer, der sie gut nutzt. Bezug: Die App sucht ständig in Tabellen mit Zehntausenden Konten und Hunderttausenden Aktivitäten (Anmeldung per E-Mail, Suche nach Aktivitäten in der Nähe und nach Datum).

• Sperrmechanismen: Sperren verhindern Konflikte, wenn mehrere Benutzer*innen gleichzeitig dieselben Daten ändern (z. B. verlorene Updates). Leistungsfähige Systeme sperren feingranular nur die betroffenen Zeilen (row-level locking) statt ganzer Tabellen; so können viele Transaktionen parallel laufen, ohne aufeinander zu warten. Bezug: Viele Nutzende melden sich gleichzeitig zu Aktivitäten an (Wartelisten, begrenzte Plätze); eine Tabellensperre auf teilnahme würde bei jedem Klick alle anderen Anmeldungen blockieren.

Je Anforderung 2 Punkte (konkrete, passende Maßnahme mit Bezug), zum Beispiel:

Je Grund 1 Punkt für die Erläuterung mit Bezug auf die Daten und 1 Punkt für eine passende Gegenmaßnahme, zum Beispiel:

• Langsame Abfrage ohne passenden Index: Die häufigste Abfrage im Slow-Query-Log filtert nach status und beginn und sortiert nach beginn. Ohne Index liest MariaDB jedes Mal die ganze Tabelle aktivitaet; bei Hunderten gleichzeitigen Aufrufen steigt die CPU-Last. Gegenmaßnahme: Abfrage mit EXPLAIN prüfen und einen Index auf (status, beginn) anlegen (siehe d).

• Datensicherung während der Arbeitszeit: Die nächtliche Vollsicherung läuft seit 2:00 Uhr und ist noch nicht fertig. Sie liest den gesamten Datenbestand und belastet Datenträger und CPU. Gegenmaßnahme: Sicherung auf einem Replikat ausführen, inkrementell sichern oder Dauer überwachen und rechtzeitig vor Arbeitsbeginn beenden.

• Zu viele Verbindungen: 487 von 500 möglichen Verbindungen sind belegt; neue Anfragen müssen warten oder werden abgewiesen. Ursache können die langsamen Abfragen selbst sein (Verbindungen bleiben länger offen) oder ein App-Server, der Verbindungen nicht schließt. Gegenmaßnahme: Verbindungs-Pooling im App-Server, Ursache mit SHOW PROCESSLIST suchen, Last auf Replikate verteilen.

• Arbeitsspeicher und Datenträger am Limit: Der Speicher ist fast voll und es wird ausgelagert (Swap); der Datenträger ist innerhalb einer Woche von 71 % auf 93 % gewachsen (z. B. durch wachsende Protokolle oder Sicherungsdateien). Gegenmaßnahme: Puffergrößen prüfen, RAM erweitern, Ursache des Wachstums suchen (Binärlogs aufräumen, Sicherungen auslagern), Speicher erweitern.

Je Aussage 1 Punkt: type ALL und key NULL bedeuten, dass kein Index genutzt wird und MariaDB die ganze Tabelle liest (full table scan, rund 168.000 Zeilen), obwohl nur die Aktivitäten einer Woche gebraucht werden. Using filesort zeigt, dass das Ergebnis anschließend zusätzlich sortiert werden muss.

2 Punkte für die Anweisung, 1 Punkt für die Begründung:

CREATE INDEX idx_akt_status_beginn ON aktivitaet (status, beginn);

Zuerst die Spalte, die auf Gleichheit geprüft wird (status = 'geplant'), dann die Spalte mit dem Bereich und der Sortierung (beginn). So findet MariaDB im Index direkt alle geplanten Aktivitäten im Zeitraum und kann sie in Indexreihenfolge liefern; der Plan zeigt danach type range mit wenigen Hundert Zeilen und kein filesort mehr. Ein Index nur auf beginn ist als Alternative ebenfalls richtig.

Je zwei Inhalte 1 Punkt: Tabellenstruktur (CREATE TABLE mit Datentypen, Schlüsseln, Constraints, Indizes), Daten aller Tabellen (INSERT), Beziehungen (Fremdschlüssel), Views, Trigger, Stored Procedures und Ereignisse, Benutzerkonten und ihre Rechte.

3 Punkte für den Dump (Programm, --single-transaction, Routinen/Trigger), 1 Punkt für das Einspielen:

mariadb-dump --single-transaction --routines --triggers letsmeet > letsmeet.sql

mariadb letsmeet_test < letsmeet.sql

--single-transaction liest alle InnoDB-Tabellen in einer einzigen Transaktion: Der Dump zeigt einen einheitlichen Stand, ohne Tabellen zu sperren. Trigger sind bei mariadb-dump standardmäßig enthalten, Stored Procedures nur mit --routines. Anmeldedaten werden in der Praxis über eine geschützte Optionsdatei übergeben (--defaults-extra-file) statt auf der Kommandozeile. mysqldump bzw. mysql sind ebenfalls richtig.

1 Punkt je Argument, höchstens 3 Punkte:

• Ein Dump ist eine Momentaufnahme. Fällt db01 um 16 Uhr aus, wären ohne weitere Maßnahmen alle Anmeldungen und Änderungen seit der letzten Nacht verloren. Deshalb wird zusätzlich das Binärlog gesichert, mit dem sich die Änderungen bis kurz vor dem Ausfall nachspielen lassen (Point-in-Time-Recovery).

• Ein Dump hilft nicht gegen Ausfallzeit: Das Einspielen von Hunderttausenden Datensätzen dauert. Für hohe Verfügbarkeit ist ein Replikat nötig, auf das umgeschaltet werden kann.

• Eine Sicherung ist erst nützlich, wenn die Wiederherstellung regelmäßig getestet wird und die Sicherung getrennt vom Server (eine Kopie außer Haus, vor Ransomware geschützt) aufbewahrt wird.

• Fazit: Der Dump ist ein wichtiger Baustein, ersetzt aber weder Binärlog, Replikation noch Wiederherstellungstests.

Je sinnvollem Schritt in passender Reihenfolge 1 Punkt; viele Lösungen sind möglich, zum Beispiel:

2. Datenbank barcamp mit Zeichensatz utf8mb4 und passender Kollation anlegen.

3. Tabellen einschließlich der neuen Tabelle kurzlink mit Datentypen, Schlüsseln, Constraints und Indizes per SQL-DDL erstellen.

4. Daten vom Testrechner übernehmen (Dump einspielen oder CSV-Import in der richtigen Tabellenreihenfolge).

5. Implementierung testen: Primär- und Fremdschlüssel, Constraints, Anzahl der Datensätze, typische Abfragen; verwaiste Datensätze suchen.

6. Benutzer und Rollen für Orga-Team, Check-in und Webseite anlegen, Rechte vergeben und testen.

7. Datensicherung einrichten und eine Wiederherstellung testen; danach die Anwendungen auf den neuen Server umstellen und den alten Bestand stilllegen.

Auch richtig: Views anlegen, Verbindung über TLS einrichten, Überwachung einrichten, Daten vor der Übernahme bereinigen.

3 Punkte (Begriff 2 Punkte, Beispiel 1 Punkt): Referentielle Integrität bedeutet, dass Beziehungen zwischen Tabellen über Fremdschlüssel widerspruchsfrei bleiben: Ein Fremdschlüsselwert darf nur vorkommen, wenn es in der referenzierten Tabelle einen Datensatz mit diesem Primärschlüsselwert gibt (oder er ist NULL, sofern erlaubt). Das DBMS weist Einfügungen, Änderungen und Löschungen ab, die das verletzen würden. Beispiel: Jede Zeile in anmeldung verweist mit person_id auf eine Person. Eine Anmeldung mit person_id = 15 darf es nur geben, wenn in person die Person 15 existiert; eine Person mit Anmeldungen darf nicht einfach gelöscht werden (fk_anm_person ohne ON DELETE).

2 Punkte für eine zulässige Reihenfolge, 2 Punkte für die Begründung. Zum Beispiel:

1. veranstaltung, person, raum (verweisen auf keine andere Tabelle, Reihenfolge untereinander beliebig)

2. slot (verweist auf veranstaltung)

3. anmeldung (verweist auf veranstaltung und person)

4. session (verweist auf veranstaltung, raum, slot)

5. session_person (verweist auf session und person)

6. session_buchung (verweist auf session und anmeldung)

Begründung: Eine Tabelle darf erst befüllt werden, wenn alle Tabellen, auf die ihre Fremdschlüssel verweisen, schon die passenden Datensätze enthalten. Also zuerst die Elterntabellen, dann die Kindtabellen. Würde zum Beispiel anmeldung vor person importiert, wiese MariaDB jede Zeile ab (Cannot add or update a child row: a foreign key constraint fails). Andere Reihenfolgen, die diese Regel einhalten, sind ebenfalls richtig.

1 Punkt für die Spaltenauswahl, 2 Punkte für die Verknüpfung (LEFT JOIN oder NOT EXISTS/NOT IN), 1 Punkt für die Bedingung:

SELECT a.anmeldung_id, a.person_id, a.badge_code
FROM anmeldung a
LEFT JOIN person p ON p.person_id = a.person_id
WHERE p.person_id IS NULL;

Gleichwertig:

SELECT a.anmeldung_id, a.person_id, a.badge_code
FROM anmeldung a
WHERE NOT EXISTS (SELECT 1 FROM person p WHERE p.person_id = a.person_id);

Ergebnis auf dem übernommenen Bestand (die Personen gehen nur bis person_id 14):

Je Aspekt 1 Punkt: Ohne Prüfung übernimmt MariaDB auch Fremdschlüsselwerte ohne passenden Elterndatensatz; beim Wiedereinschalten werden die vorhandenen Daten nicht nachträglich geprüft. Verwaiste Datensätze bleiben unbemerkt und führen später zu Fehlern (z. B. ein Badge ohne Namen beim Check-in, falsche Teilnehmerzahlen bei JOIN-Abfragen). Vertretbar ist das Abschalten nur kurzzeitig beim Einspielen eines vollständigen, in sich konsistenten Dumps (so macht es mariadb-dump selbst), wenn danach mit Abfragen wie in da) geprüft wird.

1 Punkt für den Begriff, 2 Punkte für die Ursache: Ist eine Zeichenkette bzw. ein Vergleich case sensitive, wird zwischen Groß- und Kleinbuchstaben unterschieden; Xk3Pq9 und xk3pq9 sind dann verschiedene Werte. Die Kollation utf8mb4_unicode_ci ist aber case insensitive (ci): Beim Vergleich, also auch beim Prüfen des Primärschlüssels, gelten beide Codes als gleich. Der zweite Code verletzt damit die Eindeutigkeit des Primärschlüssels. Außerdem würde eine Suche nach xk3pq9 die Folien von Session 1 liefern – beim Aufruf eines Kurzlinks käme also der falsche Inhalt.

2 Punkte (1 Punkt ALTER TABLE … MODIFY, 1 Punkt binäre bzw. case-sensitive Kollation):

ALTER TABLE kurzlink
  MODIFY code VARCHAR(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

Danach lassen sich Xk3Pq9 und xk3pq9 beide speichern, und die Suche WHERE code = 'xk3pq9' findet nur den passenden Datensatz. Gleichwertig ist VARBINARY(8) oder eine …_cs-Kollation, sofern das DBMS sie anbietet. Der Primärschlüssel bleibt bei MODIFY erhalten.