Microsoft PowerPoint - 002_第二章關聯式資料庫(cchen).ppt

Size: px

Start display at page:

Download "Microsoft PowerPoint - 002_第二章關聯式資料庫(cchen).ppt"

潭资
5 years ago
Views:

1 關聯式資料庫

2 關聯式資料庫 (Relation Database) 假設學校行政系統中有一個尚未分割的學籍資料表, 如下表所示 : 由上表中, 我們可以清楚看出多筆資料多筆資料重複現象, 如果有某一筆資料打錯, 將會導致資料不一致現象導致資料不一致現象例如 : 在上表中的第 5 筆記錄的系主任, 應該是李碩安卻打成李安

3 因此, 我們就必須要將原始的學籍資料表學籍資料表分割分割成數個不重複的資料表, 再利用關聯式資料庫的方法關聯式資料庫的方法來進行來進行資料表的關聯資料表的關聯何謂關聯式資料庫關聯式資料庫呢呢? 它是由兩個兩個或兩個以上的資料表兩個以上的資料表組合組合而成其目的 : 1. 節省重複輸入的時間時間與儲存空間儲存空間 2. 確保異動資料 ( 新增修改刪除 ) 時的一致性一致性及完整性完整性因此, 我們必須將各種資料依照性質的不同 ( 如 : 學籍資料選課資料, 課程資料, 學習歷程資料等 ),, 分別存放在幾個不同的表格中, 表格與表格表格之間的關係, 則以共同的欄位值 ( 如 : 學號欄位 ) 相互連結, 以這種方式來存放資料的資料庫, 在電腦術語中, 稱為關聯式資料庫 (Relational Database)

4 定義定義 1. 是由一群相互關係一群相互關係的正規化關聯 ( 表格 ) 所組成 2. 關聯 ( 表格 ) 之間是透過相同的欄位值 ( 即外鍵外鍵參考參考主鍵 ) 來連繫 3. 關聯 ( 表格 ) 中的所有屬性內含值所有屬性內含值都是都是基元值 (Atomic Value) 因此, 我們可以將上表中的學籍資料表學籍資料表分割分割為學生資料表與科系代碼表, 如何產生關聯式資料庫呢? 它是透過兩個資料表的相同欄位值 ( 即系碼 ) 來進行連結如下所示 : 註 : 主鍵與外鍵專有名詞會有後面章節中詳細介紹

5 優點優點 1. 節省記憶體空間相同的資料記錄不須要再重複輸入 2. 提高行政效率因為資料不須再重複輸入, 故可以節省行政人員的輸入時間 3. 達成資料的一致性因為資料不須再重複輸入, 故可以減少多次輸入產生人為的錯誤

6 關聯名詞關聯名詞關聯式資料庫模型的相關術語通常是用來說明的相關術語通常是用來說明資料庫系統的相關理論, 而 SQL Server 或 Access 等資料庫管理系統所使用的所使用的資料庫相關名詞資料庫相關名詞是利用另成一套術語, 不過這些名詞名詞或術語術語都代表都代表相同意義, 如表 2-1 所示 : 表關聯式資料模型 2-1 關聯名詞比較表關聯 (Relation) 表格 (Table) SQL Server 或 Access 值組 (Tuple) 屬性 (Attribute) 基數 (Cardinality) 主鍵 (Primary Key) 定義域 (Domain) 橫列 (Row) 或記錄 (Record) 直欄 (Column) 或欄位 (Filed) 記錄個數 (number of Record) 唯一識別 (unique identifier) 合法值群 (pool legal values)

7 圖示說明 : 重要專有名詞重要專有名詞 1. 資料表 (Table): : 又稱為表格, 它是真正儲存資料的地方真正儲存資料的地方它可視為特定主題的資料集合並且它是由資料行資料行與與資料列資料列的的二維表格組合而成例如 : 上表中的學生資料表學生資料表 2. 資料行 (Column): : 是指資料表中的某些欄位, 它是以垂直垂直方式來呈現例如 : 上表中的學號姓名學號姓名等 3. 資料列 (Row) : 是指資料表中某些記錄, 它是以水平水平方式來呈現例如 : 上表中的第一筆記錄 #1 S0001,, 一心,D001,

8 圖示說明 : 重要專有名詞重要專有名詞 4. 主鍵 ( Primary Key; PK): : 是指用來識別記錄的唯一性, 它不可以重複它不可以重複及空值 (Null) 例如 : 上表中的學生資料表學生資料表中的中的學號學號及科系代碼表科系代碼表中的中的系碼 5. 外鍵 ( Foreign Key; FK): : 是指用來建立建立資料表之間的資料表之間的關係, 其外鍵內含值其必須要與另一個資料表的主鍵相同主鍵相同例如 : 上表中的學生資料表學生資料表中的中的系碼 6. 關聯性 (Relationship): : 在資料表之間, 透過外鍵外鍵來參考另一個資料表的主鍵, 如果具有相同欄位值具有相同欄位值就可以進行關聯例如 : 上表中的學生資料表學生資料表中的中的系碼系碼與科系代碼表科系代碼表中的中的系碼系碼都具有相同欄位值, 因此, 就可以進行關聯

9 鍵值屬性在關聯式資料庫關聯式資料庫中中, 每一個關聯 ( 表格 ) 會有許多不同的鍵值屬性 (Key Attribute),, 因此, 我們可以分成兩個部份兩個部份來探討 : 一屬性 (Attribute): : 是指一般屬性一般屬性或欄位欄位如下圖所示二鍵值屬性 (Key Attribute): : 是指由一個或一個以上的屬性一個或一個以上的屬性所組成, 並且在一個關聯中, 必須要具有唯一性的屬性具有唯一性的屬性來當作來當作鍵 (Key) 例如 : 在關聯式資料庫中, 常見的鍵 (Key) 可分為 : 超鍵候選鍵候選鍵主鍵及交替鍵, 其各鍵的關係, 如下圖所示

10 屬性 (Attribute) 定義定義用來描述用來描述實體實體的性質 (Property) 例如例如學號姓名性別都是用來描述都是用來描述學生實體學生實體的性質, 並且每一個屬性一定要有一個定義域定義域 (Domain)( 亦即資料類型範圍大小資料類型範圍大小等 ) 其中, 性別屬性的內含值, 必須是男生或女生, 而不能超出而不能超出定義域 (Domain) 的合法值群分類分類 1. 簡單屬性 (Simple Attribute) 2. 複合屬性 (Composite Attribute) 3. 衍生屬性 (Derived Attribute)

11 1. 簡單屬性 (Simple Attribute) 定義定義已經已經無法再繼續切割無法再繼續切割成其他成其他有意義的單位, 亦即該屬性為基元值 (Atomic Value) 例如例如學號屬性學號屬性便是便是簡單屬性簡單屬性碼地址就不算是簡單屬性, 因為它還可以再切割成區域號縣市鄉鎮路巷弄號等小單位

12 2. 複合屬性 (Composite Attribute) 定義定義由兩個或兩個以上兩個或兩個以上的其他的其他屬性的值屬性的值所組成例如例如地址屬性是由是由區域號碼縣市鄉鎮路巷弄號區域號碼縣市鄉鎮路巷弄號等各個屬性所組成適用時機適用時機戶政事務查詢, 房屋仲介網站那些屬性是屬於複合屬性呢? 必須要視需求而定一般使用者在設定客戶資料表或學生資料表時, 地址屬性是視為是視為簡單屬性簡單屬性優點優點大量查詢時, 較快速 where 地址 Like * 苓雅區 * 速度較慢 where 區域 = 苓雅區速度較快

$屬性的值, 便可換算出年齡年齡屬性的值年齡 = 目前的系統時間生日 Dim Age As Integer Age = Year(Now()) - Val(TextBox1.Text) Label3.Text = " 您今年 " & Age & " 歲程式名稱 : ch2\ch2 ch2-2\ch2-2.sln2.$

13 3. 衍生屬性 (Derived Attribute) 定義定義 SELECT DATEDIFF("YYYY",#1971/10/9#,NOW()); 指可以經由某種方式的計算或推論計算或推論而獲得的例如 1 年齡屬性便屬於衍生屬性以實際的年齡實際的年齡為例, 可以由目前的系統時間目前的系統時間減去減去生日生日屬性的值, 便可換算出年齡年齡屬性的值年齡 = 目前的系統時間生日 Dim Age As Integer Age = Year(Now()) - Val(TextBox1.Text) Label3.Text = " 您今年 " & Age & " 歲程式名稱 : ch2\ch2 ch2-2\ch2-2.sln2.sln

14 超鍵 (Super Key) 基本上, 我們會在每一個資料表每一個資料表中中, 選出一個具有唯一性的欄位具有唯一性的欄位來當作主鍵, 但是, 在一個資料表中, 如果找不到具有唯一性的欄位時, 我們也可以選出兩個或兩個以上的欄位兩個或兩個以上的欄位組合起來, 以作為唯一識別資料的欄位定義定義是指在一個資料表中, 選出兩個或兩個以上的欄位兩個或兩個以上的欄位組合起來, 以作為唯一識別資料的欄位, 因此, 我們可以稱這種組合出來的欄位, 就是超鍵在在一個關聯 ( 表格 ) 中至少有一個超鍵, 就是所有屬性的集合

15 例如例如以學生資料表為例, 若是全班的學生姓名中, 若有人同名同姓時 ( 重複 ),, 則我們可以搭配學生的搭配學生的學號學號, 讓學生的學號與學生的姓名兩欄位結合起來 ( 亦即學號 + 姓名 ) 來產生新的鍵產生新的鍵所以,{, 姓名, 學號 } 是一個超鍵超鍵因為因為不可能不可能有兩個學生的有兩個學生的姓名與學號姓名與學號皆相同 { 身份證字號 } 也是一個超鍵超鍵同名同姓設定 { 姓名, 學號 } 為超鍵

16 同理同理 { 姓名, 學號, 身份證字號, 年齡, 系別 },{ 姓名, 學號, 身份證字號, 年齡 } 也都是超鍵超鍵因為它可以造成因為它可以造成唯一性唯一性的限制分析分析 1.{ { 年齡 } 或 { 姓名 } 都不是超鍵都 2. 最大的超鍵是所有屬性的集合 ; 最小的超鍵則是關聯的則是關聯的主鍵主鍵

17 主鍵 (Primary Key) 在關聯式資料庫模型關聯式資料庫模型中中, 將每一個資料表將每一個資料表視為視為一個實體, 而每一個實體利用利用屬性屬性描述之, 而這些屬性屬性就稱為就稱為鍵值鍵值其中用來識別資料表中記錄的唯一值的鍵值, 稱為主鍵主鍵定義定義 1. 從候選鍵候選鍵中選擇一個用來中選擇一個用來唯一識別值組 ( 記錄 ) 的鍵, 稱為主鍵主鍵 2. 在關聯綱要關聯綱要裡裡, 我們會在主鍵的屬性主鍵的屬性名稱名稱加一個底線加一個底線 3. 在一個關聯中, 只有一個主鍵, 若候選鍵若候選鍵未被選為未被選為主鍵主鍵時時, 則稱為交替鍵 (Alternate Key) 4. 主鍵之鍵值之鍵值不可為虛值 (Null Value) 5. 在建立資料表時一般都是以 P.K. 來代表來代表主鍵主鍵

18 舉例舉例學生資料表 ( 學號, 姓名, 生日, 身分證字號, 科系 ) (1) 候選鍵 :( 學號 ) 或 ( 身分證字號 ) (2) 主鍵 : 學號 (3) 交替鍵 : 身分證字號

19 如何挑選主鍵? 基本上, 我們要從多個鍵值多個鍵值中挑選主鍵挑選主鍵時時, 會依循以下三個原則 : 1. 固定不會再變更的值在挑選主鍵時, 必須要找永遠不會被變更的欄位, 否則會增加爾後的管理和維護資料的困難度與複雜性例如 : 學號與身份證字號在決定之後, 幾乎不會再改變 2. 單一的屬性在一個資料表中, 最好只選取單一屬性的候選鍵作為主鍵, 因為可以節省記憶體空間節省記憶體空間及提高執行效率提高執行效率例如 :{: 姓名 + 學號 } 與 { 學號 },, 雖然二者都具有唯一性, 但是後者 { 學號 } 是單一屬性 3. 不可以為空值或重覆依照關聯式資料完整性規則, 主鍵的鍵值不可以重覆, 也不可以也為空值 (NULL) 例如 :{: 姓名 } 欄位就不適合當作主鍵欄位因為可能會重複

20 複合鍵 (Composite Key) 定義定義是指資料表中的是指資料表中的主鍵, 是由兩個或兩個欄位以上兩個或兩個欄位以上所組成, 這種使用時機使用時機主鍵稱為複合鍵 (Composite Key) 當表格中某一欄位的值某一欄位的值無法區分資料記錄無法區分資料記錄時時, 可以使用這種方法例如例如在表 2-2a2a 中縣市的欄位值有重複, 無法區分出每一筆記錄, 所以縣市欄位不能當作主鍵欄位不能當作主鍵欄位因此, 必須要把縣市與區域兩個欄位組合在一起, 當作主鍵欄位主鍵欄位如表 2-2b2b 所示重複重複

21 定義定義候選鍵候選鍵就是就是主鍵主鍵的候選人, 並且也是關聯表的屬性子集所組成條件條件一個屬性 ( 欄位 ) 是要成為候選鍵, 則必須同時要符合下列兩項條件 : 1. 具有唯一性是指在一個關聯表中, 用來唯一識別資料記錄的欄位唯一識別資料記錄的欄位例如 : 超鍵 (Super Key) 但可以是由多個欄位組合 { 縣市 + 區域 } 而成候選鍵 (Candidate Key) 2. 具有最小性是指除了符合唯一性的條件除了符合唯一性的條件之外, 還必須要在該屬性子集中移除任一個屬性任一個屬性之後, 不再符合唯一性亦即亦即鍵值欄位個數鍵值欄位個數為最小最小例如 : { 縣市 + 區域 } 組合成來符合唯一性的條件並且在移除任一個屬性 { 區域 } 之後, { 縣市 } 不再符合唯一性因此, { 縣市

22 特性特性 1. 候選鍵可以唯一識別值組 ( 記錄 ), 大部份關聯都只有一個候選鍵只有一個候選鍵 2. 若候選鍵候選鍵只包含一個屬性包含一個屬性時時, 稱為簡單 (simple) 候選鍵例如 : { 學號 } 若包含兩個或兩個以上屬性兩個或兩個以上屬性時時, 稱為複合 (composite) 候選鍵例如 : { 縣市 + 區域 }

23 舉例 1 假設現在有一個學生資料表, 其相關的欄位如下所示 : 學生資料表 ( 學號, 姓名, 生日, 身分證字號, 科系 ) 請找出此資料表中的兩個候選鍵候選鍵解答解答第一個候選鍵 (1) 找出具有唯一性具有唯一性的欄位 { 學號 + 姓名 } 共同組成時, 滿足唯一性 (2) 檢查是否具有最小性但是 { 學號 + 姓名 } 不滿足最小性, 因為在移去姓名屬性移去姓名屬性之後, 學號仍然具有唯一性因此, 我們必須要縮減為最小欄位縮減為最小欄位為 { 學號 },, 所以找到第一個候選鍵

24 學生資料表 ( 學號, 姓名, 生日, 身分證字號, 科系 ) 解答解答第二個候選鍵 (1) 找出具有唯一性具有唯一性的欄位 { 身分證字號 + 科系 } 共同組成時, 滿足唯一性 (2) 檢查是否具有最小性但是 { 身分證字號 + 科系 } 不滿足最小性, 因為在移去科系屬性之後, 身分證字號仍然具有唯一性因此, 我們必須要縮減為最小欄位縮減為最小欄位為 { 身分證字號 }, 所以找到第二個候選鍵所以,{, 學號 } 或 { 身分證字號 } 皆為候選鍵候選鍵

25 舉例 2 假設現在有一個通訊錄資料表, 其相關的欄位如下所示 : 通訊錄資料表 ( 姓名, 生日, 電話, 地址 ) 請找出此資料表中的一個候選鍵候選鍵解答解答 (1) 找出具有唯一性具有唯一性的欄位 { 姓名 + 生日 } 共同組成時, 滿足唯一性 (2) 檢查是否具有最小性並且 { 姓名 + 生日 } 也滿足最小性, 因為在移去移去生日生日屬性之後, 姓名姓名就就不具有唯一性不具有唯一性因此, { 姓名 + 生日 } 兩個欄位組合, 缺一不可, 所以這種候選鍵候選鍵又稱為複合式候選鍵所以, { 姓名 + 生日 } 為複合式複合式候選鍵候選鍵

26 外來鍵 (Foreign Key) 在關聯式資料庫中, 任兩個資料表要進行關聯 ( 對應 ) 時, 必須要透過外來鍵外來鍵參考參考主鍵主鍵才能建立, 其中主鍵值主鍵值的所在資料表稱為父關聯, 而外來鍵值而外來鍵值的所在資料表稱為的所在資料表稱為子關聯子關聯定義定義外來鍵是指是指父關聯嵌入的鍵, 並且外來鍵外來鍵在父關聯中扮演在父關聯中扮演主鍵的角色因此, 外來鍵一定會存放另一個資料表的一定會存放另一個資料表的主鍵, 主要目的是用來確定資料的參考完整性參考完整性所以, 當父關聯的主鍵值主鍵值不存在時, 則子關聯則子關聯的外來鍵值也不可能存在參考嵌入子關聯父關聯

27 外來鍵的特性外來鍵的特性 1. 子關聯的外鍵外鍵必須對應必須對應父關聯的主鍵父關聯的主鍵 2. 外鍵是用來建立是用來建立子關聯子關聯與與父關聯父關聯的連結關係例如 : 張三同學可以找到對應的系主任參考嵌入子關聯父關聯說明 : 在 SQL 語言中, 通常是主鍵值 = 外來鍵值當作條件式例如 : 在 SELECT 之 WHERE 子句中撰寫如下 : 學生資料表. 系碼 = 科系代碼表. 系碼說明 : 以上 SQL 指令是用來聯結學生資料表和科系代碼表兩個資料表

28 外來鍵的特性外來鍵的特性 < 續 > 3. 外來鍵和父關聯的主鍵欄位父關聯的主鍵欄位必須要具有必須要具有相同定義域, 亦即相同的資料型態和欄位長度, 但名稱但名稱則可以不相同可以不相同舉例 1 相同的相同的資料型態資料型態和欄位長度假設現在有一個關聯圖如下 : 其中, 科系代碼表的系碼欄位的資料類型為文字, 現在欲改為備忘的資料類型, 則會出現以下的錯誤產生 :

29 舉例 2 外來鍵和父關聯的主鍵欄位外來鍵和父關聯的主鍵欄位名稱名稱則可以不相同假設現在有一個關聯圖如下 : 其中, 科系代碼表的系碼系碼欄位名稱, 現在欲改為科系代碼欄位名稱, 則是可以的如下圖所示 : 系碼科系代碼註 : 因此, 我們可以清楚得知, 子關聯的外來鍵參考參考父關聯

30 外來鍵的特性外來鍵的特性 < 續 > 4. 外來鍵的欄位值可以是重覆值重覆值或空值 (NULL) (1) 重覆值的例子參考重覆值子關聯父關聯說明 : 在上表中, 代表張三張三與李四李四都是就讀資工系

31 外來鍵的特性外來鍵的特性 < 續 > 4. 外來鍵的欄位值可以是重覆值重覆值或空值 (NULL) (2) 空值 (NULL) 的例子參考子關聯空值父關聯說明 : 在上表中, 代表王五王五尚未決定要就讀那一個科系

32 關聯式資料庫的種類假設現在有甲與乙兩個資料表, 其關聯式資料庫中資料表的關聯種類可以分為下列三種 : 1. 一對一的關聯 (1:1) 1): 甲資料表中的一筆記錄, 只能對應對應到乙資料表中的乙資料表中的一筆記錄, 並且乙資料表中的一筆記錄, 只能對應對應到甲資料表中的甲資料表中的一筆記錄一筆記錄 2. 一對多的關聯 (1:M) M): 甲資料表中的一筆記錄, 可以對應對應到乙資料表中的乙資料表中的多筆記錄, 但是乙資料表中的一筆記錄, 卻只能對應對應到甲資料表中的甲資料表中的一筆記錄一筆記錄 3. 多對多的關聯 (M:N) N): 甲資料表中的一筆記錄, 能夠對應對應到乙資料表中的乙資料表中的多筆記錄, 並且乙資料表中的一筆記錄, 也能夠對應對應到甲資料表中的甲資料表中的多筆記錄多筆記錄

33 一對一關聯 (1:1) 1) 定義定義假設現在有甲與乙兩個資料表, 在一對一關聯在一對一關聯中中, 甲資料表中的一筆記錄, 只能對應對應到乙資料表中的乙資料表中的一筆記錄, 並且乙資料表中的乙資料表中的一筆記錄, 只能對應對應到甲資料表中的甲資料表中的一筆記錄一筆記錄舉例舉例以成績處理系統為例, 當兩個資料表之間做一對一的關聯時, 表示學生資料表中的學生資料表中的每一筆記錄, 只能對應對應到成績資料表的一筆記錄, 而且成績資料表的成績資料表的每一筆記錄, 也只能對應對應到學生資料表的一筆記錄, 這就是所謂的 1 對 1 關聯適用時機適用時機通常是基於安全上的考量 ( 資料保密因素 ),, 將某一部份的欄位分割到另一個資料表中

34 一對一的關聯圖一對一的關聯圖在下圖中, 學生資料表與成績資料表是一對一的關係因此, 學生資料表的主鍵主鍵必須必須對應對應成績資料表的成績資料表的主鍵, 才能設定為 1:1 的關聯圖在實務上, 我們也可以將這兩個資料表這兩個資料表合併合併成一個資料表, 其合併結果如下圖所示

35 一對一的實例一對一的實例欲將學生資料表與成績資料表的這兩個資料表這兩個資料表合併合併成一個資料表一個資料表時時, 必須要先完成以下兩個條件, 否則就無法進行合併 : 1. 先檢查學生資料表中學號欄位值是否與成績資料表中學號欄位值完全相同 2. 再建立 1:1 的資料庫關聯圖, 下圖所示 :

36 一對多關聯 (1:M) 定義定義假設現在有甲與乙兩個資料表, 在一對多關聯在一對多關聯中中, 甲資料表中的一筆記錄, 可以對應對應到乙資料表中的乙資料表中的多筆記錄, 但是乙資料表中的乙資料表中的一筆記錄, 卻只能對應對應到甲資料表中的甲資料表中的一筆記錄一筆記錄舉例舉例以數位學習系統為例, 當兩個資料表之間做一對多的關聯一對多的關聯時時, 表示老師資料表中的每一筆記錄, 可以對應到課程資料表中的多筆記錄, 但課程資料表的每一筆記錄但, 只能對應到老師資料表中的一筆記錄, 這就是所謂的一對多關聯, 這種方式是最常被使用

37 一對多的關聯圖一對多的關聯圖在下圖中, 老師資料表與課程資料表是一對多的關係因此, 老師資料表的主鍵主鍵必須必須對應對應課程資料表的課程資料表的外來鍵, 才能設定為 1:M 的關聯圖註 : * 代表該欄位為代表該欄位為主鍵, # 代表該欄位為代表該欄位為外來鍵外來鍵

38 一對多的實例一對多的實例我們建立兩個資料表, 分別為老師資料表與課程資料表, 此時, 我們可以了解老師資料表中的一筆記錄 (T0001),, 可以對應對應到課程資料表中的多筆記錄 (C001,C002,C003),, 但是課程資料表中的一筆記錄, 卻只能對應到老師資料表中的到老師資料表中的一筆記錄一筆記錄如下圖所示

39 多對多關聯 (M:N) 定義定義假設現在有甲與乙兩個資料表, 在多對多關聯在多對多關聯中中, 甲資料表中的一筆記錄, 能夠對應對應到乙資料表中的乙資料表中的多筆記錄, 並且乙資料表中的乙資料表中的一筆記錄, 也能夠對應對應到甲資料表中的甲資料表中的多筆記錄多筆記錄舉例舉例以選課系統為例, 當兩個資料表之間做多對多的關聯多對多的關聯時時, 表示學生資料表中的每一筆記錄, 可以對應到課程資料表中的多筆記錄, 並且課程資料表中的每一筆記錄, 也能夠對應到學生資料表中的多筆記錄, 這就是所謂的多對多關聯

40 多對多的關聯圖多對多的關聯圖雖然, 一對多關聯是最常先的一種關聯性, 但是在實務上, 多對多關聯的情況也不少, 也就是說由兩個資料表 ( 實體 ) 呈現多對多的關聯多對多的關聯例如 : 學生資料表與課程資料表如下圖所示在上圖中表示 : 每一位學生可以選修多門課程, 並且每一門課程也可以被多位學生來選修兩個資料表多對多關聯之問題兩個資料表多對多關聯之問題在實務上多對多關聯多對多關聯如果只有如果只有兩個資料表來建置, 難度較高, 並且容易出問題

41 解決方法解決方法利用三個資料表三個資料表來建置來建置多對多關聯, 也就是說, 在原來的兩個在資料表之間之間再加入一個聯合資料表 (Junction( Table) ), 使他們可以順利處理多對多的關聯其中, 聯合資料表 (Junction( Table) 中的主索引鍵 ( 複合主鍵 ) 是由資料表 A( 學生資料表 ) 和資料表 B( 課程資料表 ) 兩者的主鍵所組成例如 : 在學生資料表與課程資料表之間再加入第三個資料表選課資料表, 如下圖所示聯合資料表

42 聯合資料表說明 : 1. 在學生資料表與選課資料表的關係是以一對多 2. 在課程資料表與選課資料表的關係是以一對多 3. 藉由選課資料表的使用, 使學生資料表與課程資料表關係變成多對多的關聯式, 亦即每一位學生可以選修一門以上的課程並且每一門課程也可以被多位同學的選修 4. 以資料表 (Table) 之方式組成關聯, 將這些關聯組合起來, 即形成一個關聯式資料庫

43 資料庫之完整性規則完整性規則 (Integrity Rules) 是用來確保資料的一致性一致性與完整性, 以避免避免資料在經過資料在經過新增修改及刪除新增修改及刪除等運算之後, 而產生的異常現象異常現象亦即避免避免使用者將使用者將錯誤或不合法的資料值錯誤或不合法的資料值存入資料庫中如下圖所示 :

44 三種完整性規則三種完整性規則關聯式資料模式的完整性規則, 有下列三種 : 如下圖所示 1. 實體完整性規則 (Entity Integrity Rule) 2. 參考完整性規則 (Referential Integrity Rule) 3. 值域完整性規則 (Domain Integrity Rule) 學生資料表 ( 子關聯 ) 科系代碼表 ( 父關聯 ) 註 : 在關聯式資料庫中, 任兩個資料表要進行關聯 ( 參考 ) 時, 必須透過主鍵主鍵對應外來鍵才能建立, 其中主鍵主鍵值的所在資料表稱為值的所在資料表稱為父關聯, 而外來而鍵值的所在資料表稱為值的所在資料表稱為子關聯子關聯

45 完整性的綜合分析完整性的綜合分析我們已經學會一對一一對一一對多一對多及多對多多對多的資料庫關聯圖了, 但是, 你是否有注意到, 當我們勾選強迫參考完整性勾選強迫參考完整性時時,Access, 會自動啟動串聯更新更新關聯欄位關聯欄位及串聯串聯刪除刪除關聯欄位關聯欄位的功能, 來讓使用者勾選呢? 說明如下所示 :

46 完整性的綜合分析完整性的綜合分析 < 續 > 一為主鍵多為外來鍵多為外來鍵一為主鍵註在編輯關聯對話方塊中, 如果有勾選強迫參考完整性時, 則必須要具備以下條件 : (1) 兩個資料表之間的關聯欄位關聯欄位的資料類型資料類型必須要必須要相同相同 (2) 如果關聯類型是一對多時, 則一的那方則一的那方必須要是必須要是主鍵, 而多的那方多的那方必須要是必須要是外來鍵外來鍵並且主鍵主鍵不可以是自動編

47 1. 強迫參考完整性以下圖的學生資料表與科系代碼表為例 : 科系代碼表內的內的系碼欄位為主鍵, 而學生資料表而學生資料表內的內的系碼為外來鍵外來鍵因此, 強迫參考完整性有勾選時時, 則 DBMS 會限制使用者輸入資料是否有違反是否有違反參考完整性參考完整性何謂參考完整性? 是指用來確保相關資料表間的資料一致性資料一致性, 避免因一個資料表的記錄改變記錄改變時時, 造成另一個資料表的內容變成無效的值內容變成無效的值如下圖所示

48 2. 串聯更新更新關聯欄位在更新更新父關聯父關聯的主鍵主鍵時時, 也會同時更新子關聯子關聯的外鍵值外鍵值例如 : 在下表中, 欲更新科系代碼表中的 D001 為 A001, 也必須同時將學生資料表中的第二筆及第三筆記錄的 D001 修改為 A001 子關聯父關聯

49 實體完整性規則針對主鍵定義定義每一個關聯表中的值組都必須是都必須是可以識別可以識別的的, 因此, 主鍵必須要必須要具有唯一性, 並且主鍵主鍵不可重複不可重複或為或為空值 (NULL) 否則, 就無法唯一識別某一記錄 ( 值組 ) 特性特性 1. 實體必須是必須是可區別的 (Distinguishable) 2. 主鍵值未知未知代表是一個代表是一個不確定的實體, 不能存放在資料關聯中如下圖所示 :

50 3. 實體完整性規則只適用於基本關聯 (Base Relation),, 不考慮視界 (View) (1) 基本關聯 (Base Relation) 真正存放資料的具名關聯, 是透過 SQL 的 Create Table 敘述來建立基本關聯對應於 ANSI/SPARC 的概念層 (2) 視界 (View) 是一種具名的衍生關聯虛擬關聯, 定義在某些基本關聯上, 本身不含任何資料視界視界相對應於 ANSI/SPARC 的外部層 4. 在建立資料表資料表時可以設定某欄位為時可以設定某欄位為主鍵, 以確保實體實體完整性和唯一性

51 5. 複合主鍵 ( 學號與課號 ) 中的任何屬性值任何屬性值皆不可以皆不可以是空值 (Null) 如下圖所示 : 說明 : 主鍵是由是由多個欄位多個欄位連結而成的連結而成的組合鍵, 因此, 每一個欄位值都不可為空值 (Null)

52 參考完整性規則針對外鍵在完成建立資料庫資料庫及資料表資料表之後, 如果沒有把它們整合沒有把它們整合起來, 則學生資料表學生資料表中的中的外鍵 ( 系碼 ) 就無法與科系代碼表科系代碼表的主鍵 ( 系碼 ) 之間來進行關聯了, 這將會導致資料庫不一致導致資料庫不一致的問題也就是的問題也就是違反違反了資料庫之參考完整性規則參考完整性規則定義定義是指用來確保兩個資料表確保兩個資料表之間的資料之間的資料一致性, 避免因一個資料表的記錄改變改變時時, 造成另一個資料表的內容變成無效的值內容變成無效的值因此, 子關聯的外來鍵 (FK) 的資料欄位值, 一定要存在於父關聯父關聯的主鍵 (PK) 中的資料欄位值

53 例如例如學生資料表 ( 子關聯 ) 的系碼 ( 外鍵 ;F.K.); 一定要存在於存在於系別代碼表 ( 父關聯 ) 的系碼 ( 主鍵 ;P.K.); 中如下圖所示 : 存在於學生資料表 ( 子關聯 ) 系別代碼表 ( 父關聯 )

科系代碼表尚未勾選參考完整性規則, 如下圖所示 : 因此, 行政人員假設在填入系碼欄位, 誤填為 D003 時,DBMS,

54 例如例如尚未勾選參考完整性規則尚未勾選參考完整性規則的情況假設現有二個資料表, 分別為學生資料表與科系代碼表, 其中有一位五福同學欲從資工系 (D001) 轉為資管系 (D002),, 但是, 在轉系的過程中, 學校的校務行政系統中的學生資料表與科系代碼表尚未勾選參考完整性規則, 如下圖所示 : 因此, 行政人員假設在填入系碼欄位, 誤填為 D003 時,DBMS, 就無法檢查出來, 此時, 將會使得系碼 D003 內容變成無效的值, 以致於產生資料不一致資料不一致現象??? 無效的值 ( 因為無法參考到科系代碼表 )

55 例如例如有勾選參考完整性規則有勾選參考完整性規則的情況假設現有二個資料表, 分別為學生資料表與科系代碼表, 其中有一位五福同學欲從資工系 (D001) 轉為資管系 (D002),, 並且, 在轉系的過程中, 學校的校務行政系統中的學生資料表與系別資料表有勾選參考完整性規則, 如下圖所示 : 因此, 行政人員假設在填入系碼欄位, 誤填為 D003 時,DBMS, 就可以檢查出來, 此時, 系碼 D003 就無法修改, 因此, 不會產生資料不一致現象

56 參考完整性規則的特性參考完整性規則的特性 1. 至少要有兩個或兩個以上兩個或兩個以上的資料表才能執行的資料表才能執行參考完整性規則參考完整性規則 2. 由父關聯表父關聯表的主鍵主鍵與子關聯表子關聯表的外來鍵外來鍵的關係來的關係來建立兩資料表間資料的關聯性 3. 建立參考完整性之後, 就可以即時有效檢查即時有效檢查使用者的輸入值, 以避免無效的值避免無效的值發生

57 值域完整性規則定義定義是指在單一資料表單一資料表中中, 對於所有屬性 (Attributes) 的內含值, 必須來自值域 (Domain) 的合法值群中亦即是指在單一資料表中, 同一資料行中的中的資料屬性資料屬性必須要必須要相同相同亦即亦即同一行的欄位同一行的欄位之資料類型要相同相同例如例如性別屬性的內含值, 必須是男生或女生, 而不能超出定義域 (Domain) 的合法值群特性特性 1. 作用在單一資料表單一資料表中 2. 同一資料行同一資料行中的中的資料屬性資料屬性必須要必須要相同 3. 建立資料表可以設定條件設定條件來查檢值域是否為合法值群

58 定義定義 1. 空值是一種特殊記號, 用以記錄目前不詳的資料值不詳的資料值 2. 空值不是不是指指空白格空白格或或零值零值 3. 空值可分為以下三種 : (1) 可應用的空值 (Application Null Value): : 一般指目前不知道的值, 但此值確實存在例如 : 張三已婚, 但其配偶欄的姓名尚未填入尚未填入 (2) 不可應用的空值 (Inapplicable Null Value): : 目前完全沒有存在這個值例如 : 張三未婚, 其配偶欄的值為空值空值 (3) 完全未知的空值 (Totally Unknown): : 完全不知道這個值是否存在空值 (NULL Values)

59 非空值 (NOT NULL) 定義定義資料行資料行必須有必須有正確的資料值, 不可為虛值例如例如在學生資料表中的學號和姓名兩欄位值學號和姓名兩欄位值必須確定, 不可為虛值因此, 在建立資料表資料表時就必須宣告為 NOT NULL 必須有資料 : 是 ( 代表不能為空值 )

PowerPoint Presentation

PowerPoint Presentation 第四章關聯式資料模型資料庫系統設計理論李紹綸著本章內容關聯式資料模型的資料結構關聯表綱目 (Relation Schema) 關聯表案例 (Relation Instance) 關聯表的特性關聯表的種類關聯式資料模型的完整性限制實體完整性限制 (Entity Integrity Constraints) 外鍵 (Foreign Keys) 外鍵參考圖 (Referential Diagram)

Microsoft PowerPoint - 002_第二章 關聯式資料庫(cchen).ppt

Microsoft PowerPoint - 002_第二章關聯式資料庫(cchen).ppt