第四章 合併理論 資料庫系統理論與實務 [邏輯思維系列]

Presentation on theme: "第四章 合併理論 資料庫系統理論與實務 [邏輯思維系列]"— Presentation transcript:

1 第四章 合併理論 資料庫系統理論與實務 [邏輯思維系列]
第四章 合併理論 資料庫系統理論與實務 [邏輯思維系列]

2 本章在架構中的位置 MS SQL Server 2005 理論與實作(一) (08)
My SQL Server 2005 理論與實作(二)(13) 回復技術(11) 結構化查詢語言 SQL(一)(06) 結構化查詢語言 SQL(二)(07) 與管理(12) 資料庫安全 關聯式代數(05) 並行控制(10) 關聯式模型(03) (正規化) 合併理論(04) 交易處理(09) 資料模型(02) 資料庫系統簡介(01) 邏輯與思維 2 /51

3 本章內容 4-1簡介 4-2卡氏積 4-3內部合併 4-4外部合併 4-5合併之間的比較 4-6不同的合併對應關係 4-7自我合併
3 /51

4 4-1簡介 『分分、合合』 正規化的目的 合併的目的 分：依據Codd博士所提出的正規化 合：合併理論 去除關聯異動的異常現象
透過『合併』(Join)過程得到一個虛擬關聯來達到查詢上的方便 4 /51

5 本章內容 4-1簡介 4-2卡氏積 4-3內部合併 4-4外部合併 4-5合併之間的比較 4-6不同的合併對應關係 4-7自我合併
5 /51

6 4-2卡氏積 『卡氏積』 (Cartesian Product) 也稱之為『交叉乘積』 (Cross Product)
或稱為『交叉合併』 (Cross Join) 6 /51

7 卡式積的表示方式 關聯R有m個屬性的集合(A1, A2, …, Am) 關聯S有n個屬性的集合(B1, B2, …, Bn) 可以分別表示成
R(A1, A2, …, Am) S(B1, B2, …, Bn) 卡氏積的操作中，兩個關聯R與S的乘積會 以『×』的符號來表示 R(A1、A2、…、Am) ×S(B1、B2、…、Bn) 簡寫成R × S 7 /51

8 關聯中的『值組』(Tuple) 關聯R中的值組(tuple)表示成 關聯S中的值組(tuple)表示成 t(R)
t(A1, A2, …, Am) 關聯S中的值組(tuple)表示成 t(S) t(B1, B2, …, Bn) 8 /51

9 卡式積範例(一) 倘若有兩個關聯，分別為R與S t(R)={t1(R), t2(R)} 且 t(S)={t1(S), t2(S), t3(S)} 則 R×S={ ( t1(R), t1(S) ), ( t1(R), t2(S) ),( t1(R), t3(S) ), ( t2(R), t1(S) ), ( t2(R), t2(S) ), ( t2(R), t3(S) ) } 示意圖如下一頁 9 /51

10 卡式積的示意圖範例(一) × t1(R) t1(S) t1(R) t2(R) 關聯R t1(S) t2(S) t3(S) 關聯S t2(S)
＝ t1(S) t2(R) t2(R) t2(S) t3(S) t2(R) 關聯R的屬性 關聯S的屬性 圖4-1 關聯R與S的卡氏積 10 /51

11 卡式積範例(二) 倘若有兩個關聯，分別為R與S t(R)={t1(R), t2(R), …, ti(R)} 且 t(S)={t1(S), t2(S), ..., tj(S)} 則 R×S=R(A1, A2, …, Am)×S(B1, B2, …, Bn) ={ ( t1(R), t1(S) ), ( t1(R), t2(S) ),…,( t1(R), tj(S) ), …… ( ti(R), t1(S) ), ( ti(R), t2(S) ),…, ( ti(R), tj(S) ) } 示意圖如下一頁 續下頁 11 /51

12 卡式積的示意圖範例(二) ……. ……. … × … 筆值組 m個屬性 i 筆值組 n個屬性 j m+n 個屬性 筆值組 i× j ＝
t1(A1,A2,…,Am) m個屬性 ……. t2(A1,A2,…,Am) ti(A1,A2,…,Am) i t1(B1,B2,…,Bn) t2(B1,B2,…,Bn) t3(B1,B2,…,Bn) 筆值組 n個屬性 ……. tj(B1,B2,…,Bn) j m+n 個屬性 t1(A1,A2,…,Am) t1(B1,B2,…,Bn) 筆值組 i× j t1(A1,A2,…,Am) t2(B1,B2,…,Bn) … × ＝ t1(A1,A2,…,Am) tj(B1,B2,…,Bn) t2(A1,A2,…,Am) t1(B1,B2,…,Bn) … ti(A1,A2,…,Am) tj(B1,B2,…,Bn) 圖4-2 關聯R與S的卡氏積和屬性關係 關聯R的屬性 關聯S的屬性 12 /51

13 卡式積的實際範例 關聯 員工×客戶 員工(員工代號, 姓名, 部門, 職稱) 客戶(負責人代號, 客戶代號, 地區代號)
員工(員工代號, 姓名, 部門, 職稱) × 客戶(負責人代號, 客戶代號, 地區代號) = (員工代號, 姓名, 部門, 職稱, 負責人代號, 客戶代號, 地區代號) 13 /51

14 關聯『員工』與『客戶』的值組 (a)關聯『員工』 (b)關聯『客戶』 圖4-3 (a) 合併原理之範例關聯 14 /51 員工代號 姓名
部門 職稱 00001 陳明明 業務部 經理 00002 林立人 研發部 主任 00003 劉銘船 專案經理 00004 趙子龍 專員 (a)關聯『員工』 負責人代號 客戶代號 地區代號 00002 C0005 A C0010 B 00003 C0020 C 00005 C0025 D (b)關聯『客戶』 圖4-3 (a) 合併原理之範例關聯 14 /51

15 員工×客戶的結果 圖4-3 (b) 卡氏積的結果 同1筆﹃員工﹄對應4筆不同﹃客戶﹄ 關聯『員工』的屬性 關聯『客戶』的屬性 15 /51
員工代號 姓名 部門 職稱 負責人代號 客戶代號 地區代號 00001 陳明明 業務部 經理 00002 C0005 A C0010 B 00003 C0020 C 00005 C0025 D 林立人 研發部 主任 劉銘船 專案經理 00004 趙子龍 專員 關聯『員工』的屬性 關聯『客戶』的屬性 15 /51

16 本章內容 4-1簡介 4-2卡氏積 4-3內部合併 4-4外部合併 4-5合併之間的比較 4-6不同的合併對應關係 4-7自我合併
16 /51

17 4-3內部合併 (Inner Join) 亦稱為『條件式合併』(Condition Join)
兩關聯之間的『條件限制』或稱為『對應』(Mapping)關係 『內部合併』講究的是兩個關聯之間 必須有相對應的屬性 彼此做一對應關係或比較關係 『比較關係』 (Comparison Relationship) 包括 ＝、!=、>、>=、<、<= 17 /51

18 內部合併(概念說明圖) 關聯A的屬性 關聯B的屬性 關聯A和B相等的屬性 關聯A 合併後 關聯B (a)合併前的示意圖 (b)合併後的示意圖
圖4-4 內部合併(Inner Join) 18 /51

19 內部合併(具體說明圖) (a)關聯『員工』 1筆對應2筆 (b)關聯『客戶』 內部合併( Inner Join )後 關聯『員工』的屬性
姓名 部門 職稱 員工代號 陳明明 業務部 經理 00001 趙子龍 專員 00004 林立人 研發部 主任 00002 劉銘船 專案經理 00003 (a)關聯『員工』 1筆對應2筆 00002 C0005 A C0010 B 00003 C0020 C 00005 C0025 D 負責人代號 客戶代號 地區代號 (b)關聯『客戶』 內部合併( Inner Join )後 姓名 部門 職稱 員工代號 負責人代號 客戶代號 地區代號 林立人 研發部 主任 00002 C0005 A C0010 B 劉銘船 專案經理 00003 C0020 C 關聯『員工』的屬性 關聯『客戶』的屬性 圖4-5 內部合併(Inner Join)範例 19 /51

20 內部合併包含於卡式積內 Inner Join 圖4-6 (a) 值組的包含關係 關聯『員工』的屬性 關聯『客戶』的屬性 20 /51
員工代號 姓名 部門 職稱 負責人代號 客戶代號 地區代號 00001 陳明明 業務部 經理 00002 C0005 A C0010 B 00003 C0020 C 00005 C0025 D 林立人 研發部 主任 劉銘船 專案經理 00004 趙子龍 專員 Inner Join 關聯『員工』的屬性 關聯『客戶』的屬性 20 /51

21 卡式積 / 交叉乘積 / 交叉合併 (Cartesian Product / Cross Product / Cross Join)
內部合併 / 條件式合併 ( Inner Join / Condition Join) 圖4-6 (b) 包含的示意關係圖 21 /51

22 內部合併後(圖4-5)消失的值組 關聯『員工』 關聯『客戶』 為何消失不見？ 合理嗎？
{ ( 0001, 陳明明, 業務部, 經理) , ( 0004, 趙子龍, 業務部, 專員) } 關聯『客戶』 { ( 00005, C0025, D) } 為何消失不見？ 彼此對應不到 合理嗎？ 視需求而定 22 /51

23 本章內容 4-1簡介 4-2卡氏積 4-3內部合併 4-4外部合併 4-5合併之間的比較 4-6不同的合併對應關係 4-7自我合併
23 /51

24 4-4 外部合併(Outer Join) 外部合併(Outer Join)主要可分為三種 左邊外部合併 (Left Outer Join)
右邊外部合併 (Right Outer Join) 完全外部合併 (Full Outer Join) 24 /51

25 左邊外部合併(Left Outer Join)
以左邊的關聯為主要關聯 合併後的值組 包括兩者關聯彼此能互相對應的值組 包括左邊關聯未能對應到右邊關聯的其他值組 對應不到右邊關聯的值組，會在右邊關聯中的屬性填入空值(Null Value) 25 /51

26 左邊外部合併(概念說明圖) 關聯A 關聯B (a)合併前的示意圖 Null Value 關聯A 關聯B (b)合併後的示意圖 合併後
圖4-7 左邊外部合併 26 /51

27 左邊外部合併(具體說明圖) Null Value (a)關聯『員工』 (b)關聯『客戶』
姓名 部門 職稱 員工代號 陳明明 業務部 經理 00001 趙子龍 專員 00004 林立人 研發部 主任 00002 劉銘船 專案經理 00003 (a)關聯『員工』 Null Value 00002 C0005 A C0010 B 00003 C0020 C 00005 C0025 D 負責人代號 客戶代號 地區代號 (b)關聯『客戶』 左邊外部合併( Left Outer Join )後 圖4-8 左邊外部合併範例 姓名 部門 職稱 員工代號 負責人代號 客戶代號 地區代號 陳明明 業務部 經理 00001 Null 趙子龍 專員 00004 林立人 研發部 主任 00002 C0005 A C0010 B 劉銘船 專案經理 00003 C0020 C Inner Join 關聯『員工』的屬性 關聯『客戶』的屬性 27 /51

28 右邊外部合併(Right Outer Join)
以右邊的關聯為主要關聯 合併後的值組 包括兩者關聯彼此能互相對應的值組 包括右邊關聯未能對應到左邊關聯的其他值組 對應不到左邊關聯的值組，會在左邊關聯中的屬性填入空值(Null Value) 28 /51

29 右邊外部合併(概念說明圖) 關聯A 關聯B (a)合併前的示意圖 Null Value 關聯A 關聯B (b)合併後的示意圖 合併後
圖4-9 右邊外部合併 29 /51

30 右邊外部合併(具體說明圖) Null Value (a)關聯『員工』 (b)關聯『客戶』
姓名 部門 職稱 員工代號 陳明明 業務部 經理 00001 趙子龍 專員 00004 林立人 研發部 主任 00002 劉銘船 專案經理 00003 (a)關聯『員工』 Null Value 00002 C0005 A C0010 B 00003 C0020 C 00005 C0025 D 負責人代號 客戶代號 地區代號 (b)關聯『客戶』 右邊外部合併( Right Outer Join )後 圖4-10右邊外部合併範例 姓名 部門 職稱 員工代號 負責人代號 客戶代號 地區代號 林立人 研發部 主任 00002 C0005 A C0010 B 劉銘船 專案經理 00003 C0020 C Null 00005 C0025 D Inner Join 關聯『員工』的屬性 關聯『客戶』的屬性 30 /51

31 三種合併範例之語意說明 內部合併 左邊外部合併 右邊外部合併 『列出有負責客戶的員工及所負責客戶之資料』
『列出所有員工以及所負責客戶的全部資料』 右邊外部合併 『列出所有客戶，以及所負責的員工資料』 31 /51

32 完全外部合併(概念說明圖) 關聯A 關聯B (a)合併前的示意圖 Null Value 關聯A 關聯B (b)合併後的示意圖 合併後
圖4-11 完全外部合併 32 /51

33 完全外部合併(具體說明圖) Null Value (a)關聯『員工』 (b)關聯『客戶』 完全外部合併(Full Outer Join )後
姓名 部門 職稱 員工代號 陳明明 業務部 經理 00001 趙子龍 專員 00004 林立人 研發部 主任 00002 劉銘船 專案經理 00003 (a)關聯『員工』 Null Value 00002 C0005 A C0010 B 00003 C0020 C 00005 C0025 D 負責人代號 客戶代號 地區代號 (b)關聯『客戶』 完全外部合併(Full Outer Join )後 圖4-12 完全外部合併範例 姓名 部門 職稱 員工代號 負責人代號 客戶代號 地區代號 陳明明 業務部 經理 00001 Null 趙子龍 專員 00004 林立人 研發部 主任 00002 C0005 A C0010 B 劉銘船 專案經理 00003 C0020 C 00005 C0025 D Left Join Inner Join Right Join 關聯『員工』的屬性 關聯『客戶』的屬性 33 /51

34 內部合併與外部合併 完全外部合併 ( Full Join ) 左邊外部合併 ( Left Outer Join )
右邊外部合併 ( Right Outer Join ) 內部合併 ( Inner Join ) 圖4-13 內部合併與外部合併 34 /51

35 本章內容 4-1簡介 4-2卡氏積 4-3內部合併 4-4外部合併 4-5合併之間的比較 4-6不同的合併對應關係 4-7自我合併
35 /51

36 4-5 合併之間的比較 (d) (b) (a) (c) Cross Join Full Outer Join Left Outer Join
Right Outer Join Inner Join 圖4-14 所有合併之間的包含關係 36 /51

37 各部份說明(一) ( a ) ( b ) ( c ) ( d ) 『內部合併』(Inner Join)。
：左邊關聯中的某些(一個或多個)屬性值，無法對應到右邊相對應的屬性值的值組。 ( c ) 右邊關聯中的某些(一個或多個)屬性值，無法對應到左邊相對應的屬性值的值組。 ( d ) 左、右兩邊關聯的某些(一個或多個)相對應的屬性值彼此無法『對應』 (Mapping)的部份，但是在合併後的左、右兩邊屬性皆會有值存在。 37 /51

38 各部份說明(二) ( a ) + ( b ) ( a ) + ( c ) ( a ) + ( b ) + ( c )
『左邊外部合併』 (Left Outer Join)。 ( a ) + ( c ) 『右邊外部合併』 (Right Outer Join)。 ( a ) + ( b ) + ( c ) 『完全外部合併』 (Full Outer Join) ( a ) + ( d ) 『卡氏積』 (Cartesian Product)。 38 /51

39 本章內容 4-1簡介 4-2卡氏積 4-3內部合併 4-4外部合併 4-5合併之間的比較 4-6不同的合併對應關係 4-7自我合併
39 /51

40 4-6 不同的合併對應關係 『對應關係』並非只有『等於』的一種對應關係 其他『比較運算子』亦有其使用時機和必要
大於、小於、大於或等於、小於或等於、不等於 40 /51

41 非『相等』的『對應關係』範例 例如有兩個關聯，其一為『訂單』，此訂單中僅會有一筆產品資料，對應到另一關聯為『產品』
倘若要找到『訂單』關聯中的產品銷售之單價小於『產品』關聯中訂價之值組，此時的兩個關聯的對應關係則為 『訂單』關聯中的產品編號 『等於』 『產品』關聯中的產品編號 『訂單』關聯中的單價 『小於』『產品』關聯中的訂價 41 /51

42 『訂單』與『產品』關聯 『訂單』 『產品』 單價 < 訂價 (小於關係) 訂單.產品編號 = 產品.產品編號 (相等關係)
訂單編號 經手人 產品編號 單價 00001 陳明明 P001 30,000 00002 劉銘銘 24,000 00003 林森木 P002 12,000 00004 蔡元圓 1,5000 00005 何璧珠 P003 18,000 『訂單』 訂單.產品編號 = 產品.產品編號 (相等關係) 產品編號 訂價 產品名稱 P001 30,000 冷氣 P002 1,5000 冰箱 P003 20,000 洗衣機 P004 9,000 微波爐 P005 850 電風扇 圖4-15 訂單與產品 『產品』 42 /51

43 圖4-16 『小於』的關係 內部合併後 訂單編號 經手人 產品編號 單價 00001 陳明明 P001 30,000 00004 蔡元圓
1,5000 00002 劉銘銘 00003 林森木 00005 何璧珠 P001 24,000 < 30,000 冷氣 P002 12,000 < 15,000 冰箱 P003 18,000 < 20,000 洗衣機 P004 9,000 微波爐 P005 850 電風扇 產品編號 訂價 產品名稱 內部合併後 圖4-16 『小於』的關係 訂單編號 經手人 產品編號 單價 訂價 產品名稱 00002 劉銘銘 P001 24,000 30,000 冷氣 00003 林森木 P002 12,000 15,000 冰箱 00005 何璧珠 P003 18,000 20,000 洗衣機 43 /51

44 本章內容 4-1簡介 4-2卡氏積 4-3內部合併 4-4外部合併 4-5合併之間的比較 4-6不同的合併對應關係 4-7自我合併
44 /51

45 4-7 自我合併 只會有一個實際的關聯存在 在合併時卻會將此一關聯當成兩個不同的關聯(以所扮演的角色來區分)來看待 45 /51

46 兩個不同關聯的查詢(分解動作) 續下頁 (a)關聯『員工』 (b)關聯『客戶』 圖4-17 內部合併的過程 46 /51 員工代號 姓名
部門 職稱 00001 陳明明 業務部 經理 00002 林立人 研發部 主任 00003 劉銘船 專案經理 00004 趙子龍 專員 (a)關聯『員工』 負責人代號 客戶代號 地區代號 00002 C0005 A C0010 B 00003 C0020 C 00005 C0025 D (b)關聯『客戶』 圖4-17 內部合併的過程 續下頁 46 /51

47 員工 客戶 員工代號 姓名 部門 職稱 負責人代號 客戶代號 地區代號 47 /51

48 單一個關聯的查詢(分解動作) 續下頁 圖4-18 自我合併的查詢過程 48 /51 員工編號 姓名 職稱 報告人 8111131 陳明明
總經理 Null 黃謙仁 工程師 林其達 工程助理 陳森耀 工程協理 徐沛汶 業務助理 劉逸萍 業務 朱辛傑 業務協理 胡琪偉 吳志梁 林美滿 業務經理 劉嘉雯 張懷甫 張若蘭 續下頁 圖4-18 自我合併的查詢過程 48 /51

49 角色扮演 員工 員工(上司) 員工編號 姓名 職稱 報告人 49 /51

50 自我合併+內部合併 圖4-19 (a) 自我合併的內部合併 員工 上司 50 /51 員工編號 姓名 職稱 報告人 8111261 黃謙仁
工程師 陳森耀 工程協理 林其達 工程助理 陳明明 總經理 Null 徐沛汶 業務助理 張懷甫 業務經理 劉逸萍 業務 林美滿 朱辛傑 業務協理 胡琪偉 吳志梁 劉嘉雯 張若蘭 員工 上司 50 /51

51 自我合併+外部合併 圖4-19 (b) 自我合併的左邊外部合併 員工 上司 51 /51 員工編號 姓名 職稱 報告人 8111131
陳明明 總經理 Null 黃謙仁 工程師 陳森耀 工程協理 林其達 工程助理 徐沛汶 業務助理 張懷甫 業務經理 劉逸萍 業務 林美滿 朱辛傑 業務協理 胡琪偉 吳志梁 劉嘉雯 張若蘭 員工 上司 51 /51