3. Prinzipien und Nutzung von relationalen Datenbanksystemen

3. Prinzipien und Nutzung von relationalen
Datenbanksystemen
Inhalt:





Dateien vs. Datenbanken
Datenbanken: Tabellen, Attribute und Datentyp
Datenmodellierung und Normalformen einer Datenbank
Structured Query Language SQL
Zugriff aus einer Anwendung auf eine Datenbank
Peter Sobe
1
Vergleich: Dateien und Datenbanken (1)
Dateien:
 sind benannte Objekte, die eine Speicherung von
Anwendungsdaten auf einem linearen Adressraum
zulassen
 Persistente Speicherung, d.h. Daten bleiben nach
Beendigung der Anwendung und auch nach Abschalten
des Systems erhalten
 Dateisystem als Teil des Betriebssystems sorgt für Zugriff
mit Dateinamen, Verzeichnisse, Pufferung, Zugriffsschutz
 Datei-Daten können beliebig strukturiert sein:
 Binärdaten
 Textdaten (lesbarer Text)
 XML Datenformate
 es werden keine Regelmäßigkeiten der abgespeicherten
Datenstrukturen vorausgesetzt.
Peter Sobe
2
Vergleich: Dateien und Datenbanken (2)
Dateien (Fortsetzung):
 Wenn Daten eine Struktur bzw. Regelmäßigkeiten
aufweisen, so wird dieser Umstand nicht vom Dateisystem
gespeichert und nicht verwaltet.
 Hinweis auf Typ/Struktur der Datei über Dateityp, z.B.
bild.jpg ist eine Bilddatei im JPEG-Format … aber nicht
mehr
 Zugriff auf Daten in vollständiger Kontrolle des
Anwendungsprogramms
 Konkurrierende Zugriffe durch mehrere Anwendungen
sind nicht berücksichtigt und werden höchstens durch
Sperren der gesamten Datei vermieden.
Peter Sobe
3
Vergleich: Dateien und Datenbanken (3)
Datenbanken:
 Tabellen mit gleichartig aufgebauten Datensätzen
 Beziehungen zwischen Tabellen durch geeigneten
Datenbankentwurf
 Zugriff entweder durch Satzzugriff auf einzelne Sätze einer
Tabelle
 oder Zugriff durch eine Abfragesprache (SQL)
 Die Datenbank-Tabellen werden als Dateien gespeichert,
oder als Image auf einem Speichermedium, ein direkter
Zugriff über Datei-Operation ist aber nicht vorgesehen.
 Die regelmäßige Struktur der Daten wird in der Datenbank
gespeichert und zur Steuerung der Zugriffe ausgenutzt.
Peter Sobe
4
Vergleich: Dateien und Datenbanken (4)
Datenbanken (Fortsetzung):
 Ein Datenbank-Management-System dient zur Laufzeit dem
Zugriff auf die Datenbank. Das sind i.d.R. Serverprozesse,
die im Hintergrund ausgeführt werden.
 Persistente Speicherung, d.h. Daten bleiben nach
Beendigung der Anwendung und auch nach Abschalten des
Systems erhalten (wie Dateien)
 Konkurrierende Zugriffe erlaubt, Verhalten bei Konflikten
durch Transaktionen geregelt
Peter Sobe
5
Begriffe für Datenbanken
Datenbank
DB-Server
Metadaten
(Data
Dictionary),
daten-modellbezogen
Peter Sobe
organisatorische Da-ten
(Schlüssel,
Indexdateien, ...),
bezogen auf
internes
Schema
reale Daten
Installation
Nutzerverwaltung
bezogen auf
externes
Schema
Datenbanksoftware
organisatorische
Festlegung zum
Datenbankbetrieb
(DatenbankAdministrator)
Datenbasis
Hardware
Datensicherheit
Datenbankbetriebssystem
(DBMS)
Datenbanksprache
Nutzerprogramme
Entwicklungsumgebung
SQL
6
Tabellen, Attribute und Datentyp
Tabellenname
Attribut
Wetter
Ortsnr
Ortsname
Temperatur
Luftdruck
1
Dresden
22
1012
2
Leipzig
20
1010
3
Berlin
25
998
…
…
…
…
Tupel,
auch
Datensatz
oder Zeile
Wert mit einem
speziellen Datentyp, der
je Attribut definiert ist
Peter Sobe
7
Tabellen, Attribute und Datentyp
Eine Datenbank besteht oft aus mehreren einzelnen Tabellen, die
Referenzen aufeinander beinhalten können.
Orte
Ortsnr
Name
geogr.
Breite
Geogr.
Länge
Höhe
1
Dresden
51,05
13,74
113
2
Leipzig
51,34
12,37
118
3
Berlin
52,52
13,41
34
…
…
…
…
…
Beispiel:
Tabelle Orte und
Tabelle Wetter
Bezug über
gleiche Ortsnr.
Wetter
Peter Sobe
Datum
Zeit
Temperatur Ortsnr Luftdruck
1-1-2013
17:01:24
-5
1
1012
1-1-2013
17:04:00
-4.9
2
1010
1-1-2013
17:00:01
3.1
3
998
…
…
…
…
…
8
Tabellen als so genannte Relation
Die mathematische Modellierung von Datenbanken und
Zugriffsoperationen kann über das Relationale
Datenmodell (Codd 1970) erfolgen. Es basiert auf dem
mathematischen Relationenbegriff. Durch die
Einfachheit (Tabellensicht) und klare mathematische
Basis konnte sich das relationale Modell in der
Entwicklung der Datenbanktechnologie seit 1970
durchsetzen.
Entity-Relationship-Modell (Chen 1976) dient zur
Datenmodellierung, d.h. zur Entscheidung wie Dinge
und Beziehungen auf Tabellen abgebildet werden.
Peter Sobe
9
Definition der mathematischen Relation (1)
(mathematische) Definition der Relation:
Gegeben seien die nichtleeren Mengen W1, W2, ... Wn mit
W1xW2x...xWn = {(w1,w2,...,wn) | wi є Wi (i=1,2,...,n)}
Dann ist jede nichtleere Teilmenge R  W1xW2x...xWn eine n-stellige
Relation über W1, W2, ..., Wn . Die Wi müssen nicht alle paarweise
verschieden sein.
Ein Tupel (w1,w2,...,wn) =
(1; 1-1-2013; 17:01:24; -5; 1020).
könnte z.B. zu R gehören. Als Trennzeichen zwischen den Tupelwerten
wurde das Semikolon verwendet, da das Komma in Dezimalzahlen
vorkommen kann.
Für das obige Beispiel wären die Mengen Wi konkret festgelegt:
W1 ganze Zahl (int), W2 Datum, W3 Zeit, W4 und W5 Fließkommazahl.
Peter Sobe
10
Wertebereiche, Eigenschaften
und Relationenformat (1)
Wir deuten jetzt die Mengen Wi als Wertebereiche und die wi є Wi als
Werte und verbinden die Wi , genauer das Kreuzprodukt W1xW2x...xWn
, mit einer Interpretation I
E={E1,E2,...,En } = I(W1xW2x...xWn ) für i=1,2,...,n
Diese Ei werden als Eigenschaften (Attribute) von Entitäten gedeutet.
Für das obige Beispiel wären die Eigenschaften Ei wie folgt gedeutet:
E1 Datum, E2 Uhrzeit, E3 Ortsnr(Identifikation des Orts),
E4 Temperatur , E5 Luftdruck.
E nennt man Relationenformat.
Mit diesem Format würde das Tupel
(1-1-2013; 17:01:24; 1; -5; 1020)
die Entität einer Wettermessung an einem bestimmten Ort und zu einer
bestimmten Zeit eindeutig charakterisieren.
Peter Sobe
11
Wertebereiche, Eigenschaften
und Relationenformat (2)
In Datenbanken ist es möglich, dass ein Wert fehlt, d.h.
undefiniert ist. Dieser Fall wird durch den (Sonder-)Wert NULL
angezeigt.
Definition:
Eine Relation R= R(E1,E2,...,En) ist eine Menge von Tupeln,
wobei R der Name der Relation, E={E1,E2,...,En } das
Relationenformat und die Ei (i=1,2,...,n) die Eigenschaften sind,
deren Werte wi aus den zugeordneten Wertebereichen Wi
stammen müssen. Die Ei , die zu einer Relation gehören,
müssen paarweise verschieden sein. Ist r ein Tupel der Relation,
bezeichnet wi = r.Ei den Wert der i-ten Eigenschaft des Tupels r є
R (W1xW2x...xWn ). n heißt Grad der Relation.
Eine Relation ist eine Tabelle, die besondere Eigenschaften
aufweist …
Peter Sobe
12
Charakteristische Eigenschaften der Relation
Eine Relation hat folgende charakteristische Eigenschaften:
1. R ist eine Menge. Damit gibt keine paarweise identischen
Tupel. R hat deshalb einen Primärschlüssel, der jedes Tupel
eindeutig identifiziert.
2. R ist eine Menge. Deshalb ist die Reihenfolge der Tupel in R
ohne Belang.
3. Die Reihenfolge der Eigenschaften Ei in R ist ohne Belang.
4. Die Eigenschaftswerte wi = r.Ei mit r є R müssen einfach sein,
d.h. dürfen nicht mengenwertig oder nicht strukturiert sein.
wi = NULL ist aber möglich.
Peter Sobe
13
Primärschlüssel einer Relation
Primärschlüssel PS :
Ein Teil der Eigenschaften stellt sich oft als identifizierend für die Tupel
heraus.
PS  E
 PS ist die einzelne identifizierende Eigenschaft bzw. eine
Eigenschaftskombination, d.h. jeder Wert des PS verweist eindeutig
auf ein Tupel.
 PS sollten minimal sein, d.h nur die eine oder so wenige
Eigenschaften enthalten, damit (gerade so) eine eindeutige
Identifaktion der Tupel entsteht.
 Oft werden derartige Schlüssel schon durch organisatorische
Maßnahmen unabhängig von der Datenbank eingeführt, zum
Beispiel
Matrikelnummern, Kundennummern, Artikelnummern, Zugnummern.
Peter Sobe
14
Primärschlüssel einer Relation
Studenten
Matrikelnr. Vorname
Name
Gebdat
Wohnort
111111
Felix
Fritsche
1.1.1980
Freiberg
111113
Lise
Müller
26.3.1979
München
…
…
…
…
…
Die Matrikelnummer ist hier der Primärschlüssel. Es gibt jede
Matrikelnummer nur ein einziges mal im Diskursbereich.
Matrikelnummer und Geburtsdatum wäre auch ein guter Schlüssel,
ist aber nicht minimal, da Matrikelnummer auch ohne Geburtsdatum
eindeutig identifiziert.
Peter Sobe
15
Datenmodellierung und Normalformen einer
Datenbank(1)
In der Regel werden Diskursbereiche durch mehrere Relationen
(Tabellen) abgebildet.
Ziele:
 Vermeiden von Redundanz in Relationen
 Vermeiden von Inkonsistenzen
 Effizienzsteigerung, wenn nur wirklich benötigte Daten gelesen
werden, bzw. nur die Informationen in wenigen Tupeln
aktualisiert werden müssen.
Beispiel: eine Datenbank über Verkäufe, eine Tabelle ‘Gesamt‘
Gesamt
Verkaufs-Nr.
Produkt
Einzelpreis
Anzahl
Käufer
Käufer-Adresse
…
…
…
…
…
…
Peter Sobe
16
Datenmodellierung … (2)
Gesamt
Verk.-Nr.
Produkt
Einzelpreis
Anzahl
Käufer
Käufer-Adresse
1
Tomate
0.20
4
Heidi
33333 Wiesenhain,
Am Waldrand 1
2
Apfelsaft
1.40
2
Heidi
33333 Wiesenhain,
Am Waldrand 1
3
Katzenfutter
2.30
1
Kurt
33221 Bachhagen,
Hauptstr. 23
4
Apfelsaft
1.40
3
Kurt
33221 Bachhagen,
Hauptstr. 23
5
Gurke
0.55
1
Peter
33200 Feldstadt
Am Markt 5
6
Tomate
0.20
8
Peter
33200 Feldstadt
Am Markt 5
7
Bier
0.78
4
Kurt
22331 Bachhagen,
Hauptstr. 23
…
…
…
…
…
…
Peter Sobe
Redundanzen und Inkonsistenz
17
Datenmodellierung … (3)
Produkte
Verkaeufe
Produkt-ID
Name
Preis
T
Tomate
0.20
42
A
Apfelsaft
1.40
2
42
K
Katzenfutter
2.30
K
1
20
G
Gurke
0.55
4
A
3
20
B
Bier
0.78
5
G
1
17
…
6
T
8
17
Kunden
7
B
4
20
Käufer-ID
Name
Adresse
…
…
…
…
17
Peter
33200 Feldstadt
Am Markt 5
20
Kurt
33221 Bachhagen,
Hauptstr. 23
42
Heidi
33333 Wiesenhain,
Am Waldrand 1
…
…
…
Verk.
Nr.
ProduktID
Anzahl
1
T
4
2
A
3
Käufer-ID
 Redundanz ist jetzt
beseitigt
 Inkonsistenzen können jetzt
nicht mehr auftreten
Peter Sobe
…
18
Normalformen (1)
Ziele:
 Vermeiden von Redundanz in Relationen – bei
ungünstiger Struktur der Tabellen würde gleiche
Information u.U. mehrfach gespeichert. Eine
Strukturierung entsprechend der dritten Normalform
verhindert das.
 Vermeiden von Anomalien – bei ungünstigem Entwurf
können Informationen u.U. ungewollt verloren gehen.
Peter Sobe
19
Normalformen (2)
Verk.-Nr.
Produkt
Einzelpreis
Anzahl
Käufer
Käufer-Adresse
…
…
…
…
…
…
Anomalien:
 Einfügeanomalie – Neue Sachverhalte können nicht
eingefügt werden. Im Beispiel oben kann keine neues
Produkt mit einem Preis eingefügt werden, ohne einen
Kaufvorgang zu beschreiben.
 Löschanomalie – Das Löschen von Kaufvorgängen
bedingt u.U. den Verlust von Produktinformationen und
Käuferdaten.
 Änderungsanomalie – Das Abändern eines Sachverhalts
verlang oft das Ändern vieler Datensätze (bei
Redundanz von z.B. Käufer-Adressen)
Peter Sobe
20
Zweck der Normalformen
Ein Datenbankentwurf nach dem Entity-Relationship-Modell bringt
die Tabellen automatisch in eine redundanzfreie und anomaliefreie
Struktur.
ERModell
regelbasierter
Entwurf
(hier nicht gezeigt)
Existierender
Datenbestand
Umstrukturierter
Datenbestand
Redundanzfreie
und
anomaliefreie
Datenbank
Herstellung der
Normalformen
Für existierende Datenbanken können Probleme behoben
werden, indem das Vorhandensein der Normalformen geprüft wird
und diese durch Umstrukturierung der Tabellenstruktur hergestellt
werden.
Peter Sobe
21
Erste Normalform (1)
Eine Tabelle ist in der ersten Normalform (1NF), falls die
Wertebereiche der Merkmale atomar sind.
1NF verlangt, dass jedes Merkmal Werte aus einem
unstrukturierten Wertebereich aufnimmt. Damit dürfen keine
Mengen, Aufzählungstypen oder Wiederholungsgruppen in
den einzelnen Merkmalen vorkommen.
Beispiel für eine nicht erfüllte 1NF:
Matrnr
Name
Vorname
Studiengänge
11
Schmidt
Robert
{Mathemetik, Physik}
47
Schulze
Klaus
{Sport, Erziehungswissenschaften}
78
Peters
Karoline
{Informatik}
Peter Sobe
22
Erste Normalform (2)
Beispiel mit atomarem Wertebereich für Studiengang
Matrnr
Name
Vorname
Studiengang
11
Schmidt
Robert
Mathematik
11
Schmidt
Robert
Physik
47
Schulze
Klaus
Sport
47
Schulze
Klaus
Erziehungswissenschaften
78
Peters
Karoline
Informatik
Peter Sobe
23
Zweite Normalform (1)
Eine Tabelle ist in der zweiten Normalform (2NF), wenn sie in 1NF
ist und wenn jedes Nichtschlüsselmerkmal von jedem Schlüssel
voll funktional abhängig ist.
Voll funktional abhängig bezieht sich auf zusammengesetzte
Schlüssel (S1,S2) von denen alle Nichtschlüsselmerkmale
abhängig sind: (S1,S2) -> N
Sie dürfen dabei aber nicht funktional abhängig von einem
Teilschlüssel sein, z.B. S1->N
Im Beispiel sind Name und Vorname Nichtschlüsselmerkmale, der
Schlüssel setzt sich aus Matrnr und Studiengang zusammen.
(Matrnr, Studiengang) -> Name
aber auch Matrnr -> Name … damit keine 2NF
Peter Sobe
24
Zweite Normalform (2)
Herstellung der zweiten Normalform (2NF): Tabellenteilung
Alle Merkmale, die von einem Teilschlüssel abhängig sind werden
in eine eigene Tabelle ausgegliedert.
Matrnr
Name
Vorname
Matrnr
Studiengang
11
Schmidt
Robert
11
Mathematik
47
Schulze
Klaus
11
Physik
78
Peters
Karoline
47
Sport
47
Erziehungswissenschaften
78
Informatik
Peter Sobe
25
Dritte Normalform (1)
Eine Tabelle ist in der dritten Normalform (3NF), wenn sie in 2NF ist
und kein Nichtschlüsselmerkmal von irgendeinem Schlüssel
transitiv abhängig ist
Ein Beispiel für nicht erfüllte 3NF:
Matrnr
WohnPLZ
Wohnort
11
01217
Dresden
47
01219
Dresden
78
14197
Berlin
89
14197
Berlin
95
01217
Dresden
Peter Sobe
Matr
nr
Wohn
PLZ
Wohn
ort
Wohnort tritt
redundant auf.
26
Dritte Normalform (2)
Überführung in dritte Normalform (3NF), indem transitiv
abhängiges Merkmal in eine neue Tabelle ausgegliedert wird. Das
Merkmal von dem die letzte Stufe der Abhängigkeit ausgegangen
ist, wird als Fremdschlüssel in die neue Tabelle aufgenommen.
Matrnr
WohnPLZ
PLZ
Ort
11
01217
01217
Dresden
47
01219
14197
Berlin
78
14197
01219
Dresden
89
14197
95
01217
Peter Sobe
27