Erstellen der Tabelle enrollments für die Verbindung von Studierenden und Kursen

Noch ein bisschen Theorie. Lass uns die Schritte zum Aufbau einer MANY-TO-MANY-Struktur genauso detailliert durchgehen. Bereit?

Die MANY-TO-MANY-Beziehung zwischen Studierenden und Kursen kann nicht direkt in einer einzigen Tabelle dargestellt werden. Ein Student kann in mehreren Kursen eingeschrieben sein und ein Kurs kann von mehreren Studierenden besucht werden.

Um das zu lösen, erstellen wir eine Zwischentabelle enrollments, die Infos über Einschreibungen speichert, also welches Student-Kurs-Paar existiert. Das sorgt nicht nur für Datenintegrität, sondern macht es auch easy, die Funktionalität zu erweitern, z.B. ein Einschreibedatum hinzuzufügen.

Wie sieht die Tabelle enrollments aus?

Du weißt schon, dass die Tabelle enrollments der zentrale Knotenpunkt zwischen den Tabellen students und courses ist. Hier ist ihre Struktur:

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,           -- Einzigartige ID des Eintrags
    student_id INT REFERENCES students(student_id), -- Foreign Key auf die Tabelle students
    course_id INT REFERENCES courses(course_id),    -- Foreign Key auf die Tabelle courses
    enrollment_date DATE DEFAULT CURRENT_DATE   -- Wann der Student im Kurs eingeschrieben wurde
);

Schauen wir uns jede Zeile an:

• enrollment_id: Das ist der eindeutige Bezeichner für jeden Eintrag. Jeder eingeschriebene Student muss eindeutig identifiziert werden.
• student_id: Zeigt an, welcher Student eingeschrieben ist. Das ist ein Foreign Key, der auf die Tabelle students (Spalte student_id) verweist.
• course_id: Zeigt an, in welchem Kurs der Student eingeschrieben ist. Diese Spalte ist mit der Tabelle courses (Spalte course_id) verbunden.
• enrollment_date: Ein nützliches Extra, das das Einschreibedatum zeigt. Wir nutzen DEFAULT CURRENT_DATE, damit automatisch das aktuelle Datum beim Erstellen des Eintrags gesetzt wird.

Erstellen der Tabellen students und courses

Bevor wir weitermachen, stellen wir sicher, dass wir schon die Tabellen students und courses haben:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,      -- Eindeutige ID des Studenten
    name TEXT NOT NULL,                 -- Name des Studenten
    email TEXT NOT NULL UNIQUE          -- E-Mail des Studenten, damit es keine Duplikate gibt
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,       -- Eindeutige ID des Kurses
    title TEXT NOT NULL,                -- Titel des Kurses
    description TEXT,                   -- Beschreibung des Kurses
    start_date DATE                     -- Startdatum des Kurses
);

Beachte, dass wir hier nützliche Details hinzugefügt haben, z.B. eindeutige E-Mails für Studierende und eine Kursbeschreibung in der Tabelle courses.

Alles miteinander verbinden

Jetzt, wo unsere Tabellen bereit sind, erstellen wir die Tabelle enrollments:

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,           -- Eindeutige ID der Einschreibung
    student_id INT NOT NULL REFERENCES students(student_id), -- Foreign Key
    course_id INT NOT NULL REFERENCES courses(course_id),    -- Foreign Key
    enrollment_date DATE DEFAULT CURRENT_DATE   -- Einschreibedatum
);

Daten in die Tabellen einfügen

Okay, die Tabellen sind fertig, aber sie langweilen sich ohne Daten. Lass uns ein paar Studierende, Kurse und deren Einschreibungen hinzufügen:

Studierende einfügen:

INSERT INTO students (name, email)
VALUES
    ('Alex Lin', 'alex.lin@example.com'),
    ('Maria Chi', 'maria.chi@example.com'),
    ('Otto Song', 'otto.song@example.com');

Kurse einfügen:

INSERT INTO courses (title, description, start_date)
VALUES
    ('Programmierung Grundlagen', 'Kurs für angehende Programmierer.', '2023-11-01'),
    ('Datenbanken', 'Wir lernen SQL und relationale Datenbanken.', '2023-11-15'),
    ('Web-Entwicklung', 'Erstellung von Websites und Web-Apps.', '2023-12-01');

Einschreibungen einfügen:

INSERT INTO enrollments (student_id, course_id)
VALUES
    (1, 1), -- Alex Lin in "Programmierung Grundlagen"
    (1, 2), -- Alex Lin in "Datenbanken"
    (2, 2), -- Maria Chi in "Datenbanken"
    (3, 3); -- Otto Song in "Web-Entwicklung"

Hier stimmen student_id und course_id mit den IDs in den jeweiligen Tabellen überein.

Beziehungen mit Queries prüfen

Alle Einschreibungen abfragen:

SELECT e.enrollment_id, s.name AS student_name, c.title AS course_title, e.enrollment_date
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id;

Ergebnis:

Aufgabe zum Selbstüben

Versuch mal, noch ein paar Studierende und Kurse hinzuzufügen und sie dann in die Tabelle enrollments einzuschreiben. Zum Beispiel: Füge den Kurs "Maschinelles Lernen" hinzu und schreib dort 1-2 Studierende ein. Nutze die oben stehende Query, um das Ergebnis zu prüfen.

Mögliche typische Fehler

Beim Arbeiten mit Foreign Keys und Zwischentabellen gibt es ein paar Fallen, in die man leicht tappen kann:

1. Kein Eintrag in der übergeordneten Tabelle: Wenn du versuchst, einen Eintrag in enrollments mit einem student_id oder course_id hinzuzufügen, die es in den Tabellen students oder courses nicht gibt, bekommst du einen Fehler. Der Foreign Key passt da streng auf.

2. Verletzung der Datenintegrität beim Löschen: Wenn du einen Studenten oder Kurs löschst, die schon in der Tabelle enrollments verwendet werden, ohne ON DELETE CASCADE zu setzen, führt das zu einem Fehler.

3. Doppelte Einträge: Achte darauf, dass du nicht denselben Studenten mehrfach in denselben Kurs einschreibst, falls das nicht von der Business-Logik vorgesehen ist.

Jetzt hast du ein funktionierendes Modell, um eine MANY-TO-MANY-Beziehung zwischen Studierenden und Kursen in PostgreSQL darzustellen. Diese Struktur wird super oft in echten Anwendungen genutzt, wie z.B. Lernmanagementsystemen, CRM und vielen anderen. Weiter zur nächsten Vorlesung!