Schwere Probeklausur: Datenbanken - Praktische Umsetzung

Lösung 1: DDL und Integrität

CREATE TABLE artist_contract (
  contract_id integer GENERATED ALWAYS AS IDENTITY,
  artist_id integer NOT NULL,
  label_name varchar(80) NOT NULL,
  starts_on date NOT NULL,
  ends_on date,
  royalty_rate numeric(5,4) NOT NULL,
  exclusive boolean NOT NULL DEFAULT true,

  CONSTRAINT pk_artist_contract
    PRIMARY KEY (contract_id),
  CONSTRAINT fk_artist_contract_artist
    FOREIGN KEY (artist_id)
    REFERENCES artist (artist_id)
    ON DELETE CASCADE,
  CONSTRAINT ck_artist_contract_dates
    CHECK (ends_on IS NULL OR ends_on >= starts_on),
  CONSTRAINT ck_artist_contract_royalty
    CHECK (royalty_rate > 0 AND royalty_rate <= 0.5),
  CONSTRAINT uq_artist_contract_label_start
    UNIQUE (artist_id, label_name, starts_on)
);

ALTER TABLE album
  ADD CONSTRAINT uq_album_artist_title
  UNIQUE (artist_id, title);

Risiko: Der Befehl scheitert, wenn Bestandsdaten bereits doppelte Kombinationen aus artist_id und title enthalten. Außerdem kann das Sperren der Tabelle im Produktivbetrieb stören.

Lösung 2: Abfrageergebnis

Der Filter auf CANCELLED steht in der ON-Bedingung, deshalb bleiben Kunden ohne passende Rechnung beim LEFT JOIN erhalten; in der WHERE-Klausel würden die NULL-erweiterten Zeilen herausfallen.

Lösung 3: SQL-Abfragen

-- 1) Alben mit mindestens zwei Genres und überdurchschnittlicher Gesamtspielzeit
WITH album_lengths AS (
  SELECT
    a.album_id,
    ar.name AS artist_name,
    a.title AS album_title,
    COUNT(DISTINCT t.genre_id) AS genre_count,
    SUM(t.milliseconds) AS total_ms
  FROM album a
  JOIN artist ar ON ar.artist_id = a.artist_id
  JOIN track t ON t.album_id = a.album_id
  GROUP BY a.album_id, ar.name, a.title
)
SELECT
  artist_name,
  album_title,
  genre_count,
  ROUND(total_ms / 60000.0, 2) AS total_minutes
FROM album_lengths
WHERE genre_count >= 2
  AND total_ms > (SELECT AVG(total_ms) FROM album_lengths)
ORDER BY total_minutes DESC;

-- 2) Kunden, deren jede bezahlte Rechnung mindestens zwei Positionen hat
SELECT c.customer_id, c.email
FROM customer c
WHERE EXISTS (
  SELECT 1
  FROM invoice i
  WHERE i.customer_id = c.customer_id
    AND i.status = 'PAID'
)
AND NOT EXISTS (
  SELECT 1
  FROM invoice i
  WHERE i.customer_id = c.customer_id
    AND i.status = 'PAID'
    AND (
      SELECT COUNT(*)
      FROM invoice_line il
      WHERE il.invoice_id = i.invoice_id
    ) < 2
);

-- 3) Pro Genre: nie verkaufte, aber in Playlist enthaltene Tracks
SELECT
  g.name AS genre_name,
  COUNT(t.track_id) AS unsold_playlist_tracks
FROM genre g
LEFT JOIN track t
  ON t.genre_id = g.genre_id
 AND EXISTS (
   SELECT 1
   FROM playlist_track pt
   WHERE pt.track_id = t.track_id
 )
 AND NOT EXISTS (
   SELECT 1
   FROM invoice_line il
   WHERE il.track_id = t.track_id
 )
GROUP BY g.genre_id, g.name
ORDER BY g.name;

-- 4) Top-5-Alben nach Umsatz im Jahr 2025
SELECT
  ar.name AS artist_name,
  a.title AS album_title,
  SUM(il.quantity * il.unit_price) AS revenue,
  SUM(il.quantity) AS sold_tracks
FROM invoice i
JOIN invoice_line il ON il.invoice_id = i.invoice_id
JOIN track t ON t.track_id = il.track_id
JOIN album a ON a.album_id = t.album_id
JOIN artist ar ON ar.artist_id = a.artist_id
WHERE i.status = 'PAID'
  AND i.invoice_date >= DATE '2025-01-01'
  AND i.invoice_date < DATE '2026-01-01'
GROUP BY ar.artist_id, ar.name, a.album_id, a.title
ORDER BY revenue DESC, sold_tracks DESC, artist_name, album_title
LIMIT 5;

Lösung 4: Views, Index, Rechte und Systemkatalog

CREATE OR REPLACE VIEW v_paid_album_revenue AS
SELECT
  ar.artist_id,
  ar.name AS artist_name,
  a.album_id,
  a.title AS album_title,
  SUM(il.quantity * il.unit_price) AS revenue
FROM invoice i
JOIN invoice_line il ON il.invoice_id = i.invoice_id
JOIN track t ON t.track_id = il.track_id
JOIN album a ON a.album_id = t.album_id
JOIN artist ar ON ar.artist_id = a.artist_id
WHERE i.status = 'PAID'
GROUP BY ar.artist_id, ar.name, a.album_id, a.title;

Die View ist nicht direkt änderbar, weil sie Joins, Aggregation und GROUP BY enthält. Änderungen müssten über Basistabellen oder über einen passenden INSTEAD OF-Trigger laufen.

CREATE INDEX ix_invoice_status_date
  ON invoice (status, invoice_date);

CREATE INDEX ix_invoice_line_track
  ON invoice_line (track_id);

Der erste Index unterstützt Filter nach Status und Zeitraum, der zweite Index unterstützt Joins und Existenzprüfungen von Track zu Rechnungspositionen.

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'invoice_line'
ORDER BY ordinal_position;

Lösung 5: Function und Trigger

CREATE OR REPLACE FUNCTION recalc_invoice_total(p_invoice_id integer)
RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
  v_total numeric(10,2);
BEGIN
  SELECT COALESCE(SUM(quantity * unit_price), 0)
    INTO v_total
  FROM invoice_line
  WHERE invoice_id = p_invoice_id;

  UPDATE invoice
     SET total = v_total
   WHERE invoice_id = p_invoice_id;

  RETURN v_total;
END;
$$;

CREATE OR REPLACE FUNCTION trg_invoice_line_recalc()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP IN ('UPDATE', 'DELETE') AND EXISTS (
    SELECT 1
    FROM invoice
    WHERE invoice_id = OLD.invoice_id
      AND status = 'PAID'
  ) THEN
    RAISE EXCEPTION 'Paid invoice % must not be changed', OLD.invoice_id;
  END IF;

  IF TG_OP IN ('INSERT', 'UPDATE') THEN
    IF NEW.quantity <= 0 OR NEW.unit_price < 0 THEN
      RAISE EXCEPTION 'Invalid invoice line values';
    END IF;

    IF EXISTS (
      SELECT 1
      FROM invoice
      WHERE invoice_id = NEW.invoice_id
        AND status = 'PAID'
    ) THEN
      RAISE EXCEPTION 'Paid invoice % must not be changed', NEW.invoice_id;
    END IF;
  END IF;

  IF TG_OP IN ('UPDATE', 'DELETE') THEN
    PERFORM recalc_invoice_total(OLD.invoice_id);
  END IF;

  IF TG_OP IN ('INSERT', 'UPDATE') THEN
    PERFORM recalc_invoice_total(NEW.invoice_id);
    RETURN NEW;
  END IF;

  RETURN OLD;
END;
$$;

CREATE TRIGGER invoice_line_recalc_after_change
AFTER INSERT OR UPDATE OR DELETE ON invoice_line
FOR EACH ROW
EXECUTE FUNCTION trg_invoice_line_recalc();

Nebenläufigkeitsgrenze: Zwei parallele Änderungen derselben Rechnung können sich gegenseitig überholen oder unerwartet blockieren. Praktisch entschärft man das durch klare Transaktionsgrenzen und Sperren der Rechnungszeile, z. B. SELECT ... FOR UPDATE auf invoice, bevor Positionen geändert werden.

Lösung 6: Transaktionen

Bei READ COMMITTED kann T1 beim zweiten Lesen 125.00 sehen, weil T2 inzwischen committet hat und der Trigger invoice.total aktualisiert hat. Das ist ein Non-Repeatable Read auf der Rechnungszeile; liest T1 direkt die Positionen per Aggregat, entspricht der neu auftauchende Datensatz zusätzlich einem Phantom-Effekt.

Bei REPEATABLE READ arbeitet T1 mit einem stabilen Snapshot und sieht beim zweiten Lesen weiterhin 100.00, solange T1 nur liest. Für blockierendes Lesen:

BEGIN;
SELECT total
FROM invoice
WHERE invoice_id = 42
FOR UPDATE;

Deadlock-Beispiel: T1 sperrt Rechnung 1 und wartet danach auf Rechnung 2; T2 sperrt Rechnung 2 und wartet danach auf Rechnung 1. Vermeidung: alle Transaktionen sperren Ressourcen in derselben Reihenfolge, halten Sperren kurz und behandeln Deadlock-Fehler mit Retry.

2PL ist ein Sperrprotokoll für Isolation: Sperren werden in einer Wachstumsphase erworben und in einer Schrumpfungsphase freigegeben. 2PC ist ein Commit-Protokoll für verteilte Transaktionen: Coordinator fragt Teilnehmer in der Prepare-Phase und entscheidet danach Commit oder Rollback. 2PL verhindert Nebenläufigkeitsanomalien, 2PC koordiniert atomaren Abschluss über mehrere Systeme.

Lösung 7: JDBC und JPA

String updateSql = """
    UPDATE track t
       SET unit_price = unit_price * ?
      FROM genre g
     WHERE g.genre_id = t.genre_id
       AND g.name = ?
    """;
String logSql = """
    INSERT INTO price_log(genre_name, factor, changed_at)
    VALUES (?, ?, CURRENT_TIMESTAMP)
    """;

try {
    conn.setAutoCommit(false);

    try (PreparedStatement update = conn.prepareStatement(updateSql);
         PreparedStatement log = conn.prepareStatement(logSql)) {
        update.setBigDecimal(1, factor);
        update.setString(2, genre);
        int changedRows = update.executeUpdate();

        log.setString(1, genre);
        log.setBigDecimal(2, factor);
        log.executeUpdate();

        if (changedRows == 0) {
            throw new SQLException("No tracks changed for genre: " + genre);
        }
    }

    conn.commit();
} catch (SQLException ex) {
    conn.rollback();
    throw ex;
} finally {
    conn.setAutoCommit(true);
    conn.close();
}

Fehler bei Statement und String-Konkatenation: SQL Injection über genre und fehlerhafte Typ-/Formatbehandlung bei Zahlen oder Strings. Außerdem gehen Ausführungsplan-Wiederverwendung und klare Parameterbindung verloren.

Lösung 8: Verteilte Datenbanken und NoSQL

db.artist_profiles.find(
  { active: true, tags: "live" },
  { _id: 0, name: 1, "metadata.tourYear": 1 }
);

CREATE TABLE listen_events_by_user_day (
  user_id uuid,
  event_day date,
  listened_at timestamp,
  track_id uuid,
  artist_id uuid,
  seconds_played int,
  PRIMARY KEY ((user_id, event_day), listened_at, track_id)
) WITH CLUSTERING ORDER BY (listened_at DESC, track_id ASC);

Partition Key: (user_id, event_day). Clustering Columns: listened_at, track_id. Nicht passend ist z. B. eine beliebige Suche nach artist_id über alle Nutzer und Tage ohne eigene Tabelle oder Sekundärmodellierung.

MATCH (c:Customer)-[:USES]->(p:Playlist)-[:CONTAINS]->(:Track)
      -[:PERFORMED_BY]->(a:Artist {name: 'Metallica'})
RETURN DISTINCT c.customer_id, c.email
ORDER BY c.email;

Lösung 9: Multiple Choice