Aufgabe 1: Die Händler-Datenbank

Die Händlerdatenbank

Lösen Sie die fol­gen­de Auf­ga­be mit Post­greS­QL. Für spe­zi­el­le Syn­tax­fra­gen steht Ihnen die offi­zi­el­le Online-Doku­men­ta­ti­on von Post­greS­QL zur Verfügung.

Vorbereitung

1. Öff­nen Sie phpP­gAd­min in einem Web-Browser.
2. Geben Sie in der Alert-Box Ihre RZ-Ken­nung + Pass­wort ein und kli­cken Sie auf OK.
3. Log­gen Sie sich erneut mit Ihrer RZ-Ken­nung ein (lin­ke Spal­te: Klick auf praktikum, rech­tes Fens­ter: RZ-Ken­nung + Pass­wort ein­ge­ben, Klick auf Anmelden).
4. Erstel­len Sie eine Daten­bank: Datenbanken → Datenbank erstellen und geben Sie ihr den Namen
   ACCOUNT_haendler (wobei ACCOUNT für Ihren RZ-Account steht).
5. Lösen Sie nun die nach­fol­gen­den Auf­ga­ben, indem Sie die SQL-Befeh­le in einer UTF8-Text­da­tei erstel­len (mit einem Text­edi­tor Ihrer Wahl wie Atom, Sub­li­me, Node­pad++ etc.) und jeweils ins SQL-Fes­ter von phpP­gAd­min ein­fü­gen. Spei­chern Sie Datei regel­mä­ßig ab, damit die Befeh­le erhal­ten bleiben.

Die Händlertabelle

Ein Händ­ler habe eine ein­deu­ti­ge Händ­ler­num­mer h_id, die als Pri­ma­ry Key (PK) ver­wen­det wird. Außer­dem habe er einen Namen und eine Ort­schaft, in der er tätig ist. (Eigent­lich müss­te man eine voll­stän­di­ge Adres­se, d. h. auch noch Post­leit­zahl, Stra­ße und Haus­num­mer spei­chern. Dar­auf wird hier aller­dings aus Grün­den der Ein­fach­heit ver­zich­tet.) Bei­de Attri­bu­te zusam­men iden­ti­fi­zie­ren einen Händ­ler eben­falls ein­deu­tig (UNIQUE Key). Die Ort­schaft muss nicht unbe­dingt ange­ge­ben wer­den (NULLABLE).

Rela­tio­nen­sche­ma:

h_id        INTEGER     NOT NULL  (PK) 
h_name      VARCHAR(30) NOT NULL 
h_ortschaft VARCHAR(50)
{ UNUQUE (h_name, h_ortschaft) }

Erstel­len Sie für die­se Tabel­le ein SQL-Tabel­le haendler in Ihrer Daten­bank. Benut­zen Sie dazu den nach­fol­gen­den 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 pas­sen­de 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')
;

Tes­ten Sie nun Ihre Daten­bank, indem Sie ein paar SQL-Anwei­sun­gen 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 ein­deu­ti­ge Waren­num­mer w_id sowie einen Typ (Default­wert: 'Sonstiges') und eine Bezeich­nung. Zusam­men iden­ti­fi­zie­ren der Typ und die Bezeich­nung eine Ware eben­falls eindeutig.

Rela­tio­nen­sche­ma:

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

Erstel­len Sie die­se Tabel­le und fügen Sie fol­gen­de 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'

Stel­len Sie selbst ein paar SELECT-Anfra­gen, um auf Inhal­te die­ser Tabel­le zuzugreifen. 

Die Händler-Datenbank

Zwi­schen Händ­lern und Waren besteht eine Bezie­hung: Jeder Händ­ler kann belie­big vie­le Waren lie­fern und jede Ware kann von belie­big vie­len Händ­lern gelie­fert wer­den. Dabei muss sicher­ge­stellt sein, dass es den Händ­ler und die Ware, auf die in der Tabel­le liefert ver­wie­sen wird, auch exis­tie­ren. Dies wird durch soge­nann­te For­eign Keys (FK) erreicht.

Ein Händ­ler ver­langt für eine Lie­fe­rung einen bestimm­ten Preis. Die­ser kann abhän­gig von der Lie­fer­zeit (in Arbeits­ta­gen) unter­schied­lich aus­fal­len. Die Lie­fer­zeit muss nicht nicht unbe­dingt ange­ge­ben wer­den. Wenn Sie aber ange­ge­ben wird, muss sie grö­ßer als Null sein (Check Cons­traint: lieferzeit > 0).

Eine Lie­fer­mög­lich­keit ist erst durch die Händ­ler-ID, die Waren-ID und den Preis ein­deu­tig iden­ti­fi­ziert. Eine bestimm­te Ware kann von meh­re­ren Händ­lern gelie­fert wer­den. Eben­so kön­nen zwei Händ­ler die glei­che Ware lie­fern. Ja, es kann sogar ein Händ­ler die­sel­be Ware – abhän­gig von der Lie­fer­zeit! – zu unter­schied­li­chen Prei­sen lie­fern. Daher besteht hier der Pri­mär­schlüs­sel hier aus drei Attributen.

Rela­tio­nen­sche­ma der Tabel­le 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‑, son­dern auch Fix­kom­ma­zah­len. NUMERIC(8,2) bezeich­net Fix­kom­ma­zah­len mit maxi­mal 8 Stel­len. Zwei die­ser Stel­len sind Nach­kom­ma­stel­len. Bei­spiels­wei­se sind 100.00 und 123456.78 Ele­men­te die­ses Daten­typs. Bei 1234567.89 und 1234567.8 han­delt es sich dage­gen nicht um NUMERIC(8,2)-Zahl. Die­ser Daten­typ ist sehr gut zur Spei­che­rung von Geld­be­trä­gen geeignet.

Erstel­len Sie die feh­len­de Tabel­le liefert. Fügen Sie in den CREA­TE-TABLE-Befehl zusätz­lich eine so genann­te Check-Cons­traint ein:

CHECK (l_lieferzeit > 0) 

Fügen Sie fol­gen­de Daten ein, sofern dies ohne Feh­ler­mel­dung mög­lich ist (über­le­gen Sie sich, war­um dies in eini­gen Fäl­len nicht mög­lich 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 ange­ge­be­nen Daten­sät­ze sind teil­wei­se feh­ler­haft. Eini­ge Feh­ler habe Sie schon wegen der Inte­gri­täts­be­din­gung „Pri­ma­ry Key“ (PK, Pri­mär­schlüs­sel) oder der Check-Cons­traint gefun­den. Pri­mär­schlüs­sel und Uni­que-Cons­traints stel­len sicher, dass bestimm­te Attri­but­wer­te in der Tabel­le jeweils nur ein­mal vor­kom­men. Check-Cons­traints stel­len sicher, dass Attri­but­wer­te bestimm­te Wer­te oder Wert­kom­bi­na­tio­nen nicht anneh­men können. 

Dar­über hin­aus gibt es die so genann­ten „For­eign Keys“ (FK, Fremd­schlüs­sel). Das sind Inte­gri­täts­be­din­gun­gen, die sicher­stel­len, dass zu einen Iden­ti­fi­ka­tor ein pas­sen­des Ele­ment in einer ande­ren (frem­den) Tabel­le exis­tiert. Im obi­gen Rela­tio­nen­sche­ma sind zwei For­eign Keys ange­ge­ben. 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 Lie­fert-Tabel­le, erstel­len Sie Sie neu und ver­su­chen Sie noch ein­mal die Daten ein­zu­fü­gen, die Sie zuvor ein­ge­fügt haben. Was beob­ach­ten Sie? Erklä­ren Sie sich Ihre Beobacchtungen.

Einfache SQL-Anfragen

Füh­ren Sie fol­gen­de SQL-Anfra­gen aus und inter­pre­tie­ren Sie jeweils das Ergeb­nis. Über­le­gen Sie sich für jede Fra­ge, was sie leis­tet und wie sie sich bzw. das Ergeb­nis von den ande­ren Anfra­gen unter­schei­det. Schrei­ben Sie Ihre Über­le­gun­gen in einen kur­zen Kom­men­tar vor die jewei­li­ge Anfra­ge. Für die ers­ten paar Anfra­gen habe ich das schon bei­spiels­haft 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; 
 

Expe­ri­men­tie­ren Sie nun mit eige­nen Anfragen!