Datenbank 笔记

Datenbank 笔记

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

  • σ Selektion
  • π Projektion (会自动去重)
  • × Kreuzprodukt
  • Join (Verbund)
  • ρ Umbenennung
  • Mengendifferenz
  • ÷ Division
  • Vereinigung
  • Mengendurchschnitt
  • Semi-Join (linkes Argument wird gefiltert) (筛选左表,仅保留在右表中有匹配记录的左表行。)
  • Semi-Join (rechtes Argument wird gefiltert)
  • 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.: {ppProfessorenp.Rang=C4}

Der Domänenkalkül

{[v1,v2,,vn]P(v1,,vn)}

Bsp.: [m,n]s([m,n,s]Studentenv,p,g([m,v,p,g]prüfena,r,b([p,a,r,b]Professorena='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:

(tR (P(t)))(¬(tR (¬P(t))))

(RT)(¬RT)

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}

假设 α,βR

αβ :
r,sR:r.α=s.αr.β=s.β

(可以理解成函数的 rechtseindeutig)

α Super-Schlüssel :

αR

( 生成元)

β voll funktional abhängig von α (αβ) :

  • αβ

  • α kann nicht mehr verkleinert werden

    (Aα:¬((α{A})β))

α Kandidaten-Schlüssel:

αR

( 最小生成元)

Armstrong-Axiome

FD-Hülle einer Attributmenge

Kanonische Überdeckung α+

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

Berechnung:

  1. 消除右部冗余属性 (ABC, AB 时仅保留ABAC)
  2. 消除左部冗余属性
  3. 删除冗余的函数依赖 (ABBCAC 时仅保留ABBC)

R = R1 R2

2 Korrektheitskriterien für die Zerlegung von Relationenschemata:

  1. Verlustlosigkeit
  2. Abhängigkeitserhaltung

verlustlose Zerlegung:

( R = R1 R2 )

Hinreichende Bedingung für die Verlustlosigkeit einer Zerlegung:
(R1R2)R1 oder (R1R2)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 AR 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} {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} {C}。

Dritte Normalform (3NF)

wenn für jede für R geltende funktionale Abhängigkeit der Form αB mit BR und mindestens eine von drei Bedingungen gilt:

  • Bα, d.h., die FD ist trivial
  • Das Attribut B ist in einem Kandidatenschlüssel von R enthalten – also B ist prim
  • α 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} {C}, {A,B} {D} voll,所以满足2NF;

有{C} {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 αβF (FDs) und mindestens eine von drei Bedingungen gilt:

  • Bα, d.h., die FD ist trivial
  • α 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 CD

C 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):

αβ

2行对上,2行交叉对应

Jede FD is auch eine MVD:

αβαβ

Eine MVD αβ ist trivial iff:

  • βα oder
  • β=Rα

Vierte Normalform (4NF):

falls für jede MVD αβ eine der folgenden gilt:

  • Die MVD ist trivial oder
  • α 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 Relationale Algebra

Äquivalenzerhaltende Transformationsregeln

一共12条

Dependent Join

Physische Optimierung

Nested Loop Join

foreach rR :

​ foreach sS :

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

O(N*M)

Block-Nested Loop Algorithmus

foreach Block BR

​ foreach sS

​ foreach rB

​ if s.b = r.a then Res:= Res (r 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 aA: 计算h_i(a) forall 1ik

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 redo

    • Noch nicht abgeschlossene TAs zurücksetzten

      R3-Recovery undo

  3. Fehler mit Hintergrundspeicherverlust

    • R4-Recovery

¬ steal:未提交的事务修改的页面不会被替换。

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

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

¬ force:修改的数据仍可保留在缓存中,提高性能。

Auswirkung auf Recovery:

force ¬ force
¬ 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 der zugehörige Serialisierbarkeitsgraph SG(H) azyklisch ist.

Ti liest von Tj 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

__EOF__

本文作者笔墨绘星河
本文链接https://www.cnblogs.com/bimohuixinghe/p/18727565.html
关于博主:评论和私信会在第一时间回复。或者直接私信我。
版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!
声援博主:如果您觉得文章对您有帮助,可以点击文章右下角推荐一下。您的鼓励是博主的最大动力!
posted @   笔墨绘星河_Archer  阅读(4)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 单线程的Redis速度为什么快?
· 展开说说关于C#中ORM框架的用法!
· Pantheons:用 TypeScript 打造主流大模型对话的一站式集成库
· SQL Server 2025 AI相关能力初探
· 为什么 退出登录 或 修改密码 无法使 token 失效
点击右上角即可分享
微信分享提示