Händler-Datenbank (SQL-Beispiel)/Projektion: Unterschied zwischen den Versionen
Kowa (Diskussion | Beiträge) |
Kowa (Diskussion | Beiträge) Keine Bearbeitungszusammenfassung |
||
(25 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt) | |||
Zeile 1: | Zeile 1: | ||
[[ | {{Qualität | ||
|correctness = 4 | |||
|extent = 4 | |||
|numberOfReferences = 4 | |||
|qualityOfReferences = 5 | |||
|conformance = 5 | |||
}} | |||
Die nachfolgenden Beispiele können beispielsweise mit [[SQLite]] oder [[PostgreSQL]] getestet werden. | |||
Installieren Sie dazu die zugehörige [[Händler-Datenbank (SQL-Beispiel)|Händler-Datenbank]]. | |||
== Die Projektionsfunktion== | |||
[[File:Relational Algebra Projection Extended.svg|mini|410px|Eine Projektionsfunktion berechnet für jedes Tupel einer Tabelle ein neues Tupel; Duplikatzeilen werden in einer mengenbasierten Algebra entfernt]] | |||
In jeder [[Relationale Algebra#Selektion|Relationalen Algebra]] gibt es [[unendlich]] viele (üblicherweise [[abzählbar]] viele) '''Projektionsfunktionen'''. | |||
Eine Projektionsfunktion | |||
<div class="formula"> | |||
<math>π_{f_1 \,\text{as}\, b_1, \ldots, f_k \,\text{as}\, b_k}: R\rightharpoonup R</math><br /> | |||
</div> | |||
ermittelt für jedes Tupel einer Relation <math>r</math> ein neues Tupel mit Hilfe der Funktionen <math>f_i</math>. | |||
Die Relation <math>r</math> enthalte Tupel der Art <math>(a_1:v_1, \ldots, a_n:v_n)</math>, wobei <math>a_1, \ldots a_n</math> Attributnamen sind | |||
und <math>v_1, \ldots v_n</math> Werte der zugehörigen [[Domäne]]n <math>D_1, \ldots D_n</math>. Die Funktionen <math>f_i</math> berechnen für jedes Tupel | |||
<math>(a_1:v_1, \ldots, a_n:v_n) \in r</math> einen Wert <math>f_i((a_1:v_1, \ldots, a_n:v_n))</math>. Dieser | |||
wird jeweils unter dem Namen <math>b_i</math> in das Ergebnistupel eingefügt. | |||
Für Relationen, die Tupel anderer Bauart enthalten, {{dh}} andere Attribute oder gleichnamige Attribute mit nicht-kompatiblen Domänen, ist die Projektionsfunktion | |||
<math>π_{f_1 \,\text{as}\, b_1, \ldots, f_k \,\text{as}\, b_k}</math> nicht definiert. | |||
Man beachte: Wenn durch die Projektion Duplikate entstehen, müssen diese entfernt werden, sofern die relationale Algebra nicht | |||
multimengenbasiert sondern mengenbasiert ist. | |||
== Beispiele bezüglich der [[Händler-Datenbank (SQL-Beispiel)|Händler-Datenbank]]== | |||
Den nachfolgenden Beispielen liegt die [[Händler-Datenbank_(SQL-Beispiel)|Händler-Datenbank]] zugrunde. | |||
Alle Beispiele wurden mit [[PostgreSQL]] und [[SQLite]] getestet. | |||
===Selektion aller Attribute der Tabelle <code>haendler</code> ([[Händler-Datenbank (SQL-Beispiel)/Identität|Identität]])=== | |||
<source lang="sql"> | |||
SELECT h_id, h_name, h_ortschaft | |||
FROM haendler | |||
</source> | |||
<table> | |||
<tr><td><math>\texttt{haendler}</math></td><td>→ </td><td><math>π_{\texttt{h_id},\,\texttt{h_name},\,\texttt{h_ortschaft}}(\texttt{haendler})</math></td></tr> | |||
<tr> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_id</th><th>h_name</th><th>h_ortschaft</th></tr> | |||
<tr><td> 1 </td><td> Maier </td><td> Königsbrunn </td></tr> | |||
<tr><td> 2 </td><td> Müller </td><td> Königsbrunn </th></tr> | |||
<tr><td> 3 </td><td> Maier </td><td> Augsburg </th></tr> | |||
<tr><td> 4 </td><td> Huber </td><td> NULL </th></tr> | |||
<tr><td> 5 </td><td> Schmidt </td><td> Hamburg </th></tr> | |||
</table></td> | |||
<td>→ </td> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_id</th><th>h_name</th><th>h_ortschaft</th></tr> | |||
<tr><td> 1 </td><td> Maier </td><td> Königsbrunn </td></tr> | |||
<tr><td> 2 </td><td> Müller </td><td> Königsbrunn </th></tr> | |||
<tr><td> 3 </td><td> Maier </td><td> Augsburg </th></tr> | |||
<tr><td> 4 </td><td> Huber </td><td> NULL </th></tr> | |||
<tr><td> 5 </td><td> Schmidt </td><td> Hamburg </th></tr> | |||
</table></td> | |||
</tr> | |||
</table> | |||
===Umbenennen der Attribute der Tabelle <code>haendler</code>=== | |||
<source lang="sql"> | |||
SELECT h_id AS id, h_name AS name, h_ortschaft AS ortschaft | |||
FROM haendler | |||
</source> | |||
<table> | |||
<tr><td><math>\texttt{haendler}</math></td><td>→ </td><td><math>π_{\texttt{h_id AS id},\,\texttt{h_name AS name},\,\texttt{h_ortschaft AS ortschaft}}(\texttt{haendler})</math></td></tr> | |||
<tr> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_id</th><th>h_name</th><th>h_ortschaft</th></tr> | |||
<tr><td> 1 </td><td> Maier </td><td> Königsbrunn </td></tr> | |||
<tr><td> 2 </td><td> Müller </td><td> Königsbrunn </th></tr> | |||
<tr><td> 3 </td><td> Maier </td><td> Augsburg </th></tr> | |||
<tr><td> 4 </td><td> Huber </td><td> NULL </th></tr> | |||
<tr><td> 5 </td><td> Schmidt </td><td> Hamburg </th></tr> | |||
</table></td> | |||
<td>→ </td> | |||
<td><table class="datatable-sql"> | |||
<tr><th>id</th><th>name</th><th>ortschaft</th></tr> | |||
<tr><td> 1 </td><td> Maier </td><td> Königsbrunn </td></tr> | |||
<tr><td> 2 </td><td> Müller </td><td> Königsbrunn </th></tr> | |||
<tr><td> 3 </td><td> Maier </td><td> Augsburg </th></tr> | |||
<tr><td> 4 </td><td> Huber </td><td> NULL </th></tr> | |||
<tr><td> 5 </td><td> Schmidt </td><td> Hamburg </th></tr> | |||
</table></td> | |||
</tr> | |||
</table> | |||
===Änderung der Position der Attribute=== | |||
<source lang="sql"> | |||
SELECT h_name, h_ortschaft, h_id | |||
FROM haendler | |||
</source> | |||
<table> | |||
<tr><td><math>\texttt{haendler}</math></td><td>→ </td><td><math>π_{\,\texttt{h_name},\,\texttt{h_ortschaft},\,\texttt{h_id}}(\texttt{haendler})</math></td></tr> | |||
<tr> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_id</th><th>h_name</th><th>h_ortschaft</th></tr> | |||
<tr><td> 1 </td><td> Maier </td><td> Königsbrunn</td></tr> | |||
<tr><td> 2 </td><td> Müller </td><td> Königsbrunn</th></tr> | |||
<tr><td> 3 </td><td> Maier </td><td> Augsburg </th></tr> | |||
<tr><td> 4 </td><td> Huber </td><td> NULL </th></tr> | |||
<tr><td> 5 </td><td> Schmidt </td><td> Hamburg </th></tr> | |||
</table></td> | |||
<td>→ </td> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_name</th><th>h_ortschaft</th><th>h_id</th></tr> | |||
<tr><td> Maier </td><td> Königsbrunn</td><td> 1 </td></tr> | |||
<tr><td> Müller </td><td> Königsbrunn</th><td> 2 </td></tr> | |||
<tr><td> Maier </td><td> Augsburg </th><td> 3 </td></tr> | |||
<tr><td> Huber </td><td> NULL </th><td> 4 </td></tr> | |||
<tr><td> Schmidt </td><td> Hamburg </th><td> 5 </td></tr> | |||
</table></td> | |||
</tr> | |||
</table> | |||
===Entfernen einer Spalte=== | |||
<source lang="sql"> | |||
SELECT h_name, h_ortschaft | |||
FROM haendler | |||
</source> | |||
<table> | |||
<tr><td><math>\texttt{haendler}</math></td><td>→ </td><td><math>π_{\,\texttt{h_name},\,\texttt{h_ortschaft}}(\texttt{haendler})</math></td></tr> | |||
<tr> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_id</th><th>h_name</th><th>h_ortschaft</th></tr> | |||
<tr><td> 1 </td><td> Maier </td><td> Königsbrunn </td></tr> | |||
<tr><td> 2 </td><td> Müller </td><td> Königsbrunn </th></tr> | |||
<tr><td> 3 </td><td> Maier </td><td> Augsburg </th></tr> | |||
<tr><td> 4 </td><td> Huber </td><td> NULL </th></tr> | |||
<tr><td> 5 </td><td> Schmidt </td><td> Hamburg </th></tr> | |||
</table></td> | |||
<td>→ </td> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_name</th><th>h_ortschaft</th></tr> | |||
<tr><td> Maier </td><td> Königsbrunn </td></tr> | |||
<tr><td> Müller </td><td> Königsbrunn </th></tr> | |||
<tr><td> Maier </td><td> Augsburg </th></tr> | |||
<tr><td> Huber </td><td> NULL </th></tr> | |||
<tr><td> Schmidt </td><td> Hamburg </th></tr> | |||
</table></td> | |||
</tr> | |||
</table> | |||
Das Ergebnis enthält keine Duplikate, da die Attribute <code>(h_name, h_ortschaft)</code> | |||
gemäß dem [[Händler-Datenbank_(SQL-Beispiel)|Datenmodell]] einen [[Schlüsselkandidat]] (''key candidate, ''unique key'') bilden. | |||
===Entfernen zweier Spalten=== | |||
<source lang="sql"> | |||
SELECT h_name | |||
FROM haendler | |||
</source> | |||
oder auch | |||
<source lang="sql"> | |||
SELECT ALL h_name | |||
FROM haendler | |||
</source> | |||
<table> | |||
<tr><td><math>\texttt{haendler}</math></td><td>→ </td><td><math>π^{\star}_{\texttt{h_name}}(\texttt{haendler})</math></td></tr> | |||
<tr> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_id</th><th>h_name</th><th>h_ortschaft</th></tr> | |||
<tr><td> 1 </td><td> Maier </td><td> Königsbrunn </td></tr> | |||
<tr><td> 2 </td><td> Müller </td><td> Königsbrunn </th></tr> | |||
<tr><td> 3 </td><td> Maier </td><td> Augsburg </th></tr> | |||
<tr><td> 4 </td><td> Huber </td><td> NULL </th></tr> | |||
<tr><td> 5 </td><td> Schmidt </td><td> Hamburg </th></tr> | |||
</table></td> | |||
<td>→ </td> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_name</th></tr> | |||
<tr><td> Maier </td></tr> | |||
<tr><td> Müller </td></tr> | |||
<tr><td> Maier </td></tr> | |||
<tr><td> Huber </td></tr> | |||
<tr><td> Schmidt </td></tr> | |||
</table></td> | |||
</tr> | |||
</table> | |||
Beim Operator <math>π^{\star}</math> handelt es sich um einen Nonstandard-Projektionsoperator, der für Multimengen definiert ist und keine Duplikate entfernt. | |||
Das Ergebnis enthält den Namen „Maier“ doppelt, da es zwei Händler mit dem gleichen Namen gibt. | |||
===Entfernen von Duplikaten=== | |||
Mit Hilfe des <code>DISTINCT</code>-Operators können Duplikate entfernt werden: | |||
<source lang="sql"> | |||
SELECT DISTINCT h_name | |||
FROM haendler | |||
</source> | |||
<table> | |||
<tr><td><math>\texttt{haendler}</math></td><td>→ </td><td><math>π_{\texttt{h_name}}(\texttt{haendler}) \,\equiv\, \texttt{DISTINCT}(π^{\star}_{\,\texttt{h_name}}(\texttt{haendler})))</math></td></tr> | |||
<tr> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_id</th><th>h_name</th><th>h_ortschaft</th></tr> | |||
<tr><td> 1 </td><td> Maier </td><td> Königsbrunn </td></tr> | |||
<tr><td> 2 </td><td> Müller </td><td> Königsbrunn </th></tr> | |||
<tr><td> 3 </td><td> Maier </td><td> Augsburg </th></tr> | |||
<tr><td> 4 </td><td> Huber </td><td> NULL </th></tr> | |||
<tr><td> 5 </td><td> Schmidt </td><td> Hamburg </th></tr> | |||
</table></td> | |||
<td>→ </td> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_name</th></tr> | |||
<tr><td> Huber </td></tr> | |||
<tr><td> Maier </td></tr> | |||
<tr><td> Müller </td></tr> | |||
<tr><td> Schmidt </td></tr> | |||
</table></td> | |||
</tr> | |||
</table> | |||
===Sortieren des Ergebnises=== | |||
Beachten Sie bitte, dass sich die Reihenfolge geändert hat. SQL garantiert keine bestimmte Reihenfolge der Daten. Diese | |||
kann sich auch jederzeit ändern ({{zB}} nach einer einfachen [[Schemaevolution]], bei der beispielsweise <code>VARCHAR(30)</code> | |||
durch <code>VARCHAR(50)</code> ersetzt wurde). Sollten die Daten in einer bestimmte Reihenfolge benötigt werden, müssen sie '''immer''' sortiert werden: | |||
<source lang="sql"> | |||
SELECT DISTINCT h_name | |||
FROM haendler | |||
ORDER BY h_name DESC | |||
</source> | |||
<table> | |||
<tr><td><math>\texttt{haendler}</math></td><td>→ </td><td><math>\texttt{ORDER_BY}_{\texttt{h_name DESC}}(π_{\,\texttt{h_name}}(\texttt{haendler})))</math></td></tr> | |||
<tr> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_id</th><th>h_name</th><th>h_ortschaft</th></tr> | |||
<tr><td> 1 </td><td> Maier </td><td> Königsbrunn </td></tr> | |||
<tr><td> 2 </td><td> Müller </td><td> Königsbrunn </th></tr> | |||
<tr><td> 3 </td><td> Maier </td><td> Augsburg </th></tr> | |||
<tr><td> 4 </td><td> Huber </td><td> NULL </th></tr> | |||
<tr><td> 5 </td><td> Schmidt </td><td> Hamburg </th></tr> | |||
</table></td> | |||
<td>→ </td> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_name</th></tr> | |||
<tr><td> Schmidt </td></tr> | |||
<tr><td> Müller </td></tr> | |||
<tr><td> Maier </td></tr> | |||
<tr><td> Huber </td></tr> | |||
</table></td> | |||
</tr> | |||
</table> | |||
===Berechnung von Attributen=== | |||
<source lang="sql"> | |||
SELECT h_id, h_name || ' in ' || h_ortschaft AS haendler | |||
FROM haendler | |||
</source> | |||
Der Konkatenationsoperator <code>||</code> verknüpft zwei Zeichenketten zu einer einzigen. Achtung: In anderen Programmiersprachen, wie [[C]]/[[C++]], [[Java]], [[JavaScript]] etc. steht <code>||</code> für den Oder-Operator ([[Disjunkion]]). | |||
<table> | |||
<tr><td><math>\texttt{haendler}</math></td><td>→ </td><td><math>π_{\texttt{h_id},\,\texttt{h_name || ' in ' || h_ortschaft AS haendler}}(\texttt{haendler})</math></td></tr> | |||
<tr> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_id</th><th>h_name</th><th>h_ortschaft </th></tr> | |||
<tr><td> 1 </td><td> Maier </td><td> Königsbrunn </td></tr> | |||
<tr><td> 2 </td><td> Müller </td><td> Königsbrunn </th></tr> | |||
<tr><td> 3 </td><td> Maier </td><td> Augsburg </th></tr> | |||
<tr><td> 4 </td><td> Huber </td><td> NULL </th></tr> | |||
<tr><td> 5 </td><td> Schmidt </td><td> Hamburg </th></tr> | |||
</table></td> | |||
<td>→ </td> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_id</th><th>haendler </th></tr> | |||
<tr><td> 1 </td><td> Maier in Königsbrunn </td></tr> | |||
<tr><td> 2 </td><td> Müller in Königsbrunn </th></tr> | |||
<tr><td> 3 </td><td> Maier in Augsburg </th></tr> | |||
<tr><td> 4 </td><td> NULL </th></tr> | |||
<tr><td> 5 </td><td> Schmidt in Hamburg </th></tr> | |||
</table></td> | |||
</tr> | |||
</table> | |||
Ein Projektionsfunktion zur Berechung eines neuen Attributwertes darf auf beliebig viele (auch null) Attribute des jeweiligen Tupels zugreifen. | |||
Falls eines der Attributwerte gleich <code>NULL</code> ist (wie beim Attribut <code>h_ortschaft</code> des Händlers <code>Huber</code>), ist | |||
das Ergebnis standardmäßig gleich <code>NULL</code>. | |||
Um auch für den Händler code>Huber</code> einen sinnvollen Wert auszugeben, kann man die Funktion <code>COALESCE</code> verwenden, | |||
der beliebig viele Argumente übergeben werden können. Als Ergebnis gibt sie das erste Argument zurück, das ungleich <code>NULL</code> zurück. | |||
Gibt es kein derartiges Argument, ist das Ergebnis <code>NULL</code>. | |||
<source lang="sql"> | |||
SELECT h_id, COALESCE(h_name || ' in ' || h_ortschaft, h_name) AS haendler | |||
FROM haendler | |||
</source> | |||
oder alternativ | |||
<source lang="sql"> | |||
SELECT h_id, | |||
CASE | |||
WHEN h_ortschaft IS NULL THEN h_name | |||
ELSE h_name || ' in ' || h_ortschaft | |||
END AS haendler | |||
FROM haendler | |||
</source> | |||
<table> | |||
<tr><td><math>\texttt{haendler}</math></td><td>→ </td><td><math>π_{\texttt{h_id},\,\texttt{COALESCE(h_id, h_name || ' in ' || h_ortschaft, h_name) AS haendler}}(\texttt{haendler})</math></td></tr> | |||
<tr> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_id</th><th>h_name</th><th>h_ortschaft </th></tr> | |||
<tr><td> 1 </td><td> Maier </td><td> Königsbrunn </td></tr> | |||
<tr><td> 2 </td><td> Müller </td><td> Königsbrunn </th></tr> | |||
<tr><td> 3 </td><td> Maier </td><td> Augsburg </th></tr> | |||
<tr><td> 4 </td><td> Huber </td><td> NULL </th></tr> | |||
<tr><td> 5 </td><td> Schmidt </td><td> Hamburg </th></tr> | |||
</table></td> | |||
<td>→ </td> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_id</th><th>haendler </th></tr> | |||
<tr><td> 1 </td><td> Maier in Königsbrunn </td></tr> | |||
<tr><td> 2 </td><td> Müller in Königsbrunn </th></tr> | |||
<tr><td> 3 </td><td> Maier in Augsburg </th></tr> | |||
<tr><td> 4 </td><td> Huber </th></tr> | |||
<tr><td> 5 </td><td> Schmidt in Hamburg </th></tr> | |||
</table></td> | |||
</tr> | |||
</table> | |||
====Unteranfragen zur Berechnung von Attributen==== | |||
Eine '''Unteranfrage''' ('''Subquery'''), die genau ein Tupel mit einem Attribut als Ergebnis hat, kann als Funktion aufgeasst werden. | |||
(Sollte die Unteranfrage gar kein Ergebnis liefern, vereinbart man, dass <code>NULL</code> das Ergbnis dieser Anfrage ist.) | |||
Eine Unteranfrage kann – wie alle anderen Funktionen zur Berechnung von Attributwerten – auf Attribute des aktuellen Tupels der übergeordnet Anfrage zugreifen. | |||
In diesem Fall spricht man von einer '''korrelierten''' Unteranfrage. Diese muss für jedes Tupel der übergeordnet Anfrage erneut berechnet werden. Eine | |||
nicht-korrelierte Unteranfrage braucht dagegen nur ein einziges Mal ausgewertet werden. Alle Ergebnistupel erhalten denselben Wert. | |||
<source lang="sql"> | |||
SELECT h_id, COALESCE(h_name || ' in ' || h_ortschaft, h_name) AS haendler, | |||
-- nicht-korrelierte Unteranfrage | |||
(SELECT COUNT(*) | |||
FROM haendler | |||
) AS gesamtanzahl_haendler, | |||
-- korrelierte Unteranfrage | |||
(SELECT COUNT(*) > 1 | |||
FROM haendler h2 | |||
WHERE h1.h_ortschaft = h2.h_ortschaft | |||
) AS konkurrenz_in_ortschaft | |||
FROM haendler AS h1 | |||
</source> | |||
<table> | |||
<tr><td><math>\texttt{haendler}</math></td><td>→ </td><td><math>π_{\texttt{h_id},\,\texttt{... AS haendler},\,\texttt{... AS gesamtanzahl_haendler},\,\texttt{... AS konkurrenz_in_ortschaft}}(\texttt{haendler})</math></td></tr> | |||
<tr> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_id</th><th>h_name</th><th>h_ortschaft </th></tr> | |||
<tr><td> 1 </td><td> Maier </td><td> Königsbrunn </td></tr> | |||
<tr><td> 2 </td><td> Müller </td><td> Königsbrunn </th></tr> | |||
<tr><td> 3 </td><td> Maier </td><td> Augsburg </th></tr> | |||
<tr><td> 4 </td><td> Huber </td><td> NULL </th></tr> | |||
<tr><td> 5 </td><td> Schmidt </td><td> Hamburg </th></tr> | |||
</table></td> | |||
<td>→ </td> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_id</th><th>haendler </th><th>gesamtanzahl_haendler </th><th>konkurrenz_in_ortschaft </th></tr> | |||
<tr><td> 1 </td><td> Maier in Königsbrunn </td><td> 5 </td><td> true </td></tr> | |||
<tr><td> 2 </td><td> Müller in Königsbrunn </th><td> 5 </td><td> true </td></tr> | |||
<tr><td> 3 </td><td> Maier in Augsburg </th><td> 5 </td><td> false </td></tr> | |||
<tr><td> 4 </td><td> Huber </th><td> 5 </td><td> null </td></tr> | |||
<tr><td> 5 </td><td> Schmidt in Hamburg </th><td> 5 </td><td> false </td></tr> | |||
</table></td> | |||
</tr> | |||
</table> | |||
Die übliche Notation von Ausdrücken mit Operatoren der zugrundeliegenden Relationalen Algebra wird bei diesem Beispiel schon etwas unübersichtlich. | |||
Insgesamt hat das Anfrageergebnis vier Attribute.<br/> | |||
<div class="formula"> | |||
<math>π_{\texttt{h_id},\,\texttt{... AS haendler},\,\texttt{... AS gesamtanzahl_haendler},\,\texttt{... AS konkurrenz_in_ortschaft}}(\texttt{haendler})</math> | |||
</div> | |||
Das Attribut <code>haendler</code> wird – wie oben bereits beschrieben wurde – mit Hilfe der Funktion <code>COALESCE</code> ermittelt:<br/> | |||
<div class="formula"> | |||
<math>π_{\texttt{...},\,\texttt{COALESCE(h_id, h_name || ' in ' || h_ortschaft, h_name) AS haendler},\,\texttt{...}}(\texttt{haendler})</math> | |||
</div> | |||
Für das Attribut <code>gesamtanzahl_haendler</code> kommt die [[Händler-Datenbank_(SQL-Beispiel)/Aggregation|Aggregatsfunktion]] <code>COUNT</code> zum Einsatz. Diese liefert – wie gefordert – stets genau ein Ergebnistupel:<br/> | |||
<div class="formula"> | |||
<math>π_{\texttt{...},\,\gamma_{\texttt{COUNT(*)}}(\texttt{haendler})\,\texttt{AS gesamtanzahl_haendler},\,\texttt{... }}(\texttt{haendler})</math> | |||
</div> | |||
Für das Attribut <code>konkurrenz_in_ortschaft</code> wird ebenfalls <code>COUNT</code> verwendet. Korrelierte Unteranfragen können mit Hilfe | |||
von Relationalen Ausdrücken nicht ganz so einfach ausgedrückt werden, wie mit Hilfe von SQL: | |||
<div class="formula"> | |||
<math>π_{\texttt{...},\,π_{\texttt{c} > 1}(\gamma_{\texttt{COUNT(*) AS c}}(\sigma_{\texttt{h1.h_ortschaft} = \texttt{h2.h_ortschaft}}(\texttt{haendler AS h1})))\,\texttt{AS | |||
konkurrenz_...}}(\texttt{haendler AS h1})</math> | |||
</div> | |||
Ganz sauber ist diese Notation nicht, da Tabellen in der Relationalen Algebra üblicherweise nicht benannt werden können (im Gegensatz zu benannten Attributen, wenn man entsprechende Tupel verwendet). Das heißt, eine korrekte Notation der korrelierten Unterabfrage wäre noch aufwändiger. | |||
Man sieht, dass Relationale Ausdrücke durch Unteranfragen „zweidimensional“ werden. In den Indizes von relationalen Operatoren stehen wieder Operatoren mit eigenen Indizes. | |||
Wenn man Anfragen tiefer verschachtelt, werden die Indizes immer komplexer. | |||
====SQL als Taschenrechner==== | |||
Da eine Projektionsfunktion nicht auf irgendwelche Attribute zugreifen muss, | |||
kann man SQL als Taschenrechner einsetzen: | |||
<source lang="sql"> | |||
SELECT 17*23 AS ergebnis | |||
</source> | |||
<table class="datatable-sql"> | |||
<tr><th>ergebnis</th></tr> | |||
<tr><td> 391 </td></tr> | |||
</table> | |||
In der Realtionalen Algebra muss stets eine Tabelle angegeben werden, auf die eine Projektion angewendet wird. | |||
Ursprünglich war dies auch in SQL so, das heißt, es musste stets eine From-Klausel angegeben werden. | |||
In der Zwischenzeit unterstützen allerdings immer mehr Datenbankmanagementsysteme die obige Kurzschreibweise. | |||
Standardkonform wäre hingegen folgende Lösung: | |||
<source lang="sql"> | |||
SELECT 17*23 AS ergebnis | |||
FROM haendler | |||
</source> | |||
<table> | |||
<tr><td><math>\texttt{haendler}</math></td><td>→ </td><td><math>π_{17 \cdot 23 \,\texttt{AS ergebnis}}(\texttt{haendler})</math></td></tr> | |||
<tr> | |||
<td><table class="datatable-sql"> | |||
<tr><th>h_id</th><th>h_name</th><th>h_ortschaft </th></tr> | |||
<tr><td> 1 </td><td> Maier </td><td> Königsbrunn </td></tr> | |||
<tr><td> 2 </td><td> Müller </td><td> Königsbrunn </th></tr> | |||
<tr><td> 3 </td><td> Maier </td><td> Augsburg </th></tr> | |||
<tr><td> 4 </td><td> Huber </td><td> NULL </th></tr> | |||
<tr><td> 5 </td><td> Schmidt </td><td> Hamburg </th></tr> | |||
</table></td> | |||
<td>→ </td> | |||
<td><table class="datatable-sql"> | |||
<tr><th>ergebnis</th></tr> | |||
<tr><td> 391 </td></tr> | |||
<tr><td> 391 </td></tr> | |||
<tr><td> 391 </td></tr> | |||
<tr><td> 391 </td></tr> | |||
<tr><td> 391 </td></tr> | |||
</table></td> | |||
</tr> | |||
</table> | |||
Diese Lösung ist allerdings unbefriedigend. Sie enthält das Ergebnis entsprechend der Anzahl der Tupel der zugehörigen Tabelle. | |||
Duplikate könnte man mittels <code>DISTINCT</code> entfernen, aber der unnötige Rechenaufwand, zunächst alle Ergebnisse zu berechnen und | |||
danach die Duplikate zu löschen, würde bestehen bleiben. Noch schlechter wäre es, wenn die Tabelle überhaupt keine Elemente enthalten würde. | |||
Dann gäbe es auch kein Ergebnis. | |||
In Oracle wurde aus diesem Grund die Tabelle <code>DUAL</code> vordefiniert, die genau ein Tupel mit einem Attribut enthält (ursprünglich enthielt sie | |||
zwei Attribute – daher erklärt sich auch der Name). | |||
So eine Tabelle kann man natürlich auch selbst definieren: | |||
<source lang="sql"> | |||
CREATE TABLE one | |||
( id INTEGER CHECK (id = 1) ); -- one kann nur ein Tupel enthalten | |||
INSERT INTO one VALUES (1); | |||
-- Hier fehlt noch ein Trigger, der verhindert, dass das eine Tupel wieder entfernt wird. | |||
</source> | |||
Nun kann man den den Taschenrechner standardkonform formulieren: | |||
<source lang="sql"> | |||
SELECT 17*23 AS ergebnis | |||
FROM one | |||
</source> | |||
<table> | |||
<tr><td><math>\texttt{one}</math></td><td>→ </td><td><math>π_{17 \cdot 23 \,\texttt{AS ergebnis}}(\texttt{one})</math></td></tr> | |||
<tr> | |||
<td><table class="datatable-sql"> | |||
<tr><th>id</th></tr> | |||
<tr><td> 1 </td></tr> | |||
</table></td> | |||
<td>→ </td> | |||
<td><table class="datatable-sql"> | |||
<tr><th>ergebnis</th></tr> | |||
<tr><td> 391 </td></tr> | |||
</table></td> | |||
</tr> | |||
</table> | |||
==Quellen== | |||
<references/> | |||
<ol start="1"> | |||
<li>{{Quelle|Kowarschick, W. (MMDB-Skript): Skriptum zur Vorlesung Multimedia-Datenbanksysteme}}</li> | |||
<li>{{Quelle|Kowarschick, W.: Multimedia-Datenbanksysteme}}, https://kowa.hs-augsburg.de/mmdb/mmdb-beispiele/haendler-datenbank/</li> | |||
</ol> | |||
[[Kategorie:PostgreSQL-Beispiel]] | |||
[[Kategorie:Praktikum:MMDB]] | |||
==Siehe auch== | |||
* [[Händler-Datenbank (SQL-Beispiel)]] | |||
* [[Händler-Datenbank (SQL-Beispiel)/Identität]] | |||
* [[Händler-Datenbank (SQL-Beispiel)/Selektion]] |
Aktuelle Version vom 10. Oktober 2019, 11:36 Uhr
Dieser Artikel erfüllt die GlossarWiki-Qualitätsanforderungen:
Korrektheit: 4 (großteils überprüft) |
Umfang: 4 (unwichtige Fakten fehlen) |
Quellenangaben: 4 (fast vollständig vorhanden) |
Quellenarten: 5 (ausgezeichnet) |
Konformität: 5 (ausgezeichnet) |
Die nachfolgenden Beispiele können beispielsweise mit SQLite oder PostgreSQL getestet werden. Installieren Sie dazu die zugehörige Händler-Datenbank.
Die Projektionsfunktion
In jeder Relationalen Algebra gibt es unendlich viele (üblicherweise abzählbar viele) Projektionsfunktionen.
Eine Projektionsfunktion
$ π_{f_1 \,\text{as}\, b_1, \ldots, f_k \,\text{as}\, b_k}: R\rightharpoonup R $
ermittelt für jedes Tupel einer Relation $ r $ ein neues Tupel mit Hilfe der Funktionen $ f_i $. Die Relation $ r $ enthalte Tupel der Art $ (a_1:v_1, \ldots, a_n:v_n) $, wobei $ a_1, \ldots a_n $ Attributnamen sind und $ v_1, \ldots v_n $ Werte der zugehörigen Domänen $ D_1, \ldots D_n $. Die Funktionen $ f_i $ berechnen für jedes Tupel $ (a_1:v_1, \ldots, a_n:v_n) \in r $ einen Wert $ f_i((a_1:v_1, \ldots, a_n:v_n)) $. Dieser wird jeweils unter dem Namen $ b_i $ in das Ergebnistupel eingefügt.
Für Relationen, die Tupel anderer Bauart enthalten, d. h. andere Attribute oder gleichnamige Attribute mit nicht-kompatiblen Domänen, ist die Projektionsfunktion $ π_{f_1 \,\text{as}\, b_1, \ldots, f_k \,\text{as}\, b_k} $ nicht definiert.
Man beachte: Wenn durch die Projektion Duplikate entstehen, müssen diese entfernt werden, sofern die relationale Algebra nicht multimengenbasiert sondern mengenbasiert ist.
Beispiele bezüglich der Händler-Datenbank
Den nachfolgenden Beispielen liegt die Händler-Datenbank zugrunde. Alle Beispiele wurden mit PostgreSQL und SQLite getestet.
Selektion aller Attribute der Tabelle haendler
(Identität)
SELECT h_id, h_name, h_ortschaft
FROM haendler
$ \texttt{haendler} $ | → | $ π_{\texttt{h_id},\,\texttt{h_name},\,\texttt{h_ortschaft}}(\texttt{haendler}) $ | ||||||||||||||||||||||||||||||||||||
|
→ |
|
Umbenennen der Attribute der Tabelle haendler
SELECT h_id AS id, h_name AS name, h_ortschaft AS ortschaft
FROM haendler
$ \texttt{haendler} $ | → | $ π_{\texttt{h_id AS id},\,\texttt{h_name AS name},\,\texttt{h_ortschaft AS ortschaft}}(\texttt{haendler}) $ | ||||||||||||||||||||||||||||||||||||
|
→ |
|
Änderung der Position der Attribute
SELECT h_name, h_ortschaft, h_id
FROM haendler
$ \texttt{haendler} $ | → | $ π_{\,\texttt{h_name},\,\texttt{h_ortschaft},\,\texttt{h_id}}(\texttt{haendler}) $ | ||||||||||||||||||||||||||||||||||||
|
→ |
|
Entfernen einer Spalte
SELECT h_name, h_ortschaft
FROM haendler
$ \texttt{haendler} $ | → | $ π_{\,\texttt{h_name},\,\texttt{h_ortschaft}}(\texttt{haendler}) $ | ||||||||||||||||||||||||||||||
|
→ |
|
Das Ergebnis enthält keine Duplikate, da die Attribute (h_name, h_ortschaft)
gemäß dem Datenmodell einen Schlüsselkandidat (key candidate, unique key) bilden.
Entfernen zweier Spalten
SELECT h_name
FROM haendler
oder auch
SELECT ALL h_name
FROM haendler
$ \texttt{haendler} $ | → | $ π^{\star}_{\texttt{h_name}}(\texttt{haendler}) $ | ||||||||||||||||||||||||
|
→ |
|
Beim Operator $ π^{\star} $ handelt es sich um einen Nonstandard-Projektionsoperator, der für Multimengen definiert ist und keine Duplikate entfernt.
Das Ergebnis enthält den Namen „Maier“ doppelt, da es zwei Händler mit dem gleichen Namen gibt.
Entfernen von Duplikaten
Mit Hilfe des DISTINCT
-Operators können Duplikate entfernt werden:
SELECT DISTINCT h_name
FROM haendler
$ \texttt{haendler} $ | → | $ π_{\texttt{h_name}}(\texttt{haendler}) \,\equiv\, \texttt{DISTINCT}(π^{\star}_{\,\texttt{h_name}}(\texttt{haendler}))) $ | |||||||||||||||||||||||
|
→ |
|
Sortieren des Ergebnises
Beachten Sie bitte, dass sich die Reihenfolge geändert hat. SQL garantiert keine bestimmte Reihenfolge der Daten. Diese
kann sich auch jederzeit ändern (z. B. nach einer einfachen Schemaevolution, bei der beispielsweise VARCHAR(30)
durch VARCHAR(50)
ersetzt wurde). Sollten die Daten in einer bestimmte Reihenfolge benötigt werden, müssen sie immer sortiert werden:
SELECT DISTINCT h_name
FROM haendler
ORDER BY h_name DESC
$ \texttt{haendler} $ | → | $ \texttt{ORDER_BY}_{\texttt{h_name DESC}}(π_{\,\texttt{h_name}}(\texttt{haendler}))) $ | |||||||||||||||||||||||
|
→ |
|
Berechnung von Attributen
SELECT h_id, h_name || ' in ' || h_ortschaft AS haendler
FROM haendler
Der Konkatenationsoperator ||
verknüpft zwei Zeichenketten zu einer einzigen. Achtung: In anderen Programmiersprachen, wie C/C++, Java, JavaScript etc. steht ||
für den Oder-Operator (Disjunkion).
$ \texttt{haendler} $ | → | $ π_{\texttt{h_id},\,\texttt{h_name || ' in ' || h_ortschaft AS haendler}}(\texttt{haendler}) $ | ||||||||||||||||||||||||||||||
|
→ |
|
Ein Projektionsfunktion zur Berechung eines neuen Attributwertes darf auf beliebig viele (auch null) Attribute des jeweiligen Tupels zugreifen.
Falls eines der Attributwerte gleich NULL
ist (wie beim Attribut h_ortschaft
des Händlers Huber
), ist
das Ergebnis standardmäßig gleich NULL
.
Um auch für den Händler code>Huber einen sinnvollen Wert auszugeben, kann man die Funktion COALESCE
verwenden,
der beliebig viele Argumente übergeben werden können. Als Ergebnis gibt sie das erste Argument zurück, das ungleich NULL
zurück.
Gibt es kein derartiges Argument, ist das Ergebnis NULL
.
SELECT h_id, COALESCE(h_name || ' in ' || h_ortschaft, h_name) AS haendler
FROM haendler
oder alternativ
SELECT h_id,
CASE
WHEN h_ortschaft IS NULL THEN h_name
ELSE h_name || ' in ' || h_ortschaft
END AS haendler
FROM haendler
$ \texttt{haendler} $ | → | $ π_{\texttt{h_id},\,\texttt{COALESCE(h_id, h_name || ' in ' || h_ortschaft, h_name) AS haendler}}(\texttt{haendler}) $ | ||||||||||||||||||||||||||||||
|
→ |
|
Unteranfragen zur Berechnung von Attributen
Eine Unteranfrage (Subquery), die genau ein Tupel mit einem Attribut als Ergebnis hat, kann als Funktion aufgeasst werden.
(Sollte die Unteranfrage gar kein Ergebnis liefern, vereinbart man, dass NULL
das Ergbnis dieser Anfrage ist.)
Eine Unteranfrage kann – wie alle anderen Funktionen zur Berechnung von Attributwerten – auf Attribute des aktuellen Tupels der übergeordnet Anfrage zugreifen. In diesem Fall spricht man von einer korrelierten Unteranfrage. Diese muss für jedes Tupel der übergeordnet Anfrage erneut berechnet werden. Eine nicht-korrelierte Unteranfrage braucht dagegen nur ein einziges Mal ausgewertet werden. Alle Ergebnistupel erhalten denselben Wert.
SELECT h_id, COALESCE(h_name || ' in ' || h_ortschaft, h_name) AS haendler,
-- nicht-korrelierte Unteranfrage
(SELECT COUNT(*)
FROM haendler
) AS gesamtanzahl_haendler,
-- korrelierte Unteranfrage
(SELECT COUNT(*) > 1
FROM haendler h2
WHERE h1.h_ortschaft = h2.h_ortschaft
) AS konkurrenz_in_ortschaft
FROM haendler AS h1
$ \texttt{haendler} $ | → | $ π_{\texttt{h_id},\,\texttt{... AS haendler},\,\texttt{... AS gesamtanzahl_haendler},\,\texttt{... AS konkurrenz_in_ortschaft}}(\texttt{haendler}) $ | ||||||||||||||||||||||||||||||||||||||||||
|
→ |
|
Die übliche Notation von Ausdrücken mit Operatoren der zugrundeliegenden Relationalen Algebra wird bei diesem Beispiel schon etwas unübersichtlich.
Insgesamt hat das Anfrageergebnis vier Attribute.
$ π_{\texttt{h_id},\,\texttt{... AS haendler},\,\texttt{... AS gesamtanzahl_haendler},\,\texttt{... AS konkurrenz_in_ortschaft}}(\texttt{haendler}) $
Das Attribut haendler
wird – wie oben bereits beschrieben wurde – mit Hilfe der Funktion COALESCE
ermittelt:
$ π_{\texttt{...},\,\texttt{COALESCE(h_id, h_name || ' in ' || h_ortschaft, h_name) AS haendler},\,\texttt{...}}(\texttt{haendler}) $
Für das Attribut gesamtanzahl_haendler
kommt die Aggregatsfunktion COUNT
zum Einsatz. Diese liefert – wie gefordert – stets genau ein Ergebnistupel:
$ π_{\texttt{...},\,\gamma_{\texttt{COUNT(*)}}(\texttt{haendler})\,\texttt{AS gesamtanzahl_haendler},\,\texttt{... }}(\texttt{haendler}) $
Für das Attribut konkurrenz_in_ortschaft
wird ebenfalls COUNT
verwendet. Korrelierte Unteranfragen können mit Hilfe
von Relationalen Ausdrücken nicht ganz so einfach ausgedrückt werden, wie mit Hilfe von SQL:
$ π_{\texttt{...},\,π_{\texttt{c} > 1}(\gamma_{\texttt{COUNT(*) AS c}}(\sigma_{\texttt{h1.h_ortschaft} = \texttt{h2.h_ortschaft}}(\texttt{haendler AS h1})))\,\texttt{AS konkurrenz_...}}(\texttt{haendler AS h1}) $
Ganz sauber ist diese Notation nicht, da Tabellen in der Relationalen Algebra üblicherweise nicht benannt werden können (im Gegensatz zu benannten Attributen, wenn man entsprechende Tupel verwendet). Das heißt, eine korrekte Notation der korrelierten Unterabfrage wäre noch aufwändiger.
Man sieht, dass Relationale Ausdrücke durch Unteranfragen „zweidimensional“ werden. In den Indizes von relationalen Operatoren stehen wieder Operatoren mit eigenen Indizes. Wenn man Anfragen tiefer verschachtelt, werden die Indizes immer komplexer.
SQL als Taschenrechner
Da eine Projektionsfunktion nicht auf irgendwelche Attribute zugreifen muss, kann man SQL als Taschenrechner einsetzen:
SELECT 17*23 AS ergebnis
ergebnis |
---|
391 |
In der Realtionalen Algebra muss stets eine Tabelle angegeben werden, auf die eine Projektion angewendet wird. Ursprünglich war dies auch in SQL so, das heißt, es musste stets eine From-Klausel angegeben werden. In der Zwischenzeit unterstützen allerdings immer mehr Datenbankmanagementsysteme die obige Kurzschreibweise.
Standardkonform wäre hingegen folgende Lösung:
SELECT 17*23 AS ergebnis
FROM haendler
$ \texttt{haendler} $ | → | $ π_{17 \cdot 23 \,\texttt{AS ergebnis}}(\texttt{haendler}) $ | ||||||||||||||||||||||||
|
→ |
|
Diese Lösung ist allerdings unbefriedigend. Sie enthält das Ergebnis entsprechend der Anzahl der Tupel der zugehörigen Tabelle.
Duplikate könnte man mittels DISTINCT
entfernen, aber der unnötige Rechenaufwand, zunächst alle Ergebnisse zu berechnen und
danach die Duplikate zu löschen, würde bestehen bleiben. Noch schlechter wäre es, wenn die Tabelle überhaupt keine Elemente enthalten würde.
Dann gäbe es auch kein Ergebnis.
In Oracle wurde aus diesem Grund die Tabelle DUAL
vordefiniert, die genau ein Tupel mit einem Attribut enthält (ursprünglich enthielt sie
zwei Attribute – daher erklärt sich auch der Name).
So eine Tabelle kann man natürlich auch selbst definieren:
CREATE TABLE one
( id INTEGER CHECK (id = 1) ); -- one kann nur ein Tupel enthalten
INSERT INTO one VALUES (1);
-- Hier fehlt noch ein Trigger, der verhindert, dass das eine Tupel wieder entfernt wird.
Nun kann man den den Taschenrechner standardkonform formulieren:
SELECT 17*23 AS ergebnis
FROM one
$ \texttt{one} $ | → | $ π_{17 \cdot 23 \,\texttt{AS ergebnis}}(\texttt{one}) $ | ||||
|
→ |
|
Quellen
- Kowarschick (MMDB-Skript): Wolfgang Kowarschick; Vorlesung Multimedia-Datenbanksysteme – Sommersemester 2018; Hochschule: Hochschule Augsburg; Adresse: Augsburg; Web-Link; 2018; Quellengüte: 4 (Skript)
- Kowarschick (MMDB): Wolfgang Kowarschick; Vorlesung „Multimedia-Datenbanksysteme“; Hochschule: Hochschule Augsburg; Adresse: Augsburg; Web-Link; 2016; Quellengüte: 3 (Vorlesung), https://kowa.hs-augsburg.de/mmdb/mmdb-beispiele/haendler-datenbank/