Händler2-Datenbank (SQL-Beispiel)

Datenmodell (UML)

Medium:Haendler2_Modell.jpg

Datenbankschema

Medium:Haendler2_Schema.png

Dieses Schema wurde nach dem in Kowarschick (2009) 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;

DROP TABLE IF EXISTS dummy    CASCADE;


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

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

/***********************************************************************
 * Dummy-Tabelle. Diese Tabelle kann für Abfragen eingesetzt 
 * werden, für deren Beantwortung eigentlich gar keine Tabelle
 * benötigt wird. Laut SQL-Standard muss immer eine Tabelle
 * in der FROM-Klausel einer SELECT-Anweisung angegeben werden.
 *
 * In PostgreSQL ist es auch möglich, Select-Anweisungen ohne 
 * From-Klausel anzugeben. Dies ist allerdings nicht standard-konform.
 ***********************************************************************/

CREATE TABLE dummy
(
 id INTEGER NOT NULL,
 CONSTRAINT p_dummy
   PRIMARY KEY (id)
);

Beispiels-Daten

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


/* Die Dummy-Tabelle enthält stets genau ein Tupel. */

INSERT INTO dummy(id)
VALUES
(1);

Quellen

• Kowarschick, W.: Multimedia-Datenbanksysteme
• Kowarschick, W. (2009): Skriptum zur Vorlesung Multimedia-Datenbanksysteme

Siehe auch

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

Dieser Artikel ist GlossarWiki-konform.