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 Ver­fü­gung.

Gege­ben sei fol­gen­des Daten­mo­dell einer Händ­ler-Daten­bank:

Datenmodell der Händleranwendung

Ein Händ­ler habe eine ein­deu­ti­ge Händ­ler­num­mer h_id (Pri­ma­ry Key, PK). Außer­dem habe er einen Namen und eine Adres­se. Bei­de Attri­bu­te zusam­men iden­ti­fi­zie­ren einen Händ­ler eben­falls ein­deu­tig (UNIQUE Key). Die Adres­se muss nicht unbe­dingt ange­ge­ben wer­den (NULLABLE).

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 ein­deu­tig.

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 werden.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 (zwei For­eign Keys, FK).

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­ö­er-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 Attri­bu­ten.

Chris­ti­na Rot­ter hat mir ein Doku­ment zur Ver­fü­gung gestellt, das die phpPgAdmin-Aus­ga­ben einer älte­ren Ver­si­on die­ser Auf­ga­be als Bil­der ent­hält. Herz­li­chen Dank!

  1. Öff­nen Sie phpP­g­Ad­min in einem Web-Brow­ser.
  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: DatenbankenDatenbank erstellen und geben Sie ihr den Namen
    ACCOUNT_haendler (wobei ACCOUNT für Ihren RZ-Account steht).
  5. Öff­nen Sie in der lin­ken Spal­te die zuge­hö­ri­ge Daten­bank. Öff­nen Sie dann den Ord­ner Schematapublic und kli­cken Sie auf Tabellen.
  6. Erzeu­gen Sie die Tabel­le haendler mit drei Attri­bu­ten (Spal­ten). Anmer­kung: U Attri­but­na­men, die mit PK mar­kiert sind, die­nen als Pri­mär­schlüs­sel (Häk­chen nicht ver­ges­sen), VARCHER ist eine Abkür­zung für CHARACTER VARYING.
    h_id      INTEGER     NOT NULL  (PK)
    h_name    VARCHAR(30) NOT NULL
    h_adresse VARCHAR(50)
    

    Ach­ten Sie dar­auf, dass Sie nicht ver­se­hent­lich Leer­zei­chen oder Groß­buch­sta­ben beim Tabel­len- oder bei Attri­but­na­men ein­ge­ben!

  7. Erzeu­gen Sie die Tabel­le ware mit drei Attri­bu­ten.
    w_id          INTEGER     NOT NULL                      (PK)
    w_typ         VARCHAR(30) NOT NULL  DEFAULT 'Sonstiges'
    w_bezeichnung VARCHAR(50) NOT NULL
    

    Bach­ten Sie bit­te, dass eine Zei­chen­ket­te mit ein­fa­chen Anfüh­rungs­zei­chen gekenn­zeich­net wird.
    Die Zei­chen­fol­ge 'Sonstiges' bezeich­net eine Zei­chen­ket­te (String, Varchar), die Zei­chen­fol­gen Sonstiges und "Sonstiges" sind dage­gen SQL-Bezeich­ner für ein Attri­but oder eine Tabel­le mit dem schö­nen Namen Sonstiges. Aller­dings gibt es in unse­rem Bei­spiel weder eine Tabel­le, noch ein Attri­but mit die­sem Namen. (In MyS­QL kön­nen Tabel­len- und Attri­but-Bezeich­ner auch mit Back­quo­tes gekenn­zeich­net wer­den: `Sonstiges`. Dies ist aller­dings nicht stan­dard­kon­form.)

  8. Erzeu­gen Sie die Tabel­le liefert mit vier Attri­bu­ten. In SQL gibt es nicht nur Gleit­kom­ma-, 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 geeig­net.
    h_id         INTEGER      NOT NULL  (PK)
    w_id         INTEGER      NOT NULL  (PK)
    l_preis      NUMERIC(8,2) NOT NULL  (PK)
    l_lieferzeit SMALLINT
    
  9. Erzeu­gen Sie für alle Tabel­len jeweils einen Pri­mär­schlüs­sel, wenn Sie dies noch nicht gemacht haben (Rei­ter Constraints). Ver­wen­den Sie bit­te stets die unter­stri­che­nen Attri­bu­te als Pri­mär­schlüs­sel-Attri­bu­te.
  10. Fügen Sie für die Tabel­le liefert zwei Fremd­schlüs­sel ein (Rei­ter Constraints):
    FOREIGN KEY (h_id) REFERENCES haendler(h_id)
    FOREIGN KEY (w_id) REFERENCES ware(w_id)
    
  11. Fügen Sie für die Tabel­le liefert einen Check-Cons­traint hin­zu (Rei­ter Constraints):
    CHECK (l_lieferzeit > 0)
    
  12. Fügen Sie für die Tabel­le haendler einen ein­deu­ti­gen (UNIQUE) Schlüs­sel hin­zu (Rei­ter Constraints):
    UNIQUE (h_name, h_adresse)
    
  13. Fügen Sie für die Tabel­le ware einen ein­deu­ti­gen (UNIQUE) Schlüs­sel hin­zu (Rei­ter Constraints):
    UNIQUE (w_typ, w_bezeichnung)
    
  14. Fügen Sie Daten in die Tabel­le ware ein.
    Geben Sie bei Zei­chen­ket­ten die Anfüh­rungs­stri­che nur an, wenn Sie als For­mat „Ausdruck“ gewählt haben. Falls Wert als For­mat gewählt wird (oder vor­ein­ge­stellt ist), dür­fen die Anfüh­rungs­stri­che nicht mit ange­ge­ben wer­den.
    1 'CPU'       'Pentium IV 3,8'
    2 'CPU'       'Celeron 2,6'
    3 'CPU'       'Athlon XP 3000+'
    4 'RAM'       'DDR 1GB'
    5 'Sonstiges' 'Eieruhr'
    
  15. Fügen Sie Daten in die Tabel­le haendler ein.
      
      1  'Maier'   'Königsbrunn'
      2  'Maier'   'Augsburg'
      3  'Müller'  'Augsburg'
      4  'Huber'   NULL
    
  16. Fügen Sie fol­gen­de Daten in die Tabel­le liefert ein, sofern dies mög­lich ist. Über­le­gen Sie sich, war­um dies in eini­gen Fäl­len nicht mög­lich 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
    
  17. Stel­len Sie die fol­gen­den SQL-Anfra­gen, indem Sie einen SQL-Select-Befehl nach dem ande­ren in das SQL-Fes­ter von phpP­G­Ad­min ein­fü­gen und aus­füh­ren (phpP­g­Ad­min: Klick auf „Schemata“ in der lin­ken Spal­te, dann Klick auf „SQL“ im rech­ten Fenster).Überlegen Sie sich in jedem Fall, was für Daten Sie mit der jewei­li­gen Anfra­ge ermit­teln. Machen Sie sich ins­be­son­de­re klar, wel­che Aus­wir­kun­gen die ein­zel­nen syn­tak­ti­schen Kon­struk­te, 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;
    
  18. Expe­ri­men­tie­ren Sie mit eige­nen Anfra­gen.