Die Händlerdatenbank
Lösen Sie die folgende Aufgabe mit PostgreSQL. Für spezielle Syntaxfragen steht Ihnen die offizielle Online-Dokumentation von PostgreSQL zur Verfügung.
Vorbereitung
- Öffnen Sie phpPgAdmin in einem Web-Browser.
- Geben Sie in der Alert-Box Ihre RZ-Kennung + Passwort ein und klicken Sie auf OK.
- Loggen Sie sich erneut mit Ihrer RZ-Kennung ein (linke Spalte: Klick auf
praktikum
, rechtes Fenster: RZ-Kennung + Passwort eingeben, Klick aufAnmelden
). - Erstellen Sie eine Datenbank:
Datenbanken
→Datenbank erstellen
und geben Sie ihr den NamenACCOUNT_haendler
(wobeiACCOUNT
für Ihren RZ-Account steht). - Lösen Sie nun die nachfolgenden Aufgaben, indem Sie die SQL-Befehle in einer UTF8-Textdatei erstellen (mit einem Texteditor Ihrer Wahl wie Atom, Sublime, Nodepad++ etc.) und jeweils ins SQL-Fester von phpPgAdmin einfügen. Speichern Sie Datei regelmäßig ab, damit die Befehle erhalten bleiben.
Die Händlertabelle
Ein Händler habe eine eindeutige Händlernummer h_id
, die als Primary Key (PK
) verwendet wird. Außerdem habe er einen Namen und eine Ortschaft, in der er tätig ist. (Eigentlich müsste man eine vollständige Adresse, d. h. auch noch Postleitzahl, Straße und Hausnummer speichern. Darauf wird hier allerdings aus Gründen der Einfachheit verzichtet.) Beide Attribute zusammen identifizieren einen Händler ebenfalls eindeutig (UNIQUE
Key). Die Ortschaft muss nicht unbedingt angegeben werden (NULLABLE
).

haendler
in UL- und in ER-Notation.Relationenschema:
h_id INTEGER NOT NULL (PK) h_name VARCHAR(30) NOT NULL h_ortschaft VARCHAR(50) { UNUQUE (h_name, h_ortschaft) }
Erstellen Sie für diese Tabelle ein SQL-Tabelle haendler
in Ihrer Datenbank. Benutzen Sie dazu den nachfolgenden SQL-Befehl:
-- Tabelle löschen, falls Sie schon existiert DROP TABLE IF EXISTS haendler CASCADE; -- Tabelle erstellen CREATE TABLE haendler (h_id INTEGER NOT NULL, h_name VARCHAR(30) NOT NULL, h_ortschaft VARCHAR(50), CONSTRAINT pk_haendler PRIMARY KEY (h_id), CONSTRAINT unique_haendler_name_ortschaft UNIQUE (h_name, h_ortschaft) );
Fügen Sie nun passende Daten ein:
INSERT INTO haendler(h_id, h_name, h_ortschaft) VALUES (1, 'Maier', 'Königsbrunn'), (2, 'Müller', 'Königsbrunn'), (3, 'Maier', 'Augsburg'), (4, 'Huber', NULL), (5, 'Schmidt', 'Hamburg') ;
Testen Sie nun Ihre Datenbank, indem Sie ein paar SQL-Anweisungen ausführen:
-- Selektiere alle Attribute (*) von allen Händlern: SELECT * FROM haendler; -- Selektiere die Namen aller Händler: SELECT h_name FROM haendler; -- Selektiere die Namen aller Händler und entferne Duplikate: SELECT DISTINCT h_name FROM haendler; -- Selektiere alle Händler mit dem Namen Maier: SELECT h_name, h_ortschaft, h_id FROM haendler WHERE h_name = 'Maier'; -- Ändere die Ortschaft von Händler Schmidt in Hamburg: UPDATE haendler SET h_ortschaft = 'Berlin' WHERE h_name = 'Schmidt' AND h_ortschaft = 'Hamburg'; SELECT * FROM haendler; -- Lösche den Händler mit der ID 5: DELETE FROM haendler WHERE h_id = 5; SELECT * FROM haendler; -- Füge den Händler Schmidt aus Hamburg wieder ein: INSERT INTO haendler(h_id, h_name, h_ortschaft) VALUES (5, 'Schmidt', 'Hamburg'); SELECT * FROM haendler;
Die Warentabelle
Eine Ware habe eine eindeutige Warennummer w_id
sowie einen Typ (Defaultwert: 'Sonstiges'
) und eine Bezeichnung. Zusammen identifizieren der Typ und die Bezeichnung eine Ware ebenfalls eindeutig.

haendler
in UML- und in ER-Notation.Relationenschema:
w_id INTEGER NOT NULL (PK) w_typ VARCHAR(30) NOT NULL DEFAULT 'Sonstiges' w_bezeichnung VARCHAR(50) NOT NULL { UNIQUE (w_typ, w_bezeichnung) }
Erstellen Sie diese Tabelle und fügen Sie folgende Daten ein:
w_id w_typ w_bezeichnung 1 'CPU' 'Pentium IV 3,8' 2 'CPU' 'Celeron 2,6' 3 'CPU' 'Athlon XP 3000+' 4 'RAM' 'DDR 1GB' 5 'Sonstiges' 'Eieruhr'
Stellen Sie selbst ein paar SELECT-Anfragen, um auf Inhalte dieser Tabelle zuzugreifen.
Die Händler-Datenbank
Zwischen Händlern und Waren besteht eine Beziehung: Jeder Händler kann beliebig viele Waren liefern und jede Ware kann von beliebig vielen Händlern geliefert werden. Dabei muss sichergestellt sein, dass es den Händler und die Ware, auf die in der Tabelle liefert
verwiesen wird, auch existieren. Dies wird durch sogenannte Foreign Keys (FK
) erreicht.
Ein Händler verlangt für eine Lieferung einen bestimmten Preis. Dieser kann abhängig von der Lieferzeit (in Arbeitstagen) unterschiedlich ausfallen. Die Lieferzeit muss nicht nicht unbedingt angegeben werden. Wenn Sie aber angegeben wird, muss sie größer als Null sein (Check Constraint: lieferzeit > 0
).
Eine Liefermöglichkeit ist erst durch die Händler-ID, die Waren-ID und den Preis eindeutig identifiziert. Eine bestimmte Ware kann von mehreren Händlern geliefert werden. Ebenso können zwei Händler die gleiche Ware liefern. Ja, es kann sogar ein Händler dieselbe Ware – abhängig von der Lieferzeit! – zu unterschiedlichen Preisen liefern. Daher besteht hier der Primärschlüssel hier aus drei Attributen.


Relationenschema der Tabelle liefert
:
h_id INTEGER NOT NULL (PK, FK) w_id INTEGER NOT NULL (PK, FK) l_preis NUMERIC(8,2) NOT NULL (PK) l_lieferzeit SMALLINT { l_lieferzeit > 0 }
In SQL gibt es nicht nur Gleitkomma‑, sondern auch Fixkommazahlen. NUMERIC(8,2)
bezeichnet Fixkommazahlen mit maximal 8 Stellen. Zwei dieser Stellen sind Nachkommastellen. Beispielsweise sind 100.00
und 123456.78
Elemente dieses Datentyps. Bei 1234567.89
und 1234567.8
handelt es sich dagegen nicht um NUMERIC(8,2)
-Zahl. Dieser Datentyp ist sehr gut zur Speicherung von Geldbeträgen geeignet.
Erstellen Sie die fehlende Tabelle liefert
. Fügen Sie in den CREATE-TABLE-Befehl zusätzlich eine so genannte Check-Constraint ein:
CHECK (l_lieferzeit > 0)
Fügen Sie folgende Daten ein, sofern dies ohne Fehlermeldung möglich ist (überlegen Sie sich, warum dies in einigen Fällen nicht möglich ist):
h_id w_id l_preis l_lieferzeit 1 1 200.00 1 1 1 194.00 6 1 2 100.00 NULL 1 3 150.00 7 2 1 160.00 NULL 2 3 170.00 4 1 4 10.00 1 2 1 190.00 1 2 2 180.00 NULL 3 1 195.00 2 3 2 190.00 1 4 1 150.00 3 4 3 180.00 5 4 3 199.99 1 6 1 199.00 2 1 7 399.00 4 1 1 300.00 0 1 1 200.00 5
Die oben angegebenen Datensätze sind teilweise fehlerhaft. Einige Fehler habe Sie schon wegen der Integritätsbedingung „Primary Key“ (PK, Primärschlüssel) oder der Check-Constraint gefunden. Primärschlüssel und Unique-Constraints stellen sicher, dass bestimmte Attributwerte in der Tabelle jeweils nur einmal vorkommen. Check-Constraints stellen sicher, dass Attributwerte bestimmte Werte oder Wertkombinationen nicht annehmen können.
Darüber hinaus gibt es die so genannten „Foreign Keys“ (FK, Fremdschlüssel). Das sind Integritätsbedingungen, die sicherstellen, dass zu einen Identifikator ein passendes Element in einer anderen (fremden) Tabelle existiert. Im obigen Relationenschema sind zwei Foreign Keys angegeben. Fügen Sie sie in Ihren CREATE-TABLE-BEFEHL ein:
FOREIGN KEY (h_id) REFERENCES haendler(h_id), FOREIGN KEY (w_id) REFERENCES ware(w_id)
Löschen Sie die Liefert-Tabelle, erstellen Sie Sie neu und versuchen Sie noch einmal die Daten einzufügen, die Sie zuvor eingefügt haben. Was beobachten Sie? Erklären Sie sich Ihre Beobacchtungen.
Einfache SQL-Anfragen
Führen Sie folgende SQL-Anfragen aus und interpretieren Sie jeweils das Ergebnis. Überlegen Sie sich für jede Frage, was sie leistet und wie sie sich bzw. das Ergebnis von den anderen Anfragen unterscheidet. Schreiben Sie Ihre Überlegungen in einen kurzen Kommentar vor die jeweilige Anfrage. Für die ersten paar Anfragen habe ich das schon beispielshaft gemacht.
/* Selektiere alle Attribute (*) von allen Händlern. */ SELECT * FROM haendler; SELECT h_id, h_name, h_ortschaft FROM haendler; /* Selektiere die Namen aller Händler: Maier wird zweimal ausgegeben! */ SELECT h_name FROM haendler; /* Selektiere die Namen aller Händler und entferne Duplikate: Maier wird einmal ausgegeben! */ SELECT DISTINCT h_name FROM haendler; /* Selektiere den Namen und die eindeutige Händlernummer aller Händler. */ SELECT h_name, h_id FROM haendler; /* Nun bewirkt DISTINCT keine Änderung! */ SELECT DISTINCT h_name, h_id FROM haendler; /* Benenne die Ausgabe-Attribute um. */ SELECT h_name AS h_name, h_id AS haendlernummer FROM haendler; /* SQL-Befehle sind nicht case-sensitiv. Die Großbuchstaben der umbenannten Attribute werden nicht beachtet. */ SELECT h_name AS h_name, h_id AS Haendlernummer FROM haendler; /* Wenn man Attributnamen oder Tabellennamen in doppelte Anführungszeichen schließt, sind alle Sonderzeichen erlaubt. */ SELECT h_name AS "h_name", h_id AS "Händlernummer" FROM haendler; /* Man kann Attribute und Tabellen(!) auch in der FROM-Klausel umbenennen. */ SELECT * FROM haendler AS h(id, h_name, haendlerortschaft); /* In den restlichen Klauseln (wie z.B. der SELECT-Klausel) muss man dann die neuen Namen verwenden. */ SELECT haendler.* FROM haendler; SELECT h.* FROM haendler AS h(id, h_name, haendlerortschaft); /* Die Punkt-Notation (h.id) kann, muss aber nicht verwendet werden, wenn ein Attributname eindeutig ist. */ SELECT h.id, h_name, haendlerortschaft FROM haendler AS h(id, h_name, haendlerortschaft); /* Das Schlüsselwort "AS" ist in der FROM-Klausel optional. */ SELECT h.id, h_name, haendlerortschaft FROM haendler h(id, h_name, haendlerortschaft); /* Formatieren der Händler-Attribute */ SELECT h_id, h_name || ' in ' || h_ortschaft AS info FROM haendler; SELECT h_id, CASE WHEN h_ortschaft IS NOT NULL THEN h_name || ' in ' || h_ortschaft ELSE h_name END AS info FROM haendler; SELECT h_id, COALESCE(h_name || ' in ' || h_ortschaft, h_name) AS info FROM haendler; SELECT h_id, CONCAT(h_name, ' in ', h_ortschaft) AS info -- 'in' wird immer ausgegeben :-( FROM haendler; SELECT json_build_object ('id', h_id, 'name', h_name, 'ortschaft', h_ortschaft ) AS info FROM haendler; /* Selektiere alle Attribute aller Händler. */ SELECT * FROM haendler; /* Selektiere alle Attribute aller Händler als ein einziges Tupel. */ SELECT h FROM haendler h; /* Syntax, bei der die doppelte Bedeutung des Namens ware deutlich wird. In der FROM-Klausel steht ware für die ganze Tabelle, in der SELECT-Klausel steht ware für das jeweils aktuelle Tupel. */ SELECT haendler FROM haendler; /* Selektiere alle Attribute aller Waren. */ SELECT w_id, w_typ, w_bezeichnung FROM ware; /* Selektiere alle CPUs aus der Tabelle ware. */ SELECT w_id, w_typ, w_bezeichnung FROM ware WHERE w_typ = 'CPU'; /* Selektiere alle CPUs und RAMs aus der Tabelle ware. */ SELECT w_id, w_typ, w_bezeichnung FROM ware WHERE w_typ = 'CPU' OR w_typ = 'RAM'; SELECT w_id, w_typ, w_bezeichnung FROM ware WHERE w_typ IN ('CPU', 'RAM'); /* Selektiere alle Waren mit Ausnahme von CPUs. */ SELECT w_id, w_typ, w_bezeichnung FROM ware WHERE NOT w_typ <> 'CPU'; SELECT w_id, w_typ, w_bezeichnung FROM ware WHERE NOT w_typ = 'CPU'; SELECT w_id, w_typ, w_bezeichnung FROM ware WHERE w_typ NOT IN ('CPU'); /* Selektiere alle Waren, deren Bezeichnung mit 'A' beginnt. */ SELECT w_id, w_typ, w_bezeichnung FROM ware WHERE w_bezeichnung LIKE 'A%'; /* Was berechnet die folgende Anfrage? Antwort: */ SELECT * FROM haendler h, liefert l, ware w; /* Die folgende Query ist sinnvoller. Sie beantwortet folgende Frage: */ SELECT * FROM haendler h, liefert l, ware w WHERE h.h_id = l.h_id AND l.w_id = w.w_id; /* Dasselbe ohne Preis und Lieferzeit. */ SELECT h.*, w.* FROM haendler h, liefert l, ware w WHERE h.h_id = l.h_id AND l.w_id = w.w_id; SELECT h.h_id, h.h_name, h.h_ortschaft, w.w_id, w.w_typ, w.w_bezeichnung FROM haendler h, liefert l, ware w WHERE h.h_id = l.h_id AND l.w_id = w.w_id; /* Da die Attribute l_preis und l_lieferzeit nicht ausgegeben werden, können Duplikate entstehen. So liefert Huber einen Athlon z.B. für 180 Euro in fünf Tagen und für 199.99 Euro in einem Tag. Diese Information wird jedoch gar nicht angezeigt, wenn man die Attribute l_preis und l_lieferzeit nicht ausgibt. Die entstehenden Duplikate sollte man mit DISTINCT entfernen. */ SELECT DISTINCT h.h_id, h.h_name, h.h_ortschaft, w.w_id, w.w_typ, w.w_bezeichnung FROM haendler h, liefert l, ware w WHERE h.h_id = l.h_id AND l.w_id = w.w_id; SELECT DISTINCT h.h_id, h.h_name, h.h_ortschaft, w.w_id, w.w_typ, w.w_bezeichnung FROM haendler h, liefert l, ware w WHERE h.h_id = l.h_id AND l.w_id = w.w_id ORDER BY h.h_name, h.h_ortschaft, w.w_typ, w.w_bezeichnung; /* Welche Waren liefern die Händler mit dem Namen 'Maier' zu welchem Preis und in welcher Zeit. */ SELECT h.h_id, h_name, h_ortschaft, w.w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit FROM haendler h, liefert l, ware w WHERE h.h_id = l.h_id AND l.w_id = w.w_id AND h_name = 'Maier' ORDER BY h_name, h_ortschaft, w_typ, w_bezeichnung, l_preis ; SELECT h.h_id, h_name, h_ortschaft, w.w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit FROM haendler h JOIN liefert l ON h.h_id = l.h_id JOIN ware w ON l.w_id = w.w_id WHERE h_name = 'Maier' ORDER BY h_name, h_ortschaft, w_typ, w_bezeichnung, l_preis ; /* Welche Waren liefert Händler Maier aus Königsbrunn zu welchem Preis und in welcher Zeit. */ SELECT h.h_id, h_name, h_ortschaft, w.w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit FROM haendler h JOIN liefert l ON h.h_id = l.h_id JOIN ware w ON l.w_id = w.w_id WHERE h_name = 'Maier' AND h_ortschaft = 'Königsbrunn' ORDER BY h_name, h_ortschaft, w_typ, w_bezeichnung, l_preis ; SELECT h.h_id, h_name, h_ortschaft, w.w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit FROM haendler h JOIN liefert l ON h.h_id = l.h_id JOIN ware w ON l.w_id = w.w_id WHERE h.h_id = 1 ORDER BY h_name, h_ortschaft, w_typ, w_bezeichnung, l_preis ; SELECT h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit FROM haendler h JOIN liefert l USING (h_id) JOIN ware w USING (w_id) WHERE h_id = 1 ORDER BY h_name, h_ortschaft, w_typ, w_bezeichnung, l_preis ; /* Nonsens-Anfrage (Warum?): */ SELECT * FROM haendler h, liefert l, ware w WHERE h.h_id = l.w_id AND l.h_id = w.w_id; /* Nochmal zum besseren Verständnis: Vergleichen Sie nicht Äpfel mit Birnen! */ -- Sinnvolle Anfrage SELECT name, ortschaft, typ, bezeichnung, preis, lieferzeit, apfel.apfel_id, liefert.apfel_id, liefert.birne_id, birne.birne_id FROM haendler AS apfel (apfel_id, name, ortschaft), liefert AS liefert (apfel_id, birne_id, preis, lieferzeit), ware AS birne (birne_id, typ, bezeichnung) WHERE apfel.apfel_id = liefert.apfel_id AND liefert.birne_id = birne.birne_id; -- Nonsens-Anfrage SELECT name, ortschaft, typ, bezeichnung, preis, lieferzeit, apfel.apfel_id, liefert.birne_id, liefert.apfel_id, birne.birne_id FROM haendler AS apfel (apfel_id, name, ortschaft), liefert AS liefert (apfel_id, birne_id, preis, lieferzeit), ware AS birne (birne_id, typ, bezeichnung) WHERE apfel.apfel_id = liefert.birne_id AND liefert.apfel_id = birne.birne_id;
Experimentieren Sie nun mit eigenen Anfragen!