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) { UNIQUE (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:
h_id h_name, h_ortschaft 1 'Maier' 'Königsbrunn' 2 'Müller' 'Königsbrunn' 3 'Maier' 'Augsburg' 4 'Huber' NULL 5 'Schmidt' 'Hamburg'
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); /* Was berechnet die folgende Anfrage? */ SELECT * FROM haendler h, liefert l, ware w; /* Die folgende Query ist sinnvoller. Was berechnet sie? */ 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; /* 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;
Experimentieren Sie nun mit eigenen Anfragen!
Überprüfen Sie, wie sich folgende Änderungen am Datenmodell auf die erlaubten Daten der Tabelle „liefert” auswirken: