第 2 章 關聯式資料庫系統

1. 第2章 關聯式資料庫系統

2. 大綱 • 2.1 前言 • 2.2 關聯式資料庫系統的基本術語 • 2.3 關聯式資料模型的運算模式 • 2.4 關聯式資料庫管理系統應具備的基本功能 • 2.5 資料庫管理師的主要工作

3. 2.1 前言 • Codd, E.F.於1970年首先提出了關聯式資料模型(Relational Data Model)理論 • 該模型植基於數學中的關連理論(Relational Theory) • 關聯式資料模型的基本單位稱做值域(Domain)，每一個值域可以表示一個企業的最基本資料項

4. 2.1 前言 • 關聯式資料庫系統的基本觀念和術語 • 關聯式資料庫管理系統 • 關聯式資料庫 • 資料表、檢視表 • 索引、主鍵、外來鍵 • 資料庫如何維繫資料的完整性和一致性 • 關聯式運算 • 維護資料庫安全 • 交易管理與平(並)行控制 • 資料備份與復原

5. 2.2 關聯式資料庫系統的基本術語 • 一個典型的關聯式資料庫架構

6. 2.2.1關聯式資料庫管理系統 • RDBMS • Relational Data Base Management System • RDBMS • Oracle • DB2 • MS SQL Server • Sybase • 資料庫 • 教職員資料庫、學生學籍資料庫、圖書資料庫、人事資料庫、銷售資料庫、客服資料庫

7. 2.2.1關聯式資料庫管理系統 • 資料庫管理系統的主要功能 • 提供便捷的方法讓使用者建立資料庫並管理資料 • 管理各資料庫在電腦中的組織與架構 • 管理資料庫物件 • 確保資料的一致性及完整性 • 管制資料庫的使用權限以維護資料安全 • 決定存取資料的最佳化路徑 • 保存資料庫存取及異動的歷史紀錄 • 資料庫之備份與復原 • 交易管理與並行控制

8. 2.2.2 資料庫(Data Base) • 資料庫是資料儲存在RDBMS裡的一種物件 • 表格空間(Tablespace) • 資料表(Base Table) • 索引(Index) • 資料表 • 組成資料庫的最基本單位 • 可以將資料表視為一個檔案 • 不同公司所提供的RDBMS在設計上可能有些微差異；例如有的有表格空間，有的則沒有；然整體觀念上是極類似的

9. 2.2.3 資料表(Base Table) • 資料表(Base Table，或Table) • 或稱基底資料表 • 是資料庫裡儲存資料的最基本單位 • 可以將之視為資料檔 • 資料表 • 資料行 • 資料列

10. 2.2.3 資料表(Base Table) • 「資料行」(Column) • 又稱為欄位(Field) 、屬性(Attribute) • 同一個資料行裡的資料都具有相同的屬性 • 「資料列」 (Row) • 又稱為記錄(Record)

11. 2.2.3 資料表(Base Table) • 資料表、資料列、資料行及主鍵與外來鍵之關係

12. 2.2.3 資料表(Base Table) • EMP資料表擁有5個資料行 • EMP_NO • EMP_NAME • SEX • SALARY • DEPT_CODE • SEX資料行用來存放性別資訊，它的值域為 • {’男’，’女’} • EMP_NAME資料行專門用來存放員工姓名，它的值域為 • {’張學友’， ’張惠妹’，’永邦’，’王菲’，’伍百’，’SHE’}

13. 2.2.3 資料表(Base Table) • NULL(虛值) • 虛值不是空白，而是「未知，尚未確定」之意 • 例如張惠妹是新進員工，還不確定要安置到哪一個部門，這時我們可以用虛值(NULL)來代替 • NOT NULL • 資料行必須有正確的資料值，不可為虛值 • 例如EMP_NO和EMP_NAME兩欄位的值必須確定，不可為虛值 • 在建立資料表時就必須宣告為NOT NULL

14. 2.2.3 資料表(Base Table) • 資料行在資料表中的順序並沒有特別的意義 • 因為我們查詢資料時可以輕易地指定資料行的輸出順序 • SELECT EMP_NO, EMP_NAME • 表示EMP_No 在EMP_NAME之前 • SELECT EMP_NAME, EMP_NO • 表示EMP_No 在EMP_NAME之後

15. 2.2.3 資料表(Base Table) • 「資料列」(Row) • 又稱為記錄(Record) • 資料列在資料表中的順序並沒有特別的意義，因為我們查詢資料時可以輕易地指定資料列的輸出順序 • 先印出男生後印出女生 • 按薪資遞減順序列印

16. 2.2.4 索引(Index) • 建立好資料表之後接著也要跟著建立適當之索引(Index) • 建立索引的目的是為了加快存取資料之速度 • 哪些資料行須建索引 • 主鍵(Primary Key) • 外來鍵(Foreign Key) • 經常需要用來當作查詢條件的欄位 • 索引鍵之順序 • 遞增(Ascending) • 遞減(Descending)

17. 2.2.4 索引(Index) • 適當的索引可以縮短存取資料表的時間 • 資料量越大，索引的效益越明顯 • 不當的索引反而會增加新增資料、修改資料、刪除資料所須的時間 • 因為這些異動也須要一併去更新索引，索引太多反而耗時 • 常用的索引技術有 • B樹索引 (B-tree Index) • 雙向連結串列 (Doubly-Linked List)

18. 2.2.5 鍵 (Key) 的種類 • 「鍵(Key) 」 • 可以用來唯一(Unique)識別出資料表中某一筆記錄的欄位或欄位組合稱之 • 鍵的種類： • 候選鍵(Candidate Key) • 替代鍵(Alternate Key) • 主鍵(Primary Key) • 組合鍵(Composite Key) • 外來鍵(Foreign Key)

19. 2.2.5 鍵 (Key) 的種類 • 候選鍵(Candidate Key) • 所有可能被選為主鍵的鍵稱之 • 替代鍵(Alternate Key) • 候選鍵中未被選為主鍵者均稱為替代鍵

20. 2.2.5 鍵 (Key) 的種類 • 主鍵(Primary Key，PK) • 又稱為主要鍵 • 從眾多候選鍵中挑選其中一個為該資料表的主鍵 • 在建立資料表時用「PRIMARY KEY」來宣告主鍵 • 主鍵之鍵值不可為虛值(Null Value) • 對資料表中紀錄之異動或查詢通常是以主鍵行之

21. 2.2.5 鍵 (Key) 的種類 • 主鍵(Primary Key) • SEX、SALARY及DEPT_CODE等3個資料行都無法用來識別出唯一的一筆記錄，因此不能當作「鍵(Key) 」 • EMP_NO 和EMP_NAME資料行的都可以用來識別出唯一的一筆記錄，兩者皆為候選鍵 • 因可能會出現同姓同名的員工，所以我們選擇EMP_NO為主鍵(PK)

22. 2.2.5 鍵 (Key) 的種類 • 組合鍵(Composite Key) • 又稱為連結鍵(Concatenated Key)，由多個屬性所組合而成的鍵稱之 • (Ono,Pno)為一個組合鍵，因為它可以決定出唯一的一筆記錄 • 只要決定了訂單序號和產品代號，就可決定出訂購數量

23. 2.2.5 鍵 (Key) 的種類 • 外來鍵(Foreign Key ，FK) • A資料表的欄位(假設A3)，其欄位值是源自於B資料表(也可能源自於同一張資料表)的某一欄位值(假設B1)，則我們稱A3這個欄位為「外來鍵(FK)」。

24. 2.2.5 鍵 (Key) 的種類 • 外來鍵(Foreign Key ，FK) • 「外來鍵」用來建立起欄位間的「參照(考)」關係。 • 被A3參照(考) 的B1資料行通常是該資料表的主鍵(PK)。 • A3資料行的欄位值是參照(考) B1資料行而來，因此A3與B1都具有相同的屬性定義。 • A3資料行的欄位值如果不是虛值，那麼就必須存在(包含) 於B1資料行中。

25. 2.2.5 鍵 (Key) 的種類 • 外來鍵(Foreign Key ，FK) • 參考圖2.2，對於EMP資料表而言，它是以EMP_NO為主鍵(PK)，而以DEPT_CODE為外來鍵(FK)。 • 就DEPT資料表來看，它以DEPT_CODE為主鍵，並且這個主鍵又被EMP資料表的DEPT_CODE所參考。

26. 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兩張資料表。 • 我們需要對外來鍵建立索引，以加快合併運算的執行速度。

27. 2.2.6 資料完整性 (Data Integrity)的限制 • 完整性法則(Integrity Rule) 確保資料的完整性和一致性，可以避免因新增、修改、刪除資料所引起之異常現象 • 個體完整性(Entity Integrity) • 作用在單一資料表 • 值域完整性(Domain Integrity) • 作用在單一資料表 • 參照完整性(Referential Integrity) • 作用在兩張資料表 • 使用者定義完整性 (User Define Integrity) • 作用在一張、兩張或數張資料表

28. 2.2.6 資料完整性 (Data Integrity)的限制 • 個體完整性(Entity Integrity) • 作用在單一資料表 • 主鍵必須能識別出唯一的資料列，因此主鍵之鍵值不可為虛值 • 若主鍵是由多個欄位連結而成的組合鍵，則每一個欄位值都不可為虛值 • 在建立資料表時可以將某欄位為PRIMARY KEY來確保個體完整性和唯一性 • 在建立索引時可以宣告某欄位為UNIQUE INDEX來確保個體的唯一性

29. 2.2.6 資料完整性 (Data Integrity)的限制 • 值域完整性(Domain Integrity) • 作用在單一資料表 • 限制某欄位之值必須在某一限制範圍 • 或限制某欄位之值必須受某些條件之約束 • 規定員工之性別(SEX)不是 ’男’ 就是 ’女’，不可以是虛值或其他值 • 規定每一位員工的薪資(SALARY)必須介於20000元與150000元之間 • 建立資料表時可以用CHECK來設定條件約束(Constraint)

30. 2.2.6 資料完整性 (Data Integrity)的限制 • 參照完整性(Referential Integrity) • 作用在兩張資料表 • 參照完整性乃藉由「主鍵」與「外來鍵」的關係來規範兩資料表間資料的完整性 • 例如：規定EMP資料表的DEPT_CODE欄位值必須存在於DEPT資料表的DEPT_CDE欄裡，如此可降低EMP資料表的DEPT_CODE欄位值輸入錯誤的機會，也可以保持EMP與DEPT資料表間DEPT_CODE資料之完整 EMP 資料表 DEPT資料表

31. 2.2.6 資料完整性 (Data Integrity)的限制 • 使用者定義完整性 (User Define Integrity) • 依據使用者的實際需求或商業邏輯來規範「單一資料表內」或「兩張資料表間」的資料完整性。 • 例如：某圖書館規定若有借書逾期歸還情形，則一個月之內不得再借書。 • 又如某公司規定筆記型電腦的使用年限為4年，滿4年後才可辦理報廢。 • 以上這些商業邏輯均屬於使用者定義的完整性，我們可以透過條件約束(Constraint)、預存程序(Stored Procedure)、觸發程序(Trigger)的設計來達成。

32. 2.2.7 檢視表(View) • 檢視表(View Table) • 用視界(View)所定義的表格稱之 • 檢視表是一個虛擬表格 • 當它被使用時DBMS才從系統資料表裡找出檢視表之定義，然後臨時從一個或數個資料表(或檢視表)上擷取部分資料，重新組合後得到一個暫時性的表格

33. 2.2.7 檢視表(View) • 資料表與檢視表

34. 2.2.7 檢視表(View) • 檢視表的主要特性： • 是一個虛擬表格 • 是一個暫時性的表格 • 檢視表的資料可以來自資料表或檢視表 • 對資料表的存取速度較快，對檢視表的存取速度較慢 • 某些情況下可以透過檢視表來異動資料，某些檢視表是不能異動的，因為會造成原始資料表資料之紊亂、異常

35. 2.2.7 檢視表(View) • 檢視表其實只是資料表的一個「視界」而已 • 檢視表往往只是資料表的一部份而非全部 • 檢視表的用途 • 我們可以透過檢視表的過濾處理，讓不同的使用者看到不同的資料內容 • 維護資料庫的安全

36. 2.2.8 結構化查詢語言 • Structured Query Language，簡稱SQL • 資料定義語言 • Data Definition Language，簡稱DDL • 資料處理語言 • Data Manipulation Language，簡稱DML • 資料控制語言 • Data Control Language，簡稱DCL

37. 2.2.8 結構化查詢語言 • 資料定義語言 • 定義資料庫的組織架構 • 建立資料庫(CREAT DATABASE) • 建立資料表(CREAT TABLE) • 建立索引(CREATE INDEX) • 建立檢視表(CREAT VIEW)

38. 2.2.8 結構化查詢語言 • 資料處理語言 • 定義資料庫的組織架構後就好比蓋好了一棟美麗堂皇的學生宿舍，開始會有學生搬進搬出，可能有訪客來尋人拜訪 • 開始有新的資料須新增到資料庫的某個或數個資料表中，以及執行修改資料表中的資料、查詢資料、刪除資料等等處理 • SELECT(查詢) • INSERT(新增) • UPDATE(修改) • DELETE(刪除)

39. 2.2.8 結構化查詢語言 • 資料控制語言 • 經過授權的人才能存取資料庫中的資料，有的人只能讀取資料，有的人不但可以讀取也可以修改 • GRANT(授予使用權) • REVOKE(撤銷使用權) • 若須要對某幾個資料表進行一些緊急維護工作；這段期間不允許使用者存取該資料表；這時候可以下達資料控制語言來鎖住該資料表，待維護作業完成後再重新開放供使用者存取資料

40. 2.2.8 結構化查詢語言 • 可以在RDBMS提供的交談式介面下直接下達SQL指令 • 也可以將SQL指令內嵌(Embedded)在VB、C、C++、PowerBuilder、Delphi、Java等高階程式語言內以進行資料的存取，並與程式語言之處理邏輯、圖形化使用者介面(GUI)相結合，以提供更友善的人機操作介面

41. 2.3 關聯式資料模型的運算模式 • 階層式和網狀式資科模型 • 藉由節點間指標(Pointer)的連結來找到相關的資料 • 關聯式資料模型 • 經由操作在資料表間的數學運算來處理資料，並且所得到的結果仍然以資料表的形式呈現 • 運作在關連式資料模型上的數學運算 • 代數運算 • 集合運算

42. 2.3.1 代數運算 • E.F.Code所提出的關連代數理論 • 選取(Select) • 投影(Project) • 合併(Join) • 除法(Divide)

43. 2.3.1 代數運算 • 以Emp 資料表和Dept 資料表來說明 Emp 資料表 Dept 資料表

44. 2.3.1 代數運算 • 選取(Select) • 在一個關聯(亦即資料表)中，依照查詢條件抽取它的「資料列」之運算稱為選取 • 找出EMP資料表中女性員工的所有資訊，將會得到： • 選取運算相當於SQL SELECT指令之WHERE子句

45. 2.3.1 代數運算 • 投影(Project) • 在一個關聯(亦即資料表)中，用來抽取它的「資料行」之運算稱為投影 • 找出Emp資料表中有所有員工的姓名及其薪資，得到 • 投影運算相當於SQL SELECT指令所選取之資料行(Select_list)

46. 2.3.1 代數運算 • 合併(Join) • 合併運算作用在兩個具有共同值域的關聯上 • 藉由合併運算可以將這兩個關聯之相同值域值所對應的元素組全部取出，並結合在一起，而另組成一個新的關聯 • 結合Emp資料表和Dept 資料表，以找出員工的姓名和該員工所屬的部門，將會得到：

47. 2.3.1 代數運算 • 合併(Join) • 這是因為Emp資料表的Dept_code資料行和Dept資料表的Dept_code資料行具有相同的定義(不一定要如本例用相同的Dept_code) • 用合併運算來結合兩張資料表 • 合併運算相當於SQL SELECT…JOIN指令

48. 2.3.1 代數運算 • 除法(Divide) • 除法運算只作用在兩個關聯中相同的部份 • A資料表除以B資料表，則分兩個步驟來處理 • 步驟1：檢查A資料表的每一資料列，若該資料列包含B資料表中的某一列時，則將之取出 • 步驟2：將步驟1之結果刪掉B資料表之資料行

49. 2.3.1 代數運算 • 除法(Divide) • 設Product為產品資料表，In_Product為入庫資料表

50. 2.3.1 代數運算 • 除法(Divide) • 問題：本次入庫產品之產品名稱、單價及其現有庫存量為何？ • 解答：用Product資料表除以In_Product資料表即可得解