Übung: Schulverwaltung – ER-Modell, Spezialisierung, SQL

Diese Datei ist so aufgebaut, dass die Lösungen jeweils in aufklappbaren Bereichen versteckt sind.
Die Schülerinnen und Schüler sehen zunächst nur die Aufgaben und können bei Bedarf die Lösung aufklappen.

1. Szenario (zum Nachlesen)

Ein Gymnasium benötigt ein System zur Verwaltung von Lehrkräften, Schülern und Kursen:

  • Personen: Lehrer und Schüler haben Namen und eine eindeutige ID (LNr / SNr).
  • Unterricht: Lehrer unterrichten Mathe/Informatik in Klassen (8–12).
    • Lehrer haben oft mehrere Klassen, Klassen haben mehrere Lehrer → n:m-Beziehung.
    • Wichtig: Zeitpunkt und Raum des Unterrichts müssen gespeichert werden.
  • Vertretung: Lehrer können Kollegen vertreten → rekursive 1:n-Beziehung (ein Lehrer ist fester Vertreter für mehrere Kollegen).
  • Spezialkurse: Die Kurse „IT-KI“ und „Höhere Mathematik“ finden wöchentlich statt.
    • Schüler können sich in beliebig viele dieser Kurse einschreiben (n:m).
    • Jeder Kurs hat genau einen leitenden Lehrer (1:n).

2. Aufgabe A: ER-Modell entwerfen

2.1 Auftrag an die Lernenden

  1. Identifiziere die benötigten Entitäten (z.B. Lehrer, Schüler, Klasse, Kurs, …).
  2. Bestimme sinnvolle Schlüsselattribute.
  3. Lege die Beziehungen (inkl. Kardinalitäten) fest:
    • Unterricht zwischen Lehrer und Klasse
    • Vertretung zwischen Lehrern
    • Spezialkurs(e) und Einschreibung von Schülern
  4. Zeichne ein ER-Diagramm (z.B. Chen-Notation) mit:
    • Entitäten
    • Attributen
    • Beziehungen (mit Kardinalität)
    • Spezialisierung von Person → Lehrer / Schüler

2.2 Musterlösung ER-Modell (textuell)

ER-Modell – Lösung anzeigen

Entitäten

  • Person(PersonID, Name, Typ)
    • PersonID (Primärschlüssel)
    • Name
    • Typ (z.B. ‘L’ für Lehrer, ‘S’ für Schüler)
  • Lehrer(PersonID, LNr, Fachrichtung, …)
    • PersonID (PK, zugleich FK auf Person)
    • LNr (eindeutige Lehrernummer)
    • weitere Attribute nach Bedarf (z.B. Fachrichtung)
  • Schueler(PersonID, SNr, Jahrgang, …)
    • PersonID (PK, zugleich FK auf Person)
    • SNr (eindeutige Schülernummer)
    • Jahrgang
  • Klasse(KlassenID, Stufe, Bezeichnung)
    • KlassenID (PK)
    • Stufe (8–12)
    • Bezeichnung (z.B. “10B”)
  • Spezialkurs(KursID, Titel, Wochentermin, LeitenderLehrerID)
    • KursID (PK)
    • Titel (z.B. „IT-KI“, „Höhere Mathematik“)
    • Wochentermin (z.B. „Montag 8:00–9:30“)
    • LeitenderLehrerID (FK auf Lehrer.PersonID)

Beziehungen

  • UNTERRICHTET (Lehrer ↔︎ Klasse, n:m)
    • Realisiert durch eigene Tabelle Unterricht
    • Attribute: LehrerID (FK), KlassenID (FK), Fach, Raum, Zeit
  • VERTRETUNG (Lehrer ↔︎ Lehrer, rekursiv 1:n)
    • Ein Lehrer ist fester Vertreter für mehrere Kollegen
    • Tabelle Vertretung(VertreterID, VertretenerID)
  • LEITET (Lehrer ↔︎ Spezialkurs, 1:n)
    • In Spezialkurs durch Attribut LeitenderLehrerID
  • EINGESCHRIEBEN (Schueler ↔︎ Spezialkurs, n:m)
    • Tabelle Einschreibung(SchuelerID, KursID, Anmeldedatum)

3. Aufgabe B: Spezialisierung Person – Lehrer / Schüler

3.1 Auftrag an die Lernenden

  1. Definiere Person als Obertyp mit gemeinsamen Attributen.
  2. Definiere Lehrer und Schueler als Untertypen.
  3. Bestimme:
    • Ist die Spezialisierung total oder partiell?
    • Ist sie disjunkt oder überlappend?
  4. Begründe deine Entscheidung.

3.2 Musterlösung Spezialisierung

Spezialisierung – Lösung anzeigen
  • Obertyp:
    • Person(PersonID, Name, Typ)
  • Untertypen:
    • Lehrer(PersonID, LNr, …)
    • Schueler(PersonID, SNr, Jahrgang, …)
  • Totalität:
    • Total: Jede Person im System ist entweder Lehrer oder Schüler.
    • Es gibt keine „anonyme“ Person ohne Rolle.
  • Disjunktheit:
    • Disjunkt: Eine Person ist entweder Lehrer oder Schüler, nicht beides.
    • Im gegebenen Szenario sind Doppelrollen (z.B. Lehrkraft UND gleichzeitig Schüler) ausgeschlossen.

4. Aufgabe C: Relationales Schema und Demodaten

4.1 Auftrag an die Lernenden

  1. Überführe das ER-Modell in ein relationales Schema (Tabellen).
  2. Lege Primärschlüssel und Fremdschlüssel fest.
  3. Definiere die Tabellen mit SQL CREATE TABLE.
  4. Füge einige Demodaten mit INSERT INTO ein, damit Abfragen getestet werden können.

4.2 Musterlösung: CREATE TABLE

CREATE TABLE – Lösung anzeigen

```sql CREATE TABLE Person ( PersonID INT PRIMARY KEY, Name VARCHAR(100) NOT NULL, Typ VARCHAR(10) NOT NULL – ‘L’ oder ‘S’ );

CREATE TABLE Lehrer ( PersonID INT PRIMARY KEY, LNr INT UNIQUE NOT NULL, Fachrichtung VARCHAR(100), FOREIGN KEY (PersonID) REFERENCES Person(PersonID) );

CREATE TABLE Schueler ( PersonID INT PRIMARY KEY, SNr INT UNIQUE NOT NULL, Jahrgang INT, FOREIGN KEY (PersonID) REFERENCES Person(PersonID) );

CREATE TABLE Klasse ( KlassenID INT PRIMARY KEY, Stufe INT CHECK (Stufe BETWEEN 8 AND 12), Bezeichnung VARCHAR(20) );

CREATE TABLE Spezialkurs ( KursID INT PRIMARY KEY, Titel VARCHAR(100) NOT NULL, Wochentermin VARCHAR(50) NOT NULL, LeitenderLehrerID INT NOT NULL, FOREIGN KEY (LeitenderLehrerID) REFERENCES Lehrer(PersonID) );

CREATE TABLE Unterricht ( LehrerID INT NOT NULL, KlassenID INT NOT NULL, Fach VARCHAR(50) NOT NULL, Raum VARCHAR(20) NOT NULL, Zeit VARCHAR(50) NOT NULL, PRIMARY KEY (LehrerID, KlassenID, Fach, Zeit), FOREIGN KEY (LehrerID) REFERENCES Lehrer(PersonID), FOREIGN KEY (KlassenID) REFERENCES Klasse(KlassenID) );

CREATE TABLE Vertretung ( VertreterID INT NOT NULL, VertretenerID INT NOT NULL, PRIMARY KEY (VertreterID, VertretenerID), FOREIGN KEY (VertreterID) REFERENCES Lehrer(PersonID), FOREIGN KEY (VertretenerID) REFERENCES Lehrer(PersonID) );

CREATE TABLE Einschreibung ( SchuelerID INT NOT NULL, KursID INT NOT NULL, Anmeldedatum DATE, PRIMARY KEY (SchuelerID, KursID), FOREIGN KEY (SchuelerID) REFERENCES Schueler(PersonID), FOREIGN KEY (KursID) REFERENCES Spezialkurs(KursID) );