Datenbankaufgabe Kursverwaltung

Ein Lerninstitut veranstaltet Kurse zu verschiedenen Themen. An diesen Kursen nehmen  verschiedene Personen teil. Zur Verwaltung soll nun eine Datenbank entworfen werden. Erfasst  werden folgende Daten (noch ungeordnet):

  • Vorname
  • Nachname
  • Adresse
  • Kursbezeichnung
  • Bezahlt
  • Art der Teilnahme/Beteiligung am Kurs
  • Thema
  • Kursinhalte im Detail
  • Kursort
  • Preis
  • Beginn (Datum/Zeit)
  • Ende (Datum/Zeit)

Diese Daten sind nun als Felder geeignet auf Tabellen aufzuteilen. Nimmt man die für die Struktur wichtigsten Felder heraus, so würden sie zum Beispiel die folgende Tabelle ergeben:

Sowohl Personen als auch Kurse kommen mehrfach vor. Um das zu vermeiden, könnte man nun Personen und Kurse auf zwei Tabellen aufteilen:

 

Die Frage ist nun: Wo wird die Teilnahme einer Person an einem Kurs eingetragen?

  • P_ID als Fremdschlüssel in der Kurstabelle würde bedeuten, dass an einem Kurs nur eine Person beteiligt sein kann. Da aber ein Kurs davon lebt, dass es mehrere Teilnehmer und mindestens einen Trainer gibt, ist das so nicht möglich.
  • K_ID als Fremdschlüssel in der Personentabelle würde bedeuten, dass jede Person nur genau einen Kurs besuchen kann. Die wäre wohl eine unzumutbare Einschränkung.

Zwischen zwei Tabellen ist nur eine 1:n-Relation möglich. In diesem Fall benötigt man eine n:n-Relation. Diese kann nur durch Hinzufügen einer weiteren Zwischentabelle realisiert werden. Diese Tabelle regelt die Beteiligung am Kurs und soll daher auch so bezeichnet werden.

Umsetzung in M$ Access 2007

Umsetzung mit Libre/OpenOfficeBase

Neue Datenbank: Name_Kurs.odb

Tabellen in Entwurfansicht erstellen

Definition der Kurstabelle

Defintion Beteiligungstabelle

Extras – Beziehungen

Beziehungen zwischen den Tabellen festlegen

Nun kann man die die Tabellen mit entsprechenden Daten füllen.

Abfragen

Aufgabenstellungen für Abfragen können nun sein:

  1. Alle Teilnehmer eines bestimmten Kurses
  2. Teilnehmer, die einen oder mehrere Kurse noch nicht bezahlt haben, Trainer müssen nicht zahlen!
  3. Kurse, für die es noch keine Teilnehmer gibt
  4. Alle Kurse, die ein bestimmter Teilnehmer belegt hat
  5. Alle Trainer und ihre Kurse

Erweiterung

Die Personendaten stehen nur in einer einzigen Tabelle, egal ob es sich dabei zum Beispiel um Kursteilnehmer, Tutoren oder Vortragende handelt. Es kann ja eine Person in einem Kurs  Teilnehmer, in einem anderen Vortragender sein. Trotzdem kann man sie in Ihrer Funktion unterscheiden. Diese Funktion soll nun in eine weitere Tabelle ausgelagert werden.

Mögliche Abfragen

  1. Daten der Personen, die an einem bestimmten Kurs teilnehmen.
    SELECT P_Name AS Name, P_Vorname AS Vorname, P_Adresse1 AS Strasse, P_Adresse2 AS Ort, Geschlecht, B_K_FID AS Kurs FROM beteiligung, person WHERE P_ID=B_P_FID AND B_K_FID=1 AND B_Rolle=1
  2. Daten der Kurse, an denen eine bestimmte Person teilgenommen hat.
    SELECT K_Bezeichnung, K_Thema, K_Inhalt, K_Ort, K_Preis, K_Beginn, K_Ende
    FROM kurs, beteiligung, person WHERE P_ID=B_P_FID AND K_ID=1 AND P_ID=1
  3. Alle Trainer und Kurse, die sie leiten.
    SELECT P_Vorname, P_Name, K_Bezeichnung, K_Thema, K_Inhalt, K_Ort, K_Preis, K_Beginn, K_Ende
    FROM kurs, beteiligung, person WHERE P_ID=B_P_FID AND K_ID=B_K_FID AND B_Rolle=2
  4. Alle Teilnehmer, die noch nicht bezahlt haben.
    SELECT P_Vorname, P_Name FROM beteiligung, person
    WHERE P_ID = B_P_FID AND B_Rolle =1 AND B_bezahlt = 'nein'
  5. Alle Trainer, die an anderen Kursen teilgenommen haben.
    SELECT P_Vorname, P_Name FROM beteiligung, person, beteiligung Teilnehmer
    WHERE P_ID = beteiligung.B_P_FID AND beteiligung.B_P_FID = Teilnehmer.B_P_FID
    AND beteiligung.B_Rolle =2 AND Teilnehmer.B_Rolle =1
  6. Alle Kurse und die Anzahl der Teilnehmer.
    SELECT K_Bezeichnung , Count( P_ID ) AS 'Anzahl der Teilnehmer'
    FROM kurs, person LEFT JOIN beteiligung ON K_ID = B_K_FID
    WHERE P_ID = B_P_FID AND B_Rolle =1 GROUP BY K_ID
  7. Alle Personen und die Anzahl der Kurse, an denen sie teilgenommen haben.
    SELECT P_Name , Count(K_ID) AS 'Anzahl Kurse' FROM person , beteiligung
    WHERE P_ID = B_P_FID GROUP BY P_ID
  8. Alle Kurse, für die weniger als 4 Personen angemeldet sind.
    SELECT K_bezeichnung , Count(B_K_FID) AS 'Anzahl Kurse' FROM kurs, beteiligung
    WHERE K_ID = B_K_FID GROUP BY K_ID Having Count(B_K_FID)<4
  9. Personen, die keine Teilnehmer sind, sondern nur andere Rollen haben.
    SELECT P_Name, R_Bezeichnung, B_Rolle AS 'Anzahl Kurse' FROM beteiligung, person, rolle
    WHERE P_ID = B_P_FID AND R_ID = B_Rolle AND B_Rolle <> 1 GROUP BY B_Rolle
  10. Formulieren Sie eine weitere Abfrage Ihrer Wahl.
    SELECT K_Bezeichnung AS 'Kurs' , P_Name AS 'Trainer' FROM beteiligung, person, kurs
    WHERE P_ID = B_P_FID AND K_ID = B_K_FID AND B_Rolle = 2