Relation - Fakultät für Mathematik und Informatik - Friedrich
Werbung
3. Relationales Daten(bank)modell
Vorbemerkung
- Historie
- Einordnung
Begriffe und Eigenschaften
- Relationen und Tabellen
- Relationsschema
- Schlüssel, Integrität
Relationale Abbildung des E/R-Modells
- Beziehungstypen
- Nichtatomare Attribute
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 97
3.1 Vorbemerkung/Historie
Datenbanksysteme nach dem hierarchischen Modell und Netzwerk-Modell
"weit entfernt" vom gelegentlichen Endbenutzer ("casual user"):
- Datenbank nur aus Anwendungsprogramm heraus ansprechbar, dazu
sind Programmierkenntnisse erforderlich
- Datenbanksprache (DL/1, CODASYL) schwer zu erlernen
• GET UNIQUE, GET NEXT, GET NEXT WITHIN PARENT
• FIND OWNER, FIND FIRST, FIND NEXT
- Datenbanksystem nur für kleinen Kreis von Spezialisten zugänglich,
dadurch "Entwicklungsstau"
- Komplizierter Umgang mit Datenbanksystem führt zu Fehlern
• Datenbank wird inkonsistent, DBVS kann dies nicht feststellen
• "Zustand der modellierten Miniwelt <> Zustand der Datenbank"
• Fatal vor allem bei kritischen Anwendungen (z.B. Prozessteuerung
in der Industrie, Finanzbereich)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 98
3.1 Vorbemerkung/Historie
Entwicklungsziele für relationales Datenmodell / Datenbanksysteme im IBM
Forschungslabor in San Jose Ende 60er Jahre (E.F. Codd u.a.):
- Einfaches Datenmodell mit mathematisch fundierter Grundlage
- Einfache Datenbanksprache (auch für Ad-hoc-Anfragen) mit
mathematisch fundierter Grundlage
• Verhalten von Datenbanksprachanweisungen (Semantik) ist
wohldefiniert auf mathematischer Grundlage
• Benutzer beschreibt nur noch, was er haben möchte (lesen, einfügen,
ändern, löschen), das wie überlässt er dem DBS
• Deskriptive Anfragen statt Navigation!
- Überwachung von Konsistenz/Integrität möglichst weitgehend durch das
DBVS und nicht durch den Benutzer
• Semantik der Daten muss dem DBVS offengelegt werden, so dass
automatische Überprüfungen möglich sind
• DBVS berücksichtigt bei Operationen die Dateninhalte und verlässt
sich nicht darauf, dass der Benutzer "die Zeiger richtig setzt"
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 99
3.1 Vorbemerkung/Historie
Ergebnis der Entwicklung/Forschung:
- Datenmodell und Datenbanksprache leichter zu erlernen Æ näher am
(End-)Benutzer Æ breiterer Benutzerkreis Æ weniger Entwicklungsstau
- Datenbankzugriffe auch ad-hoc möglich, nicht zwingend per Anwendung
- Inkonsistente Datenbanken unwahrscheinlicher
- Ineffizienter Datenbankzugriff unwahrscheinlicher
• DBVS ist für das wie des Datenzugriffs verantwortlich
• Optimale Ausführungsstrategie wählbar (Zugriffsplan, Optimizer)
Entwicklungsweg relationaler DBMS
- Erste Prototypen ab Mitte der 1970er
• System R (IBM, später DB2), Ingres (Univ. of California)
- Erste Produkte ab Ende der 1970er
• SQL/DS, DB2, Ingres, Oracle
- Stärkere Verbreitung in der Praxis ab Mitte der 1980er und 1990er
• Informix, Sybase, MS SQL Server, MySQL
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 100
3.2 Begriffe und Eigenschaften
Ziele bei der Definition des Modells:
- Wenige, einfache, mathematisch fundierte Konzepte und Begriffe
- Beschreibung ausschließlich logischer Aspekte (konzeptuelles
Schema)
- Physische Aspekte (internes Schema) nicht Gegenstand des Modells
Modellierung in Form von Relationen, Grundlage zur
- Darstellung der Daten
- Datenabfrage und Manipulation
Begriffsklärung nachfolgend:
- Relation
- Relationsschema
- Schlüssel
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 101
3.2.1 Relation
Definition:
- Gegeben seien Wertebereiche/Domänen D1, D2, ..., Dn
- Eine Relation (Menge) R ist eine Teilmenge des kartesischen Produkts
der Domänen D1, D2, ..., Dn
• R ⊆ D1 × D2 × ... × Dn (n≥1)
• n = Stelligkeit (Grad) der Relation
• Element r = (d1, d2, ..., dn) mit diєDi wird als Tupel von R bezeichnet,
di als die i-te Komponente des Tupels
Darstellungsform:
- Relationen können als Tabellen dargestellt / aufgefasst werden
- Tupel = Tabellenzeile ("rows")
- Komponente des Tupels = Wert in Tabellenspalte ("columns")
Relation R
D1
D2
...
Dn
d1
d2
...
dn
Å Tupel
...
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 102
3.2.1 Relation
Beispiel:
- Domänen:
• D1 = {rot, blau, grün}
• D2 = {0,1}
- Kartesisches Produkt:
• D1 × D2 = {(rot,0), (rot,1), (blau,0), (blau,1), (grün,0), (grün,1)}
- Mögliche 2-stellige Relationen:
• R1 = {(rot,0), (grün,1)}
rot
0
grün
1
rot
0
• R2 = { }
• R3 = {(rot,0)}
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 103
3.2.2 Relationsschema
Bisher wurden Relationen (Mengen von Tupeln) betrachtet
- Aus Datenbanksicht ist aber auch das zugehörige Schema von
Interesse, das einen Relationstyp beschreibt
- Abstraktion von konkreter Relation zum Relationstyp
Definition: Relationsschema besteht aus
- Schemaname
- Menge von Domänen(namen) D1, D2, ..., Dn
- Menge von Attributnamen A1, A2, ..., An (Tabellenspalten mit aiєDi)
- Zusätzlichen Integritätsbedingungen
Beispiel:
- Schemaname: FarbTabelle, kurz: FarbTabelle(Farbe, Wert)
- Domänen: D1 = {rot, blau, grün}, D2 = {0,1}
- Attributnamen: Farbe (D1), Wert (D2)
- Integritätsbedingung: Farbe rot darf nicht mit Wert 1 ein Tupel bilden
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 104
3.2.2 Relationsschema
Beispiel:
- Relationsschema Angest (Name, Beruf, Wohnort, GebJahr)
- Domänen/Integritätsbedingungen hier nicht aufgeführt
- Darstellung von Schema + Daten
• Schemaname: Angest
• Attributnamen: Name, Beruf, Wohnort, GebJahr
Attributname
Schemaname
Angest
Tupel
(Darstellung als
Tabellenzeilen)
Name
Müller
Meier
Schulze
Beruf
Schreiner
Schmied
Bergmann
Wohnort
GebJahr
Schema
Jena
Jena
Seiffen
1960
1958
1935
Daten,
konkrete
Relation
Attributwert
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 105
3.2.2 Relationsschema
Datenbankschema = Menge von Relationsschemata (mit verschiedenen
Schemanamen) + Integritätsbedingungen (relationsschemaübergreifend)
Keine strikte Unterscheidung zwischen Relationsschema und Relation; wenn
von Relation die Rede ist, wird meist darunter auch das Schema subsumiert
(d.h. Daten + Schema oder auch nur Schema)
Definition: Eine Relation ist in 1. Normalform, wenn jedes Attribut aufgrund
der Schemadefinition nur atomare Wertebereich hat.
- Mengenwertige, strukturierte und geschachtelte Attribute im E/RVerständnis sind verboten, Relation ist frei von Wiederholungsgruppen
• Entscheidung abhängig vom Anwendungskontext
• Bsp: Attribut "Adresse" (PLZ, Ort, Strasse, Nr) in 4 Attribute splitten
- Das relationale Modell, wie von Codd definiert und in DBVS-Produkten
realisiert, verlangt "im Prinzip" 1. Normalform!!!
- Typische atomare Attribute: Integer, Real, Decimal, Char, String, Boolean
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 106
3.2.3 Schlüssel
Relationen sind Mengen, es dürfen also in einer Relation keine identischen
Tupel auftauchen
Müller
Müller
Schreiner
Schreiner
Jena
Jena
1960
1960
Relationenmodell ("reine Lehre") verlangt für jede Relation einen
- Schlüssel als identifizierende Attributkombination mit
- Minimalitätseigenschaft, d.h. bei Weglassen eines Attributs aus der
Kombination geht die Schlüsseleigenschaft verloren
- Existieren für eine Relation mehrere Schlüssel(kandidaten), muss einer
ausgewählt werden als Primärschlüssel
Beispiel: Relation Angest (Name, Beruf, Wohnort, GebJahr)
- Was ist hier der Schlüssel?
- Erweiterung der Relation erforderlich/sinnvoll:
• Angest (PNR, Name, Beruf, Wohnort, GebJahr)
• Schlüssel = PNR
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 107
3.2.3 Schlüssel
Definition: besitzt eine Attributkombination einer Relation R1 in einer Relation
R2 (Primär-)Schlüsseleigenschaft, kann sie in R1 als Fremdschlüssel mit
Bezug auf den (Primär-)Schlüssel in R2 bezeichnet werden
- Fremdschlüssel ist üblicherweise kein Schlüssel in R1!
- Fremdschlüssel als Definitionsmittel für referentielle Integrität, wobei
Werte als Verweise dienen (keine "Pointer")
- Namensidentität nicht zwingend, aber Kompatibilität der Wertebereiche
- Relationen dürfen beliebig viele Fremdschlüssel haben
Beispiel:
- R1 = Angest (PNR, Name, Beruf, Wohnort, GebJahr, ANR)
- R2 = Abteilung (ANR, AOrt, Mgr, Budget)
- "ANR" kann als Fremdschlüssel in der Relation "Angest" bezüglich des
Primärschlüssels "ANR" in "Abteilung" vereinbart werden (per DDL)
- Bedeutung: Für "ANR" in "Angest" dürfen nur Werte auftreten, die auch
als ANR-Wert in "Abteilung" vorkommen
• Reine Lehre: Waisenkinder sind verboten, Einfügereihenfolge!
• SQL / Produkte: Möglichkeiten der Aufweichung (on update/delete ...)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 108
3.3 Relationale Abbildung des E/R-Modells
Beziehungstypen
- Nichtrekursive 1:n-Beziehung
- Rekursive 1:n-Beziehung
- Nichtrekursive n:m-Beziehung
- Rekursive n:m-Beziehung
Umgang mit nichtatomaren Attributen
- Strukturierte Attribute
- Mengenwertige Attribute
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 109
3.3.1 Nichtrekursive 1:n-Beziehung
E/R-Diagramm:
ABTEILUNG
(0,*)
HAT
(1,1)
MITARBEITER
Relational:
- ABTEILUNG (ANR, AOrt, ...)
- MITARBEITER (PNR, Name, ..., ANR)
Beispieltabellen:
- Reihenfolge der Spalten und Zeilen ist unerheblich (Menge!)
Abteilung
ANR
AOrt
...
3815
3952
4717
...
Jena
Weimar
Erfurt
...
...
...
...
...
Mitarbeiter
PNR
2837
1113
1548
...
Name
Meier
Meyer
Maier
...
...
...
...
...
...
ANR
3815
3952
4717
...
Überführung eines Entity-Typ in Relation(sschema)
- Unmittelbare Übernahme der (atomaren) Attribute
- Vorsicht bei Schlüsselüberführung wegen Minimalitätsforderung
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 110
3.3.1 Nichtrekursive 1:n-Beziehung
ANR in MITARBEITER ist Fremdschlüssel bezüglich Primärschlüssel in
ABTEILUNG
- Darstellung des Beziehungstyp HAT aus dem E/R-Diagramm
- Überführung von HAT in eigenständige Relation nicht erforderlich
Beliebige Kardinalitäten (z.B. (3,7)) nicht direkt im rel. Modell darstellbar
Falls Mitarbeiter erlaubt sein sollen, die keiner Abteilung angehören, ist für
ANR in MITARBEITER definierbar, dass ein Nullwert (NULL) gestattet ist
- Nullwert ("undefiniert") hat spezielle Semantik, nicht normaler Wert 0
- Bsp: Attribute ANR und Name in MITARBEITER dürfen Nullwerte haben
Mitarbeiter
PNR
2837
1113
1548
2964
Name
Meier
Meyer
Maier
NULL
...
...
...
...
...
ANR
3952
NULL
4717
3815
Nullwerte sind für Primärschlüssel verboten, auch für Teil-Attribute!!
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 111
3.3.2 Rekursive 1:n-Beziehung
E/R-Diagramm:
(0,1)
ist ...
MITARBEITER
(0,1)
hat als ...
VORGESETZTER
Relational:
- MITARBEITER (PNR, Name, ..., MgrPNR)
MgrPNR ist Fremdschlüssel in MITARBEITER bezüglich Primärschlüssel der
gleichen Relation (ist erlaubt!!)
Problem: wie stellen wir als Tupel den "obersten Boss" dar? Alternativen:
- MgrPNR und PNR besitzen gleichen Wert (Trick nicht empfehlenswert!)
- MgrPNR bekommt in diesem Fall einen undefinierten Wert (Nullwert)
• Dies muss im relationalen Modell an MITARBEITER definiert werden
• Weitere Integritätsbedingung dann notwendig, die sicherstellt, dass am
MgrPNR-Attribut nur einmal der Wert NULL vorkommt (Big Boss)
• Integritätsbedingung wegen Zyklenfreiheit
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 112
3.3.2 Rekursive 1:n-Beziehung
Beispieltabelle:
Mitarbeiter
PNR
3814
4755
5812
6719
...
Name
Schmidt
Dagobert
Graf
Schneider
...
...
...
...
...
...
...
MgrPNR
NULL
3814
3814
4755
...
Ausprägungsbaum:
- Tabelle (Relation) Mitarbeiter enthält für jeden Knoten (Mitarbeiter) im
Ausprägungsbaum ein Tupel
- Kanten im Ausprägungsbaum werden in der Tabelle durch die
Primärschlüssel-Fremdschlüssel-Beziehungen dargestellt
PNR 3814
PNR 4755
PNR 5812
...
PNR 6719
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 113
3.3.3 Nichtrekursive n:m-Beziehung
MENGE
E/R-Diagramm:
LIEFERANT
(0,*)
LIEFERT
(0,*)
TEIL
Relational:
- LIEFERANT (LieferNr, Name, Ort, ...)
- TEIL (TeileNr, Bezeichnung, ...)
- LIEFERT (TeileNr, LieferNr, Menge)
Beziehungstyp wird in eigenständige Relation überführt (LIEFERT)
- Primärschlüssel von LIEFERT ist die Attributkombination der Schlüssel
der Relationen LIEFERANT und TEIL
- Fremdschlüssel in LIEFERT: TeileNr (TEIL), LieferNr (LIEFERANT)
- Warum Beziehungstyp als eigenständige Relation statt TeileNr aus TEIL
als Fremdschlüssel in LIEFERANT aufnehmen?
• Redundanzvermeidung
• Abbildung von Lieferanten ohne Teile bzw. Teile ohne Lieferanten
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 114
3.3.3 Nichtrekursive n:m-Beziehung
Konsequenz der 2 Primärschlüssel-Fremdschlüssel-Beziehungen:
- In der Spalte TeileNr/LieferNr von LIEFERT dürfen nur Teilenummern
bzw. Lieferantennummern vorkommen, die auch in TEIL/LIEFERANT
vorhanden sind
- Nullwerteproblematik hier durch Definition ausgeschlossen (warum?)
LIEFERANT LieferNr
Name
Ort
3612
Jenoptik
Jena
2525
Zeiss
Jena
5888
Rodenstock Stuttgart
...
...
...
TEIL
Datenbanksysteme
TeileNr
374
812
111
...
Bezeichnung
Glasauge
Linse
Okular
...
...
...
...
...
...
...
...
...
...
...
LIEFERT TeileNr
374
374
111
812
812
...
Friedrich-Schiller-Universität Jena
LieferNr
3612
5888
2525
2525
3612
...
Menge
7
5
8
30
30
...
Seite 115
3.3.4 Rekursive n:m-Beziehung
E/R-Diagramm:
- Modell einer Stückliste
- Entitytyp TEIL als Synonym für
• Einzelteil
• Baugruppe
TEIL
(0,*)
(0,*)
enthält / enthalten in
MENGE
Relational:
- TEIL (TeileNr, Bezeichnung, ...)
- STRUKTUR (OberteilNr, UnterteilNr, Menge)
Beziehungstyp wird in eigenständige Relation überführt (STRUKTUR)
- Primärschlüssel von STRUKTUR setzt sich aus Attributen OberteilNr
und UnterteilNr zusammen
- OberteilNr und UnterteilNr sind jeweils einzeln Fremdschlüssel in
STRUKTUR mit Bezug auf Primärschlüssel von TEIL
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 116
3.3.4 Rekursive n:m-Beziehung
Konsequenz der 2 Primärschlüssel-Fremdschlüssel-Beziehungen:
- In der Spalte OberteilNr/UnterteilNr von STRUKTUR dürfen nur
Teilenummern vorkommen, die auch in TEIL vorhanden sind
- Nullwerteproblematik hier ebenfalls durch Definition ausgeschlossen
- Zusätzliche Integritätsbedingung wegen Zyklenfreiheit!
TEIL TeileNr
A
B
C
D
E
F
Bezeichnung
Getriebe
Gehäuse
Welle
Schraube
Schraube
Kugellager
...
...
...
...
...
...
...
Darstellung als Gozinto-Graph
- "Zepartzat Gozinto"
- DAG = directed acyclic graph
STRUKTUR Ober- UnterteilNr teilNr
B
A
C
A
E
A
D
B
E
B
E
C
F
C
B
3
D
Datenbanksysteme
Friedrich-Schiller-Universität Jena
1
1
4
7
3
1
5
2
Mengen
A
1
Menge
4
C
7
5
E
2
F
Seite 117
3.3.5 Abbildung nichtatomarer Attribute
Nichtatomare Attribute sind nicht direkt im relationalen Modell abbildbar
- Grund: Einhaltung 1. Normalform
- Vorstellung von Lösungsmöglichkeiten am Beispiel ANGEST
• Mengenwertige Attribute (Telefon#)
• Strukturierte Attribute (Adresse)
E/R-Diagramm-Beispiel:
Telefon#
ANGEST
PNR
Name
PLZ
Datenbanksysteme
Adresse
Ort
Straße
Friedrich-Schiller-Universität Jena
Hausnummer
Seite 118
3.3.5 Abbildung nichtatomarer Attribute – Mengenwertige Attribute
Modellierungsvorschlag 1: Eine Tabelle
ANGEST
PNR
3814
3814
4755
4755
4755
...
TelNr
31350
31051
31189
24753
24752
...
Name
Schmidt
Schmidt
Dagobert
Dagobert
Dagobert
...
...
...
...
...
...
...
...
nein!
- Mitarbeiter ohne Telefon können in ANGEST nicht aufgenommen werden!
- Redundanz in Tabellenspalte Name, dadurch Änderungsaufwand und
Integritätsgefährung! Æ Lösung: 2. Normalform
Modellierungsvorschlag 2: Zwei Tabellen
ANGEST
Datenbanksysteme
PNR
3814
4755
5812
...
Name
Schmidt
Dagobert
Graf
...
...
...
...
...
...
TELEFON
Friedrich-Schiller-Universität Jena
PNR
3814
3814
4755
4755
4755
TelNr
31350
31051
31189
24753
24752
Seite 119
3.3.5 Abbildung nichtatomarer Attribute – Mengenwertige Attribute
- PNR in TELEFON ist Fremdschlüssel mit Bezug auf den Primärschlüssel
von ANGEST
- Nullwertproblematik hier per Definition ausgeschlossen
- Zusätzliche Integritätsbedingung denkbar:
• Kein "Telefonnummern-Sharing", d.h. zwei Angestellte besitzen nie
die gleiche Telefonnummer
• TelNr in TELEFON zum Schlüsselkandidaten machen garantiert
Werteeindeutigkeit!
• Primärschlüssel wäre nach der reinen Lehre dann TelNr, aber
Definition über (PNR, TelNr) mit UNIQUE-Bedingung auf TelNr lassen
SQL/Produkte auch zu
- Nachteil der Verwendung zweier Tabellen (ANGEST, TELEFON):
• Eine Datenbankanfrage, die z.B. Name und TelNr sehen möchte,
muss auf beide Tabellen zugreifen
• Verknüpfungs-Operation (JOIN) ist bei falscher Verwendung sehr
teuer (Optimizer-Aufgabe!)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 120
3.3.5 Abbildung nichtatomarer Attribute – Strukturierte Attribute
Modellierungsvorschlag 1: Eine Tabelle
ANGEST PNR
3814
4755
...
Name
Schmidt
Dagobert
...
PLZ
73428
13121
...
Ort
Jena
Berlin
...
Straße
Freiheit
Moabit
...
Hausnr.
47
17
...
...
...
...
...
- Herausnehmen der Ebene "Adresse" und Hochziehen der zugehörigen
atomaren Attribute
- Nachteil: Verlust von Semantik
• Zusammengehörigkeit von PLZ-Ort-Straße-Hausnummer verloren
• Adresse in DB-Anfrage nicht mehr einfach als ganzes ansprechbar
Modellierungsvorschlag 2: Zwei Tabellen
Name
ANGEST PNR
3814 Schmidt
4755 Dagobert
...
...
...
...
...
...
ADRESSEN
PNR
3814
4755
...
PLZ
73428
13121
...
Ort
Jena
Berlin
...
...
...
...
...
- PNR in ADRESSEN ist Primär-/Fremdschlüssel auf PNR in ANGEST
- Adresstabelle liefert gesamte Adresse für eine gegebene PNR
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 121
