3. Das Relationale Datenmodell

Das Relationale Datenmodell
3. Das Relationale Datenmodell
‰
geht zurück auf Codd (1970):
E. F. Codd: A Relational Model of Data for Large Shared Data Banks. Comm. of the ACM
13(6): 377-387(1970)
‰ DBMS wie z. B. Oracle, SQL Server, Sybase, Informix, DB/2 basieren auf dem relationalen
Modell
‰ Relationales Modell
–
Datentypen:
Menge, Multimenge, …
–
Operationen:
Vereinigung, Differenz, …
Seite 33 von 57
Das Relationale Datenmodell
3.1 Basiskomponenten des Datenmodells
‰
‰
Wertebereich (“domain”): Integer, String[20], Datum, …
Relation R ist definiert auf einem Relationenschema RSR.
–
RSR entspricht einer Menge von Attributen {A1,…,Ak}
–
zu jedem Attribut Aj gibt es einen Wertebereich Dj = dom(Aj)
–
eine Instanz IR einer Relation R ist eine Teilmenge des kartesischen Produkts der
Wertebereiche:
IR ⊆ D1 × D2 … × Dk , k ≥ 1
‰
Sprechweisen
–
Der Begriff Relation wird oft für die Instanz einer Relation verwendet.
–
Ist klar, um welche Relation es sich handelt, wird der Index bei der Instanz und beim
Schema einfach weggelassen.
‰
Beispiel:
Wertebereiche: D 1 = { a, b, c }, D 2 = { 0, 1 }
Kartesisches Produkt: D 1 × D 2 = { ( a, 0 ), ( a, 1 ), ( b, 0 ), ( b, 1 ), ( c, 0 ), ( c, 1 ) }
mögliche Instanzen: I 1 = { ( a, 0 ), ( b, 0 ), ( c, 0 ), ( c, 1 ) }
und
I2 = ∅
Seite 34 von 57
Das Relationale Datenmodell
Notation:
‰
‰
‰
‰
Tupel: Element einer Relationeninstanz.
Für eine Relation mit k Attributen bezeichnet k den Grad der Relation.
Beschränkung auf endliche Instanzen einer Relation.
Darstellung der Relationen durch Tabellen:
R1 A B
a 0
b 0
c 0
c 1
A und B sind hier Spaltennamen (Attributsnamen)
Reihenfolge der Attribute
‰
‰
Reihenfolge der Zeilen (Tupel) ist bisher von Bedeutung
Wunsch: Reihenfolge der Spalten (Attribute) soll nicht von Bedeutung sein.
–
D = ∪1 ≤ j ≤ k dom(Aj)
–
Eine Relationeninstanz I ist eine endliche Menge von totalen Abbildungen
I = {t1,…,tn | mit tj: RS --> D; tj (Am) ∈ dom(Am), 1 ≤ m ≤ k}
Seite 35 von 57
Das Relationale Datenmodell
Beispiel:
Städte Name
Einwohner Land
München 1.211.617 Bayern
Bremen 535.058
Bremen
‰ Relationenschema der Relation Städte:
{Name, Einwohner, Land} mit dom(Name) = String[40], dom(Einwohner) =
INTEGER und dom(Land) = String[40]
‰ Ausprägungen: t1 und t2
t1(Name) = München, t1(Einwohner) = 1211617 und t1(Land) = Bayern
t2(Name) = Bremen, t2(Einwohner) = 535058 und t2(Land) = Bremen
‰
Sei RS ein Relationenschema und X ⊆ RS dann bezeichnet t[X] das Tupel t eingeschränkt auf
X.
Notation:
‰
‰
‰
Datenbankschema: Menge der Relationenschemata
Datenbank: Menge der aktuellen Instanzen der Relationen
Die bisherige Definition einer Relation läßt Instanzen zu, die real nicht exisitieren können. Es
ist hier sinnvoll, die Instanzen durch geeignete semantische Bedingungen einzuschränken.
Seite 36 von 57
Das Relationale Datenmodell
Schlüssel
‰
‰
analog zum Schlüsselbegriff des ER Modells
gegeben: Relation R mit Schema RS
‰
X ⊆ RS wird als Schlüssel bezeichnet, wenn folgende Bedingungen erfüllt sind:
– Eindeutigkeit: für alle (real möglichen) Relationeninstanzen I der Relation R gilt: Für
zwei beliebige Tupel t1 und t2 aus I gilt:
t1 [ X ] = t2 [ X ] ⇒ t1 = t2
–
Minimalität: es gibt kein Y ⊂ X ( Y ≠ X ), so daß die Eindeutigkeit erfüllt ist.
Beachte:
Schlüsseleigenschaft bezieht sich nicht auf die aktuell abgespeicherten Instanz einer Relation,
sondern ist eine semantische Eigenschaft des Relationenschema
‰ Mehrere Teilmengen eines Relationenschemas können Schlüssel sein. Dann wird einer von
diesen als Primärschlüssel ausgezeichnet.
‰ Die Attribute des Primärschlüssels werden im Schema oft durch Unterstreichen
hervorgehoben.
–
Relation Städte mit Schema {Name, Einwohner, Land}
Seite 37 von 57
Das Relationale Datenmodell
3.2 Abbildung eines ER Schema in ein
relationales Schema
‰
‰
Datenstruktur der ER-Datenmodellierung
–
Entitätstypen
–
Beziehungstypen
Datenstruktur des relationalen Modells
–
Relationen (bzw. Relationenschema)
Fragestellung
‰
‰
Wie kann ein ER Datenmodell in ein relationales Model umgesetzt werden?
Diese Frage wird nun in zwei Schritten beantwortet:
–
Einfache Umsetzung von Entitätstypen und Beziehungstypen
–
Konsolidierung des Relationenschema
Seite 38 von 57
Das Relationale Datenmodell
Umsetzung eines Entitätstyps
‰
‰
‰
‰
Der Entitätsyp wird als eigenständige Relation umgesetzt, wobei jede Eigenschaft des
Entitätstyps durch ein Attribut in der Relation dargestellt wird.
Die Namen der Attribute können entsprechend den Namen der Eigenschaften des Entitätstyps
gewählt werden.
Der Schlüssel des Entitätstyps wird als Primärschlüssel des Relationenschema vereinbart.
Beispiel (siehe ER Diagramm aus Kapitel ):
–
Entität Zug:
{ZugNr: Integer, Typ: String}
–
Entität Bahnhof
{Id: Integer, Stadt: String, Name: String}
Seite 39 von 57
Das Relationale Datenmodell
Umsetzung eines Beziehungstyps
‰
‰
‰
‰
Der Beziehungstyp wird als eigenständige Relation umgesetzt, wobei
–
die Primärschlüssel der beteiligten Entitäten vollständig im Relationenschema
aufgenommen werden
–
und jede Eigenschaft des Beziehungstyps durch ein Attribut in der Relation dargestellt
wird.
Die aus einem Primärschlüssel gewonnen Attribute werden als Fremdschlüssel bezeichnet.
Die Namen der Attribute eines Fremdschlüssel müssen z. T. umbenannt werden, damit eine
Eindeutigkeit der Namen gewährleistet wird.
Beispiel (m:n Beziehungstyp)
–
Umsetzung des m:n Beziehungstyps hält und die an dem Beziehungstyp beteiligten
Entitätstypen Zug und Bahnhof.
–
Der Schlüssel von hält entspricht dabei der Menge von Fremdschlüsseln.
Zug
Id
2475
95786
…
Typ
IR
RB
…
hält Zug
2475
2475
95786
…
Station
1
2
1
…
Gleis
4
10
2
…
Bahnhof
Id
1
2
3
Stadt
Marburg
Darmstadt
Wetter
Name
Lahn
Hbf
HessNass
… …
Seite 40 von 57
Das Relationale Datenmodell
‰
Beispiel (Umsetzung eines 1:n Beziehungstyps)
–
Betrachten wir hierzu ein Beispiel aus der Universitätswelt, wo ein Professor
Vorlesungen abhält. Wenn der Primärschlüssel von Professor die PersNr und die von
Vorlesung die VorNr ist, würden die Beziehung liest als zweistellige Relation umgesetzt
werden.
1
Professor
–
‰
liest
n
Vorlesung
Man beachte aber, daß {PersNr, VorNr} kein Schlüssel von liest ist, da die Eigenschaft
der Minimalität verletzt ist. {VorNr} erfüllt aber die Eigenschaft eines Schlüssels.
Mit der Fremdschlüsseleigenschaft bezeichnet man nun folgende Nebenbedingung:
– Sei F der Fremdschlüssel in R, der sich auf eine Relation S bezieht, so muß stets
IR [ F ] ⊆ IS [ F ]
erfüllt sein.
Seite 41 von 57
Das Relationale Datenmodell
Konsolidierung des Schemas
‰
‰
Mögliche Vereinfachungen des Schemas durch Verschmelzen von Relationen, die aus einer
zweistelligen Beziehungstyp der Kardinalität 1:1 oder 1:n hervorgegangen sind.
Seien R und S, die Relationen der beteiligten Entitätstypen eines 1:n Beziehungstyps, und T
die Relation des Beziehungstyps. Dann können wir alternativ folgendes Schema verwenden:
–
Statt der Relation S und T wird eine neue Relation U eingführt, wobei
RS U = RS S ∪ RS T
Die Instanzen von U sollen nun die gleiche Information wie zuvor die Instanzen von S
und T enthalten. Hierzu verknüpfen wir die Tupel aus S und T bzgl. ihrer Fremdschlüssel.
Für die Tupel s ∈ I S , die kein t ∈ I T mit s[F] = t[F] besitzen, wird ein Tupel u erzeugt, so
‰
daß u [ RS S ] = s . Die anderen Werte von u werden mit einem speziellen Wert (NULL)
aufgefüllt.
–
Die Relation R bleibt unverändert bestehen.
Was sind die Vor- und Nachteile einer solchen Konsolidierung?
Seite 42 von 57
Das Relationale Datenmodell
Umsetzung von IS-A Beziehungstypen
‰
‰
IS-A Beziehungstypen werden nicht durch eine eigene Relation repräsentiert. Die Beziehung
wird dadurch bereits ausgedrückt, wenn der Schlüssel des allgemeinen Typs auch als Schlüssel
der spezializierten Typen benutzt wird.
Beispiel:
Angestellter
ANGNR
Name
IS-A
Fachgebiet
Professor
Mitarbeiter
Thema
Daraus ergeben sich drei Relationen mit dem Schema {ANGNR, Name}, {ANGNR,
Fachgebiet} und {ANGNR, Thema}.
‰ Als Alternative bietet sich auch an, den IS-A Beziehungstyp und die beteiligten Entitätstypen
durch eine Relation mit Schema {ANGNR, Name, Fachgebiet, Thema} zu präsentieren und
bei den Instanzen die fehlende Information durch NULL-Werte aufzufüllen.
Was ist bei diesem Ansatz problematisch?
Seite 43 von 57
Das Relationale Datenmodell
3.3 Die relationale Algebra
Algebra:
‰
gegeben eine Menge N (“Anker der Algebra”)
‰
Menge von Operationen { σ 1 ,… ,σ n } der Form σ j :N k → N
Relationale Algebra
‰
‰
Anker ist die Menge aller Relationen
insgesamt gibt es 6 Grundoperationen
Seite 44 von 57
Das Relationale Datenmodell
3.3.1 Grundoperationen der relationalen
Algebra
‰
‰
besteht aus 5 (+1) Grundoperationen
gegeben zwei Relationen R(A1,…,Ar) und S(B1,…,Bs) mit Grad r und Grad s
‰
zur Erleichterung der Definition sei hier angenommen, daß die Attribute der Relationen
geordnet sind. Dann gilt:
IR = {(a1,…,ar) | ai ∈ dom(Ai), 1 ≤ i ≤ r} und IS = { (b1,…,bs) | bi ∈ dom(Bi), 1 ≤ i ≤ s}
‰
‰
Vereinigung: R ∪ S
R und S besitzen gleiches Relationenschema (bis auf Umbenennungen identisch)
‰
Differenz: R – S
R und S besitzen gleiches Relationenschema (bis auf Umbenennungen identisch)
‰
Kartesisches Produkt: R × S
Relationenschema der Resultatsrelation ergibt sich aus der Vereinigung der Schemata der
beteiligten Relationen.
‰
Projektion: π i
–
1,
…, i m ( R )
neues Relationenschema = selektierte Spalten des alten Relationschema
Seite 45 von 57
Das Relationale Datenmodell
Sei { i 1, …, i m } ⊆ { 1, …, r } mit paarweise verschiedenen i j . Dann ist:


π i , …, i ( R ) =  ( c 1, …, c m ) ∃( a 1, …, a r ) ∈ R : c j = a i für j = 1…m 
1
m
j


Statt Indizes können auch Attributsnamen verwendet werden (X = {A1,A2}):
π X ( R ) = π A , A ( R ) = π 1, 2 ( R )
1 2
‰
Selektion: σ F ( R )
–
–
–
neues Relationenschema = altes Relationenschema
Die zu berechnende Instanz besteht aus allen Tupeln der Relation R, die der Formel F
genügen.
Formel F setzt sich zusammen aus
a) Operanden: Konstanten oder Name eines Attributs
b) Vergleichsoperatoren:
= ,≠,<,≤,>,≥
c) Boole’sche Operatoren: ∧ , ∨ , ¬
Seite 46 von 57
Das Relationale Datenmodell
‰
Umbennen von Relationen und Attributen (“+ 1- Operation”)
ρ S ( R ) : Relation R wird in Relation S umbenannt
ρ B ← A ( R ) : Attribut A der Relation R wird umbenannt in B
–
–
Umbennung unterscheidet sich von den anderen Operatoren dadurch, daß keine neue
Instanz erzeugt wird, sondern nur das Schema der Relation verändert wird.
Operator ist notwendig, wenn eine Relation mehrfach in einer Anfrage vorkommt.
Seite 47 von 57
Das Relationale Datenmodell
Beispiele
R
R∪S
A
a
d
c
B
b
a
b
C
c
f
d
S
R×S
R –S
a
d
c
b
π A, C ( R )
b
a
b
g
c
f
d
a
A C
a c
d f
c d
D E F
b g a
d a f
a b c
c b d
σB = b ( R )
A
a
a
d
d
c
c
B
b
b
a
a
b
b
C
c
c
f
f
d
d
D
b
d
b
d
b
d
E
g
a
g
a
g
a
F
a
f
a
f
a
f
A B C
a b c
c b d
ρB ← D ( S ) B E F
b g a
d a f
Seite 48 von 57
Das Relationale Datenmodell
Beispiele für Anfragen
‰
Relationenschemata: Städte ( SName, SEinw, LName ) Länder ( LName, LEinw, Partei )
‰
Bestimme alle Großstädte und ihre Einwohnerzahlen
π SName, SEinw ( σ SEinw ≥ 500.000 ( Städte ) )
‰
In welchem Lande liegt die Stadt Passau?
π LName ( σ SName = Passau ( Städte ) )
‰
Bestimme die Namen aller Städte, deren Einwohnerzahl die eines Landes übersteigt.
π SName ( σ SEinw > LEinw ( Städte × Länder ) )
‰
Finde alle Städtenamen in CDU-regierten Ländern.
π SName ( σ Städte.LName = Länder.LName ( Städte × σ Partei = CDU ( Länder ) ) )
‰
Gib alle Städte, die es nur in Hessen gibt?
π SName ( σ LName = Hessen ( Städte ) ) – π SName ( σ LName ≠ Hessen ( Städte ) )
Seite 49 von 57
Das Relationale Datenmodell
3.3.2 Abgeleitete Operationen
Durchschnitt: R ∩ S
‰
‰
R ∩ S = R – (R – S)
Beispiel:
R
B
b
b
b
a
C
c
c
f
d
D
a
d
b
c
S
B
b
b
a
C
c
c
d
D
d
e
b
R∩S
B C D
b c d
Seite 50 von 57
Das Relationale Datenmodell
Quotient (Division): R ÷ S
‰
vereinfachende Annahme:
‰
r > s, S ≠ ∅ und Ar = Bs, Ar-1 = Bs-1,…, Ar-s+1 = B1
Relationenschema = {A1,…,Ar-s}
‰
Resultatsinstanz des Quotienten:
( R ÷ S ) := { ( a 1, …, a r – s ) ∀( b 1, …, b s ) ∈ I S : ( a 1, …, a r – s, b 1, …, b s ) ∈ I R }
‰
‰
Ableitung des Quotienten durch die Basisoperationen (siehe Übung)
Beispiel:
R
A
a
a
b
e
e
a
B
b
b
c
d
d
b
C
c
e
e
c
e
d
D
d
f
f
d
f
e
S
C D
c d
e f
R÷S
A B
a b
e d
Seite 51 von 57
Das Relationale Datenmodell
Theta-Join (Verbund):
‰
Auswahl bestimmter Tupel aus dem kartesischen Produkt R × S :
R iθj S := σ A θ B ( R × S )
i
mit
‰
‰
R iθj S
j
θ∈ { = ,≠,<,≤,>,≥ }
für θ = “=“ wird der Join auch als “Equijoin” bezeichnet
Beispiel:
R
A
1
4
7
B
2
5
8
C
3
6
9
S
D E
3 1
6 2
RB < D S
A B C D E
1 2 3 3 1
1 2 3 6 2
4 5 6 6 2
Seite 52 von 57
Das Relationale Datenmodell
Natürlicher Verbund (natural join): R
‰
‰
S
wichtigste Operation neben der Selektion
vereinfachende Annahme:
A 1 = B 1 , …, A k = B k und A j ≠ B i für alle j und i mit k < j ≤ r und k < i ≤ s
‰
σ

Dann ist: ( R S ) := π i
(
R
×
S
)

k + 1, i k + 2, …, i r + s  R.A 1 = S.B 1 ∧ … ∧ R.A k = S.B k
‰
Beispiel:
R
B
b
b
b
a
C
c
c
f
d
A
a
d
b
c
S
B
b
b
a
C
c
c
d
D
d
e
b
R
S
B
b
b
b
b
a
C
c
c
c
c
d
A
a
a
d
d
c
D
d
e
d
e
b
Seite 53 von 57
Das Relationale Datenmodell
Beispiele
Datenbankschema:
Abteilungsleiter:
P-M-Zuteilung:
pnr
mnr
Personal:
Fähigke
it
abtnr
pnr
pnr
PName
Vorname
abtnr
Lohn
B10
67
67
Meier
Helmut
B10
L4
67
84
3
A63
333
73
Müller
Margot
B10
L5
67
93
2
A64
51
114
Bayer
Martin
A63
L6
67
101
3
51
Daum
Birgit
A64
L7
73
84
5
69
Störmer
Willi
A64
L6
333
Haar
Hans
A63
L6
Abteilung:
114
93
5
114
101
3
abtnr
AName
701
Reiner
Willi
A64
L6
51
93
2
B10
Spielzeug
82
Just
Michael
A64
L6
69
101
2
A63
Computer
333
84
3
A64
Suppen
701
84
2
mnr
MName
701
101
2
84
Presse
82
101
2
93
Füllanlage
101
Säge
Maschinen:
Seite 54 von 57
Das Relationale Datenmodell
Anfragen
‰
Gib alle Namen von Personen, die an einer Maschine ausgebildet sind.
π PName ( Personal
‰
P-M-Zuteilung )
Gib alle Namen der Personen, die an keiner Maschine genügend gut ausgebildet sind.
π PName ( ( π pnr ( Personal ) – π pnr ( σ Fähigkeit < 5 ( P-M-Zuteilung ) ) )
‰
Gib die Namen der Personen aus Abteilung “Suppen”, die an der Maschine mit mnr = 93
ausgebildet sind.
π PName ( ( ( σ AName = Suppen ( Abteilung ) )
‰
Personal )
Personal )
σ mnr = 93 ( P-M-Zuteilung ) )
Gib die Namen der Personen, die an der gleichen Maschine ausgebildet sind wie die Person mit
pnr = 114.
π PName ( Personal
( ( π mnr ( σ pnr = 114 ( P-M-Zuteilung ) ) )
P-M-Zuteilung ) )
Seite 55 von 57
Das Relationale Datenmodell
Weitere Join-Operatoren
‰
bisherige Join-Operatoren werden auch als innere Joins bezeichnet
–
Datensätze ohne Join-Partner gehen verloren
äußere Join-Operatoren (engl.: outer joins):
‰
‰
‰
‰
Datensätze ohne Join-Partner werden (teilweise) berücksichtigt
linker äußerer Join R
S: Tupel von R bleiben erhalten
recher äußerer Join R
S: Tupel von S bleiben erhalten
vollständiger äußerer Join R
S: Tupel von S und R bleiben erhalten
Semi-Join
‰
Sei RS das Schema der Relation R. Dann ist der Semi-Join von R und S folgendermaßen
definiert:
R
–
S = Π RS ( R
S)
enthält alle Tupel der Relation R, die an dem Join mit der Relation S beteiligt sind.
Seite 56 von 57
Das Relationale Datenmodell
Beispiele
R
A B C
a1 b1 c1
a2 b2 c2
S
C D E
c1 d1 e1
c3 d2 e2
‰ linker äußerer Join
R
S
A B C D
E
e1
a1 b1 c1 d1
a2 b2 c2 NULL NULL
‰ Semi-Join
R
S
A B C
a1 b1 c1
Seite 57 von 57