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
- Kowarschick (MMDB-Skript): Wolfgang Kowarschick; Vorlesung Multimedia-Datenbanksysteme – Sommersemester 2018; Hochschule: Hochschule Augsburg; Adresse: Augsburg; Web-Link; 2018; Quellengüte: 4 (Skript)
- 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)
