Aufgabe 19 - Datenintegrität

Aufgabe 19 - Datenintegrität

Worum geht es?

In dieser Übung wird dafür gesorgt, dass nur korrekte Daten in die Datenbank gelangen (siehe Kapitel 11 - Datenintegrität). Integritätsbedingungen werden gesetzt, referenzielle Aktionen passend gewählt und das Verhalten bei Verletzungen beobachtet. Im Expertenteil stößt der CHECK-Constraint an seine Grenze.

Was Sie dafür brauchen

• Kapitel 11 - Datenintegrität.
• Ein PostgreSQL-Server und ein SQL-Editor.

Welche Kompetenzen Sie erwerben und zeigen

• Sie unterscheiden die vier Integritätskategorien.
• Sie setzen NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT korrekt ein.
• Sie wählen referenzielle Aktionen (CASCADE, RESTRICT, SET NULL) begründet.
• Sie erkennen, welche Regeln ein CHECK-Constraint nicht abbilden kann.

Pädagogische Einordnung

• Reproduktion: Kategorien zuordnen und Constraints setzen (Teil A).
• Reorganisation und Transfer: Fremdschlüssel und Geschäftsregeln umsetzen (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: ein vollständiges Schema entwerfen und die Grenze von CHECK beurteilen (Teil D).

Arbeitsaufträge

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

Ausgangsdaten (Ticketsystem, absichtlich ohne Constraints)

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,'Villach City Hall','Villach',5000),(2,'Vienna Concert Hall','Vienna',1200),(3,'Castle Open-Air Stage','Klagenfurt',800);
INSERT INTO events VALUES (101,1,'Rock by the Lake','2027-07-15',49.90,'ACTIVE'),(102,2,'Classical Night','2027-09-20',89.00,'ACTIVE'),(103,3,'Open-Air Cabaret','2027-08-10',29.50,'CANCELLED'),(104,2,'Jazz at the Concert Hall','2027-11-05',65.00,'ACTIVE');
INSERT INTO customers VALUES (1,'[email protected]','Max','Doe','2024-01-10 09:00'),(2,'[email protected]','Anna','Sample','2024-03-22 14:30'),(3,'[email protected]','Chris','Test','2025-01-05 11:15');
INSERT INTO orders VALUES (1001,1,101,2,99.80,'2025-02-01 10:00'),(1002,2,102,1,89.00,'2025-02-15 16:45'),(1003,1,104,3,195.00,'2025-03-10 08:30');

Teil A - Kategorien und Basis-Constraints

1. Ordnen Sie jede Situation einer Kategorie zu (Entitäts-, referenzielle, Domänen-, benutzerdefinierte Integrität): (a) Bestellung verweist auf nicht existierende event_id; (b) Event ohne Titel; (c) zwei Kunden mit gleicher customer_id; (d) Preis -15.00; (e) quantity = 0; (f) status = 'VIELLEICHT'.
2. Legen Sie customers neu an, mit customer_id als automatisch vergebenem Primärschlüssel, email NOT NULL UNIQUE, first_name/last_name NOT NULL, created_at mit Default CURRENT_TIMESTAMP. Testen Sie den doppelten E-Mail-Fall.

Teil B - Fremdschlüssel und referenzielle Aktionen

1. Legen Sie events neu an, mit einem benannten Fremdschlüssel fk_events_venues auf venues.
2. Entscheiden und begründen Sie: Was passiert beim Löschen einer venue mit noch zugeordneten Events (RESTRICT oder CASCADE)? Setzen Sie die gewählte Aktion um.

Teil C - CHECK-Constraints und nachträgliche Änderungen

1. Setzen Sie für events: Preis > 0; status IN ('ACTIVE','CANCELLED','SOLD_OUT'); Datum in der Zukunft. Für orders: quantity BETWEEN 1 AND 10; total > 0. Vergeben Sie Namen nach chk_<tabelle>_<spalte>.
2. Fügen Sie per ALTER TABLE nachträglich hinzu: venues.capacity NOT NULL und > 0; orders.quantity BETWEEN 1 AND 10. Fügen Sie danach bewusst eine ungültige Zeile ein und beobachten Sie die Fehlermeldung. Entfernen Sie den Constraint auf orders.quantity wieder.

Teil D - Expertenteil: Vollständiges Schema und die Grenze von CHECK

1. Schema von Grund auf: Entwerfen Sie alle vier Tabellen neu, mit automatischen Primärschlüsseln, benannten Fremdschlüsseln samt begründeten referenziellen Aktionen, allen NOT NULL/UNIQUE/DEFAULT und den Geschäftsregeln aus Teil C.
2. Constraint auf befüllter Tabelle: Angenommen, venues enthält bereits eine Zeile mit capacity = -50. Zeigen Sie, dass ADD CONSTRAINT ... CHECK (capacity > 0) daran scheitert, und lösen Sie es sauber: entweder die Daten korrigieren oder den Constraint mit NOT VALID hinzufügen und später mit VALIDATE CONSTRAINT prüfen. Erklären Sie den Unterschied in zwei Sätzen.
3. Grenze von CHECK - tabellenübergreifende Regel: Die Firma will erzwingen, dass orders.total immer quantity * price des zugehörigen Events entspricht. Erklären Sie in drei bis vier Sätzen, warum ein gewöhnlicher CHECK-Constraint das nicht kann (er darf nur Spalten derselben Zeile prüfen, keine andere Tabelle), und nennen Sie zwei mögliche Umsetzungen (ein Trigger, oder total gar nicht speichern und stattdessen berechnen bzw. als generierte Spalte je nach Datenlage).
4. Grenze von CHECK - zeilenübergreifende Regel: Ebenso soll die Summe der bestellten Tickets pro Event die capacity des Veranstaltungsorts nicht überschreiten. Erklären Sie, warum auch das kein CHECK leisten kann (es müsste über mehrere Zeilen aggregieren) und auf welcher Ebene diese Regel gehört.

Wissenscheck

1. Worin unterscheiden sich UNIQUE und PRIMARY KEY (zwei Punkte)?
2. Verletzt ein CHECK (discount BETWEEN 0 AND 100) den Constraint, wenn discount NULL ist?
3. Was meint ein Kollege mit „stillen Kettenreaktionen” bei ON DELETE CASCADE, und wann ist RESTRICT besser?
4. Warum kann ein CHECK-Constraint keine Regel über zwei Tabellen erzwingen?
5. Wozu dient ADD CONSTRAINT ... NOT VALID und das spätere VALIDATE CONSTRAINT?

Abgabe

• Die Datei aufgabe19_integritaet.sql mit allen CREATE/ALTER/Test-Anweisungen.
• Kurze schriftliche Antworten zur Kategorien-Zuordnung und zu den Begründungen aus Teil D.

---

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