第五章 關聯式代數 資料庫系統理論與 實務

1. 第五章 關聯式代數資料庫系統理論與實務

2. 本章內容 • 5-1簡介 • 5-2一元關聯操作 • 5-3二元關聯操作 • 5-4集合論(Set Theory)操作 • 5-5聚合函數(Aggregate Functions)計算 • 5-6綜合查詢

3. 5-1簡介 • 關聯式代數就是著重於如何取得資料的過程，也就是重視『How』 • 關聯式計算則著重於要取得什麼資料，也就是重視『What』

4. 倘若將子關聯(外來鍵)參考父關聯(主要鍵)的關聯性，表示成『子關聯(外來鍵) = 父關聯(主要鍵)』 • 則圖5-1 (a)中的所有關聯性(Relationship)如下 • 訂單(員工編號) = 員工(員工編號) • 訂單(客戶編號) = 客戶(客戶編號) • 訂單明細(訂單編號) = 訂單(訂單編號) • 訂單明細(產品編號) = 產品資料(產品編號) • 產品資料(供應商編號) = 供應商(供應商編號) • 產品資料(類別編號) = 產品類別(類別編號)

5. 範例資料庫之實體關聯圖 圖5-1 範例資料庫 (a) 實體關聯圖

6. 員工 圖5-1 範例資料庫 (b) 員工

7. 客戶 圖5-1 範例資料庫 (c) 客戶

8. 供應商 圖5-1 範例資料庫 (d) 供應商

9. 訂單&訂單明細 圖5-1 範例資料庫 (e) 訂單 圖5-1 範例資料庫 (f) 訂單明細

10. 產品資料&產品類別 圖5-1 範例資料庫 (g) 產品資料 圖5-1 範例資料庫 (h) 產品類別

11. 關聯式代數 • 關聯式代數可依性質分類為四種 • 對於單一關聯操作的『一元關聯操作』 (Unary Relational Operation) • 對於兩個關聯操作的『二元關聯操作』 (Binary Relational Operation) • 以集合論為基礎的『集合論操作』 (Set Theory Operation) • 聚合函數(Aggregate Function)計算

12. 本章內容 • 5-1簡介 • 5-2一元關聯操作 • 5-3二元關聯操作 • 5-4集合論(Set Theory)操作 • 5-5聚合函數(Aggregate Functions)計算 • 5-6綜合查詢

13. 5-2 一元關聯操作 • 一元的關聯操作主要是針對一個關聯的操作 • 『選取操作』 (Select Operation) • 『投影操作』 (Project Operation) • 『更名操作』 (Rename Operation)。

14. 選取操作 ( SELECT Operation ) 圖5-2 關聯R的SELECT操作示意圖

15. 選取操作 ( SELECT Operation ) • 有一關聯稱之為R，則SELECT操作的表示方式如下σ<選取條件>(R) • 此處的符號σ (唸成sigma) ，單一關聯R操作 • 屬於一元關聯操作(Unary Relational Operation) • <選取條件>：篩選關聯R值組的條件判斷式，有兩種<屬性名稱> <比較運算子> <常數值>或<屬性名稱> <比較運算子> <屬性名稱> • <屬性名稱>：關聯R中的屬性 • <比較運算子> • 一個運算子(Operator)，比較兩邊是否相等的布林值(Boolean) • 亦可透過不同的邏輯運算子{ NOT, AND, OR }來連接以上之基本表示式，以達到邏輯運算的目的

16. 【範例5-1】 • 從員工關聯中挑選出男性員工。 • 【說明】 • 此查詢中，主要是針對員工關聯中的『性別』屬性的屬性值進行篩選動作。 • 【表示式】 σ（性別=‘男’)(員工)

17. 圖5-3 【範例5-1】的結果

18. 所有員工 男性員工 業務 【範例5-2】 • 從員工關聯中挑選出男性業務。 • 【說明】 • 此範例與【範例5-1】不同，因為查詢5-1只要挑選出性別屬性的屬性值為『男』即可，但此查詢不但是要求『性別』的屬性值為『男』，『職稱』屬性的屬性值也必須是『業務』，也就是兩者條件要同時成立，如圖5-4，選取員工性別為男與職稱為業務的兩者共同之交集 • 【表示式】 σ（性別=‘男’ AND職稱=‘業務’)(員工) 男性業務 圖5-4 AND的示意圖

19. 圖5-5 【範例5-2】的結果

20. 所有員工 男性員工 業務 【範例5-3】 • 從員工關聯中挑選男性員工或是職稱為業務之員工 • 【說明】 • 此查詢與【查詢5-2】不同，因為查詢5-2是要挑選出，同時是男性員工，而且又必須是業務身份。而此查詢卻只要符合其中一個條件者，即可被列出。所以以圖5-6而言，是選取符合兩者條件資料之聯集 • 【表示式】 σ（性別=‘男’ OR職稱=‘業務’)(員工) 男性業務 圖5-6 OR的示意圖

21. 圖5-7 【範例5-3】的結果

22. 投影操作 ( Project Operation ) 圖5-8 關聯R的PROJECT操作示意圖

23. 投影操作 ( Project Operation ) • 有一關聯稱之為R，則PROJECT操作的表示方式如下π<屬性列>(R) • 符號π (唸成pi) • 對單一關聯R做操作 • 歸屬於一元關聯操作(Unary Relational Operation) • <屬性列> • 在此處的目的就是限制住不該看到的屬性，只挑選出要看到的屬性，要注意的是 • 此處所出現的所有屬性，必須都要是關聯R中的屬性，不得超出關聯R之外的屬性

24. 【範例5-4】 • 查詢所有員工的員工編號、姓名、職稱和地址 • 【說明】 • 此種範例主要是針對『屬性』做篩選，並非一個關聯的所有屬性皆要輸出，目的除了可以避免某些資料被未授權者所看到外，亦可篩選掉多餘而不需要的屬性項 • 【表示式】π(員工編號, 姓名, 職稱, 地址)(員工)

25. 圖5-9 【範例5-4】的結果

26. 暫存關聯 • 用意在暫時儲存某些操作後所產生的關聯，故稱為暫存關聯 • 可將一連串的操作分為數個獨立的操作分別進行 • 例如要查詢男性員工的員工編號、姓名、職稱、性別與地址，可以使用兩次的操作，如下 男性員工= σ(性別=‘男’)(員工) π(員工編號、姓名、職稱、性別與地址)(男性員工)or 暫存員工= π(員工編號、姓名、職稱、性別與地址)(員工) σ(性別=‘男’)(暫存員工)

27. 更名操作 ( Rename Operation ) • 在關聯中，每一個屬性皆有其名稱，但在某些操作後，倘若想要將其屬性名稱做更改，就有必要使用更名操作(Rename Operation) • 例如要將關聯員工的屬性，員工編號、姓名、職稱和地址，在輸出時，更改為編號、員工姓名、職務和通訊地址，在關聯代數的表示式為 暫存員工(編號,員工姓名,職務,通訊地址)=π(員工編號, 姓名, 職稱, 地址)(員工)

28. 【範例5-5】 • 查詢所有男性員工的員工編號、姓名、職稱、性別和地址。 • 【說明】 • 在此範例中，不僅對橫向的屬性值做篩選，例如『男性員工』即是針對性別屬性中挑選出屬性值為『男』的值組；亦對縱向的屬性做一投影操作，例如員工編號、姓名、職稱、性別和地址。所以在挑選時，不但要使用選取操作(SELECT operation)，亦要同時使用投影操作(PROJECT operation)兩個操作，可做一組合的混合操作。至於該使用『先選取操作，後投影操作』或是『先投影操作，後選取操作』呢？在此範例中，兩者皆可，如下表示式所示

29. 【表示式】先選取操作，後投影操作

30. 圖5-10 【範例5-5】的結果

31. 【表示式】先投影操作，後選取操作

32. 圖5-10 【範例5-5】的結果

33. 【範例5-6】 • 查詢所有男性員工的員工編號、姓名、職稱和地址 • 【說明】 • 在此範例彷彿和【範例5-5】很相似，唯獨其中只有缺少一個『性別』屬性之差異 • 【表示式】 圖5-11 【範例5-6】的結果

34. 缺少『性別』屬性 不可交換的情形 圖5-12 先投影操作，後選取操作的問題

35. 【範例5-7】 • 查詢所有男性業務和女性業務助理之員工編號、姓名、職稱和性別。 • 【說明】 • 在此範例中，不僅對橫向的屬性值做篩選(即為選取操作)，例如『男性員工』即是針對『選取操作』的橫向篩選，而輸出的員工編號、姓名、職稱和性別等屬性，則為縱向篩選(即為投影操作)。

36. 【表示式】 • 先『投影操作』，後『選取操作』 或 • 先『選取操作』，後『投影操作』 圖5-13 【範例5-7】查詢的結果

37. 本章內容 • 5-1簡介 • 5-2一元關聯操作 • 5-3二元關聯操作 • 5-4集合論(Set Theory)操作 • 5-5聚合函數(Aggregate Functions)計算 • 5-6綜合查詢

38. 5-3 二元關聯操作(Binary Relational Operation) • 對兩個關聯進行的操作，稱之為二元操作 • 單一個關聯操作的過程中，主要都是針對關聯的屬性與值組的篩選動作 • 二元操作的主要重點則是在於關聯與關聯之間的合併(Join)動作。

39. 『卡式積』 (Cartesian Product) • 在關聯式代數中，是以 × 來表示卡式積 • 或稱為交叉乘積(Cross Product) ，或交叉合併(Cross Join) • 屬於二元操作，也就是針對兩個關聯的操作 • 例如有兩個關聯，分別名為R與S，則表示為R × S • 兩個關聯分別有m與n個屬性，可表示成R(A1,A2,…Am)與S(B1,B2,…Bn)，而此兩者的卡式積若為關聯Q，亦可表示成Q = R(A1,A2,…Am) × S(B1,B2,…Bn) • 其結果關聯Q的屬性數，必為m+n個，其內容如下Q(A1, A2,…, Am ,B1, B2,…, Bn) • 左邊為關聯R的m個屬性A1,A2,…, Am右邊為關聯S的n個屬性B1,B2,…Bn • 卡式積之後，全部皆成為關聯Q之屬性A1,A2,…Am,B1,B2,…Bn • 共有m+n個屬性數。倘若關連R具有p筆值組，S具有q筆值組，則關聯Q將會具有p×q筆值組

40. θ-Join • 卡式積就是將兩個關聯合併，並且將所有的合併情形皆輸出 • 在兩個關聯之間，會存在於一個所謂的關聯性(Relationship)，此關聯性是利用比較運算子來比較兩個關聯中的某一個或多個屬性之間的比較關係，這種合併在關聯代數中，稱之為θ-Join(唸成theta-join)或THETA JOIN • θ-Join比較運算子包括{=,<, ≦,>, ≧, ≠}

41. EQUIJOIN • 若是只針對單一個等號{=}的比較運算子，稱之為EQUIJOIN • 也就是θ-Join的一個子集合或是特例 • EQUIJOIN也是在關聯式代數中，最常被使用到的一種合併(Join)關係，通常表示成 • 例如兩個關聯R與S的EQUIJOIN R (條件情形)S

42. 關聯R 關聯R 關聯S 相同的屬性出現兩次 關聯S θ-Join or EQUIJOIN圖解 (a) THETA JOIN or EQUIJOIN 圖5-14 THETA JOIN , EQUIJOIN與NATURE JOIN

43. NATURE JOIN • 使用EQUIJOIN的等號{=}比較運算子，則重複的屬性其屬性值兩邊一定皆會相等，則重複出現將失去其意義 • 依據EQUIJOIN之後，去除一邊重複的屬性，則稱之為NATURE JOIN • 以 * 來表示NATURE JOIN，倘若有關聯R與S，則R與S的NATURE JOIN表示成R * (條件情形)S

44. 關聯R 關聯R 關聯S 相同屬性只出現一次 關聯S NATURE JOIN圖解 (b) NATURE JOIN 圖5-14 THETA JOIN , EQUIJOIN與NATURE JOIN

45. 【範例5-8】 • 倘若要查詢每一位員工所經手的訂單資料。 • 【說明】 • 在此查詢中，如果先以簡單將『員工』所有屬性和『訂單』的所有屬性輸出，則只要將此兩個關聯進行合併動作，而此處的關聯性來自於員工中的員工編號與訂單中的員工編號，也由於這兩個屬性名稱剛好相同，所以在表示式中，必須在屬性前加上關聯名稱，例如員工的員工編號將表示成員工.員工編號，訂單中的員工編號表示成訂單.員工編號 • 【表示式】員工 (員工.員工編號=訂單.員工編號)訂單

46. 『員工』關聯的屬性 『訂單』關聯的屬性 此兩個屬性值相等 【範例5-8】EQUIJOIN JOIN 的結果 (a) 圖5-15 EQUIJOIN JOIN & NATURE JOIN的結果

47. 『訂單』關聯的屬性少一個員工編號屬性 『員工』關聯的屬性 【範例5-8】 NATURE JOIN的結果 (b) 圖5-15 EQUIJOIN JOIN & NATURE JOIN的結果

48. 【範例5-9】 • 查詢所有訂單中，哪些產品的實際單價並未以建議單價來銷售。 • 【說明】 • 在此查詢中，有相關的關聯包括『訂單明細』與『產品資料』兩個關聯，並且依據查詢的條件中，可清楚看出，在『訂單明細』與『產品資料』兩個關聯的相同產品編號做『相等』的比較運算，而訂單明細中的實際單價與產品資料中的建議單價做『不相等』的比較運算。 • 【表示式】訂單明細θ (訂單明細.產品編號=產品資料.產品編號 AND 訂單明細.實際單價<>產品資料.建議單價)產品資料

49. 『訂單明細』關聯之屬性 『產品資料』關聯之屬性 實際單價與建議單價之屬性值不相等 產品編號之屬性值相等 圖5-16 THETA JOIN(不相等的比較運算)的結果

50. 外部合併(Outer Join) • 左邊外部合併(Left Outer Join) • 以左邊的關聯為主，倘若無法對應到右邊的關聯，則左邊的資料亦會全部出現，僅在右邊關聯的所有屬性其值皆會以空值(Null Value)出現 • 右邊外部合併(Right Outer Join) • 以右邊的關聯為主 • 完全外部合併(Full Outer Join) • 以兩邊的關聯皆為主，可說是左邊外部關聯與右邊外部關聯的聯集，也就是兩邊的所有資料皆要出現於合併後的關聯中，彼此無法對應上的部份，則另一邊關聯的所有屬性的屬性值皆會出現空值(Null Value)