Aufgabe 19 - Datenintegrität
Aufgabe 19 - Datenintegrität
Abschnitt betitelt „Aufgabe 19 - Datenintegrität“Worum geht es?
Abschnitt betitelt „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 TABLEnachträglich hinzufügen oder entfernen. - Verstehen: das Verhalten bei Constraint-Verletzungen nachvollziehen und vermeiden.
Szenario: Ticketsystem für Veranstaltungen
Abschnitt betitelt „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
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
Abschnitt betitelt „Arbeitsaufträge“Aufgabe 1: Integritätskategorien zuordnen
Abschnitt betitelt „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
Abschnitt betitelt „Aufgabe 2: Basistabelle mit Integritätsbedingungen“Erstellen Sie die Tabelle customers neu — diesmal mit allen notwendigen Integritätsbedingungen:
customer_idist der Primärschlüssel und wird automatisch vergeben.emailmuss angegeben werden und darf nicht doppelt vorkommen.first_nameundlast_namemüssen immer ausgefüllt sein.created_atsoll 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
Abschnitt betitelt „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
venuegelöscht wird, der noch aktiveeventszugeordnet 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_ideiner 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
Abschnitt betitelt „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
statusdarf 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
Abschnitt betitelt „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.capacitydarf nichtNULLsein und muss größer als null sein.orders.quantitymuss 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
Abschnitt betitelt „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
Abschnitt betitelt „Abgabe & Kriterien“- Abgabe der Datei
aufgabe19_integritaet.sqlmit allenCREATE 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
Abschnitt betitelt „Wissens-Check“- Frage 1: Worin unterscheidet sich ein
UNIQUE-Constraint von einemPRIMARY 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 Spaltediscountden WertNULLenthält — wird der Constraint verletzt? - Frage 3: Sie haben für einen Fremdschlüssel
ON DELETE CASCADEgewählt. Ein Kollege warnt Sie vor “stillen Kettenreaktionen”. Was meint er damit, und wann wäreRESTRICTdie bessere Wahl?
HTL Villach, Schuljahr 2025-2026,
https://www.htl-villach.at