Folien

Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Datenbanken
Unit 4: Das Relationale Modell & Datenintegrität
Ronald Ortner
14. III. 2017
Ronald Ortner
Organisatorisches
SQL
Outline
1
Organisatorisches
2
SQL
3
Relationale Algebra – Notation
4
Datenintegrität
Ronald Ortner
Relationale Algebra – Notation
Datenintegrität
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Organisatorisches
Erster Zwischentest:
nächste Woche am 22. März zur Übungszeit
Stoff: alles bisher (ohne Joins)
Zwei Beispiele in 30 – 40 Minuten
Weitere Übungsbeispiele: auf der Lehrveranstaltungsseite
Neue Übungsblätter zum Thema Joins:
zu machen bis 29. März
Wissensüberprüfung über Joins am 29. März
Ronald Ortner
Organisatorisches
SQL-Lesson 4
Heute:
Joins
NULL-Werte
Ronald Ortner
SQL
Relationale Algebra – Notation
Datenintegrität
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Relationale Algebra
Relationale Algebra:
formale Sprache für Anfragen an relationale Datenbank
theoretische Grundlage für SQL
SQL kann typischerweise mehr als in der relationalen Algebra
formulierbar
Wir verwenden zur Vereinfachung ein wenig Notation aus der
relationalen Algebra.
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Relationale Algebra – Selektion
Für ein sogenanntes Selektionsprädikat F wählt eine Selektion alle
Tupel (∼
= Zeilen) t einer Relation (∼
= Tabelle) R, die F erfüllen:
σF (R) := {t ∈ R | F (t)}
Das Prädikat (∼
= Eigenschaft) F kann dabei aus folgenden
Komponenten bestehen:
Konstanten
Namen von Attributen der Relation R
Vergleichsoperatoren: <, >, ≤, ≥, =, 6=
Logische Verknüpfungen: ∧ (und), ∨ (oder), ¬ (nicht)
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Relationale Algebra – Selektion
Beispiel:
Zeige alle Länder Europas, die größer als 100000km2 sind.
Konstanten: ‘Europe’, 100000
Namen von Attributen der Relation R: country, region, area
Vergleichsoperatoren: =, >
Logische Verknüpfungen: AND
D.h., Selektion entspricht dem WHERE in SQL-Abfrage.
(Welche Zeilen?)
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Relationale Algebra – Projektion
Selektion wählt Zeilen,
Projektion wählt Spalten:
Für eine Relation R und Attribute A1 , A2 , . . . , An wählt die Projektion
ΠA1 ,A2 ,...,An (R) := (t.A1 , t.A2 , . . . , t.An ) t ∈ R
für jedes Tupel der Relation die entsprechenden Attributwerte aus.
Typischerweise kommt dabei jedes Tupel der Projektion nur einmal vor.
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Relationale Algebra – Projektion
Für eine Relation R und Attribute A1 , A2 , . . . , An wählt die Projektion
ΠA1 ,A2 ,...,An (R) := (t.A1 , t.A2 , . . . , t.An ) t ∈ R
für jedes Tupel der Relation die entsprechenden Attributwerte aus.
Typischerweise kommt dabei jedes Tupel der Projektion nur einmal vor.
Beispiele:
Zeige alle Regionen aus cia.
Zeige die in sowe vorkommenden Kombinationen von Monaten und
Jahren.
D.h., Projektion entspricht dem SELECT einer SQL-Abfrage.
(Welche Spalten?)
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Datenintegrität: Konsistenz der gespeicherten Daten.
Bisher implizite Anforderungen an Datenintegrität:
Ronald Ortner
Datenintegrität
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Datenintegrität
Datenintegrität: Konsistenz der gespeicherten Daten.
Bisher implizite Anforderungen an Datenintegrität:
Primärschlüssel:
keine verschiedenen Zeilen mit demselben Primärschlüssel
z.B. keine zwei Studenten mit derselben Matrikelnummer
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Datenintegrität
Datenintegrität: Konsistenz der gespeicherten Daten.
Bisher implizite Anforderungen an Datenintegrität:
Primärschlüssel:
keine verschiedenen Zeilen mit demselben Primärschlüssel
z.B. keine zwei Studenten mit derselben Matrikelnummer
1 : N Relationen:
Für jeden Wert des Primärschlüssels lässt sich nur ein
entsprechender Attributwert speichern.
z.B. hat jedes Land in cia nur eine zugeordnete Region region
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Datenintegrität
Datenintegrität: Konsistenz der gespeicherten Daten.
Bisher implizite Anforderungen an Datenintegrität:
Primärschlüssel:
keine verschiedenen Zeilen mit demselben Primärschlüssel
z.B. keine zwei Studenten mit derselben Matrikelnummer
1 : N Relationen:
Für jeden Wert des Primärschlüssels lässt sich nur ein
entsprechender Attributwert speichern.
z.B. hat jedes Land in cia nur eine zugeordnete Region region
Domänen für Attribute:
bestimmt Datentyp für Attribute
z.B. population in cia muss Integer sein
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Datenintegrität
Zwei Arten von Datenintegrität:
Statische Integrität:
Konsistenz des Datensatzes zu einem bestimmten Zeitpunkt
Dynamische Integrität:
garantiert, dass eine statisch integre Datenbank auch nach
Änderungen statisch integer bleibt
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Referentielle Integrität
Idee:
Ein Wert für einen Fremdschlüssel braucht entsprechenden Wert für
Primärschlüssel.
Zur Erinnerung:
Wird ein Primärschlüssel einer Tabelle als Attribut in anderer Tabelle
verwendet, nennt man letzteren einen Fremdschlüssel.
Beispiel:
In den Tabellen Orders und Order_Options der ldb-Datenbank ist
P_No ein Fremdschlüssel: Dieses Attribut bezieht sich auf den
Primärschlüssel P_No der Tabelle Parts.
→ Für jeden Wert von P_No in Orders und Order_Options
muss es entsprechende P_No in Parts geben.
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Referentielle Integrität
Idee:
Ein Wert für einen Fremdschlüssel braucht entsprechenden Wert für
Primärschlüssel.
Zur Erinnerung:
Wird ein Primärschlüssel einer Tabelle als Attribut in anderer Tabelle
verwendet, nennt man letzteren einen Fremdschlüssel.
Beispiel:
director in der movie Datenbank ist ein Fremdschlüssel:
director bezieht sich auf den Primärschlüssel id in der Tabelle
actor.
→ Für jeden Wert von director muss es entsprechende id in
actor geben.
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Referentielle Integrität: Formale Definition
Definition (Fremdschlüssel, referentielle Integrität)
Seien R und R 0 zwei Relationen (Tabellen), κ der Primärschlüssel in R
und α ein entsprechender Fremdschlüssel in R 0 . Dann muss für alle
Tupel (Zeilen) r 0 in R 0 gelten:
1
2
Entweder r 0 .α enthält lauter NULL-Werte oder nur Werte 6= NULL.
Wenn r 0 .α keine NULL-Werte enthält, dann gibt es ein r in R,
sodass
r 0 .α = r .κ.
Dies wird als referentielle Integrität bezeichnet.
Beispiel: Jede P_No in Orders ist entweder NULL oder entspricht
einer P_No in Parts.
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Referentielle Integrität: “Dangling References”
Weiteres Beispiel: Jeder Wert für director in movie ist entweder
NULL oder entspricht einer id in actor.
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Referentielle Integrität: “Dangling References”
Weiteres Beispiel: Jeder Wert für director in movie ist entweder
NULL oder entspricht einer id in actor.
Fügen wir einen neuen Film in die Tabelle movie
?id
title
votes
score
12345
Databaseland
1
2.3
director
0
mit einem Wert für director (0), der nicht als id in actor vorkommt,
so wird die referentielle Integrität der Datenbank verletzt.
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Referentielle Integrität: “Dangling References”
Weiteres Beispiel: Jeder Wert für director in movie ist entweder
NULL oder entspricht einer id in actor.
Fügen wir einen neuen Film in die Tabelle movie
?id
title
votes
score
12345
Databaseland
1
2.3
director
0
mit einem Wert für director (0), der nicht als id in actor vorkommt,
so wird die referentielle Integrität der Datenbank verletzt.
Der entsprechende Eintrag für director ist eine sogenannte
“Dangling Reference”.
→ Bei Änderungen in einer Datenbank müssen solche Einträge
vermieden werden! (dynamische Integrität)
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Erhaltung der referentiellen Integrität
Definition (Fremdschlüssel, referentielle Integrität)
Seien R und R 0 zwei Relationen, κ der Primärschlüssel in R und α ein
entsprechender Fremdschlüssel in R 0 . Dann muss für alle Tupel r 0 in
R 0 gelten:
1
2
Entweder r 0 .α enthält lauter NULL-Werte oder nur Werte 6= NULL.
Wenn r 0 .α keine NULL-Werte enthält, dann gibt es ein r in R,
sodass
r 0 .α = r .κ.
Dies wird als referentielle Integrität bezeichnet.
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Erhaltung der referentiellen Integrität
Gemäß der Definition muss gelten:
Πα (R 0 ) ⊆ Πκ (R),
wobei Πα (R 0 ) alle α-Werte in R 0 sind und Πκ (R) alle κ-Werte in R.
→ Erlaubte Änderungen in Datenbank:
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Erhaltung der referentiellen Integrität
Gemäß der Definition muss gelten:
Πα (R 0 ) ⊆ Πκ (R),
wobei Πα (R 0 ) alle α-Werte in R 0 sind und Πκ (R) alle κ-Werte in R.
→ Erlaubte Änderungen in Datenbank:
1
Hinzufügen von r 0 in R 0 wenn r 0 (α) in Πκ (R):
z.B. einen neuen Film hinzufügen, dessen director in actor
bereits vorkommt
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Erhaltung der referentiellen Integrität
Gemäß der Definition muss gelten:
Πα (R 0 ) ⊆ Πκ (R),
wobei Πα (R 0 ) alle α-Werte in R 0 sind und Πκ (R) alle κ-Werte in R.
→ Erlaubte Änderungen in Datenbank:
1
Hinzufügen von r 0 in R 0 wenn r 0 (α) in Πκ (R):
z.B. einen neuen Film hinzufügen, dessen director in actor
bereits vorkommt
2
Ändern eines Wertes r 0 .α von w auf w 0 mit w 0 in Πκ (R):
z.B. Ändern des director eines Films auf einen in actor
vorkommenden Wert
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Erhaltung der referentiellen Integrität
Gemäß der Definition muss gelten:
Πα (R 0 ) ⊆ Πκ (R),
wobei Πα (R 0 ) alle α-Werte in R 0 sind und Πκ (R) alle κ-Werte in R.
→ Erlaubte Änderungen in Datenbank:
1
Hinzufügen von r 0 in R 0 wenn r 0 (α) in Πκ (R):
z.B. einen neuen Film hinzufügen, dessen director in actor
bereits vorkommt
2
Ändern eines Wertes r 0 .α von w auf w 0 mit w 0 in Πκ (R):
z.B. Ändern des director eines Films auf einen in actor
vorkommenden Wert
3
Ändern oder Löschen von r .κ in R wenn σα=r .κ (R 0 ) = ∅:
z.B. Ändern oder Löschen einer id in actor,
wenn es keine Filme in movie mit dieser id als director gibt
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Wenn referentielle Integrität verletzt wird
Was kann man im Falle einer Verletzung der referentiellen Integrität
(verursacht durch eine Änderung in der Datenbank) machen?
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Wenn referentielle Integrität verletzt wird
Was kann man im Falle einer Verletzung der referentiellen Integrität
(verursacht durch eine Änderung in der Datenbank) machen?
1
Erlaube entsprechende Operation nicht / mache sie wieder
rückgängig.
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Wenn referentielle Integrität verletzt wird
Was kann man im Falle einer Verletzung der referentiellen Integrität
(verursacht durch eine Änderung in der Datenbank) machen?
1
Erlaube entsprechende Operation nicht / mache sie wieder
rückgängig.
2
Setze Werte im Fremdschlüssel auf NULL:
Beispiel: Wenn ein director in actor gelöscht oder seine id
verändert wird, dann setze den Wert von director in movie auf
NULL.
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Wenn referentielle Integrität verletzt wird
Was kann man im Falle einer Verletzung der referentiellen Integrität
(verursacht durch eine Änderung in der Datenbank) machen?
1
2
Erlaube entsprechende Operation nicht / mache sie wieder
rückgängig.
Setze Werte im Fremdschlüssel auf NULL.
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Wenn referentielle Integrität verletzt wird
Was kann man im Falle einer Verletzung der referentiellen Integrität
(verursacht durch eine Änderung in der Datenbank) machen?
1
2
3
Erlaube entsprechende Operation nicht / mache sie wieder
rückgängig.
Setze Werte im Fremdschlüssel auf NULL.
Kaskadieren:
Beispiele:
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Wenn referentielle Integrität verletzt wird
Was kann man im Falle einer Verletzung der referentiellen Integrität
(verursacht durch eine Änderung in der Datenbank) machen?
1
2
3
Erlaube entsprechende Operation nicht / mache sie wieder
rückgängig.
Setze Werte im Fremdschlüssel auf NULL.
Kaskadieren:
Beispiele:
Wenn id in actor verändert wird
→ verändere entsprechende Werte von director in movie.
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Wenn referentielle Integrität verletzt wird
Was kann man im Falle einer Verletzung der referentiellen Integrität
(verursacht durch eine Änderung in der Datenbank) machen?
1
2
3
Erlaube entsprechende Operation nicht / mache sie wieder
rückgängig.
Setze Werte im Fremdschlüssel auf NULL.
Kaskadieren:
Beispiele:
Wenn id in actor verändert wird
→ verändere entsprechende Werte von director in movie.
Wenn id in actor gelöscht wird
→ lösche alle entsprechenden Filme in movie.
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Wenn referentielle Integrität verletzt wird
Was kann man im Falle einer Verletzung der referentiellen Integrität
(verursacht durch eine Änderung in der Datenbank) machen?
1
2
3
Erlaube entsprechende Operation nicht / mache sie wieder
rückgängig.
Setze Werte im Fremdschlüssel auf NULL.
Kaskadieren:
Beispiele:
Wenn id in actor verändert wird
→ verändere entsprechende Werte von director in movie.
Wenn id in actor gelöscht wird
→ lösche alle entsprechenden Filme in movie.
NB:
Kaskadieren kann riskant sein, da z.B. ein Löschvorgang mehrere
automatische Operationen auslösen kann!
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Wenn referentielle Integrität verletzt wird
Was kann man im Falle einer Verletzung der referentiellen Integrität
(verursacht durch eine Änderung in der Datenbank) machen?
1
Erlaube entsprechende Operation nicht / mache sie wieder
rückgängig
2
Setze Werte im Fremdschlüssel auf NULL.
3
Kaskadieren
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Wenn referentielle Integrität verletzt wird
Was kann man im Falle einer Verletzung der referentiellen Integrität
(verursacht durch eine Änderung in der Datenbank) machen?
1
Erlaube entsprechende Operation nicht / mache sie wieder
rückgängig
2
Setze Werte im Fremdschlüssel auf NULL.
3
Kaskadieren
4
Verwende Trigger :
d.h., lasse bestimmte Prozeduren ablaufen, wenn es Änderung in
Datenbank gibt.
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Wenn referentielle Integrität verletzt wird
Was kann man im Falle einer Verletzung der referentiellen Integrität
(verursacht durch eine Änderung in der Datenbank) machen?
1
Erlaube entsprechende Operation nicht / mache sie wieder
rückgängig
2
Setze Werte im Fremdschlüssel auf NULL.
3
Kaskadieren
4
Verwende Trigger :
d.h., lasse bestimmte Prozeduren ablaufen, wenn es Änderung in
Datenbank gibt.
NB:
Wie Kaskadieren beinhaltet auch das Verwenden von Triggern u.U.
bestimmte Risiken!
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
Komplexere Integritätsbedingungen
Referentielle Integrität ist eine einfache Form von Bedingungen an
die Datenintegrität.
Komplexere Integritätsbedingungen sind möglich.
Beispiel:
Erlaube nur jenen Studierenden eine Prüfungsanmeldung, die
auch zur Lehrveranstaltung angemeldet waren und/oder
bestimmte andere Fächer bereits positiv abgeschlossen haben.
über Trigger oder Applikation in höherer Programmiersprache
behandeln
Ronald Ortner
Organisatorisches
SQL
Relationale Algebra – Notation
Datenintegrität
SQL and Datenintegrität
In Abhängigkeit von der SQL Implementierung gibt es in SQL die
Möglichkeit,
√
Primär- und Fremdschlüssel in Tabellen festzulegen (MySQL )
die statische Datenintegrität zu überprüfen (MySQL×)
√
Kaskadieren zu benutzen (MySQL )
√
Trigger zu definieren (MySQL )
Ronald Ortner