Händler-Datenbank (SQL-Beispiel)/Projektion
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/
