Händler-Datenbank (SQL-Beispiel): Unterschied zwischen den Versionen
Kowa (Diskussion | Beiträge) Keine Bearbeitungszusammenfassung |
Kowa (Diskussion | Beiträge) Keine Bearbeitungszusammenfassung |
||
Zeile 9: | Zeile 9: | ||
Daher wird dieses Beispiel auch in der Vorlesung [[Kowarschick, W.: Multimedia-Datenbanksysteme|Multimedia-Datenbanksysteme]] verwendet. | Daher wird dieses Beispiel auch in der Vorlesung [[Kowarschick, W.: Multimedia-Datenbanksysteme|Multimedia-Datenbanksysteme]] verwendet. | ||
==Datenmodell/[[ER-Diagramm]] | ==Datenmodell/[[ER-Diagramm]]== | ||
[[Datei: | [[Datei:Haendler1_ER.png|gerahmt|ohne|Datenmodell in [[ER-Diagramm|ER]]-Notation]] | ||
[[Datei:Haendler1_UML.png|gerahmt|ohne|Datenmodell in [[UML]]-Notation]] | |||
Eine Händler, der durch die | Eine Händler, der durch die Händler-ID <code>h_id</code> eindeutig identifiziert werden kann und weitere Attribute, wie Name, Adresse etc. hat, | ||
liefert bestimmte Waren. Die zugehörigen [[Entity|Entities]] ([[Objekt]]e) werden durch den [[Entity-Typ]] (die Klasse) <code>haendler</code> definiert. | liefert bestimmte Waren. Die zugehörigen [[Entity|Entities]] ([[Objekt]]e) werden durch den [[Entity-Typ]] (die Klasse) <code>haendler</code> definiert. | ||
Welche Waren es gibt, ist durch einen weiteren Entity-Typ festgelegt: <code>ware</code>. Eine Ware ist durch die | Welche Waren es gibt, ist durch einen weiteren Entity-Typ festgelegt: <code>ware</code>. Eine Ware ist durch die WarenID <code>w:id</code> | ||
eindeutig festgelgt. Weitere Attribute, wie Typ (Gemüse, Fleisch, Wurst etc.) und genaue Bezeichnung (Kohlrabi, Rinderlende, | eindeutig festgelgt. Weitere Attribute, wie Typ (Gemüse, Fleisch, Wurst etc.) und genaue Bezeichnung (Kohlrabi, Rinderlende, Cervelat etc.), | ||
beschreiben die jeweilige Ware näher. | beschreiben die jeweilige Ware näher. | ||
Zeile 23: | Zeile 24: | ||
legt fest, welcher Händler welche Ware zu welchen Konditionen liefert. Ein Lieferant liefert eine Ware normalerweise zu einem bestimmten | 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 | 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. Um dies zu ermöglichen, muss das Attribut <code> | Ware zu mit unterschiedlichen Preisen unterschiedlich schnell liefert. Um dies zu ermöglichen, muss das Attribut <code>l_preis</code> | ||
zum orginären Primärschlüssel <code>( | zum orginären Primärschlüssel <code>(h_id, w_id)</code> der Beziehung <code>liefert</code> hinzugefügt werden. | ||
==Datenbankschema== | ==Datenbankschema== | ||
[[Datei:Haendler_Schema.png]] | [[Datei:Haendler_Schema.png]] | ||
<source lang="sql">haendler: h_id, h_name, h_adresse* {PK: s_id} | |||
{UNIQUE: h_name, h_adresse} | |||
ware: w_id, w_typ, w_bezeichnung {PK: w_id} | |||
{UNIQUE: w_typ, w_bezeichung} | |||
liefert: h_id, w_id, l_preis, l_lieferzeit* {PK: s_id, w_id} | |||
{FK: h_id -> haendler: h_id} | |||
{FK: w_id -> ware: w_id} | |||
{l_lieferzeit > 0} | |||
</source> | |||
Dieses Schema wurde nach dem in [[Kowarschick (MMDB-Skript)]] beschriebenen Verfahren aus dem obigen ER-Diargramm erzeugt. | Dieses Schema wurde nach dem in [[Kowarschick (MMDB-Skript)]] beschriebenen Verfahren aus dem obigen ER-Diargramm erzeugt. | ||
==Datenbankschema (SQL)== | ==Datenbankschema (SQL)== | ||
Aus dem obigen Datenbankschema leitet sich folgende SQL-[[DDL]]-Befehle ab: | |||
<source lang="sql"> | <source lang="sql"> | ||
/ | /* Alte Tabellen (in der richtigen Reihenfolge) löschen: */ | ||
DROP TABLE IF EXISTS liefert CASCADE; | DROP TABLE IF EXISTS liefert CASCADE; | ||
Zeile 43: | Zeile 55: | ||
DROP TABLE IF EXISTS haendler CASCADE; | DROP TABLE IF EXISTS haendler CASCADE; | ||
/ | /* Tabellen erstellen */ | ||
CREATE TABLE haendler | CREATE TABLE haendler | ||
( | (h_id INTEGER NOT NULL, | ||
h_name VARCHAR(30) NOT NULL, | |||
h_adresse VARCHAR(50), | |||
CONSTRAINT pk_haendler | CONSTRAINT pk_haendler | ||
PRIMARY KEY ( | PRIMARY KEY (h_id), | ||
CONSTRAINT | CONSTRAINT unique_haendler_name_adresse | ||
UNIQUE ( | UNIQUE (h_name, h_adresse) | ||
); | ); | ||
CREATE TABLE ware | CREATE TABLE ware | ||
( | (w_id INTEGER NOT NULL, | ||
w_typ VARCHAR(30) NOT NULL DEFAULT 'Sonstiges', | |||
w_bezeichnung VARCHAR(50) NOT NULL, | |||
CONSTRAINT pk_ware | CONSTRAINT pk_ware | ||
PRIMARY KEY ( | PRIMARY KEY (w_id), | ||
CONSTRAINT | CONSTRAINT unique_haendler_typ_bezeichnung | ||
UNIQUE ( | UNIQUE (w_typ, w_bezeichnung) | ||
); | ); | ||
CREATE TABLE liefert | CREATE TABLE liefert | ||
( | (h_id INTEGER NOT NULL, | ||
w_id INTEGER NOT NULL, | |||
l_preis NUMERIC(8,2) NOT NULL, | |||
l_lieferzeit SMALLINT, /* Tage */ | |||
CONSTRAINT pk_liefert | CONSTRAINT pk_liefert | ||
PRIMARY KEY ( | PRIMARY KEY (h_id, w_id, l_preis), | ||
CONSTRAINT fk_liefert_haendler | CONSTRAINT fk_liefert_haendler | ||
FOREIGN KEY ( | FOREIGN KEY (h_id) REFERENCES haendler (h_id), | ||
CONSTRAINT fk_liefert_ware | CONSTRAINT fk_liefert_ware | ||
FOREIGN KEY ( | FOREIGN KEY (w_id) REFERENCES ware (w_id), | ||
CONSTRAINT | CONSTRAINT check_liefert_lieferzeit | ||
CHECK ( | CHECK (l_lieferzeit > 0) | ||
); | ); | ||
</source> | </source> | ||
Zeile 95: | Zeile 104: | ||
<source lang="sql"> | <source lang="sql"> | ||
INSERT INTO haendler( | INSERT INTO haendler(h_id, h_name, h_adresse) | ||
VALUES | VALUES | ||
(1, 'Maier', 'Königsbrunn'), | (1, 'Maier', 'Königsbrunn'), | ||
Zeile 104: | Zeile 113: | ||
; | ; | ||
INSERT INTO ware( | INSERT INTO ware(w_id, w_typ, w_bezeichnung) | ||
VALUES | VALUES | ||
(1, 'CPU', 'Pentium IV 3,8'), | (1, 'CPU', 'Pentium IV 3,8'), | ||
Zeile 113: | Zeile 122: | ||
; | ; | ||
INSERT INTO liefert( | INSERT INTO liefert(h_id, w_id, l_preis, l_lieferzeit) | ||
VALUES | VALUES | ||
(1, 1, 200.00, 1), | (1, 1, 200.00, 1), |
Version vom 11. Mai 2018, 16:52 Uhr
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
Eine Händler, der durch die Händler-ID h_id
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 (die Klasse) haendler
definiert.
Welche Waren es gibt, ist durch einen weiteren Entity-Typ festgelegt: ware
. Eine Ware ist durch die WarenID w:id
eindeutig festgelgt. Weitere Attribute, wie Typ (Gemüse, Fleisch, Wurst etc.) und genaue Bezeichnung (Kohlrabi, Rinderlende, Cervelat 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. Um dies zu ermöglichen, muss das Attribut l_preis
zum orginären Primärschlüssel (h_id, w_id)
der Beziehung liefert
hinzugefügt werden.
Datenbankschema
haendler: h_id, h_name, h_adresse* {PK: s_id}
{UNIQUE: h_name, h_adresse}
ware: w_id, w_typ, w_bezeichnung {PK: w_id}
{UNIQUE: w_typ, w_bezeichung}
liefert: h_id, w_id, l_preis, l_lieferzeit* {PK: s_id, w_id}
{FK: h_id -> haendler: h_id}
{FK: w_id -> ware: w_id}
{l_lieferzeit > 0}
Dieses Schema wurde nach dem in Kowarschick (MMDB-Skript) beschriebenen Verfahren aus dem obigen ER-Diargramm erzeugt.
Datenbankschema (SQL)
Aus dem obigen Datenbankschema leitet sich folgende SQL-DDL-Befehle ab:
/* Alte Tabellen (in der richtigen Reihenfolge) löschen: */
DROP TABLE IF EXISTS liefert CASCADE;
DROP TABLE IF EXISTS ware CASCADE;
DROP TABLE IF EXISTS haendler CASCADE;
/* Tabellen erstellen */
CREATE TABLE haendler
(h_id INTEGER NOT NULL,
h_name VARCHAR(30) NOT NULL,
h_adresse VARCHAR(50),
CONSTRAINT pk_haendler
PRIMARY KEY (h_id),
CONSTRAINT unique_haendler_name_adresse
UNIQUE (h_name, h_adresse)
);
CREATE TABLE ware
(w_id INTEGER NOT NULL,
w_typ VARCHAR(30) NOT NULL DEFAULT 'Sonstiges',
w_bezeichnung VARCHAR(50) NOT NULL,
CONSTRAINT pk_ware
PRIMARY KEY (w_id),
CONSTRAINT unique_haendler_typ_bezeichnung
UNIQUE (w_typ, w_bezeichnung)
);
CREATE TABLE liefert
(h_id INTEGER NOT NULL,
w_id INTEGER NOT NULL,
l_preis NUMERIC(8,2) NOT NULL,
l_lieferzeit SMALLINT, /* Tage */
CONSTRAINT pk_liefert
PRIMARY KEY (h_id, w_id, l_preis),
CONSTRAINT fk_liefert_haendler
FOREIGN KEY (h_id) REFERENCES haendler (h_id),
CONSTRAINT fk_liefert_ware
FOREIGN KEY (w_id) REFERENCES ware (w_id),
CONSTRAINT check_liefert_lieferzeit
CHECK (l_lieferzeit > 0)
);
Beispielsdaten
INSERT INTO haendler(h_id, h_name, h_adresse)
VALUES
(1, 'Maier', 'Königsbrunn'),
(2, 'Müller', 'Königsbrunn'),
(3, 'Maier', 'Augsburg'),
(4, 'Huber', NULL),
(5, 'Schmidt', 'Hamburg')
;
INSERT INTO ware(w_id, w_typ, w_bezeichnung)
VALUES
(1, 'CPU', 'Pentium IV 3,8'),
(2, 'CPU', 'Celeron 2,6'),
(3, 'CPU', 'Athlon XP 3000+'),
(4, 'RAM', 'SDRAM 1GB'),
(5, 'Sonstiges', 'Eieruhr')
;
INSERT INTO liefert(h_id, w_id, l_preis, l_lieferzeit)
VALUES
(1, 1, 200.00, 1),
(1, 1, 194.00, 6),
(1, 2, 100.00, NULL),
(1, 3, 150.00, 7),
(1, 4, 10.00, 1),
(1, 5, 5.00, 1),
(2, 1, 190.00, NULL),
(2, 3, 170.00, 4),
(2, 1, 160.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)
;
SQL-Beispiele
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ändler2-Datenbank (SQL-Beispiel) (komplexere Version dieser Datenbank)