Relationenalgebra und SQL: Division

Die Division kann man sich als Gegenoperation (oder Umkehroperation) zum Kartesischen Produkt vorstellen.

Seien R und S Relationen mit den Attributmengen $\beta$ und $\gamma$.
Bei $\beta \cap \gamma =\varnothing$ gilt dann:

$T=R\times S$
$T÷S=R$

Definition

Da die Division eine abgeleitete Operation ist, definieren wir sie mit Hilfe der anderen Operationen der RA. Seien R, S Relationen und $\beta$ die zu R sowie $\gamma$ die zu S dazugehörigen Attributmengen. $R^{\prime }:=\beta \setminus \gamma$.

Die Division ist dann definiert durch:

$R÷S:={\pi }_{R^{\prime }}(R)-{\pi }_{R^{\prime }}(({\pi }_{R^{\prime }}(R)\times S)-R)$

Beispiel

Gegeben ist eine Relation R, die Väter und Mütter, deren Kinder und das Alter dieser Kinder enthält. Zusätzlich dazu ist eine Relation S gegeben, die einige Kinder und deren Alter enthält: Maria (4) und Sabine (2). Dividiert man R durch S, so erhält man als Ergebnis eine Relation, die nur noch diejenigen Ehepaare enthält, die sowohl eine Tochter Maria mit Alter 4 als auch eine Tochter Sabine mit Alter 2 haben:

R: Vater Mutter Kind Alter Hans Helga Harald 5 Hans Helga Maria 4 Hans Ursula Sabine 2 Martin Melanie Gertrud 7 Martin Melanie Maria 4 Martin Melanie Sabine 2 Peter Christina Robert 9

S: Kind Alter Maria 4 Sabine 2

R÷S Vater Mutter Martin Melanie

Umsetzmöglichkeiten

Entsprechend der Relationalen Algebra

In den folgenden SQL-Abfragen gilt:

• Tabelle 'R' = Eltern
• Tabelle 'S' = Kinder

1. Gib uns alle Elternpaare:

 SELECT DISTINCT Vater, Mutter FROM Eltern

Eltern:

Vater	Mutter
Hans	Helga
Hans	Ursula
Martin	Melanie
Peter	Christina

2. Alle Kinder, die vorhanden sein sollen (Der Einfachheit halber ohne Alter):

 SELECT Kind FROM Kinder

Kinder

Kind
Maria
Sabine

3. Kreuzprodukt aus Elternpaaren und Kindern (d.h. alle möglichen Kombinationen, die es gibt)

 SELECT Vater,Mutter,Kind 
 FROM
     (SELECT DISTINCT Vater, Mutter FROM Eltern) as Elternpaare
    ,(SELECT Kind FROM Kinder) as Kinder
    

Kreuzprodukt

Vater	Mutter	Kind
Hans	Helga	Maria
Hans	Helga	Sabine
Hans	Ursula	Maria
Hans	Ursula	Sabine
Martin	Melanie	Maria
Martin	Melanie	Sabine
Peter	Christina	Maria
Peter	Christina	Sabine

4. Entferne davon alle Einträge die, die es tatsächlich gibt:

SELECT *
FROM 
(
    SELECT Vater,Mutter,Kind 
    FROM 
      (SELECT DISTINCT Vater, Mutter FROM Eltern) as Elternpaare
     ,(SELECT Kind FROM Kinder) as Kinder
 ) AS Kreuzprodukt
WHERE NOT EXISTS 
(
     SELECT * FROM Eltern as Eltern2
     WHERE Kreuzprodukt.Kind = Eltern2.Kind
     AND Kreuzprodukt.Vater  = Eltern2.Vater
     AND Kreuzprodukt.Mutter = Eltern2.Mutter
) 

AlleMöglichkeiten

Vater	Mutter	Kind
Hans	Helga	Sabine
Hans	Ursula	Maria
Peter	Christina	Maria
Peter	Christina	Sabine

5. Anpassung der Anfrage von eben: Liste der Vater-Mutter-Kombinationen

SELECT DISTINCT Vater,Mutter 
FROM 
(
    SELECT Vater,Mutter,Kind 
    FROM 
      (SELECT DISTINCT Vater, Mutter FROM Eltern) as Elternpaare
     ,(SELECT Kind FROM Kinder) as Kinder
 ) AS Kreuzprodukt
WHERE NOT EXISTS 
(
     SELECT * FROM Eltern as Eltern2
     WHERE Kreuzprodukt.Kind = Eltern2.Kind
     AND Kreuzprodukt.Vater  = Eltern2.Vater
     AND Kreuzprodukt.Mutter = Eltern2.Mutter
) 

(Aus "SELECT *" von eben wurde ein "SELECT DISTINCT Vater,Mutter")

Eltern,die nicht beide Kinder haben

Vater	Mutter
Hans	Helga
Hans	Ursula
Peter	Christina

Wir wissen jetzt, dass diese Elternpaare *nicht* beide Kinder haben. D.h. wenn wir jetzt diese Elternpaare aus der Ursprungsliste entfernen, bleiben nur noch die Elternpaare übrig, die beide Kinder haben.

6. Elternpaare, die beide Kinder haben

SELECT DISTINCT Vater, Mutter FROM Eltern
WHERE NOT EXISTS
( 
 SELECT * 
   FROM 
   (
        SELECT DISTINCT Vater,Mutter FROM 
            (
                SELECT Vater,Mutter,Kind 
                FROM 
                  (SELECT DISTINCT Vater, Mutter FROM Eltern) as Elternpaare
                 ,(SELECT Kind FROM Kinder) as Kinder
             )
             AS Kreuzprodukt
        WHERE NOT EXISTS 
        (
             SELECT * FROM Eltern as Eltern2
             WHERE Kreuzprodukt.Kind = Eltern2.Kind
             AND Kreuzprodukt.Vater  = Eltern2.Vater
             AND Kreuzprodukt.Mutter = Eltern2.Mutter
        ) 
   ) AS KriterienTreffenNichtZu
   WHERE KriterienTreffenNichtZu.Vater = Eltern.Vater  -- Notwendigkeit dieser Klausel ist zu erklären
   AND KriterienTreffenNichtZu.Mutter = Eltern.Mutter   -- Notwendigkeit dieser Klausel ist zu erklären
)
   

AlleMöglichkeiten

Vater	Mutter
Martin	Melanie

Vereinfachte Version durch GROUP BY und COUNT

SELECT Mutter, Vater FROM Eltern
WHERE Kind IN ( SELECT Kind FROM Kinder )
GROUP BY Mutter, Vater
HAVING COUNT (*) = ( SELECT COUNT(*) FROM Kinder)