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

Eine Projektionsfunktion berechnet für jedes Tupel einer Tabelle ein neues Tupel; Duplikatzeilen werden in einer mengenbasierten Algebra entfernt

Eine Projektionsfunktion einer Relationalen Algebra

$ π_{f_1 \,\text{as}\, b_1, \ldots, f_k \,\text{as}\, b_k}: R \rightarrow R $

ermittelt für jedes Tupel einer Relation ein neues Tupel mit HilfE DER fUNKTIONEN $f_i$: Dies sind Funktionen, die für jedes Tupel $(v_1:a_1, \ldots, v_n:a_n) \in r$ jeweils einen Wert $f_i((v_1:a_1, \ldots, v_n:a_n))$ berechnen. Dieser wird jeweils unter dem Namen $b_i$ in das Ergebnistupel eingefügt.

Man beachte: Dabei entstehende Duplikate müssen ebenfalls 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} $	→	$ \pi_{\texttt{h_id},\,\texttt{h_name},\,\texttt{h_ortschaft}}(\texttt{haendler}) $
h_id h_name h_ortschaft 1 Maier Königsbrunn 2 Müller Königsbrunn 3 Maier Augsburg 4 Huber NULL 5 Schmidt Hamburg	→	h_id h_name h_ortschaft 1 Maier Königsbrunn 2 Müller Königsbrunn 3 Maier Augsburg 4 Huber NULL 5 Schmidt Hamburg

Umbenennen der Attribute der Tabelle haendler (Identität)

SELECT h_id AS id, h_name AS name, h_ortschaft AS ortschaft
FROM   haendler

$ \texttt{haendler} $	→	$ \pi_{\texttt{h_id AS id},\,\texttt{h_name AS name},\,\texttt{h_ortschaft AS ortschaft}}(\texttt{haendler}) $
h_id h_name h_ortschaft 1 Maier Königsbrunn 2 Müller Königsbrunn 3 Maier Augsburg 4 Huber NULL 5 Schmidt Hamburg	→	id name ortschaft 1 Maier Königsbrunn 2 Müller Königsbrunn 3 Maier Augsburg 4 Huber NULL 5 Schmidt Hamburg

Änderung der Position der Attribute

SELECT h_name, h_ortschaft, h_id
FROM   haendler

$ \texttt{haendler} $	→	$ \pi_{\,\texttt{h_name},\,\texttt{h_ortschaft},\,\texttt{h_id}}(\texttt{haendler}) $
h_id h_name h_ortschaft 1 Maier Königsbrunn 2 Müller Königsbrunn 3 Maier Augsburg 4 Huber NULL 5 Schmidt Hamburg	→	h_name h_ortschaft h_id Maier Königsbrunn 1 Müller Königsbrunn 2 Maier Augsburg 3 Huber NULL 4 Schmidt Hamburg 5

Entfernen einer Spalte

SELECT h_name, h_ortschaft
FROM   haendler

$ \texttt{haendler} $	→	$ \pi_{\,\texttt{h_name},\,\texttt{h_ortschaft}}(\texttt{haendler}) $
h_id h_name h_ortschaft 1 Maier Königsbrunn 2 Müller Königsbrunn 3 Maier Augsburg 4 Huber NULL 5 Schmidt Hamburg	→	h_name h_ortschaft Maier Königsbrunn Müller Königsbrunn Maier Augsburg Huber NULL Schmidt Hamburg

Das Ergebnis enthält keine Duplikate, da die Attribute (h_name, h_ortschaft) gemäß 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} $	→	$ \pi^{\star}_{\texttt{h_name}}(\texttt{haendler}) $
h_id h_name h_ortschaft 1 Maier Königsbrunn 2 Müller Königsbrunn 3 Maier Augsburg 4 Huber NULL 5 Schmidt Hamburg	→	h_name Maier Müller Maier Huber Schmidt

Beim Operator $ \pi^{\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} $	→	$ \pi_{\texttt{h_name}}(\texttt{haendler}) \,\equiv\, \texttt{DISTINCT}(\pi^{\star}_{\,\texttt{h_name}}(\texttt{haendler}))) $
h_id h_name h_ortschaft 1 Maier Königsbrunn 2 Müller Königsbrunn 3 Maier Augsburg 4 Huber NULL 5 Schmidt Hamburg	→	h_name Huber Maier Müller Schmidt

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}}(\pi_{\,\texttt{h_name}}(\texttt{haendler}))) $
h_id h_name h_ortschaft 1 Maier Königsbrunn 2 Müller Königsbrunn 3 Maier Augsburg 4 Huber NULL 5 Schmidt Hamburg	→	h_name Schmidt Müller Maier Huber

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).

<tr

$ \texttt{haendler} $	→	$ \pi_{\texttt{h_id},\,\texttt{h_name || ' in ' || h_ortschaft AS haendler}}(\texttt{haendler}) $
h_id h_name h_ortschaft 1 Maier Königsbrunn 2 Müller Königsbrunn 3 Maier Augsburg 4 Huber NULL 5 Schmidt Hamburg	→	h_id haendler 1 Maier in Königsbrunn 2 Müller in Königsbrunn 3 Maier in Augsburg 4 NULL 5 Schmidt in Hamburg

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} $	→	$ \pi_{\texttt{h_id},\,\texttt{COALESCE(h_id, h_name || ' in ' || h_ortschaft, h_name) AS haendler}}(\texttt{haendler}) $
h_id h_name h_ortschaft 1 Maier Königsbrunn 2 Müller Königsbrunn 3 Maier Augsburg 4 Huber NULL 5 Schmidt Hamburg	→	h_id haendler 1 Maier in Königsbrunn 2 Müller in Königsbrunn 3 Maier in Augsburg 4 Huber 5 Schmidt in Hamburg

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} $	→	$ \pi_{\texttt{h_id},\,\texttt{... AS haendler},\,\texttt{... AS gesamtanzahl_haendler},\,\texttt{... AS konkurrenz_in_ortschaft}}(\texttt{haendler}) $
h_id h_name h_ortschaft 1 Maier Königsbrunn 2 Müller Königsbrunn 3 Maier Augsburg 4 Huber NULL 5 Schmidt Hamburg	→	h_id haendler gesamtanzahl_haendler konkurrenz_in_ortschaft 1 Maier in Königsbrunn 5 true 2 Müller in Königsbrunn 5 true 3 Maier in Augsburg 5 false 4 Huber 5 null 5 Schmidt in Hamburg 5 false

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.

$ \pi_{\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:

$ \pi_{\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:

$ \pi_{\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:

$ \pi_{\texttt{...},\,\pi_{\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} $	→	$ \pi_{17 \cdot 23 \,\texttt{AS ergebnis}}(\texttt{haendler}) $
h_id h_name h_ortschaft 1 Maier Königsbrunn 2 Müller Königsbrunn 3 Maier Augsburg 4 Huber NULL 5 Schmidt Hamburg	→	ergebnis 391 391 391 391 391

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 dummy
( id INTEGER CHECK (id = 1) );  -- dummy kann nur ein Tupel enthalten

INSERT INTO dummy 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   dummy

$ \texttt{dummy} $	→	$ \pi_{17 \cdot 23 \,\texttt{AS ergebnis}}(\texttt{dummy}) $
id 1	→	ergebnis 391

Quellen

1. Kowarschick (MMDB-Skript): Wolfgang Kowarschick; Vorlesung Multimedia-Datenbanksysteme – Sommersemester 2018; Hochschule: Hochschule Augsburg; Adresse: Augsburg; Web-Link; 2018; Quellengüte: 4 (Skript)
2. 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/

Siehe auch

• Händler-Datenbank (SQL-Beispiel)
• Händler-Datenbank (SQL-Beispiel)/Identität
• Händler2-Datenbank (SQL-Beispiel)