Datenbank 笔记

Datenbank 笔记

目录

• Datenbank 笔记
  • 1. Datenbankentwurf / ER-Modellierung
  • 2. Das Relationale Modell
    • • Die Relationale Algebra
      • Der Relationenkalkül (relationaler Tupelkalkül)
      • Der Domänenkalkül
  • 3. SQL
    • • Datendefinition (DDL)
      • Datenmanipulation
      • Anfrage
  • 4. Datenintegrität
  • 5. Relationale Entwurfstheorie
    • • Funktionale Abhängigkeiten
      • Normalformen
  • 6. Physische Datenorganisation
    • Speicherhierarchie
    • RAID
    • Indexstrukturen
      • B-Bäume
      • B+-Bäume
      • Erweiterbares Hashing
      • R-Baum
  • 7. Anfragebearbeitung
    • Logische Optimierung
    • Physische Optimierung
      • Nested Loop Join
      • Block-Nested Loop Algorithmus
      • Index-Join
      • Merge-Join
      • Hash-Join
      • Join mit Hashfilter (Bloom-Filter)
      • Externes Sortieren
  • 8. Transaktionsverwaltung
  • 9. Fehlerbehandlung (Recovery)
    • Fehlerklassifikation
    • Einbringungsstrategie（数据提交策略）:
    • Log-Einträge:
    • Protokollierung:
    • Wiederanlauf:
  • 10. Mehrbenutzersynchronisation
    • Historie
    • Sperrbasierte Synchronisation （锁）
    • Zeitstempel-basierende Synchronisation
    • Optimistische Synchronisation
    • Transaktionsverwaltung in SQL92

1. Datenbankentwurf / ER-Modellierung

Entity-relationship diagram

Funktionalitäten:

1:1, 1:N, N:1, N:M

Min-Max: (min,max)

Multiplizität

2. Das Relationale Modell

Schlüssel, Primärschlüssel

Relationen mit gleichem Schlüssel kann man zusammenfassen.

Anomalie: 异常

Die Relationale Algebra

• \(\sigma\) Selektion
• \(\pi\) Projektion (会自动去重)
• \(\times\) Kreuzprodukt
• \(\bowtie\) Join (Verbund)
• \(\rho\) Umbenennung
• \(-\) Mengendifferenz
• \(\div\) Division
• \(\cup\) Vereinigung
• \(\cap\) Mengendurchschnitt
• \(\ltimes\) Semi-Join (linkes Argument wird gefiltert) (筛选左表，仅保留在右表中有匹配记录的左表行。)
• \(\rtimes\) Semi-Join (rechtes Argument wird gefiltert)
• \(\triangleright\) Anti-Semi-join
• ⟕ linker äußerer Join (会保留左表中的所有记录，即使在右表中没有匹配的记录时，右表对应的位置会以 NULL 填充。)
• ⟖ rechter äußerer Join
• ⟗ (voller) äußerer Join

Der Relationenkalkül (relationaler Tupelkalkül)

\(\{t | P(t) \}\)

Bsp.: \(\{ p \mid p \in \text{Professoren} \land p.\text{Rang} = 'C4' \}\)

Der Domänenkalkül

\(\{ [v_1, v_2, \dots, v_n] \mid P(v_1, \dots, v_n) \}\)

Bsp.: $ { [m, n] \mid \exists s ([m, n, s] \in \text{Studenten} \land \exists v, p, g ([m, v, p, g] \in \text{prüfen} \land \exists a, r, b ([p, a, r, b] \in \text{Professoren} \land a = \text{'Curie'}))) } $

Diese 3 Sprachen sind gleich mächtig

3. SQL

standadisierte

-Datendefinitions (DDL)-

-Datenmanipulations (DML)-

-Anfrage (Query)-Sprache

Datendefinition (DDL)

Datentypen:

• character(n), char(n)

• character varying(n), varchar(n)

• numeric(p,s), integer, decimal

  p（precision，精度）：数值的总位数（包含小数点前后的所有数字）。

  s（scale，小数位数）：小数部分的位数（小数点后的位数）。

• blob oder raw – für sehr große binäre Daten

• clob – für sehr große String-Attribute

• date – für Datumsangaben

• xml – für XML-Dokumente

create table Professoren (
    PersNr integer not null,
    Name varchar(30) not null,
    Rang character(2));

Datenmanipulation

Einfügen von Tupeln

insert into hören
select MatrNr, VorlNr
from Studenten, Vorlesungen
where Titel = 'Logik';

insert into Studenten (MatrNr, Name)
values (28121, 'Archimedes');

Löschen von Tupeln

delete Studenten
where Semester > 13;

Verändern von Tupeln

update Studenten
set Semester = Semester + 1;

Anfrage

select (distinct)  as
from
where    and 

Sortieren:

select PersNr, Name, Rang
from Professoren
order by Rang desc, Name asc;

Mengenoperation:

union, intersect, minus

select
from
where (not) exists (select from where)

where not in ()

比较：

(where) value >= ALL(子查询)

Aggregationsfunktion:

avg, max, min, count, sum

Gruppierung:

select
from
(where)
group by 

Alle in der select-Klausel aufgeführten Attribute - außer den aggregierten - auch in der group by-Klausel aufgeführt werden.

比如说下面这里的gelesenVon, Name：

select gelesenVon, Name, sum(SWS)
from Vorlesungen, Professoren
where gelesenVon = PersNr and Rang = 'C4'
group by gelesenVon, Name
		having avg(SWS) >=3;

Casting:

cast(expression AS target_data_type)
cast(h.AnzProVorl as decimal(6,2))

Allquantor und Implikation:

\[( \forall t \in R \ (P(t)) ) \Leftrightarrow ( \neg (\exists t \in R \ (\neg P(t))) ) \]

\[(R \Rightarrow T) \Leftrightarrow (\neg R \lor T) \]

Auswertung bei NULL-Werten

and, between, in (1,2,3,4)

like mit Platzhalter:

"%" (für beliebig biele Zeichen)

"_" (für genau ein Zeichen)

case

left/right/full outer join

Rekursion

Vorgänger des „Wiener Kreises“ der Tiefe n:

select v1.Vorgänger
from voraussetzen v1,
     ...
     voraussetzen vn_minus_1,
     voraussetzen vn,
     Vorlesungen v
where v1.Nachfolger = v2.Vorgänger and
      ...
      vn_minus_1.Nachfolger = vn.Vorgänger and
      vn.Nachfolger = v.VorlNr and
      v.Titel = 'Der Wiener Kreis';

Mit Rekursion:

with recursive TransVorl (Vorg, Nachf) as (
    -- 递归的基础查询（初始层级）
    select Vorgänger, Nachfolger from voraussetzen
    union all
    -- 递归部分（从前一层继续查找更深的层级）
    select t.Vorg, v.Nachfolger
    from TransVorl t, voraussetzen v
    where t.Nachf = v.Vorgänger
)

select Titel 
from Vorlesungen 
where VorlNr in (
    select Vorg 
    from TransVorl 
    where Nachf in (
        select VorlNr 
        from Vorlesungen 
        where Titel = 'Der Wiener Kreis'
    )
);

4. Datenintegrität

Integritätsbedingungen:

-Schlüssel

-Beziehungskardinalitäten

-Attributdomänen

-Inklusion bei Generalisierung

Referentielle Integrität

Fremdschlüssel

Änderung von referenzierten Daten:

1. Default 会拒绝执行主键的更改操作
2. cascade (Kaskadieren): 级联，外键会随着主键的更改一起更改
3. set NULL

Statische Integritätsbedingungen:

create table Studenten
(Semester integer check Semester between 1 and 13)

create table Prof
(Rang character(2) check(Rang in ('C2','C3','C4')))

Konsistenzbedingung:

create table prüfen
(MatrNr ···
VorNr ···
Note ···
primary key (MatrNr,VorNr))
constraint VorherHören
	check (···)

Trigger:

-- 1. 
create trigger keine Degradierung
-- 2.
before update on Professoren
for each row
-- 3.
when (old.Rang is not null)
begin
-- 4.
    if :old.Rang = 'C3' and :new.Rang = 'C2' then
        :new.Rang := 'C3';
    end if;
    
    if :old.Rang = 'C4' then
        :new.Rang := 'C4';
    end if;
    
    if :new.Rang is null then
        :new.Rang := :old.Rang;
    end if;
end;

1. create trigger Anweisung, gefolgt von einem Namen,
2. der Definition des Auslösers, in diesem Fall bevor eine Änderungsoperation (before update on) auf einer Zeile (for each row) der Tabelle Professoren ausgeführt werden kann,
3. einer einschränkenden Bedingung (when) und
4. einer Prozedurdefinition in der Oracle-proprietären Syntax.

Temporale Daten

5. Relationale Entwurfstheorie

Funktionale Abhängigkeiten

Functional Dependency (FD)

A	B	C	D
a4	b2	c4	d3
a1	b1	c1	d1
a1	b1	c1	d2
a2	b2	c3	d2
a3	b2	c4	d3

R:= {A,B,C,D}

假设 \(\alpha, \beta \subseteq R\)

\(\alpha \rightarrow \beta\) :
\(\forall r,s \in R: r.\alpha = s.\alpha \Rightarrow r.\beta = s.\beta\)

(可以理解成函数的 rechtseindeutig)

\(\alpha\) Super-Schlüssel :

\(\alpha \rightarrow R\)

(\(\approx\) 生成元)

\(\beta\) voll funktional abhängig von \(\alpha\) (\(\alpha \rightarrow^\cdot \beta\)) :

• \(\alpha \rightarrow \beta\)

• \(\alpha\) kann nicht mehr verkleinert werden

  (\(\forall A \in \alpha: \neg((\alpha - \{A\}) \rightarrow \beta)\))

\(\alpha\) Kandidaten-Schlüssel:

\(\alpha \rightarrow^\cdot R\)

(\(\approx\) 最小生成元)

Armstrong-Axiome

FD-Hülle einer Attributmenge

Kanonische Überdeckung \(\alpha^+\)

没有冗余的属性(Attribute)和依赖(Abhängigkeit)

Berechnung:

1. 消除右部冗余属性 (\(A \rightarrow BC\), \(A \rightarrow B\) 时仅保留\(A \rightarrow B\) 和 \(A \rightarrow C\))
2. 消除左部冗余属性
3. 删除冗余的函数依赖 (\(A \rightarrow B\)，\(B \rightarrow C\)，\(A \rightarrow C\) 时仅保留\(A \rightarrow B\) 和 \(B \rightarrow C\))

R = R1 \(\cup\) R2

2 Korrektheitskriterien für die Zerlegung von Relationenschemata:

1. Verlustlosigkeit
2. Abhängigkeitserhaltung

verlustlose Zerlegung:

(\(\approx\) R = R1 \(\bowtie\) R2 )

Hinreichende Bedingung für die Verlustlosigkeit einer Zerlegung:
\((R1 \cap R2) \rightarrow R1\) oder \((R1 \cap R2) \rightarrow R2\)

Normalformen

Erste Normalform (1NF):

Nur atomare Domäne.

都要是singleton，不能出现集合或者重复行

反例：

Vater	Mutter	Kinder
Johann	Martha
Johann	Maria
Heinz	Martha

正例：

Vater	Mutter	Kind
Johann	Martha	Else
Johann	Martha	Lucie
Johann	Maria	Theo
Johann	Maria	Josef
Heinz	Martha	Cleo

Zweite Normalform (2NF)：

falls jedes Nicht(kandidat)schlüssel-Attribut \(A \in R\) voll funktional abhängig ist von jedem Kandidatenschlüssel der Relation.

假设R:= {A,B,C,D}的Kandidaten-Schlüssel是{A,B},那么需要满足C,D sind voll funktional abhängig von {A,B}。

只要出现类似{A} \(\rightarrow\) {C}之类的就不行了。

可以理解成不能有多余列。

Remark: 如果Kandidaten-Schlüssel只有一个元素，那么一定满足2NF。

反例（1非2）：

A	B	C
x	a	1
x	b	1
y	c	2

{A,B}为Kandidaten-Schlüssel。有{A} \(\rightarrow\) {C}。

Dritte Normalform (3NF)：

wenn für jede für \(R\) geltende funktionale Abhängigkeit der Form \(\alpha \rightarrow B\) mit \(B \in R\) und mindestens eine von drei Bedingungen gilt:

• \(B \subseteq \alpha\), d.h., die FD ist trivial
• Das Attribut \(B\) ist in einem Kandidatenschlüssel von \(R\) enthalten – also \(B\) ist prim
• \(\alpha\) ist Superschlüssel von \(R\)

反例（2非3）：

A	B	C	D
x	a	1	n
x	b	2	m
y	c	2	m

满足{A,B} \(\rightarrow\) {C}， {A,B} \(\rightarrow\) {D} voll，所以满足2NF；

有{C} \(\rightarrow\) {D}，所以不满足3NF。

Synthesealgorighmus:

zerlegen R in R1,...,Rn, sodass:

1. R1,...,Rn verlustlos
2. R1,...,Rn abhägigkeitserhaltend
3. Alle R1,...,Rn in 3NF

Boyce-Codd-Normalform (BCNF)：

wenn für jede für \(R\) geltende funktionale Abhängigkeit der Form \(\alpha \rightarrow \beta \in F\) (FDs) und mindestens eine von drei Bedingungen gilt:

• \(B \subseteq \alpha\), d.h., die FD ist trivial
• \(\alpha\) ist Superschlüssel von \(R\)

反例（3非BCNF）：

A	B	C	D
x	a	1	1
x	b	2	1
y	a	3	2
y	b	2	1

有

AB \(\rightarrow\) CD

C \(\rightarrow\) B

因为B属于Kandidatschlüssel，所以满足3NF，但是不满足BCNF。

Man kann jede Relation verlustlos in BCNF-Relationen zerlegen, aber nicht unbedingt abhägigkeitserhaltend.

Dekompositions-Algorithmus

Mehrwertige Abhägigkeit (Multivalued Dependency, MVD):

\(\alpha \twoheadrightarrow \beta\)

2行对上，2行交叉对应

Jede FD is auch eine MVD:

\(\alpha \rightarrow \beta \Rightarrow \alpha \twoheadrightarrow \beta\)

Eine MVD \(\alpha \twoheadrightarrow \beta\) ist trivial iff:

• \(\beta \subseteq \alpha\) oder
• \(\beta = R- \alpha\)

Vierte Normalform (4NF):

falls für jede MVD \(\alpha \twoheadrightarrow \beta\) eine der folgenden gilt:

• Die MVD ist trivial oder
• \(\alpha\) ist Superschlüssel von R

6. Physische Datenorganisation

Speicherhierarchie

Register

Cache

Hauptspeicher

Plattenspeicher

Archivspeicher

RAID

Redundant Arrays of Independent Disks

MTTF, MTTR, MTTDL

RAID 0: Striping

RAID 1: Spiegelung

RAID 0+1(10): Striping und Spiegelung

RAID 2: Striping auf Bit-Ebene

RAID 3: Striping auf Bit-Ebene mit Paritätsinfo

RAID 4: Striping von Blöcken

RAID 5: Striping von Blöcken, Verteilung der Paritätsblöcke

Indexstrukturen

B-Bäume

B+-Bäume

Erweiterbares Hashing

Dynamisches Wachsen möglich

Beispiel: gespiegelte binäre PersNr

• h(004) = 00100000... (4=0...0100)
• h(006) = 01100000... (6=0...0110)
• h(048) = 00001100... (48=0...0110000)

Globale Tiefe

Lokale Tiefe

R-Baum

mehrdimensionalen Zugriffsstrukturen

7. Anfragebearbeitung

Logische Optimierung

Kanonische Übersetzung:

SQL \(\rightarrow\) Relationale Algebra

Äquivalenzerhaltende Transformationsregeln

一共12条

Dependent Join

Physische Optimierung

Nested Loop Join

foreach \(r \in R\) :

​ foreach \(s \in S\) :

​ if s.a=r.a then Res:= Res \(\cup\) (r \(\circ\) s)

O(N*M)

Block-Nested Loop Algorithmus

foreach Block \(B \subseteq R\)

​ foreach \(s \in S\)

​ foreach \(r \in B\)

​ if s.b = r.a then Res:= Res \(\cup\) (r \(\circ\) s)

O(N * M/B)

(M/B: Anzahl der Blöcke)

Index-Join

Voraussetzung: 其中一个表是sortiert的且有B-Baum

O(N * log(M))

Merge-Join

Voraus.: 2个表都是sortiert的

O(N+M)

Hash-Join

选定一个哈希函数h()，计算R.A的值，分配到hash buckets里，然后再计算S.B的值，在哈希桶里匹配。

不需要预先排序

Partitionieren und Hashing的话需要2个哈希函数。

Join mit Hashfilter (Bloom-Filter)

需要很多个（k个）不同的哈希函数
forall \(a \in A\): 计算h_i(a) forall \(1 \leq i \leq k\)

0覆盖1。

会出现False Positive，但是不会出现False Negative

Externes Sortieren

Selektivität

Dynamische Programmierung:

1. Phase: Zugriffspläne ermitteln
2. Phase: Join-Pläne ermittel (2-fach, ..., n-fach)
3. Phase: Finalisierung

8. Transaktionsverwaltung

Begin of Transaction (BOT)：转账开始的标志

read：读存款

write：写入（修改存款，出账入账）

commit：转账结束，所有操作festschreiben

abort：取消转账，所有状态复原

define savepoint

backup transaction: Auf den jüngsten Sicherungspunkt zurücksetzen.

commit work

rollback work: Alle Änderungen sollen zurückgesetzt werden.

ACID:

• Atomicity (Atomarität)：原子性。

  Alles oder Nichts.

• Consistency：一致性。

• Isolation：隔离性。

• Durability (Dauerhaftigkeit)：持久性。

  所有更改都必须永久存储。Änderungen erfolgreicher Transaktionen dürfen nie verloren gehen.

9. Fehlerbehandlung (Recovery)

Fehlerklassifikation

1. Lokaler Fhler in einer noch nicht festgeschriebenen Transaktion

   • Wirkung zurücksetzten
     R1-Recovery

2. Fehler mit Hauptspeicherverlust

   • Abgeschlossene TAs erhalten bleiben

     R2-Recovery \(\rightarrow\) redo

   • Noch nicht abgeschlossene TAs zurücksetzten

     R3-Recovery \(\rightarrow\) undo

3. Fehler mit Hintergrundspeicherverlust

   • R4-Recovery

\(\neg\) steal：未提交的事务修改的页面不会被替换。

steal：允许替换缓存中的任何非固定页面。

force：事务提交时立即将数据写入磁盘。

\(\neg\) force：修改的数据仍可保留在缓存中，提高性能。

Auswirkung auf Recovery:

	force	\(\neg\) force
\(\neg\) steal	- Kein Undo - Kein Redo	- Kein Undo - Redo
steal	- Undo - Kein Redo	- Undo - Redo

Einbringungsstrategie（数据提交策略）:

1. Update in Place：直接覆盖
2. Twin-Block-Verfahren：复制整个数据块
3. Schattenspeicherkonzept：复制修改的页面

Log-Einträge:

• LSN (Log Sequence Number)：日志序列号
• Transaktionskennung(TA_ID)
• PageID
• Redo：纪录当前操作（比如说+=50）
• Undo：当前操作的逆向（比如-=50）
• PrevLSN：上一个日志记录的指针

[LSN, TransaktionsID, PageID, Redo, Undo, PrevLSN]

（中括号）

Protokollierung:

• Physische Protokollierung
  • before-image：修改前的Zustand
  • after-image
• Logische Protokollierung
  • Undo-Code
  • Redo-Code

Log-Information会记录2次以上：

1. Log-Datei

   R1,R2,R3

2. Log-Archiv

   R4

WAL-Prinzip (Write Ahead Log)：

1. Commit前，确保所有相关的日志记录已写入日志文件。

2. modifizierte Seite auslagern前，确保相关日志记录已写入日志存档（Log-Archiv）。

Winner：在崩溃前已经完成，需要Redo

Loser：在崩溃时仍然处于未提交状态，需要Undo

Wiederanlauf:

1. Analyse

   • Log-Datei analysieren
   • Winner-Menge ermitteln
   • Loser-Menge ermitteln

2. Redo

3. Undo

CLR: Compensating Log Record

用于记录撤销的操作。（给undo的）

简单来说就是为了预防恢复崩溃时发生的其他崩溃。

CLR-Einträge:

• LSN (Log Sequence Number)：日志序列号
• Transaktionskennung(TA_ID)
• PageID
• Redo：
• PrevLSN：上一个日志记录的指针
• UndoNxtLSN：Verweis auf die nächste rückgängig zu machende Änderung

<LSN, TransaktionsID, PageID, Redo, PrevLSN, UndoNxtLSN>

（尖括号）

注意：CLR没有undo信息。

3种Sicherungspunkte(-Qualitäten)：

1. transaktionskonsistent （事务一致）

   所有已提交的事务都被完全存储，未提交的都没有被写入磁盘。（只在所有活跃事务完成后进行检查点记录）

2. aktionskonsistent （操作一致）

   可能包含未提交的事务，但是所有操作都是完整的。

3. unscharf (fuzzy)

   修改的页面不会立即写入磁盘，只记录“脏页”的信息，而不是数据本身

   DirtyPages (Menge der modifizierten Seiten, 尚未写入磁盘), MinDirtyPageLSN, MinLSN

R4-Recovery

10. Mehrbenutzersynchronisation

Fehler (bei unkontroliertem Mehrbenutzerbetrieb):

1. Lost Update:

   Verlorengegangene Änderungen

2. Dirty Read:

   Abhängigkeit von nicht freigegebenen Änderungen

3. Phantomproblem

Historie

Seiralisierbarkeit

2 Historien äquivalent: wenn sie die Konfliktoperationen der nicht abgebrochenen Transaktionen in derselben Reihenfolge ausführen.

SR:

Eine Historie ist serialisierbar wenn sie äquivalent zu einer seriellen Historie Hs ist.

Eine Historie H serialisierbar \(\Longleftrightarrow\) der zugehörige Serialisierbarkeitsgraph SG(H) azyklisch ist.

\(T_i\) liest von \(T_j\) in der Historie H

RC: rücksetzbare Historie

kaskadierendes Rücksetzen (Cascading Rollback)：

假如T2读取了T1里被修改过但是还未被提交（commit）的数据，那么当T1 abort的时候，也需要abort T2。

ACA: Historien ohne kaskadierendes Rücksetzen (avoiding cascading abort)

ST: Strikte Historien

想要对一个被修改过的对象进行操作前，必须要确保其已经被commit或者abort了。

Sperrbasierte Synchronisation （锁）

Sperrmodi:

• S (shared, read lock)

  允许多个事务（Transaktion）同时读取数据，但不能进行写操作。

• X (exclusive, write lock)

  允许事务对数据进行读取和写入，但其他事务不能同时访问该数据。

Verträglichkeitsmatrix / Kompatibilitätsmatrix:

	NL	S	X
S	✅（兼容）	✅	❌
X	✅	❌（不兼容）	❌

Zwei-Phasen-Sperrprotokoll:

1. Wachstumsphase：只获取锁
2. Schrumpfphase：只释放锁

Strenges Zwei-Phasen-Sperrprotokoll:

所有锁直到事务提交（commit）或回滚（abort）后才释放，避免级联回滚。

Verklemmungen (Deadlocks)

Erkennung:

1. Wartegraph

Vermeidung:

1. Preclaiming
2. durch Zeitstempel
   1. Wound-Wait Strategie
   2. Wait-Die Strategie

Multi-Granularity Locking (MGL)

Phantomproblem:

Zugriffsweg sperren

Zeitstempel-basierende Synchronisation

readTS(A)：上次读取 A的时间戳。

writeTS(A)：上次写入 A 的时间戳。

Synchronisationsverfahren:

1. 当 Ti 试图读取数据 A (ri(A))：
   • 若 TS(Ti)<writeTS(A)，则 Ti必须被回滚（因为 AAA 可能已被更新）。
   • 否则，Ti可以继续读取，并更新 readTS(A) = max(TS(Ti), readTS(A))。
2. 当 Ti 试图写入数据 A (wi(A))：
   • 若 TS(Ti)<readTS(A)，说明在 Ti之前已经有其他事务读取了 A，则 Ti 必须回滚。
   • 若 TS(Ti)<writeTS(A)，说明 Ti 试图覆盖一个更新的值，必须回滚。
   • 否则，Ti可以写入，并更新 writeTS(A) = TS(Ti)。

Optimistische Synchronisation

1. Lesephase
2. Validierungsphase（验证）
3. Schreibphase

Snapshot Isolation

Synchronisation von Indexstrukturen:

zu aufwendig, redundante

Transaktionsverwaltung in SQL92

isolation level:

1. read uncommited
2. read commited
3. repeatable read
4. serializable