第四章關聯式資料模型 資料庫系統設計理論李紹綸著
本章內容 關聯式資料模型的資料結構 關聯表綱目 (Relation Schema) 關聯表案例 (Relation Instance) 關聯表的特性 關聯表的種類 關聯式資料模型的完整性限制 實體完整性限制 (Entity Integrity Constraints) 外鍵 (Foreign Keys) 外鍵參考圖 (Referential Diagram) 參考完整性限制 (Referential Integrity Constraints) 關聯式資料庫的更新操作 其他完整性限制 2
關聯表的組成 關聯式資料模型的資料結構 關聯表名稱 主鍵 屬性名稱 員工 身分證號碼 姓名 電話 生日 薪水 A123456789 李鴻章 89511111 61/09/01 28000 E123456789 李紹綸 Null 58/11/01 50000 列錄 F123123123 簡嘉祐 Null 61/07/11 43000 F212121212 賴怡君 27041234 63/07/03 Null F232323232 莊雅玫 77388888 62/12/04 43000 基數 屬性值 維度 3
關聯表綱目 (Relation Schema) 關聯表 (Relations) 屬性 (Attributes) 屬性名稱 (Attribute Name) 資料類型 (Data Type) 主鍵 (Primary Key) 定義域 (Domains) 列錄 (Tuples) 維度 (Degree) 基數 (Cardinality) 4
關聯表案例 (Relation Instance) 資料儲存的單位 通常包含了數個屬性值所組合成的集合 一般我們稱之為列錄 (Tuple) 又稱為關聯表狀態 (Relation State) 關聯表綱目用 R(A 1,...,A n ) 表示 關聯表案例可用 r(r) 來表示 r = {tuple 1,...,tuple m } 表示關聯表中含有 m 個列錄 tuple j = < v 1,...,v n >,1 j m 每一個 v i 值,1 i n 一定是屬於 DOM (A i ) 或虛值 5
超級鍵 (Super Key) 主鍵 (Primary Key) 在關聯表綱目 R 中, 必定存在一組屬性 SK, 使得關聯表案例 r(r) 中的任何兩筆列錄 tuple 1 和 tuple 2 的 SK 屬性值都不一樣, 也就是說 tuple 1 [SK] tuple 2 [SK] 最小超級鍵 (Minimal Super Key) 在關聯表綱目 R 中, 不會存在另一個屬性 Z, 使得屬性 (SK - Z) 仍為關聯表綱目 R 的超級鍵時, 則屬性 SK 即為一個最小超級鍵 候選鍵 (Candidate Key) 在關聯表綱目 R 存在一個以上的鍵, 且具備唯一性和最小性時 在這些候選鍵中, 我們通常會選擇其中一個長度最短 語意最清楚候選鍵當作關聯表綱目 R 中的主鍵, 而其它未被選中的候選鍵則稱之為次選鍵 (Secondary Key) 6
挑選出關聯表主鍵的四項原則 永遠不會變更其值 (Never Change) 不可為虛值 (Not Null) 不要含有特殊意義的識別值 (Non-identifying Value) 簡短且簡單值 (Brief and Simple Value) 7
定義域 (Domains) 屬性 A i 的定義域 DOM(A i ) = {tuple 1 [A i ],...,tuple m [A i ]} = {tuple 1 (v i )}... {tuple m (v i )},1 i n 簡單型屬性 (Simple Attributes) 複合型屬性 (Composite Attributes) 8
屬性值 (Attribute Values) 屬性值具有下列性質 必須是單元值 (Atomic) 需指定其定義域 可能為虛值 (Null Value) 9
虛值 (Null Value) 因為暫時無法得知其明確的資訊, 而用一個 不確定 的值來代表一些不知道或不存在的資料 通常 虛值 是指 不適用 (Not Applicable) 屬性值遺失 (Missing Attribute Values) 完全未知 (Total Unknown) 10
關聯表的特性 根據 E.F. Codd 對關聯表原始的定義來看, 一個關聯表具有下列四項特性 沒有重複的列錄 列錄之間是沒有順序的 屬性之間也是沒有順序的 所有的屬性值都是單元值 11
屬性之間是沒有順序的 存取員工關聯表資料的程式碼 01' 定義 Connection 物件 02Dim cn 公司 As New SqlConnection() 03' 定義 Command 物件 04Dim cm 員工 As New SqlCommand() 05' 設定 Connection 物件的連線字串 06cn 公司.ConnectionString = "Data Source = localhost; Initial Catalog = 公司 ; Integrated Security = SSPI;" 07' 設定 Command 物件的 Connection 屬性, 也就是說要透過哪個 Connection 物件來連接 08cm 員工.Connection = cn 公司 09' 設定 Command 物件的 CommandText 屬性所要執行的 SQL 指令 10cm 員工.CommandText = "SELECT * FROM 員工 " 11' 開啟連接 12cn 公司.Open() 13' 建立 SqlDataReaader 物件 14Dim rd 員工 As SqlDataReader 15'ExecuteReader 會傳回一個 DataReader, 把它丟給 rd 員工 16rd 員工 = cm 員工.ExecuteReader(CommandBehavior.CloseConnection) 17'read = True, 開始擷取每一欄位資料 18If rd 員工.Read Then 19 tx 身分證號碼.Text = rd 員工.GetString(0) 20 tx 姓名.Text = rd 員工.GetString(1) 21 tx 電話.Text = rd 員工.GetString(2) 22 tx 生日.Text = rd 員工.GetDatetime(3) 23 tx 薪水.Text = rd 員工.GetDecimal(4) 24Else 25'read = False, 表示無資料可讀取 26 MessageBox.Show(" 找不到員工資料!") 27End If 28' 關閉 rd 員工, 也同時關閉 cn 公司 29rd 員工.Close() 12
所有的屬性值都是單元值 含多重值屬性 電話 的員工關聯表 員工 身分證號碼 姓名 電話 生日 薪水 A123456789 李鴻章 {89511111,89512222} 61/09/01 28000 E123456789 李紹綸 Null 58/11/01 50000 F123123123 簡嘉祐 Null 61/07/11 43000 F212121212 賴怡君 77388888 63/07/03 Null F232323232 莊雅玫 27041234 62/12/04 43000 13
所有的屬性值都是單元值 第一階正規形式的員工關聯表 員工 身分證號碼 姓名 電話 生日 薪水 A123456789 李鴻章 89511111 61/09/01 28000 A123456789 李鴻章 89512222 61/09/01 28000 E123456789 李紹綸 Null 58/11/01 50000 F123123123 簡嘉祐 Null 61/07/11 43000 F212121212 賴怡君 77388888 63/07/03 Null F232323232 莊雅玫 27041234 62/12/04 43000 14
基底關聯表 (Base Relations) 導出關聯表 (Derived Relations) 視界 (Views) 快照 (Snapshots) 查詢結果 (Query Result) 中間結果 (Intermediate Result) 關聯表的種類 15
關聯式資料模型的完整性限制 定義域限制 (Domain Constraints) 在關聯表中的每一個屬性 A 的值都必須要落在 DOM(A) 的單元值中 鍵限制 (Key Constraints) 在關聯表中, 任何兩個不同列錄的鍵值一定要不一樣 實體完整性限制 (Entity Integrity Constraints) 在關聯表中, 每一個列錄的主鍵值都不可含有虛值 參考完整性限制 (Referential Integrity Constraints) 在關聯表中, 當一個列錄 t 1 參考到另一個列錄 t 2 時, 則被參考到的列錄 t 2 一定要存在 16
實體完整性限制 在關聯表案例 r(r) 中的每一列錄的主鍵值 tuple[pk] Null 實體完整性限制具有下列三點特性 如果主鍵是由多個屬性所構成的複合鍵, 那麼此複合鍵所有的屬性都不可以為虛值 實體完整性僅限制在關聯表中的主鍵不可為虛值, 其他如候選鍵或次選鍵則不在此限 實體完整性只針對基底關聯表作限制, 其他如導出關聯表也不在此限 17
外鍵 (Foreign Keys) 員工關聯表和部門關聯表之間的關聯表案例 員工 身分證號碼 姓名 生日 地址 薪水 直屬上司 工作部門 A123456789 李鴻章 61/09/01 板橋市忠孝路 100 號 28000 F123123123 1 E123456789 李紹綸 58/11/01 台北市信義路 200 號 50000 Null 1 F123123123 簡嘉祐 61/07/11 台北市仁愛路 168 號 43000 E123456789 1 F212121212 賴怡君 63/07/03 板橋市四川路 120 號 Null F232323232 2 F232323232 莊雅玫 62/12/04 台北市信義路 100 號 43000 Null 2 部門 部門代號 部門名稱 經理身分證號碼 1 資訊 E123456789 2 研發 F232323232 18
外鍵 (Foreign Keys) R 1 中某筆列錄 tuple 1 的外鍵值, 剛好會和 R 2 中某筆列錄 tuple 2 的主鍵值相等, 意即 tuple 1 [FK] = tuple 2 [PK] 外鍵所具有的特性 外鍵一定要參考到其他關聯表的主鍵 外鍵可以是虛值 外鍵值和所參考到的主鍵值必須屬於相同的定義域, 但外鍵和所參考到的主鍵名稱可以不同 外鍵所參考到的主鍵如果是單一屬性, 外鍵也一定是單一屬性 外鍵不一定是此關聯表的主鍵 外鍵可以參考到同一關聯表的主鍵 19
員工和部門關聯表綱目外鍵參考圖 外鍵參考圖 員工 ( 身分證號碼, 姓名, 生日, 地址, 薪水, 直屬上司, 工作部門 ) p.k. f.k. f.k. 部門 ( 部門代號, 部門名稱, 經理身分證號碼 ) p.k. f.k. 20
參考完整性限制 若屬性 FK 是關聯表綱目 R 1 的外鍵, 屬性 PK 是關聯表綱目 R 2 的主鍵, 且在關聯表 R 1 中的列錄是利用屬性 FK 的值去參考關聯表 R 2 中的列錄時, 則對於關聯表 R 1 中的每一筆列錄而言, 該列錄 FK 的屬性值必須是虛值或是等於關聯表 R 2 中某列錄的 PK 屬性值 參考完整性限制具有兩點特性 在關聯表中不可包含無法參考的外鍵, 換言之, 被外鍵參考到的主鍵值一定要存在 如果外鍵不是關聯表的主鍵時, 其外鍵屬性值可以為虛值 21
關聯式資料庫的更新操作 新增操作指令 : 當我們將一筆新的資料加入關聯表中, 必須考量此新增的資料是否違反定義域限制 鍵限制 實體完整性限制和參考完整性限制等四種限制 在圖 4.5 員工關聯表中, 新增一筆 <F123123123, 郭信義, 64/07/30, 台北市忠孝路 186 號, 31500, Null, Null > 資料 在圖 4.5 員工關聯表中, 新增一筆 < Null, 郭信義, 64/07/30, 台北市忠孝路 186 號, 31500, Null, Null > 資料 在圖 4.5 員工關聯表中, 新增一筆 <K111111111, 郭信義, 64/07/30, 台北市忠孝路 186 號, 31500, M88888888, 1 > 資料 22
關聯式資料庫的更新操作 修改操作指令 : 當我們修改某一筆列錄的資料時, 只須考量此修改後的資料是否違反定義域限制和參考完整性限制即可 在圖 4.5 員工關聯表中, 倘若使用者要修改第五筆列錄莊雅玫的主鍵 身分證號碼 的屬性值為 E234343434 在圖 4.5 員工關聯表中, 倘若使用者要修改第五筆列錄莊雅玫的外鍵 工作部門 的屬性值為 3 23
關聯式資料庫的更新操作 當我們刪除某筆列錄的資料時, 只須考量是否違反參考完整性限制即可 在圖 4.5 部門關聯表中, 倘若使用者要刪除第一筆列錄 <1, 資訊, E123456789> 24
關聯式資料庫的更新操作 違反參考完整性限制時三種後續處理方式 限制性處理 (Restricted) 連鎖性處理 (Cascaded) 虛值化處理 (Nullifies) 25
其他完整性限制 語意完整性限制 (Semantic Integrity Constraints) 一般可分為下列三種限制條件 虛值限制 (Null Constraint) 預設值限制 (Default Value Constraint) 檢查限制 (Check Constraint) 26
其他完整性限制 透過 Transact SQL 指令設定圖 4.1 員工關聯表完整性限制 CREATE TABLE 員工 ( 身分證號碼 char(10) NOT NULL, 姓名 char(10) NOT NULL, 電話 char(8), 生日 datetime NOT NULL, 薪水 int DEFAULT 25000, CONSTRAINT 員工 _PK PRIMARY KEY( 身分證號碼 )); ALTER TABLE 員工 ADD CONSTRAINT 員工 _ 生日 CHECK ( 生日 >= 39/1/1 AND 生日 <= 84/12/31 ) FOR 生日, CONSTRAINT 員工 _ 薪水 CHECK ( 薪水 >= 17280 AND 薪水 <= 200000) FOR 薪水 ; 27