Aufgabe 19 - Datenintegrität

Worum geht es?

In dieser Übung sorgen Sie dafür, dass nur korrekte und sinnvolle Daten in die Datenbank gelangen (siehe Kapitel 11 - Datenintegrität). Sie setzen Integritätsbedingungen ein, wählen referenzielle Aktionen passend aus und beobachten, was bei Constraint-Verletzungen passiert.

In dieser Übung üben Sie:

Benennen & Abgrenzen: die vier Kategorien der Datenintegrität - Entitätsintegrität, referenzielle Integrität, Domänenintegrität und benutzerdefinierte Integrität - unterscheiden.
Einsetzen: Integritätsbedingungen (NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT) korrekt anwenden.
Auswählen & Begründen: referenzielle Aktionen (CASCADE, RESTRICT, SET NULL) situationsgerecht wählen.
Verwalten: Constraints benennen und per ALTER TABLE nachträglich hinzufügen oder entfernen.
Verstehen: das Verhalten bei Constraint-Verletzungen nachvollziehen und vermeiden.

Szenario: Ticketsystem für Veranstaltungen

Eine Firma betreibt ein kleines Online-Ticketsystem für Konzerte und Sportevents. Die Datenbank verwaltet Veranstaltungen, Kunden und Bestellungen. Die folgenden Tabellen (absichtlich ohne Integritätsbedingungen!) dienen als Ausgangspunkt für alle Aufgaben:

CREATE TABLE venues (
    venue_id   INT,
    name       TEXT,
    city       TEXT,
    capacity   INT
);

CREATE TABLE events (
    event_id   INT,
    venue_id   INT,
    title      TEXT,
    event_date DATE,
    price      NUMERIC(8, 2),
    status     TEXT
);

CREATE TABLE customers (
    customer_id INT,
    email       TEXT,
    first_name  TEXT,
    last_name   TEXT,
    created_at  TIMESTAMP
);

CREATE TABLE orders (
    order_id    INT,
    customer_id INT,
    event_id    INT,
    quantity    INT,
    total       NUMERIC(10, 2),
    ordered_at  TIMESTAMP
);

INSERT INTO venues VALUES
(1, 'Stadthalle Villach',  'Villach',  5000),
(2, 'Konzerthaus Wien',    'Wien',     1200),
(3, 'Freilichtbühne Burg', 'Klagenfurt', 800);

INSERT INTO events VALUES
(101, 1, 'Rock am See',         '2027-07-15', 49.90, 'ACTIVE'),
(102, 2, 'Klassik Nacht',       '2027-09-20', 89.00, 'ACTIVE'),
(103, 3, 'Open Air Kabarett',   '2027-08-10', 29.50, 'CANCELLED'),
(104, 2, 'Jazz im Konzerthaus', '2027-11-05', 65.00, 'ACTIVE');

INSERT INTO customers VALUES
(1, '[email protected]',   'Max',   'Mustermann', '2024-01-10 09:00:00'),
(2, '[email protected]',  'Anna',  'Muster',     '2024-03-22 14:30:00'),
(3, '[email protected]', 'Chris', 'Test',       '2025-01-05 11:15:00');

INSERT INTO orders VALUES
(1001, 1, 101, 2, 99.80,  '2025-02-01 10:00:00'),
(1002, 2, 102, 1, 89.00,  '2025-02-15 16:45:00'),
(1003, 1, 104, 3, 195.00, '2025-03-10 08:30:00');

Arbeitsaufträge

Aufgabe 1: Integritätskategorien zuordnen

Ordnen Sie jede der folgenden Situationen einer der vier Integritätskategorien zu: Entitätsintegrität, Referenzielle Integrität, Domänenintegrität, Benutzerdefinierte Integrität.

Situation	Kategorie
a) Eine Bestellung verweist auf eine `event_id`, die in der Tabelle `events` gar nicht existiert.	?
b) Eine Veranstaltung hat keinen Titel (`NULL`).	?
c) Zwei Kunden haben dieselbe `customer_id`.	?
d) Der Ticketpreis einer Veranstaltung ist `-15.00`.	?
e) Eine Bestellung enthält `quantity = 0` (also null Tickets).	?
f) Das `status`-Feld einer Veranstaltung enthält den Wert `'VIELLEICHT'` statt `'ACTIVE'` oder `'CANCELLED'`.	?

Aufgabe 2: Basistabelle mit Integritätsbedingungen

Erstellen Sie die Tabelle customers neu — diesmal mit allen notwendigen Integritätsbedingungen:

customer_id ist der Primärschlüssel und wird automatisch vergeben.
email muss angegeben werden und darf nicht doppelt vorkommen.
first_name und last_name müssen immer ausgefüllt sein.
created_at soll automatisch den aktuellen Zeitstempel erhalten, wenn kein Wert angegeben wird.

Testen Sie anschließend, was passiert, wenn Sie versuchen, zwei Kunden mit derselben E-Mail-Adresse einzufügen.

Aufgabe 3: Fremdschlüssel und referenzielle Aktionen

Erstellen Sie die Tabelle events neu und verknüpfen Sie sie über einen Fremdschlüssel mit der Tabelle venues.

Beantworten Sie dabei folgende Fragen und implementieren Sie die gewählte Lösung:

Was soll passieren, wenn eine venue gelöscht wird, der noch aktive events zugeordnet sind?
→ Soll das Löschen verboten werden (RESTRICT) oder sollen die Events automatisch mitgelöscht werden (CASCADE)?
→ Begründen Sie Ihre Entscheidung aus Sicht des Ticketsystems.
Was soll passieren, wenn die venue_id einer Location aktualisiert wird?

Vergeben Sie dem Fremdschlüssel einen aussagekräftigen Namen nach der Konvention fk_<tabelle>_<referenz>.

Aufgabe 4: CHECK-Constraints für Geschäftsregeln

Die Firma hat folgende Geschäftsregeln definiert. Implementieren Sie diese als CHECK-Constraints in der Tabelle events bzw. orders:

Für events:

Der Ticketpreis muss größer als null sein (kostenlose Events sind über ein anderes System verwaltet).
Das Feld status darf nur die Werte 'ACTIVE', 'CANCELLED' oder 'SOLD_OUT' enthalten.
Das Veranstaltungsdatum muss in der Zukunft liegen (zum Zeitpunkt des Einfügens).

Für orders: 4. Die bestellte Menge (quantity) muss mindestens 1 und höchstens 10 betragen. 5. Der berechnete Gesamtbetrag (total) muss größer als null sein.

Vergeben Sie jedem Constraint einen Namen nach der Konvention chk_<tabelle>_<spalte>.

Aufgabe 5: Constraints nachträglich hinzufügen und entfernen

Die Tabellen venues und orders aus dem ursprünglichen Szenario (ohne Integritätsbedingungen) sind bereits mit Daten befüllt. Fügen Sie nun per ALTER TABLE folgende Constraints nachträglich hinzu:

venues.capacity darf nicht NULL sein und muss größer als null sein.
orders.quantity muss zwischen 1 und 10 liegen.

Fügen Sie anschließend bewusst eine ungültige Zeile ein, die einen der Constraints verletzen würde, und beobachten Sie die Fehlermeldung.

Zusatzfrage: Angenommen, in der Tabelle venues gäbe es bereits eine Zeile mit capacity = -50. Was würde passieren, wenn Sie versuchen, den CHECK-Constraint auf einer solchen Tabelle hinzuzufügen? Wie würden Sie das Problem beheben?

Entfernen Sie zum Abschluss den zuletzt hinzugefügten CHECK-Constraint auf orders.quantity wieder.

Aufgabe 6: Vollständiges Schema mit Kaskadenverhalten

Entwerfen Sie das vollständige Datenbankschema für das Ticketsystem von Grund auf neu. Alle vier Tabellen (venues, events, customers, orders) sollen:

Sinnvolle Primärschlüssel mit Autowert haben.
Alle Fremdschlüssel mit benannten Constraints und wohlüberlegten referenziellen Aktionen.
Alle relevanten NOT NULL, UNIQUE und DEFAULT-Bedingungen.
Alle Geschäftsregeln aus Aufgabe 4 als CHECK-Constraints.

Beantworten Sie danach folgende Fragen durch SQL-Abfragen oder Überlegungen:

a) Was passiert, wenn Sie einen Kunden löschen, der noch offene Bestellungen hat? (Erklären Sie das Verhalten Ihrer gewählten referenziellen Aktion.)

b) Nennen Sie drei verschiedene Constraints Ihres Schemas, die durch ungültige Daten verletzt werden könnten. Geben Sie jeweils ein Beispiel für einen ungültigen Wert an und erklären Sie, welche Fehlermeldung die Datenbank ausgeben würde. (Hinweis: Eine einzelne INSERT-Anweisung bricht beim ersten Constraint-Verstoß ab — die Datenbank meldet immer nur einen Fehler auf einmal.)

c) Eine Veranstaltung wird auf 'SOLD_OUT' gesetzt. Schreiben Sie ein UPDATE-Statement, das dies korrekt macht. Was würde passieren, wenn jemand stattdessen status = 'AUSVERKAUFT' setzt?

Abgabe & Kriterien

Abgabe der Datei aufgabe19_integritaet.sql mit allen CREATE TABLE-, ALTER TABLE- und Test-Anweisungen.
Kurze schriftliche Antworten zu Aufgabe 1 (Zuordnungstabelle) und den Begründungsfragen.
Alle Befehle müssen auf einer PostgreSQL-Datenbank fehlerfrei ausführbar sein.

Wissens-Check

Frage 1: Worin unterscheidet sich ein UNIQUE-Constraint von einem PRIMARY KEY? Nennen Sie mindestens zwei Unterschiede.
Frage 2: Ein CHECK-Constraint mit der Bedingung (discount BETWEEN 0 AND 100) soll sicherstellen, dass ein Rabatt immer gültig ist. Was passiert, wenn die Spalte discount den Wert NULL enthält — wird der Constraint verletzt?
Frage 3: Sie haben für einen Fremdschlüssel ON DELETE CASCADE gewählt. Ein Kollege warnt Sie vor “stillen Kettenreaktionen”. Was meint er damit, und wann wäre RESTRICT die bessere Wahl?

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