Schema AUFTRAGSVER

Im Schema AUFTRAGSVER der Datenbank AUFTRAGKUNDEN ist ein Informationssystem zur Auftragsabwicklung eines kleinen Data Centers realisiert. Es sollen Informationen über Kunden, über Kontaktpersonen bei den Kunden, über Aufträge dieser Kunden und über Leistungen erfasst werden, die zu diesen Aufträgen erbracht wurden. Das Schema AUFTRAGSVER enthält die Tabellen KUNDE, KONTAKT, AUFTRAG, LEISTUNG und AUFSTAT.

Folgendes Bild zeigt in der Übersicht die Basistabellen des Schemas AUFTRAGSVER und die Abhängigkeiten zwischen den Tabellen.

Bild 5: Basistabellen des Schemas AUFTRAGSVER; referenzierende Fremdschlüssel sind grau unterlegt

Tabelle KUNDE

Die Tabelle KUNDE enthält Informationen über die Kunden. Neben einer eindeutigen Kundennummer enthält die Tabelle den Namen, die Adresse, die Telefonnummer sowie Informationen über die Branche des Kunden. Die Tabelle KUNDE wird mit der folgenden Tabellendefinition erzeugt:

CREATE TABLE	kunde
(knr	INTEGER CONSTRAINT knr_primary PRIMARY KEY,
firma	CHAR(40) CONSTRAINT firma_notnull NOT NULL,
strasse	CHAR(40),
plz	NUMERIC(5),
ort	CHAR(40),
land	CHAR(3),
ktelefon	CHAR(25),
kinfo	CHAR(50)
CONSTRAINT PlausPlz	CHECK( land IS NULL OR plz IS NULL OR
	(land = 'D' AND plz >= 00000)
	OR (land <> 'D'))
)

KNR ist der Primärschlüssel von KUNDE. Es ist eine CHECK-Bedingung definiert, die die Plausibilität der Postleitzahlen abprüft. Die Tabelle KUNDE enthält folgende Daten:

knr	firma	strasse	plz	ort	land	ktelefon	kinfo
100	Siemens AG	Otto-Hahn-Ring 6	81739	Muenchen	D	089/636-8	Elektro
101	Login GmbH	Rosenheimer Str. 34	81667	Muenchen	D	089/4488870	PC Netzwerke
102	JIKO GmbH	Posener Str. 12	30659	Hannover	D	0551/123874	Import-Export
103	Plenzer Trading	Paul-Heyse-Str. 12	80336	Muenchen	D	089/923764	Fruechtehandel
104	Jonas Fischladen	Hirschgartenstr. 12	12587	Berlin	D	016/5739921	Einzelhandel
105	Pudelshop Anke	Am Muehlentor 26	41179	Moenchengladbach	D	040/873562	Dienstleistung
106	Foreign Ltd.	26 West York St.		New York, NY	USA	001703/2386532	Handelsvertretung
107	Externa & Co KG	Berner Weg 78	3000	Bern 33	CH		Anwaltskanzlei

Tabelle 4: Daten der Tabelle KUNDE

Tabelle KONTAKT

Die Tabelle KONTAKT enthält Informationen über die Kontaktpersonen bei einem bestimmten Kunden. Sie besteht aus einer eindeutigen Kontaktnummer, der Nummer des Kunden in der Tabelle KUNDE, Vorname, Nachname, Anrede und Telefonnummer der Kontaktperson sowie Informationen über Stellung und Abteilung der Kontaktperson und den Anlass des Kontakts. Die Tabelle KONTAKT wird mit der folgenden Tabellendefinition erzeugt:

CREATE TABLE	kontakt
(konr	INTEGER CONSTRAINT konr_primary PRIMARY KEY,
knr	INTEGER CONSTRAINT ko_knr_notnull NOT NULL,
vorname	CHAR(25),
nachname	CHAR(25) CONSTRAINT name_notnull NOT NULL,
anrede	CHAR(20),
kotelefon	CHAR(25),
funktion	CHAR(50),
abteilung	CHAR(30),
koinfo	CHAR(50),
CONSTRAINT	ko_knr_ref_kunde FOREIGN KEY (knr) REFERENCES kunde
)

KONR ist der Primärschlüssel der Tabelle KONTAKT. Für die Tabelle ist eine Referenzbedingung definiert. Der Fremdschlüssel KNR bezieht sich auf den Primärschlüssel KUNDE.KNR der referenzierten Tabelle KUNDE.
Die Tabelle KONTAKT enthält folgende Daten:

konr	knr	vorname	nachname	anrede	kotelefon	funktion	abteilung	koinfo
10	100	Walter	Kuehne	Herr Dr.	089/6361896	Vorstand	Personal
11	100	Stefan	Walkers	Herr	089/63640182	Sekretaer	Vertrieb
20	101	Roland	Loetzerich	Herr	089/4488870	Geschaeftsfuehrer		Netzwerke
25	102	Ewald	Schmidt	Herr	0551/123873	Schulung
26	103	Beate	Kredler	Frau	089/923764	Organisation		SQL- Kurs
30	104	Xaver	Bauer	Herr	016/6739921	Verkaeufer
35	105	Anke	Buschmann	Frau	02161/584097	Geschaeftsfuehrer
40	106	Mary	Davis	Ms.	001703/2386531	Leitung	Einkauf
41	106	Robert	Heinlein	Mr.	001703/2386532	Ausbilder	Einkauf

Tabelle 5: Daten der Tabelle KONTAKT

Tabelle AUFSTAT

Die Tabelle AUFSTAT ermöglicht die Zuordnung der Auftragsstatus-Nummern in der Tabelle AUFTRAG (Spalte ASTNR) zu den entsprechenden Texten. Sie enthält die eindeutige Auftragsstatus-Nummer und den entsprechenden Text.

Die Tabelle AUFSTAT wird mit der folgenden Tabellendefinition erzeugt:

CREATE TABLE	aufstat
(astnr	INTEGER CONSTRAINT astnr_primary PRIMARY KEY,
astxt	CHAR(15) CONSTRAINT astxt_notnull NOT NULL
)

Die Tabelle AUFSTAT enthält folgende Daten:

astnr	astxt
1	geplant
2	Vertrag
3	erledigt
4	abgerechnet
5	Ablage

Tabelle 6: Daten der Tabelle AUFSTAT

Tabelle AUFTRAG

In der Tabelle AUFTRAG befinden sich die Grunddaten zu einem Auftrag. Die Tabelle enthält eine eindeutige Auftragsnummer, Verweise auf den Kunden und den Kundenkontakt, das Datum der Auftragserteilung, die Bezeichnung des Auftrags, Ist- und Soll-Termin der Fertigstellung sowie eine Auftragsstatus-Nummer.
Die Tabelle AUFTRAG wird mit der folgenden Tabellendefinition erzeugt:

CREATE TABLE	auftrag
(anr	INTEGER CONSTRAINT anr_primary PRIMARY KEY,
knr	INTEGER CONSTRAINT a_knr_notnull NOT NULL,
konr	INTEGER,
adatum	DATE DEFAULT CURRENT_DATE,
atext	CHAR(30),
fertigist	DATE,
fertigsoll	DATE,
astnr	INTEGER DEFAULT 1 CONSTRAINT astat_notnull NOT NULL,
CONSTRAINT	a_knr_ref_kunde FOREIGN KEY (knr) REFERENCES kunde,
CONSTRAINT	konr_ref_kontakt FOREIGN KEY (konr) REFERENCES kontakt,
CONSTRAINT	astnr_ref_aufstat FOREIGN KEY (astnr) REFERENCES aufstat(astnr)
)

ANR ist der Primärschlüssel der Tabelle AUFTRAG. Der Fremdschlüssel KNR bezieht sich auf den Primärschlüssel KUNDE.KNR der Tabelle KUNDE, der Fremdschlüssel KONR bezieht sich auf den Primärschlüssel KONTAKT.KONR der Tabelle KONTAKT, und der Fremdschlüssel ASTNR bezieht sich auf den Primärschlüssel ASTNR der Tabelle AUFSTAT.
Die DEFAULT-Klausel bei der Spaltendefinition für ADATUM legt mit CURRENT_DATE als voreingestellten Wert das aktuelle Datum fest. Die DEFAULT-Klausel für ASTNR legt den Wert 1 als voreingestellten Wert fest. Die Tabelle AUFTRAG enthält folgende Daten:

anr	knr	konr	adatum	atext	fertigist	fertigsoll	astnr
200	102	25	2009-04-15	Mitarbeiterschulung	2009-05-02	2009-05-02	5
210	106	40	2009-12-15	Kunden-Verwaltung	2010-04-12	2010-04-01	3
211	106	41	2009-12-29	Datenbank-Entwurf Kunden	2010-04-09	2010-04-01	4
250	105	35	2010-01-19	Serienbrief-Einweisung		2010-03-03	2
251	105	35	2010-01-19	Kunden-Verwaltung		2010-05-02	2
300	101	20	2010-02-16	Netzwerk-Test/Vergleich			1
305	105	35	2010-04-28	Mitarbeiterschulung		2010-05-02	2

Tabelle 7: Daten der Tabelle AUFTRAG

Tabelle LEISTUNG

Die Tabelle LEISTUNG enthält die einzelnen zu einem Auftrag erbrachten Leistungen. Die Tabelle enthält eine eindeutige Leistungsnummer, die zugehörige Auftragsnummer, das Datum, an dem die Leistung erbracht wurde, die Bezeichnung der Leistung, die Leistungseinheit, Anzahl der Einheiten, Preis pro Einheit, zugehöriger Mehrwertsteuersatz und Rechnungsnummer.

Die Tabelle LEISTUNG wird mit der folgenden Tabellendefinition erzeugt:

CREATE TABLE	leistung
(lnr	INTEGER CONSTRAINT lnr_primary PRIMARY KEY,
anr	INTEGER CONSTRAINT l_anr_notnull NOT NULL,
ldatum	DATE,
ltext	CHAR(25),
leinheit	CHAR(10),
lanz	INTEGER CONSTRAINT lanz_pos CHECK (lanz > 0),
lsatz	NUMERIC (5,0),
mwsatz	NUMERIC(2,2),
rnr	NUMERIC(4,0),
CONSTRAINT	anr_ref_auftrag FOREIGN KEY(anr) REFERENCES auftrag
)

LNR ist der Primärschlüssel der Tabelle LEISTUNG. Der Fremdschlüssel ANR bezieht sich auf den Primärschlüssel AUFTRAG.ANR der Tabelle AUFTRAG.

Die Tabelle LEISTUNG enthält folgende Daten:

lnr	anr	ldatum	ltext	leinheit	lanz	lsatz	mwsatz	rnr
1	200	2009-04-20	Schulungsmaterial	Seiten	45	75	0.19	3
2	200	2009-04-22	Schulung	Tag	1	1500	0.19	3
3	200	2009-04-23	Schulung	Tag	1	1500	0.19	3
4	211	2010-01-21	Systemanalyse	Tag	8	1200	0.00	10
5	211	2010-01-28	Datenbankentwurf	Tag	10	1200	0.00	10
6	211	2010-02-16	Kopien/Folien	Seiten	30	50	0.19	10
7	211	2010-03-24	Handbuch	Festpreis	1	200	0.07	10
10	250	2010-02-23	Reisekosten	Festpreis	2	125	0.00
11	250	2010-02-23	Schulung	Tag	1	1200	0.19

Tabelle 8: Daten der Tabelle LEISTUNG