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

Presentation on theme: "第五章 關聯式代數 資料庫系統理論與實務."— Presentation transcript:

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 )
屬性1 屬性2 屬性3 屬性4 屬性5 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 A6 B6 C6 D6 E6 屬性1 屬性2 屬性3 屬性4 屬性5 A1 B1 C1 D1 E1 A3 B3 C3 D3 E3 A4 B4 C4 D4 E4 圖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】 從員工關聯中挑選出男性業務。 【說明】 【表示式】 σ（性別=‘男’ AND 職稱=‘業務’)(員工)
此範例與【範例5-1】不同，因為查詢5-1只要挑選出性別屬性的 屬性值為『男』即可，但此查詢不但是要求『性別』的屬性值為 『男』，『職稱』屬性的屬性值也必須是『業務』，也就是兩者條 件要同時成立，如圖5-4，選取員工性別為男與職稱為業務的兩者 共同之交集 【表示式】 σ（性別=‘男’ AND 職稱=‘業務’)(員工) 所有員工 男性員工 業務 男性業務 圖5-4 AND的示意圖

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

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

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

22 投影操作 ( Project Operation )
屬性1 屬性2 屬性3 屬性4 屬性5 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 A6 B6 C6 D6 E6 屬性1 屬性2 屬性5 A1 B1 E1 A2 B2 E2 A3 B3 E3 A4 B4 E4 A5 B5 E5 A6 B6 E6 圖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 )
例如要將關聯員工的屬性，員工編號、姓名、職稱和地址，在 輸出時，更改為編號、員工姓名、職務和通訊地址，在關聯代 數的表示式為 暫存員工(編號,員工姓名,職務,通訊地址)=π(員工編號, 姓名, 職稱, 地 址)(員工)

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的一個子集合或是特例
例如兩個關聯R與S的EQUIJOIN R (條件情形)S

42 θ-Join or EQUIJOIN圖解 關聯R 關聯R 關聯S 關聯S 相同的屬性出現兩次
(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 NATURE JOIN圖解 關聯R 關聯R 關聯S 關聯S 相同屬性只出現一次
(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) 右邊外部合併(Right Outer Join)
以左邊的關聯為主，倘若無法對應到右邊的關聯，則左邊的資料亦 會全部出現，僅在右邊關聯的所有屬性其值皆會以空值(Null Value)出現 右邊外部合併(Right Outer Join) 以右邊的關聯為主 完全外部合併(Full Outer Join) 以兩邊的關聯皆為主，可說是左邊外部關聯與右邊外部關聯的聯集， 也就是兩邊的所有資料皆要出現於合併後的關聯中，彼此無法對應 上的部份，則另一邊關聯的所有屬性的屬性值皆會出現空值(Null Value)

51 在關聯式代數中，左邊外部合併的符號為，也就是 合併左右兩邊關聯，所以也是屬於二元運算子，倘 若有關聯R與S，則此兩關聯的左邊外部合併表示成
在關聯式代數中，左邊外部合併的符號為，也就是 合併左右兩邊關聯，所以也是屬於二元運算子，倘 若有關聯R與S，則此兩關聯的左邊外部合併表示成 R (條件情形)S 相對地，右邊外部關聯的表示符號為，完全外部關 聯的符號為，其分別表示成 R (條件情形)S R (條件情形)S

52 【範例5-10】 查詢所有員工承接的訂單資料，即使沒有承接任何訂單也 都要出現該員工的資料。 【說明】
在此範例中，有相關的關聯包括『員工』與『訂單』兩個關聯，並 且依據查詢的條件中，可直接使用左邊外部合併(Left Outer Join) 或右邊外部合併(Right Outer Join)來合併所需要的關聯，至於是 使用左或右，必須視其表示式中，兩個關聯放置於合併符號的左右 情形而訂。以此條件是以『員工』關聯為主，『訂單』關聯為輔， 若是將員工放置於合併符號的左邊，訂單放置於右邊，則必須使用 左邊外部合併；反之，若是將『員工』關聯放置於該符號右邊，訂 單放置於符號的左邊，則必須改用右邊外部合併。

53 【表示式】 左邊外部合併 員工 (員工.員工編號=訂單.員工編號)訂單 右邊外部合併 訂單 (員工.員工編號=訂單.員工編號)員工

54 員工 訂單 Null 員工的員工編號屬性值與訂單的員工編號屬性值做『相等』比較 圖5-17 『所有』員工承接的訂單情形

55 『遞迴封閉式操作』(Recursive Close Operations)
或稱之為『自我合併操作』 (Self-Join Operations) 屬於二元操作運算元 所使用的關聯卻只有一個

56 R R’ R” (a) (b) (c) (d) 圖5-18 遞迴封閉式操作示意圖

57 【範例5-11】 查詢員工和所屬上司之所有資料。 【說明】
在此範例中，有相關的關聯只有『員工』一個關聯，其中 的『報告人』自我參考到『員工編號』的屬性，如圖5- 18(c)，但此關聯卻要扮演兩個不同的角色，一個就是員 工，另一個就是上司角色，如圖5-18(d)的示意圖。

58 【表示式】 (Theta Join) (Outer Join) 員工關聯 = π (員工編號, 姓名, 職稱, 報告人)員工
上司關聯 = π (員工編號, 姓名, 職稱)員工 合併後關聯 = 員工關聯 (員工關聯.報告人 = 上司關聯.員工編號)上司關聯 (Outer Join) 合併後關聯 = 員工關聯 (員工關聯.報告人 = 上司關聯.員工編號)上司關聯

59 圖5-19 【範例5-11】之結果 (a) Theta Join之結果 (b) Outer Join之結果

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

61 5-4 集合論(Set Theory)操作 利用集合理論來對關聯進行不同的操作 這些操作包括
交集操作（Intersection Operation） 聯集操作（Union Operation） 差集操作（Difference Operation）

62 交集操作（Intersection Operation）
將兩個集合內，共同或相同的元素選取出來 以『集合論』的觀點而言，例如有兩個集合分別為R與S， 則此兩個集合的交集將表示成R∩S，會去除掉兩個集合中 的相同的重複元素，相同元素只會出現一次

63 R 宇集(Universal Set) S R S R∩S R∩S (a)以集合論的觀點 (b)以關聯的觀點 圖5-20 R∩S交集的圖示

64 【範例5-12】 倘若要從『客戶』與『供應商』的兩個關聯中，挑選出既 是客戶，同時又是供應商的公司相關資料，如公司名稱、 聯絡人以及地址，做為公司行銷上的參考資料。 【說明】 此查詢是要從『客戶』關聯中有的值組，同時在『供應商』關聯中 也有的共同值組挑選出來，也就是客戶中的『公司名稱』屬性和供 應商中的『供應商』屬性，具有相同屬性值的值組 【表示式】

65 圖5-21 客戶與供應商的交集結果

66 聯集操作（Union Operation）
將兩個集合內的元素合併在一起 以集合論的觀點而言，在兩個集合中均出現的元素，在經 過聯集操作之後，只會留下一個元素，而不會有重複元素 的情形 例如有兩個集合分別為R與S，則此兩個集合的聯集將表示 成R∪S，會去除掉兩個集合中相同的重複元素，相同元素 只會出現一次

67 宇集(Universal Set) R R S R∪S ← R∪S → S (a)以集合論的觀點 (b)以關聯的觀點

68 【範例5-13】 倘若公司要寄出邀請函，同時邀請客戶與供應商，並挑選 出公司名稱、聯絡人以及地址。 【說明】 【表示式】
此例的查詢，是要將兩個關聯做聯集處理，也因此在聯集後，會自 動將重複資料去除，僅留下一筆，可避免同一家公司重複收到相同 的邀請函 【表示式】

69 圖5-23 客戶與供應商的聯集結果

70 差集操作（Difference Operation）
差集操作（Set Difference Operation），表示成 ﹣ 將兩個集合內，去除掉與另一個集合相同的元素，僅留 下另一個集合沒有的元素 以『集合論』的觀點而言，例如有兩個集合分別為R與S， 則此兩個集合的差集將表示成 R-S 表示從集合R中，去除R與S共同的部份(也就是R∩S)，所剩餘的部 份就是R-S S-R 表示從集合S中，去除R與S共同的部份(也就是R∩S)，所剩餘的部 份就是S-R

71 R R S R∩S R-S S-R 宇集(Universal Set) R-S S R∩S S-R (a)以集合論的觀點 (b)以關聯的觀點

72 【範例5-14】 倘若要從客戶與供應商的兩個關聯中，挑選出 【說明】 【表示式】 (a)單純為客戶，不是供應商以及
(b)單純為供應商，不是客戶者 【說明】 (a)從客戶的關聯中，去除既為客戶又為供應商的資料 (b)從供應商的關聯中，去除既為供應商又為客戶的資料 【表示式】 (a) (b)

73 (b) 供應商 – 客戶 (a) 客戶 – 供應商 圖2-25 客戶與供應商的差集結果

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

75 5-5 聚合函數(Aggregate Functions)計算
針對關聯中某些屬性進行群組之後的計算，包括 計算加總的Sum()函數 計算平均的Average()函數 計算筆數的Count()函數 最大值的Max()函數 最小值的Min()函數 若是有個關聯名為R，則聚合運算的表示為 <群組屬性> <聚合函數表列>(R) <群組屬性> 關聯R中的屬性表列，用以分群組的一個依據 <聚合函數表列> 表示此處要使用哪一種聚合函數

76 【範例5-15】 依據每一筆訂單編號，計算出每一張訂單中所訂購產品的 總金額。 【說明】
此例的查詢，可以使用單一個『訂單明細』之關聯，先將 其中的屬性實際單價和數量進行相乘積之計算後，再依據 sum()聚合函數的加總計算，如下所表示 sum(實際單價×數量) 【表示式】 <訂單編號> sum(實際單價×數量)(訂單明細)

77 總金額 = (18×10+25×20+35×15) = 1205 總金額 = (20×9+15×6) = 270 圖5-26 【範例5-15】之結果

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

79 【範例5-16】 查詢供應商中，聯絡人為女董事長之供應商編號、供應商、 聯絡人以及聯絡人職稱。 【說明】
從需求中的四個輸出屬性，供應商編號、供應商、聯絡人和聯絡人 職稱，而這四個屬性皆可直接從供應商關聯中取得，所以可知本查 詢僅需要使用單一個『供應商』關聯即可達到查詢的需求。 在此查詢中必須要注意的是使用到投影(PROJECT)和選取(SELECT) 兩種操作，原則上此兩種操作是具有交換性，但此查詢少一個『聯 絡人性別』屬性，卻也因為在投影操作的部份沒有『聯絡人性別』 屬性，導致僅能先使用『選取操作』，再使用『投影操作』，兩者 操作順序不可互換

80 (表示式一) (表示式二) 選取操作後關聯 = σ性別=’女’ and 聯絡人職務=’董事長’ (供應商)
投影操作後關聯 = π供應商編號, 供應商, 聯絡人, 聯絡人職稱(選取操作後關聯) (表示式二) π供應商編號, 供應商, 聯絡人, 聯絡人職稱(σ性別=’ 女’ and聯絡人職務=’董事長’(供應商)) 圖5-27 【範例5-16】之結果

81 【範例5-17】 查詢員工編號小於8300000女性員工之員工編號、 姓名、職稱、性別和任用日期 【說明】
從此需求的輸出屬性可以很清楚瞭解到都是屬於員工關聯 中的屬性，所以是屬於單一關聯的操作即可 由於要查詢員工編號小於 且是女性員工屬於選取 操作，而投影操作中又有員工編號和性別之屬性，所以此 查詢不論是先選取操作或投影操作皆可

82 【表示式】 (表示式一) (表示式二) (表示式三) 選取操作後關聯 = σ員工編號 < 8300000 and 性別=’女’(員工)
投影操作後關聯 = π員工編號, 姓名, 職稱, 性別, 任用日期(選取操作後關聯) (表示式二) π員工編號, 姓名, 職稱, 性別, 任用日期(σ員工編號 < and 性別=’女’(員工)) (表示式三) σ員工編號 < and 性別=’女’(π員工編號, 姓名, 職稱, 性別, 任用日期(員工)) 圖5-28【範例5-17】之結果

83 【範例5-18】 查詢既是客戶又是供應商身份，並且其對應的聯絡 人身份為董事長之公司名稱、聯絡人和電話。 【說明】
從圖5-29中可以清楚看出客戶和供應商並不具有直接的 關聯性，而且其輸出的屬性在兩個關聯之中皆有其同義之 屬性，所以在客戶和供應商的兩個關聯中的資料可以使用 『交集操作』。

84 圖5-29 【範例5-18】之關聯圖

85 【表示式】 (表示式一) 投影操作後客戶關聯 = π公司名稱, 聯絡人, 電話 ( σ聯絡人職稱=’董事長’(客 戶) ) 投影操作後供應商關聯 = π供應商, 聯絡人, 電話 ( σ聯絡人職稱=’董事長’(供應 商) ) 交集操作後關聯 =投影操作後客戶關聯∩投影操作後供應商關聯 (表示式二) π公司名稱, 聯絡人, 電話 ( σ聯絡人職稱=’董事長’(客戶) ) ∩ π供應商, 聯絡人, 電話 ( σ聯絡人職稱=’董事長’(供應商) ) 圖5-30【範例5-18】之結果

86 【範例5-19】 查詢客戶與供應商的聯絡人之職稱為『董事長』的 所有資料，輸出為公司名稱、聯絡人和電話等屬性。 【說明】
此查詢與【查詢5-18】相似，只是查詢5-18是求兩者皆 有的資料，也就是取交集，而此查詢則是要將兩個關聯的 資料合併在一起，也就是『聯集操作』。

87 【表示式】 (表示式一) 投影操作後客戶關聯 =π公司名稱, 聯絡人, 電話 ( σ聯絡人職稱=’董事長’(客戶) )
投影操作後供應商關聯 =π供應商, 聯絡人, 電話 ( σ聯絡人職稱=’董事長’(供應 商) ) 聯集操作後關聯 =投影操作後客戶關聯∪投影操作後供應商關聯 (表示式二) π公司名稱, 聯絡人, 電話 ( σ聯絡人職稱=’董事長’(客戶) )∪π供應商, 聯絡人, 電話 ( σ 聯絡人職稱=’董事長’(供應商) )

88 圖5-31【範例5-19】之結果

89 【範例5-20】 查詢有提供產品名稱為『咖啡』的供應商，包括產 品編號、產品名稱、供應商編號和供應商等屬性。 【說明】
從此查詢的輸出需求為產品編號、產品名稱、供應商編號 和供應商等屬性，必須從產品資料與供應商兩個關聯方能 找出此查詢的相關資料，如圖5-32虛線內表示出供應商 與產品資料之間的關聯性，所以將此兩個關聯做合併處理 後，再進行選取操作和投影操作。以此需求，當然也可以 先進行選取操作和投影操作後，再進行合併處理的。

90 圖5-32 【範例5-20】之關聯圖

91 【表示式】 (表示式一) 合併後關聯 = 產品資料 (產品資料.供應商編號=供應商.供應商編號)供應商
合併後關聯 = 產品資料 (產品資料.供應商編號=供應商.供應商編號)供應商 選取操作後關聯 = σ產品資料.產品名稱=’咖啡’(合併後關聯) 投影操作後關聯 = π產品資料.產品編號, 產品資料.產品名稱, 供應商.供應商編號, 供應商. 供應商(選取操作後關聯) (表示式二) π產品資料.產品編號, 產品資料.產品名稱, 供應商.供應商編號, 供應商.供應商(σ產品資料.產品名 稱=’咖啡’(產品資料 (產品資料.供應商編號=供應商.供應商編號)供應商)) 圖5-33【範例5-20】之結果

92 【範例5-21】 查詢每張訂單的明細資料，包括訂單編號、訂貨日期、產品編號、產 品名稱、客戶編號和客戶之公司名稱。 【說明】
此查詢雖然輸出的屬性並不多，僅有訂單編號、訂貨日期、產品編號、產 品名稱、客戶編號和客戶之公司名稱，但這些的屬性分佈在客戶、訂單、 訂單明細和產品資料等四個關聯之中，如圖5-34虛線內的關聯：所以此查 詢必須針對此四個關聯進行合併後，再利用投影操作。在此處要特別注意 到，是否可先進行投影操作後再進行合併呢？若是先進行投影操作後，有 些屬性將會消失，導致在進行合併時會出問題，所以必須先進行合併操作 後再進行投影操作。 在合併時，我們必須要注意到的事，由於有四個關聯，而關聯之間皆有一 個關聯性(Relationship)，所以總共會有 4 – 1 = 3個關聯性的產生，也就 是要進行三次的合併動作。

93 圖5-34 【範例5-21】之關聯圖

94 【表示式】 (表示式一) 合併後關聯 = 產品資料 產品資料.產品編號=訂單明細.產品編號( 訂單明細 訂單明細.訂單編號=訂單.訂單編號( 訂單 訂單.客戶編號=客戶.客戶編號客戶 ) ) 投影操作後關聯 = π訂單.訂單編號, 訂單.訂貨日期 , 訂單明細.產品編號, 產品資料.產品 名稱, 訂單明細.客戶編號, 客戶.公司名稱(合併後關聯) (表示式二) π訂單.訂單編號, 訂單.訂貨日期 , 訂單明細.產品編號, 產品資料.產品名稱, 訂單明細.客戶編號, 客 戶.公司名稱(產品資料 產品資料.產品編號=訂單明細.產品編號( 訂單明細 訂 單明細.訂單編號=訂單.訂單編號( 訂單 訂單.客戶編號=客戶.客戶編號客戶 ) ))

95 圖5-35 【範例5-21】之結果

96 【範例5-22】 查詢每張訂單的相關資料，包括訂單編號、訂貨日期和產 品名稱等屬性。 【說明】
本查詢的輸出屬性僅有訂單編號、訂貨日期和產品名稱等三個屬性， 雖然訂單編號與訂貨日期可於『訂單』關聯中找到，而產品名稱可 於『產品資料』中找到，但依據圖5-36虛線內的關聯圖中，因為 訂單與訂單明細具有一關聯性，而訂單明細與產品資料也有一關聯 性，但訂單與產品資料之間卻沒有直接的關聯性，所以此查詢雖然 沒有屬性是歸屬於訂單明細內，但依據合併原理，仍然要使用到三 個關聯，訂單、訂單明細與產品資料。

97 圖5-36 【範例5-22】之關聯圖

98 【表示式】 (表示式一) 合併後關聯 = 產品資料 產品資料.產品編號=訂單明細.產品編號 ( 訂單明細 訂單明細.訂單編號=訂單.訂單編號 訂單 ) 投影操作後關聯 = π訂單.訂單編號, 訂單.訂貨日期, 產品資料.產品名稱 (合併後關 聯) (表示式二) π訂單.訂單編號, 訂單.訂貨日期, 產品資料.產品名稱 ( 產品資料 產品資料.產品編號= 訂單明細.產品編號 ( 訂單明細 訂單明細.訂單編號=訂單.訂單編號 訂單 ) )

99 圖5-37【範例5-22】之結果

100 【範例5-23】 查詢每位員工的上司資料，且該上司的職稱為業務經理之 資料，包括員工編號、員工的姓名、報告人的員工編號、 報告人的姓名。
【說明】 本查詢由於每位員工的上司也就是報告人，而報告人與員工都歸屬 於同一個關聯之中，所以此種查詢也稱之為遞迴封閉式操作 (Recursive Closure Operations)或是自我合併(Self Join)。 遞迴封閉式操作的處理方式是將同一個關聯扮演成兩個不同的角色， 一個扮演成員工本身，一個扮演成上司，再進行兩個關聯的合併處 理。但是此查詢條件中，還特別限制其上司的職稱為業務經理，所 以在表示式中，上司必須要具有職稱的屬性。

101 【表示式】 員工 = π員工編號, 姓名, 報告人(員工) 上司 = π員工編號, 姓名, 職稱, 報告人(員工)
遞迴封閉式操作後關聯 = 員工 員工.報告人=上司.員工編號 AND 職稱=’業務經 理’上司 投影操作後關聯 = π員工.員工編號, 員工.姓名, 上司.員工編號, 上司.姓名(遞迴封閉式 操作後關聯) 圖5-38 【範例5-23】之結果

102 【範例5-24】 查詢出所有訂單中，實際單價小於產品的建議單價之資料， 包括訂單編號、產品名稱，實際單價、建議單價和所負責 的員工姓名等屬性。 【說明】 在此查詢中所輸出的屬性，分別屬於員工、訂單、訂單明細和產品 資料四個關聯，如圖5-39虛線內所示，除了一般的合併之關聯性 之外，在此值得特別注意的是『實際單價小於產品的建議單價』， 此條件也等於限制了訂單明細與產品資料之間的關聯性除了兩個關 聯間的產品編號要相等之外，還要多一個訂單明細中的『實際單價』 要小於產品資料中的『建議單價』。

103 圖5-39 【範例5-24】之關聯圖

104 【表示式】 (表示式一) 合併後關聯 = 產品資料 產品資料.產品編號=訂單明細.產品編號(訂單明細 訂單明細.訂單編號=訂單.訂單編號(訂單 訂單.員工編號=員工.員工編號員工)) 選取後關聯 = σ訂單明細.實際單價 < 產品資料.建議單價 (合併後關聯) 投影後關聯 = π訂單.訂單編號, 產品資料.產品名稱, 訂單明細.實際單價, 產品資料.建議單 價, 員工.姓名 (選取後關聯) (表示式二) π訂單.訂單編號, 產品資料.產品名稱, 訂單明細.實際單價, 產品資料.建議單價, 員工.姓名 (σ訂單 明細.實際單價 < 產品資料.建議單價 (產品 資料產品資料.產品編號=訂單明細.產品編號 (訂單明細 訂單明細.訂單編號=訂單.訂單編號(訂單 訂單.員工編號=員工.員工編 號員工))))

105 圖5-40 【範例5-24】之結果

106 【範例5-25】 查詢出哪些產品的『庫存量』小於『安全存量』的相關資 料，包括產品編號、產品名稱、供應商編號和供應商等屬 性。 【說明】
由於庫存量與安全存量皆屬於產品資料，所以只要直接做比較即可， 但輸出的屬性還包括供應商編號和供應商，所以必須再與供應商關 聯做合併處理。 在操作的順序上，如果先使用合併後的關聯，再使用選取操作和投 影操作是最安全且沒問題的順序，倘若是先使用投影操作，如同前 述，必須要注意到是否有後續操作要使用到的屬性在投影操作過程 中消失了。

107 圖5-41 【範例5-25】之關聯圖

108 【表示式】 (表示式一) 合併後關聯 = 產品資料 產品資料.供應商編號=供應商.供應商編號供應商
合併後關聯 = 產品資料 產品資料.供應商編號=供應商.供應商編號供應商 選取操作後關聯 = σ產品資料.庫存量 < 產品資料.安全存量(合併後關聯) 投影操作後關聯 =π產品資料.產品編號, 產品資料.產品名稱, 產品資料.供應商編號, 供應 商.供應商(選取操作後關聯) (表示式二) π產品資料.產品編號, 產品資料.產品名稱, 產品資料.供應商編號, 供應商.供應商(σ庫存量<安全存 量(產品資料 產品資料.供應商編號=供應商.供應商編號供應商)) 圖5-42 【範例5-25】之結果

109 【範例5-26】 計算每一筆訂單的總金額，包括訂單編號、訂貨日期和總 金額等屬性。 【說明】
此範例是屬於聚合函數計算之模式，由於是計算『每一筆』訂單的 總金額，所以是以訂單的訂單編號為分群之依據，但是因為輸入屬 性還包括訂貨日期，系統並不會知道同一個訂單編號是否只會有一 個訂貨日期，所以應該要以訂單編號和訂貨日期兩者同時為群組屬 性，故此範例不同於【範例5-26】只要以一個訂單編號為群組即 可；在計算總金額之前，必須先將每一個產品的小計計算出，如小 計=實際單價×數量，再將同一張訂單的所有小計加總，成為 sum(實際單價×數量)。

110 圖5-43 【範例5-26】之關聯圖

111 【表示式】 (表示式一) 合併後關聯 = 訂單 訂單.訂單編號=訂單明細.訂單編號訂單明細
合併後關聯 = 訂單 訂單.訂單編號=訂單明細.訂單編號訂單明細 分群加總後關聯 =訂單.訂單編號,訂單.訂貨日期 sum(訂單明細.實 際單價 × 訂單明細.數量)( 合併後關聯) (表示式二) 訂單.訂單編號, 訂單.訂貨日期 sum(訂單明細.實際單價 × 訂單明細.數 量)(訂單 訂單.訂單編號=訂單明細.訂單編號訂單明細)

112 圖5-44 【範例5-26】之結果

113 【範例5-27】 計算每一位員工所承接的每一張訂單總金額，包括員工編 號、員工的姓名、訂單編號、總金額等屬性 【說明】
本範例與前一查詢有些差異，因為此查詢又多了一個員工資料，並 且先以每一位員工做群組，再依每一張訂單做群組。簡言之，就是 以員工編號、員工之姓名和訂單編號三個屬性分群計算，並使用到 員工、訂單及訂單明細三個關聯 但仔細看輸出的屬性還包括員工的姓名，所以必須要將此屬性也加 入群組之中，否則將會出現錯誤。

114 圖5-45 【範例5-27】之關聯圖

115 【表示式】 (表示式一) 合併後關聯 = 訂單明細 訂單明細.訂單編號=訂單.訂單編號 (員工 員工. 員工編號=訂單.員工編號訂單)
合併後關聯 = 訂單明細 訂單明細.訂單編號=訂單.訂單編號 (員工 員工. 員工編號=訂單.員工編號訂單) 分群加總後關聯 = (員工.員工編號, 員工.姓名, 訂單.訂單編號) sum(訂單明 細.實際單價 × 訂單明細.數量)( 合併後關聯) (表示式二) (員工.員工編號, 員工.姓名, 訂單.訂單編號) sum(訂單明細.實際單價 × 訂單明細.數 量)( 訂單明細訂單明細.訂單編號=訂單.訂單編號 (員工 員工.員工編號=訂單.員工編 號訂單))

116 圖5-46 【範例5-27】之結果

117 【範例5-28】 計算每位員工所承接訂單中，不同產品的總數量， 包括員工編號、員工的姓名、產品編號以及總數量 等屬性 【說明】
此查詢與【查詢5-27】所使用到的關聯相同，參考圖5- 45之關聯圖，而此查詢所要計算的總數量是以員工和產品 為主要分群，但以輸出的屬性而言，可以很清楚瞭解到群 組屬性為員工編號、員工的姓名和產品編號；聚合函數的 計算是使用sum(數量)。

118 【表示式】 (表示式一) 合併後關聯 = 訂單明細 訂單明細.訂單編號=訂單.訂單編號 (員工員工.員工編號 =訂單.員工編號訂單)
合併後關聯 = 訂單明細 訂單明細.訂單編號=訂單.訂單編號 (員工員工.員工編號 =訂單.員工編號訂單) 分群加總後關聯 = (員工.員工編號, 員工.姓名, 訂單明細.產品編號) sum(訂單明 細.數量)( 合併後關聯) (表示式二) (員工.員工編號, 員工.姓名, 訂單明細.產品編號) sum(訂單明細.數量) ( 訂單明細 訂單明細.訂單編號=訂單.訂單編號 (員工 員工.員工編號=訂單.員工編號訂單))

119 圖5-47 【範例5-28】之結果

120 【範例5-29】 計算出每一種產品類別被訂購的總數量，包括類別 編號、類別名稱、總數量等屬性。 【說明】
此範例的主要目標是產品類別的計算，但輸出資料還有類 別名稱，所以應該以類別編號和類別名稱為分群組之依據； 但是所計算的總數量是依據訂單明細中的數量之加總，所 以本查詢必須使用產品類別、產品資料和訂單明細三個關 聯進行合併處理之後，再以類別編號、類別名稱為群組屬 性。

121 圖5-48 【範例5-29】之關聯圖

122 【表示式】 (表示式一) 合併後關聯 = 訂單明細 訂單明細.產品編號=產品資料.產品編號(產 品資料 產品資料.類別編號=產品類別.類別編號產品類別) 聚合函數計算 = 產品類別.類別編號, 產品類別.類別名稱 sum(訂單明 細.數量)(合併後關聯) (表示式二) 產品類別.類別編號, 產品類別.類別名稱 sum(訂單明細.數量) ( 訂單明 細 訂單明細.產品編號=產品資料.產品編號(產品資料 產品資料.類別 編號=產品類別.類別編號產品類別))

123 圖5-49 【範例5-29】之結果

124 【範例5-30】 計算出產品類別編號小於5的所有產品，訂購的總 數量，包括產品類別、類別名稱，總數量。 【說明】
此範例與【範例5-29】幾乎是相同的處理分式，可參考 圖5-48之關聯圖，只是在聚合函數計算之前，必須先經 過選取操作，選取出符合產品類別編號小於5的產品，並 過濾掉不符合條件的資料之後，再進行聚合函數的sum(數 量)計算。

125 【表示式】 (表示式一) 合併後關聯 = 訂單明細 訂單明細.產品編號=產品資料.產品編號(產品資料 產品資料.類別編號=產品類別.類別編號產品類別) 選取操作後關聯 = σ產品類別.類別編號 < 5 (合併後關聯) 聚合函數計算 = 產品類別.類別編號, 產品類別.類別名稱 sum(訂單明細.數 量)( 選取操作後關聯) (表示式二) 產品類別.類別編號, 產品類別.類別名稱 sum(訂單明細.數量)( σ產品類別.類別編號 < 5 (訂單明細 訂單明細.產品編號=產品資料.產品編號(產品資料 產品資料.類別 編號=產品類別.類別編號產品類別))) 圖5-50 【範例5-30】之結果

126 【範例5-31】 計算出每位業務所承接訂單的總金額，包括員工編 號、姓名、總金額。 【說明】
本範例的主要計算是依據職務為『業務』之員工，並且輸 出又包括員工之姓名，所以在分群的屬性必須包括員工編 號和姓名兩個，再將所有承接訂單的總金額進行聚合函數 sum(實際單價×數量)的計算。 在關聯的部份可參考圖5-45之關聯圖，也就是本查詢所 使用的關聯包括員工、訂單和訂單明細三個。

127 【表示式】 (表示式一) 合併後關聯 = 訂單明細 訂單明細.訂單編號=訂單.訂單編號 ( 員工 員工. 員工編號=訂單.員工編號訂單 )
合併後關聯 = 訂單明細 訂單明細.訂單編號=訂單.訂單編號 ( 員工 員工. 員工編號=訂單.員工編號訂單 ) 選取操作後關聯 = σ員工.職稱=’業務’ ( 合併後關聯 ) 分群加總後關聯 = (員工.員工編號, 員工.姓名, 訂單.訂單編號) sum(訂單明細. 實際單價 ×訂單明細.數量) ( 選取操作後關聯 ) (表示式二) (員工.員工編號, 員工.姓名, 訂單.訂單編號) sum(訂單明細.實際單價 ×訂單明細.數量) ( σ員工.職稱=’業務’ ( 訂單明細 訂單明細.訂單編號=訂單.訂單編號 ( 員工 員工.員工編號=訂單.員工編號訂單 ) ) ) 圖5-51【範例5-31】之結果