Übungsaufgaben zum Relationenmodell

Der Primärschlüssel (primary key) ist eine Spalte oder Spaltenkombination, deren Wert jeden Datensatz einer Tabelle eindeutig kennzeichnet (1 Punkt). Er darf nie leer sein und in der Tabelle nicht doppelt vorkommen (1 Punkt).

Ein Fremdschlüssel (foreign key) verknüpft zwei Tabellen: In einer Tabelle wird der Primärschlüssel eines Datensatzes einer anderen Tabelle gespeichert (1 Punkt). Die Datenbank kann so prüfen, dass jeder Verweis auf einen vorhandenen Datensatz zeigt (referentielle Integrität) (1 Punkt).

Vorteil (1 Punkt) mit Erläuterung am Beispiel (1 Punkt), z. B.: Jede Information wird nur einmal gespeichert, Redundanz wird vermieden – die Handynummer einer Fahrerin steht nur in nutzer und lässt sich trotzdem in einer Abfrage mit allen ihren Fahrten zusammen ausgeben. Auch richtig: Änderungen sind nur an einer Stelle nötig, die Daten bleiben widerspruchsfrei.

Redundanz bedeutet, dass dieselbe Information mehrfach gespeichert ist (1 Punkt). Beispiel: Name und Handynummer von Ben Richter oder der Treffpunkt stehen in jeder Zeile erneut (1 Punkt).

Mehrfach gespeicherte Daten müssen bei einer Änderung an allen Stellen geändert werden; wird eine Stelle vergessen oder vertippt, widersprechen sich die Daten (Inkonsistenz) (1 Punkt). In der Liste steht für Ben Richter einmal 0176 04069011 und einmal 0176 04069111, und der Treffpunkt ist unterschiedlich geschrieben – welche Angabe richtig ist, lässt sich nicht erkennen (1 Punkt). Auch richtig: höherer Speicher- und Pflegeaufwand, Einfüge- oder Löschanomalien (z. B. verschwindet ein Treffpunkt aus der Liste, wenn die letzte Fahrt dort gelöscht wird).

1. Relation (Tabelle), (2) Datensatz (Tupel, Zeile), (3) Attribut (Spalte), (4) Attributwert, (5) Primärschlüssel, (6) Fremdschlüssel – je 0,5 Punkte.

Richtig sind 1 und 3 (je 1 Punkt). Aussage 2 beschreibt eine hierarchische Datenbank, Aussage 4 eine dokumentenorientierte (NoSQL-)Datenbank, Aussage 5 trifft auf keine dauerhafte Datenbank zu.

ea) SQL (Structured Query Language) (1 Punkt).

eb) je 1 Punkt, z. B. dokumentenorientierte Datenbank, Key-Value-Store, Graphdatenbank, spaltenorientierte Datenbank (Wide Column Store), objektorientierte Datenbank, hierarchische Datenbank, Netzwerkdatenbank, Zeitreihendatenbank. „NoSQL-Datenbank“ allein zählt als ein Typ.

Je 1 Punkt, z. B.: mehrere Personen bzw. die App können gleichzeitig lesen und schreiben (Mehrbenutzerbetrieb); Datentypen, Schlüssel und Fremdschlüssel sichern die Widerspruchsfreiheit (Konsistenz); Zugriffsrechte lassen sich je Benutzer*in festlegen (wichtig bei Daten Minderjähriger); große Datenmengen werden schnell durchsucht; Anwendungen wie die App greifen über Schnittstellen zu; Sicherung und Wiederherstellung sind zentral geregelt.

Stammdaten: 1 und 4; Bewegungsdaten: 2 und 3 (je 0,5 Punkte). Begründung (1 Punkt), z. B.: Stammdaten beschreiben dauerhafte Grunddaten (Personen, Orte) und ändern sich selten. Bewegungsdaten entstehen laufend im Tagesgeschäft, beziehen sich auf einen Zeitpunkt oder Vorgang (Fahrt am 19.10.) und verweisen auf Stammdaten.

Primärschlüssel (je 0,5 Punkte): nutzer_id, haltepunkt_id, angebot_id, buchung_id. Kardinalitäten (je 1 Punkt): nutzer 1 : n angebot (als Fahrer*in), haltepunkt 1 : n angebot, angebot 1 : n buchung, nutzer 1 : n buchung (als Mitfahrer*in). Die „1“ steht jeweils an der Tabelle, deren Primärschlüssel als Fremdschlüssel in der anderen Tabelle steht; die „n“ steht an der Tabelle mit dem Fremdschlüssel.

Die Kombination ist nicht eindeutig: Zwei Fahrer*innen können am selben Tag zur selben Zeit am selben Haltepunkt abfahren (1 Punkt). Außerdem ändern sich Abfahrtszeit oder Treffpunkt, wenn eine Fahrt verschoben wird; ein Primärschlüssel soll aber stabil bleiben, weil sonst alle Verweise in buchung mitgeändert werden müssten (1 Punkt).

In Textform:

mitarbeiter      (mitarbeiter_id (PK), vorname, nachname)
fahrradmodell    (modell_id (PK), hersteller, bezeichnung)
fahrrad          (fahrrad_id (PK), rahmennummer (UNIQUE, NULL erlaubt), farbe, modell_id (FK), kunde_id (FK))
werkstattauftrag (auftrag_id (PK), auftragsnr (UNIQUE), annahme_am, fertig_bis, km_stand,
                  fahrrad_id (FK), annahme_mitarbeiter_id (FK))
leistungsart     (leistungsart_id (PK), bezeichnung, stundensatz)
arbeitsposition  (auftrag_id (PK, FK), pos_nr (PK), beschreibung, leistungsart_id (FK),
                  mitarbeiter_id (FK), stunden, stundensatz)
materialposition (auftrag_id (PK, FK), artikel_id (PK, FK), menge, einzelpreis)

Beziehungen: kunde 1:n fahrrad, fahrradmodell 1:n fahrrad, fahrrad 1:n werkstattauftrag, mitarbeiter 1:n werkstattauftrag (Annahme), werkstattauftrag 1:n arbeitsposition, leistungsart 1:n arbeitsposition, mitarbeiter 1:n arbeitsposition (Ausführung), werkstattauftrag 1:n materialposition, artikel 1:n materialposition.

Bewertung: je neue Tabelle mit Primärschlüssel und passenden Attributen 1 Punkt (7 Punkte), je Fremdschlüssel 1 Punkt (9 Punkte), je Beziehung mit Kardinalität 1 Punkt (9 Punkte).

Worauf es ankommt:

• materialposition löst die m:n-Beziehung zwischen Auftrag und Artikel auf; menge und einzelpreis sind Attribute der Beziehung. arbeitsposition ist ein schwacher Entitätstyp: die Positionsnummer ist nur zusammen mit der Auftragsnummer eindeutig.

• mitarbeiter steht in zwei Beziehungen: zum Werkstattauftrag (Annahme) und zur Arbeitsposition (Ausführung) – deshalb Fremdschlüssel mit unterschiedlichen, sprechenden Namen.

• stundensatz steht sowohl in leistungsart (aktueller Satz) als auch in arbeitsposition (Satz zum Zeitpunkt der Arbeit). Das ist keine Redundanz im Sinne der Normalisierung, denn die beiden Werte dürfen sich unterscheiden. Ebenso einzelpreis gegenüber artikel.netto_preis.

• kunde_id gehört nicht in werkstattauftrag: Sie ergibt sich über das Fahrrad (transitive Abhängigkeit). Wer begründet, dass Räder den Besitz wechseln können und der Auftrag den damaligen Auftraggeber festhalten muss, darf kunde_id zusätzlich im Auftrag speichern.

• Hersteller und Modellbezeichnung stehen in einer eigenen Tabelle, weil viele Räder desselben Modells in die Werkstatt kommen und Hersteller und Modell sonst bei jedem Rad erneut (und in wechselnder Schreibweise) erfasst würden. Weil dieselbe Modellbezeichnung bei verschiedenen Herstellern vorkommen kann, ist eine Lösung mit hersteller und modell direkt in fahrrad ebenfalls richtig.

• Beträge, Summen und Mehrwertsteuer werden nicht gespeichert (sie lassen sich aus stunden * stundensatz bzw. menge * einzelpreis und dem Steuersatz berechnen). Hier gilt einheitlich 19 %; müssen auch Änderungen des Steuersatzes nachvollziehbar bleiben, wird der Satz – wie der Preis – zusätzlich in den Positionen festgehalten.

• Für materialposition ist auch ein zusammengesetzter Schlüssel (auftrag_id, pos_nr) richtig – dann darf dasselbe Ersatzteil in zwei Positionen auftauchen.

Je Zeile 1 Punkt (Datentyp passend und begründet).

Anforderungen (je 1 Punkt, zwei verlangt): Der Primärschlüssel muss jeden Datensatz eindeutig identifizieren, er darf nie leer (NULL) sein, er sollte unveränderlich und möglichst kurz (minimal) sein.

Stellungnahme (2 Punkte), z. B.:

• dagegen: Die Rahmennummer ist ein natürlicher Schlüssel, den CSB-Rad nicht selbst vergibt. Bei Rädern anderer Hersteller kann sie fehlen oder unleserlich sein – ein Primärschlüssel darf aber nie leer sein; ein Tippfehler bei der Annahme müsste in allen Tabellen mit Fremdschlüssel korrigiert werden. Ein künstlicher Schlüssel (fahrrad_id) ist stabil und kurz; die Rahmennummer bleibt als UNIQUE-Attribut erhalten (leere Werte sind dabei erlaubt) und kann trotzdem gesucht werden.

• dafür (ebenfalls richtig, wenn begründet): Die Rahmennummer ist bereits eindeutig und auf dem Rad eingeschlagen; man spart ein zusätzliches Attribut und kann das Rad bei der Annahme direkt finden.

CREATE TABLE materialposition (
  auftrag_id  INT UNSIGNED      NOT NULL,
  artikel_id  INT UNSIGNED      NOT NULL,
  menge       SMALLINT UNSIGNED NOT NULL,
  einzelpreis DECIMAL(10,2)     NOT NULL,   -- Preis zum Zeitpunkt der Reparatur
  PRIMARY KEY (auftrag_id, artikel_id),
  CONSTRAINT fk_mp_auftrag FOREIGN KEY (auftrag_id) REFERENCES werkstattauftrag (auftrag_id),
  CONSTRAINT fk_mp_artikel FOREIGN KEY (artikel_id) REFERENCES artikel (artikel_id)
);

Bewertung: Spalten mit passenden Datentypen 1 Punkt, zusammengesetzter Primärschlüssel 2 Punkte, je Fremdschlüssel 1 Punkt. Die Datentypen der Fremdschlüssel müssen zu den referenzierten Primärschlüsseln passen (hier INT UNSIGNED), sonst lehnt MariaDB den Fremdschlüssel ab.

Der Werkstattauftrag verweist über den Fremdschlüssel fahrrad_id auf das Fahrrad. Würde das Fahrrad gelöscht, zeigte der Fremdschlüssel ins Leere – die referentielle Integrität wäre verletzt; deshalb verweigert MariaDB das Löschen (1 Punkt; Fehlermeldung: Cannot delete or update a parent row: a foreign key constraint fails).

Reihenfolge (2 Punkte): zuerst die abhängigen Datensätze, dann die referenzierten – arbeitsposition (und ggf. materialposition) → werkstattauftrag → fahrrad.

Alternative: Die Fremdschlüssel mit ON DELETE CASCADE anlegen. Für Werkstattaufträge ist das aber riskant, weil dann beim Löschen eines Fahrrads die Auftragshistorie verschwindet.

In Textform:

aufgabenbereich (aufgabenbereich_id (PK), bezeichnung, beschreibung, verantwortlich_id (FK → person))
schicht         (schicht_id (PK), beginn, ende, max_helfende, veranstaltung_id (FK),
                 aufgabenbereich_id (FK), raum_id (FK, darf NULL sein))
einsatz         (schicht_id (PK, FK), person_id (PK, FK), eingetragen_am, erschienen)
geraeteausgabe  (geraet_id (PK, FK), schicht_id (PK, FK¹), person_id (FK¹, NOT NULL), ausgegeben_um, zurueck_um)
                 ¹ (schicht_id, person_id) zusammen als Fremdschlüssel auf einsatz

Bewertung:

• je Tabelle mit sinnvollem Namen und Nichtschlüsselattributen 1 Punkt (4 Punkte),

• je richtiger Primärschlüssel von aufgabenbereich, einsatz und geraeteausgabe 1 Punkt (3 Punkte),

• je Fremdschlüssel 1 Punkt (8 Punkte: verantwortlich_id, veranstaltung_id, aufgabenbereich_id, raum_id, einsatz.schicht_id, einsatz.person_id, geraeteausgabe.geraet_id, geraeteausgabe.(schicht_id, person_id)),

• je Beziehung mit Kardinalität 1 Punkt (8 Punkte; 1 statt c an der Raum-Seite ist ebenfalls richtig).

Worauf es ankommt:

• einsatz löst die m:n-Beziehung zwischen person und schicht auf; eingetragen_am und erschienen gehören zur Beziehung, nicht zu einer der beiden Tabellen.

• person steht zweimal in Beziehung: als verantwortliche Lehrkraft eines Aufgabenbereichs und als Helfende in einsatz.

• raum_id ist ein optionaler Fremdschlüssel (NULL erlaubt). In Notationen mit Optionalität steht deshalb auf der Seite von raum ein c (bzw. 0..1), die Beziehung lautet c:n. Dass Schichten der Raumbetreuung tatsächlich einen Raum haben, kann das Modell allein nicht erzwingen; das prüft die Anwendung.

• Dass ein Gerät nur an eine Person der Schicht ausgegeben wird, sichert der zusammengesetzte Fremdschlüssel (schicht_id, person_id) auf einsatz – eine Beziehung zu einer Beziehung. Eine Lösung mit zwei getrennten Fremdschlüsseln auf schicht und person erfüllt diese Anforderung nicht vollständig (Abzug 1 Punkt). Der Primärschlüssel (geraet_id, schicht_id) verhindert, dass ein Gerät in einer Schicht zweimal ausgegeben wird. Wichtig: person_id muss NOT NULL sein – ist ein Teil eines zusammengesetzten Fremdschlüssels leer, prüft MariaDB den Fremdschlüssel nicht.

• Die Höchstzahl max_helfende ist ein Attribut der Schicht; die aktuelle Zahl der Eingetragenen wird nicht gespeichert, sondern gezählt.

Zwischen Person und Schicht besteht eine m:n-Beziehung. Relationale Datenbanken können sie nicht direkt speichern (ein Attribut kann nur einen Wert aufnehmen). Sie wird über eine Zwischentabelle (einsatz) aufgelöst (1 Punkt). Die Zwischentabelle enthält die Primärschlüssel beider Tabellen als Fremdschlüssel; zusammen bilden sie ihren Primärschlüssel (1 Punkt). Aus der m:n-Beziehung werden dadurch zwei 1:n-Beziehungen: person 1:n einsatz und schicht 1:n einsatz (1 Punkt).

Begründung (1 Punkt): Kardinalitäten im Relationenmodell unterscheiden nur „einer“ und „viele“ (bzw. optional/verpflichtend). Eine Mindestanzahl größer als eins betrifft mehrere Zeilen von einsatz gleichzeitig; Fremdschlüssel und Primärschlüssel prüfen aber immer nur eine einzelne Zeile.

Möglichkeiten (je 1 Punkt, zwei verlangt):

• Prüfung in der Anwendung (z. B. Warnung im Schichtplan, Schicht erst „bestätigt“, wenn zwei Einträge vorliegen),

• Abfrage bzw. View, die unterbesetzte Schichten auflistet – auch solche ohne jeden Eintrag (schicht LEFT JOIN einsatz … GROUP BY s.schicht_id HAVING COUNT(e.person_id) < 2), und regelmäßige Kontrolle durch die Verantwortlichen,

• Trigger bzw. gespeicherte Prozedur, die das Austragen verhindert, wenn danach weniger als zwei Personen übrig wären.

Der Vorschlag ist abzulehnen (1 Punkt für eine begründete Entscheidung), z. B. weil (je Argument 1 Punkt, drei verlangt):

• die Spalte nicht atomar ist – die Tabelle verletzt schon die erste Normalform,

• Namen redundant zur Tabelle person gespeichert werden: Ändert sich ein Name, muss er in allen Schichten geändert werden (Änderungsanomalie), Schreibweisen weichen ab,

• es keine Fremdschlüsselbeziehung gibt: Die Datenbank kann nicht prüfen, ob die Person existiert, und Namensgleichheiten sind nicht unterscheidbar,

• Auswertungen wie „Welche Schichten übernimmt Hannah Klein?“ oder „Wie viele Helfende hat jede Schicht?“ nur mit fehleranfälliger Textsuche (LIKE '%Klein%') möglich sind,

• die Beziehungsattribute (eingetragen_am, erschienen) und die Geräteausgabe je Person nicht mehr abgebildet werden können.

Der Fremdschlüssel raum_id wird ohne Eingabepflicht angelegt, er darf also NULL sein (1 Punkt). Im Modell kann das z. B. durch den Zusatz „NULL erlaubt“ bzw. das Fehlen von NOT NULL, durch eine Kardinalität c/0..1 auf der Raum-Seite oder (in der Krähenfußnotation) durch einen Kreis am Linienende gekennzeichnet werden (1 Punkt).

In Textform:

gruppe         (gruppe_id (PK), name, beschreibung, gegruendet_am, hobby_id (FK), leitung_id (FK → benutzer))
mitgliedschaft (benutzer_id (PK, FK), gruppe_id (PK, FK), beitritt_am (PK), austritt_am, rolle)
verifizierung  (benutzer_id (PK, FK), verfahren, geprueft_am, ergebnis)
umfrage        (gruppe_id (PK, FK), umfrage_nr (PK), frage, endet_am)
antwortoption  (gruppe_id (PK, FK¹), umfrage_nr (PK, FK¹), option_nr (PK), text)
abstimmung     (gruppe_id (PK, FK¹), umfrage_nr (PK, FK¹), benutzer_id (PK, FK))
stimme         (stimme_id (PK), gruppe_id (FK²), umfrage_nr (FK²), option_nr (FK²))
aktivitaet     (aktivitaet_id (PK), …, gruppe_id (FK, NULL erlaubt))      -- vorhandene Tabelle, erweitert
¹ zusammen Fremdschlüssel auf umfrage   ² zusammen Fremdschlüssel auf antwortoption

Bewertung: je Tabelle mit Primärschlüssel und Attributen 2 Punkte (7 Tabellen, 14 Punkte), je Beziehung mit Fremdschlüssel und Kardinalität 1 Punkt (11 Punkte). Optionale Seiten (c) müssen nicht gekennzeichnet sein.

Worauf es ankommt:

• m:n „ist Mitglied“ → Zwischentabelle mitgliedschaft mit den Beziehungsattributen. Weil dieselbe Person mehrfach beitreten darf, reicht (benutzer_id, gruppe_id) als Primärschlüssel nicht; beitritt_am gehört dazu (als DATETIME, falls ein Wiedereintritt am selben Tag möglich sein soll; alternativ: künstlicher Schlüssel mitgliedschaft_id). rolle unterscheidet nur Mitglied und Moderation – die Leitung steht in gruppe.leitung_id und wird nicht doppelt gespeichert.

• 1:n „hat Thema“ und „veranstaltet“ → Fremdschlüssel auf der n-Seite (gruppe.hobby_id, aktivitaet.gruppe_id). Weil nicht jede Aktivität zu einer Gruppe gehört, ist aktivitaet.gruppe_id optional.

• Gruppenleitung: Weil nur die aktuelle Leitung zählt, genügt ein Fremdschlüssel leitung_id in gruppe (1:n zwischen benutzer und gruppe). Müsste die Historie erhalten bleiben, wäre eine eigene Tabelle mit Zeitraum nötig.

• 1:1 „wird geprüft“ → verifizierung übernimmt den Primärschlüssel von benutzer als Primär- und Fremdschlüssel (Kardinalität 1:c, nicht jedes Konto ist verifiziert). Siehe auch Teilaufgabe b.

• Schwache Entitätstypen: umfrage wird über die Gruppe und die Umfrage-Nummer identifiziert, antwortoption über die Umfrage (zwei Attribute) und die Options-Nummer. Der Fremdschlüssel von antwortoption auf umfrage ist deshalb zusammengesetzt.

• Anonymität: abstimmung speichert nur, dass jemand abgestimmt hat (der Primärschlüssel verhindert eine zweite Abstimmung), stimme nur, was gewählt wurde – ohne Bezug zur Person. Siehe Teilaufgabe c. Zwei Regeln kann das Modell nicht allein sicherstellen: dass nur aktuelle Mitglieder abstimmen (abstimmung.benutzer_id verweist auf benutzer, nicht auf eine Mitgliedschaft) und dass zu jeder Stimme genau eine Abstimmung gehört. Beides übernimmt die Anwendung, die beide Einträge in einer Transaktion schreibt.

Varianten (je 1 Punkt, zwei verlangt):

1. Zusammenfassen in einer Tabelle: benutzer (benutzer_id (PK), …, verifizierung_verfahren, verifizierung_geprueft_am, verifizierung_ergebnis) – die Spalten bleiben bei nicht verifizierten Konten leer.

2. Zwei Tabellen mit gemeinsamem Primärschlüssel: benutzer (benutzer_id (PK), …) und verifizierung (benutzer_id (PK, FK), …).

3. Eigener Primärschlüssel und eindeutiger Fremdschlüssel: verifizierung (verifizierung_id (PK), benutzer_id (FK, UNIQUE), …) – ohne UNIQUE entstünde versehentlich eine 1:n-Beziehung.

Begründung (2 Punkte), z. B. für Variante 2: Nicht alle Konten sind verifiziert, die Verifizierungsdaten werden selten gebraucht und unterliegen eigenen Löschfristen (Datenminimierung: Nachweise der Prüfung können getrennt gelöscht werden, ohne das Konto anzufassen). Der gemeinsame Schlüssel garantiert die 1:1-Beziehung ohne zusätzliches UNIQUE. Das bisherige Feld benutzer.verifiziert ist dann ein abgeleitetes Attribut und sollte entfallen (oder bewusst als Cache dokumentiert werden), weil es sonst den Daten in verifizierung widersprechen kann. Variante 1 ist ebenfalls vertretbar, wenn begründet wird, dass sie einfacher abzufragen ist.

Erläuterung (2 Punkte): Die Teilnahme und die Wahl werden in zwei getrennten Tabellen gespeichert. abstimmung hat den Primärschlüssel (gruppe_id, umfrage_nr, benutzer_id) – ein zweiter Eintrag derselben Person für dieselbe Umfrage wird von der Datenbank abgelehnt. stimme enthält nur die gewählte Antwortoption, aber keinen Fremdschlüssel auf benutzer. Damit die Zuordnung nicht über die Reihenfolge oder Zeitstempel rekonstruiert werden kann, speichert stimme keinen genauen Zeitpunkt (und die Anwendung schreibt beide Einträge in einer Transaktion). Noch sicherer ist eine Zähltabelle (gruppe_id, umfrage_nr, option_nr, anzahl) statt einzelner Stimmen, denn auch eine fortlaufende stimme_id verrät die Reihenfolge.

Stellungnahme (2 Punkte): Der Vorschlag ist abzulehnen. Mit der benutzer_id wäre jede Stimme einer Person zuzuordnen – die Abstimmung wäre nicht mehr anonym, sondern höchstens pseudonym. Das widerspricht der Anforderung und dem Grundsatz der Datenminimierung (Art. 5 Abs. 1 lit. c DSGVO): Für die Auswertung ist die Person nicht nötig. „Nur intern“ schützt nicht vor Fehlern, Datenpannen oder neugierigen Administrator*innen.

ALTER TABLE aktivitaet
  ADD COLUMN gruppe_id INT UNSIGNED NULL,
  ADD CONSTRAINT fk_akt_gruppe FOREIGN KEY (gruppe_id)
      REFERENCES gruppe (gruppe_id) ON DELETE SET NULL;

Bewertung: neue Spalte mit passendem Datentyp und NULL erlaubt 1 Punkt, Fremdschlüssel-Constraint 1 Punkt, ON DELETE SET NULL 1 Punkt.

Änderungsanomalie (1 Punkt für das Problem, 1 Punkt für das Beispiel), z. B.: Der Haltepunkt am Gütersloher Hauptbahnhof steht in mehreren Zeilen und Spalten – sogar in unterschiedlichen Schreibweisen („Gütersloh Hbf ZOB Steig 3“, „Gütersloh Hauptbahnhof“, „Gütersloh Hbf“). Zieht der Haltepunkt an einen anderen Steig um, muss jede Fundstelle geändert werden; wird eine vergessen, sind die Daten widersprüchlich (inkonsistent). Ebenso: Bekommt Ben Richter ein anderes Auto und fährt er weitere Gemeinschaften, muss die Fahrzeugangabe in jeder dieser Zeilen geändert werden.

Je Fehler mit Begründung 2 Punkte (fünf verlangt):

1. fahrgemeinschaft.wochentage enthält mehrere Werte („Di, Fr“) – nicht atomar, verletzt die erste Normalform. Besser: eigene Tabelle fg_wochentag (fg_id, wochentag) (vertretbar ist auch der MariaDB-Datentyp SET('Mo','Di','Mi','Do','Fr'), wenn begründet).

2. fahrgemeinschaft.freie_plaetze ist ein abgeleitetes Attribut (Sitzplätze des Fahrzeugs minus Anzahl der Mitfahrenden). Es kann den tatsächlichen Daten widersprechen und wird deshalb berechnet statt gespeichert.

3. Primärschlüssel von route besteht nur aus fg_id: Damit könnte jede Fahrgemeinschaft nur einen Haltepunkt haben. Richtig: (fg_id, haltepunkt_id) oder (fg_id, reihenfolge).

4. Kardinalität haltepunkt – route ist 1:1 eingetragen; ein Haltepunkt gehört aber zu vielen Routen (z. B. Gütersloh Hbf zu zwei Gemeinschaften) – richtig ist 1:n.

5. mitfahrt.nutzer_name ist redundant: Der Name hängt über nutzer_id transitiv vom Primärschlüssel ab (Verstoß gegen die 3. Normalform, Änderungsanomalie).

6. mitfahrt.zustieg ist ein Freitext statt eines Fremdschlüssels: Die Datenbank kann nicht prüfen, ob der Haltepunkt existiert und ob die Gemeinschaft ihn überhaupt anfährt; Schreibweisen weichen ab.

7. (Schwachstelle) mitfahrt hat einen künstlichen Schlüssel ohne UNIQUE (fg_id, nutzer_id) – dieselbe Person könnte doppelt eingetragen werden.

In Textform:

fahrgemeinschaft (fg_id (PK), bezeichnung, fahrzeug_id (FK))
fg_wochentag     (fg_id (PK, FK), wochentag (PK))
route_halt       (fg_id (PK, FK), haltepunkt_id (PK, FK), reihenfolge, abfahrt)
mitgliedschaft   (fg_id (PK, FK¹), nutzer_id (PK, FK), zustieg_haltepunkt_id (FK¹, NOT NULL))
                 ¹ (fg_id, zustieg_haltepunkt_id) zusammen Fremdschlüssel auf route_halt

Bewertung: je Tabelle mit Attributen 1 Punkt (4 Punkte), je Primärschlüssel 1 Punkt (4 Punkte), je Fremdschlüssel 0,5 Punkte (6 Fremdschlüssel, 3 Punkte), je Beziehung mit Kardinalität 1 Punkt (6 Punkte: fahrzeug–fahrgemeinschaft, fahrgemeinschaft–fg_wochentag, fahrgemeinschaft–route_halt, haltepunkt–route_halt, nutzer–mitgliedschaft, route_halt–mitgliedschaft).

Hinweise:

• Fahrer*in, Fahrzeugfarbe und Platzzahl ergeben sich über fahrzeug_id aus fahrzeug und nutzer; sie werden nicht in fahrgemeinschaft wiederholt. Wer begründet, dass auch fremde Fahrzeuge gefahren werden (z. B. der Vereinsbus), darf zusätzlich fahrer_id (FK → nutzer) aufnehmen.

• Klasse und Schultage der Mitfahrenden stehen bereits in nutzer/klasse und werden nicht übernommen.

• Der zusammengesetzte Fremdschlüssel (fg_id, zustieg_haltepunkt_id) → route_halt (fg_id, haltepunkt_id) erzwingt, dass nur Haltepunkte der eigenen Route gewählt werden können. Eine Lösung mit einem einfachen Fremdschlüssel auf haltepunkt ist ein gültiges Modell in der 3. NF, erfüllt diese Anforderung aber nicht (Abzug 1 Punkt). zustieg_haltepunkt_id muss NOT NULL sein, sonst prüft MariaDB den zusammengesetzten Fremdschlüssel nicht – und weil fg_id hier keinen eigenen Fremdschlüssel auf fahrgemeinschaft hat, könnte dann sogar eine nicht vorhandene Fahrgemeinschaft eingetragen werden.

• UNIQUE (fg_id, reihenfolge) in route_halt verhindert zwei Halte an derselben Position.

Erläuterung (2 Punkte): In der dritten Normalform darf kein Nichtschlüsselattribut von einem anderen Nichtschlüsselattribut abhängen. Hier bestimmt die Postleitzahl den Ort (plz → ort), der Ort hängt also nur transitiv über plz vom Primärschlüssel haltepunkt_id ab. Ändert sich der Ortsname zu einer PLZ, müssten alle Datensätze mit dieser PLZ geändert werden.

Stellungnahme (1 Punkt), z. B.: Die Tabelle kann so bleiben. Die Abhängigkeit plz → ort gilt in der Praxis nicht streng (es gibt PLZ für mehrere Orte und Orte mit mehreren PLZ), eine eigene Tabelle postleitzahl brächte bei wenigen Haltepunkten kaum Nutzen, aber zusätzliche Verbünde. Viele Aufgaben erlauben deshalb ausdrücklich, dass PLZ und Ort in einer Tabelle stehen. Eine Auslagerung ist ebenfalls richtig, wenn sie begründet wird (z. B. bei vielen Adressen und Pflege über ein amtliches PLZ-Verzeichnis).

Je Mangel 2 Punkte (Beispiel 1 Punkt, Regel 1 Punkt), z. B.:

Andere sinnvolle Antworten sind möglich.

In Textform (vorhandene Tabellen nur mit Schlüsseln):

interessent    (interessent_id INT (PK), vorname VARCHAR(50), nachname VARCHAR(50),
                telefon VARCHAR(25) NULL, email VARCHAR(100) NULL,
                kunde_id INT (FK, UNIQUE, NULL erlaubt))
probefahrt     (probefahrt_nr INT (PK), datum DATE, dauer_min SMALLINT, kauf BOOLEAN,
                interessent_id INT (FK), mitarbeiter_id INT (FK))
probefahrt_rad (probefahrt_nr INT (PK, FK), artikel_id INT (PK, FK))

Beziehungen: kunde c : c interessent (beide Seiten optional), interessent 1 : n probefahrt, mitarbeiter 1 : n probefahrt, probefahrt 1 : n probefahrt_rad, artikel 1 : n probefahrt_rad.

Annahmen (Beispiele): Eine Probefahrt wird von genau einer Person und einer Beraterin bzw. einem Berater betreut; bei einer Probefahrt können mehrere Modelle gefahren werden (m:n über probefahrt_rad); erfasst wird das Modell (Artikel), nicht das einzelne Vorführrad; die Nummer vom Probefahrtschein ist eindeutig und wird als natürlicher Primärschlüssel übernommen; eine interessierte Person gehört höchstens zu einem Kundenkonto; nach der Verknüpfung gelten die Kontaktdaten in kunde, die Angaben in interessent werden dann nicht mehr gepflegt bzw. gelöscht. Die Akkukapazität wird nicht bei der Probefahrt gespeichert (siehe d).

Bewertung: drei neue Tabellen (3 Punkte), Primär- und Fremdschlüssel einschließlich des zusammengesetzten Schlüssels in probefahrt_rad (4 Punkte), Attribute mit passenden Datentypen, Name aufgeteilt, Kontakt aufgeteilt (4 Punkte), fünf Beziehungen mit Kardinalitäten (3 Punkte), nachvollziehbare Annahmen (2 Punkte).

Auch richtig: Interessierte direkt als Datensätze in kunde anlegen, wenn begründet wird, dass email dort dann nicht mehr verpflichtend sein darf; ein künstlicher Schlüssel probefahrt_id mit probefahrt_nr als UNIQUE-Attribut. Ein DDL-Skript zur Beispiellösung mit Testdaten liegt unter src/aufgabe6.sql.

probefahrt (je Zeile 1,5 Punkte):

probefahrt_rad (2 Punkte):

Die Akkuangaben und die Bezeichnungen der Räder werden nicht übernommen, sie stehen bereits in artikel. Spaltennamen und Werte dürfen zu Deinem Modell aus b) passen.

Die Akkukapazität hängt nur vom Modell (artikel_id) ab, nicht von der Probefahrt. In probefahrt_rad hinge sie also nur von einem Teil des zusammengesetzten Primärschlüssels ab (Verstoß gegen die zweite Normalform) und stünde bei jeder Probefahrt erneut (Redundanz) (1 Punkt). Sie gehört als Attribut in die Tabelle artikel (z. B. akku_wh) bzw. in eine Tabelle mit technischen Daten der Modelle (1 Punkt).

Je Meldung 2 Punkte (Ursache 1 Punkt, Behebung 1 Punkt):

1. Ein Datensatz mit dem Primärschlüssel 3 existiert bereits – hier die doppelte Zeile Nr. 3, die nicht entfernt wurde (oder der Import wurde zweimal gestartet). Behebung: Duplikate vor dem Import entfernen bzw. bereits importierte Datensätze überspringen.

2. Ein Fremdschlüssel verweist auf einen Datensatz, den es (noch) nicht gibt, z. B. eine probefahrt mit einer interessent_id, die noch nicht angelegt ist, oder eine falsch zugeordnete artikel_id. Behebung: in der richtigen Reihenfolge importieren – zuerst interessent, dann probefahrt, zuletzt probefahrt_rad – und die Schlüsselwerte prüfen. Die Datenbank prüft die referentielle Integrität und weist den Datensatz ab.

3. Das Datum steht noch im deutschen Format; MariaDB erwartet JJJJ-MM-TT (bzw. der Wert passt nicht zum Datentyp DATE). Die Fehlermeldung erscheint im üblichen strikten SQL-Modus; ohne ihn speichert MariaDB nur eine Warnung und 0000-00-00. Behebung: Datumsangaben vorher umwandeln, z. B. beim Import mit STR_TO_DATE('03.10.2026', '%d.%m.%Y').

Je Schritt 1 Punkt, Reihenfolge muss sinnvoll sein, z. B.:

1. Sicherungskopie des Exports anlegen (vorgegeben).

2. Die Daten analysieren, Bereinigungsregeln festlegen und die Daten bereinigen (Duplikate, Formate, Aufteilung von Namen und Kontakt, Zuordnung der Kunden-, Artikel- und Mitarbeiter-IDs).

3. Die neuen Tabellen mit SQL anlegen (interessent vor probefahrt vor probefahrt_rad), dabei Schlüssel und Datentypen festlegen.

4. Die bereinigten Daten in der richtigen Reihenfolge importieren.

5. Den Import prüfen (Anzahl der Datensätze, Stichproben gegen den Export, Fremdschlüssel, Abfrage „Kaufquote je Modell“).

6. Zugriffsrechte für das Verkaufsteam vergeben und eine Eingabemaske bzw. Shop-Funktion bereitstellen.

Weitere mögliche Schritte: Löschfrist für Daten von Interessierten ohne Kauf festlegen (Datenschutz), Datensicherung einrichten, die alte Tabellenkalkulation schreibgeschützt archivieren, Verkaufsteam einweisen.

In Textform:

themenfeld              (themenfeld_id (PK), kuerzel (UNIQUE), bezeichnung, koordination_id (FK → person))
session_themenfeld      (session_id (PK, FK), themenfeld_id (PK, FK))
gutachterzustaendigkeit (person_id (PK, FK), themenfeld_id (PK, FK))
gutachten               (session_id (PK, FK), gutachter_id (PK, FK → person), kommentar, erstellt_am)
kriterium               (kriterium_id (PK), bezeichnung)
bewertung               (session_id (PK, FK), gutachter_id (PK, FK), kriterium_id (PK, FK), punkte)
                         -- (session_id, gutachter_id) ist gemeinsam ein Fremdschlüssel auf gutachten
statusaenderung         (aenderung_id (PK), session_id (FK), status_neu, geaendert_am, geaendert_von (FK → person))

Beziehungen: person 1 : n themenfeld (koordiniert), session 1 : n session_themenfeld, themenfeld 1 : n session_themenfeld, person 1 : n gutachterzustaendigkeit, themenfeld 1 : n gutachterzustaendigkeit, session 1 : n gutachten, person 1 : n gutachten (begutachtet), gutachten 1 : n bewertung, kriterium 1 : n bewertung, session 1 : n statusaenderung, person 1 : n statusaenderung (ändert).

Bewertung: sieben neue Tabellen (7 Punkte), Primärschlüssel (4 Punkte), Fremdschlüssel einschließlich der Rollennamen und des zusammengesetzten Fremdschlüssels in bewertung (4 Punkte), Beziehungen mit Kardinalitäten (5 Punkte).

Hinweise: Die Punkte gehören in bewertung, weil sie von Einreichung, Gutachter*in und Kriterium abhängen; der Kommentar gehört in gutachten, weil es ihn je Gutachten nur einmal gibt. Die Koordination ist eine eigene 1:n-Beziehung zwischen person und themenfeld, getrennt von der m:n-Beziehung der Gutachter*innen. Auch richtig: künstliche Schlüssel (gutachten_id) mit UNIQUE (session_id, gutachter_id); Status als eigene Werteliste status (status_id, bezeichnung) mit Fremdschlüssel in statusaenderung.

Je Anforderung 2 Punkte (Anforderung 1, Lösungsweg 1):

• „Mindestens zwei Gutachten je Einreichung“: In der verwendeten Notation mit 1, c und n lässt sich „mindestens zwei“ nicht ausdrücken. Die Anwendung erlaubt den Status „angenommen“ bzw. „abgelehnt“ erst ab zwei Gutachten, oder eine Kontrollabfrage listet Einreichungen mit weniger als zwei Gutachten.

• „Keine Begutachtung eigener Einreichungen“: Die Regel vergleicht Daten aus gutachten und session_person. Sie wird in der Anwendung beim Zuteilen geprüft oder per Trigger beim Einfügen eines Gutachtens.

Auch richtig: „Punkte nur von 1 bis 5“ – über einen CHECK-Constraint lösbar (dann als Constraint, nicht als Kardinalität); „Gutachter*in muss für ein Themenfeld der Einreichung zuständig sein“ – Prüfung in der Anwendung.

Für den Vorschlag spricht: Der aktuelle Status ist aus statusaenderung ableitbar (letzter Eintrag je Session). Wird er zusätzlich in session gespeichert, ist er redundant und kann dem Protokoll widersprechen, wenn eine Änderung nur an einer Stelle eingetragen wird (1 Punkt). Dagegen spricht: Der Status wird sehr oft abgefragt (Sessionplan, Infomonitore); die Ableitung über den jeweils letzten Eintrag ist aufwendiger, und vorhandene Abfragen und Programme müssten angepasst werden (1 Punkt). Begründetes Fazit (1 Punkt), z. B.: Das Attribut entfernen und den aktuellen Status über eine Sicht (View) bereitstellen – oder es bewusst als redundante Kopie behalten, die nur gemeinsam mit dem Protokolleintrag in einer Transaktion bzw. per Trigger geändert wird.

da) Neue Tabelle für die Standortzuordnung mit Zeitraum anlegen (Skizze 2 Punkte, Beschreibung 1 Punkt):

geraetestandort (standort_id (PK), geraet_id (FK), raum_id (FK), von DATETIME, bis DATETIME NULL)

geraet 1 : n geraetestandort, raum 1 : n geraetestandort. Den Fremdschlüssel raum_id aus geraet entfernen, weil er nur einen Raum speichern kann und sonst doppelt zur neuen Tabelle gepflegt werden müsste (1 Punkt). Auch richtig: zusammengesetzter Primärschlüssel aus geraet_id und von.

db) Der aktuelle Standort ist der Datensatz eines Gerätes, bei dem bis leer (NULL) ist; bei einem Umzug wird bis auf den Umzugszeitpunkt gesetzt und ein neuer Datensatz angelegt (1 Punkt). Funktionen der Maske, z. B.: alle Geräte anzeigen, die gerade in einem Raum stehen; ein Gerät in einen anderen Raum umziehen; den bisherigen Standortverlauf eines Gerätes anzeigen (1 Punkt).

Auswirkung (1 Punkt): Mit Löschweitergabe werden beim Löschen eines Datensatzes automatisch alle Datensätze gelöscht, die per Fremdschlüssel darauf verweisen – auch über mehrere Stufen.

Stellungnahme (je Beziehung 1,5 Punkte, Fazit 1 Punkt), z. B.:

• gutachten → bewertung: sinnvoll. Bewertungen gehören untrennbar zu ihrem Gutachten; ohne Gutachten sind sie wertlos.

• person → gutachten, statusaenderung oder themenfeld: nicht sinnvoll. Löscht jemand eine Person (z. B. nach dem Barcamp), verschwänden ihre Gutachten samt Bewertungen und das Protokoll der Statusänderungen; über die Koordination würde sogar ein ganzes Themenfeld mit allen Zuordnungen gelöscht. Hier ist RESTRICT (Löschen verweigern) oder bei optionalen Beziehungen SET NULL besser.

Fazit: Löschweitergabe nicht pauschal, sondern nur für abhängige Daten ohne eigenen Wert (Bewertungen, Zuordnungstabellen wie session_themenfeld) festlegen.

Einfügen (1,5 Punkte): Eine Bewertung lässt sich nur speichern, wenn das Gutachten (gleiche session_id und gutachter_id) und das Kriterium bereits existieren; sonst meldet die Datenbank eine Verletzung der referentiellen Integrität.

Löschen (1,5 Punkte): Ein Gutachten kann nicht gelöscht werden, solange noch Bewertungen darauf verweisen (bei ON DELETE RESTRICT); erst müssen die Bewertungen gelöscht werden – oder sie werden bei ON DELETE CASCADE mitgelöscht.

Reihenfolge (2 Punkte), z. B.: kriterium und themenfeld → session_themenfeld und gutachterzustaendigkeit → gutachten → bewertung; statusaenderung jederzeit nach den vorhandenen Tabellen. Begründung: Eine Tabelle mit Fremdschlüssel kann erst angelegt werden, wenn die Tabelle existiert, auf die der Fremdschlüssel verweist (die „1“-Seite der Beziehung).