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

資料庫系統理論與實務　陳祥輝著

©2007 版權所有，翻印必究

第四章合併理論

資料庫系統理論與實務 [ 邏輯思維系列 ]

2 /51


©2007 版權所有，翻印必究第四章合併理論

邏輯與思維

資料庫系統簡介 (01)

資料模型 (02)

關聯式模型 (03)( 正規化 )

合併理論 (04)

關聯式代數 (05)

本章在架構中的位置

結構化查詢語言SQL( 一 )(06)

結構化查詢語言SQL( 二 )(07)

交易處理 (09)

並行控制 (10)

回復技術 (11)

MS SQL Server 2005理論與實作 ( 一 ) (08)

資料庫安全

與管理(12)

My SQL Server 2005理論與實作 ( 二 )(13)

3 /51



本章內容

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

4 /51



4-1 簡介

• 『分分、合合』 – 分：依據 Codd 博士所提出的正規化 – 合：合併理論

• 正規化的目的– 去除關聯異動的異常現象

• 合併的目的– 透過『合併』 (Join) 過程得到一個虛擬關聯來達到查詢上的方便

5 /51



本章內容

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

6 /51



4-2 卡氏積

• 『卡氏積』 (Cartesian Product)– 也稱之為『交叉乘積』 (Cross Product)

– 或稱為『交叉合併』 (Cross Join)

7 /51



卡式積的表示方式

• 關聯 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

8 /51



關聯中的『值組』 (Tuple)

• 關聯 R 中的值組 (tuple) 表示成– t(R)

– t(A1, A2, …, Am)

• 關聯 S 中的值組 (tuple) 表示成– t(S)

– t(B1, B2, …, Bn)

9 /51



卡式積範例 ( 一 )

• 倘若有兩個關聯，分別為 R 與 St(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) ) }

• 示意圖如下一頁

10 /51



卡式積的示意圖範例 ( 一 )

t1(R) t1(S)

t2(R) t2(S)

t3(S)t1(R)

t1(S)t2(R)

t2(S)t1(R)

t3(S)t2(R)

關聯 RR 的屬性

關聯 SS 的屬性

× ＝t1(R)

t2(R)

關聯 RR

t1(S)

t2(S)

t3(S)

關聯 SS

圖 4-1 關聯 R 與 S 的卡氏積

11 /51



卡式積範例 ( 二 )

• 倘若有兩個關聯，分別為 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) ) }

• 示意圖如下一頁續下頁

12 /51



× ＝

m+n 個屬性

關聯 RR 的屬性

關聯 SS 的屬性

筆值

組

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

…t1(A1,A2,…,Am) t1(B1,B2,…,Bn)

t1(A1,A2,…,Am) t2(B1,B2,…,Bn)

ti(A1,A2,…,Am) tj(B1,B2,…,Bn)

筆值組

i× j

t1(A1,A2,…,Am) tj(B1,B2,…,Bn)

t2(A1,A2,…,Am) t1(B1,B2,…,Bn)

…

卡式積的示意圖範例 ( 二 )

圖 4-2 關聯 R 與 S 的卡氏積和屬性關係

13 /51



卡式積的實際範例

• 關聯– 員工 ( 員工代號 , 姓名 , 部門 , 職稱 )

– 客戶 ( 負責人代號 , 客戶代號 , 地區代號 )

• 員工 × 客戶– 員工 ( 員工代號 , 姓名 , 部門 , 職稱 ) × 客戶

( 負責人代號 , 客戶代號 , 地區代號 ) = ( 員工代號 , 姓名 , 部門 , 職稱 , 負責人代號 , 客戶代號 , 地區代號 )

14 /51



關聯『員工』與『客戶』的值組

員工代號姓名部門職稱

00001 陳明明業務部經理

00002 林立人研發部主任

00003 劉銘船研發部專案經理

00004 趙子龍業務部專員

負責人代號客戶代號地區代號

00002 C0005 A

00002 C0010 B

00003 C0020 C

00005 C0025 D

(a) 關聯『員工』

(b) 關聯『客戶』

圖 4-3 (a) 合併原理之範例關聯

15 /51



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

00001 陳明明業務部經理 00002 C0005 A

00001 陳明明業務部經理 00002 C0010 B

00001 陳明明業務部經理 00003 C0020 C

00001 陳明明業務部經理 00005 C0025 D

00002 林立人研發部主任 00002 C0005 A

00002 林立人研發部主任 00002 C0010 B

00002 林立人研發部主任 00003 C0020 C

00002 林立人研發部主任 00005 C0025 D

00003 劉銘船研發部專案經理 00002 C0005 A

00003 劉銘船研發部專案經理 00002 C0010 B

00003 劉銘船研發部專案經理 00003 C0020 C

00003 劉銘船研發部專案經理 00005 C0025 D

00004 趙子龍業務部專員 00002 C0005 A

00004 趙子龍業務部專員 00002 C0010 B

00004 趙子龍業務部專員 00003 C0020 C

00004 趙子龍業務部專員 00005 C0025 D

同1

筆﹃員工﹄對應4

筆不同﹃客戶﹄

關聯『員工』的屬性關聯『客戶』的屬性

員工 × 客戶的結果圖 4-3 (b) 卡氏積的結果

16 /51



本章內容

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

17 /51



4-3 內部合併 (Inner Join)

• 亦稱為『條件式合併』 (Condition Join)• 兩關聯之間的『條件限制』或稱為『對應』

(Mapping) 關係• 『內部合併』講究的是兩個關聯之間

– 必須有相對應的屬性– 彼此做一對應關係或比較關係

• 『比較關係』 (Comparison Relationship) – 包括＝、 != 、 > 、 >= 、 < 、 <=

18 /51



關聯 A

關聯 B 合併後

(b) 合併後的示意圖(a) 合併前的示意圖

關聯 A 的屬性關聯 B 的屬性

關聯 A 和 B 相等的屬性

內部合併 ( 概念說明圖 )

圖 4-4 內部合併 (Inner Join)

19 /51



姓名部門職稱員工代號

陳明明業務部經理 00001

趙子龍業務部專員 00004

林立人研發部主任 00002


劉銘船研發部專案經理 00003

00002 C0005 A

00002 C0010 B

00003 C0020 C

00005 C0025 D


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

林立人研發部主任 00002 00002 C0005 A

林立人研發部主任 00002 00002 C0010 B

劉銘船研發部專案經理 00003 00003 C0020 C

內部合併 ( Inner Join )後


1

筆對應2

筆



內部合併 ( 具體說明圖 )

圖 4-5 內部合併 (Inner Join) 範例

20 /51



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

00001 陳明明業務部經理 00002 C0005 A

00001 陳明明業務部經理 00002 C0010 B

00001 陳明明業務部經理 00003 C0020 C

00001 陳明明業務部經理 00005 C0025 D

00002 林立人研發部主任 00002 C0005 A

00002 林立人研發部主任 00002 C0010 B

00002 林立人研發部主任 00003 C0020 C

00002 林立人研發部主任 00005 C0025 D

00003 劉銘船研發部專案經理 00002 C0005 A

00003 劉銘船研發部專案經理 00002 C0010 B

00003 劉銘船研發部專案經理 00003 C0020 C

00003 劉銘船研發部專案經理 00005 C0025 D

00004 趙子龍業務部專員 00002 C0005 A

00004 趙子龍業務部專員 00002 C0010 B

00004 趙子龍業務部專員 00003 C0020 C

00004 趙子龍業務部專員 00005 C0025 D


InnerJoin

圖 4-6 (a) 值組的包含關係

內部合併包含於卡式積內

21 /51



卡式積 / 交叉乘積 / 交叉合併(Cartesian Product / Cross Product / Cross Joi

n)

內部合併 / 條件式合併( Inner Join / Condition Join)

圖 4-6 (b) 包含的示意關係圖

22 /51



內部合併後 ( 圖 4-5) 消失的值組

• 關聯『員工』– { ( 0001, 陳明明 , 業務部 , 經理 ) ,

( 0004, 趙子龍 , 業務部 , 專員 ) }

• 關聯『客戶』– { ( 00005, C0025, D) }

• 為何消失不見？– 彼此對應不到

• 合理嗎？– 視需求而定

23 /51



本章內容

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

24 /51



4-4 外部合併 (Outer Join)

• 外部合併 (Outer Join) 主要可分為三種– 左邊外部合併 (Left Outer Join)

– 右邊外部合併 (Right Outer Join)

– 完全外部合併 (Full Outer Join)

25 /51



左邊外部合併 (Left Outer Join)

• 以左邊的關聯為主要關聯• 合併後的值組

– 包括兩者關聯彼此能互相對應的值組– 包括左邊關聯未能對應到右邊關聯的其他值組– 對應不到右邊關聯的值組，會在右邊關聯中的屬性填入空值 (Null Value)

26 /51



合併後

Null Value

關聯 A

關聯 B

(b) 合併後的示意圖

關聯 A

關聯 B

(a) 合併前的示意圖

左邊外部合併 ( 概念說明圖 )

圖 4-7 左邊外部合併

27 /51









00002 C0005 A

00002 C0010 B

00003 C0020 C

00005 C0025 D



陳明明業務部經理 00001 Null Null Null

趙子龍業務部專員 00004 Null Null Null

林立人研發部主任 00002 00002 C0005 A

林立人研發部主任 00002 00002 C0010 B


左邊外部合併 ( Left Outer Join )後


Null Value

Inner JoinInner Join



左邊外部合併 ( 具體說明圖 )

圖 4-8 左邊外部合併範例

28 /51



右邊外部合併 (Right Outer Join)

• 以右邊的關聯為主要關聯• 合併後的值組

– 包括兩者關聯彼此能互相對應的值組– 包括右邊關聯未能對應到左邊關聯的其他值組– 對應不到左邊關聯的值組，會在左邊關聯中的屬性填入空值 (Null Value)

29 /51



右邊外部合併 ( 概念說明圖 )

合併後Null

Value

關聯 A

關聯 B


關聯 A

關聯 B

(a) 合併前的示意圖圖 4-9 右邊外部合併

30 /51









00002 C0005 A

00002 C0010 B

00003 C0020 C

00005 C0025 D



林立人研發部主任 00002 00002 C0005 A

林立人研發部主任 00002 00002 C0010 B


Null Null Null Null 00005 C0025 D

右邊外部合併 ( Right Outer Join )後


Null Value




右邊外部合併 ( 具體說明圖 )

圖 4-10 右邊外部合併範例

31 /51



三種合併範例之語意說明

• 內部合併– 『列出有負責客戶的員工及所負責客戶之資料』

• 左邊外部合併– 『列出所有員工以及所負責客戶的全部資料』

• 右邊外部合併– 『列出所有客戶，以及所負責的員工資料』

32 /51



完全外部合併 ( 概念說明圖 )

合併後

Null Value

Null Value

關聯 A

關聯 B


關聯 A

關聯 B

(a) 合併前的示意圖圖 4-11 完全外部合併

33 /51









00002 C0005 A

00002 C0010 B

00003 C0020 C

00005 C0025 D



陳明明業務部經理 00001 Null Null Null

趙子龍業務部專員 00004 Null Null Null

林立人研發部主任 00002 00002 C0005 A

林立人研發部主任 00002 00002 C0010 B


Null Null Null Null 00005 C0025 D

完全外部合併 (Full Outer Join )後



Right JoinRight Join

Left JoinLeft Join



Null Value

Null Value

圖 4-12 完全外部合併範例

完全外部合併 ( 具體說明圖 )

34 /51



完全外部合併( Full Join )

左邊外部合併( Left Outer Join

)

右邊外部合併( Right Outer Join )

內部合併與外部合併

內部合併( Inner Join )

圖 4-13 內部合併與外部合併

35 /51



本章內容

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

36 /51



4-5 合併之間的比較

(a)

(d)

(b) (c)

Inner JoinRight Outer Join

Left Outer Join

Full Outer Join Cross Join

圖 4-14 所有合併之間的包含關係

37 /51



各部份說明 ( 一 )

• ( a ) – 『內部合併』 (Inner Join) 。

• ( b )– ：左邊關聯中的某些 ( 一個或多個 ) 屬性值，無法對應到右邊相對應的屬性值的值組。

• ( c )– 右邊關聯中的某些 ( 一個或多個 ) 屬性值，無法對應到左邊相對應的屬性值的值組。

• ( d )– 左、右兩邊關聯的某些 ( 一個或多個 ) 相對應的屬性值彼此無法『對應』 (Mapping) 的部份，但是在合併後的左、右兩邊屬性皆會有值存在。

38 /51



各部份說明 ( 二 )

• ( a ) + ( b ) – 『左邊外部合併』 (Left Outer Join) 。

• ( a ) + ( c ) – 『右邊外部合併』 (Right Outer Join) 。

• ( a ) + ( b ) + ( c )– 『完全外部合併』 (Full Outer Join)

• ( a ) + ( d ) – 『卡氏積』 (Cartesian Product) 。

39 /51



本章內容

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

40 /51



4-6 不同的合併對應關係

• 『對應關係』並非只有『等於』的一種對應關係

• 其他『比較運算子』亦有其使用時機和必要– 大於、小於、大於或等於、小於或等於、不等於

41 /51



非『相等』的『對應關係』範例

• 例如有兩個關聯，其一為『訂單』，此訂單中僅會有一筆產品資料，對應到另一關聯為『產品』

• 倘若要找到『訂單』關聯中的產品銷售之單價小於『產品』關聯中訂價之值組，此時的兩個關聯的對應關係則為– 『訂單』關聯中的產品編號『等於』『產品』關聯中的產品編號

– 『訂單』關聯中的單價『小於』『產品』關聯中的訂價

42 /51



『訂單』與『產品』關聯

訂單編號經手人產品編號單價00001 陳明明 P001 30,000

00002 劉銘銘 P001 24,000

00003 林森木 P002 12,000

00004 蔡元圓 P002 1,5000

00005 何璧珠 P003 18,000

產品編號訂價產品名稱P001 30,000 冷氣P002 1,5000 冰箱P003 20,000 洗衣機

P004 9,000 微波爐

P005 850 電風扇

『訂單』

『產品』

單價 < 訂價(小於關係 )

訂單 .產品編號 = 產品 .產品編號

( 相等關係 )

圖 4-15 訂單與產品

43 /51



訂單編號經手人產品編號單價

00001 陳明明 P001 30,000

00004 蔡元圓 P002 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 電風扇

產品編號訂價產品名稱

訂單編號經手人產品編號單價產品編號訂價產品名稱

00002 劉銘銘 P001 24,000 P001 30,000 冷氣

00003 林森木 P002 12,000 P002 15,000 冰箱

00005 何璧珠 P003 18,000 P003 20,000 洗衣機

內部合併後圖 4-16 『小於』的關係

44 /51



本章內容

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

45 /51



4-7 自我合併

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

46 /51



員工代號姓名部門職稱

00001 陳明明業務部經理

00002 林立人研發部主任

00003 劉銘船研發部專案經理

00004 趙子龍業務部專員


00002 C0005 A

00002 C0010 B

00003 C0020 C

00005 C0025 D



兩個不同關聯的查詢 ( 分解動作 )

圖 4-17 內部合併的過程續下頁

47 /51



員工客戶

員工代號

姓名

部門

職稱

負責人代號

客戶代號

地區代號

48 /51



單一個關聯的查詢 ( 分解動作 )員工編號姓名職稱報告人

8111131 陳明明總經理 Null

8111261 黃謙仁工程師 8201141

8112061 林其達工程助理 8111261

8201141 陳森耀工程協理 8111131

8203161 徐沛汶業務助理 8312261

8205231 劉逸萍業務 8308271

8209241 朱辛傑業務協理 8111131

8210171 胡琪偉業務 8308271

8307021 吳志梁業務 8308271

8308271 林美滿業務經理 8209241

8311051 劉嘉雯業務 8308271

8312261 張懷甫業務經理 8209241

8411151 張若蘭業務助理 8312261

圖 4-18 自我合併的查詢過程

續下頁

49 /51



員工員工 ( 上司 )

員工編號

姓名

職稱

報告人

員工編號

姓名

職稱

報告人

角色扮演

50 /51



自我合併 + 內部合併

員工編號姓名職稱報告人員工編號姓名職稱報告人

8111261 黃謙仁工程師 8201141 8201141 陳森耀工程協理 8111131

8112061 林其達工程助理 8111261 8111261 黃謙仁工程師 8201141

8201141 陳森耀工程協理 8111131 8111131 陳明明總經理 Null

8203161 徐沛汶業務助理 8312261 8312261 張懷甫業務經理 8209241

8205231 劉逸萍業務 8308271 8308271 林美滿業務經理 8209241

8209241 朱辛傑業務協理 8111131 8111131 陳明明總經理 Null

8210171 胡琪偉業務 8308271 8308271 林美滿業務經理 8209241

8307021 吳志梁業務 8308271 8308271 林美滿業務經理 8209241

8308271 林美滿業務經理 8209241 8209241 朱辛傑業務協理 8111131

8311051 劉嘉雯業務 8308271 8308271 林美滿業務經理 8209241

8312261 張懷甫業務經理 8209241 8209241 朱辛傑業務協理 8111131

8411151 張若蘭業務助理 8312261 8312261 張懷甫業務經理 8209241

員工上司

圖 4-19 (a) 自我合併的內部合併

51 /51



自我合併 + 外部合併

員工編號姓名職稱報告人員工編號姓名職稱報告人

8111131 陳明明總經理 Null Null Null Null Null

8111261 黃謙仁工程師 8201141 8201141 陳森耀工程協理 8111131

8112061 林其達工程助理 8111261 8111261 黃謙仁工程師 8201141

8201141 陳森耀工程協理 8111131 8111131 陳明明總經理 Null

8203161 徐沛汶業務助理 8312261 8312261 張懷甫業務經理 8209241

8205231 劉逸萍業務 8308271 8308271 林美滿業務經理 8209241

8209241 朱辛傑業務協理 8111131 8111131 陳明明總經理 Null

8210171 胡琪偉業務 8308271 8308271 林美滿業務經理 8209241

8307021 吳志梁業務 8308271 8308271 林美滿業務經理 8209241

8308271 林美滿業務經理 8209241 8209241 朱辛傑業務協理 8111131

8311051 劉嘉雯業務 8308271 8308271 林美滿業務經理 8209241

8312261 張懷甫業務經理 8209241 8209241 朱辛傑業務協理 8111131

8411151 張若蘭業務助理 8312261 8312261 張懷甫業務經理 8209241

員工上司

圖 4-19 (b) 自我合併的左邊外部合併

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

Documents

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

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

Documents

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

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

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