9. Lehreinheit

Vertiefung SQL-Joins

Zur Vertiefung des Erlernten werden wir im Folgenden eine etwas komplexere Datenbankabfrage über mehrere Tabellen erarbeiten. Ziel unserer Beispielabfrage ist es die Grabstätten mit den meisten darin befindlichen Habsburgern zu ermitteln. Dabei sollen nur Bestattete berücksichtigt werden, die ausschließlich an einem Ort liegen.

Die Verbindung der Personen mit ihren Grabstätten erfolgt über die Verknüpfungstabelle "begraben". Zum besseren Verständnis gehen wir Schritt für Schritt vor.

• Zunächst joinen wir nur die Tabelle "personen" mit der Tabelle "begraben":
  

SELECT personen.name, personen.funktion, begraben.id_grabstaette, begraben.bestattungsart, begraben.komplett FROM personen INNER JOIN begraben ON personen.id_person = begraben.id_person;

---

• Im nächsten Schritt fügen wir in einem zweiten Schritt die Grabstättenbezeichnung hinzu:
  

SELECT personen.name, personen.funktion, begraben.bestattungsart, begraben.komplett, grabstaette.name, grabstaette.geokoordinate1, grabstaette.geokoordinate2 FROM personen INNER JOIN (begraben INNER JOIN grabstaette ON begraben.id_grabstaette = grabstaette.id_grabstaette) ON personen.id_person = begraben.id_person

---

• Nun möchten wir noch den Ort der Grabstätte mit einfließen lassen:
  

SELECT personen.name, personen.funktion, begraben.bestattungsart, begraben.komplett, grabstaette.name, ort.ortsname, grabstaette.geokoordinate1, grabstaette.geokoordinate2 FROM personen INNER JOIN (begraben INNER JOIN (grabstaette INNER JOIN ort ON grabstaette.id_ort = ort.id_ort) ON begraben.id_grabstaette = grabstaette.id_grabstaette) ON personen.id_person = begraben.id_person

---

• Wie wir in den vorangehenden Einheiten zu SELECT-Abfragen gelernt haben, können wir die Abfrage nun noch weiter spezifizieren, z.B. durch eine WHERE-Klausel. Wir möchten uns im Folgenden nur noch diejenigen Fälle ausgeben lassen, bei denen es sich um eine Körperbestattung (= "Typ 1") handelt:
  

SELECT personen.name, personen.funktion, begraben.bestattungsart, begraben.komplett, grabstaette.name, ort.ortsname, grabstaette.geokoordinate1, grabstaette.geokoordinate2 FROM personen INNER JOIN (begraben INNER JOIN (grabstaette INNER JOIN ort ON grabstaette.id_ort = ort.id_ort) ON begraben.id_grabstaette = grabstaette.id_grabstaette) ON personen.id_person = begraben.id_person WHERE bestattungsart = "1"

---

• In der nun ausgegebenen Ergebnisliste finden sich aber noch Fälle, bei denen zwar der Körper begraben wurde, jedoch nicht komplett, d.h. andere Körperteile (Herz, Eingeweide) liegen andernorts. Filtern wir diese Fälle nun noch heraus, um nur die Ganzkörperbestattungen zu erfassen:
  

SELECT personen.name, personen.funktion, begraben.bestattungsart, begraben.komplett, grabstaette.name, ort.ortsname, grabstaette.geokoordinate1, grabstaette.geokoordinate2 FROM personen INNER JOIN (begraben INNER JOIN (grabstaette INNER JOIN ort ON grabstaette.id_ort = ort.id_ort) ON begraben.id_grabstaette = grabstaette.id_grabstaette) ON personen.id_person = begraben.id_person WHERE bestattungsart = "1" AND komplett = "1"

---

Merke: Bei zunehmender Komplexität der Abfrage nimmt die Zahl der Ergebnisse entsprechend ab.

---

• Im nächsten Schritt möchten wir nun herausfinden, wie viele Personen in den einzelnen Grabstätten begraben liegen. Hierzu greifen wir auf die COUNT-Funktion zurück und gruppieren das Ergebnis nach den Grabstätten:
  

SELECT personen.name, personen.funktion, begraben.bestattungsart, begraben.komplett, grabstaette.name, COUNT(*) "Anzahl", ort.ortsname, grabstaette.geokoordinate1, grabstaette.geokoordinate2 FROM personen INNER JOIN (begraben INNER JOIN (grabstaette INNER JOIN ort ON grabstaette.id_ort = ort.id_ort) ON begraben.id_grabstaette = grabstaette.id_grabstaette) ON personen.id_person = begraben.id_person WHERE bestattungsart = "1" AND komplett = "1" GROUP BY grabstaette.name

---

• Das Ergebnis erscheint noch nicht zufriedenstellend, da wir jeweils nur den ersten Fall angezeigt bekommen. Daher müssen wir noch eine weitere Funktion verwenden: GROUP_CONCAT():
  

SELECT begraben.bestattungsart, begraben.komplett, grabstaette.name, COUNT(*) "Anzahl", GROUP_CONCAT(personen.name, personen.funktion), ort.ortsname, grabstaette.geokoordinate1, grabstaette.geokoordinate2 FROM personen INNER JOIN (begraben INNER JOIN (grabstaette INNER JOIN ort ON grabstaette.id_ort = ort.id_ort) ON begraben.id_grabstaette = grabstaette.id_grabstaette) ON personen.id_person = begraben.id_person WHERE bestattungsart = "1" AND komplett = "1" GROUP BY grabstaette.name

---

• Schöner wird es, wenn wir noch ein Leerzeichen samt Komma zwischen Namen und Titel einfügen; außerdem können wir die GROUP_CONCAT-Spalte als "Personen" bezeichnen:
  

SELECT begraben.bestattungsart, begraben.komplett, grabstaette.name, COUNT(*) "Anzahl", GROUP_CONCAT(personen.name, ', ', personen.funktion SEPARATOR '\n') "Personen", ort.ortsname, grabstaette.geokoordinate1, grabstaette.geokoordinate2 FROM personen INNER JOIN (begraben INNER JOIN (grabstaette INNER JOIN ort ON grabstaette.id_ort = ort.id_ort) ON begraben.id_grabstaette = grabstaette.id_grabstaette) ON personen.id_person = begraben.id_person WHERE bestattungsart = "1" AND komplett = "1" GROUP BY grabstaette.name

---

• Zu guter Letzt können wir noch nach der Anzahl sortieren und auch innerhalb der Spalte "Personen" eine Sortierung einfügen, sinnvollerweise nach dem Namen der Person:
  

SELECT begraben.bestattungsart, begraben.komplett, grabstaette.name, COUNT(*) "Anzahl", GROUP_CONCAT(personen.name, ', ', personen.funktion ORDER BY personen.name SEPARATOR '\n') "Personen", ort.ortsname, grabstaette.geokoordinate1, grabstaette.geokoordinate2 FROM personen INNER JOIN (begraben INNER JOIN (grabstaette INNER JOIN ort ON grabstaette.id_ort = ort.id_ort) ON begraben.id_grabstaette = grabstaette.id_grabstaette) ON personen.id_person = begraben.id_person WHERE bestattungsart = "1" AND komplett = "1" GROUP BY grabstaette.name ORDER BY Anzahl DESC

---

• Optional können wir noch als Kriterium angeben, dass nur die Grabstätten ausgegeben werden sollen, bei denen mehr als 3 Personen begraben liegen (HAVING-Funktion):
  

SELECT begraben.bestattungsart, begraben.komplett, grabstaette.name, COUNT(*) "Anzahl", GROUP_CONCAT(personen.name, ', ', personen.funktion ORDER BY personen.name SEPARATOR '\n') "Personen", ort.ortsname, grabstaette.geokoordinate1, grabstaette.geokoordinate2 FROM personen INNER JOIN (begraben INNER JOIN (grabstaette INNER JOIN ort ON grabstaette.id_ort = ort.id_ort) ON begraben.id_grabstaette = grabstaette.id_grabstaette) ON personen.id_person = begraben.id_person WHERE bestattungsart = "1" AND komplett = "1" GROUP BY grabstaette.name Having Anzahl > 3 ORDER BY Anzahl DESC

Ergebnisabfrage unmittelbar über die phpMyAdmin-Oberfläche visualisieren:

• Klicken Sie auf "Diagramm anzeigen"
• Wählen Sie bei x-Achse "name" und bei Reihe "Anzahl" an
• Wählen Sie als Diagrammtyp "Torte" aus
• Vergeben Sie eine Überschrift
• In der rechten oberen Ecke der Darstellung finden Sie die Option, das Diagramm als Bild abzuspeichern

Fortsetzung 2: Datenstrukturierung unseres Mapping-Projekts

Vorgehen Phase "Personen"

• Personen werden in die Tabelle "personen" eingetragen; es werden sowohl die fettgedruckten Namen wie auch die darunter befindlichen Eltern/Ehepartner erfasst (Hier gilt es vorsichtig zu sein, um Redundanzen zu vermeiden!)
• Name wird - sofern korrekt - von Lauro übernommen
• Als Funktion (Titel) greifen wir auf Wikipedia zurück und tragen den jeweils höchsten erreichten Titel ein
• Zeitliche Angaben werden als "unsicher" (1) oder "sicher" (2) gekennzeichnet
• Bei Geburts- und Sterbeort binden wir die Orte aus der entsprechenden Tabelle via Fremdschlüssel ein. Fehlt der entsprechende Ort, so muss er zunächst in der Tabelle "ort" nachgetragen werden
• Metainformationen einbinden: GND-Nummer (via Wikipedia); Kaiserhof-ID; Wikidata-Seite via Q-Nummer: Wikipedia-Artikel → Werkzeuge → Wikidata-Datenobjekt → Q-Nummer befindet sich neben dem Namen; gibt es keinen Eintrag bei Wikipedia, kann in die vorgesehene Spalte ("link_alt") eine andere (vertrauenswürdige) Webseite verlinkt werden
• Abbildungen zu den Personen können entweder über die Kaiserhof-ID oder über das Wikidata-Objekt bezogen werden.

Vorgehen Phase "Verknüpfungstabellen"

• Verknüpfung der Personen mit den Grabstätten in der Tabelle "begraben"
  • Personen und Grabstätten nach den Angaben von Lauro über ihre ID zuordnen
  • ENUM-Auswahlfeld "bestattungsart" (1 = Körper; 2 = Herzurne; 3 = Intestinaurne; 4 = unbekannt)
  • ENUM-Auswahlfeld "komplett": 1 = ja, 2 = nein (Spezifierung der Körperbestattung)
• Verknüpfung der Beziehungen der Personen in der Tabelle "beziehung"
  • Personen nach den Angaben von Lauro über ihre ID zuordnen (vgl. Abbildungen)
  • Fettgedruckte Person den Eltern zuordnen (ENUM-Auswahlfeld "beziehungstyp": "ist Ehepartner von" (1), "ist Kind von" (2)
  • Spalte "ergaenzungen" für Zusatzinformationen (z.B. "zweite Ehefrau von..")