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-Anfra­gen stellen:

-- 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_adresse, h_id
FROM  haendler
WHERE h_name = 'Maier';

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;

/* 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;

/* Bennenne die Ausgabe-Attribute um. */
SELECT h_name AS h_name, h_id AS haendlernummer FROM haendler;

/* SQL-Befehle sind nicht case-sensiv. Die Großbuchstaben
   der umbenannten Attribute werden nicht beachtet.i
 */
SELECT h_name AS h_name, h_id AS Haendlernummer FROM haendler;

/* Wenn man Attributenamen 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 eindeutich 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);

/* Selektiere alle Attribute aller Waren. */
SELECT * FROM ware;

/* Nicht-standard SQL:
   Selektiere alle Attribute aller Waren als ein einziges Tupel.
 */
SELECT w FROM ware w;

/* 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 ware FROM ware;

/* Selektiere alle CPUs aus der Tabelle ware. */
SELECT * FROM ware
WHERE w_typ = 'CPU';

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

/* Diese Anfrage 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;

/*
 */
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 XP 3000+
   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;

/* 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, 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; 

-- Nonsense-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!