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 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:
|
|
|
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
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
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
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 )
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")
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 )
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)