Lösung 8

SPITZBART Roman
Matr-Nr.: 0055050
Aufgabe 1
Annahmen: max. 1000 Filialen pro Bankleitzahl
österreichische Postleitzahlen (4stellig)
CREATE TABLE Filiale (
blz
NUMBER(5),
fnr
NUMBER(3),
strasse
VARCHAR(40) NOT NULL,
plz
NUMBER(4) NOT NULL,
PRIMARY KEY (blz, fnr));
CREATE TABLE Konto (
knr
NUMBER(10) PRIMARY KEY,
saldo
NUMBER(12,4) NOT NULL,
eroeffDat
DATE NOT NULL,
blz
NUMBER(5),
fnr
NUMBER(3),
FOREIGN KEY (blz,fnr)
REFERENCES Filiale(blz,fnr)
ON UPDATE CASCADE,
ON DELETE CASCADE);
CREATE TABLE Umsatz (
unr
NUMBER(12),
bnr
NUMBER(12) NOT NULL,
datum
DATE NOT NULL,
betrag
NUMBER(12,4) NOT NULL,
text
VARCHAR(50) NOT NULL,
knr
NUMBER(10),
blz
NUMBER(5),
fnr
NUMBER(3),
PRIMARY KEY (unr, knr),
FOREIGN KEY (knr)
REFERENCES Konto(knr)
ON UPDATE CASCADE,
ON DELETE CASCADE),
FOREIGN KEY (blz,fnr)
REFERENCES Filiale(blz,fnr)
ON UPDATE CASCADE,
ON DELETE CASCADE,
CHECK betrag > 0);
Seite 1 / 4
Übung 8
SPITZBART Roman
Matr-Nr.: 0055050
CREATE TABLE Person (
svnr
NUMBER(10) PRIMARY KEY,
vorname
VARCHAR(30) NOT NULL,
zuname
VARCHAR(30) NOT NULL);
CREATE TABLE Angest (
svnr
NUMBER(10),
gehalt
NUMBER(12,4) NOT NULL,
einDatum
DATE NOT NULL,
blz
NUMBER(5),
fnr
NUMBER(3),
PRIMARY KEY (svnr),
FOREIGN KEY (svnr)
REFERENCES Person(svnr)
ON UPDATE CASCADE,
ON DELETE CASCADE,
FOREIGN KEY (blz,fnr)
REFERENCES Filiale(blz,fnr),
ON UPDATE CASCADE,
ON DELETE CASCADE);
CREATE TABLE Kunde (
kunr
NUMBER(10) PRIMARY KEY,
svnr
NUMBER(10),
FOREIGN KEY (svnr)
REFERENCES Person(svnr),
ON UPDATE CASCADE,
ON DELETE CASCADE);
CREATE TABLE Filiale_betreute_Kunde (
blz
NUMBER(5),
fnr
NUMBER(3),
kunr
NUMBER(10),
PRIMARY KEY (blz,fnr,kunr),
FOREIGN KEY (blz,fnr)
REFERENCES Filiale(blz,fnr),
ON DELETE CASCADE,
ON UPDATE CASCADE,
FOREIGN KEY (kunr)
REFERENCES Kunde(kunr),
ON DELETE CASCADE,
ON UPDATE CASCADE);
Seite 2 / 4
Übung 8
SPITZBART Roman
Matr-Nr.: 0055050
CREATE TABLE Zeichnungsber (
knr
NUMBER(10),
kunr
NUMBER(10),
seit
DATE NOT NULL,
PRIMARY KEY (knr,kunr),
FOREIGN KEY (kunr)
REFERENCES Kunde(kunr),
ON DELETE NO ACTION,
ON UPDATE CASCADE,
FOREIGN KEY (knr)
REFERENCES Konto(knr),
ON DELETE NO ACTION,
ON UPDATE CASCADE);
Es darf keinen Umsatz geben bei dem sich die blz/fnr (Schlüssel für Filiale) von denen
unterscheiden, die über die knr des Umsatzes gefunden werden können.
CREATE ASSERTION UmsatzAndKontoHaveSameFiliale CHECK
NOT EXISTS (SELECT *
FROM Umsatz u
WHERE (u.blz,u.fnr) NOT IN (SELECT k.blz,k.fnr
FROM Konto k
WHERE k.knr=u.knr))
DEFERABLE INITIALLY DEFERRED;
Es darf keine Einträge in Person geben zu denen es keine Eintrag in Kunde und/oder
Angest gibt.
CREATE ASSERTION PersonAbstract CHECK
NOT EXISTS (SELECT *
FROM Person
WHERE svnr NOT IN (SELECT svnr FROM Kunde
UNION
SELECT svnr FROM Angest))
DEFERABLE INITIALLY DEFERRED;
Es gelten die folgenden Inklusionsabhängigkeiten:
πfnr, blz (Konto) ⊆ π fnr, blz (Filiale)
π knr (Umsatz) ⊆ π knr (Konto)
πfnr, blz (Umsatz) ⊆ πfnr, blz (Filiale)
πsvnr (Angest) ⊆ π svnr (Person)
πfnr, blz (Angest) ⊆ π fnr, blz (Filiale)
πsvnr (Kunde) ⊆ π svnr (Person)
πfnr, blz (Filiale_betreut_Kunde) ⊆ π fnr, blz (Filiale)
π kunr (Filiale_betreut_Kunde) ⊆ π kunr (Kunde)
π kunr (Zeichnungsber) ⊆ π kunr (Kunde)
π knr (Zeichnungsber) ⊆ π knr (Konto)
Seite 3 / 4
Übung 8
SPITZBART Roman
Matr-Nr.: 0055050
Referentielle Integrität: Mathematisch ausgedrückt bedeutet referentielle Integrität, daß
die Menge der verweisenden Nummern immer eine Teilmenge der
Nummern sein muß, auf die verwiesen wird.
Das heißt es dürfen in einem Fremdschlüssel-Feld keine Werte
enthalten sein, die nicht in der Tabelle stehen auf die der
Fremschlüssel verweist.
In SQL wird die referentielle Integrität durch das Statment
FOREIGN KEY gewährleistet.
Beispiel zur referentielle Integrität (Datenmodell siehe weiter oben):
Filiale( blz,
20000
20001
20002
fnr, ...)
1
2
1
Versuch in Konto (knr, saldo, eroeffDat, blz, fnr) den folgenden Tupel einzufügen:
1, 0.0, 20011210, 20000, 2
Wird von der Datenbank nicht zugelassen da hier die referentielle Integrität verletzt wäre
(blz-fnr 20000-2 existiert in Filiale nicht).
Seite 4 / 4
Übung 8