Zum Inhalt springen

Relationenalgebra und SQL: Division

Aus Wikibooks


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

Seien R und S Relationen mit den Attributmengen und .
Bei gilt dann:


Definition

[Bearbeiten]

Da die Division eine abgeleitete Operation ist, definieren wir sie mit Hilfe der anderen Operationen der RA. Seien R, S Relationen und die zu R sowie die zu S dazugehörigen Attributmengen. .

Die Division ist dann definiert durch:

Beispiel

[Bearbeiten]

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

[Bearbeiten]

Entsprechend der Relationalen Algebra

[Bearbeiten]

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

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