Miniwelt E/R-Diagramm Relationales DB

2.7 Typischer Datenbankentwurf
Anforderungsanalyse
und -spezifikation
Konzeptioneller
Entwurf
Miniwelt
Alternative: UML-Diagramm
E/R-Diagramm
(E/R-Modell Kapitel 2)
Logischer
Entwurf
Relationales
DB-Schema
(RDM Kapitel 3)
Datendefinition
Durch Wahl eines
Produkts
SQL-Anweisungen gemäß
SQL-Dialekt des gewählten
DBMS (Übung: DB2)
Alternativen:
Bachman-Diagramm
(Netzwerk-DB-Modell)
Hierarchisches DB-Schema
(Hierarchisches DB-Modell)
Alternativen:
Wahl eines anderen RDBMS
(SQL-Norm, Kapitel 5)
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 97
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
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 98
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)
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 99
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"
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 100
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
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 101
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
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 102
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 ("row")
- Komponente des Tupels = Wert in Tabellenspalte ("column")
Relation R
D1
D2
...
Dn
d1
d2
...
dn
 Tupel
...
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 103
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)}
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 104
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
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 105
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
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 106
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
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 107
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
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 108
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 ...)
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 109
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
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 110
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
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 111
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!!
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 112
3.3.2 Rekursive 1:n-Beziehung
 E/R-Diagramm:
MITARBEITER
(0,1)
ist ...
(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
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 113
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
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 114
3.3.3 Nichtrekursive n:m-Beziehung
 E/R-Diagramm:
MENGE
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
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 115
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
TeileNr
374
812
111
...
Datenbanken und Informationssysteme
Bezeichnung
Glasauge
Linse
Okular
...
...
...
...
...
...
...
...
...
...
...
LIEFERT
Friedrich-Schiller-Universität Jena
TeileNr
374
374
111
812
812
...
LieferNr
3612
5888
2525
2525
3612
...
Menge
7
5
8
30
30
...
Seite 116
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
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 117
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
A
B
A
C
A
E
B
D
B
E
C
E
C
F
B
3
D
Datenbanken und Informationssysteme
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 118
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
Datenbanken und Informationssysteme
Adresse
Ort
Straße
Friedrich-Schiller-Universität Jena
Hausnummer
Seite 119
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
PNR
3814
4755
5812
...
Datenbanken und Informationssysteme
Name
Schmidt
Dagobert
Graf
...
...
...
...
...
...
TELEFON
Friedrich-Schiller-Universität Jena
PNR
3814
3814
4755
4755
4755
TelNr
31350
31051
31189
24753
24752
Seite 120
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!)
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 121
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
ANGEST PNR
Name
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
Datenbanken und Informationssysteme
Friedrich-Schiller-Universität Jena
Seite 122