Aufgabe 11 - Schemaänderungen mit ALTER TABLE und DROP TABLE

Aufgabe 11 - Schemaänderungen mit ALTER TABLE und DROP TABLE (PostgreSQL)

Worum geht es?

In dieser Übung verändern Sie ein bestehendes Datenbankschema gezielt mit ALTER TABLE und DROP TABLE (siehe Kapitel 5 - SQL-Grundlagen). Sie fügen Spalten und Constraints hinzu, ändern sie, entfernen sie wieder und beachten dabei die Abhängigkeiten zwischen Tabellen.

In dieser Übung üben Sie:

Anlegen: ein einfaches Ausgangsschema mit CREATE TABLE erstellen.
Ändern: Spalten mit ALTER TABLE hinzufügen, umbenennen, im Typ ändern und löschen sowie Defaults/NOT NULL setzen und entfernen.
Verwalten: Constraints (PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY) hinzufügen, ändern und löschen.
Verstehen: Abhängigkeiten zwischen Tabellen und den Umgang mit DROP TABLE (Reihenfolge bzw. CASCADE) erfassen.
Dokumentieren: Änderungen Schritt für Schritt nachvollziehbar festhalten.

Benötigte Unterlagen

SQL-Editor (z. B. psql, pgAdmin, DBeaver mit VSCode oder vergleichbar),
Zugriff auf eine PostgreSQL-Instanz
eine Datei aufgabe11.sql, in der alle Befehle in sinnvoller Reihenfolge gesammelt werden.

Szenario: Kursverwaltung (einfaches Ausgangsschema)

Eine HTL verwaltet interne Workshops/Kurse für ihre Schülerinnen und Schüler. Teilnehmende können sich für Kurse anmelden. Pro Kurs kann es mehrere Anmeldungen geben.

Für diese Aufgabe wird kein ER-Diagramm benötigt. Das Ausgangsschema besteht aus drei Tabellen:

kurs (Kurse/Workshops),
teilnehmer (Personen, die teilnehmen können),
anmeldung (Zuordnung Teilnehmer ↔ Kurs).

Ausgangsschema (zu erstellen)

Erstellen Sie die folgenden Tabellen in PostgreSQL. Verwenden Sie nach Möglichkeit exakt die angegebenen Namen und Constraints.

Hinweis: Es ist erlaubt, SERIAL zu verwenden (wie unten). Alternativ kann auch GENERATED BY DEFAULT AS IDENTITY eingesetzt werden.

CREATE TABLE kurs (
  kurs_id        SERIAL PRIMARY KEY,
  titel          VARCHAR(120) NOT NULL,
  start_datum    DATE NOT NULL,
  max_teilnehmer INTEGER NOT NULL CHECK (max_teilnehmer > 0)
);

CREATE TABLE teilnehmer (
  teilnehmer_id  SERIAL PRIMARY KEY,
  matrikelnummer VARCHAR(20) NOT NULL UNIQUE,
  vorname        VARCHAR(60) NOT NULL,
  nachname       VARCHAR(60) NOT NULL,
  geburtsdatum   DATE
);

CREATE TABLE anmeldung (
  anmeldung_id   SERIAL PRIMARY KEY,
  kurs_id        INTEGER NOT NULL REFERENCES kurs(kurs_id),
  teilnehmer_id  INTEGER NOT NULL REFERENCES teilnehmer(teilnehmer_id),
  anmeldedatum   DATE NOT NULL DEFAULT CURRENT_DATE,
  status         VARCHAR(15) NOT NULL CHECK (status IN ('angemeldet', 'warteliste', 'storniert')),
  CONSTRAINT uq_anmeldung UNIQUE (kurs_id, teilnehmer_id)
);

Arbeitsaufträge

Teil A - Ausgangsschema anlegen und testen

Schema anlegen
- Legen Sie die drei Tabellen exakt wie oben beschrieben an.
- Kontrollieren Sie die Tabellen (z. B. über \d kurs, \d teilnehmer, \d anmeldung in psql oder über die Strukturansicht Ihres Tools).
Testdaten einfügen
- Fügen Sie 2 Kurse, 3 Teilnehmende und 4 Anmeldungen ein.

INSERT INTO kurs (titel, start_datum, max_teilnehmer) VALUES
  ('Web-Workshop: SQL Basics', DATE '2026-02-03', 20),
  ('Datenbanken: Constraints & Keys', DATE '2026-02-10', 16);

INSERT INTO teilnehmer (matrikelnummer, vorname, nachname, geburtsdatum) VALUES
  ('HTL-2026-0001', 'Anna',  'Moser',   DATE '2008-04-12'),
  ('HTL-2026-0002', 'Lukas', 'Berger',  DATE '2007-11-03'),
  ('HTL-2026-0003', 'Sara',  'Kovacs',  NULL);

INSERT INTO anmeldung (kurs_id, teilnehmer_id, anmeldedatum, status)
VALUES
  (
    (SELECT kurs_id FROM kurs WHERE titel = 'Web-Workshop: SQL Basics' AND start_datum = DATE '2026-02-03'),
    (SELECT teilnehmer_id FROM teilnehmer WHERE matrikelnummer = 'HTL-2026-0001'),
    DATE '2026-01-12',
    'angemeldet'
  ),
  (
    (SELECT kurs_id FROM kurs WHERE titel = 'Web-Workshop: SQL Basics' AND start_datum = DATE '2026-02-03'),
    (SELECT teilnehmer_id FROM teilnehmer WHERE matrikelnummer = 'HTL-2026-0002'),
    DATE '2026-01-13',
    'warteliste'
  ),
  (
    (SELECT kurs_id FROM kurs WHERE titel = 'Datenbanken: Constraints & Keys' AND start_datum = DATE '2026-02-10'),
    (SELECT teilnehmer_id FROM teilnehmer WHERE matrikelnummer = 'HTL-2026-0001'),
    DATE '2026-01-15',
    'angemeldet'
  ),
  (
    (SELECT kurs_id FROM kurs WHERE titel = 'Datenbanken: Constraints & Keys' AND start_datum = DATE '2026-02-10'),
    (SELECT teilnehmer_id FROM teilnehmer WHERE matrikelnummer = 'HTL-2026-0003'),
    DATE '2026-01-16',
    'storniert'
  );

Fügen Sie jeweils mindestens einen weiteren Datensatz pro Tabelle hinzu.

Überprüfen Sie mit SELECT-Abfragen, ob die Daten korrekt eingefügt wurden:

SELECT * FROM kurs;
SELECT * FROM teilnehmer;
SELECT * FROM anmeldung;

Testen Sie bewusst auch mindestens einen fehlerhaften Insert, um zu sehen, ob Constraints greifen (z. B. negativer max_teilnehmer, ungültiger status, doppelte Anmeldung).

Ab hier sollen alle Änderungen ausschließlich über ALTER TABLE und DROP TABLE erfolgen (kein Neu-Anlegen der Tabellen, außer es wird explizit verlangt).

Teil B - Spaltenänderungen mit ALTER TABLE

Spalte hinzufügen
- Ergänzen Sie in Tabelle teilnehmer eine neue Spalte email vom Typ VARCHAR(120).
- Setzen Sie einen sinnvollen Default oder lassen Sie den Wert zunächst NULL-fähig, damit bestehende Datensätze nicht scheitern.
- Ergänzen Sie anschließend eine Eindeutigkeit für email (UNIQUE-Constraint).
NOT NULL korrekt setzen
- Tragen Sie für alle bestehenden Teilnehmenden eine email ein (per UPDATE).

Beispiel:

UPDATE teilnehmer SET email = '[email protected]' WHERE matrikelnummer = 'HTL-2026-0001';

Setzen Sie danach email auf NOT NULL.

Spalte umbenennen
- Benennen Sie die Spalte matrikelnummer in schueler_nr um.
Datentyp ändern
- Ändern Sie in kurs den Datentyp von titel von VARCHAR(120) auf VARCHAR(160).
Spalte löschen
- Entfernen Sie in teilnehmer die Spalte geburtsdatum.

Teil C - Constraints ändern und löschen

CHECK-Constraint anpassen (ändern durch Drop + Add)
- Erweitern Sie die erlaubten Werte für anmeldung.status um den Status abgeschlossen.
- Da ein CHECK-Constraint in PostgreSQL üblicherweise nicht “editierbar” ist, soll folgender Ablauf eingehalten werden:
  1. bestehenden CHECK-Constraint identifizieren (Name),
  2. DROP CONSTRAINT,
  3. neuen CHECK-Constraint mit der erweiterten Liste hinzufügen.
FOREIGN KEY ändern (ON DELETE-Regel)
- Passen Sie die Fremdschlüssel in anmeldung so an, dass beim Löschen eines Kurses oder Teilnehmenden die zugehörigen Anmeldungen automatisch mitgelöscht werden (ON DELETE CASCADE).
- Auch hier gilt: Fremdschlüssel-Constraints müssen dazu in der Regel zuerst gelöscht und neu angelegt werden.
Constraint löschen

Entfernen Sie den Constraint uq_anmeldung (die Eindeutigkeit auf (kurs_id, teilnehmer_id)).
Demonstrieren Sie anschließend mit einem Insert, dass nun doppelte Anmeldungen technisch möglich wären.

Teil D - DROP TABLE und Abhängigkeiten

DROP TABLE in korrekter Reihenfolge

Löschen Sie die Tabellen so, dass keine Fehler aufgrund von Abhängigkeiten auftreten.
Dokumentieren Sie kurz, warum die Reihenfolge relevant ist.

DROP TABLE mit CASCADE

Stellen Sie das Schema (Tabellen + wenige Testdaten) erneut her (verwenden Sie dazu die Befehle aus Teil A).
Löschen Sie anschließend nur die Tabelle kurs mit DROP TABLE ... CASCADE.
Dokumentieren Sie, welche abhängigen Objekte dadurch ebenfalls entfernt wurden.

Abgabe

Geben Sie folgende Dateien ab:

SQL-Datei aufgabe11.sql
- enthält alle Befehle in der Reihenfolge, in der Sie sie ausgeführt haben,
- mit kurzen Kommentaren zu jedem Schritt (z. B. -- Teil B.3: Spalte email hinzufügen),
- inkl. Test-INSERT/UPDATE/SELECT, die zeigen, dass die Änderung wirkt.
Kurzes Protokoll (PDF oder Word, max. 1 Seite)
- Welche Änderungen waren besonders fehleranfällig (und warum)?
- Welche Constraints mussten Sie zuerst löschen und anschließend neu anlegen?
- Worin liegt der praktische Unterschied zwischen „Reihenfolge richtig wählen“ und DROP ... CASCADE?

Wissens-Check

Frage 1: Warum muss man einen CHECK- oder FOREIGN KEY-Constraint in PostgreSQL meist löschen und neu anlegen, statt ihn direkt zu “editieren”?
Frage 2: Was bewirkt ON DELETE CASCADE bei einem Fremdschlüssel?
Frage 3: Warum ist die Reihenfolge beim DROP TABLE mehrerer verknüpfter Tabellen wichtig, und wie umgeht CASCADE dieses Problem?

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