Übungsaufgaben zum Anlegen und Ändern von Tabellen (SQL-DDL)

Übungsaufgaben zum Anlegen und Ändern von Tabellen (SQL-DDL)

https://bildung.social/@oerinformatik/

https://oer-informatik.de/sql-ddl-uebungsaufgaben

SQL, DDL, CREATE TABLE, ALTER TABLE, Constraints, Fremdschlüssel, Index, MariaDB, Übungsaufgaben, Prüfungsvorbereitung

tl/dr; (Bearbeitungszeit insgesamt 140 min, mehrere Aufgaben): Vier Übungsaufgaben zur SQL-DDL: Tabellen mit Datentypen, Primär- und Fremdschlüsseln, NOT NULL, UNIQUE, DEFAULT und CHECK anlegen, bestehende Tabellen mit ALTER TABLE umbauen, Indizes setzen, Tabellen archivieren und löschen, fehlerhafte CREATE-TABLE-Anweisungen korrigieren sowie in einer Mitfahr-App einen Datentyp-Entwurf auswerten, eine Tabelle nachträglich per Fremdschlüssel einbinden und Zählwerte mit Constraints absichern und über eine View auswerten. Alle Lösungen sind ausgeblendet und mit Punkteverteilung versehen [AI-Assisted]. (Zuletzt geändert am 28.10.2026)

Dieser Artikel ist Teil einer Artikelreihe über die SQL-DDL: Grundlagen der DDL / Datentypen / Constraints / Übungsaufgaben zur DDL

Hinweise zur Bearbeitung: Als FIA, FISY, FIDV, ISE oder IKS musst Du zur Teil-2-Prüfung Aufgaben auf Kernkompetenz-Ebene lösen können, das sind Aufgabe 2.1, Aufgabe 2.2 und Aufgabe 2.3. Von IKS wird zusätzlich erwartet, dass ihnen Aufgaben auf Profilkompetenz-Ebene (Aufgabe 3.1) keine Probleme bereiten. Die Teilaufgaben einer Aufgabe bauen aufeinander auf. Schreib Deine Lösung zuerst vollständig auf, bevor Du die Beispiellösung aufklappst. Die Beispiellösungen stehen in Tabs für MariaDB/MySQL (voreingestellt), MS SQL Server, PostgreSQL, Oracle (ab Version 23ai) und SQLite; Fehlernummern und Erläuterungen beziehen sich auf MariaDB. Andere korrekte Lösungen (z. B. Constraints inline statt als eigene Zeile, andere sinnvolle Längen bei VARCHAR, andere Constraint-Namen) sind ebenfalls richtig. Für die Aufgaben 2.2 und 2.3 legt ein Testdaten-Skript für MariaDB den Ausgangszustand an; die Ergebnisse in den Beispiellösungen beziehen sich auf diese Daten. „Heute“ ist in allen Aufgaben der 19.10.2026. Am Ende findest Du eine SQL-Kurzreferenz – Teilaufgaben, die darauf verweisen, setzen Funktionen voraus, die Du nicht auswendig kennen musst.

Du möchtest eigene Aufgaben zum Üben von einem KI-Sprachmodell generieren lassen?

Kopiere den Text in ein KI-Sprachmodell Deiner Wahl und passe Kompetenzstufe, Anzahl, Bearbeitungszeit und Szenario bei Bedarf an. Wie die Aufgaben aufgebaut sind, beschreibt der Artikel Wie sind die Aufgaben aufgebaut?. Prüfe die Ergebnisse mit dem Lehrartikel: KI-Sprachmodelle machen Fehler.

Erstelle neue Übungsaufgaben zum Thema „Anlegen und Ändern von Tabellen
(SQL-DDL)“.

Bauplan: Halte Dich an Aufbau, Kompetenzstufen, Operatoren,
Anforderungsbereiche, Punkte und Zeitschätzung, die im Artikel
https://oer-informatik.de/aufgaben-aufbau beschrieben sind.

Fachliche Grundlage:
- https://oer-informatik.de/sql-ddl-basics
- https://oer-informatik.de/sql-ddl-datentypen
- https://oer-informatik.de/sql-ddl-constraint
- https://oer-informatik.de/relationenmodell
- Stilbeispiel: https://oer-informatik.de/sql-ddl-uebungsaufgaben

Die Aufgaben sollen Inhalte zu diesen Schlagwörtern abdecken:
- SQL
- DDL
- CREATE TABLE
- ALTER TABLE
- Constraints
- Fremdschlüssel
- Index
- MariaDB

Kompetenzstufe: Kernkompetenz (alternativ: Basiskompetenz, Profilkompetenz, Expertise)
Anzahl: 1 Aufgabe mit Teilaufgaben
Bearbeitungszeit in Minuten ca.: <5 bis 30 min>
Szenario: eines der Szenarien aus dem Bauplan

Vorgehen:
1. Lies zuerst die verlinkten Artikel. Kannst Du keine Webseiten
   abrufen, sag mir das, statt zu raten.
2. Steht bei einer Angabe noch ein Platzhalter in <…>, frag mich
   zuerst danach. Richte Punkte und Zahl der Teilaufgaben nach der
   Bearbeitungszeit (ca. 0,9 min je Punkt).
3. Gib je Aufgabe Titel mit Kompetenzstufe, Ausgangssituation und
   Teilaufgaben mit Operator und Punktzahl aus, dazu die geschätzte
   Bearbeitungszeit.
4. Gib die Beispiellösungen mit Punkteverteilung erst aus, wenn ich
   danach frage.

Aufgabe 2.1: Die Werkstattdatenbank eines Fahrradshops anlegen (Kernkompetenz)

(ca. 32 min Bearbeitungszeit)

Du bist Auszubildende*r im Entwicklungsteam der CSB-Rad GmbH in Bielefeld. Ab Januar 2027 nimmt die Werkstatt in Sennestadt Inspektionen und Reparaturen über das Shopsystem an. Das Datenmodell ist mit Werkstattleiter Bastian Ludwig abgestimmt (Primärschlüssel unterstrichen, Fremdschlüssel mit # gekennzeichnet):

  • mitarbeiter (mitarbeiter_id, vorname, nachname)

  • leistungsart (leistungsart_id, bezeichnung, stundensatz)

  • fahrradmodell (modell_id, hersteller, bezeichnung)

  • fahrrad (fahrrad_id, rahmennummer, farbe, #modell_id, #kunde_id)

  • werkstattauftrag (auftrag_id, auftragsnr, annahme_am, fertig_bis, km_stand, #fahrrad_id, #annahme_mitarbeiter_id)

  • arbeitsposition (#auftrag_id, pos_nr, beschreibung, #leistungsart_id, #mitarbeiter_id, stunden, stundensatz)

  • materialposition (#auftrag_id, #artikel_id, menge, einzelpreis)

Die Tabellen kunde (kunde_id INT UNSIGNED) und artikel (artikel_id INT UNSIGNED) gibt es bereits in der Shop-Datenbank. Alle Primärschlüssel sollen – soweit nicht anders angegeben – vom System fortlaufend vergeben werden. Ein Beispielauftrag aus der Werkstatt:

WERKSTATTAUFTRAG W-2026-0147          Annahme: 14.10.2026, 08:40 Uhr (Bastian Ludwig)
Fertig bis: 16.10.2026                Kilometerstand: 1.412 km
Fahrrad: E-Bike „Kanal“ Mittelmotor 500 Wh, moosgrün, Rahmennummer CSB2608EB0213

Pos  Leistung                          Leistungsart  ausgeführt von  Std.  €/Std.
1    Erstinspektion E-Bike             Inspektion    Selin Arslan    1,50   68,00
2    Bremsbeläge und Kette erneuert    Reparatur     Selin Arslan    0,50   68,00
3    Software-Update Motorsteuerung    Elektronik    Paul Neubauer   0,25   78,00

a) Erstelle die SQL-Anweisung, mit der die Tabelle leistungsart angelegt wird. Jede Bezeichnung (höchstens 30 Zeichen) muss angegeben werden und darf nur einmal vorkommen. Der Stundensatz muss angegeben werden, ist centgenau, größer als 0 € und kleiner als 1.000 €. (5 Punkte)

 
CREATE TABLE leistungsart (
  leistungsart_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  bezeichnung     VARCHAR(30)  NOT NULL UNIQUE,
  stundensatz     DECIMAL(5,2) NOT NULL,
  CONSTRAINT chk_la_satz CHECK (stundensatz > 0)
);

Bewertung: Primärschlüssel mit AUTO_INCREMENT 1 Punkt, bezeichnung mit VARCHAR(30) und NOT NULL 1 Punkt, UNIQUE 1 Punkt, DECIMAL(5,2) mit NOT NULL 1 Punkt, CHECK 1 Punkt.

Worauf es ankommt:

  • DECIMAL(5,2) speichert fünf Stellen, davon zwei Nachkommastellen – also höchstens 999,99 €. Damit ist die Obergrenze schon durch den Datentyp gesichert; CHECK (stundensatz > 0 AND stundensatz < 1000) ist ebenfalls richtig.

  • Ein Versuch, „Inspektion“ ein zweites Mal einzutragen, scheitert an UNIQUE (MariaDB-Fehler 1062 „Duplicate entry“), ein Stundensatz von 0 an der CHECK-Bedingung (Fehler 4025 „CONSTRAINT chk_la_satz failed“).

  • CHECK-Bedingungen werden von MariaDB seit Version 10.2 geprüft; ältere MySQL-Versionen (vor 8.0.16) haben sie zwar akzeptiert, aber ignoriert.

b) Dein Mit-Azubi hat für einige Spalten folgende Datentypen vorgeschlagen. Nimm zu jedem Vorschlag Stellung und gib, wenn nötig, einen besseren Datentyp an. (4 Punkte)

Vorschläge für Datentypen
Spalte Vorschlag
arbeitsposition.stundensatz FLOAT
werkstattauftrag.auftragsnr (z. B. W-2026-0147) INT
werkstattauftrag.km_stand SMALLINT UNSIGNED
arbeitsposition.pos_nr TINYINT UNSIGNED
Stellungnahme zu den Datentypen (Beispiellösung)
Spalte Vorschlag Stellungnahme
stundensatz FLOAT ungeeignet: Gleitkommazahlen speichern Dezimalbrüche wie 0,10 nur näherungsweise, bei Summen entstehen Rundungsfehler. Geldbeträge als Festkommazahl DECIMAL(5,2) speichern.
auftragsnr INT ungeeignet: Die Nummer enthält Buchstaben und Bindestriche, die führende Null in 0147 ginge verloren, gerechnet wird damit nicht. Besser CHAR(11) (feste Länge) oder VARCHAR(15).
km_stand SMALLINT UNSIGNED zu klein: Der Wertebereich endet bei 65.535 km; E-Bikes im Pendel- oder Kurierbetrieb erreichen das. Besser MEDIUMINT UNSIGNED (bis 16.777.215) oder INT UNSIGNED.
pos_nr TINYINT UNSIGNED geeignet: 0 bis 255 Positionen je Auftrag reichen; der Typ belegt nur 1 Byte.

Je Zeile 1 Punkt (Urteil mit Begründung).

c) Formuliere die SQL-Anweisung für die Tabelle fahrrad. Die Rahmennummer (bis zu 20 Zeichen) ist bei Rädern anderer Hersteller manchmal unleserlich und darf dann fehlen; wenn sie angegeben ist, darf sie aber nur bei einem Fahrrad vorkommen. Modell und Kund*in müssen immer angegeben werden. Vergib für alle Constraints sprechende Namen. (5 Punkte)

 
CREATE TABLE fahrrad (
  fahrrad_id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  rahmennummer VARCHAR(20)  NULL,
  farbe        VARCHAR(30)  NULL,
  modell_id    INT UNSIGNED NOT NULL,
  kunde_id     INT UNSIGNED NOT NULL,
  CONSTRAINT pk_fahrrad PRIMARY KEY (fahrrad_id),
  CONSTRAINT uq_fahrrad_rahmennr UNIQUE (rahmennummer),
  CONSTRAINT fk_fahrrad_modell FOREIGN KEY (modell_id) REFERENCES fahrradmodell (modell_id),
  CONSTRAINT fk_fahrrad_kunde  FOREIGN KEY (kunde_id)  REFERENCES kunde (kunde_id)
);

Bewertung: Spalten mit passenden Datentypen 1 Punkt, rahmennummer mit NULL und benanntem UNIQUE 1 Punkt, NOT NULL bei beiden Fremdschlüsseln 1 Punkt, je benanntem Fremdschlüssel 1 Punkt.

Worauf es ankommt:

  • UNIQUE erlaubt in MariaDB beliebig viele NULL-Werte – zwei Räder ohne lesbare Rahmennummer sind also kein Widerspruch. Das ist der Unterschied zum Primärschlüssel, der nie NULL sein darf.

  • Die Fremdschlüsselspalten müssen denselben Datentyp wie die referenzierten Primärschlüssel haben, einschließlich UNSIGNED. Sonst lehnt MariaDB die Tabelle ab (Fehler 1005, „errno: 150 Foreign key constraint is incorrectly formed“).

  • MariaDB benennt Primärschlüssel immer PRIMARY; der Name pk_fahrrad wird akzeptiert, aber ignoriert. Benannte Constraints sind vor allem für UNIQUE, CHECK und FOREIGN KEY nützlich: Fehlermeldungen nennen den Namen, und man kann die Bedingung später gezielt löschen.

d) Erstelle die SQL-Anweisung für die Tabelle werkstattauftrag. Die Auftragsnummer ist eindeutig und Pflicht. Wird beim Anlegen kein Annahmezeitpunkt angegeben, soll der aktuelle Zeitpunkt gespeichert werden. Der Termin „fertig bis“ ist anfangs manchmal noch offen, darf aber nicht vor dem Tag der Annahme liegen. Wer den Auftrag angenommen hat, muss gespeichert werden. (6 Punkte)

 
CREATE TABLE werkstattauftrag (
  auftrag_id             INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  auftragsnr             CHAR(11)     NOT NULL UNIQUE,
  annahme_am             DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  fertig_bis             DATE         NULL,
  km_stand               INT UNSIGNED NULL,
  fahrrad_id             INT UNSIGNED NOT NULL,
  annahme_mitarbeiter_id INT UNSIGNED NOT NULL,
  CONSTRAINT chk_wa_fertig CHECK (fertig_bis >= DATE(annahme_am)),
  CONSTRAINT fk_wa_fahrrad     FOREIGN KEY (fahrrad_id) REFERENCES fahrrad (fahrrad_id),
  CONSTRAINT fk_wa_mitarbeiter FOREIGN KEY (annahme_mitarbeiter_id) REFERENCES mitarbeiter (mitarbeiter_id)
);

Bewertung: auftragsnr mit NOT NULL UNIQUE 1 Punkt, DEFAULT CURRENT_TIMESTAMP 1 Punkt, fertig_bis als DATE NULL 1 Punkt, CHECK 1 Punkt, je Fremdschlüssel 1 Punkt.

Worauf es ankommt:

  • Ist fertig_bis NULL, ist die Bedingung „unbekannt“ – das gilt bei CHECK als erfüllt. Der offene Termin ist also erlaubt, ein Termin vor dem Annahmetag (z. B. Annahme 14.10., fertig bis 13.10.) wird abgelehnt.

  • Der Fremdschlüssel auf die Mitarbeitenden heißt annahme_mitarbeiter_id, weil mitarbeiter auch in arbeitsposition (Ausführung) vorkommt. Der Name der Fremdschlüsselspalte muss nicht mit dem des Primärschlüssels übereinstimmen – entscheidend ist REFERENCES mitarbeiter (mitarbeiter_id).

  • Der Datentyp DATETIME (Datum und Uhrzeit) passt zur Annahme um 08:40 Uhr, DATE reicht für den Fertigstellungstermin. Datumswerte werden in MariaDB im Format '2026-10-16' geschrieben, nicht '16.10.2026'.

e) Für die Tabelle arbeitsposition gilt: Die Positionsnummer beginnt in jedem Auftrag wieder bei 1. Der Stundensatz wird aus der Leistungsart übernommen und muss gespeichert werden, weil er sich später ändern kann. Es werden mindestens Viertelstunden abgerechnet, die Stundenzahl muss also größer als 0 sein.

ea) Erstelle die SQL-Anweisung für die Tabelle. Wird ein Werkstattauftrag gelöscht, sollen seine Arbeitspositionen automatisch mitgelöscht werden; Leistungsarten und Mitarbeitende, die in Positionen vorkommen, dürfen dagegen nicht gelöscht werden können. (6 Punkte)

eb) Begründe, warum für die Fremdschlüssel unterschiedliche Regeln beim Löschen sinnvoll sind. (2 Punkte)

ea)

 
CREATE TABLE arbeitsposition (
  auftrag_id      INT UNSIGNED     NOT NULL,
  pos_nr          TINYINT UNSIGNED NOT NULL,
  beschreibung    VARCHAR(200)     NOT NULL,
  leistungsart_id INT UNSIGNED     NOT NULL,
  mitarbeiter_id  INT UNSIGNED     NOT NULL,
  stunden         DECIMAL(4,2)     NOT NULL,
  stundensatz     DECIMAL(5,2)     NOT NULL,
  PRIMARY KEY (auftrag_id, pos_nr),
  CONSTRAINT chk_ap_stunden CHECK (stunden > 0),
  CONSTRAINT fk_ap_auftrag      FOREIGN KEY (auftrag_id)      REFERENCES werkstattauftrag (auftrag_id) ON DELETE CASCADE,
  CONSTRAINT fk_ap_leistungsart FOREIGN KEY (leistungsart_id) REFERENCES leistungsart (leistungsart_id),
  CONSTRAINT fk_ap_mitarbeiter  FOREIGN KEY (mitarbeiter_id)  REFERENCES mitarbeiter (mitarbeiter_id)
);

Bewertung: zusammengesetzter Primärschlüssel 2 Punkte, ON DELETE CASCADE beim Auftrag 1 Punkt, Fremdschlüssel auf Leistungsart und Mitarbeitende 1 Punkt, stunden und stundensatz als DECIMAL mit NOT NULL 1 Punkt, CHECK 1 Punkt.

Worauf es ankommt:

  • Der Primärschlüssel (auftrag_id, pos_nr) ist zusammengesetzt: Position 1 gibt es in jedem Auftrag. Ein Primärschlüssel nur aus pos_nr ließe genau einen Auftrag mit Position 1 zu, einer nur aus auftrag_id nur eine Position je Auftrag.

  • Ohne ON DELETE-Angabe gilt RESTRICT (in MariaDB gleichbedeutend mit NO ACTION): Eine Leistungsart, auf die noch eine Position verweist, kann nicht gelöscht werden (Fehler 1451 „Cannot delete or update a parent row“). ON DELETE RESTRICT ausdrücklich hinzuschreiben ist ebenfalls richtig.

  • Weil der Primärschlüssel mit auftrag_id beginnt, nutzt MariaDB ihn auch als Index für den Fremdschlüssel auf den Auftrag. Für die beiden anderen Fremdschlüssel legt InnoDB automatisch einen Index an.

eb) (je Argument 1 Punkt) Eine Arbeitsposition ist ohne ihren Auftrag sinnlos (schwacher Entitätstyp, Komposition): Wird ein – z. B. versehentlich angelegter – Auftrag gelöscht, sollen keine verwaisten Positionen zurückbleiben, deshalb CASCADE. Leistungsarten und Mitarbeitende sind dagegen eigenständige Stammdaten. Würden sie samt Positionen gelöscht, verschwänden abgerechnete Arbeiten aus alten Aufträgen und Rechnungen wären nicht mehr nachvollziehbar; deshalb RESTRICT. Nicht mehr angebotene Leistungsarten werden besser deaktiviert (siehe g).

f) Für ein Testsystem soll ein Skript alle sieben Werkstatt-Tabellen anlegen und am Ende des Tests wieder löschen. Gib eine Reihenfolge an, in der die Tabellen angelegt werden können, erstelle die DROP-Anweisungen für das Aufräumen in einer funktionierenden Reihenfolge und begründe die Reihenfolge. (4 Punkte)

Anlegen (1 Punkt), z. B.: mitarbeiter, leistungsart, fahrradmodell, fahrrad, werkstattauftrag, arbeitsposition, materialposition. Die ersten drei Tabellen haben keine Fremdschlüssel und können in beliebiger Reihenfolge stehen; fahrrad braucht fahrradmodell (und kunde), werkstattauftrag braucht fahrrad und mitarbeiter, die Positionen brauchen den Auftrag.

Löschen (2 Punkte), genau umgekehrt – erst die Tabellen, die auf andere verweisen:

 
DROP TABLE IF EXISTS materialposition;
DROP TABLE IF EXISTS arbeitsposition;
DROP TABLE IF EXISTS werkstattauftrag;
DROP TABLE IF EXISTS fahrrad;
DROP TABLE IF EXISTS fahrradmodell;
DROP TABLE IF EXISTS leistungsart;
DROP TABLE IF EXISTS mitarbeiter;

Begründung (1 Punkt): Ein Fremdschlüssel kann nur auf eine Tabelle verweisen, die schon existiert. Umgekehrt verweigert MariaDB das Löschen einer Tabelle, auf die noch ein Fremdschlüssel verweist (Fehler 1451) – auch dann, wenn sie leer ist. IF EXISTS verhindert, dass das Skript abbricht, wenn eine Tabelle nicht angelegt wurde.

Alternative für das Testsystem: vorher SET FOREIGN_KEY_CHECKS = 0; setzen, danach wieder = 1. Im Produktivsystem ist das Abschalten der Prüfung tabu.

g) Bastian möchte Leistungsarten, die nicht mehr angeboten werden, deaktivieren statt löschen. Ergänze die Tabelle leistungsart um eine passende Pflichtspalte aktiv und erläutere, welchen Wert die bereits gespeicherten Leistungsarten erhalten. (3 Punkte)

 
ALTER TABLE leistungsart
  ADD COLUMN aktiv BOOLEAN NOT NULL DEFAULT TRUE;

Bewertung: ALTER TABLE … ADD COLUMN 1 Punkt, Datentyp BOOLEAN mit NOT NULL 1 Punkt, Erläuterung 1 Punkt.

Erläuterung: Durch DEFAULT TRUE erhalten alle vorhandenen Datensätze beim Hinzufügen der Spalte den Wert TRUE (in MariaDB intern 1, denn BOOLEAN ist ein Alias für TINYINT(1)); Inspektion, Reparatur und Elektronik bleiben also aktiv. Ohne DEFAULT würde MariaDB bei NOT NULL den „impliziten Standardwert“ des Typs eintragen, hier 0 – alle Leistungsarten wären dann versehentlich deaktiviert.

Aufgabe 2.2: Eine Freizeit-App beschleunigen und umbauen (Kernkompetenz)

(ca. 40 min Bearbeitungszeit)

Du bist Auszubildende*r im Entwicklungsteam der Let’s Meet GmbH in Bielefeld. Die App vermittelt gemeinsame Freizeitaktivitäten; die MariaDB-Datenbank enthält inzwischen 41.800 Konten, jeden Monat kommen rund 3.100 Aktivitäten hinzu. Entwicklungsleiter Yusuf Demir hat beobachtet, dass die Startseite („Was ist los in den nächsten sieben Tagen?“) und die Suche nach barrierefreien Treffpunkten immer langsamer werden. Außerdem stehen einige Umbauten an. Die Tabellen mit Testbestand legt Teil 1 des Testdaten-Skripts an. Auszug aus den Tabellen:

CREATE TABLE benutzer (
  benutzer_id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  vorname         VARCHAR(50)  NOT NULL,
  nachname        VARCHAR(50)  NOT NULL,
  email           VARCHAR(100) NOT NULL UNIQUE,
  plz             CHAR(5)      NOT NULL,
  ort             VARCHAR(60)  NOT NULL,
  verifiziert     BOOLEAN      NOT NULL DEFAULT FALSE,
  registriert_am  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
  -- weitere Spalten …
);
CREATE TABLE treffpunkt (
  treffpunkt_id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name           VARCHAR(80)  NOT NULL,
  plz            CHAR(5)      NOT NULL,
  barrierefrei   BOOLEAN      NOT NULL DEFAULT FALSE
  -- weitere Spalten …
);
CREATE TABLE aktivitaet (
  aktivitaet_id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  titel          VARCHAR(100) NOT NULL,
  treffpunkt_id  INT UNSIGNED NOT NULL,
  beginn         DATETIME     NOT NULL,
  CONSTRAINT fk_akt_treffpunkt FOREIGN KEY (treffpunkt_id) REFERENCES treffpunkt (treffpunkt_id)
  -- weitere Spalten …
);
CREATE TABLE telefon (
  telefon_id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  benutzer_id  INT UNSIGNED NOT NULL,
  nummer       VARCHAR(25)  NOT NULL,
  typ          ENUM('mobil','festnetz') NOT NULL DEFAULT 'mobil',
  CONSTRAINT fk_telefon_benutzer FOREIGN KEY (benutzer_id) REFERENCES benutzer (benutzer_id) ON DELETE CASCADE
);

Die Startseite fragt die Aktivitäten so ab:

SELECT aktivitaet_id, titel, beginn
FROM aktivitaet
WHERE beginn >= CURDATE() AND beginn < CURDATE() + INTERVAL 7 DAY
ORDER BY beginn;

Yusuf hat sich mit EXPLAIN vor der Abfrage anzeigen lassen, wie MariaDB sie ausführt (Ausgabe gekürzt):

EXPLAIN-Ausgabe der Startseiten-Abfrage
id select_type table type possible_keys key rows Extra
1 SIMPLE aktivitaet ALL NULL NULL 98412 Using where; Using filesort

a) Erläutere anhand der Spalten type, key, rows und Extra, wie MariaDB die Abfrage zurzeit ausführt und warum die Startseite mit jedem Monat langsamer wird. (3 Punkte)

  • type = ALL und key = NULL: MariaDB nutzt keinen Index (possible_keys ist ebenfalls leer, es gibt also keinen passenden). Die Tabelle wird vollständig Zeile für Zeile gelesen (full table scan). (1 Punkt)

  • rows = 98412: Für die wenigen Aktivitäten der nächsten sieben Tage prüft MariaDB schätzungsweise alle rund 98.000 Zeilen. Weil jeden Monat rund 3.100 Aktivitäten hinzukommen, wächst dieser Aufwand ständig mit – die Antwortzeit steigt mit der Tabellengröße, obwohl das Ergebnis etwa gleich groß bleibt. (1 Punkt)

  • Extra: Using where – die Bedingung wird erst nach dem Lesen jeder Zeile geprüft; Using filesort – die gefundenen Zeilen werden anschließend in einem eigenen Schritt nach beginn sortiert. Ein Index auf der Spalte könnte beides ersetzen, weil er die Werte schon sortiert vorhält und MariaDB direkt zum ersten passenden Zeitpunkt springen kann. (1 Punkt)

b) Leite aus der Abfrage und dem Ausführungsplan einen passenden Index ab und lege ihn an. Im Team heißen Indizes nach dem Muster idx_<tabelle>_<spalte>. Beschreibe, woran Du in einer erneuten EXPLAIN-Ausgabe erkennst, dass der Index wirkt. Im Team kommt außerdem der Vorschlag auf, „sicherheitshalber“ jede Spalte von aktivitaet einzeln zu indizieren – nenne zwei Gründe, die dagegen sprechen. (5 Punkte)

 
CREATE INDEX idx_aktivitaet_beginn ON aktivitaet (beginn);

Gleichwertig: ALTER TABLE aktivitaet ADD INDEX idx_aktivitaet_beginn (beginn);

Bewertung Index (2 Punkte): Spalte beginn gewählt, weil sie in WHERE (Bereichsbedingung) und in ORDER BY steht, 1 Punkt; CREATE INDEX mit Name nach Muster und ON aktivitaet (beginn) 1 Punkt.

Erneute EXPLAIN-Ausgabe (1 Punkt): key zeigt idx_aktivitaet_beginn, type wechselt von ALL auf range, der Schätzwert in rows sinkt auf die Größenordnung der Treffer (einige Hundert statt rund 98.000), und Using filesort verschwindet, weil die Zeilen über den Index schon sortiert gelesen werden. Im Testbestand liefert die Abfrage für den 19.10.2026 die Aktivitäten 3 („Feierabend-Kick“), 4 („Sprachtandem Deutsch–Arabisch“) und 5 („Bouldern für Einsteiger*innen“); bei nur acht Zeilen liest MariaDB allerdings trotz Index oft einfach die ganze Tabelle.

Gründe gegen „alles indizieren“ (je 1 Punkt, zwei verlangt):

  • Jeder Index belegt zusätzlichen Speicher, bei großen Tabellen leicht so viel wie die Daten selbst.

  • Bei jedem INSERT, UPDATE und DELETE müssen alle betroffenen Indizes mit angepasst werden – das Eintragen der monatlich 3.100 neuen Aktivitäten wird langsamer.

  • Viele Spalten werden nie zum Filtern, Verknüpfen oder Sortieren verwendet (z. B. titel); deren Index nutzt keine Abfrage. Bei Spalten mit wenigen verschiedenen Werten liest das DBMS ohnehin lieber die ganze Tabelle.

c) Die Suche nach Treffpunkten filtert fast immer nach der Postleitzahl, oft zusätzlich nach barrierefrei = TRUE.

ca) Erstelle einen Index, der beide Suchen unterstützt, und begründe die Reihenfolge der Spalten. (2 Punkte)

cb) Dein Mit-Azubi Jonas Weber schlägt vor, auch die Spalten benutzer.email und aktivitaet.treffpunkt_id zu indizieren. Nimm Stellung. (2 Punkte)

ca)

 
CREATE INDEX idx_treffpunkt_plz_barrierefrei ON treffpunkt (plz, barrierefrei);

(1 Punkt) Begründung (1 Punkt): Ein zusammengesetzter Index ist zuerst nach der ersten Spalte sortiert, innerhalb gleicher Werte nach der zweiten – wie ein Telefonbuch nach Nachname, dann Vorname. Er hilft deshalb bei Bedingungen auf die erste Spalte allein und auf beide Spalten (leftmost prefix), nicht aber bei einer Suche nur nach der zweiten Spalte. Da plz immer gefiltert wird, gehört sie nach vorn; ein Index auf barrierefrei allein wäre wegen der nur zwei Werte kaum nützlich.

cb) (je Spalte 1 Punkt) Beide Indizes sind überflüssig: Für email gibt es durch UNIQUE bereits einen eindeutigen Index. Für die Fremdschlüsselspalte treffpunkt_id legt InnoDB beim Anlegen des Fremdschlüssels automatisch einen Index an, falls noch keiner existiert. Ein zweiter Index auf dieselbe Spalte kostet nur Speicher und Schreibzeit. Welche Indizes vorhanden sind, zeigt SHOW INDEX FROM benutzer;.

d) In der Tabelle telefon soll dieselbe Nummer bei einem Konto nicht doppelt gespeichert werden können. Jonas möchte dazu die Spalte nummer als UNIQUE kennzeichnen. Erstelle eine bessere SQL-Anweisung und begründe Deine Lösung. Erläutere außerdem, was geschieht, wenn beim Anlegen bereits doppelte Einträge vorhanden sind. (3 Punkte)

 
ALTER TABLE telefon
  ADD CONSTRAINT uq_telefon_konto_nummer UNIQUE (benutzer_id, nummer);

Gleichwertig: CREATE UNIQUE INDEX uq_telefon_konto_nummer ON telefon (benutzer_id, nummer); (1 Punkt)

Begründung (1 Punkt): Ein UNIQUE nur auf nummer würde verbieten, dass zwei Konten dieselbe Nummer angeben – etwa Mitglieder eines Haushalts mit gemeinsamem Festnetzanschluss. Gefordert ist die Eindeutigkeit je Konto, also über die Kombination beider Spalten.

Duplikate (1 Punkt): MariaDB prüft beim Anlegen alle vorhandenen Zeilen. Gibt es schon ein doppeltes Paar, bricht die Anweisung mit Fehler 1062 („Duplicate entry … for key …“) ab und der Index wird nicht angelegt. Die Duplikate müssen vorher gesucht (GROUP BY benutzer_id, nummer HAVING COUNT(*) > 1) und bereinigt werden. Im aktuellen Bestand gibt es keine, die Anweisung läuft also durch.

e) Let’s Meet bucht für einige Aktivitäten Kursräume in einem Stadtteilzentrum. Dessen Raumliste liegt in der Tabelle raum_import; die Spalte raum VARCHAR(20) enthält Gebäudebuchstabe und Raumnummer, getrennt durch einen Bindestrich:

raum_import (vollständig)
zeile raum plaetze
1 B-204 24
2 A-012 16
3 A-105 30
4 C-001 12
5 Saal 120
6 B-110 20

Jonas sollte raum in die Spalten gebaeude und raumnummer aufteilen. Er hat folgendes Skript geschrieben und in seinem SQL-Client am Stück ausführen lassen; der Client meldete beim UPDATE einen Fehler und arbeitete danach einfach weiter:

ALTER TABLE raum_import
  ADD COLUMN gebaeude   CHAR(1)  NOT NULL,
  ADD COLUMN raumnummer SMALLINT NOT NULL;

UPDATE raum_import
SET gebaeude   = SUBSTRING_INDEX(raum, '-', -1),
    raumnummer = SUBSTRING_INDEX(raum, '-', 1);

ALTER TABLE raum_import DROP COLUMN raum;

Zum Glück gibt es eine Sicherung: Teil 1 des Testdaten-Skripts stellt den Ausgangszustand wieder her. Nutze die SQL-Kurzreferenz in der Anlage.

ea) Beschreibe, was die drei Anweisungen nacheinander bewirken und in welchem Zustand die Tabelle raum_import nach dem Skript ist. (3 Punkte)

eb) Selbst wenn das UPDATE fehlerfrei durchliefe, wäre das Ergebnis nicht brauchbar. Benenne drei weitere Schwachstellen des Skripts. (3 Punkte)

ec) Erstelle ein verbessertes Skript, das die Aufteilung zuverlässig erledigt und vor dem Entfernen der alten Spalte prüft, ob alle Zeilen aufgeteilt werden konnten. (4 Punkte)

ea) (je Anweisung 1 Punkt)

  1. ALTER TABLE … ADD läuft durch. Weil kein DEFAULT angegeben ist, füllt MariaDB die neuen Pflichtspalten in den sechs vorhandenen Zeilen mit dem impliziten Standardwert: gebaeude = '' (Leerstring), raumnummer = 0.

  2. Das UPDATE scheitert schon in Zeile 1: Die Argumente von SUBSTRING_INDEX sind vertauscht, gebaeude soll '204' aufnehmen, passt aber nur ein Zeichen. Im strikten Modus bricht MariaDB mit Fehler 1406 („Data too long for column ‘gebaeude’ at row 1“) ab; die Anweisung wird als Ganzes zurückgenommen, keine Zeile ist geändert.

  3. Das DROP COLUMN wird trotzdem ausgeführt und lässt sich als DDL-Anweisung nicht mit ROLLBACK zurücknehmen. Danach enthält raum_import nur noch zeile, plaetze, gebaeude = '' und raumnummer = 0 – die Raumbezeichnungen sind verloren.

eb) (je Schwachstelle 1 Punkt, drei verlangt)

  • raumnummer als SMALLINT: Führende Nullen gehen verloren (A-012 → 12, C-001 → 1), obwohl sie zur Bezeichnung gehören (z. B. Erdgeschoss). Mit der Nummer wird nicht gerechnet – passend ist ein Text wie VARCHAR(5).

  • Der Sonderfall Saal ohne Bindestrich wird nicht behandelt: SUBSTRING_INDEX liefert dann beide Male den ganzen Text, und beide Spalten bekämen Unsinn.

  • NOT NULL ohne Standardwert gleich beim Hinzufügen: Fehlende Werte werden durch '' und 0 verdeckt, statt als NULL auffindbar zu bleiben; für den Saal gäbe es zudem keinen erlaubten „unbekannt“-Wert.

  • Vor dem DROP COLUMN wird nichts geprüft und keine Sicherung angelegt; ein Fehler in den Schritten davor führt direkt zu Datenverlust.

ec)

 
ALTER TABLE raum_import
  ADD COLUMN gebaeude   CHAR(1)    NULL,
  ADD COLUMN raumnummer VARCHAR(5) NULL;

UPDATE raum_import
SET gebaeude   = CASE WHEN raum LIKE '_-%' THEN LEFT(raum, 1) END,
    raumnummer = CASE WHEN raum LIKE '_-%' THEN SUBSTRING(raum, 3) END;

-- vor dem Löschen: Zeilen suchen, die nicht aufgeteilt werden konnten
SELECT zeile, raum, gebaeude, raumnummer
FROM raum_import
WHERE gebaeude IS NULL OR raumnummer IS NULL;

-- erst wenn alle Sonderfälle geklärt sind (und eine Sicherung existiert):
ALTER TABLE raum_import DROP COLUMN raum;

Ergebnis vor dem DROP COLUMN:

raum_import nach dem Aufteilen (Auszug)
zeile raum gebaeude raumnummer
1 B-204 B 204
2 A-012 A 012
4 C-001 C 001
5 Saal NULL NULL

Die Prüfabfrage liefert nur Zeile 5. Für den Saal klärst Du mit dem Stadtteilzentrum, in welchem Gebäude er liegt, und trägst ihn per UPDATE nach (oder lässt ihn bewusst ohne Raumnummer) – erst danach wird raum entfernt.

Bewertung: neue Spalten mit Textdatentyp und zunächst ohne NOT NULL 1 Punkt, Gebäude und Raumnummer richtig herausgelöst 1 Punkt, Sonderfall ohne Bindestrich abgefangen 1 Punkt, Prüfabfrage vor dem DROP COLUMN 1 Punkt. Statt LEFT/SUBSTRING ist auch SUBSTRING_INDEX(raum, '-', 1) bzw. SUBSTRING_INDEX(raum, '-', -1) richtig, wenn der Sonderfall abgefangen wird.

f) Die App soll Teilnehmende künftig mit einem Anzeigenamen wie „Sabine H.“ begrüßen: Vorname, Leerzeichen, erster Buchstabe des Nachnamens, Punkt. Im Team gibt es drei Vorschläge für die neue Spalte anzeigename in benutzer:

  • Vorschlag A (Jonas): Spalte gleich als VARCHAR(55) NOT NULL DEFAULT '' hinzufügen und dann mit einem UPDATE für alle Konten füllen. Bei neuen Konten setzt die App den Anzeigenamen.

  • Vorschlag B (Yusuf): Spalte zunächst ohne NOT NULL hinzufügen, mit einem UPDATE füllen und danach per MODIFY zum Pflichtfeld machen. Neue Konten wie bei A.

  • Vorschlag C: eine generierte Spalte, die MariaDB aus vorname und nachname berechnet.

fa) Vergleiche die drei Vorschläge: Was steht nach dem Umbau in den 13 vorhandenen Konten, was geschieht, wenn die App bei einem neuen Konto den Anzeigenamen vergisst, und was passiert, wenn jemand den Nachnamen ändert? Empfiehl einen Vorschlag und gib die SQL-Anweisung(en) dafür an. (6 Punkte)

fb) Yusuf überlegt, anzeigename zusätzlich als UNIQUE zu kennzeichnen, „damit es keine Verwechslungen gibt“. Im aktuellen Bestand gäbe es keinen Konflikt. Nimm Stellung. (2 Punkte)

fa) Vergleich (je Vorschlag 1 Punkt):

A: NOT NULL DEFAULT '' B: erst füllen, dann MODIFY C: generierte Spalte
vorhandene Konten nach dem UPDATE „Sabine H.“ … „Deniz Ç.“ wie A sofort „Sabine H.“ … „Deniz Ç.“, ohne UPDATE
App vergisst den Wert '' wird gespeichert – formal Pflicht, inhaltlich leer Fehler 1364 („Field ‘anzeigename’ doesn’t have a default value“), das Konto wird nicht angelegt kann nicht passieren, die Datenbank berechnet den Wert
Nachname ändert sich alter Anzeigename bleibt stehen (redundant, veraltet) wie A Anzeigename passt sich automatisch an

Empfehlung (1 Punkt): Vorschlag C, weil der Anzeigename vollständig aus anderen Spalten derselben Zeile folgt und nie veralten kann. Soll der Anzeigename später frei wählbar sein, ist B die richtige Wahl: Dann ist er kein abgeleiteter Wert mehr, und die Pflicht wird ohne leeren Standardwert durchgesetzt.

SQL für C (2 Punkte):

 
ALTER TABLE benutzer
  ADD COLUMN anzeigename VARCHAR(55)
    AS (CONCAT(vorname, ' ', LEFT(nachname, 1), '.')) VIRTUAL;

Wer B empfiehlt, gibt diese drei Anweisungen an (ebenfalls 2 Punkte):

ALTER TABLE benutzer ADD COLUMN anzeigename VARCHAR(55) NULL;
UPDATE benutzer SET anzeigename = CONCAT(vorname, ' ', LEFT(nachname, 1), '.');
ALTER TABLE benutzer MODIFY COLUMN anzeigename VARCHAR(55) NOT NULL;

Worauf es ankommt:

  • Bei B ist die Reihenfolge entscheidend: Solange Zeilen ohne Wert existieren, bricht MODIFY … NOT NULL im strikten Modus mit Fehler 1138 („Invalid use of NULL value“) ab. Bei MODIFY wird die komplette Spaltendefinition angegeben.

  • In eine generierte Spalte kann niemand selbst schreiben; ein INSERT oder UPDATE, das anzeigename setzen will, wird abgewiesen. VIRTUAL belegt keinen Speicher, STORED lohnt sich, wenn oft nach dem Anzeigenamen gesucht wird.

  • VARCHAR(55), weil vorname bis zu 50 Zeichen lang sein darf und noch Leerzeichen, Initiale und Punkt dazukommen.

fb) (2 Punkte) Davon ist abzuraten. Der Anzeigename ist aus Vorname und Initiale gebildet und naturgemäß nicht eindeutig: Bei 41.800 Konten gibt es viele „Sabine H.“ oder „Malik H.“. Mit UNIQUE würde schon die Registrierung des zweiten Kontos dieses Namens mit Fehler 1062 abbrechen. Verwechslungen vermeidet man über den Primärschlüssel und z. B. ein Profilbild, nicht über den Namen. (Wer für frei wählbare Nutzernamen argumentiert, die die Person selbst festlegt, darf UNIQUE dafür befürworten.)

g) Datenschutzbeauftragte Dr. Marta Kowalczyk verlangt, dass jede Verifizierung mit Verfahren, Zeitpunkt und Ergebnis dokumentiert wird. Bisher gibt es nur die Spalte benutzer.verifiziert. Für ein Konto können mehrere Versuche gespeichert werden; wird ein Konto gelöscht, sollen auch seine Verifizierungen gelöscht werden.

ga) Lege mit einer SQL-Anweisung die Tabelle verifizierung an. Als Verfahren sind „Ausweis-App“, „Video-Ident“ und „Altbestand“ möglich, als Ergebnis „bestanden“ und „abgelehnt“. (4 Punkte)

gb) Schreibe die Anweisungen, mit denen Du die bisher verifizierten Konten als „Altbestand“ mit dem Registrierungszeitpunkt als Prüfzeitpunkt in die neue Tabelle überträgst und danach die Spalte verifiziert entfernst. (4 Punkte)

gc) Jonas möchte die Anweisungen aus gb) in START TRANSACTION; … COMMIT; einschließen, „dann kann ich bei einem Fehler einfach ROLLBACK machen“. Nimm Stellung. (3 Punkte)

ga)

 
CREATE TABLE verifizierung (
  verifizierung_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  benutzer_id      INT UNSIGNED NOT NULL,
  verfahren        ENUM('Ausweis-App','Video-Ident','Altbestand') NOT NULL,
  geprueft_am      DATETIME     NOT NULL,
  ergebnis         ENUM('bestanden','abgelehnt') NOT NULL,
  CONSTRAINT fk_ver_benutzer FOREIGN KEY (benutzer_id)
    REFERENCES benutzer (benutzer_id) ON DELETE CASCADE
);

Bewertung: Primärschlüssel 1 Punkt, Fremdschlüssel mit ON DELETE CASCADE 1 Punkt, verfahren und ergebnis mit festen Werten 1 Punkt, geprueft_am DATETIME und NOT NULL 1 Punkt.

Statt ENUM sind auch VARCHAR mit CHECK (verfahren IN (…)) oder eigene Wertetabellen mit Fremdschlüssel richtig. Weil mehrere Versuche je Konto möglich sind, darf benutzer_id allein nicht Primärschlüssel sein; (benutzer_id, geprueft_am) als zusammengesetzter Schlüssel ist eine Alternative.

gb)

 
INSERT INTO verifizierung (benutzer_id, verfahren, geprueft_am, ergebnis)
SELECT benutzer_id, 'Altbestand', registriert_am, 'bestanden'
FROM benutzer
WHERE verifiziert = TRUE;

ALTER TABLE benutzer DROP COLUMN verifiziert;

Bewertung: INSERT … SELECT mit Spaltenliste 1 Punkt, feste Werte und registriert_am 1 Punkt, Bedingung 1 Punkt, DROP COLUMN erst nach dem Übertragen 1 Punkt.

Im Testbestand werden 11 Konten übertragen (alle außer Tobias Lange und Jana Meier). Ob ein Konto verifiziert ist, ergibt sich künftig aus einer Abfrage, z. B. EXISTS (SELECT 1 FROM verifizierung v WHERE v.benutzer_id = b.benutzer_id AND v.ergebnis = 'bestanden').

gc) (3 Punkte: implizites COMMIT 1 Punkt, Folge für INSERT und ROLLBACK 1 Punkt, sicheres Vorgehen 1 Punkt) Das funktioniert nur teilweise: DDL-Anweisungen wie ALTER TABLE, CREATE TABLE oder DROP TABLE lösen in MariaDB ein implizites COMMIT aus und lassen sich nicht zurücknehmen. Das ALTER TABLE … DROP COLUMN beendet die Transaktion: Das vorherige INSERT wird dabei festgeschrieben, die gelöschte Spalte ist mit ROLLBACK nicht wiederherstellbar. Sicher ist folgendes Vorgehen: vorher eine Sicherung erstellen (z. B. mariadb-dump), nach dem INSERT prüfen, ob die Anzahl übertragener Zeilen der Anzahl verifizierter Konten entspricht (hier 11), und erst dann die Spalte löschen. Eine Transaktion um das INSERT allein ist sinnvoll. In PostgreSQL, SQL Server und SQLite lassen sich DDL-Anweisungen dagegen innerhalb einer Transaktion zurücknehmen; Oracle schreibt wie MariaDB implizit fest.

Aufgabe 2.3: Schichtplanung und Aufräumen in der Barcamp-Datenbank (Kernkompetenz)

(ca. 32 min Bearbeitungszeit)

Du bist Auszubildende*r und hilfst im Orga-Team des Barcamps „Lernen.Digital.Leben 2027“ am Ratz und Rübe Berufskolleg (RRB) in Bielefeld mit. Für die rund 40 Helfenden am 13.03.2027 soll die Schichtplanung in die bestehende Barcamp-Datenbank aufgenommen werden. Das Datenmodell hat Dimitrios Papadopoulos (Gesamtleitung) bereits freigegeben:

  • aufgabenbereich (aufgabenbereich_id, bezeichnung, beschreibung, #verantwortlich_id → person)

  • schicht (schicht_id, beginn, ende, max_helfende, #veranstaltung_id, #aufgabenbereich_id, #raum_id – nur bei Raumbetreuung, sonst leer)

  • einsatz (#schicht_id, #person_id, eingetragen_am, erschienen) – eine Person kann mehrere Schichten übernehmen

  • geraeteausgabe (#geraet_id, #schicht_id, #person_id, ausgegeben_um, zurueck_um) – ein Gerät wird je Schicht an höchstens eine Person dieser Schicht ausgegeben

Die Tabellen person, veranstaltung, raum und geraet sind vorhanden; ihre Primärschlüssel haben alle den Typ INT UNSIGNED. Außerdem gibt es die Tabelle orga_aufgabe (aufgabe_id, titel, verantwortlich_id, faellig_am, status) mit sechs Aufgaben. Laut Aufsichtsregel der Schule muss jede Schicht mit mindestens zwei Personen besetzt sein.

Den Ausgangszustand mit Testdaten legt Teil 2 des Testdaten-Skripts an; Teil 3 ergänzt nach Teilaufgabe a Schichten und Einsätze, Teil 4 enthält Testanweisungen zu Teilaufgabe b.

a) Hannah Klein (Schülerin der FIA-Klasse am RRB) hat ein Skript für die ersten drei Tabellen geschrieben. Es läuft nicht und erfüllt auch die Anforderungen noch nicht vollständig:

CREATE TABLE schicht (
  schicht_id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  veranstaltung_id   INT UNSIGNED NOT NULL,
  aufgabenbereich_id INT UNSIGNED NOT NULL,
  raum_id            INT UNSIGNED NOT NULL,
  beginn             VARCHAR(5),
  ende               VARCHAR(5),
  max_helfende       TINYINT UNSIGNED NOT NULL DEFAULT 2
  PRIMARY KEY (schicht_id),
  FOREIGN KEY (veranstaltung_id) REFERENCES veranstaltung (veranstaltung_id),
  FOREIGN KEY (aufgabenbereich_id) REFERENCES aufgabenbereich (aufgabenbereich_id),
  FOREIGN KEY (raum_id) REFERENCES raum (raum_id)
);

CREATE TABLE aufgabenbereich (
  aufgabenbereich_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  bezeichnung        string NOT NULL,
  beschreibung       VARCHAR(300),
  verantwortlich_id  INT NOT NULL,
  FOREIGN KEY (verantwortlich_id) REFERENCES person (person_id)
);

CREATE TABLE einsatz (
  schicht_id      INT UNSIGNED NOT NULL,
  person_id       INT UNSIGNED NOT NULL,
  eingetragen_am  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  erschienen      BOOLEAN NOT NULL DEFAULT 'nein',
  PRIMARY KEY (person_id),
  FOREIGN KEY (schicht_id) REFERENCES schicht (schicht_id),
  FOREIGN KEY (person_id) REFERENCES person (person_id)
);

Finde acht Fehler und gib jeweils an, wie er korrigiert wird. (8 Punkte)

Je gefundenem und korrigiertem Fehler 1 Punkt:

Fehler im Skript (Beispiellösung)
Nr. Fehler Korrektur
1 Reihenfolge: schicht verweist auf aufgabenbereich, das erst danach angelegt wird – MariaDB lehnt den Fremdschlüssel ab (Fehler 1005, errno 150). aufgabenbereich zuerst anlegen, dann schicht, dann einsatz.
2 Nach DEFAULT 2 fehlt das Komma vor PRIMARY KEY (Syntaxfehler 1064). max_helfende TINYINT UNSIGNED NOT NULL DEFAULT 2,
3 raum_id ist NOT NULL, nur Schichten der Raumbetreuung haben aber einen Raum. raum_id INT UNSIGNED NULL
4 beginn und ende als Text: Uhrzeiten lassen sich so weder sicher vergleichen noch prüfen (als Text gilt „8:30“ > „10:30“), ungültige Werte wie „25:99“ wären möglich; außerdem fehlt die Eingabepflicht. beginn TIME NOT NULL, ende TIME NOT NULL
5 string ist kein SQL-Datentyp (Syntaxfehler). bezeichnung VARCHAR(40) NOT NULL
6 verantwortlich_id INT passt nicht zu person.person_id INT UNSIGNED; Fremdschlüssel brauchen denselben Typ einschließlich Vorzeichen (Fehler 1005, errno 150). verantwortlich_id INT UNSIGNED NOT NULL
7 BOOLEAN (intern TINYINT(1)) kann den Standardwert 'nein' nicht aufnehmen (Fehler 1067 „Invalid default value“). erschienen BOOLEAN NOT NULL DEFAULT FALSE
8 Primärschlüssel nur person_id: Jede Person könnte nur eine einzige Schicht übernehmen. PRIMARY KEY (schicht_id, person_id)

Weitere sinnvolle Verbesserungen (ohne Punkte, als Alternative für einen der Fehler aber gleichwertig): CHECK (ende > beginn) und CHECK (max_helfende >= 2), damit die Aufsichtsregel der Schule überhaupt erfüllbar ist, Namen für die Constraints.

Korrigiert lautet die Tabelle schicht z. B.:

 
CREATE TABLE schicht (
  schicht_id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  veranstaltung_id   INT UNSIGNED NOT NULL,
  aufgabenbereich_id INT UNSIGNED NOT NULL,
  raum_id            INT UNSIGNED NULL,
  beginn             TIME NOT NULL,
  ende               TIME NOT NULL,
  max_helfende       TINYINT UNSIGNED NOT NULL DEFAULT 2,
  PRIMARY KEY (schicht_id),
  CONSTRAINT chk_schicht_zeit CHECK (ende > beginn),
  FOREIGN KEY (veranstaltung_id) REFERENCES veranstaltung (veranstaltung_id),
  FOREIGN KEY (aufgabenbereich_id) REFERENCES aufgabenbereich (aufgabenbereich_id),
  FOREIGN KEY (raum_id) REFERENCES raum (raum_id)
);

b) Erstelle die SQL-Anweisung für die Tabelle geraeteausgabe. Die Datenbank soll selbst sicherstellen, dass ein Gerät nur an eine Person ausgegeben wird, die in dieser Schicht eingetragen ist. Die Rückgabezeit ist bei der Ausgabe noch leer und darf nicht vor der Ausgabezeit liegen. (5 Punkte)

 
CREATE TABLE geraeteausgabe (
  geraet_id     INT UNSIGNED NOT NULL,
  schicht_id    INT UNSIGNED NOT NULL,
  person_id     INT UNSIGNED NOT NULL,
  ausgegeben_um TIME NOT NULL,
  zurueck_um    TIME NULL,
  PRIMARY KEY (geraet_id, schicht_id),
  CONSTRAINT chk_ga_zeit CHECK (zurueck_um >= ausgegeben_um),
  CONSTRAINT fk_ga_geraet  FOREIGN KEY (geraet_id) REFERENCES geraet (geraet_id),
  CONSTRAINT fk_ga_einsatz FOREIGN KEY (schicht_id, person_id)
    REFERENCES einsatz (schicht_id, person_id)
);

Bewertung: zusammengesetzter Primärschlüssel 1 Punkt, Fremdschlüssel auf geraet 1 Punkt, zusammengesetzter Fremdschlüssel auf einsatz 2 Punkte, Zeitspalten mit CHECK 1 Punkt.

Worauf es ankommt:

  • Ein Fremdschlüssel aus zwei Spalten muss auf einen Primärschlüssel (oder UNIQUE-Schlüssel) mit denselben zwei Spalten in derselben Reihenfolge verweisen. Zwei getrennte Fremdschlüssel auf schicht und person würden auch eine Person akzeptieren, die gar nicht in der Schicht eingetragen ist.

  • person_id muss NOT NULL sein: Ist ein Teil eines zusammengesetzten Fremdschlüssels NULL, prüft MariaDB den Fremdschlüssel nicht.

  • Ist zurueck_um NULL, gilt die CHECK-Bedingung als erfüllt; CHECK (zurueck_um IS NULL OR zurueck_um >= ausgegeben_um) ist gleichwertig und deutlicher. „Nicht vor“ heißt >=: Eine Rückgabe zur selben Uhrzeit ist erlaubt.

  • Test mit den Testdaten (Skript, Teil 3 und 4): Ist Hannah (Person 14) in Schicht 1 eingetragen, Emre (Person 4) aber nicht, wird die Ausgabe des Terminals CHK-01 an Hannah gespeichert, die an Emre mit Fehler 1452 („Cannot add or update a child row“) abgelehnt; eine zweite Ausgabe von CHK-01 in Schicht 1 scheitert am Primärschlüssel.

c) Die sechs vorhandenen Orga-Aufgaben sollen künftig einem Aufgabenbereich zugeordnet werden können. Binde die Tabelle aufgabenbereich mit einer SQL-Anweisung in die Tabelle orga_aufgabe ein. Wird ein Aufgabenbereich gelöscht, sollen die Orga-Aufgaben erhalten bleiben. Begründe, warum die neue Spalte vorerst kein Pflichtfeld sein kann. (5 Punkte)

 
ALTER TABLE orga_aufgabe
  ADD COLUMN aufgabenbereich_id INT UNSIGNED NULL,
  ADD CONSTRAINT fk_oa_bereich FOREIGN KEY (aufgabenbereich_id)
    REFERENCES aufgabenbereich (aufgabenbereich_id) ON DELETE SET NULL;

Bewertung: ADD COLUMN mit passendem Typ 1 Punkt, ADD CONSTRAINT … FOREIGN KEY … REFERENCES 2 Punkte, ON DELETE SET NULL 1 Punkt, Begründung 1 Punkt.

Begründung: Die sechs Orga-Aufgaben existieren schon und haben noch keinen Aufgabenbereich. Eine NOT NULL-Spalte bräuchte sofort einen gültigen Wert für alle Zeilen – 0 oder ein willkürlicher Bereich würde den Fremdschlüssel verletzen bzw. falsche Zuordnungen erzeugen. Außerdem verlangt ON DELETE SET NULL, dass die Spalte NULL aufnehmen kann. Erst wenn alle Aufgaben zugeordnet sind, könnte man sie zum Pflichtfeld machen (dann aber mit ON DELETE RESTRICT).

Zwei getrennte Anweisungen (ALTER TABLE … ADD COLUMN …; und ALTER TABLE … ADD FOREIGN KEY …;) sind ebenfalls richtig.

d) Für die Anmeldung wurde bis Anfang Oktober ein Online-Formular genutzt; sein Export liegt unbereinigt in der Tabelle formular_export (zeile, zeitstempel, name, email, schule_firma, teilnahme_als, sessionidee, essen, hinweise); zeile ist eine fortlaufende Nummer, alle übrigen Spalten sind Text und erlauben NULL. Alle Anmeldungen wurden inzwischen in die Tabellen person und anmeldung übernommen. Aus Gründen der Datenminimierung soll der Export gelöscht werden. Die eingereichten Session-Ideen möchte Dimitrios aber ohne Personenbezug für die Themensuche im nächsten Jahr aufheben.

da) Ermittle mit einer SQL-Abfrage alle Zeilen des Exports, die eine Session-Idee enthalten. (2 Punkte)

db) Erstelle die Tabelle ideen_archiv mit einem eigenen Schlüssel, den Spalten teilnahme_als und sessionidee aus dem Export und dem Jahr des Barcamps (Standardwert 2027), und übertrage die Ideen ohne Duplikate. (4 Punkte)

dc) Erstelle die Anweisung, die den Export vollständig entfernt, und nimm Stellung, warum hier weder DELETE FROM formular_export; noch TRUNCATE TABLE formular_export; gewählt wird. (5 Punkte)

da) (2 Punkte)

 
SELECT zeile, name, teilnahme_als, sessionidee
FROM formular_export
WHERE sessionidee IS NOT NULL;

Ergebnis: fünf Zeilen (1 Oluwaseun Adeyemi, 3 und 4 Gerhard Vogt – doppelt abgeschickt, 5 Lisa Brandt, 7 Mia Schröder). Die zusätzliche Bedingung AND sessionidee <> '' ist ebenfalls richtig.

db)

 
CREATE TABLE ideen_archiv (
  idee_id       INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  teilnahme_als VARCHAR(50)  NULL,
  sessionidee   VARCHAR(300) NOT NULL,
  jahr          SMALLINT UNSIGNED NOT NULL DEFAULT 2027
);

INSERT INTO ideen_archiv (teilnahme_als, sessionidee)
SELECT DISTINCT teilnahme_als, sessionidee
FROM formular_export
WHERE sessionidee IS NOT NULL;

Bewertung: Tabelle mit Schlüssel, sinnvollen Datentypen und DEFAULT 2027 2 Punkte, INSERT … SELECT mit Spaltenliste 1 Punkt, DISTINCT und Bedingung 1 Punkt.

Ergebnis: 4 Ideen; die doppelte Zeile von Gerhard Vogt wird durch DISTINCT nur einmal übernommen. jahr erhält den Standardwert, weil die Spalte im INSERT fehlt.

Alternative: CREATE TABLE ideen_archiv AS SELECT DISTINCT teilnahme_als, sessionidee FROM formular_export WHERE sessionidee IS NOT NULL; legt die Tabelle und die Daten in einem Schritt an, übernimmt aber keinen Primärschlüssel und keine Indizes; die Spalte jahr entsteht gar nicht, und weil die Textspalten des Exports NULL erlauben, ist auch sessionidee nicht NOT NULL. Das müsste mit ALTER TABLE ergänzt werden. Umgekehrt übernimmt CREATE TABLE … LIKE die Struktur samt Indizes, aber ohne Daten und ohne Fremdschlüssel.

dc)

 
DROP TABLE formular_export;

(1 Punkt; DROP TABLE IF EXISTS ebenfalls richtig)

Stellungnahme (4 Punkte):

  • DELETE FROM formular_export; (DML) löscht nur die Datensätze, Zeile für Zeile; die leere Tabelle mit ihrer Struktur bleibt bestehen. Gefordert ist, den Export vollständig zu entfernen – die Tabelle wird nicht mehr gebraucht.

  • TRUNCATE TABLE formular_export; (DDL) leert die Tabelle schneller, indem sie sie intern neu anlegt, und setzt den AUTO_INCREMENT-Zähler zurück. Auch hier bleibt die leere Tabelle bestehen.

  • DROP TABLE (DDL) entfernt Struktur und Inhalt. Wie TRUNCATE ist es nicht mit ROLLBACK umkehrbar. Deshalb vorher prüfen, ob wirklich alle Ideen archiviert sind (Ergebnis von db).

  • Würde eine andere Tabelle per Fremdschlüssel auf den Export verweisen, verweigerten DROP und TRUNCATE die Ausführung; das ist hier nicht der Fall.

e) Für das nächste Barcamp möchte Emre Aksoy die Check-in-Terminals ausprobieren, ohne die echte Datenbank barcamp zu gefährden.

ea) Erstelle die Anweisungen, mit denen Du eine Datenbank barcamp_test anlegst (nur, falls sie noch nicht existiert), sie auswählst, darin eine Tabelle raum mit derselben Struktur wie barcamp.raum anlegst und deren Struktur anzeigen lässt. Nutze die SQL-Kurzreferenz. (3 Punkte)

eb) Dimitrios schlägt vor, auch im Echtbetrieb für jedes Barcamp-Jahr eine eigene Datenbank (barcamp2027, barcamp2028 …) anzulegen. Nimm Stellung. (3 Punkte)

ea)

 
CREATE DATABASE IF NOT EXISTS barcamp_test;
USE barcamp_test;
CREATE TABLE raum LIKE barcamp.raum;
DESCRIBE raum;

Bewertung: CREATE DATABASE IF NOT EXISTS und USE 1 Punkt, CREATE TABLE … LIKE 1 Punkt, DESCRIBE (oder SHOW CREATE TABLE raum; bzw. SHOW COLUMNS FROM raum;) 1 Punkt.

CREATE TABLE … LIKE übernimmt Spalten, Datentypen, Standardwerte und Indizes (einschließlich Primärschlüssel), aber keine Daten und keine Fremdschlüssel. Daten lassen sich anschließend mit INSERT INTO raum SELECT * FROM barcamp.raum; kopieren. In MariaDB sind DATABASE und SCHEMA gleichbedeutend.

eb) (3 Punkte; begründete Ablehnung mit drei Argumenten oder begründete Zustimmung)

Abzuraten, weil

  • das Datenmodell schon mehrere Veranstaltungen kennt (veranstaltung_id in anmeldung, slot, session, schicht); jedes Jahr ist nur ein weiterer Datensatz in veranstaltung,

  • Stammdaten wie person, raum oder geraet sonst in jeder Datenbank doppelt gepflegt würden (Redundanz, Änderungsanomalien) und Fremdschlüssel über Datenbankgrenzen hinweg unübersichtlich werden,

  • Auswertungen über mehrere Jahre („Wie hat sich die Teilnehmendenzahl entwickelt?“) dann Abfragen über mehrere Datenbanken bräuchten,

  • jede Strukturänderung (z. B. die neue Spalte aus c) in allen Datenbanken wiederholt werden müsste.

Für die Datenminimierung reicht es, personenbezogene Daten vergangener Barcamps nach Ablauf der Frist zu löschen bzw. zu anonymisieren. Eine getrennte Testdatenbank wie in ea) ist dagegen sinnvoll.

Aufgabe 3.1: Ausbildungsbetriebe und Parkplatzbelegung in der Mitfahr-App (Profilkompetenz)

(ca. 36 min Bearbeitungszeit)

Du bist Auszubildende*r im Projektteam der Mitfahr-App „RatzFahrt“ des Ratz und Rübe Berufskollegs in Bielefeld. Die MariaDB-Datenbank ratzfahrt läuft seit September; in der Tabelle nutzer sind zehn Personen registriert. Zwei neue Wünsche liegen auf dem Tisch: Mehrere Ausbildungsbetriebe wollen Fahrgemeinschaften ihrer Auszubildenden mit einem Bonus je Fahrt fördern, und die Schul-IT möchte in der App anzeigen, wie voll die Schulparkplätze gerade sind. Auszug aus der bestehenden Datenbank:

CREATE TABLE nutzer (
  nutzer_id        INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  pseudonym        VARCHAR(30)  NOT NULL UNIQUE,
  vorname          VARCHAR(50)  NOT NULL,
  nachname         VARCHAR(50)  NOT NULL,
  email_schule     VARCHAR(100) NOT NULL UNIQUE,
  geburtsdatum     DATE         NOT NULL,
  klasse_id        INT UNSIGNED NULL,          -- NULL bei Lehrkräften
  -- weitere Spalten
  CONSTRAINT fk_nutzer_klasse FOREIGN KEY (klasse_id) REFERENCES klasse (klasse_id)
);

CREATE TABLE parkplatz (
  parkplatz_id     TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  bezeichnung      VARCHAR(40)  NOT NULL UNIQUE,
  stellplaetze     SMALLINT     NOT NULL
);
-- Inhalt: (1, 'Parkplatz A (Lehrkräfte)', 64), (2, 'Parkplatz C (Schüler*innen)', 180)

a) Für die neue Tabelle ausbildungsbetrieb hat Thomas Becker (Projektleitung) einen ersten Entwurf auf dem Testsystem ausprobiert:

CREATE TABLE ausbildungsbetrieb_test (
  betrieb_id        INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name              VARCHAR(100) NOT NULL,
  plz               INT          NOT NULL,
  ort               VARCHAR(60)  NOT NULL,
  ust_id            BIGINT       NULL,
  foerderung_aktiv  VARCHAR(4)   NOT NULL DEFAULT 'ja',
  vertrag_seit      VARCHAR(10)  NULL,
  bonus_je_fahrt    INT          NOT NULL DEFAULT 0
);

INSERT INTO ausbildungsbetrieb_test
  (name, plz, ort, foerderung_aktiv, vertrag_seit, bonus_je_fahrt)
VALUES ('Elektro Brinkmann KG',   '33689', 'Bielefeld', 'ja',   '01.08.2026', 1.50),
       ('Logistik Auensee GmbH',  '04109', 'Leipzig',   'nein', '2026-09-15', 0.75),
       ('Tischlerei Vogt',        '33602', 'Bielefeld', 'Ja',   '1.10.26',    0.40);

Danach hat er drei Dinge ausprobiert:

  1. SELECT betrieb_id, plz, foerderung_aktiv, vertrag_seit, bonus_je_fahrt FROM ausbildungsbetrieb_test; liefert:
Testergebnis Thomas Becker
betrieb_id plz foerderung_aktiv vertrag_seit bonus_je_fahrt
1 33689 ja 01.08.2026 2
2 4109 nein 2026-09-15 1
3 33602 Ja 1.10.26 0

  1. Die Abfrage „Betriebe mit Vertrag vor dem 1. September 2026“ (… WHERE vertrag_seit < '2026-09-01') liefert die Betriebe 1 und 3.

  2. UPDATE ausbildungsbetrieb_test SET ust_id = 'DE813245679' WHERE betrieb_id = 1; (Umsatzsteuer-Identifikationsnummer) bricht mit Fehler 1366 („Incorrect integer value“) ab.

Werte die Tests aus: Benenne für fünf Spalten, welches Problem der Entwurf verursacht, und schlage jeweils einen besser geeigneten Datentyp vor. Begründe bei plz und ust_id außerdem, warum eine Zeichenkette fester Länge passt. (5 Punkte)

Je Spalte 1 Punkt:

Spalte Problem im Entwurf besser
plz Als Zahl fällt die führende Null weg (04109 → 4109); mit Postleitzahlen wird nie gerechnet. CHAR(5)
ust_id Die USt-IdNr. beginnt mit dem Länderkürzel (DE) – Buchstaben passen in keine Ganzzahl; gerechnet wird mit ihr ohnehin nicht. CHAR(11) für deutsche USt-IdNr. (DE + 9 Ziffern), VARCHAR(14) für ausländische
foerderung_aktiv Freier Text erlaubt ja, Ja, jo …; Abfragen wie WHERE foerderung_aktiv = 'ja' übersehen Varianten. BOOLEAN NOT NULL DEFAULT TRUE
vertrag_seit Datumsangaben als Text in verschiedenen Schreibweisen; der Vergleich läuft Zeichen für Zeichen ('1.10.26' < '2026-09-01', weil '1' < '2'), deshalb erscheint Betrieb 3 fälschlich im Ergebnis. DATE (Eingabe als '2026-08-01')
bonus_je_fahrt Ganzzahl: Die Beträge werden gerundet, die Cent gehen verloren (1,50 € → 2, 0,40 € → 0). DECIMAL(4,2) (Festkomma, keine Rundungsfehler wie bei FLOAT)

Feste Länge: Deutsche Postleitzahlen haben immer fünf, deutsche USt-IdNr. immer elf Zeichen. CHAR(n) braucht dann kein zusätzliches Längenfeld, und VARCHAR brächte keinen Vorteil. (Die Begründung ist Teil der Punkte für plz und ust_id.)

b) Gib die SQL-Anweisung an, die die Tabelle ausbildungsbetrieb mit den Feldern aus a) anlegt. Dabei gilt: Die ID wird automatisch vergeben; Name, PLZ und Ort sind Pflichtangaben; eine USt-IdNr. darf fehlen (z. B. bei Kleinunternehmen), kommt aber höchstens einmal vor; neue Betriebe werden standardmäßig als „Förderung aktiv“ angelegt; der Bonus liegt zwischen 0 und 5 € und ist ohne Angabe 0. (6 Punkte)

 
CREATE TABLE ausbildungsbetrieb (
  betrieb_id        INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  name              VARCHAR(100)  NOT NULL,
  plz               CHAR(5)       NOT NULL,
  ort               VARCHAR(60)   NOT NULL,
  ust_id            CHAR(11)      NULL,
  foerderung_aktiv  BOOLEAN       NOT NULL DEFAULT TRUE,
  vertrag_seit      DATE          NULL,
  bonus_je_fahrt    DECIMAL(4,2)  NOT NULL DEFAULT 0.00,
  CONSTRAINT pk_betrieb PRIMARY KEY (betrieb_id),
  CONSTRAINT uq_betrieb_ust_id UNIQUE (ust_id),
  CONSTRAINT chk_bonus CHECK (bonus_je_fahrt BETWEEN 0 AND 5)
);

Bewertung: CREATE TABLE mit Spalten und Datentypen 1 Punkt, Primärschlüssel mit AUTO_INCREMENT 1 Punkt, NOT NULL für die Pflichtfelder 1 Punkt, UNIQUE für die USt-IdNr. (mehrere NULL bleiben erlaubt) 1 Punkt, zwei DEFAULT-Werte 1 Punkt, CHECK 1 Punkt. Constraints können auch direkt an der Spalte stehen.

Getestet mit zwei Beispielbetrieben; ein Bonus von 7,00 € wird abgewiesen (Fehler 4025 „CONSTRAINT chk_bonus failed“).

c) Jede*r Auszubildende gehört zu genau einem Ausbildungsbetrieb, ein Betrieb kann viele Auszubildende haben; Lehrkräfte haben keinen Betrieb. Gib die SQL-Anweisungen in der richtigen Reihenfolge an, mit denen die Tabelle nutzer unter Einhaltung der 3. Normalform mit ausbildungsbetrieb verknüpft wird. Wird ein Betrieb gelöscht, sollen die Nutzerkonten erhalten bleiben. (5 Punkte)

 
-- 1. Tabelle ausbildungsbetrieb anlegen (siehe b)

-- 2. Fremdschlüsselspalte ergänzen (gleicher Typ wie der Primärschlüssel!)
ALTER TABLE nutzer
  ADD COLUMN betrieb_id INT UNSIGNED NULL AFTER klasse_id;

-- 3. Fremdschlüssel festlegen
ALTER TABLE nutzer
  ADD CONSTRAINT fk_nutzer_betrieb FOREIGN KEY (betrieb_id)
      REFERENCES ausbildungsbetrieb (betrieb_id)
      ON DELETE SET NULL;

Bewertung: Spalte mit passendem Typ INT UNSIGNED und NULL erlaubt (Lehrkräfte) 2 Punkte, Fremdschlüssel mit REFERENCES 2 Punkte, richtige Reihenfolge und ON DELETE SET NULL 1 Punkt. Beide Änderungen dürfen auch in einer ALTER TABLE-Anweisung stehen. Nicht in der 3. Normalform wäre es, Name oder Ort des Betriebs als Spalten in nutzer zu speichern.

d) Oksana Melnyk schlägt vor, die Tabelle nutzer einfach mit CREATE TABLE neu anzulegen, „dann steht betrieb_id gleich an der richtigen Stelle“. Nimm Stellung zu ihrem Vorschlag. Gehe dabei darauf ein, was mit den zehn Nutzerkonten und den Tabellen geschähe, die auf nutzer verweisen, wie sich Deine Anweisungen aus c) dagegen auf den Bestand auswirken und was danach noch zu erledigen ist. (4 Punkte)

Oksanas Vorschlag (2 Punkte): CREATE TABLE nutzer scheitert, solange die Tabelle existiert (Fehler 1050 „Table ‘nutzer’ already exists“). Um sie neu anzulegen, müsste sie vorher mit DROP TABLE gelöscht werden – das verweigert MariaDB aber, solange angebot, buchung usw. per Fremdschlüssel auf nutzer verweisen. Und selbst wenn es gelänge, wären alle zehn Nutzerkonten weg. Die gewünschte Position erreicht man auch mit ALTER TABLE … ADD COLUMN … AFTER klasse_id; für die Datenbank ist die Spaltenreihenfolge ohnehin bedeutungslos.

Wirkung von c) (1 Punkt): ALTER TABLE ändert nur die Struktur. Alle zehn Datensätze bleiben erhalten und haben in der neuen Spalte betrieb_id den Wert NULL, da kein DEFAULT angegeben ist. Der Fremdschlüssel lässt sich sofort anlegen, weil NULL-Werte die referenzielle Integrität nicht verletzen.

Nacharbeit (1 Punkt): Für jede*n Auszubildende*n wird der passende Betrieb per UPDATE nachgetragen, z. B. UPDATE nutzer SET betrieb_id = 1 WHERE nutzer_id = 1;. Lehrkräfte behalten NULL.

e) Für die Ansprechpersonen in den Betrieben hat ein Mitschüler folgende Anweisung geschrieben. MariaDB lehnt sie ab. Benenne sechs Fehler und gib eine korrigierte Anweisung an. (6 Punkte)

CREATE TABLE betrieb-ansprechperson (
  ansprech-id   INT NOT NULL AUTO_INCREMENT,
  name          VARCHAR(80) NOT NULL,
  email         VARCHAR(100),
  betrieb_id    INT NOT NULL,
  PRIMARY KEY (ansprech-id),
  FOREIGN KEY betrieb_id REFERENCES ausbildungsbetrieb,
);

Fehler (je 0,5 Punkte):

  1. Bindestrich im Tabellennamen betrieb-ansprechperson (wird als Minus gelesen) → Unterstrich oder Name in Backticks.
  2. Bindestrich im Spaltennamen ansprech-id (in der Spaltendefinition und im PRIMARY KEY).
  3. betrieb_id ist INT, der Primärschlüssel in ausbildungsbetrieb ist INT UNSIGNED: Fremdschlüssel und referenzierte Spalte müssen gleichen Typ und gleiches Vorzeichen haben.
  4. Nach FOREIGN KEY fehlen die Klammern um die Spalte: FOREIGN KEY (betrieb_id).
  5. Nach REFERENCES ausbildungsbetrieb fehlt die referenzierte Spalte (betrieb_id).
  6. Komma nach der letzten Tabellenbedingung vor der schließenden Klammer.

Korrigiert (3 Punkte):

 
CREATE TABLE betrieb_ansprechperson (
  ansprech_id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name          VARCHAR(80)  NOT NULL,
  email         VARCHAR(100),
  betrieb_id    INT UNSIGNED NOT NULL,
  PRIMARY KEY (ansprech_id),
  FOREIGN KEY (betrieb_id) REFERENCES ausbildungsbetrieb (betrieb_id)
);

f) An den Einfahrten der Schulparkplätze zählen Induktionsschleifen die ein- und ausfahrenden Autos. Ihre Steuerung meldet alle fünf Minuten, wie viele Stellplätze belegt sind; die App soll damit anzeigen, ob sich die Suche nach einem Platz lohnt. Svenja Krämer (Schul-IT) hat dafür bereits eine Tabelle angelegt:

CREATE TABLE belegung (
  belegung_id   INT UNSIGNED     NOT NULL AUTO_INCREMENT PRIMARY KEY,
  parkplatz_id  TINYINT UNSIGNED NOT NULL,
  gemeldet_um   DATETIME         NOT NULL,
  belegt        SMALLINT         NOT NULL,
  CONSTRAINT fk_belegung_parkplatz FOREIGN KEY (parkplatz_id)
      REFERENCES parkplatz (parkplatz_id)
);

fa) In der ersten Testwoche kamen nach Funkaussetzern Meldungen doppelt an (gleicher Parkplatz, gleicher Zeitpunkt), und nach einem Stromausfall meldete eine Steuerung negative Belegungen. Ergänze die Tabelle mit einer ALTER TABLE-Anweisung so, dass die Datenbank beides künftig abweist. Erläutere, was passiert, wenn noch fehlerhafte Testzeilen in der Tabelle stehen. (4 Punkte)

fb) Die App soll Meldungen wie „Parkplatz C (Schüler*innen): noch 23 von 180 Plätzen frei, 87 % belegt“ anzeigen. Jonas möchte dazu in belegung eine generierte Spalte frei mit dem Ausdruck stellplaetze - belegt ergänzen. Erkläre, warum MariaDB das ablehnt, und erstelle stattdessen eine View v_belegung, die zu jeder Meldung Bezeichnung des Parkplatzes, Zeitpunkt, belegte und freie Plätze sowie die Auslastung in ganzen Prozent liefert. (5 Punkte)

fc) Svenja schlägt vor, die Zahl der Stellplätze zusätzlich in jede Zeile von belegung zu schreiben – „dann klappt die generierte Spalte doch“. Nimm Stellung. (2 Punkte)

fa)

 
ALTER TABLE belegung
  ADD CONSTRAINT uq_belegung_zeitpunkt UNIQUE (parkplatz_id, gemeldet_um),
  ADD CONSTRAINT chk_belegung_belegt   CHECK (belegt >= 0);

Bewertung: UNIQUE über beide Spalten (nicht nur gemeldet_um, denn beide Parkplätze melden zur selben Zeit) 1 Punkt, CHECK (belegt >= 0) 1 Punkt, beides in einer ALTER TABLE-Anweisung mit ADD CONSTRAINT 1 Punkt. Gleichwertig für den zweiten Teil: MODIFY belegt SMALLINT UNSIGNED NOT NULL – dann weist MariaDB negative Werte im strikten Modus als „Out of range“ (Fehler 1264) ab.

Vorhandene Fehlzeilen (1 Punkt): MariaDB prüft beim Hinzufügen der Constraints alle vorhandenen Zeilen. Gibt es noch ein doppeltes Paar, bricht die Anweisung mit Fehler 1062 („Duplicate entry“) ab, bei einer negativen Belegung mit Fehler 4025 („CONSTRAINT chk_belegung_belegt failed“). Die ganze Anweisung wird dann nicht ausgeführt; vorher müssen die Fehlzeilen gesucht (GROUP BY parkplatz_id, gemeldet_um HAVING COUNT(*) > 1 bzw. WHERE belegt < 0) und bereinigt werden.

fb)

Begründung (1 Punkt): Der Ausdruck einer generierten Spalte darf nur Spalten derselben Zeile derselben Tabelle verwenden. stellplaetze steht in parkplatz; MariaDB meldet deshalb eine unbekannte Spalte. Unterabfragen sind in generierten Spalten ebenfalls nicht erlaubt. Werte, die Daten aus mehreren Tabellen verbinden, berechnet eine View.

 
CREATE VIEW v_belegung AS
SELECT p.bezeichnung,
       b.gemeldet_um,
       b.belegt,
       p.stellplaetze - b.belegt                AS frei,
       ROUND(b.belegt * 100 / p.stellplaetze)  AS auslastung_prozent
FROM belegung AS b
JOIN parkplatz AS p ON p.parkplatz_id = b.parkplatz_id;

Bewertung: CREATE VIEW … AS SELECT 1 Punkt, Verknüpfung über parkplatz_id 1 Punkt, freie Plätze 1 Punkt, Auslastung in ganzen Prozent 1 Punkt.

Beispiel: Meldet Parkplatz C um 7:55 Uhr 157 belegte Plätze, liefert die View frei = 23 und auslastung_prozent = 87 (157 · 100 / 180 ≈ 87,2). Die App fragt dann nur die jüngste Meldung je Parkplatz ab, z. B. mit ORDER BY gemeldet_um DESC LIMIT 1.

fc) (2 Punkte) In der Regel abzulehnen: stellplaetze hängt nur vom Parkplatz ab, nicht von der einzelnen Meldung. Die Kopie in jeder Zeile wäre redundant (Verstoß gegen die 3. Normalform), kostet bei 288 Meldungen je Parkplatz und Tag unnötig Speicher und führt zu Widersprüchen, sobald sich die Zahl der Stellplätze ändert, aber nicht überall nachgetragen wird. Die View aus fb) liefert dasselbe ohne Redundanz. Vertretbar ist der Vorschlag nur, wenn bewusst die Kapazität zum Meldezeitpunkt festgehalten werden soll (z. B. weil eine Baustelle zeitweise Plätze sperrt) – dann ist es kein redundanter Wert, sondern ein eigener Messwert und sollte auch so heißen (z. B. stellplaetze_verfuegbar).

g) Die Steuerung der Zählschleifen soll sich mit einem eigenen Datenbankbenutzer anmelden, der ausschließlich Belegungsmeldungen eintragen darf. Die Steuerungen stehen im Netz 10.50.30.0/24. Gib die SQL-Anweisungen an, die den Benutzer zaehldienst mit Passwort anlegen und ihm nur dieses Recht geben. (3 Punkte)

 
CREATE USER 'zaehldienst'@'10.50.30.%' IDENTIFIED BY 'Vh7!pQ2#rT9w';
GRANT INSERT ON ratzfahrt.belegung TO 'zaehldienst'@'10.50.30.%';

Bewertung: Benutzer mit Passwort 1 Punkt, Einschränkung auf das Netz der Steuerungen 1 Punkt, nur INSERT auf genau diese Tabelle 1 Punkt. Weil die Spalte belegung_id automatisch vergeben wird, genügt das INSERT-Recht; die View v_belegung braucht der Zähldienst nicht. Mehr zu Benutzern und Rechten: SQL-DCL.

Anlage: SQL-Kurzreferenz DDL (MariaDB)

SQL-Kurzreferenz DDL für MariaDB
Syntax Beschreibung
CREATE DATABASE [IF NOT EXISTS] name; / USE name; Datenbank anlegen / auswählen (SCHEMA gleichbedeutend mit DATABASE)
CREATE TABLE tabelle (spalte TYP [optionen], …, [tabellenbedingungen]); Tabelle anlegen
CREATE TABLE neu LIKE alt; leere Tabelle mit gleicher Struktur (ohne Fremdschlüssel)
CREATE TABLE neu AS SELECT …; Tabelle aus einem Abfrageergebnis (ohne Schlüssel und Indizes)
Spaltenoptionen: NOT NULL, NULL, DEFAULT wert, AUTO_INCREMENT, UNIQUE, PRIMARY KEY Pflichtfeld, optional, Standardwert, fortlaufende Nummer, eindeutig, Primärschlüssel
[CONSTRAINT name] PRIMARY KEY (spalte1[, spalte2 …]) (zusammengesetzter) Primärschlüssel
[CONSTRAINT name] UNIQUE (spalte1[, spalte2 …]) Werte(kombination) eindeutig, NULL mehrfach erlaubt
[CONSTRAINT name] CHECK (bedingung) nur Werte erlauben, für die die Bedingung nicht FALSE ist
[CONSTRAINT name] FOREIGN KEY (spalte[, …]) REFERENCES tabelle (spalte[, …]) [ON DELETE \| ON UPDATE RESTRICT \| CASCADE \| SET NULL] Fremdschlüssel; ohne Angabe gilt RESTRICT
ALTER TABLE tabelle ADD [COLUMN] spalte TYP [optionen] [AFTER spalte]; Spalte hinzufügen
ALTER TABLE tabelle DROP [COLUMN] spalte; Spalte entfernen
ALTER TABLE tabelle MODIFY [COLUMN] spalte TYP [optionen]; Datentyp/Optionen einer Spalte ändern (vollständige Definition)
ALTER TABLE tabelle CHANGE [COLUMN] alt neu TYP [optionen]; / RENAME COLUMN alt TO neu Spalte umbenennen (und ändern)
ALTER TABLE tabelle ADD [CONSTRAINT name] PRIMARY KEY \| UNIQUE \| CHECK \| FOREIGN KEY …; Bedingung nachträglich hinzufügen
ALTER TABLE tabelle DROP CONSTRAINT name; / DROP FOREIGN KEY name; / DROP INDEX name; Bedingung, Fremdschlüssel, Index entfernen
RENAME TABLE alt TO neu; Tabelle umbenennen
DROP TABLE [IF EXISTS] tabelle[, …]; Tabelle mit Inhalt löschen
TRUNCATE TABLE tabelle; alle Datensätze löschen, Struktur bleibt
CREATE [UNIQUE] INDEX name ON tabelle (spalte1[, spalte2 …]); / DROP INDEX name ON tabelle; Index anlegen / löschen
DESCRIBE tabelle; / SHOW CREATE TABLE tabelle; / SHOW INDEX FROM tabelle; Struktur, CREATE-Anweisung, Indizes anzeigen
spalte TYP AS (ausdruck) [STORED \| VIRTUAL] generierte (berechnete) Spalte; Ausdruck nur aus Spalten derselben Zeile
CREATE [OR REPLACE] VIEW name AS SELECT …; / DROP VIEW name; View (gespeicherte Abfrage) anlegen / löschen
CREATE USER 'name'@'host' IDENTIFIED BY 'passwort'; / GRANT recht ON db.tabelle TO 'name'@'host'; Benutzer anlegen / Recht vergeben (DCL)
TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT [UNSIGNED] Ganzzahlen (1, 2, 3, 4, 8 Byte)
DECIMAL(p, s) Festkommazahl mit p Stellen, davon s Nachkommastellen
FLOAT, DOUBLE Gleitkommazahlen (4, 8 Byte)
CHAR(n), VARCHAR(n), TEXT Zeichenkette fester / variabler Länge, langer Text
DATE, TIME, DATETIME Datum ('2026-10-19'), Uhrzeit ('08:30:00'), beides
BOOLEAN Wahrheitswert (Alias für TINYINT(1), TRUE = 1, FALSE = 0)
ENUM('a', 'b', …) genau einer der aufgezählten Werte
SUBSTRING_INDEX(text, trennzeichen, n) Teil von text vor dem n-ten Trennzeichen; bei negativem n Teil nach dem n-ten Trennzeichen von rechts
SUBSTRING(text, start[, laenge]) Teil ab Position start (erstes Zeichen = 1)
LEFT(text, n), RIGHT(text, n) die ersten / letzten n Zeichen
LOCATE(suchtext, text) Position von suchtext in text (0, wenn nicht enthalten)
TRIM(text) entfernt Leerzeichen am Anfang und Ende
CONCAT(text1, text2, …) Texte aneinanderhängen
CASE WHEN bedingung THEN wert [ELSE wert] END Fallunterscheidung
text LIKE muster Mustervergleich, % beliebig viele Zeichen, _ genau ein Zeichen

Hinweis zur Nachnutzung als Open Educational Resource (OER)

Dieser Artikel und seine Texte, Bilder, Grafiken, Code und sonstiger Inhalt sind - sofern nicht anders angegeben - lizenziert unter CC BY 4.0. Nennung gemäß TULLU-Regel bitte wie folgt: “Übungsaufgaben zum Anlegen und Ändern von Tabellen (SQL-DDL)” von oer-informatik.de (H. Stein), Lizenz: CC BY 4.0. Der Artikel wurde unter https://oer-informatik.de/sql-ddl-uebungsaufgaben veröffentlicht, die Quelltexte sind in weiterverarbeitbarer Form verfügbar im Repository unter https://gitlab.com/oer-informatik/db-sql/sql. Erstellt mit KI-Assistenz; von mir umfassend redaktionell überarbeitet, geprüft und freigegeben. Stand: 28.10.2026.

[Kommentare zum Artikel lesen, schreiben] / [Artikel teilen] / [gitlab-Issue zum Artikel schreiben]

Kommentare gerne per Mastodon, Verbesserungsvorschläge per gitlab issue (siehe oben). Beitrag teilen:

Tags:

You may also like...