Händler2-Datenbank (SQL-Beispiel)

Dieser Artikel erfüllt die GlossarWiki-Qualitätsanforderungen:

Korrektheit: 4 (großteils überprüft)

Umfang: 5 (wesentliche Fakten vorhanden)

Quellenangaben: 5 (vollständig vorhanden)

Quellenarten: 5 (ausgezeichnet)

Konformität: 5 (ausgezeichnet)

Das Händler-liefert-Ware-Beispiel ist das klassische Beispiel, um viele Konzepte von Datenbanksystemen und SQL zu demonstieren. Daher wird dieses Beispiel auch in der Vorlesung Multimedia-Datenbanksysteme verwendet.

Datenmodell/ER-Diagramm (in UML-Notation)

Eine Händler, der durch die Händlernummer hnr eindeutig identifiziert werden kann und weitere Attribute, wie Name, Adresse etc hat, liefert bestimmte Waren. Die zugehörigen Entities (Objekte) werden durch den Entity-Typ haendler definiert.

Welche Waren es gibt, ist durch einen weiteren Entity-Typ festgelegt: ware. Eine Ware ist durch die Warennummer wnr eindeutig festgelgt. Weitere Attribute, wie Typ (Gemüse, Fleisch, Wurst etc.) und genaue Bezeichnung (Kohlrabi, Rinderlende, Crevelat etc.), beschreiben die jeweilige Ware näher.

Zwischen den Entity-Typen haendler und ware besteht eine Beziehung: liefert. Diese legt fest, welcher Händler welche Ware zu welchen Konditionen liefert. Ein Lieferant liefert eine Ware normalerweise zu einem bestimmten Preis. Zusätzlich kann eine gewisse Lieferzeit (in Tagen) angegeben werden. Es ist auch erlaubt, dass ein Händler ein und dieselbe Ware zu mit unterschiedlichen Preisen unterschiedlich schnell liefert.

Im Gegensatz zum vereinfachten Datenmodell (siehe Händler-Datenbank), wird ein Händler durch mehr Attribute beschrieben. Die Adresse setzt sich aus einem Ort (der im Entity-Typ ort erfasst worden sein muss), einer Postleitzahl und einer optionalen Straße zusammen. Des weiteren kann man zu jedem Händler Large Binary Objects ablegen: Eine Anmerkung (ein beliebig langer Text: CLOB) sowie ein Bild des Hänlderlogos (BLOB). Von welchem Typ das Bild ist (GIF, PNG, JPEG) wird in einem gesonderten Attribut gespeichert.

Man beachte, dass CLOB und BLOB von PostgreSQL nicht standard-konform als TEXTB und BYTEA unterstützt werden.

Jeder Ort wird durch den Entity-Typ ort beschrieben. Jeder Ort wird sowohl durch den Amtlichen Gemeindeschlüssel, als auch durch den Namen und eine Postleitzeahl beschrieben. Beide Informationen findet man in der Datenbank des Statistischen Bundesamtes. Man beachte, dass eine Händler-Adresse durchaus eine andere Postleitzeahl haben kann, als für den zugehörige Ort erfasst wurde.

Das erweiterte Händler-liefert-Waren-Modell unterscheidet sich noch in einem weiteren Punkt von der einfachen Version: Die Warentypen werden mit einem eigenen Entity-Typ typ beschrieben. Hier wird sogar eine hierarchische Typisierung unterstützt. Jeder Warentyp kann eine Obertypen haben. Beispiel: Die Ware Johnanta Gold ist vom Typ Apfel -> Obst -> Lebensmittel.

Datenbankschema

Dieses Schema wurde nach dem in Kowarschick (MMDB-Skript) beschriebenen Verfahren aus dem obigen ER-Diargramm erzeugt.

Datenbankschema (SQL)

/* Alte Tabellen und Datentypen (in der richtigen Reihenfolge) löschen: */

DROP TABLE IF EXISTS liefert  CASCADE;
DROP TABLE IF EXISTS ware     CASCADE;
DROP TABLE IF EXISTS typ      CASCADE;
DROP TABLE IF EXISTS haendler CASCADE;
DROP TABLE IF EXISTS ort      CASCADE;

DROP TYPE IF EXISTS D_IMAGE_MIME;

/* Datentypen definieren: */

CREATE TYPE D_IMAGE_MIME AS ENUM ('gif', 'png', 'jpeg');


/* Tabellen definieren: */

CREATE TABLE ort
(onr     INTEGER     NOT NULL,
 ags     VARCHAR(9)  NOT NULL CHECK (ags ~ '\\A[0-9]{8}\\Z'),
 ort     VARCHAR(30) NOT NULL,
 ort_plz VARCHAR(5)  NOT NULL CHECK (ort_plz ~ '\\A[0-9]{5}\\Z'),

 CONSTRAINT pk_ort
   PRIMARY KEY (onr),

 CONSTRAINT unique_ags
   UNIQUE (ags),

 CONSTRAINT unique_ort_ort_plz
   UNIQUE (ort, ort_plz)
);

CREATE TABLE haendler
(hnr       INTEGER     NOT NULL,
 name      VARCHAR(20) NOT NULL,
 onr       INTEGER     NOT NULL,
 plz       VARCHAR(5)  NOT NULL CHECK (plz ~ '\\A[0-9]{5}\\Z'),
 strasse   VARCHAR(20),
 anmerkung TEXT,  --CLOB
 logo      BYTEA, --BLOB
 logo_mime D_IMAGE_MIME,

 CONSTRAINT pk_haendler
   PRIMARY KEY (hnr),

 CONSTRAINT unique_name_address
   UNIQUE (name, onr),

 CONSTRAINT check_logo_null
   CHECK  (   (logo IS NULL AND logo_mime IS NULL)
           OR (logo IS NOT NULL AND logo_mime IS NOT NULL)
          ),

 CONSTRAINT fk_haendler_onr
   FOREIGN KEY (onr) REFERENCES ort (onr)
);

CREATE TABLE typ
(tnr     INTEGER     NOT NULL,
 typ     VARCHAR(20) NOT NULL,
 obertyp INTEGER,

 CONSTRAINT pk_typ
   PRIMARY KEY (tnr),

 CONSTRAINT unique_typ
   UNIQUE (typ),

 CONSTRAINT fk_typ_obertyp
   FOREIGN KEY (obertyp) REFERENCES typ (tnr)
);

CREATE TABLE ware
(wnr          INTEGER     NOT NULL,
 tnr          INTEGER     NOT NULL DEFAULT 0,
 bezeichnung  VARCHAR(20) NOT NULL,

 CONSTRAINT pk_ware
   PRIMARY KEY (wnr),

 CONSTRAINT unique_typ_bezeichnung
   UNIQUE (tnr, bezeichnung),

 CONSTRAINT fk_typ_tnr
   FOREIGN KEY (tnr) REFERENCES typ (tnr)
);

CREATE TABLE liefert
(hnr         INTEGER       NOT NULL,
 wnr         INTEGER       NOT NULL,
 preis       NUMERIC(6,2)  NOT NULL,
 lieferzeit  SMALLINT      CHECK (lieferzeit >= 0), /* Tage */

 CONSTRAINT pk_liefert
   PRIMARY KEY (hnr,wnr,preis),

 CONSTRAINT fk_liefert_haendler
   FOREIGN KEY (hnr) REFERENCES haendler (hnr),

 CONSTRAINT fk_liefert_ware
   FOREIGN KEY (wnr) REFERENCES ware (wnr)
);

Beispielsdaten

INSERT INTO ort(onr, ags, ort, ort_plz)
VALUES
(1, '09772163', 'Königsbrunn', '86343'),
(2, '09761000', 'Augsburg',    '86150'),
(3, '02000000', 'Hamburg',     '20038');

INSERT INTO haendler(hnr, name, onr, plz, strasse)
VALUES 
(1, 'Maier',   1, '86343', 'Hauptstraße'),
(2, 'Müller',  1, '86343', 'Blumenallee'),
(3, 'Maier',   2, '86150', 'Maxstraße'),
(4, 'Huber',   2, '86161', NULL),
(5, 'Schmidt', 3, '20036', 'St. Pauli');

INSERT INTO typ(tnr, typ, obertyp)
VALUES
(0,   'Sonstiges', null),
(1,   'Hardware',  null),
(100, 'CPU',       1),
(101, 'RAM',       1);

INSERT INTO ware(wnr, tnr, bezeichnung)
VALUES
(1, 100, 'Pentium IV 3,8'),
(2, 100, 'Celeron 2,6'),
(3, 100, 'Athlon XP 3000+'),
(4, 0,   'Eieruhr');

INSERT INTO liefert(hnr, wnr, preis, lieferzeit)
VALUES 
(1, 1, 200.00, 1),
(1, 2, 100.00, NULL),
(1, 3, 150.00, 7),
(2, 3, 150.00, 4),
(1, 4,  10.00, 1),
(2, 1, 160.00, 1),
(2, 2, 180.00, NULL),
(3, 1, 160.00, 4),
(3, 2, 190.00, 1),
(4, 1, 150.00, 3),
(4, 3, 180.00, 5),
(4, 3, 199.99, 1);

Quellen

1. Kowarschick (MMDB-Skript): Wolfgang Kowarschick; Vorlesung Multimedia-Datenbanksysteme – Sommersemester 2018; Hochschule: Hochschule Augsburg; Adresse: Augsburg; Web-Link; 2018; Quellengüte: 4 (Skript)
2. Kowarschick (MMDB): Wolfgang Kowarschick; Vorlesung „Multimedia-Datenbanksysteme“; Hochschule: Hochschule Augsburg; Adresse: Augsburg; Web-Link; 2016; Quellengüte: 3 (Vorlesung)

Siehe auch

• Händler-Datenbank (SQL-Beispiel) (vereinfachte Version dieser Datenbank)