Datenbanken

Datenbanken
Christian Silberbauer
Inhaltsverzeichnis
1. Allgemeine Begriffsdefinitionen .................................................................................................... 1
1.1. Datenbanken allgemein .............................................................................................................. 1
1.2. Relationale Datenbanken ........................................................................................................... 1
1.3. Transaktion ................................................................................................................................. 2
2. Normalformen .................................................................................................................................. 3
2.1. Begriffsdefinitionen ..................................................................................................................... 3
2.2. Definitionen der Normalformen................................................................................................... 3
3. SQL ................................................................................................................................................... 5
3.1. Begriffsdefinitionen ..................................................................................................................... 5
3.2. Data Definition Language (DDL)................................................................................................. 5
3.3. Data Manipulation Language (DML)........................................................................................... 8
4. SQL für Objektrelationale Datenbanken ..................................................................................... 11
5. PHP ................................................................................................................................................. 13
5.1. Funktionen ................................................................................................................................ 13
5.2. Beispielskripte........................................................................................................................... 14
6. Regeln zum Datenbankentwurf.................................................................................................... 16
7. Concurrency und Recovery ......................................................................................................... 17
Dieses Skript orientiert sich überwiegend an der Vorlesung Datenbanken im Studiengang
Wirtschaftsinformatik an der Fachhochschule Regensburg von Prof. Dr. Schicker.
Alle Rechte vorbehalten.
Allgemeine Begriffsdefinitionen
1. Allgemeine Begriffsdefinitionen
1.1. Datenbanken allgemein
1.1.1.
Datenbank
Semantisch zusammengehörige Daten, die von einem Datenbankmanagementsystem verwaltet
werden.
1.1.2.
Datenbankmanagementsystem (DBMS)
Ein Datenbankverwaltungssystem kapselt die Verwaltung persistenter Daten von deren Nutzung
durch ein Anwendungssystem. Semantisch zusammengehörige Daten werden in so genannten
Datenbanken vereint.
Die derzeit wirtschaftlich erfolgreichsten Datenbankmanagementsysteme sind der Reihe nach
„Oracle“ (Oracle), „DB/2“ (IBM) und „SQL-Server“ (Microsoft). Weit verbreitet sind zudem „MS-Access“
(Microsoft) und „MySQL“.
1.1.3.
Datenbankschnittstelle
Die Datenbankschnittstelle ermöglicht dem Anwendungssystem den Zugriff die Daten der Datenbank.
1.2. Relationale Datenbanken
1.2.1.
Relationale Datenbank
Relationale Datenbanken sind Datenbanken, deren Daten in Relationen gespeichert sind.
Beziehungen zwischen diesen Relationen werden ausschließlich durch Fremdschlüsselattribute
hergestellt.
1.2.2.
Überblick
Formale Relationale Ausdrücke Informelle Ausdrucksweise
Relation
Tupel
Kardinalität
Attribut
Grad
Primärschlüssel
Gebiet
1.2.3.
Tabelle
eine Zeile einer Tabelle
Anzahl der Zeilen einer Tabelle
eine Spalte einer Tabelle
Anzahl der Spalten einer Tabelle
eindeutiger Bezeichner
Menge aller möglichen Werte
Relation
Eine Relation ist eine Tabelle mit folgenden Eigenschaften:
-
Es gibt keine doppelten Tupel
Tupel sind nicht geordnet
Attribute sind nicht geordnet
Alle Attribute sind atomar
Datenbanken
-1-
Christian Silberbauer
Allgemeine Begriffsdefinitionen
1.2.4.
Schwache Relation
Eine Relation heißt „schwach“, wenn sie lediglich eine andere Relation referenziert und die Existenz
ihrer Tupel von der Existenz der referenzierten Tupel der anderen Relation abhängig ist.
Eine Subtyp-Relation (1-1-Beziehung) ist meistens schwach.
1.2.5.
Beziehungsrelation
Eine Beziehungsrelation ist eine Relation mit zwei oder mehreren Fremdschlüsseln, die zusammen
einen Schlüsselkandidaten bilden.
1.2.6.
Schlüsselkandidat
Ein Schlüsselkandidat besteht aus einer minimalen Menge von Attributen einer Relation, die die Tupel
der Relation eindeutig identifizieren.
„minimale Menge“ bedeutet, dass keine Teilmenge dieser Attribute auch Schlüsselkandidat sein darf.
1.2.7.
Primärschlüssel
Ein Primärschlüssel ist ein ausgewählter Schlüsselkandidat einer Relation. Der Wert eines
Fremdschlüssels, der auf ein Tupel dieser Relation verweist, muss einem Wert des Primärschlüssels
entsprechen.
1.2.8.
Alternative Schlüssel
Alternative Schlüssel sind Schlüsselkandidaten, die nicht Primärschlüssel sind.
1.2.9.
Fremdschlüssel
Als Fremdschlüssel bezeichnet man ein oder mehrere Attribute, der oder die gemeinsam einen
definierten Primärschlüssel beinhalten und somit auf das zugehörige Tupel verweisen.
1.2.10. Schlüsselattribut
Ein Schlüsselattribut ist ein Attribut eines Schlüsselkandidaten.
1.2.11. Nichtschlüsselattribut
Ein Nichtschlüsselattribut ist ein Attribut, das keinem Schlüsselkandidaten zugehört.
1.3. Transaktion
Eine Transaktion ist eine feste Folge von Operationen. Transaktionen besitzen die ACIDEigenschaften:
Atomarität (atomicity):
Eine Transaktion wird entweder ganz oder gar nicht
ausgeführt.
Konsistenz (consistency):
Nach Ausführung der Transaktion ist die Datenbank wieder
in widerspruchsfreiem Zustand.
Isolation (isolation):
Jede Transaktion ist unabhängig von eventuell parallel
laufenden Transaktionen.
Dauerhaftigkeit/Persistenz (durability): Änderungen von Transaktionen gelten auf Dauer.
Datenbanken
-2-
Christian Silberbauer
Normalformen
2. Normalformen
2.1. Begriffsdefinitionen
2.1.1.
Funktionale Abhängigkeit
Ein Attribut Y einer Relation R heißt „funktional abhängig“ vom Attribut X derselben Relation, wenn zu
jedem X-Wert höchstens ein Y-Wert möglich ist. (X -> Y)
alle Attribute hängen vom Primärschlüssel und den alternativen Schlüsseln funktional ab.
2.1.2.
Volle Funktionale Abhängigkeit
Ein Attribut Y einer Relation R heißt „voll funktional abhängig“ vom Attribut X derselben Relation, wenn
es funktional abhängig ist von X und es nicht funktional abhängig ist von beliebigen Teilattributen von
X. (X => Y)
es gilt:
es gilt nicht:
2.1.3.
Primärschlüssel -> alle anderen Attribute
Primärschlüssel => alle anderen Attribute
Determinante
Eine Determinante ist ein Attribut, von dem ein anderes voll funktional abhängig ist.
2.1.4.
Mehrfache Abhängigkeit (Grob-Definition)
Ein Attribut Y einer Relation R heißt mehrfach abhängig vom Attribut X derselben Relation, wenn zu
jedem X-Wert höchstens eine eng begrenzte Anzahl Y-Werte möglich sind.
2.2. Definitionen der Normalformen
2.2.1.
1. Normalform
Eine Relation ist in der 1. Normalform, wenn alle ihr zugrunde liegenden Gebiete nur atomare Werte
enthalten.
Eine (normalisierte) Relation ist immer in der ersten Normalform
2.2.2.
2. Normalform
Eine Relation ist in der 2. Normalform, wenn sie in der 1. Normalform ist und jedes NichtSchlüsselattribut vom Primärschlüssel voll funktional abhängig ist.
Das heißt, die Attribute müssen vom ganzen Primärschlüssel abhängig sein
Es gilt mit 2. NF: Primärschlüssel => Nichtschlüsselattribute
Eine Relation in erster Normalform mit nicht zusammengesetztem Primärschlüssel ist immer in
der zweiten Normalform
2.2.3.
3. Normalform (nach Boyce/Codd)
Eine Relation ist in der 3. Normalform, wenn sie in der 2. Normalform ist und jede Determinante dieser
Relation ein Schlüsselkandidat ist.
Das heißt, ein Nichtschlüsselattribut darf von keinem anderen Nichtschlüsselattribut abhängig
sein.
Datenbanken
-3-
Christian Silberbauer
Normalformen
2.2.4.
4. Normalform
Eine Relation ist in der 4. Normalform, wenn sie in der 3. Normalform ist und jede mehrfache
Abhängigkeit auch eine funktionale Abhängigkeit nach sich zieht.
2.2.5.
5. Normalform (Projektion-Join-Normalform; Grob-Definition)
Eine Relation ist in der 5. Normalform, wenn sie in der 4. Normalform ist und sie nicht-trivial nicht mehr
in mindestens zwei Relationen zerlegt werden kann.
Datenbanken
-4-
Christian Silberbauer
SQL
3. SQL
3.1. Begriffsdefinitionen
SQL (Structured Query Language) ist eine Abfrage- und Manipulationssprache für relationale
Datenbanken. SQL ist eine deklarative, mengenorientierte Sprache. SQL-Anweisungen werden in drei
Klassen:
-
DDL (Data Definition Lanugage): Datenbankobjekte (Tabellen, Views etc.) angelegen und
manipulieren (z.B. CREATE TABLE, CREATE VIEW)
-
DML (Data Manipulation Language): Daten aus Tabellen selektieren und verändern (z.B.
SELECT, INSERT, UPDATE, DELETE)
-
DCL (Data Control Language): Steuern von Transaktionen (z.B. COMMIT, ROLLBACK)
3.2. Data Definition Language (DDL)
3.2.1.
CREATE TABLE-Befehl
Syntax
CREATE TABLE Tabellenname
({
Spalte { Datentyp | Gebietsname } [Spaltenbedingung […]]
| Tabellenbedingung
}
[, …]
)
Spaltenbedingung :=
[CONSTRAINT Bedingungsname]
{
NOT NULL | PRIMARY KEY | UNIQUE | CHECK (Bedingung)
| REFERENCES Tabellenname [(Spalte [,…])]
[ ON DELETE { NO ACTION | CASCADE | SET NULL }]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL }]
} […]
Tabellenbedingung :=
[CONSTRAINT Bedingungsname]
{
PRIMARY KEY (Spalte [, …]) | UNIQUE (Spalte [, …])
| CHECK (Bedingung)
| FOREIGN KEY (Spalte [,…]) REFERENCES Tabellenname [(Spalte [,…])]
[ ON DELETE { NO ACTION | CASCADE | SET NULL }]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL }]
} […]
Datenbanken
-5-
Christian Silberbauer
SQL
Die wichtigsten Datentypen
INTEGER, INT
Ganzzahl
SMALLINT
Ganzzahl
NUMERIC(x, y)
x-stellige Dezimalzahl mit y Nachkommastellen
DECIMAL(x, y)
mindestens x-stellige Dezimalzahl mit y Nachkommastellen
FLOAT(x)
Gleitpunktzahl mit x Nachkommstellen
CHARACTER(n), CHAR(n)
Zeichenkette der Länge n (ungenutzte Zeichen werden mit
Leerzeichen aufgefüllt!)
VARCHAR(n)
Variable Zeichenketten mit bis zu n Zeichen
DATE
Datumsfeld
Anwenden der relationalen Integritätsregel
Im Falle von Manipulationen referenzierter Tupel (Primärschlüsselwerte ändern oder Tupel löschen)
sind bei einem Fremdschlüssel folgende Konsequenzen zur Erhaltung der relationalen Integrität
möglich:
Aktion zurückweisen
ON DELETE NO ACTION
ON UPDATE NO ACTION
Aktion kaskadierend ausführen
ON DELETE CASCADE
ON UPDATE CASCADE
Referenzen auf NULL setzen
ON DELETE SET NULL
ON UPDATE SET NULL
Datumsfeld
Variable (String) in Datum umwandeln
CAST
z.B. CAST(‘2000-01-01‘ AS DATE)
DATE
z.B. DATE ‘2000-01-01‘
Oracle
TO_DATE
z.B. TO_DATE(‘00-Jan-01‘,‘YY-MMM-DD‘)
MySQL
STR_TO_DATE z.B. STR_TO_DATE(‘1.1.2000‘,‘%d.%m.%y‘)
MS-Access
CDATE
SQL
z.B. CDATE(‘1.1.00‘)
Heutiges Datum
SQL, Oracle, MySQL
Current_Date
Oracle
Sysdate
MS-Access
Date()
CHECK
Im CHECK als Spaltenbedingung darf nur auf das assoziierte Attribut selbst Bezug genommen werden.
Andernfalls ist eine Tabellenbedingung notwendig.
Datenbanken
-6-
Christian Silberbauer
SQL
3.2.2.
CREATE VIEW-Befehl
Syntax
CREATE VIEW Sichtname [(Spalte [, …] )]
AS Select-Befehl
[WITH CHECK OPTION]
// wenn gesetzt, wird eine Änderung auf die Sicht nur dann
// ausgeführt, wenn die betroffenen Tupel weiterhin Elemente
// der Sicht bleiben.
Mutationen auf Sichten sind nicht erlaubt, wenn der Select-Befehl…
- auf mehr als eine Relation zugreift,
- eine GROUP-BY-Klausel enthält,
- eine DISTINCT-Angabe enthält,
- die Spaltenliste nicht nur aus einzelnen Spaltennamen besteht,
- mindestens einen der Operatoren UNION, INTERSECT oder EXCEPT enthält
oder:
- der SELECT-Befehl auf einer Sicht basiert, die eine der fünf obigen Bedingungen erfüllt
3.2.3.
GRANT-Befehl
Syntax
GRANT { Zugriffsrecht [,…] | ALL PRIVILEGES }
ON { DOMAIN Gebietsname | [TABLE] { Tabellenname | Sichtname } }
TO { Benutzer [, …] | PUBLIC }
[WITH GRANT OPTION]
// erh. Rechte dürfen weitergegeben werden
Zugriffsrecht :=
{
|
|
|
|
|
SELECT
INSERT [(Spalte [, …] )]
UPDATE [(Spalte [, …] )]
DELETE
REFERENCES [(Spalte [, …] )]
USAGE
// Referenzieren der angegebenen Attribute
// Verwenden des angegebenen Gebiets
}
Datenbanken
-7-
Christian Silberbauer
SQL
3.3. Data Manipulation Language (DML)
3.3.1.
SELECT-Befehl
Syntax
Select-Hauptteil
[ { UNION | EXCEPT | INTERSECT } [ALL]
Select-Hauptteil
[{ UNION | EXCEPT | INTERSECT } [ALL] …] ]
[ORDER BY Ordnungsliste]
// Vereinigung, Differenz, Schnitt
// Sortierung
Select-Hauptteil :=
SELECT [ALL | DISTINCT] Spaltenauswahlliste
FROM
Tabellenliste
[WHERE
Bedingung]
[[GROUP BY Spaltenliste]
[HAVING
Bedingung]]
// Projektion
// Kreuzprodukt oder Join
// Restriktion
// Gruppierung
// Restriktion nach Gruppierung
Aggregatsfunktionen
Durchschnitt avg()
Anzahl
count()
Maximum
max()
Minimum
min()
Summe
sum()
// Besonderheit: count() gibt 0 zurück, wenn keine Sätze gefunden
// wurden. Alle anderen Aggregatsfunktionen geben in einem solchen
// Fall NULL zurück.
Funktionen, um NULL-Werte zu ersetzen
coalesce() SQL-Standard
nvl()
Oracle
nz()
Access
// Ermittelt eigentlich aus einer Liste den ersten Wert ungleich NULL
Operatoren in der WHERE-Klausel
Boolesche Operatoren
AND, OR, NOT
Vergleichsoperatoren
<, <=, >, >=, =, <>
Intervalloperator
[NOT] BETWEEN … AND …
Enthaltenoperator
[NOT] IN
Auswahloperatoren
ALL, ANY, SOME
Ähnlichkeitsoperator
[NOT] LIKE
Existenzoperator
EXISTS
Eindeutigkeitsoperator
UNIQUE
Nulloperator
IS [NOT] NULL
Datenbanken
// SOME und ANY sind Synonyme
-8-
Christian Silberbauer
SQL
Wildcards (unter Verwendung des Ähnlichkeitsoperators)
%
ersetzt beliebig viele Zeichen (* in Access)
_
ersetzt genau ein Zeichen (? in Access)
JOIN
Inner-Join
Tabelle1 [INNER] JOIN Tabelle2
ON Tabelle1.Feld = Tabelle2.Feld
Outer-Join
Tabelle1 {LEFT | RIGHT | FULL} [OUTER] JOIN Tabelle2
ON Tabelle1.Feld = Tabelle2.Feld
Natural-Join
Tabelle1 NATURAL INNER JOIN Tabelle2
Logische Abarbeitungsreihenfolge des Select-Befehls
1.
2.
3.
4.
5.
6.
Relationen mittels Kreuzprodukt oder JOIN verknüpfen
Restriktion mit Hilfe der WHERE-Bedingung
Projektion durchführen
Gruppieren gemäß der GROUP-BY-Klausel
Restriktion auf Ergebnis der Gruppierung mit HAVING-Klausel
Alle so erstellten Hauptteile des SELECT-Befehls mittels UNION, INTERSECT oder EXCEPT
miteinander verknüpfen
7. Ergebnisrelation anhand der ORDER-BY-Klausel sortieren
Beispiele
SELECT
SELECT
SELECT
SELECT
count(*) FROM tab
count(DISTINCT feld) FROM tab
* FROM tab WHERE feld = any(1000, 2000, 3000)
feld1 AS a, feld2 FROM tab ORDER BY 1 desc, a
Datenbanken
-9-
Christian Silberbauer
SQL
3.3.2.
INSERT-Befehl
Syntax
INSERT INTO { Tabellenname | Sichtname } [(Spaltenliste)]
{ VALUES (Auswahlliste) | Select-Befehl }
Beispiel
INSERT INTO Auftragsposten (Nr, Menge, Preis) VALUES (1, 10, 9.99)
3.3.3.
UPDATE-Befehl
Syntax
UPDATE { Tabellenname | Sichtname }
SET { Spalte = { Spaltenausdruck | NULL }} [,…]
[WHERE Bedingung]
Beispiel
UPDATE Auftragsposten SET Menge = Menge + 5
WHERE Nr = 1
3.3.4.
DELETE-Befehl
Syntax
DELETE FROM { Tabellenname | Sichtname }
[WHERE Bedingung]
Beispiel
DELETE FROM Auftragsposten WHERE Nr = 1
Datenbanken
- 10 -
Christian Silberbauer
SQL für Objektrelationale Datenbanken
4. SQL für Objektrelationale Datenbanken
4.1. CREATE TYPE – Syntax
CREATE TYPE Typname AS
{ VARRAY | VARRYING ARRAY } (Anzahl) OF Datentyp
CREATE TYPE Typname AS OBJECT
( Spalte Datentyp [, …],
[ { MEMBER { Prozedurname | Funktionsname } } [, …]])
CREATE TYPE Typname AS
MEMBER { Prozedurdeklaration | Funktionsdeklaration }; [, …]
CREATE TYPE Typname AS TABLE OF Datentyp
4.2. Funktionen für eingebettete Relationen
the()
Wandelt eingebettete Relation in Ausgaberelation um
multiset()
Fasst eingebettete Relationen zusammen
CAST-Operator
Typkonvertierung
4.3. Beispiele
CREATE TYPE T_Name AS
VARRAY(10) OF CHARACTER(30)
// Array anlegen
CREATE TYPE T_Adresse AS OBJECT
( Strasse
CHARACTER(30),
PLZ
CHARACTER(5),
ORT
CHARACTER(30) )
// Objekt anlegen
CREATE TYPE T_APos AS OBJECT
( Nr
INTEGER,
Anzahl
INTEGER,
Preis
NUMERIC(10,2) )
// Objekt anlegen
CREATE TYPE ET_APos AS TABLE OF TAPos
// Eingebettete Tabelle anlegen…
CREATE TABLE Auftrag
( Nr
INTEGER PRIMARY KEY,
Bearbeiter T_Name,
Lieferadr T_Adresse,
APos
ET_APos )
NESTED TABLE APos STORE AS ET_APos_TAB
// …Tabelle mit…
// …Array…
// …Objekt…
// …und eingebetteter Tabelle anlegen
CREATE VIEW VAuftrag(Nr, Lieferadr) AS
// View mit Objekt erzeugen
SELECT A.Nr, T_Adresse(A.Strasse, A.PLZ, A.Ort)
FROM AuftragAlt A
Datenbanken
- 11 -
Christian Silberbauer
SQL für Objektrelationale Datenbanken
INSERT INTO Auftrag
// Satz mit Objekten einfügen
( 1,
T_Name(‘Meier’, ‘Müller’),
T_Adresse(’Walhallaallee 26’, 93093, ’Donaustauf ’),
ET_APos(T_APos(1023, 5, 232.00), T_APos(3938, 1, 15.99)) )
INSERT INTO
the(SELECT APos FROM Auftrag WHERE Nr = 1)
VALUES(T_APos(8020, 2, 30.11))
// Einfügen in eingebetteter Tabelle
SELECT Nr, A.Lieferadr.Ort FROM Auftrag A
// Objektattribut auslesen
SELECT *
// Eingebettete Tabelle ausgeben
FROM the(SELECT APos FROM Auftrag WHERE Nr=1)
SELECT sum(P.Preis)
// Aggregatsf. auf eingebettete Tab.
FROM the(SELECT APos FROM Auftrag WHERE Nr=1) P
SELECT A1.Nr,
// Eingeb. Tabellen zusammenfassen
cast(multiset(
SELECT * FROM
the(SELECT APos FROM Auftrag A2 WHERE A2.Nr = A1.Nr) P
WHERE P.Preis > 100
) AS ET_APos)
FROM Auftrag A1
Datenbanken
- 12 -
Christian Silberbauer
PHP
5. PHP
5.1. Funktionen
5.1.1.
String-Funktionen
trim(string)
entfernt Leerzeichen am Anfang und am Ende
strlen(string)
gibt die Länge einer Zeichenkette zurück
strcmp(str1, str2)
strcasecmp(str1, str2)
vergleicht zwei Zeichenketten
vergleicht zwei Zeichenketten ohne Beachtung der Groß/Kleinschreibung
strtolower(string)
strtoupper(string)
wandelt in Kleinbuchstaben um
wandelt in Großbuchstaben um
5.1.2.
Datenbank-Funktionen (Oracle)
resource oci_connect (string username, string password, string db)
array
oci_error (resource source)
resource oci_parse (resource connection, string query)
oci_execute (resource stmt, int mode)
bool
int
string
oci_num_fields (resource statement)
oci_field_name (resource statement, int field)
array
object
int
int
// per default: numerisch und assoziativ
oci_fetch_array (resource statement)
oci_fetch_object (resource statement)
oci_fetch_all (resource statement, array &output)
// nicht bei select verwenden!
oci_num_rows (resource stmt)
bool
bool
oci_commit (resource connection)
oci_rollback (resource connection)
bool
bool
oci_free_statement (resource statement)
oci_close (resource connection)
Datenbanken
- 13 -
Christian Silberbauer
PHP
5.2. Beispielskripte
5.2.1.
Beispiel Datenbankzugriff
<html>
<head><title>Beispiel Datenbankzugriff (select und update)</title></head>
<body>
<?php
$conn = @oci_connect($_POST['Kennung'], $_POST['Passwort'], ”rfhs8012_ora9i”)
or die(”Verbindung zur Datenbank gescheitert. Abbruch”);
isset($_POST['Rabatt']) or die(”Rabatt wurde nicht definiert. Abbruch”);
isset($_POST['Limit']) or die(”Limit wurde nicht definiert. Abbruch”);
$sql = ”SELECT * FROM Auftragsposten WHERE Gesamtpreis > $_POST[Limit]”;
$stmt = oci_parse($conn, $sql);
@oci_execute($stmt, OCI_DEFAULT) or die(”Fehler beim Daten auslesen. Abbruch.”);
($rec = oci_fetch_array($stmt)) or die(”Keine Aufträge betroffen. Abbruch.”);
echo ”<table border=1>”;
do {
echo ”<tr>
<td>$rec[ANR]</td><td>$rec[TEILENR]</td>
</tr>”;
} while ($rec = oci_fetch_array($stmt));
echo ”</table>”;
$sql = ”UPDATE Auftragsposten
SET Gesamtpreis = Gesamtpreis * (100 - $_POST[Rabatt]) / 100
WHERE Gesamtpreis > $_POST[Limit]”;
$stmt = oci_parse($conn, $sql);
@oci_execute($stmt, OCI_DEFAULT) or die(”Fehler beim Daten aktualisieren. Abbruch.”);
oci_commit($conn);
oci_close($conn);
?>
</body>
</html>
Datenbanken
- 14 -
Christian Silberbauer
PHP
5.2.2.
Beispiel Deadlocks
<html>
<head><title>Beispiel Deadlocks (mit try-catch)</title></head>
<body>
<?php
try {
$conn = @oci_connect($_POST['Kennung'], $_POST['Passwort'], ”rfhs8012_ora9i”)
if (!$conn) {
$err = oci_error();
throw new Exception(”Fehler beim Connect ($err[message])”, $err['code']);
}
do {
try {
$sql = ”UPDATE Kunde SET Sperre = 0 WHERE Nr = 1”;
$stmt = oci_parse($conn, $sql);
@oci_execute($stmt, OCI_DEFAULT);
$err = oci_error($stmt);
if ($err['code']) throw new Exception (”Fehler beim Ausführen”, $err['code']);
$sql = ”UPDATE Kunde SET Sperre = 0 WHERE Nr = 2”;
$stmt = oci_parse($conn, $sql);
@oci_execute($stmt, OCI_DEFAULT);
$err = oci_error($stmt);
if ($err['code']) throw new Exception (”Fehler beim Ausführen”, $err['code']);
$ready = true;
}
catch (Exception $e) {
if ($e->getCode() == 60) { // Deadlock?
echo ”e->getMessage(), Zeile: $e->getLine(), Code: $e->getCode()<br>”;
echo ”Deadlock! Transaktion wird abgebrochen und neu gestartet.<br><br>”;
oci_rollback($conn);
$ready = false;
}
else throw $e;
}
} while (!$ready);
oci_commit($conn);
oci_close($conn);
}
catch (Exception $e) {
echo ”e->getMessage(), Zeile: $e->getLine(), Code: $e->getCode()<br><br>”;
oci_rollback($conn);
oci_close($conn);
}
?>
</body>
</html>
Datenbanken
- 15 -
Christian Silberbauer
Regeln zum Datenbankentwurf
6. Regeln zum Datenbankentwurf
6.1. Primärschlüssel
-
Kein NOT NULL und kein UNIQUE beim Primärschlüssel definieren. Das ist automatisch der Fall.
6.2. Alternative Schlüssel
-
UNIQUE explizit setzen
Bei zusammengesetztem alternativen Schlüssel jedes Attribut mit NOT NULL kennzeichnen
6.3. Pflichtfelder
-
NOT NULL setzen
6.4. Fremdschlüssel
6.4.1.
-
Generell ON UPDATE CASCADE verwenden
6.4.2.
-
Bei Schwachen Relationen
Bei dem einzigen (!) Fremdschlüssel sind NULL-Werte nicht erlaubt
Der Fremdschlüssel hat die Eigenschaft ON DELETE CASCADE
6.4.4.
-
Bei Beziehungsrelationen
Bei Fremdschlüsseln sind NULL-Werte nicht erlaubt
Beide Fremdschlüssel haben die Eigenschaft ON DELETE NO ACTION oder ON DELETE
CASCADE
6.4.3.
-
Allgemein
Bei Subtyp-Relationen
Sie sind i.d.R. Schwache Relationen
Der Fremdschlüssel ist beim Subtyp zu setzen
6.5. „Kringel“ setzen…
-
…bei Subtyp-Relation beim Subtyp
…bei 1:n-Beziehungen immer beim „n“ (ist technisch bedingt)
…bei 1:n-Beziehungen bei der „1“, wenn der Fremdschlüssel NULL sein kann. Dann und nur dann
darf aber NOT NULL bei Fremdschlüsseln nicht gesetzt sein.
Datenbanken
- 16 -
Christian Silberbauer
Concurrency und Recovery
7. Concurrency und Recovery
7.1. Concurrency
Gleichzeitiger Zugriff auf Daten einer Datenbank im Mehrbenutzerbetrieb.
7.2. Recovery
Rekonstruktion einer Datenbank im Fehlerfall.
7.3. Logdatei
Eine Logdatei speichert alle Datenbankänderungen zusätzlich auf einer eigenen Festplatte. Die
Speicherung in dieser Datei erfolgt im Streaming-Modus (sequentiell).
Durch die doppelte Datenhaltung ist das Datenbanksystem immun gegenüber Plattencrashs und
gewährt dennoch hohe Performance, da durch das sequentielle Schreiben kaum Positionierzeiten auf
der Platte anfallen.
7.4. Hot Spots
Aus dem Datenbankpuffer werden geänderte Daten vom Datenbanksystem auf die Festplatte zurück
geschrieben, wenn diese länger nicht benötigt werden. Auf manche Daten wird aber laufend lesend
und schreibend zugegriffen, sodass die Änderungen auf Platte auf diese Art nicht stattfinden. Die
entsprechenden Blöcke im Datenbankpuffer nennt man Hot Spots.
7.5. Checkpoints
Regelmäßige Zeitpunkte, in denen der Datenbankpuffer gelehrt wird.
Dadurch wird zwar der Datenbankbetrieb durch den hohen Ausgabeverkehr merklich gebremst,
allerdings beseitigen sie Hot Spots und beschleunigen eine eventuelle Recovery.
Das optimale regelmäßige Zeitintervall zwischen zwei Checkpoints zu finden ist Erfahrungssache. Es
ist von der jeweiligen Anwendung abhängig. Zu häufige Checkpoints erhöhen den Ausgabeverkehr,
zu seltene Checkpoints führen zu enormen Stoßbelastungen, da der Aufwand pro Checkpoint sehr
hoch ist.
7.6. Transaktionssteuerung
BEGIN TRANSACTION
Startet eine Transaktion
COMMIT WORK
Schließt eine Transaktion ab
ROLLBACK WORK
Macht die bisherigen Änderungen einer
offenen Transaktion rückgängig
7.7. Zwei-Phasen-Commit
Bei datenbankübergreifenden Transaktionen, also bei Transaktionen, an denen mehr als eine
Datenbank beteiligt ist, ist ein Protokoll namens „Zwei-Phasen-Commit“ notwendig, um primär die
Konsistenz der Datenbank zu gewährleisten.
Datenbanken
- 17 -
Christian Silberbauer
Concurrency und Recovery
Dabei wird eine übergreifende Transaktion zunächst in lokalen Transaktionen abgearbeitet.
Anschließend erfolgt eine Meldung über die erfolgreiche oder auch nicht erfolgreiche lokale
Abarbeitung an einen sog. Koordinator (Phase 1). Wenn alle lokalen Transaktionen erfolgreich
abgeschlossen werden konnten, setzt der Koordinator einen globalen Commit (Phase 2). Die
datenbankübergreifende Transaktion ist damit abgeschlossen. Andernfalls wird ein Rollback initiiert
und alle beteiligten lokalen Transaktionen werden rückgängig gemacht.
7.8. Sperrmodi
Share-Lock
erlaubt weitere Share-Locks, weist ExclusivLocks aber zurück (bei lesendem Zugriff)
Exklusiv-Lock weist alle weiteren Lockanforderungen zurück
(bei schreibendem Zugriff)
7.9. Deadlock
Eine Verklemmung, bei der zwei oder mehrere Transaktionen gegenseitig auf die Freigabe eines oder
mehrerer Locks warten, heißt Deadlock.
7.10.Isolation Level
7.10.1. Level setzen
SET TRANSACTION ISOLATION LEVEL Level
7.10.2. Definierte Level
Beliebiges Lesen
möglich
Lesen nicht
wiederholbar
Phantom möglich
ja
ja
ja
Read Committed
nein
ja
ja
Repeatable Read
nein
nein
ja
Serializable
nein
nein
nein
Level
Read Uncommitted
Beliebiges Lesen:
Hier sind Lesezugriffe jederzeit erlaubt
Nicht wiederholbares
Lesen:
Innerhalb einer Transaktion kann wiederholtes Lesen andere
Ergebnisse liefern.
Phantom-Effekt:
Beim wiederholten Lesen können plötzlich zusätzliche Zeilen
auftreten (-> Phantom)
Datenbanken
- 18 -
Christian Silberbauer