unterschiedliche logisch Beziehungen in einer DB-Tabelle speichern
bearbeitet von Rolf BHallo 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.
Zwischen Kurse und UndTerme kommt nun noch eine Tabelle `OderTerme`, die angibt, welche Und-Terme für einen Kurs relevant sind:
~~~
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.
~~~SQL
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:
~~~SQL
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.
~~~SQL
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.
~~~sql
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_
--
sumpsi - posui - clusi
unterschiedliche logisch Beziehungen in einer DB-Tabelle speichern
bearbeitet von Rolf BHallo 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 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. Die Gruppe 103 besagt: Wer KursC schon belegt hat, soll etwas nicht tun können.
Zwischen Kurse und UndTerme kommt nun noch eine Tabelle `OderTerme`, die angibt, welche Und-Terme für einen Kurs relevant sind:
~~~
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.
~~~SQL
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:
~~~SQL
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.
~~~SQL
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.
~~~sql
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_
--
sumpsi - posui - clusi
unterschiedliche logisch Beziehungen in einer DB-Tabelle speichern
bearbeitet von Rolf BHallo 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 dauert 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 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. Die Gruppe 103 besagt: Wer KursC schon belegt hat, soll etwas nicht tun können.
Zwischen Kurse und UndTerme kommt nun noch eine Tabelle `OderTerme`, die angibt, welche Und-Terme für einen Kurs relevant sind:
~~~
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.
~~~SQL
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:
~~~SQL
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.
~~~SQL
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.
~~~sql
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_
--
sumpsi - posui - clusi