unterschiedliche logisch Beziehungen in einer DB-Tabelle speichern

[Rolf B]

Hallo Camping_RIDER,

KursA UND (KursB ODER (KursE ODER (KursC UND KursE)))

Whoa - wie soll man denn das relational abbilden?

Ich habe heute nachmittag angefangen mir Gedanken zu machen und auf meinem lokalen MySQL etwas experimentiert. Das dauerte natürlich eine Weile.

Grundsätzlich sollte man, wenn man logische Ausdrücke automatisiert verarbeiten will, eine Normalform dieser Ausdrücke anstreben. Davon gibt's zwei, nämlich die disjunktive und die konjunktive Normalform (Details siehe z.B. Wikipedia). Für mich persönlich ist die diskunktive Form leichter verständlich, darum gehe ich darauf jetzt näher ein.

Disjunktive Normalform (DNF) bedeutet, dass man einige Terme hat, die Variablen durch UND verknüpfen (Konjunktion), und diese Terme durch ein ODER (Disjunktion) verknüpft. Außerdem ist es erlaubt, Variablen vor der UND-Verknüpfung zu negieren. Auf diese Weise kann man sozusagen alle Zeilen der Wahrheitstabelle, wo im Ergebnis ein TRUE steht, mehr oder weniger gedankenfrei als UND-Terme aufschreiben und diese Terme ODER verknüpfen.

Was fängt man nun damit an? Nehmen wir an, wir hätten folgende Kurse:

KursA - HTML Grundlagen
KursB - CSS Grundlagen
KursC - Großer Webentwickler-Basiskurs
KursD - Aufbaukurs GRID

Für's Beispiel soll es so sein, dass man für KursD entweder KursA und KursB gemacht haben muss, oder KursC (der beinhaltet KursA und KursB).

Das kann man so in eine Tabelle UndTerme bringen:

Gruppe  VorKurs  Erforderlich
101     KursA    1
101     KursB    1
102     KursC    1
103     KursC    0
104     KursC    0

Die Spalte Erforderlich soll angeben, ob dieser Vorkurs vorausgesetzt wird, oder ob dieser Vorkurs zu einer Nichtbelegbarkeit führen soll. Damit wird die Negierungsmöglichkeit einer Variablen abgebildet, die in der DNF vorgesehen ist. Die Gruppe 103 besagt: Wer KursC schon belegt hat, soll etwas nicht tun können.

Die Zuordnung von Kurse zu UndTerme erfolgt mit einer Tabelle OderTerme, die angibt, welche Und-Gruppen für einen Kurs mit ODER verknüpft werden müssen:

Kurs    Gruppe
KursD   101
KursD   102
KursA   103
KursB   104

Wer KursD machen will, muss also Gruppe 101 oder Gruppe 102 erfüllen.

Wer KursA oder KursB machen will, muss Gruppe 103 bzw. 104 erfüllen. Die erfüllt man aber nur, wenn man KursC nicht gemacht hat. Konkret: Wer den Webentwickler-Basiskurs gemacht hat, kann HTML- oder CSS-Grundlagen nicht belegen. Das wäre für denjenigen nämlich nichts Neues mehr.

SELECT Gruppe, Vorkurs, Erforderlich
FROM   OderTerme ot JOIN UndTerme ut ON ot.gruppe = ut.gruppe
WHERE  ot.Kurs = 'KursD'

liefert dann

Gruppe  Vorkurs   Erforderlich
101     KursA     1
101     KursB     1
102     KursC     1

Das kann man nun Gruppe für Gruppe programmatisch auswerten, um für einen Kunden die Belegbarkeit zu prüfen. Oder Gruppe für Gruppe ausgeben, um dem Kunden die Kursvoraussetzungen anzuzeigen.

Oder mit SQL auswerten! Nehmen wir noch eine Tabelle hinzu, KursErfolg, in der für einen Kunden steht, welche Kurse er gemacht und bestanden hat (da würde natürlich im Reallife nicht "Rolf" stehen, sondern eine ID).

Kunde   Kurs   Bestanden
Rolf    KursA  1
Rolf    KursB  0

Diese Tabelle kann ich in das SQL einbeziehen:

SELECT Gruppe, Vorkurs, Erforderlich, Bestanden
FROM   OderTerme ot 
       JOIN UndTerme ut ON ot.gruppe = ut.gruppe
       LEFT JOIN KursErfolg ke ON ke.Kurs = ku.VorKurs
                                AND ke.Kunde = 'Rolf'
WHERE  ot.Kurs = 'KursD'

Ergebnis:

Gruppe  Vorkurs   Erforderlich   Bestanden
101     KursA     1              1
101     KursB     1              0
102     KursC     1              NULL

Die NULL in Zeile 3 erschwert die weitere Verarbeitung, darum ersetzen wir im SQL noch Bestanden durch COALESCE(Bestanden,0). Das schreibe ich jetzt nicht auf, jedenfalls wird dadurch ein nicht belegter Kurs wie Nichtbestanden behandelt. Und wir müssen Erforderlich mit Bestanden vergleichen, nur wenn es übereinstimmt, ist dieser Teil der UND-Bedingung erfüllt.

SELECT Gruppe, Vorkurs, Erforderlich=COALESCE(Bestanden,0) as Zutreffend
FROM   OderTerme ot 
       JOIN UndTerme ut ON ot.gruppe = ut.gruppe
       LEFT JOIN KursErfolg ke ON ke.Kurs = ku.VorKurs 
                                AND ke.Kunde = 'Rolf'
WHERE  ot.Kurs = 'KursD'

ergibt

Gruppe  Vorkurs   Zutreffend
101     KursA     1
101     KursB     0
102     KursC     0

Diese Treffermenge können wir nun gruppieren und aggregieren, um pro Und-Term nur noch eine Zeile zu bekommen. Ein UND ist falsch, sobald ein Teil davon falsch ist, wir müssen also MIN(Zutreffend) bilden. Das ist nur 1, wenn alle Teile des UND die 1 tragen. Den Vorkurs können wir jetzt nicht mehr ausgeben, der ist ja nicht gruppiert.

SELECT Gruppe, MIN(Erforderlich=COALESCE(Bestanden,0)) as Zutreffend
FROM   OderTerme ot 
       JOIN UndTerme ut ON ot.gruppe = ut.gruppe
       LEFT JOIN KursErfolg ke ON ke.Kurs = ku.VorKurs 
                                AND ke.Kunde = 'Rolf'
WHERE  ot.Kurs = 'KursD'
GROUP BY Gruppe

ergibt

Gruppe  Zutreffend
101     0
102     0

Das müssen wir nun noch verODERn. Ein ODER ist wahr, sobald ein Teilterm wahr ist. Das bekommen wir mit MAX(Zutreffend) geliefert.

SELECT MAX(og.Zutreffend)
FROM (SELECT Gruppe, MIN(Erforderlich=COALESCE(Bestanden,0)) as Zutreffend
      FROM   OderTerme ot 
           JOIN UndTerme ut ON ot.gruppe = ut.gruppe
           LEFT JOIN KursErfolg ke ON ke.Kurs = ku.VorKurs 
                                    AND ke.Kunde = 'Rolf'
      WHERE  ot.Kurs = 'KursD'
      GROUP BY Gruppe) og

Das ergibt die 0 als Ergebnis. Falls Rolf KursB bestanden hätte, wäre für Gruppe 101 der Zutreffend-Wert 1 ermittelt worden, und das große SQL liefert dann 1.

Ich hoffe, ich habe beim Übertragen von meinem Spiel-MySQL ins Forum keine SQL-Fehler eingebaut. Vollziehe es mal nach, probiere auch mal aus, was passiert, wenn ein Kunde den KursC belegt hat und Du die Voraussetzungen für KursA prüfst.

Wie Du das mit einem Editor pflegst, ist natürlich noch eine ganz andere Sache...

Rolf