【資料庫】總結

第一章

概念模型（ER圖）

橢圓是屬性
矩形是物體
菱形為聯系

概念模型到關系模型轉化

主碼下面要加下劃線

1-m聯系

物體型->關系模式
將1端主碼連同聯系屬性放入n端屬性中

1-1聯系

物體型->關系模式
任意一端主碼連同聯系屬性放入到另外一端物體當中

n-m聯系

物體型->關系模式
產生一個新關系，關系名為聯系名，將兩端物體的主碼連同關系屬性

資料庫系統的三級模式結構

概念

資料庫的三級模式結構是由外模式、模式、內模式三級構成，
模式，也成邏輯模式，是資料庫中全體資料的邏輯結構和特征的描述，是所有用戶的公共資料視圖，一個資料庫只有一個模式，
外模式，也稱子模式或用戶模式，它是資料庫用戶能夠看見或使用的區域資料的邏輯結構和特征的描述，是資料庫用戶的資料視圖，是與某一應用有關的資料的邏輯表示，外模式通常是模式的子集，一個資料庫可以有多個外模式，
內模式，也稱存盤模式，一個資料庫只有一個內模式，它是資料物理結構和存盤方式描述，是資料在資料庫內部的組織方式，

優點

外模式使得資料庫安全性大大提高，
外模式/模式映像、模式/內模式映像使程式具有較高的邏輯獨立性和物理獨立性，使得資料定義和描述可以從應用程式中分離出去，另外，由于資料的存取由資料管理系統管理，從而簡化了應用程式的編制，大大減少了應用程式維護和修改，

資料庫第二章(關系資料庫)

關系完整性

物體完整性

若屬性（指一個或一組屬性）A是基本關系R的主屬性，則A不能取空值（null），

參照完整性

若屬性（或者屬性組）F是基本關系R的外碼，它與基本關系S的主碼Ks相對應（基本關系R和S不一定是不同的關系），則對于R中每個元組在F上的值必須
或者取空值
或者等于S中某個元組的主碼值

補充：外碼
設F是基本關系R的一個或一組屬性，但不是關系R的碼，Ks是基本關系S的主碼，如果F與Ks相對應，則稱F是R的外碼，并稱基本關系R為參照關系，基本關系S為被參照關系，關系R和關系S不一定是不同的關系，

用戶定義完整性

針對某一具體關系資料庫的約束條件，它反映某一具體應用所涉及的資料必須滿足的語意要求，如定義性別只能取男女，成績為0-100等，

關系代數

查詢至少選修1 號課程和3 號課程的學生號碼
查詢選修了2 號課程的學生的學號
查詢至少選修了一門其直接先行課為5 號課程的學生姓名
查詢選修全部課程的學生學號
求選修了1 號或2 號課程的學生號
檢索劉老師所授課程的課程號和課程名
檢索年齡大于23 歲的男學生的學號和姓名
檢索學號為S3 學生所學課程的課程名與任課教師名
檢索至少選修劉老師所授課程中一門課程的女學生姓名
檢索WANG 同學不學的課程的課程號
檢索至少選修兩門課的學生學號
檢索全部學生都選修的課程的課程號和課程名
檢索選修課程包含LIU 老師所授全部課程的學生學號

傳統的集合運算

• 并

  R∪S={t|t∈R∨t∈S}
  t為元組變數

• 差

  R-S={t|t∈R∧t?S}
  t為元組變數

• 交

  R∧S={t|t∈R∧t∈S}
  t為元組變數

• 笛卡爾積

  R×S={ts tr|tr∈R∧ts∈S }
  t為元組

專門的關系運算

• 選擇
• 投影
• 連接
• 除

關系驗算語言

第三章

資料定義

模式定義

創建 create schema
洗掉 drop schema

表定義

創建 create table
洗掉 drop table
修改 alter table

創建學院表dept 學院號是主碼，學院名不可為空
create table dept
(
dno char(2),
dname char(20),
dtel char(10),
primary key(dno)
);

建立學生表，學號是主碼，姓名取值唯一,性別不能為空
create table student
(
sno char(10) primary key,
sname char(20) unique,
ssex char(2) not null,
foreign key(dno) references dept(dno)
);
洗掉表
DROP TABLE Student [restrict|cascade];

視圖

創建 create view
洗掉 drop view
詳見視圖部分

索引定義

創建 create index
洗掉 drop index
修改 alter index
建立索引的目的：加快查詢速度

create [unique][cluster]
index <索引名>
on <表名>
(<列名>[<次序>][,<列名>[<次序>]]…);
unique:唯一索引，每個索引項只對應唯一的記錄型別
cluster:聚簇索引，索引項的順序與表中記錄的物理順序一致的索引組織，

drop index<索引名>

資料更新

插入資料

插入時會檢查所插入的元組是否滿足完整性規則
物體完整性
參照完整性
用戶定義完整性
not null
unique
值域約束

• 插入元組（插入一條記錄）

  格式：
  insert into<表名>[(<屬性列1>[,<屬性列2>…])]

  將一個新學生元組 （ 學號 ：200215128 ；姓名 ： 陳冬 ； 性別 ： 男 ； 所在系 ：CS ； 年齡 ：18 歲） 插入到Student 表中 ，
  insert into s(sno,sname,ssex,sdept,sage)
  values(‘200215128’,‘陳東’,‘男’,‘cs’,18);

  對每一個系 ， 求學生的平均年齡 ， 并把結果存入資料庫
  create table dept_age
  (
  dept char(15) primary key,
  avg_age smallint
  );
  insert into dept_age(dept,avg_age)
  select sdept,avg(sage)
  from s
  group by sdept;

• 插入子查詢結果（插入多條記錄）

修改資料

update <表名>
set <列名>=<運算式>[,<列名>=<運算式>…]
[where <條件>];

• 修改某一個元組的值

  將學生200215121 的年齡改為22 歲
  update s
  set sage=22
  where sno=‘200215121’;

• 修改多個元組的值

  所有教師工資上調5%
  update prof
  set sal =sal*1.05;

• 帶有子查詢的修改陳述句

  將IS系全體學生成績清零
  update sc
  set grade=0
  where sno in(
  select sno from s
  where sdept=‘IS’
  );

  當某同學1號課的成績低于該課程平均成績時，將該同學該門課成績提高5%，
  update sc
  set grade=grade*1.05
  where cno=‘1’ and grade<(select avg(grade) from sc where cno=‘1’);

洗掉資料

• 洗掉某一個元組的值

• 洗掉多個元組的值

• 帶子查詢的洗掉陳述句

  洗掉有四門不及格課程的所有同學資訊，
  delete from s
  where sno in (
  select sno from sc
  where grade<60
  group by sno
  having count(*)>=4
  );

視圖

虛表，是從一個或幾個基本表（或視圖）匯出的表
只存放視圖的定義，不存放視圖對應的資料
基表中的資料發生變化，從視圖中查詢出的資料也隨之改變

定義視圖

create view
<視圖名> [<列名>[,<列名>…]]
as <子查詢>
[with check option];

建立is系學生視圖
create view is_student
as
select sno,sname,sage
from s
where sdept=‘IS’;

建立IS 系學生的視圖 ， 并要求進行修改和插
入操作時仍需保證該視圖只有IS
create view is_student
as
select sno,sname,sage
from s
where sdept=‘IS’
with check option;

with check option
如上題，更新時自動加上sdept=‘IS’

洗掉視圖
DROP VIEW < 視圖名>[CASCADE] ;
PS:cascade 是級聯

查詢視圖

更新視圖

視圖的作用

視圖能夠簡化用戶的操作
視圖使用戶能以多種角度看待同一資料
視圖對重構資料庫提供了一定程度的邏輯獨立性
視圖能夠對機密資料提供安全保護
適當的利用視圖可以更清晰的表達查詢

嵌入式sql

database.cpp

資料查詢

單表查詢

選擇表中若干列

• select 后面列出目標列運算式

• 目標列運算式

  • 算術運算式
  • 字串常量
  • 函式(count, sum, avg, min, max)
  • 列別名（可使用列別名改變查詢結果的列標題）

選擇表中若干元組

• 查詢結果可能出現重復元組，通過distinct去重

• 常用的查詢條件

  • 比較(=, >, <. >=, <=, !=, <>)

  • 確定范圍(between and, not between and)

  • 確定集合(in, not in)

    select sname,ssex
    from s
    where sdept in(“CS”, “MA”,“IS”)

  • 字符匹配(like, not like)

    • [not] like ‘匹配串’ escape ‘換碼字符’
    • %匹配0個及以上任意字符, _匹配任意單個字符

  • 多重條件(and, or, not)

• 涉及空值的查詢

  選修1號課的不及格的學生及缺考的學生學號，
  select sno from sc
  where cno=‘1’ and grade<60
  union
  select sno from sc
  where cno=‘1’ and grade is null

order by 子句

按照系好升序列出老師姓名，所在系別，同一系中老師按姓名降序排序，
select pname,dno
from prof
order by dno, pname desc;

• 升序 asc (默認) 降序 desc

聚集函式

• 計數 count(* | [distinct | alll] <列名>)
• 計算總和 sum([distinct | all] <列名>)
• 計算平均值 avg([distinct | all] <列名>)
• 最大最小值 max([distinct | all] <列名>) min([distinct | all] <列名>)

group by 子句

• 結合聚集函式
• having陳述句進行對分組篩選(組為單位) where對記錄進行篩選(記錄為單位)，先進行where再進行having

連接查詢

等值與非等值連接查詢

例題：查詢 每個學生及其選修課程的情況
select s.,sc.
from s,sc
where s.sno=sc.sno
自然連接
查詢每個學生及其選修課程的情況，用自然連接完成
select s.sno, sname,sage,ssex,sdept,cno,grade
from s,sc
where s.sno=sc.sno

自身連接

例題：查詢每一門課的間接選修課（先修課的先修課）
select first.cno,second.pno
from sc first,sc second
where first.pno=second.cno

外連接

查詢每個學生及其選修課程的情況
select s.sno,sname,ssex,sage,cno,grade
from s left outer join sc on(s.sno=sc.sno)

• 左外連接

  左邊關系中的任何元組t都會出現在結果表中
  格式：
  R left outer join S on(R.c=S.c)

• 右外連接

  右邊關系的任何元組t都會出現在結果表中
  格式：
  R right outer join S on(R.c=S.c)

• 全外連接

  是前兩者的并
  格式：
  R full outer join S on(R.c=S.c)

復合條件連接

WHERE子句中含多個連接條件和限定

1. 查詢選修2號課程且成績在90分以上的所有學生的學號和姓名
   select s.sno,sname
   from s,sc
   where s.sno=sc.sno
   and sc.cno=‘2’
   and sc.grade>90

2. 選出既學過1號課程，又學過2號課程的學生學號
   select sno
   from sc sc1,sc sc2
   where sc1.sno=sc2.sno
   and sc1.cno=‘1’
   and sc2.cno=‘2’

嵌套查詢

一個SELECT-FROM-WHERE 陳述句稱為一個查詢塊
將一個查詢塊嵌套在另一個查詢塊的WHERE 子句或HAVING 短語的條件中的查詢稱為嵌套查詢
子查詢的限制：不能使用order by子句
有些嵌套查詢可以用連接查詢代替

帶有in謂詞的子查詢

查詢與 “ 劉晨 ” 在同一個系學習的學生姓名
select sname
from s s1
where s1.sdept in(
select sdept from s s2
where s2.sname=‘劉晨’
);
ps:該查詢是不相關子查詢，s1 s2可以省略

查詢選修了課程名為 “ 資訊系統 ” 的學生學號
和姓名
select s.sno,sname
from s,sc
where s.sno=sc.sno and sc.cno in (
select cno from c
where c.cname=‘資訊系統’
);
這種情況是1對1的 用連接查詢比較方便
select s.sno,sname
from s,sc,c
where s.sno=sc.sno and sc.cno=c.cno and c.cname=‘資訊系統’;

求兩門課以上不及格課程學號以及其平均成績
select sno,avg(grade)
from sc
where sc.sno in (
select sno from sc
where grade < 60
group by sno
having count(*)>2
)
group by sno

列出沒學過編譯原理課程的所有同學姓名
select sname
from s
where sno not in (
select sno from sc,c
where sc.cno=c.cno and cname=‘編譯原理’
);

帶有比較運算子的子查詢

子查詢一定要跟在比較符之后，當確切知道內層查詢回傳單值時，可用比較運算子(>,<,<=,>=,!=或者<>)

找出每個學生超過他選修課程平均成績的
課程號 ，
select sno,cno
from sc sc1
where sc1.grade>(
select avg(grade)
from sc sc2
where sc2.cno=sc1.cno
)

帶有any(some)或all謂詞的子查詢

ANY 大于子查詢結果中的某個值
ALL 大于子查詢結果中的所有值
< ANY 小于子查詢結果中的某個值
< ALL 小于子查詢結果中的所有值
= ANY 大于等于子查詢結果中的某個值
= ALL 大于等于子查詢結果中的所有值
<= ANY 小于等于子查詢結果中的某個值
<= ALL 小于等于子查詢結果中的所有值
= ANY 等于子查詢結果中的某個值
=ALL 等于子查詢結果中的所有值（無意義）
!=（或<>）ANY 不等于子查詢結果中的某個值
!=（或<>）ALL 不等于子查詢結果中的任何一個值

• any 任意一個值
• all 所有值

帶有exists謂詞的子查詢

查詢所有選修了1 號課程的學生姓名
select sname
from s
where exists(
select * from sc
where sc.cno=‘1’ and sc.sno=s.sno
)

查詢與 “ 劉晨 ” 在同一個系學習的學生姓名
select sname
from s s1
where exists(
select * from s s2
where s2.sdept=s1.sdept and s2.sname=‘劉晨’
)

查詢選修了全部課程的學生姓名 （沒有一門課程是他不選修的），
select sname
from s
where not exists(
select * from sc where not exists(
select * from c
where c.cno=sc.cno and s.sno=sc.sno
)
)

集合查詢

參加集合操作的個查詢結果的列數必相同；
對應項的資料型別也必須相同

并操作(union)

交操作(intersect)

差操作(except)

基于派生表的查詢

from (SELECT Sno FROM SC WHERE Cno=’ 1 ') as sc1

第五章、關系資料理論

函式之間依賴關系

完全函式依賴(好的依賴關系)

部分函式依賴

傳遞函式依賴

判斷范式

1NF

一個關系模式所以屬性都是不可分的資料項

2NF

R∈1NF，且每一個非主屬性完全函式依賴于任何一個候選碼，則R∈2NF

3NF

設關系模式R<U,F>∈1NF，若R中不存在這樣的碼X，屬性組Y及非主屬性Z（Z不是Y子集）使得X->Y,Y->Z成立，Y ->X,則稱R<U,F>∈3NF

BCNF

關系模式R<U,F>∈1NF，若X->Y且Y不是X子集時，X必含有碼，則R<U,F>∈BCNF

證明

• 若R為3NF，證明其為2NF
• 若R是BCNF，證明其為3NF

模式分解

原則：消除不合理依賴，保留好的依賴，保持無損連接，

1NF -> 2NF

消除非主屬性對碼的部分函式依賴

2NF -> 3NF

消除非主屬性對碼的傳遞函式依賴

3NF -> BCNF

消除主屬性對碼的部分和傳遞函式依賴

屬性集閉包計算及公理系統

屬性集閉包計算：
已知關系模式R<U,F>,其中，
U={A,B,C,D,E}
F={AB->C,B->D,C->E,EC->B,AC->B}
求(AB)_F^+

資料庫設計

需求分析

了解與分析用戶需求（包括資料和處理），是最為繁瑣的一步，

概念結構設計

通過對用戶需求進行綜合，歸納和抽象，形成一個獨立與具體資料庫管理系統的概念模型(如er圖)，

邏輯結構設計

將概念結構轉化為某個資料庫管理系統所支持的資料模型，并對其進行優化，

資料庫物理設計

為邏輯資料模型選取一個最適合應用環境的物理結構（包括存盤結構和存取方法），

資料庫實施

設計人員運用資料庫管理系統提供的資料庫語言及其宿主語言，根據邏輯設計和物理設計的結果建立資料庫，撰寫與除錯應用程式，組織資料入庫，并進行試運行，

資料庫運行與維護

資料庫應用系統經過試運行后即可投入正式運行，在資料庫系統運行程序中必須不斷的對其進行評估，調整和修改，

第七章、資料庫恢復

事物：事務(Transaction)是用戶定義的一個資料庫操作序列,這些操作要么全做，要么全不做，是一個不可分割的作業單位，

BEGIN TRANSACTION;
COMMIT;
ROLLBACK;
事務通常是以begin transaction開始，以commit或者rollback結束，
commit:表示提交，及提交事務的所有操作，
rollback:表示回滾，事務運行程序中出現了某種故障，事務不能正常執行，系統將事務中對資料庫所有已完成的操作全部撤銷，

事務四個特性：原子性、一致性、隔離性、持續性，

故障種類

事務故障

某個事務在運行程序中由于種種原因未運行至正常終止點就夭折了，

– 輸入資料有誤
– 運算溢位
– 違反了某些完整性限制
– 某些應用程式出錯
– 并行事務發生死鎖

系統故障

系統故障是指造成系統停止運轉的任何事件，使得系統要重新啟動，

– 整個系統的正常運行突然被破壞
– 所有正在運行的事務都 非正常終止
– 記憶體中 資料庫緩沖區的資訊全部丟失
– 外部存盤設備上的資料未受影響

介質故障

介質故障是指存盤資料庫的設備( 如硬碟) 發生故障，使存盤在其上的資料部分丟失或全部丟失，

磁盤損壞、
磁頭碰撞、
作業系統的某種潛在錯誤
瞬時強磁場干擾，

恢復實作技術

資料轉儲(備份)

資料庫管理員定期地將整個資料庫復制到磁帶，磁盤或者其他存盤介質上保存起來的程序，

資料庫轉儲還可分為兩個方式：海量轉儲(每次轉儲全部資料庫)和增量轉儲(每次只轉儲上一次轉儲后更新的資料)，其和兩種轉儲狀態可兩兩組合，

• 靜態轉儲

  在系統無運行事務時進行的轉儲操作，

  在轉儲操作開始的時刻資料庫處于一致性狀態，而轉儲期間不允許（或不存在）對資料庫的任何存取、修改活動，

• 動態轉儲

  是指轉儲期間允許對資料庫進行存取或修改，

  必須把轉儲期間各事務對資料庫的修改活動登記下來，建立日志檔案，

日志檔案

• 日志檔案的格式和內容

  是用來記錄事務對資料庫的更新操作的檔案，

  • 以記錄為單位

    各個事務的開始標記(BEGIN TRANSACTION)
    各個事務的結束標記(COMMIT 或ROLLBACK)
    各個事務的所有更新操作
    這三個構成日志檔案中的一個日志記錄，

    每個日志記錄內容

    1. 事務標識
    2. 操作型別（插入、洗掉或修改）
    3. 操作物件
    4. 更新前資料的舊值（對插入操作而言，此項為空值）
    5. 更新后資料的新值（對洗掉操作而言, 此項為空值）

  • 以資料塊為單位

    – 事務標識
    – 更新前的資料塊（對插入操作而言，此項為空值）
    – 更新后的資料塊（對洗掉操作而言, 此項為空值）

• 日志檔案的作用

  • 進行事務故障恢復
  • 進行系統故障恢復
  • 介質故障恢復(協助后備副本進行)

• 登記日志檔案

  • 登記次序嚴格按照并發事務執行的時間次序

  • 先寫日記檔案，后寫資料庫

    原因：
    – 寫資料庫和寫日志檔案是兩個不同的操作；
    – 在這兩個操作之間可能發生故障，
    – 如果先寫了資料庫修改，而在日志檔案中沒有記登
    這個修改，則以后就無法恢復這個修改了；
    – 如果先寫日志，但沒有修改資料庫， 按日志檔案恢
    復時，只不過是多執行一次不必要的UNDO 操作，并
    不會影響資料庫的正確性，

具有檢查點的恢復技術

1. 從重新開始檔案中找到最后一個檢查點記錄在日志檔案中的地址，由該地址在日志檔案中找到最后一個檢查點記錄；
2. 由該檢查點記錄得到檢查點建立時刻所有正在執行的事單務清單ACTIVE-LIST ，建立兩個事務佇列
   – UNDO-LIST
   – REDO-LIST
   把首先把ACTIVE-LIST 暫時放入UNDO-LIST 佇列，REDO佇列暫為空，
3. 從檢查點開始正向掃描日志檔案，直到日志檔案結束
   – 如有新開始的事務Ti, 把Ti 暫時放入UNDO-LIST 佇列
   – 如有提交的事務Tj ,把Tj 從UNDO-LIST 佇列移到REDO-LIST 佇列
4. 對UNDO-LIST 中的每個事務執行UNDO 操作, 對REDO-
   LIST 中的每個事務執行REDO

恢復策略

事務故障的恢復

(a) 反向掃描檔案日志（即從最后向前掃描日志檔案），查找該事務的更新操作，
(b) 對該事務的更新操作執行逆操作，即將日志記錄中 “ 更新前的值 ” 寫入資料庫，
插入操作， “ 更新前的值 ” 為空，則相當于做洗掉操作
洗掉操作， “ 更新后的值 ” 為空，則相當于做插入操作
修改操作，則用 “ 更新前的值 ”代替"更新后的值"
? 繼續反向掃描日志檔案，查找該事務的其他更新操作，并做同樣處理，
(d) 如此處理下去，直至讀到此事務的開始標記，事務故障恢復就完成了，

系統故障的恢復

總：撤銷故障發生時未完成的事務，重做已完成的事務，

1. 正向掃描日志檔案（即從頭掃描日志檔案），生成如下兩個佇列，
   Redo 佇列: 在故障發生前已經提交的事務
   Undo 佇列: 故障發生時尚未完成的事務

2. 對Undo 佇列事務進行Undo處理：
   反向掃描日志檔案，對每個Undo事務的更新操作執行逆操作，即將 “ 更新前的值 ” 寫入資料庫，(回滾)

3. 對Redo 佇列事務進行REDO 處理：
   正向掃描日志檔案，對每個REDO 事務重新執行日志檔案登記的操作，即將 “ 更新后的值 ” 寫入資料庫，(重做)

介質故障的恢復

1. 重裝資料庫
   裝入最新的后備資料庫副本，使資料庫恢復到最近一
   次轉儲時的一致性狀態，
2. 重做已完成事務
   裝入有關的日志檔案副本（轉儲結束時刻的日
   志檔案副本），重做已完成的事務，

第八章、并發控制

事務并發執行帶來的問題

會帶來資料不一致性

丟失修改

丟失修改是指事務1與事務2從資料庫中讀入同一資料并修改，事
務2的提交結果破壞了事務1提交的結果，導致事務1 的修改被丟失，

不可重復讀

不可重復讀是指事務1讀取資料后，事務2執行更新操作，使事務1無法再現前一次讀取結果 ，

• 修改

  事務1 讀取某一資料后：事務2對其做了修改， 當事務1再次讀該資料時，得到與前一次不同的值，

• 洗掉

  事務2洗掉了其中部分記錄 ，當事務1 再次讀取資料時，發現某些記錄神秘地消失了，

• 插入

  事務2 插入了一些記錄 ，當事務1 再次按相同條件讀取資料時，
  發現多了一些記錄，

讀臟資料

事務1修改某一資料，并將其寫回磁盤，事務2讀取同一資料后 讀取同一資料后，事務1由于某種原因被撤消 ，這時事務1已修改過的資料恢復原值，事務2讀到的資料就與資料庫中的資料不同，是不正確的資料，又稱為 “ 臟 ” 資料 ，

封鎖

封鎖就是事務T 在對某個資料物件（例如表、記錄等）操作之前，先向系統發出請求，對其加鎖，
加鎖后事務T 就對該資料物件有了一定的控制，在事務T釋放它的鎖之前，其它的事務不能更新此資料物件，

基本封鎖型別

• 共享鎖(s鎖，又稱為讀鎖)

  若事務T對資料物件A加上S鎖，則其它事務只能再對A加S鎖，而不能加X鎖，直到T釋放A上的S鎖，

• 排它鎖(X鎖，又稱為寫鎖)

  若事務T對資料物件A 加上X 鎖，則只允許T讀取改和修改A，其它任何事務都不能再對A加任何型別的鎖，直到T釋放A上的鎖，

封鎖協議

• 一級封鎖協議

  事務T在修改資料R之前必須先對其加X鎖，直到事務結束(commit或rollback)才釋放，

  可防止丟失修改

  如果是讀資料，不需要加鎖的，所以它不能保證可重復讀和不讀“臟”資料，

• 二級封鎖協議

  1級封鎖協議+ 事務T在讀取資料R前必須加先加S鎖，讀完后即可釋放S鎖，

  2級封鎖協議可以防止丟失修改和讀“臟”資料，

  由于讀完資料后即可釋放S 鎖，所以它不能保證可重復讀，

• 三級封鎖協議

  1 級封鎖協議+事務T在讀取資料R之前必須先對其加S鎖，直到事務結束才釋放，

  3級封鎖協議可以防止丟失修改和讀“臟”資料和不可重復讀，

活鎖和死鎖

活鎖

由于系統調度原因，某些事物的加鎖請求得不到回應而永遠等待，

策略：可采用先到先服務的策略，

死鎖

兩個或多個事務都已封鎖了一些資料物件，然后又都請求對已被其他事務封鎖的資料物件加鎖，從而出現死等待，

• 死鎖的預防

  這兩種策略并不太適合資料庫的特點，因此采用診斷并解除的方法，

  • 一次封鎖法

    要求每個事務必須一次將所有要使用的資料全部加鎖，否則就不能繼續執行 ，

    難于事先精確確定封鎖物件
    降低并發度

  • 順序封鎖法

    順序封鎖法是預先對資料物件規定一個封鎖順序，所有事務都按這個順序實行封鎖，

    維護成本高
    難于實作

• 死鎖的診斷和解除

  • 診斷

    • 超時法

      如果一個事務的等待時間超過了規定的時限，就認為發生了死鎖，

      實作簡單

      1. 若時限設定過短，有可能誤判死鎖
      2. 若時限設定過長，容易發現不到死鎖，

    • 等待圖法

      用事務等待圖動態反映所有事務的等待情況，并發控制子系統周期性地生成事務等待圖，并進行檢測，

  • 解除

    選擇一個處理死鎖代價最小的事務，將其撤消，釋放此事務持有的所有的鎖，使其它事務能繼續運行下去，

并發操作的可串行性

將所有事務串行起來的調度策略一定是正確的調度策略，

多個事務的并行執行是正確的， 當且僅當其結果與按某一次序串行地執行它們時的結果相同，這種并行調度策略稱為可串行化的調度，

兩段鎖協議

事務分為兩個階段

并行執行的所有事務均遵守兩段鎖協議，則對這些事務的所有并行調度策略都是可串行化的，

1. 一次封鎖法要求每個事務必須一次將所有要使用的資料全部加鎖，否則就不能繼續執行，因此，一次封鎖法遵守兩段鎖協議；
2. 但是兩段鎖協議并不要求事務必須一次將所有要使用的資料全部加鎖，因此，遵守兩段鎖協議的事務可能發生死鎖，

擴展階段

第一階段獲得鎖，也稱為擴展階段，在這個階段，事務可以申請獲得資料項任何型別鎖，但是不能釋放任何鎖，

收縮階段

第二階段釋放鎖，也稱為收縮階段，在這個階段，事務可以釋放資料項任何型別鎖，但是不能申請任何鎖，

第九章、資料庫安全性

保護資料庫以防止不合法使用所造成的的資料泄露，更改或破壞，

其他的還有
用戶層面：用戶標記（用戶身份鑒別）
硬碟層面：密碼方式存盤
視圖（把要保密的資料隱藏起來）

存取控制

存取控制機制

• 定義用戶權限

  定義用戶權限，并將用戶權限登記到資料字典中，

• 合法權限檢查

  根據安全性規則進行安全性檢查，若用戶請求超出了定義的權限，則系統拒絕操作，

兩種存取控制方法

• 自主存取控制

  用戶對于不同的資料庫物件有不同的存取權限，
  不同用戶對同一物件也有不同的權限，
  用戶還可將其擁有的存取權限轉授給其他用戶，

  • 授權：授予

    grant <權限>[,<權限>]…
    on <物件型別> <物件名>[,<物件型別><物件名>]…
    to <用戶>[,<用戶>]…
    [with grant option]

    WITH GRANT OPTION子句:
    指定：可以再授予
    沒有指定：不能傳播

    (1) 把查詢Student表權限授給用戶U1
    GRANT SELECT
    ON TABLE Student
    TO U1;
    (2) 把對Student表和Course表的全部權限授予用戶U2和U3
    GRANT ALL PRIVILIGES
    ON TABLE Student, Course
    TO U2, U3;
    (3) 把對表SC的查詢權限授予所有用戶
    GRANT SELECT
    ON TABLE SC
    TO PUBLIC;
    (4) 把查詢Student表和修改學生學號的權限授給用戶U4
    GRANT UPDATE(Sno), SELECT
    ON TABLE Student
    TO U4;

  • 授權：識訓

    REVOKE <權限>[,<權限>]…
    ON <物件型別> <物件名>，…
    FROM <用戶>[,<用戶>]
    [CASCADE|RESTRICT];

    cascade:級聯識訓 系統只識訓直接或間接從U5處獲得的權限

• 強制存取控制

  每個資料庫物件被標記以一定的密級，每一個用戶也被授予某一個密級的許可證，對于任意一個物件，只有具有合法許可證的用戶才可以存取，

  • 主體與客體

    主體：是系統中的活動物體，既包括資料庫管理系統所管理的實際用戶，也代表用戶的各個行程，
    客體：是系統中的被動物體，是受主題操縱的，包括檔案、基本表、索引、視圖等，

  • 規則

    當主體許可證級別大于等于客體密級時，該主體才能讀取回應的客體
    當主體許可證級別小于等于客體密級時，該主體才能寫相應的客體，

審計

將用戶對資料庫的所有操作自動記錄下來，放入到審計檔案中，

設定審計功能 (AUDIT)

對修改SC表結構或修改SC表資料的操作進行審計
AUDIT ALTER，UPDATE
ON SC;

取消審計功能 (NOAUDIT)

取消對SC表的一切審計
NOAUDIT ALTER，UPDATE
ON SC;

第十章、資料庫完整性

資料庫完整性是指資料的正確性和相容性，
正確性：資料是符合現實世界語意反應當前實際狀況的；
相容性：資料庫同一物件在不同關系表中的資料是符合邏輯的，

物體完整性

檢查與違約處理

檢查主碼值是否唯一，如果不唯一則拒絕插
入或修改
檢查主碼的各個屬性是否為空，只要有一個
為空就拒絕插入或修改

參照完整性

CREATE TABLE SC
(Sno CHAR(9) NOT NULL， ，
Cno CHAR(4) NOT NULL， ，
Grade SMALLINT， ，
PRIMARY KEY （Sno ，Cno ），
FOREIGN KEY (Sno) REFERENCES Student(Sno)
ON DELETE CASCADE /* 級聯洗掉SC 表中相應的元組*/
ON UPDATE CASCADE， /* 級聯更新 新SC 表中相應的元組 組*/
FOREIGN KEY (Cno) REFERENCES Course(Cno)
ON DELETE NO ACTION /* 當洗掉course 表中的元
組造成了與SC 表不一致 時 拒絕洗掉*/
ON UPDATE CASCADE /* 當更新course 表中的cno時 時 ， 級
聯更新SC 表中相應的元組*/);

違約處理

拒絕(NO ACTION)執行(默認策略)
級聯(CASCADE)操作
設定為空值（SET-NULL）

用戶定義完整性

不滿足則拒絕執行

屬性上約束條件

不滿足則拒絕執行

• 不允許空值 not null
• 列值唯一 unique
• 用check陳述句設定 如ssex值允許取’男’和’女’ check( ssex in(‘男’，‘女’))

元組上約束條件

最后加check

當學生的性別是男時，其名字不能以Ms.打頭，
CREATE TABLE Student
(Sno CHAR(9)，
Sname CHAR(8) NOT NULL，
Ssex CHAR(2)，
Sage SMALLINT，
Sdept CHAR(20)，
PRIMARY KEY (Sno)，
CHECK (Ssex=‘女’ OR Sname NOT LIKE’Ms.%’)
/定義了元組中Sname和 Ssex兩個屬性值之間
的約束條件/
);

觸發器

觸發器（Trigger）是用戶定義在關系表上的一類由事件驅動的特殊程序，

定義觸發器

• 定義陳述句

  create trigger <觸發器名>
  {before|after} 觸發事件 on <表名>
  referencing new|old row as<變數>
  for each {row|statement}
  begin
  執行 內容
  end

  • 例題

    【1】當洗掉某一同學Sno時，該同學所有選課也要洗掉，請用觸發器實作，
    create trigger dels
    after delete on s
    referencing old oldi
    for each row
    begin
    delete from sc where sno=oldi.sno
    end;

    【2】對sc表的grade屬性進行修改時，若分數增加了10%，則將此操作記錄到另一表sc_u(sno,cno,oldgrade,newgrade)中，
    create trigger sc_t
    after update of grade on sc
    referencing
    oldrow as oldtuple
    newrow as newtuple
    for each row
    when(newtuple.grade>=1.1*oldtuple.grade)
    insert into sc_u(sno,cno,oldgrade,newgrade)
    values(oldtulpe.sno,oldtuple.cno,oldtuple.grade,
    newtuple.grade)

激活觸發器

• 1. 執行該表上的before觸發器
• 2. 激活該觸發器對應的sql陳述句
• 3. 執行該表上的after觸發器

洗掉觸發器

觸發器必須是已經創建的觸發器
只能由具有相應權限的用戶洗掉
PS:只有表的擁有者能創建觸發器

• drop trigger <觸發器名> on <表名>;