1 Übersicht
Bei einer ordentlichen Modellierung sind relevante Daten häufig über mehrere Tabellen verteilt. Um diese Daten zusammenzutragen und weiterzuverarbeiten, muss man die Tabellen geeignet miteinander kombinieren. Wie das geht, lernst du in diesem Kurs.
Vorwissen:
Danach kannst du:
Abfragen in SQL schreiben, die über mehrere Tabellen hinweg gehen
Den Unterschied zwischen einem natürlichen Verbund und einem kartesischen Produkt erklären
Kursdauer: 90 Min
2 Andere Nutzer schauten auch...
Ein Streaminganbieter sammelt und analysiert das Filmschau-Verhalten seiner Kunden, um ihm und anderen Kunden geeignete Vorschläge zu machen. Dazu erfasst es alle Filme, die ein Nutzer ansieht und erstellt mithilfe eines Algorithmus Empfehlungen.
Damit Familien, die ihren Account zusammen nutzen, individuelle Vorschläge bekommen können, sind die Zahlungsdaten von den Nutzerinformationen getrennt.
Das vereinfachte Klassendiagramm sieht so aus:
3 Ein Blick in die Datenbank
Wenn du nochmal üben möchtest, wie man ausgehend vom Klassendiagramm zu einem Datenbankschema kommt und wie die Beziehungen zwischen den Klassen übertragen werden, kannst du diese Aufgabe bearbeiten:
Laden
4 Vielschauer oder Schummler?
Die Finanzabteilung stellt die Vermutung auf, dass der Großteil der Nutzer, die mehr als 2000 Stunden gestreamt haben, in ihren Zahlungsdaten das Preismodell "Single" haben. Die hohen Nutzungswerte kommen davon, dass mehrere Leute sich über einen Nutzeraccount einloggen, um Geld zu sparen.
Der Datenbankadministrator soll diese Vermutung überprüfen. Er öffnet die Tabelle mit allen Nutzern.
Laden
5 Der Schlüssel zum Sucherfolg
Die Ergebnistabelle nach der Abfrage präsentiert so einige Informationen, aber nicht das Zahlungsmodell, wie du in diesem Ausschnitt sehen kannst:
Jetzt ist es an der Zeit, die Fremdschlüssel-Eigenschaft auszunutzen, die bei der Übertragung ins Datenbankschema zwischen NUTZER und ZAHLUNGSDATEN angelegt wurde:
Der Attributwert, der in NUTZER in der Spalte ZAHLUNGSDATEN_KundenID liegt, kann dazu genutzt werden, um in der Tabelle ZAHLUNGSDATEN den Accountbesitzer eindeutig zu identifizieren! Schau es dir auf der nächsten Seite an.
6 Die große Suche
Durch die Fremdschlüsselbeziehung kann man die beiden Tabellen NUTZER und ZAHLUNGSDATEN nebeneinander legen und die passenden Paare finden:
Eine gleiche Farbe bedeutet, dass der Schlüssel von ZAHLUNGSDATEN und der Fremdschlüssel in NUTZER übereinstimmen und somit der Nutzer, der mehr als 2000 Nutzungsstunden hat, den Zahlungsdaten und dem Preismodell zugeordnet werden kann.
Für kleine Tabellen ist es vielleicht noch machbar, sich die Datensätze händisch zusammenzusuchen, doch in großen Datenbanken, wie denen von Streaminganbietern, ist das keine Option. Deshalb muss mit SQL Abfragen eine Verbindung zwischen den Tabellen hergestellt werden!
7 Der erste Versuch
Laden
8 Das kartesische Produkt
Wenn du die Aufgabe auf der vorherigen Seite bearbeitet oder zumindest die Lösung durchgelesen hast, ist dir das kartesische Produkt schon ein Begriff:
Möchte man die Daten aus mehreren Tabellen mithilfe einer SQL-Anfrage in einer Ergebnisrelation zusammenfassen, so kann man dies mit
SELECT *
FROM Tabelle1, Tabelle2, ..., Tabelle x;erreichen. Werden keine weiteren Angaben gemacht, so erhält man als Ergebnis das kartesische Produkt dieser Tabellen.
Bildet man das kartesische Produkt aus zwei Tabellen Tabelle1 und Tabelle2, so kombiniert man jeden Datensatz der ersten Tabelle mit jedem Datensatz der zweiten Tabelle. Die Anzahl der Einträge ist dann:
Das kartesische Produkt ist selten das, was du mit einer Abfrage erhalten willst. Trotzdem ist es nützlich, es zu kennen.
9 Übungen zum kartesischen Produkt
Laden
10 Die KundenID muss passen
Laden
11 Der natürliche Verbund
Die Angabe der Bedingung hatte gar nicht so viel Neues!
Mit der Abfrage
SELECT *
FROM NUTZER, ZAHLUNGSDATEN
WHERE NUTZER.ZAHLUNGSDATEN_KundenID = ZAHLUNGSDATEN.KundenID;bekommst du die Zuordnung der Kundendaten zu den Nutzern:
Verwendest du zum Verbinden der Tabellen die Schlüssel bzw. Fremdschlüssel, um die passenden Paare zu finden, so bildest du den natürlichen Verbund.
12 Endlich eine Antwort
Mit diesem Wissen kannst du die SQL-Abfrage formulieren, die die Daten für die Finanzabteilung liefern:
Laden
13 Aufgaben zu 1:n-Beziehungen
Wenn du noch weiter üben möchtest, kannst du diese Aufgabe bearbeiten:
Laden
14 Süchtig nach Superhelden
Auch das Marketing-Team hat eine Hypothese, die mit der Datenbank getestet werden soll. Sie behauptet, dass Nutzer, die Superhelden-Franchise wie zum Beispiel Marvel oder DC schauen, sehr hohe Nutzungsstunden haben. Diese Filme würden laut dem neuen Praktikanten "süchtig machen" und zu Filmmarathons und Binge-Watching verleiten.
Als konkrete Anfrage will das Team wissen, wie hoch die Nutzungsstunden der Nutzer sind, die den Film "Captain America" gesehen haben, den ersten Film der Reihe.
15 Erinnerung: m:n-Beziehungen
Ein Blick auf das Datenbankschema verrät, dass man, um vom Filmtitel auf die Nutzungsdauer zu kommen, drei Tabellen braucht: Über FILM zu angeschaut und dann zu NUTZER.
Das liegt daran, dass es sich bei "Nutzer schaut Film an" um eine m:n-Beziehung handelt:
Ein Nutzer kann viele Filme ansehen und ein Film kann von vielen Nutzern angesehen werden.
In der Tabelle angeschaut gibt es, um diese Beziehung darzustellen, nur die zwei Fremdschlüssel als Attribute: einen zur Tabelle FILM und einen zur TabelleNUTZER.
16 Der Datenbank-Dreisprung
Laden
17 Abfragen bei m:n-Beziehungen
Bei guter Datenbankmodellierung gibt es für m:n-Beziehungen eine extra Beziehungstabelle mit den Schlüsseln der beiden anderen Tabellen als Fremdschlüssel. Um Daten aus diesen beiden Tabellen zu verknüpfen, muss man diese Beziehungstabelle in den natürlichen Verbund mit einbeziehen:
SELECT *
FROM Tabelle_1, Tabelle_2, Beziehungstabelle
WHERE Tabelle_1.Schlüssel = Beziehungstabelle.Schlüssel_1
AND Tabelle_2.Schlüssel = Beziehungstabelle.Schlüssel_2;Diese Abfrage wirkt auf den ersten Blick etwas sperrig, hat aber nicht wirklich etwas Neues im Vergleich zu den 1:n-Beziehungen.
Am besten übst du einfach mal ein wenig die Abfragen auf der nächsten Seite und dann hast du auch schon alles Wichtige gelernt!
Hinweis: Natürlich lassen sich beliebig viele Tabellen miteinander verknüpfen. Das Schema funktioniert dabei immer gleich.
18 Übungsaufgaben zu m:n-Beziehungen
Laden
19 Zusammenfassung
Häufig muss man Daten aus mehreren Tabellen mit SQL-Abfragen zusammentragen.
Hierfür kann man im FROM-Teil der Abfrage mehrere Tabellen durch ein Komma getrennt angeben:
SELECT *
FROM NUTZER, FILM, angeschaut;kartesisches Produkt
Ohne weitere Einschränkungen bildet die Abfrage oben das kartesische Produkt aus den Tabellen und kombiniert jeden Eintrag jeder Tabelle mit jedem Eintrag der anderen.
Die Anzahl der Elemente im kartesischen Produkt ist deshalb das Produkt der Anzahlen der Datensätze in den beteiligten Tabellen:
Nutzer: Einträge,
Film: Einträge,
kartesisches Produkt der beiden: Einträge
natürlicher Verbund
Wichtiger für den Alltag ist der natürliche Verbund. Hier werden Fremdschlüssel genutzt, um Paare in den beiden Tabellen zu finden und zusammen zu setzen:
SELECT *
FROM NUTZER, ZAHLUNGSDATEN
WHERE NUTZER.KundenID = ZAHLUNGSDATEN.KundenID1:n und m:n Beziehungen abfragen
Der einzige Unterschied zwischen den Abfragen für 1:n und m:n-Beziehungen ist die Anzahl der zu verknüpfenden Tabellen. Bei der 1:n-Beziehung müssen, wie im Beispiel direkt darüber, nur zwei Tabellen verknüpft werden. Bei der m:n-Beziehung muss auch die Beziehungstabelle mit verknüpft werden.
SELECT *
FROM angeschaut, NUTZER, FILM
WHERE angeschaut.UserID = NUTZER.UserID
AND angeschaut.FilmID = FILM.FilmID;Es handelt sich in allen Fällen um ganz normale SQL-Abfragen und alle Befehle und Funktionen können ganz normal verwendet werden.
