Transcript ch02 關聯式資料庫系統綜覽 - So

第2章
關聯式資料庫系統
Page:1
大綱
 2.1 前言
 2.2 關聯式資料庫系統的基本術語
 2.3 關聯式資料模型的運算模式
 2.4 關聯式資料庫管理系統應具備的基本功能
 2.5 資料庫管理師的主要工作
Page:2
2.1 前言
 Codd, E.F. 於1970年首先提出了關聯式資料模
型(Relational Data Model)理論
 該模型植基於數學中的關連理論(Relational
Theory)
 關聯式資料模型的基本單位稱做值域(Domain)，
每一個值域可以表示一個企業的最基本資料項
Page:3
2.1 前言
 關聯式資料庫系統的基本觀念和術語




關聯式資料庫管理系統
關聯式資料庫
資料表、檢視表
索引、主鍵、外來鍵
 資料庫如何維繫資料的完整性和一致性
 關聯式運算
 維護資料庫安全
 交易管理與平(並)行控制
 資料備份與復原
Page:4
2.2 關聯式資料庫系統的基本術語
 一個典型的關聯式資料庫架構
關聯式資料庫管理系統
(R elatio n al D a t a B a s a e M a n a g e m e n t S y s t e m )
資料庫 A
資料庫 B
T able A 1
T able A 2
T able B 1
T able B 2
T able B 3
Index A 11
Index A 21
Index B 11
Index B 21
Index B 31
Index A 22
Index B 22
Index B 23
Page:5
2.2.1關聯式資料庫管理系統
 RDBMS
 Relational Data Base Management System
 RDBMS
 Oracle
 DB2
 MS SQL Server
 Sybase
 資料庫
 教職員資料庫、學生學籍資料庫、圖書資料庫、
人事資料庫、銷售資料庫、客服資料庫
Page:6
2.2.1關聯式資料庫管理系統
 資料庫管理系統的主要功能
 提供便捷的方法讓使用者建立資料庫並管理
資料
 管理各資料庫在電腦中的組織與架構
 管理資料庫物件
 確保資料的一致性及完整性
 管制資料庫的使用權限以維護資料安全
 決定存取資料的最佳化路徑
 保存資料庫存取及異動的歷史紀錄
 資料庫之備份與復原
 交易管理與並行控制
Page:7
2.2.2 資料庫(Data Base)
 資料庫是資料儲存在RDBMS裡的一種物件
 表格空間(Tablespace)
 資料表(Base Table)
 索引(Index)
 資料表
 組成資料庫的最基本單位
 可以將資料表視為一個檔案
 不同公司所提供的RDBMS在設計上可能有些微
差異；例如有的有表格空間，有的則沒有；然整
體觀念上是極類似的
Page:8
2.2.3 資料表(Base Table)
 資料表(Base Table，或Table)
 或稱基底資料表
 是資料庫裡儲存資料的最基本單位
 可以將之視為資料檔
 資料表
 資料行
 資料列
Page:9
2.2.3 資料表(Base Table)
 「資料行」(Column)
 又稱為欄位(Field) 、屬性(Attribute)
 同一個資料行裡的資料都具有相同的屬性
 「資料列」 (Row)
 又稱為記錄(Record)
Page:10
2.2.3 資料表(Base Table)
 資料表、資料列、資料行及主鍵與外來鍵之關係
Page:11
2.2.3 資料表(Base Table)
 EMP資料表擁有5個資料行
EMP_NO
EMP_NAME
SEX
SALARY
DEPT_CODE
 SEX資料行用來存放性別資訊，它的值域為
 {’男’，’女’}
 EMP_NAME資料行專門用來存放員工姓名，
它的值域為
 {’張學友’， ’張惠妹’，’永邦’，’王菲’，’伍
百’，’SHE’}
Page:12
2.2.3 資料表(Base Table)
 NULL(虛值)
 虛值不是空白，而是「未知，尚未確定」之意
例如張惠妹是新進員工，還不確定要安置到哪
一個部門，這時我們可以用虛值(NULL)來代替
 NOT NULL
 資料行必須有正確的資料值，不可為虛值
例如EMP_NO和EMP_NAME兩欄位的值必須確
定，不可為虛值
在建立資料表時就必須宣告為NOT NULL
Page:13
2.2.3 資料表(Base Table)
 資料行在資料表中的順序並沒有特別的意義
 因為我們查詢資料時可以輕易地指定資料行的
輸出順序
SELECT EMP_NO, EMP_NAME
 表示EMP_No 在EMP_NAME之前
 SELECT EMP_NAME, EMP_NO
 表示EMP_No 在EMP_NAME之後
Page:14
2.2.3 資料表(Base Table)
 「資料列」(Row)
 又稱為記錄(Record)
 資料列在資料表中的順序並沒有特別的意義，
因為我們查詢資料時可以輕易地指定資料列的
輸出順序
 先印出男生後印出女生
 按薪資遞減順序列印
Page:15
2.2.4 索引(Index)
 建立好資料表之後接著也要跟著建立適當之
索引(Index)
 建立索引的目的是為了加快存取資料之速度
 哪些資料行須建索引
 主鍵(Primary Key)
 外來鍵(Foreign Key)
 經常需要用來當作查詢條件的欄位
 索引鍵之順序
 遞增(Ascending)
 遞減(Descending)
Page:16
2.2.4 索引(Index)
 適當的索引可以縮短存取資料表的時間
 資料量越大，索引的效益越明顯
 不當的索引反而會增加新增資料、修改資料、
刪除資料所須的時間
 因為這些異動也須要一併去更新索引，索引太
多反而耗時
 常用的索引技術有
 B樹索引 (B-tree Index)
 雙向連結串列 (Doubly-Linked List)
Page:17
2.2.5 鍵 (Key) 的種類
 「鍵(Key) 」
 可以用來唯一(Unique)識別出資料表中某一筆
記錄的欄位或欄位組合稱之
 鍵的種類：
 候選鍵(Candidate Key)
 替代鍵(Alternate Key)
 主鍵(Primary Key)
 組合鍵(Composite Key)
 外來鍵(Foreign Key)
Page:18
2.2.5 鍵 (Key) 的種類
 候選鍵(Candidate Key)
 所有可能被選為主鍵的鍵稱之
 替代鍵(Alternate Key)
 候選鍵中未被選為主鍵者均稱為替代鍵
Page:19
2.2.5 鍵 (Key) 的種類
 主鍵(Primary Key，PK)
 又稱為主要鍵
 從眾多候選鍵中挑選其中一個為該資料表的主鍵
 在建立資料表時用「PRIMARY KEY」來宣告主
鍵
 主鍵之鍵值不可為虛值(Null Value)
 對資料表中紀錄之異動或查詢通常是以主鍵行之
Page:20
2.2.5 鍵 (Key) 的種類
 主鍵(Primary Key)
 SEX、SALARY及DEPT_CODE等3個資料行都無法用來識別
出唯一的一筆記錄，因此不能當作「鍵(Key) 」
 EMP_NO 和EMP_NAME資料行的都可以用來識別出唯一的一
筆記錄，兩者皆為候選鍵
 因可能會出現同姓同名的員工，所以我們選擇EMP_NO為主鍵
(PK)
Page:21
2.2.5 鍵 (Key) 的種類
 組合鍵(Composite Key)
 又稱為連結鍵(Concatenated Key)，由多個屬性所組合
而成的鍵稱之
Ono
Pno
Amount
訂單序號
產品代號
訂購數量
1
P01
20
1
P02
5
2
P01
30
2
P03
100
3
P03
200
(Ono,Pno)為一個組合鍵，因為它可以決定出唯一的
一筆記錄
只要決定了訂單序號和產品代號，就可決定出訂購數
量
Page:22
2.2.5 鍵 (Key) 的種類
 外來鍵(Foreign Key ，FK)
 A資料表的欄位(假設A3)，其欄位值是源自於B資料表(也
可能源自於同一張資料表)的某一欄位值(假設B1)，則我
們稱A3這個欄位為「外來鍵(FK)」。
Page:23
2.2.5 鍵 (Key) 的種類
 外來鍵(Foreign Key ，FK)
 「外來鍵」用來建立起欄位間的「參照(考)」關係。
 被A3參照(考) 的B1資料行通常是該資料表的主鍵(PK)。
 A3資料行的欄位值是參照(考) B1資料行而來，因此A3與
B1都具有相同的屬性定義。
 A3資料行的欄位值如果不是虛值，那麼就必須存在(包含)
於B1資料行中。
Page:24
2.2.5 鍵 (Key) 的種類
 外來鍵(Foreign Key ，FK)
 參考圖2.2，對於EMP資料表而言，它是以EMP_NO為主
鍵(PK)，而以DEPT_CODE為外來鍵(FK)。
 就DEPT資料表來看，它以DEPT_CODE為主鍵，並且這
個主鍵又被EMP資料表的DEPT_CODE所參考。
Page:25
2.2.5 鍵 (Key) 的種類
 外來鍵(Foreign Key，FK)
 藉由A、B兩張資料表間之合併(Join，或稱聯結)
運算便能同時從A、B兩張資料表擷取資料。
 合併(聯結) 運算是在SQL的SELECT…WHERE
子句中下達「A.A3=B.B1」或「B.B1=A.A3」之
條件行之(請參考7.10.10節)。
 例如：我們可以用
SELECT…WHERE EMP.DEPT_CODE=DEPT.DEPT_CODE
來合併EMP和DEPT兩張資料表。
 我們需要對外來鍵建立索引，以加快合併運算
的執行速度。
Page:26
2.2.6 資料完整性 (Data Integrity) 的限制
完整性法則(Integrity Rule)
確保資料的完整性和一致性，可以避免因新增、修
改、刪除資料所引起之異常現象
個體完整性(Entity Integrity)
作用在單一資料表
值域完整性(Domain Integrity)
作用在單一資料表
參照完整性(Referential Integrity)
作用在兩張資料表
使用者定義完整性 (User Define Integrity)
作用在一張、兩張或數張資料表
Page:27
2.2.6 資料完整性 (Data Integrity) 的限制
 個體完整性(Entity Integrity)
 作用在單一資料表
 主鍵必須能識別出唯一的資料列，因此主鍵之鍵
值不可為虛值
 若主鍵是由多個欄位連結而成的組合鍵，則每一
個欄位值都不可為虛值
 在建立資料表時可以將某欄位為PRIMARY KEY
來確保個體完整性和唯一性
 在建立索引時可以宣告某欄位為UNIQUE INDEX
來確保個體的唯一性
Page:28
2.2.6 資料完整性 (Data Integrity) 的限制
 值域完整性(Domain Integrity)
 作用在單一資料表
 限制某欄位之值必須在某一限制範圍
 或限制某欄位之值必須受某些條件之約束
規定員工之性別(SEX)不是 ’男’ 就是 ’女’，
不可以是虛值或其他值
規定每一位員工的薪資(SALARY)必須介於
20000元與150000元之間
 建立資料表時可以用CHECK來設定條件約束
(Constraint)
Page:29
2.2.6 資料完整性 (Data Integrity) 的限制
 參照完整性(Referential Integrity)
 作用在兩張資料表
 參照完整性乃藉由「主鍵」與「外來鍵」的關係
來規範兩資料表間資料的完整性
 例如：規定EMP資料表的DEPT_CODE欄位值必須存在於
DEPT資料表的DEPT_CDE欄裡，如此可降低EMP資料表
的DEPT_CODE欄位值輸入錯誤的機會，也可以保持EMP
與DEPT資料表間DEPT_CODE資料之完整
EMP 資料表
DEPT資料表
Page:30
2.2.6 資料完整性 (Data Integrity) 的限制
 使用者定義完整性 (User Define Integrity)
 依據使用者的實際需求或商業邏輯來規範「單一
資料表內」或「兩張資料表間」的資料完整性。
 例如：某圖書館規定若有借書逾期歸還情形，則
一個月之內不得再借書。
 又如某公司規定筆記型電腦的使用年限為4年，
滿4年後才可辦理報廢。
 以上這些商業邏輯均屬於使用者定義的完整性，
我們可以透過條件約束(Constraint)、預存程序
(Stored Procedure)、觸發程序(Trigger) 的設
計來達成。
Page:31
2.2.7 檢視表(View)
 檢視表(View Table)
 用視界(View)所定義的表格稱之
 檢視表是一個虛擬表格
 當它被使用時DBMS才從系統資料表裡找出檢視
表之定義，然後臨時從一個或數個資料表(或檢
視表)上擷取部分資料，重新組合後得到一個暫
時性的表格
Page:32
2.2.7 檢視表(View)
 資料表與檢視表
資料表
(Table)
檢視表
(View)
V i e w _A
F11 F13
資料庫
Table 1
Table 2
F11
F12
F13
F21
F22
a1
b1
c1
d1
e1
a2
b2
c2
d2
e2
a3
b3
c3
d3
e3
a4
b4
c4
d4
e4
a5
b5
c5
d5
e5
儲存在磁碟上的永久性表格
使用者
F22
a1
c1
e1
a3
c3
e3
a4
c4
e4
View_A
F11 F13
F22
a2
c2
e2
a5
c5
e5
暫時性表格
U ser1
U ser2
不同使用者看到
不同的結果
Page:33
2.2.7 檢視表(View)
 檢視表的主要特性：
 是一個虛擬表格
 是一個暫時性的表格
 檢視表的資料可以來自資料表或檢視表
 對資料表的存取速度較快，對檢視表的存取速度
較慢
 某些情況下可以透過檢視表來異動資料，某些檢
視表是不能異動的，因為會造成原始資料表資料
之紊亂、異常
Page:34
2.2.7 檢視表(View)
 檢視表其實只是資料表的一個「視界」而已
 檢視表往往只是資料表的一部份而非全部
 檢視表的用途
 我們可以透過檢視表的過濾處理，讓不同
的使用者看到不同的資料內容
 維護資料庫的安全
Page:35
2.2.8 結構化查詢語言
 Structured Query Language，簡稱SQL
 資料定義語言
Data Definition Language，簡稱DDL
 資料處理語言
Data Manipulation Language，簡稱DML
 資料控制語言
Data Control Language，簡稱DCL
Page:36
2.2.8 結構化查詢語言
 資料定義語言
 定義資料庫的組織架構
建立資料庫(CREAT DATABASE)
建立資料表(CREAT TABLE)
建立索引(CREATE INDEX)
建立檢視表(CREAT VIEW)
Page:37
2.2.8 結構化查詢語言
 資料處理語言
 定義資料庫的組織架構後就好比蓋好了一棟
美麗堂皇的學生宿舍，開始會有學生搬進搬
出，可能有訪客來尋人拜訪
 開始有新的資料須新增到資料庫的某個或數
個資料表中，以及執行修改資料表中的資料
、查詢資料、刪除資料等等處理
SELECT(查詢)
INSERT(新增)
UPDATE(修改)
DELETE(刪除)
Page:38
2.2.8 結構化查詢語言
 資料控制語言
 經過授權的人才能存取資料庫中的資料，有
的人只能讀取資料，有的人不但可以讀取也
可以修改
GRANT(授予使用權)
REVOKE(撤銷使用權)
 若須要對某幾個資料表進行一些緊急維護工
作；這段期間不允許使用者存取該資料表；
這時候可以下達資料控制語言來鎖住該資料
表，待維護作業完成後再重新開放供使用者
存取資料
Page:39
2.2.8 結構化查詢語言
 可以在RDBMS提供的交談式介面下直接下達
SQL指令
 也可以將SQL指令內嵌(Embedded)在VB、C、
C++、PowerBuilder、Delphi、Java等高階程式
語言內以進行資料的存取，並與程式語言之處
理邏輯、圖形化使用者介面(GUI)相結合，以提
供更友善的人機操作介面
Page:40
2.3 關聯式資料模型的運算模式
 階層式和網狀式資科模型
 藉由節點間指標(Pointer)的連結來找到相
關的資料
 關聯式資料模型
 經由操作在資料表間的數學運算來處理資
料，並且所得到的結果仍然以資料表的形
式呈現
 運作在關連式資料模型上的數學運算
 代數運算
 集合運算
Page:41
2.3.1 代數運算
 E.F.Code所提出的關連代數理論




選取(Select)
投影(Project)
合併(Join)
除法(Divide)
Page:42
2.3.1 代數運算
 以Emp 資料表和Dept 資料表來說明
Emp_no Emp_name
員工代號 員工姓名
A001 張學友
Sex
Salary Dept_code
性別
男
薪資 部門代號
70000
1
Dept_code
Dept
部門代號 部門名稱
1
企劃部
A002
張惠妹
女
80000
1
2
公關部
B001
永邦
男
60000
2
3
研發部
C002
王菲
女
80000
1
4
客服部
A003
伍百
男
70000
3
D001
SHE
女
65000
4
Emp 資料表
Dept 資料表
Page:43
2.3.1 代數運算
 選取(Select)
 在一個關聯(亦即資料表)中，依照查詢條件抽
取它的「資料列」之運算稱為選取
找出EMP資料表中女性員工的所有資訊，將
會得到：
Emp_no
Emp_name
Sex
Salary
Dept_code
員工代號
A002
員工姓名
張惠妹
性別
女
薪資
80000
部門代號
1
C002
王菲
女
80000
1
D001
SHE
女
65000
4
 選取運算相當於SQL SELECT指令之WHERE
子句
Page:44
2.3.1 代數運算
 投影(Project)
 在一個關聯(亦即資料表)中，用來抽取它的
「資料行」之運算稱為投影
找出Emp資料表中有所有員工的姓名及其
薪資，得到
EMP_NAME SALARY
 投影運算相當於
SQL SELECT指令所選取
之資料行(Select_list)
員工姓名
張學友
薪資
70000
張惠妹
80000
永邦
60000
王菲
80000
伍百
70000
SHE
65000
Page:45
2.3.1 代數運算
 合併(Join)
 合併運算作用在兩個具有
共同值域的關聯上
 藉由合併運算可以將這兩
DEPT
個關聯之相同值域值所對 EMP_NAME
應的元素組全部取出，並 員工姓名 部門名稱
結合在一起，而另組成一 張學友
企劃部
個新的關聯
張惠妹
企劃部
永邦
公關部
結合Emp資料表和Dept
王菲
資料表，以找出員工的
姓名和該員工所屬的部 伍百
SHE
門，將會得到：
企劃部
研發部
客服部
Page:46
2.3.1 代數運算
 合併(Join)
 這是因為Emp資料表的Dept_code資料行和
Dept資料表的Dept_code資料行具有相同的
定義(不一定要如本例用相同的Dept_code)
 用合併運算來結合兩張資料表
 合併運算相當於SQL SELECT…JOIN指令
Page:47
2.3.1 代數運算
 除法(Divide)
 除法運算只作用在兩個關聯中相同的部份
 A資料表除以B資料表，則分兩個步驟來處理
步驟1：
檢查A資料表的每一資料列，若該資料列
包含B資料表中的某一列時，則將之取出
步驟2：
將步驟1之結果刪掉B資料表之資料行
Page:48
2.3.1 代數運算
 除法(Divide)
 設Product為產品資料表，In_Product為入庫
資料表
Page:49
2.3.1 代數運算
 除法(Divide)
 問題：
本次入庫產品之產品名稱、單價及其現有庫存量
為何？
 解答：
用Product資料表除以In_Product資料表即可
得解
Page:50
2.3.1 代數運算
 除法(Divide)
 步驟1：
檢查Product資料表的每一資料列，若該資料列
包含In_Product資料表中的某一列時，則將之取
出。因此，須取出Product資料表產品代號為
P01、P03及P05等3筆資料列，得到：
Page:51
2.3.1 代數運算
 除法(Divide)
 步驟2：
將步驟1之結果刪掉In_Product資料表之資料行
，即刪掉Pno資料行，得到：
Page:52
2.3.2 集合運算
 關連式集合運算




聯集(Union)
交集(Intersection)
差集(Difference)
卡笛生乘積(Cartesian Product)
 進行聯集、交集、差集運算時
 兩資料表的資料行個數必須相等
 相對應的資料行之資 料型態必須一致
Page:53
2.3.2 集合運算
 假設「旗標電子公司」在台北及高雄各有一生產
工廠，其產品庫存資訊分別記載於
台北廠_Inventory和高雄廠_Inventory兩資料表：
Page:54
2.3.2 集合運算
 聯集(Union)
 聯集運算乃在選取兩資料表所有的資料列，
但重複的資料列只取一次
我們可以用聯集運算得知「旗標電子公司」
有哪些庫存產品？
由於聯集是以整張資料表為單位來做集合運
算，我們先用投影運算抽取Pno和PName兩
資料行，如下：
Page:55
2.3.2 集合運算
 聯集(Union)
再進行聯集運算，得知「旗標電子公司
」有庫存之產品共計4項：
Page:56
2.3.2 集合運算
 交集(Intersection)
 交集運算乃是選取兩資料表都有的資料列
我們可以用交集運算得知「旗標電子公司」
在台北廠和高雄廠都有庫存的產品有哪些？
得到：
Page:57
2.3.2 集合運算
 差集(Difference)
 差集運算乃是從兩個資料表中選取只存在於某
一方的資料列
 如果我們用「－」來表示差集運算，則：
台北廠 － 高雄廠 之結果為：
高雄廠 － 台北廠 之結果為：
Page:58
2.3.2 集合運算
 卡笛生乘積(Cartesian Product)
 設A資料表有m個資料列，B資料表有n個資料
列，且用「x」來表示乘積運算，則A x B之結
果計有m x n個資料列
台北廠 x 高雄廠之結果為：
Page:59
2.4 關聯式資料庫管理系統應具備的基本功能
 2.4.1 建立及維護資料庫物件
 2.4.2 新增、修改、刪除、查詢資料
 2.4.3 維護資料一致性、完整性
 2.4.4 維護資料安全
 2.4.5 管理交易(Transaction)
 2.4.6 並行控制(Concurrency Control)
 2.4.7 資料備份(Backup)與復原(Recovery)
 2.4.8 復原處理
 2.4.9 資料庫重整
 2.4.10 資料庫系統目錄
Page:60
2.4.1 建立及維護資料庫物件
 關聯式資料庫管理系統(RDBMS)都會提供一套
方法讓使用者來建立資料庫物件(一般是由DBA
擔任)
 MS SQL Server
 我們可以透過SQL或交談式介面
 建立資料庫(Database)、資料表(Base Table)、
檢視表(View)、索引(Index)、預存程序(Stored
Procedure)、觸發程序(Trigger)等與資料庫有關
的物件
 在一個資料庫底下可以擁有好幾張資料表，我
們可以將資料表想像成一個專門用來儲存同性
質資料的檔案
Page:61
2.4.1 建立及維護資料庫物件
 檢視表是資料表(或檢視表)的一個子集合，可以
從一張資料表(或檢視表)擷取一部份資料成為一
張檢視表，也可以從數張資料表(或檢視表)各匯
集部分資料成為一張檢視表
 資料庫管理系統(DBMS)必須提供新增/修改/刪
除資料庫物件等功能，讓使用者非常方便地建
立資料庫、登錄資料及維護資料
Page:62
2.4.2 新增、修改、刪除、查詢資料
 建立好資料庫及其所屬的資料表後，我們就透
過RDBMS提供的交談式介面或下達SQL指令來
 儲存資料
 修改資料
 刪除資料
 當資料庫裡的資料齊全後，RDBMS還必須提供
查詢資料的功能(例如SQL或交談式介面)，讓使
用者非常方便地隨時查詢到所要的資料
Page:63
2.4.3 維護資料一致性、完整性
 RDBMS另一項重要功能是幫我們檢查及維護資
料的一致性和完整性
 RDBMS也可以NOT NULL來防止使用者忘了輸
入資料
 可以用一些條件約束來檢查輸入的資料值是否
重複、或檢查輸入的資料值是否在限定的範圍
內
 例如用CHECK條件約束來限制SALARY欄位值
必須介於20000元與150000元之間，Sex欄位值
只能是’男’或’女’
Page:64
2.4.4 維護資料安全
 RDBMS透過授權、撤銷及檢視表來維繫資料庫
的存取安全
 DBA具有使用RDBMS的最高權限，他除了可以
建立RDBMS的使用者外，也能授權該使用者的
使用等級，例如
 授權User1擁有建立資料庫和建立資料表之權力
 授權User2只能查詢某資料表
Page:65
2.4.4 維護資料安全
 假設User1經過DBA授權後已經擁有建立資量庫的權限，
今User1建立了一個員工資料庫EmpDB，以及Emp資料
表，因此User1就是EmpDB資料庫及Emp資料表的擁有
者(Owner)，它可以授權User2對Emp資料表有新增資
料、修改資料的權力，但不具備刪除資料的權力
 User1也可以授權User3對Emp資料表只能查詢，而無
法異動任何資料，但允許User3將查詢Emp資料表的權
力再授予他人。如此一來，User3便可授權User5來查
詢Emp資料表
Page:66
2.4.4 維護資料安全
 User1授予User2新增、修改權限，授予User3查詢權
限，User3再授予User5查詢權限
E m p資 料 表
新增、修改
User2
查詢
Owner:User1
User3
查詢
User5
 一但User1撤銷User2對Emp資料表新增資料之權力，
那麼User2便無法新增資料到Emp資料表，但依然可以
修改資料
Page:67
2.4.4 維護資料安全
 若 User1 撤 銷 User3 對 Emp 資 料表 之 查詢權力 ， 除 了
User3無法查詢Emp資料表外，User5也無法查詢，因
為RDBMS會一併撤回User3所授予出去的所有權力
E m p資 料 表
修改
User2
Owner:User1
User1撤回User2之新增權限，撤回User3之查詢權限
Page:68
2.4.5 管理交易(Transaction)
 交易(Transaction)
 一連串不可分割的操作程序之組合
 當交易裡的每一個操作程序都成功時，該筆交
易才算成功，否則交易就算失敗，必須恢復到
交易前的資料狀態
 COMMIT
 用來確認交易
 ROLLBACK
 用來將所有資料恢復到交易前的狀態
 一個交易不是以COMMIT正常地結束，就是以
ROLLBACK異常地結束
Page:69
2.4.5 管理交易(Transaction)
 A公司進貨液晶電視100台，將其中70台運到台北倉庫，
30台運到台中倉庫，因此須要同時更新台北和台中的庫
存量，且台北和台中兩者都成功的更新庫存量，整筆交
易才算成功否則便算失敗，須重新來過
 其邏輯概念如下：
Page:70
2.4.5 管理交易(Transaction)
 RDBMS必須能控制每一個交易，確保資料的正
確、一致、完整，並避免相互矛盾的情形發生
 RDBMS規範每一個交易都必須滿足所謂的
ACID特性，即
 不可部份完成性(Atomicity)(或稱單元性 )
 一致性(Consistency)
 隔離性(Isolation)
 耐久性(Durability)(或稱持續性)
Page:71
2.4.5 管理交易(Transaction)
 不可部份完成性(Atomicity)(或稱單元性 )
 一項交易不是完整地成功，就是徹底地失敗
 交易不是以COMMIT來正常地結束，就是以
ROLLBACK回到交易前的原點
 資料的修改不是全部執行，就是全部不執行
 一致性(Consistency)
 資料庫裡的資料及內部資料結構(索引)，不可因
交易而產生不一致的情形
Page:72
2.4.5 管理交易(Transaction)
 隔離性(Isolation)
 交易須相互隔離，互不影響的
 交易T1執行過程中所參用到的資料，或更新過的資
料或其他中間結果，都不可再被其他交易T2所使用
或更改
 交易T2所看到的資料值不是處於另一筆並行交易T1
修改前的資料值，就是處於交易T1完成交易後的資
料值，絕非中間結果
 耐久性(Durability) (或稱持續性)
 當交易進行到一半時產生故障，而於故障排除系統
重新啟動後，能自動地(使用交易記錄檔)繼續未完成
的交易
Page:73
2.4.6 平行控制(Concurrency Control)
 或稱並行控制
 RDBMS以平行控制(Concurrency Control)機
制來兼顧每一項交易
Page:74
2.4.6 平行控制(Concurrency Control)
 假設台北倉庫中電漿電視的庫存資量為20台，
有多位倉庫管理員
 現場1：
上午10點整，甲倉庫管理員接到出貨5台電漿電視的
通知，經查詢資料庫得知當時的庫存量為20台，尚有
足夠的存貨，於是打電話通知貨車前來搬運。10點20
分貨車到達，運走5台電漿電視，於是甲倉庫管理員便
將庫存量20台減去出貨的5台後存回資料庫(甲員認為
庫存量還有15台)
 現場2：
10點5分乙倉庫管理員接到進貨通知，貨車運來電漿
電視10台，於是查詢資料庫得知當時的庫存量為20台，
加上剛進貨的10台後，存回資料庫(乙員認為庫存量共
有30台)，此時剛好是10點10分
 正確的庫存量是多少呢？
Page:75
2.4.6 平行控制(Concurrency Control)
 RDBMS 處理平行控制的機制主要有以下幾種：
 鎖定(Lock)與解鎖(Unlock)
 避免死結的產生
 增加單位時間的交易數
Page:76
2.4.6 平行控制(Concurrency Control)
 鎖定(Lock)與解鎖(Unlock)
 鎖定的功用是鎖住部分資料，防止他人讀寫以避
免因多人讀寫同一筆資料而造成資料紊亂不一致
 依鎖定資料之模式可區分為
共用鎖定(Shared Lock)
獨占鎖定(Exclusive Lock)
Page:77
2.4.6 平行控制(Concurrency Control)
 鎖定(Lock)與解鎖(Unlock)
 共用鎖定
通常用在讀取資料時，當我讀取某資料後，該筆
資料就被設成共用鎖定，其他人亦可讀取該資料
但無法更改
被設定成共用鎖定的資料若未經解鎖(Unlock)，
則無法再被設成獨占鎖定。這也是被共用鎖定的
資料只供讀取而無法修改的原因
 獨占鎖定
通常發生在寫入(更改)資料時，當我要更改某筆
資料，系統就會將該筆資料設成獨占鎖定，這時
他人就無法讀取該資料，當然也就無法更改了
Page:78
2.4.6 平行控制(Concurrency Control)
 鎖定(Lock)與解鎖(Unlock)
 鎖定顆粒度(Lock Granularity)
用以表示被鎖定資料之多寡
 一般可分為3種層級
記錄鎖定(Record Lock)
資料頁鎖定(Page Lock)
資料表鎖定(Table Lock)
Page:79
2.4.6 平行控制(Concurrency Control)
 鎖定(Lock)與解鎖(Unlock)
 記錄鎖定
一次只所定一筆記錄
 資料頁鎖定
一次鎖定一個資料頁(可能有好幾筆記錄)
 資料表鎖定
一次鎖定一張資料表(可能有好幾個資料頁)
 RDBMS會依據交易所存取的資料範圍及資料量多寡
來自動地調整鎖定層級，當然使用者也可以自己下
達指令來控制鎖定的層級
Page:80
2.4.6 平行控制(Concurrency Control)
 鎖定(Lock)與解鎖(Unlock)
 死結(Dead Lock)
鎖定容易讓多筆交易產生互相等待的情形
例如交易T1已經鎖定R1，正要讀取R2，而R2早
已被交易T2鎖定，且T2正要讀取R1。此時，T1
陷入等待，等待T2解鎖R2，同時T2亦陷入等待，
等待T1解鎖R1
 RDBMS必須具被處理死結的機制，甚至主動地
避免死結的產生
Page:81
2.4.6 平行控制(Concurrency Control)
 避免死結的產生
 每一種RDBMS都必須提供一些機制以處理死結
問題
 首先要監測每一項交易，當交易產生相互等待的
情形，必須制定交易的優先序，強迫一方先執行
而另一方恢復到交易前從新來過，如此方可避免
死結的產生
Page:82
2.4.6 平行控制(Concurrency Control)
 增加單位時間的交易數
 提昇效益
 交易隔離等級 (Isolation Level)：
 READ COMMITTED
 READ UNCOMMITTED
 REPEATABLE READ
 SERIALIZABLE
Page:83
2.4.6 平行控制(Concurrency Control)
 增加單位時間的交易數
 READ COMMITTED
下達COMMIT前的資料都可再被其他交易讀取
 READ UNCOMMITTED
不會發出共用鎖定，也不允許獨占鎖定
 REPEATABLE READ
交 易 中 ， 查詢所讀取到資料都會遭到鎖定 ，以
避免其他交易更新資料
共用鎖定
 SERIALIZABLE
 正被更新中的資料，無法再被其他交易讀取
Page:84
2.4.7 資料備份(Backup)與復原(Recovery)
 資料庫中的資料必須定期地備份(Backup)
 防範因當機、突然停電、磁碟毀損等突發狀況所
造成的損害
 備份檔(Backup File)
 可以很輕易的將資料復原
 對於資料庫的所有交易過程都完整的紀錄在交易
記錄檔(Transaction Log File，或稱日誌檔)裡
 復原
備份檔
交易記錄檔
Page:85
2.4.7 資料備份(Backup)與復原(Recovery)
 系統操作員於10月1日0時對EmpDB資料庫做了一次完整的備
份，並將備份檔上磁帶異地存放
 10月3日10時50分因地震發生當機磁碟毀損，重新換裝磁碟
後，系統操作員便可拿出10月1日EmpDB資料庫的備份磁帶
以及10月3日10時50分當機當時的交易記錄檔來將資料庫復
原
備份檔
交易記錄檔
異動過程記錄到
10/01
00:00:00
10/03
10:50:00
做一次完
整的備份
當機
利用備份檔和交易記錄檔來復原資料庫
Page:86
2.4.8 復原處理
 系統當機之復原處理
 重 新 開 機 之 後 ， RDBMS 會 自 動 地 進 行 ROLLBACK 或
ROLLFORWARD處理
 如果一個交易裡已經執行過COMMIT，則系統故障重新
啟動RDBMS後，RDBMS將優先執行ROLLFORWARD，
將資料恢復到上次COMMIT後的狀態
t0
新增一
筆資料
修改五
筆資料
COMMIT
刪除一
筆資料
當機
ROLLFORWARD
t1
t2
t3
t4
t5
t6
交易開始
Page:87
2.4.8 復原處理
 系統當機之復原處理
 若交易過程中尚未執行COMMIT則系統將會恢復
到整個交易前的狀態，這個動作稱為
ROLLBACK (撤回交易)
t0
新增一
筆資料
修改五
筆資料
新增一
筆資料
刪除一
筆資料
當機
ROLLBACK
t1
t2
t3
t4
t5
t6
交易開始
Page:88
2.4.8 復原處理
 系統當機之復原處理
 ROLLBACK或ROLLFORWARD須參考備份檔
及交易記錄檔來進行，因此資料須經常備份
 備份的總類有以下幾種
 資料庫備份
備份整個資料庫，所須的時間較長
 差異式資料庫備份
只針對最後一次資料庫備份後被變更過的資料來
備份，所須的時間較短
Page:89
2.4.8 復原處理
 磁碟損毀之復原處理
 須換裝新的磁碟，然後用備份檔及交易記錄檔來
進行復原作業(Recovery)
 交易故障之復原處理
 RDBMS會自動地去下達COMMIT或ROLLBACK
命令以避免死結的發生
Page:90
2.4.9 資料庫重整
 資料庫在使用了一段時間之後，有些已被刪除
的老舊資料仍然佔用原先所盤據的磁碟空間，
而有些新被插入的資料，卻沒有放到恰當的位
置上，因此造成了運作的效率不彰
 資料庫重整就是為了重拾不連續的磁碟空間，
及重新編排資料之儲存位置，以維持有效率的
運作
Page:91
2.4.10 資料庫系統目錄
 RDBMS是利用一套專屬的資料庫來管理使用者
資料庫
 這套專屬的資料庫稱為系統目錄(System
Catalog)，或稱系統資料庫(System Database)、
資料字典(Data Dictionary)
 舉凡使用者建立之資料庫、資料表、索引、檢視表、
條件約束、預存程序、觸發程序、函數等資料庫物
件之相關資訊均會詳實地紀錄在系統目錄裡，所有
對資料庫的交易均在系統目錄的管理下，井然有序
的進行
Page:92
2.4.10 資料庫系統目錄
 一個完整RDBMS至少應包括底下幾種不同型式
的檔案：
 資料檔：用來存放資料表的檔案
 索引檔：用來存放鍵值及其索引的檔案
 備份檔：將資料庫備份下來所成之檔案，
通常會以磁帶方式異地存放
 交易記錄檔：記錄資料庫交易過程的檔案
 系統目錄：系統目錄本身也是一個資料庫架
構，它是由許多系統資料表所組
成。系統資料表用來記錄使用者
資料庫的相關資訊
Page:93
2.4.10 資料庫系統目錄
 一個完整RDBMS至少應包括底下幾種不同型式
的檔案：
RDBMS的主要檔案
用途
資料檔
儲存資料表
索引檔
備份檔
儲存索引，例如B + -Tree
儲存資料庫之備份
交易記錄檔
儲存資料庫之交易過程，類似異動檔
系統目錄
存放系統資料表，用來記錄使用者資料庫的
相關資訊
Page:94
2.5 資料庫管理師的主要工作
 資料庫系統的使用者可以分成三類：
 資料庫管理師(Database Administrator簡稱DBA)
 資料庫建立者(Data Base Creator簡稱DBC)
 一般使用者(End User簡稱EU)
Page:95
2.5 資料庫管理師的主要工作
 資料庫管理師(Database Administrator簡稱DBA)
 DBA通常由一至數人擔任
 他們負責安裝資料庫管理系統，同時也是資料
庫管理系統的擁有者
 唯有他們才有權力建立資料庫的使用者(包括資
料庫建立者和一般使用者)，維護使用者的通行
碼(Password)，分配使用者的使用權，監督資
料庫之使用情況
 執行資料庫重整、備份及復原等系統等級的重
要工作
Page:96
2.5 資料庫管理師的主要工作
 資料庫建立者(Data Base Creator簡稱DBC)
 DBC是一個資料庫設計專家，他被DBA授予權
力，可以到建立資料庫及資料表之結構，並且，
他也可以執行資料饋入及查詢等工作
 一般使用者(End User)
 EU之權力由DBC所賦予，他是資料庫的使用者，
EU不須要具備太多的資料庫專業知識，他只要
會運用DML或應用程式去新增、修改、刪除、
查詢實體資料就可以了
Page:97
2.5 資料庫管理師的主要工作
 資料庫管理師(DBA)的主要工作
 負責安裝資料庫管理系統，並建立資料庫使用
者帳號、密碼
 配合資訊系統需求進行資料庫的邏輯設計及實
體設計，然後建立(Create)資料庫、資料表、索
引、檢視表等資料庫物件，或將部分工作分配
給DBC來執行
 管理預存程序、觸發程序、函數等資料庫物件
 設定及管理資料庫的使用權限
 運用系統目錄來管理使用者資料庫
Page:98
2.5 資料庫管理師的主要工作
 訂定資料庫備份/復原計劃及程序，教導系統操
作員執行資料庫備份作業，一但系統損毀，則
按程序將資料庫復原
 管理各資料庫，監督及分析資料庫的存取效能
並調整系統資源，讓資料庫保存在最佳存取狀
態
 進行資料庫重整，讓資料庫保存在最佳存取狀
態
 回答及解決程式設計師對於資料庫存取的問題
 參予資訊系統上線前的整合測試
Page:99