Klausurvorbereitung zu Datenbanksysteme I von Rami Swailem

Rami Swailem
FH Gießen-Friedberg
1
Datenbanksysteme I
Klausurvorbereitung
Grundlagen
Begriffe
Daten bekannte zutreffende Tatsachen über die Domäne/Miniwelt
DBS Einsatz eines DBMS für eine Datenbank, DBS besteht aus folgenden Komponenten:
1. DBMS
2. Datenbank
DBMS Software für Erstellen und Pflegen von Datenbanken
Datenbank strukturierte Sammlung von Daten
• logisch zusammenhängende Daten
• systematisch ausgezeichnet
• gespeichert & gepflegt
• erfüllen einen bestimmten Zweck für Anwender
1.1
Übersicht
Akteure auf der Bühne
• Datenadminstrator, Datenbankdesigner, Systemanalytiker (für die logische Sicht zuständig)
• Datenbankadministrator DBA (für die technische Sicht zuständig)
• Endbenutzer ("Poweruser Formular verwenden – Informationsabfragen)
• Software
Akteure hinter der Bühne
• Entwickler des DBMS
• Werkzeugentwickler
1.2
Datenbankarchitektur & Datenunabhängigkeit
DbSysI.Klausurvorbereitung.tex,v,1.2,January 30, 2006 at 17:11:33 CET
Datenmodell Konzept zur Beschreibung der Struktur von Daten und Datenbanken
Datenbankschema Beschreibung der Struktur von Daten
Datenbankzustand Inhalt einer DB zu einem bestimmten Zeitpunkt
ANSI/SPARC Architektur
externe Ebene (benutzerspezifische Sicht) beschreibt einen Ausschnitt der Struktur, so wie die
Anwender sie brauchen (verschiedene Sichten für verschiedene Anwendungen)
konzeptuelle Ebene (begrifflich) beschreibt die Struktur der Daten in Termini des relationalen
Datenmodell (Darstellung der Daten, wie sie logisch gebaut sind bzw. Art der Daten, wie sie
zusammenhängen also logisches Datenbankdesign)
Wintersemester 2005/06
1
Rami Swailem
FH Gießen-Friedberg
Datenbanksysteme I
Klausurvorbereitung
interne Ebene beschreibt wie und wo die Daten physisch gespeichert sind (Datenspeicherung
also physisches Datenbankdesign)
Datenbanksprachen
SQL Structure Query Language
DDL Data Definition Language (create, alter, ceate view)
DML Data Manipulation Language (select, insert, update, delete) (Rudi)
DCL Data Control Language (Grant, Revoke)
2
Das relationale Modell
Begriffe
Ein Tupel ist eine Menge von Paaren
Relation ist
Relationsschema
Der Grad einer Relation ist die Anzahl von Attributen n des Relationsschema
Attribut ist eine Eigenschaft, ein Merkmal eines Objektes.
Definition: Eine Datenbank im relationalen Modell ist
1. eine Menge von Relationsvariablen R1, ..., Rn mit ihrem jeweiligen Relationsschema
2. eine Menge von Integritätsbedingungen zu diesen Relationsvariablen. D.h. die Integritätsbedingungen müssen in jedem Datenbankzustand erfüllt sein
2.1
Integritätsbedingungen, die eine Relation betreffen
Definition: Ein Superschlüssel ist eine Menge von Attributen mit:
Es kann keine zwei Tupel geben, die an diesen Attributen dieselben Werte haben
Definition: Ein Schlüssel ist minimaler Superschlüssel
D.h. lässt man an einem Schlüssel ein Attribut weg, gibt es Tupel mit dieselben Werten
DbSysI.Klausurvorbereitung.tex,v,1.2,January 30, 2006 at 17:11:33 CET
Beispiel: ANGESTELLTER
{SSN, NNAME} Superschlüssel
{SSN} Schlüssel
2.2
Integritätsbedingungen, die mehrere Relation betreffen
Die wichtigste dieser Integritätsbedingungen ist die referenzielle Integrität
Beispiel: in der Tabelle ANGESTELLTER dürfen nur Abteilungsnummern eingetragen werden, die
es in der Tabelle ABTEILUNG auch tatsächlich gibt.
Definition: Ein Fremdschlüssel der Relation R, der die Relation S referenziert, ist eine Menge
von R, für die gilt:
Wintersemester 2005/06
2
Rami Swailem
FH Gießen-Friedberg
Datenbanksysteme I
Klausurvorbereitung
1. Die Attribute des Fremdschlüssels in R haben dieselben Wertbereich wie die referenzierten Attribute in S
2. Ein Tupel in R kann nur Werte an Fremdschlüssel haben, die S an den referenzierten
Attributen auch vorkommen
Ausnahme: Ein Fremdschlüssel darf NULL sein
2.3
Überprüfung referenzielle Integrität
1. Beim Insert wird geprüft
(a) alle Integritätsbedingungen der Tabelle
(b) existieren alle referenzierten Werte?
2. Beim Löschen/Delete wird überprüft
enthält die zu löschende Zeile, die referenziert werden?
optional: kaskadierendes Löschen
3. Beim Update wird überprüft
(a) alle Integritätsbedingungen der Tabelle
(b) referenzielle Integrität
optional: Propagation von Änderungen bzw. kaskadierendes Update
2.4
2.4.1
SQL (Structured Qery Language)
Anfragen in SQL
Grundstruktur
Select < A t t r i b u t > −−wie s i e h t meine E r g e n b i s t a b e l l e aus?
From <Tabellenausdruck > −−welche T a b e l l e n s i n d b e r u e h r t ?
Where <Bedingung > −−welche Z e i l e n s o l l e n gewaehlt werden?
Beispiel
DbSysI.Klausurvorbereitung.tex,v,1.2,January 30, 2006 at 17:11:33 CET
s e l e c t GDATUM, ADRESSE
from ANGESTELLTER
where VNAME= ’ John ’ and I N I T I A L = ’B ’
and NNAME= ’ Smith ’
πGDAT U M,ADRESSE (σV N AM E=0 John0 AN DIN IT IAL=0 B 0 AN DN N AM E=0 Smith0 (AN GEST ELLT ER))
Der Operator Like
s e l e c t VNAME, NNAME, GDATUM, ADRESSE
from ANGESTELLTER
where ADRESSE l i k e ’%Houston ’
−− % s t e h t f u e r e i n e b e l . Anzahl von Zeichen
−−where GDATUM l i k e ’195_−__−__ ’
−− _ s t e h t f u e r genau e i n Zeichen
Wintersemester 2005/06
3
Rami Swailem
FH Gießen-Friedberg
Datenbanksysteme I
Klausurvorbereitung
Der Operator between
s e l e c t ∗ from ANGESTELLTER
where GEHALT between 30000 and 40000
and ANR=5
Der Operator exists
NAME der Angestellten mit Angehörigen gleiches Geschlechts
s e l e c t A .VNAME, A .NNAME from ANGESTELLTER as A
where e x i s t s ( s e l e c t ∗ from ANGEHOERIGER as ANG
where A .SSN=ANG.ESSN
and A .GESCHLECHT=ANG.GESCHLECHT)
NAME der Angestellten, die keine Angehörigen haben
s e l e c t A .VNAME, A .NNAME from ANGESTELLTER as A
where not e x i s t s ( s e l e c t ∗ from ANGEHOERIGER
where A .SSN=ESSN)
falsch
select distinct count(sno)
richtig
select count(distinct sno)
Formen des JOINs
1. Natural Join / Inner Join
select * from A inner join B on A.x=B.x
//"inner" kann man weglassen
Ao
n B // nur passende Kombinationen. Zeilen, die NULL haben, werden nicht berücksichtigt.
2. Cross Join
DbSysI.Klausurvorbereitung.tex,v,1.2,January 30, 2006 at 17:11:33 CET
select * from A cross join B
-ODERselect * from A, B
A × B // alle möglichen Kombinationen.
produziert alle möglichen Zeilenkombinationen aus den beiden Tabellen. → Kreuztabellenprodukt oder kartesisches Produkt
3. Left Outer Join = Left Join
select * from A left outer join B on A.x=B.x
Alle Datensätze aus der ersten (linken) Tabelle werden eingeschlossen, auch wenn keine
entsprechenden Werte für Datensätze in der zweiten Tabelle existieren
Wintersemester 2005/06
4
Rami Swailem
FH Gießen-Friedberg
Datenbanksysteme I
Klausurvorbereitung
4. Right Outer Join
siehe left outer Join
left und outer join beziehen sich also auf die Position der Tabellen in der select-Anweisung.
5. Full Outer Join = Full Join
select * from A full outer join B on A.x=B.x
produziert eine Kombination von Left Outer Join und Right Outer Join.
3
Datenbank-Design
3.1
Semantische Modellierung mit dem Entity-Relationship-Modell
Transformation des E-R-Modells in relationales Datenbankschema
1. Regel für Entitätstyp
Entitätstyp → Tabelle
2. Regel für Primärschlüssel (klar)
3. Regel für mehrwertige Attribute
Mehrwertiges Attribut → Tabelle
4. Regel für 1-1– Assoziationen
Drei Fälle
(a)
(b)
(c)
A 1
1..* B
A 1
0..1 B
Verschmelzung der Entitätstypen zu einer Tabelle
In der Tabelle B einen Fremdschlüssel auf A versehen.
A 0..1 0..1 B Wie (b) aber NULL erlaubt
5. Regel für 1-n– Assoziationen
1-n– Assoziation → Fremdschlüsselbeziehung
DbSysI.Klausurvorbereitung.tex,v,1.2,January 30, 2006 at 17:11:33 CET
Fälle
Fremdschlüssel not null
M 1
1..* D
M 1
* D
Fremdschlüssel, wo not null erlaubt ist
Wintersemester 2005/06
5
Rami Swailem
FH Gießen-Friedberg
Datenbanksysteme I
Klausurvorbereitung
M 0..1 1..* D
M 0..1
* D
6. Regel für n-m– Assoziationen
n-m– Assoziation → Zwischentabelle + 2 Fremdschlüsselbeziehungen
7. Regel für mehrwerttige (n-äre) Assoziationen (n>2)
näre Assoziation → Zwischentabelle + n Fremdschlüsselbeziehungen
8. Regel für Super-/ Subklassen–Beziehungen
Variante 1 Pro Einheitstyp eine Tabelle und geeignete View
Variante 2 Eine Tabelle pro Subklasse und geeignete View
Variante 3 eine Tabelle für alle Einheitstypen und geeignete Views
Variante 4 Eine Tabelle für alle Entitäten mit Flags
3.2
Funktionale Abhängigkeiten
3.3
Normalformen
unnötige Redundanz entdecken und vermeiden
3.3.1
Erste Normalform (1NF)
Ein Relationsschema ist in 1NF, wenn alle Attribute nur atomare Werte enthalten
3.3.2
Zweite Normalform (2NF)
Die 2NF ist verletzt, wenn ei Attribut nur von einem Teil des Schlüssel abhängt
wie kann man die 2NF erreichen?
Zerlegen des Relationsschema in Tabellen, bei denen die Teile des ursprünglichen Schlüssel
selbst die Schlüssel sind
DbSysI.Klausurvorbereitung.tex,v,1.2,January 30, 2006 at 17:11:33 CET
3.3.3
Die dritte Normalform (3NF)
3NF ist verletzt, wenn ein Nicht-Schlüssel-Attribut nicht nur vom Schlüssel, sondern auch von
einem anderen Nicht-Schlüssel-Attribut abhängig ist
Wie kann eine Tabelle in 3NF bringen?
Zerlegen der Tabelle so, dass die transitive Abhängigkeit aufgelöst wird
Wintersemester 2005/06
6
Rami Swailem
FH Gießen-Friedberg
3.3.4
4
Datenbanksysteme I
Klausurvorbereitung
Die Boyce-Cod-Normalform (BCNF)
Transaktionsmanagament
4.1
Transaktionen
Eine Transaktion ist eine logische Verarbeitungseinheit auf einer Datenbank, die eine oder
mehrere Datenbankoperationen (Einfügen, Ändern, Löschen oder Schreiben) umfasst
Eigenschaften von Transaktionen
ACID
Atomarität
Consistenz
Isolation
Dauerhaftbarkeit
atomicity
consistency
ioslation
durability
A = Atomarität Die Teilschritte einer Transaktion werden als eine unteilbare (atomare) Einheit
durchgeführt, d.h. alle Teilschritte oder gar keine
C = Konsistenz Die Datenbank ist vor und nach einer Transaktion in einem (konsistenzen) zustand, indem alle Integritätsbedingungen erfüllt sind
I = Isolation Eine Transaktion läuft unbeeinflusst (isoliert) von anderen Transaktionen ab, so als
ob sie exklusiven Zugriff auf die Datenbank hätte
D = Dauerhaftbarkeit Die Daten einer bestätigten Transaktion (nach dem Commit) sind dauerhaft gesichert, d.h. das DBMS garantiert, dass sie auch bei Fehlerereignissen sicher
wiederhergestellt werden können
4.2
Recovery (Datenwiederherstellen)
Write-Ahed-Log
Ein Log (Systemlog) ist eine Folge von Einträgen, die den Ablauf und den Inhalt von Transaktionen
enthalten,– gespeichert in einer Logdatei
DbSysI.Klausurvorbereitung.tex,v,1.2,January 30, 2006 at 17:11:33 CET
4.2.1
Vorgehen beim Rollback
4.2.2
Vorgehen bei Systemabsturz
4.2.3
Vorgehen bei Plattencrash
4.2.4
Beispiel Oracle
4.3
Isolationslevel
4.4
serielle und serialisierbare Abläufe
Ein Ablauf von Transaktionen und ihren Teilschritten heißt seriell, wenn erst alle Transaktionen
einer Aktion danach, die der zweite Transaktion usw. durchgeführt werden. D.h. die Transaktionen
kommen nacheinander dran.
Vorteil: Isolierung ist kein Problem = Isolierung ist gegeben.
Nachteil: Transaktionen müssen unnötigerweise warten
Wintersemester 2005/06
7
Rami Swailem
FH Gießen-Friedberg
4.5
Datenbanksysteme I
Klausurvorbereitung
Phänomene verschränkte Transaktionen
Phänomen 1: „Dirty Read“
Ein Dirty Read liegt vor, wenn eine Transaktion die Änderung eines Datenobjekts durch eine
andere Transaktion lesen kann, bevor die andere Transaktion die Änderung committed hat.
Dirty Reads treten auf, wenn für die Transaktion der Isolation Level Read Uncommitted
gewählt wurde. Bei den Isolation Levels Read Committed, Repeatable Read und Serializable
sind Dirty Reads ausgeschlossen.
Phänomen 2: „Nonrepeatable Read“
Während einer laufenden Transaktion können Daten von anderen Transaktionen geändert
und committed werden, so dass in der ersten Transaktion ein zweites Auslesen zu anderen
Daten führt.
Phänomen 3: „Phantom“
Eine erste Transaktion liest über eine „Where-Klausel“ eine Liste von Datensätzen. Eine
zweite Transaktion fügt weitere Datensätze hinzu (inkl. Commit). Wenn die erste Transaktion
wieder über die gleiche „Where-Klausel“ Datensätze liest oder bearbeitet, gibt es mehr
Datensätze als vorher.
4.6
Definition der Isolationslevel
• Read Uncommitted:
Geringste Isolation, höchste Performance, es können Dirty Reads, Nonrepeatable Reads
und Phantom Reads auftreten
• Read Committed:
Es gibt keine Dirty Reads mehr, aber es gibt weiterhin Nonrepeatable Reads und Phantom
Reads
• Repeatable Read:
Keine Dirty Reads und keine Nonrepeatable Reads, aber weiterhin Phantom Reads
• Serializable:
Keine Dirty Reads, keine Nonrepeatable Reads und keine Phantom Reads, höchste Isolation,
geringste Performance
DbSysI.Klausurvorbereitung.tex,v,1.2,January 30, 2006 at 17:11:33 CET
Wintersemester 2005/06
8