Aufgabe 1 | Wolfgang Kowarschick

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.

Gegeben sei folgendes Datenmodell einer Händler-Datenbank:

Ein Händler habe eine eindeutige Händlernummer h_id (Primary Key, PK). Außerdem habe er einen Namen und eine Adresse. Beide Attribute zusammen identifizieren einen Händler ebenfalls eindeutig (UNIQUE Key). Die Adresse muss nicht unbedingt angegeben werden (NULLABLE).

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.

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 (zwei Foreign Keys, FK).

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ändöer-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.

Christina Rotter hat mir ein Dokument zur Verfügung gestellt, das die phpPgAdmin-Ausgaben einer älteren Version dieser Aufgabe als Bilder enthält. Herzlichen Dank!

Ö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 auf Anmelden).
Erstellen Sie eine Datenbank: Datenbanken → Datenbank erstellen und geben Sie ihr den Namen
ACCOUNT_haendler (wobei ACCOUNT für Ihren RZ-Account steht).
Öffnen Sie in der linken Spalte die zugehörige Datenbank. Öffnen Sie dann den Ordner Schemata → public und klicken Sie auf Tabellen.
Erzeugen Sie die Tabelle haendler mit drei Attributen (Spalten). Anmerkung: U Attributnamen, die mit PK markiert sind, dienen als Primärschlüssel (Häkchen nicht vergessen), VARCHAR ist eine Abkürzung für CHARACTER VARYING.
```
h_id      INTEGER     NOT NULL  (PK)
h_name    VARCHAR(30) NOT NULL
h_adresse VARCHAR(50)
```
Achten Sie darauf, dass Sie nicht versehentlich Leerzeichen oder Großbuchstaben beim Tabellen- oder bei Attributnamen eingeben!
Erzeugen Sie die Tabelle ware mit drei Attributen.
```
w_id          INTEGER     NOT NULL                      (PK)
w_typ         VARCHAR(30) NOT NULL  DEFAULT 'Sonstiges'
w_bezeichnung VARCHAR(50) NOT NULL
```
Beachten Sie bitte, dass eine Zeichenkette mit einfachen Anführungszeichen gekennzeichnet wird. Die Zeichenfolge 'Sonstiges' bezeichnet eine Zeichenkette (String, Varchar), die Zeichenfolgen Sonstiges und "Sonstiges" sind dagegen SQL-Bezeichner für ein Attribut oder eine Tabelle mit dem schönen Namen Sonstiges. Allerdings gibt es in unserem Beispiel weder eine Tabelle, noch ein Attribut mit diesem Namen. (In MySQL können Tabellen- und Attribut-Bezeichner auch mit Backquotes gekennzeichnet werden: `Sonstiges`. Dies ist allerdings nicht standardkonform.)
Erzeugen Sie die Tabelle liefert mit vier Attributen. 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.
```
h_id         INTEGER      NOT NULL  (PK)
w_id         INTEGER      NOT NULL  (PK)
l_preis      NUMERIC(8,2) NOT NULL  (PK)
l_lieferzeit SMALLINT
```
Erzeugen Sie für alle Tabellen jeweils einen Primärschlüssel, wenn Sie dies noch nicht gemacht haben (Reiter Constraints). Verwenden Sie bitte stets die mit PK markierten als Primärschlüssel-Attribute.

Fügen Sie für die Tabelle liefert zwei Fremdschlüssel ein (Reiter Constraints):

FOREIGN KEY (h_id) REFERENCES haendler(h_id)
FOREIGN KEY (w_id) REFERENCES ware(w_id)

Fügen Sie für die Tabelle liefert einen Check-Constraint hinzu (Reiter Constraints):
```
CHECK (l_lieferzeit > 0)
```
Fügen Sie für die Tabelle haendler einen eindeutigen (UNIQUE) Schlüssel hinzu (Reiter Constraints):
```
UNIQUE (h_name, h_adresse)
```
Fügen Sie für die Tabelle ware einen eindeutigen (UNIQUE) Schlüssel hinzu (Reiter Constraints):
```
UNIQUE (w_typ, w_bezeichnung)
```
Fügen Sie Daten in die Tabelle ware ein.
Geben Sie bei Zeichenketten die Anführungsstriche nur an, wenn Sie als Format „Ausdruck“ gewählt haben. Falls Wert als Format gewählt wird (oder voreingestellt ist), dürfen die Anführungsstriche nicht mit angegeben werden.
```
1 'CPU'       'Pentium IV 3,8'
2 'CPU'       'Celeron 2,6'
3 'CPU'       'Athlon XP 3000+'
4 'RAM'       'DDR 1GB'
5 'Sonstiges' 'Eieruhr'
```

Fügen Sie Daten in die Tabelle haendler ein.

1  'Maier'   'Königsbrunn'
2  'Maier'   'Augsburg'
3  'Müller'  'Augsburg'
4  'Huber'   NULL

Fügen Sie folgende Daten in die Tabelle liefert ein, sofern dies möglich ist. Überlegen Sie sich, warum dies in einigen Fällen nicht möglich ist.

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
1   1   200.00  5
6   1   199.00  2
1   7   399.00  4
1   1   300.00  0

Stellen Sie die folgenden SQL-Anfragen, indem Sie einen SQL-Select-Befehl nach dem anderen in das SQL-Fester von phpPGAdmin einfügen und ausführen (phpPgAdmin: Klick auf „Schemata“ in der linken Spalte, dann Klick auf „SQL“ im rechten Fenster).Überlegen Sie sich in jedem Fall, was für Daten Sie mit der jeweiligen Anfrage ermitteln. Machen Sie sich insbesondere klar, welche Auswirkungen die einzelnen syntaktischen Konstrukte, wie SELECT, *, FROM, DISTINCT, 'CPU', ware etc. haben.

SELECT * FROM haendler;

SELECT h_name FROM haendler;
SELECT DISTINCT h_name FROM haendler;

SELECT h_name, h_id FROM haendler;
SELECT DISTINCT h_name, h_id FROM haendler;

SELECT h_name AS name, h_id AS haendlernummer FROM haendler;
SELECT h_name AS Name, h_id AS Haendlernummer FROM haendler;
SELECT h_name AS "Name", h_id AS "Händler-Nummer" FROM haendler;

SELECT * FROM haendler AS h(id, name, haendleradresse);

SELECT haendler.* FROM haendler;
SELECT haendler   FROM haendler;
SELECT h.*        FROM haendler AS h(id, name, haendleradresse);
SELECT h          FROM haendler AS h(id, name, haendleradresse);
SELECT h.id, name, haendleradresse
FROM   haendler AS h(id, name, haendleradresse);

/* Das Schlüsselwort "AS" ist in der FROM-Klausel optional. */
SELECT h.id, name, haendleradresse
FROM   haendler h(id, name, haendleradresse);

SELECT * FROM ware;

/* nicht Standard-SQL */
SELECT w FROM ware w;
/* nicht Standard-SQL */
SELECT ware FROM ware;

SELECT * FROM ware
WHERE w_typ = 'CPU';

/* Was berechnet die folgende Anfrage? */
SELECT *
FROM   haendler h, liefert l, ware w;

SELECT *
FROM   haendler h, liefert l, ware w
WHERE  h.h_id = l.h_id AND l.w_id = w.w_id;

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_adresse, 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_name, h_adresse, w.w_id, w_typ, 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_name, h_adresse, w.w_id, w_typ, 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_name, h_adresse, w_typ, w_bezeichnung;

-- Nonsense-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, adresse, typ, bezeichnung, preis, lieferzeit,
       apfel.apfel_id, liefert.apfel_id, liefert.birne_id, birne.birne_id
FROM   haendler AS apfel   (apfel_id, name, adresse),
       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;

-- Nonsense-Anfrage
SELECT name, adresse, typ, bezeichnung, preis, lieferzeit,
       apfel.apfel_id, liefert.birne_id, liefert.apfel_id, birne.birne_id
FROM   haendler AS apfel   (apfel_id, name, adresse),
       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 mit eigenen Anfragen.