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

1. 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).

2. 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

3. 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).

4. 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.

5. Spalte umbenennen

   • Benennen Sie die Spalte matrikelnummer in schueler_nr um.

6. Datentyp ändern

   • Ändern Sie in kurs den Datentyp von titel von VARCHAR(120) auf VARCHAR(160).

7. Spalte löschen

   • Entfernen Sie in teilnehmer die Spalte geburtsdatum.

---

Teil C - Constraints ändern und löschen

8. 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.

9. 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.

10. 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

11. 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.

12. 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:

1. 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.

2. 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