第 5 章 關 聯 表 的 正 規 化 5-1 正 規 化 的 基 礎 5-2 功 能 相 依 5-3 第 一 階 到 第 三 階 正 規 化 型 式 5-4 多 重 值 相 依 與 第 四 階 正 規 化 型 式 5-5 合 併 相 依 與 第 五 階 正 規 化 型 式
5-1 正 規 化 的 基 礎 5-1-1 關 聯 表 為 什 麼 需 要 正 規 化 5-1-2 正 規 化 的 型 式
5-1 正 規 化 的 基 礎 正 規 化 (Normalization) 是 一 種 標 準 處 理 程 序 來 決 定 關 聯 表 應 該 擁 有 哪 些 屬 性, 其 目 的 是 建 立 良 好 結 構 關 聯 表 (Well-structured Relation), 一 種 沒 有 重 複 資 料 的 關 聯 表 而 且 在 新 增 刪 除 或 更 新 資 料 時, 不 會 造 成 錯 誤 或 資 料 不 一 致 的 異 常 情 況 正 規 化 目 的 如 下 所 示 : 去 除 重 複 性 (Eliminating Redundancy): 建 立 沒 有 重 複 資 料 的 關 聯 表, 因 為 重 複 資 料 不 只 浪 費 資 料 庫 的 儲 存 空 間, 而 且 會 產 生 資 料 維 護 上 的 問 題 去 除 不 一 致 的 相 依 性 (Eliminating Inconsistent Dependency): 資 料 相 依 是 指 關 聯 表 中 的 屬 性 之 間 擁 有 關 係, 如 果 關 聯 表 擁 有 不 一 致 的 資 料 相 依, 這 些 屬 性 就 會 在 新 增 刪 除 或 更 新 資 料 時, 造 成 異 常 情 況
5-1-1 關 聯 表 為 什 麼 需 要 正 規 化 非 正 規 化 型 式 關 聯 表 關 聯 式 資 料 庫 正 規 化 的 目 的 以 一 句 話 來 說, 就 是 避 免 資 料 重 複, 關 聯 表 擁 有 非 自 然 結 合 的 屬 性, 就 會 造 成 資 料 重 複 的 問 題, 稱 為 非 正 規 化 型 式 (Unnormalized Form) 關 聯 表
5-1-1 關 聯 表 為 什 麼 需 要 正 規 化 更 新 異 常 更 新 異 常 (Update Anomaly) 是 在 Student_Course_Classes 關 聯 表 更 新 指 定 的 屬 性 資 料, 因 為 資 料 重 複 儲 存 在 多 筆 值 組 ( 記 錄 ), 所 以 需 要 同 時 更 新 多 筆 值 組 的 資 料 需 要 更 改 4 筆 值 組
5-1-1 關 聯 表 為 什 麼 需 要 正 規 化 刪 除 異 常 刪 除 異 常 (Deletion Anomaly) 是 在 Student_Course_Classes 關 聯 表 刪 除 值 組, 可 能 刪 除 合 法 資 料 例 如 :CS101 和 CS121 課 程 都 有 一 筆 值 組, 如 下 所 示 : 刪 除 值 組 連 同 課 程 資 料 也 一 併 遺 失
5-1-1 關 聯 表 為 什 麼 需 要 正 規 化 新 增 異 常 新 增 異 常 (Insertion Anomaly) 是 在 Student_Course_Classes 關 聯 表 新 增 值 組 和 刪 除 相 反, 可 能 只 新 增 部 分 值 組 的 資 料 例 如 : 新 增 一 筆 值 組, 課 程 編 號 是 CS240, 名 稱 為 資 料 結 構, 學 分 是 3, 如 下 所 示 : 新 增 值 組 只 有 課 程 資 料, 主 鍵 sid 是 空 值, 違 反 實 體 完 整 性
5-1-2 正 規 化 的 型 式 說 明 正 規 化 是 從 下 而 上 (Bottom- Up) 評 估 關 聯 表 綱 要 是 否 符 合 正 規 化 型 式, 針 對 的 是 關 聯 表 中 各 屬 性 間 的 關 係, 正 規 化 型 式 是 一 些 組 織 關 聯 表 屬 性 的 規 則 正 規 化 的 處 理 過 程 分 為 五 個 階 段 的 正 規 化 型 式, 每 一 階 正 規 化 型 式 是 在 處 理 不 同 屬 性 間 資 料 相 依 (Dependency) 的 問 題, 如 右 圖 所 示 :
5-1-2 正 規 化 的 型 式 五 階 正 規 化 第 一 階 正 規 化 型 式 (First Normal Form;1NF): 在 關 聯 表 刪 除 多 重 值 和 複 合 屬 性, 讓 關 聯 表 只 擁 有 單 元 值 屬 性 第 二 階 正 規 化 型 式 (Second Normal Form;2NF): 滿 足 1NF 且 關 聯 表 沒 有 部 分 相 依 (Partial Dependency) 第 三 階 正 規 化 型 式 (Third Normal Form;3NF): 滿 足 2NF, 而 且 關 聯 表 沒 有 遞 移 相 依 (Transitive Dependency) Boyce-Codd 正 規 化 型 式 (Boyce-Codd Normal Form;BCNF): 廣 意 第 三 階 正 規 化 型 式, 關 聯 表 如 果 擁 有 多 個 複 合 候 選 鍵, 需 要 刪 除 候 選 鍵 間 的 功 能 相 依 第 四 階 正 規 化 型 式 (Fourth Normal Form;4NF): 滿 足 BCNF 且 沒 有 多 重 值 相 依 第 五 階 正 規 化 型 式 (Fifth Normal Form;5NF): 滿 足 4NF 且 沒 有 合 併 相 依
5-2 功 能 相 依 5-2-1 功 能 相 依 的 定 義 5-2-2 功 能 相 依 的 種 類 5-2-3 找 出 關 聯 表 的 所 有 功 能 相 依 5-2-4 阿 姆 斯 壯 推 論 規 則
5-2 功 能 相 依 功 能 相 依 (Functional Dependency;FD) 是 描 述 關 聯 表 中 屬 性 間 的 相 依 關 係, 這 是 關 聯 表 正 規 化 的 基 礎 簡 單 的 說, 關 聯 表 正 規 化 的 第 一 步, 就 是 在 關 聯 表 找 出 所 有 屬 性 間 的 功 能 相 依 當 在 關 聯 表 找 出 所 有 屬 性 間 的 功 能 相 依 後, 就 可 以 幫 助 我 們 在 關 聯 表 找 出 : 重 複 資 料 : 一 些 不 該 屬 於 此 關 聯 表 的 屬 性 候 選 鍵 和 主 鍵 : 如 果 關 聯 表 的 所 有 屬 性 都 功 能 相 依 於 一 個 或 一 組 屬 性, 此 屬 性 就 是 候 選 鍵 (Candidate Key) 或 主 鍵 (Primary Key)
5-2-1 功 能 相 依 的 定 義 定 義 功 能 相 依 是 同 一 個 關 聯 表 屬 性 間 的 關 係, 其 定 義 如 下 所 示 : 定 義 5.1: 功 能 相 依 (Functional Dependency) 以 關 聯 表 R 為 例, 若 關 聯 表 R 擁 有 屬 性 A 與 B,A 與 B 可 以 是 複 合 屬 性, 我 們 可 以 說 屬 性 B 功 能 相 依 (Functional Dependent) 於 A, 或 A 功 能 決 定 (Functional Determines)B, 寫 成 : R.A R.B 或 簡 寫 成 : A B 若 關 聯 表 R 擁 有 A B 的 功 能 相 依, 則 在 關 聯 表 R 的 每 一 對 t1 和 t2 值 組 且 t1(a) = t2(a), 可 以 得 到 :t1(b) = t2(b)
5-2-1 功 能 相 依 的 定 義 說 明 在 關 聯 表 中 如 果 有 兩 個 值 組 的 屬 性 A 是 相 同 的, 就 可 以 知 道 屬 性 B 的 值 也 一 定 相 同 換 句 話 說, 如 果 知 道 屬 性 A 的 值, 就 可 以 知 道 屬 性 B 的 值 功 能 相 依 是 一 種 屬 性 間 的 關 係, 對 比 實 體 關 聯 圖 中, 就 是 實 體 的 一 對 一, 一 對 多 多 對 一 和 多 對 多 關 聯 性, 如 下 所 示 : A1 B1 A1 { B1, B2, Bn } { A1, A2,, An } B1 { A1, A2,, An } { B1, B2, Bn }
5-2-1 功 能 相 依 的 定 義 範 例 例 如 : 學 生 Students 關 聯 表 擁 有 sid name birthday 和 tel 屬 性, 一 些 功 能 相 依 範 例, 如 下 所 示 : sid birthday sid tel sid { name, birthday } sid { name, birthday, tel } { sid, name } { name, birthday, tel }
5-2-2 功 能 相 依 的 種 類 說 明 功 能 相 依 的 種 類 可 以 分 為 三 種 : 完 全 相 依 部 分 相 依 和 遞 移 相 依 在 本 節 使 用 的 範 例 是 將 第 3-5 節 的 Students Courses 和 Instructors 關 聯 表 硬 結 合 成 Student_Course_Instructors 關 聯 表 ( 新 增 講 師 辦 公 室 office 屬 性 ), 如 下 圖 所 示 :
5-2-2 功 能 相 依 的 種 類 完 全 相 依 完 全 相 依 (Full Dependency) 是 指 如 果 屬 性 A( 複 合 屬 性 ) 和 B 是 關 聯 表 屬 性,B 功 能 相 依 於 A, 而 且 B 沒 有 功 能 相 依 於 任 何 A 的 子 集, 則 稱 B 完 全 功 能 相 依 於 A 例 如 :Student_Course_Instructors 關 聯 表 的 完 全 相 依, 如 下 所 示 : sid name c_no title eid instructor eid office
5-2-2 功 能 相 依 的 種 類 部 分 相 依 部 分 相 依 (Partial Dependency) 是 指 如 果 屬 性 A( 複 合 屬 性 ) 和 B 是 關 聯 表 的 屬 性,B 功 能 相 依 於 A, 如 果 從 A 中 刪 除 部 分 屬 性 後 的 子 集 合, 仍 然 不 會 影 響 功 能 相 依, 則 稱 B 部 分 相 依 於 A 例 如 :Student_Course_Instructors 關 聯 表 的 部 分 相 依, 如 下 所 示 : { sid, c_no } name { sid, c_no} title { eid, instructor} office
5-2-2 功 能 相 依 的 種 類 遞 移 相 依 如 果 A B 和 C 是 關 聯 表 的 屬 性,A B 且 B C, 則 稱 C 是 遞 移 相 依 (Transitive Dependency) 於 A,A C 例 如 : Student_Course_Instructors 關 聯 表 的 遞 移 相 依, 如 下 所 示 : { sid, c_no, eid } office office 屬 性 是 遞 移 相 依 於 { sid, c_no, eid }, 因 為 : { sid, c_no, eid } instructor instructor office
5-2-3 找 出 關 聯 表 的 所 有 功 能 相 依 一 般 來 說, 我 們 可 以 使 用 兩 種 演 算 法 找 出 所 有 的 功 能 相 依, 如 下 所 示 : 暴 力 演 算 法 (Brute Force Algorithm): 一 種 破 解 密 碼 常 用 的 演 算 法, 以 作 苦 工 方 式 嘗 試 所 有 可 能 的 密 碼 組 合, 也 就 是 說, 在 關 聯 表 找 出 各 屬 性 間 的 所 有 可 能 組 合, 然 後 一 一 檢 查 是 否 符 合 功 能 相 依, 如 果 關 聯 表 的 屬 性 很 多, 相 對 的, 就 需 花 費 很 多 時 間 來 找 出 推 論 基 礎 演 算 法 (Inference-based Algorithm): 因 為 功 能 相 依 可 以 由 其 他 功 能 相 依 來 推 論 出 換 句 話 說, 推 論 基 礎 演 算 法 是 從 關 聯 表 已 知 的 功 能 相 依, 使 用 推 論 規 則 推 論 出 所 有 可 能 的 功 能 相 依
5-2-4 阿 姆 斯 壯 推 論 規 則 說 明 關 聯 表 R 如 果 擁 有 一 些 功 能 相 依 F, F 封 閉 性 (Closure of F) 寫 成 F+,F+ 是 F 本 身 和 由 其 所 推 論 出 的 所 有 功 能 相 依 的 聯 集 換 句 話 說,F+ 是 功 能 相 依 F, 再 加 上 從 F 推 論 出 的 所 有 功 能 相 依 的 集 合, 而 使 用 的 推 論 規 則 就 是 阿 姆 斯 壯 推 論 規 則 (Amstrong s Inference Rule) 阿 姆 斯 壯 推 論 規 則 可 以 從 某 些 關 聯 表 已 知 的 功 能 相 依 推 論 出 其 他 隱 含 的 功 能 相 依, 這 是 一 種 建 全 且 完 整 的 規 則, 其 意 義 如 下 所 示 : 建 全 (Sound): 阿 姆 斯 壯 推 論 規 則 不 會 導 出 多 餘 的 功 能 相 依 完 整 (Complete): 阿 姆 斯 壯 推 論 規 則 可 以 導 出 所 有 的 功 能 相 依
5-2-4 阿 姆 斯 壯 推 論 規 則 種 類 W. W. Armstrong 在 1974 年 提 出 三 種 推 論 規 則, 再 加 上 依 此 擴 充 出 另 外 兩 種 規 則 : 分 解 和 聯 集 規 則, 如 下 所 示 : 反 身 規 則 擴 充 規 則 遞 移 規 則 分 解 規 則 聯 集 規 則
5-2-4 阿 姆 斯 壯 推 論 規 則 反 身 規 則 反 身 規 則 (Reflexitivity Rule) 是 如 果 屬 性 B 是 屬 性 A 的 子 集 合, 則 A B, 其 定 義 如 下 : 若 :{ B1, B2,, Bn } { A1, A2,., An } 則 :{ A1, A2,., An } { B1, B2,, Bn } 反 身 規 則 的 圖 例, 如 下 圖 所 示 :
5-2-4 阿 姆 斯 壯 推 論 規 則 擴 充 規 則 擴 充 規 則 (Augmentation Rule) 是 如 果 A B 且 屬 性 C 是 屬 性 D 的 子 集 合, 則 AD BC, 其 定 義 如 下 : 若 :{ A1, A2,., An } { B1, B2,, Bn } 且 { C1,, Cn } { D1,..., Dn } 則 :{ A1, A2,., An, D1,, Dn } { B1, B2,, Bn, C1,..., Cn } 擴 充 規 則 的 圖 例,A B 依 反 身 規 則 就 是 B A, 如 下 圖 所 示 :
5-2-4 阿 姆 斯 壯 推 論 規 則 遞 移 規 則 遞 移 規 則 (Transitivity Rule) 是 如 果 A B 且 B C, 則 A C, 其 定 義 如 下 : 若 :{ A1,, An } { B1,, Bn } 且 { B1,, Bn } { C1,..., Cn } 則 :{ A1,..., An } { C1,..., Cn } 遞 移 規 則 的 圖 例, 如 下 圖 所 示 :
5-2-4 阿 姆 斯 壯 推 論 規 則 分 解 規 則 分 解 規 則 (Decomposition Rule) 是 如 果 A BC, 則 A B 且 A C, 其 定 義 如 下 : 若 :{ A1,, An } { B1,, Bn, C1,..., Cn } 則 :{ A1,..., An } { B1,..., Bn } 且 { A1,..., An } { C1,..., Cn } 分 解 規 則 的 圖 例, 如 下 圖 所 示 :
5-2-4 阿 姆 斯 壯 推 論 規 則 聯 集 規 則 聯 集 規 則 (Union Rule) 是 如 果 A B 且 A C, 則 A BC, 其 定 義 如 下 : 若 :{ A1,, An } { B1,, Bn } 且 { A1,, An } { C1,..., Cn } 則 :{ A1,..., An } { B1,..., Bn, C1,..., Cn } 聯 集 規 則 的 圖 例, 如 下 圖 所 示 :
5-2-4 阿 姆 斯 壯 推 論 規 則 範 例 現 在 我 們 只 需 使 用 阿 姆 斯 壯 推 論 規 則, 就 可 以 從 關 聯 表 現 有 的 功 能 相 依 推 論 出 其 他 功 能 相 依 例 如 : 關 聯 表 擁 有 已 知 的 三 條 功 能 相 依, 如 下 所 示 : 1: AB D 2: BD E 3: E C 請 使 用 上 述 三 條 功 能 相 依 推 論 出 AB C
5-2-4 阿 姆 斯 壯 推 論 規 則 推 論 過 程 步 驟 使 用 規 則 結 果 1 使 用 第 一 個 FD:AB D AB D AB 2 A B 使 用 反 身 規 則 AB AB 3 以 步 驟 2:AB AB 使 用 分 解 規 則 AB B 4 以 步 驟 1:AB D 和 步 驟 3:AB B 使 用 聯 集 規 則 AB BD 5 以 步 驟 4:AB BD 和 第 2 個 :BD E 使 用 遞 移 規 則 AB E 6 以 步 驟 5:AB E 和 第 3 個 :E C 使 用 遞 移 規 則 AB C
5-3 第 一 階 到 第 三 階 正 規 化 型 式 5-3-1 第 一 階 正 規 化 型 式 1NF 5-3-2 第 二 階 正 規 化 型 式 2NF 5-3-3 第 三 階 正 規 化 型 式 3NF 5-3-4 Boyce-Codd 正 規 化 型 式 BCNF 5-3-5 符 合 Boyce-Codd 正 規 化 型 式 的 範 例
5-3-1 第 一 階 正 規 化 型 式 1NF( 定 義 ) 第 一 階 正 規 化 型 式 是 在 處 理 關 聯 表 本 身, 並 沒 有 解 決 任 何 關 聯 表 存 在 功 能 相 依 所 造 成 的 資 料 重 複 或 操 作 異 常 等 問 題 其 定 義 如 下 所 示 : 定 義 5.2: 關 聯 表 R 符 合 第 一 階 正 規 化 型 式 (First Normal Form;1NF) 是 指 關 聯 表 的 每 一 個 定 義 域 (Domain) 都 是 單 元 值 (Atomic Value) 且 只 能 是 單 元 值, 也 就 是 刪 除 多 重 值 屬 性 型 態 (Multi-value Attribute Type) 與 複 合 屬 性 型 態 (Composite Attribute Type) 的 屬 性 簡 單 的 說, 上 述 定 義 是 指 關 聯 表 沒 有 多 重 值 和 複 合 屬 性
5-3-1 第 一 階 正 規 化 型 式 1NF( 範 例 ) 刪 除 複 合 屬 性 只 需 將 組 成 的 單 元 值 屬 性 展 開, 刪 除 多 重 值 屬 性 比 較 複 雜, 我 們 可 以 將 屬 性 分 割 成 關 聯 表 值 組 或 屬 性 例 如 :Students 關 聯 表 儲 存 學 生 的 選 課 資 料, 主 鍵 是 學 號 sid, 如 下 圖 所 示 :
5-3-1 第 一 階 正 規 化 型 式 1NF ( 方 法 一 : 分 割 成 不 同 的 關 聯 表 ) 關 聯 表 如 果 擁 有 多 重 值 屬 性 違 反 1NF, 第 一 階 正 規 化 可 以 將 多 重 值 屬 性 連 同 主 鍵 分 割 成 新 的 關 聯 表, 如 下 圖 所 示 :
5-3-1 第 一 階 正 規 化 型 式 1NF ( 方 法 二 : 分 割 成 值 組 ) 第 一 階 正 規 化 可 以 將 多 重 值 屬 性 改 成 重 複 值 組, 將 屬 性 的 每 一 個 多 重 值 都 新 增 一 筆 值 組, 如 下 圖 所 示 :
5-3-1 第 一 階 正 規 化 型 式 1NF ( 方 法 三 : 分 割 成 不 同 屬 性 ) 第 一 階 正 規 化 還 可 以 將 多 重 值 屬 性 配 合 空 值, 分 割 成 為 關 聯 表 的 多 個 屬 性, 不 過, 其 先 決 條 件 是 多 重 值 個 數 是 有 限 的 例 如 : 一 位 學 生 規 定 只 能 修 兩 門 課 程 ( 為 了 方 便 說 明, 筆 者 刪 除 講 師 與 教 室 部 分 的 屬 性 ), 如 下 圖 所 示 :
5-3-2 第 二 階 正 規 化 型 式 2NF( 定 義 ) 第 二 階 正 規 化 的 目 的 是 讓 每 一 個 關 聯 表 只 能 儲 存 同 類 資 料, 也 就 是 單 純 化 關 聯 表 儲 存 的 資 料 當 關 聯 表 符 合 1NF 後, 就 可 以 進 行 第 二 階 正 規 化, 其 定 義 如 下 所 示 : 定 義 5.3: 關 聯 表 R 符 合 第 二 階 正 規 化 型 式 (Second Normal Form;2NF) 是 指 關 聯 表 符 合 1NF, 而 且 所 有 非 主 鍵 (Primary Key) 的 屬 性 都 完 全 相 依 ( Fully Dependency) 於 主 鍵, 也 就 是 刪 除 所 有 部 份 相 依 的 屬 性 上 述 定 義 是 指 關 聯 表 中, 不 是 主 鍵 的 屬 性 需 要 完 全 相 依 於 主 鍵 ; 反 過 來 說, 就 是 刪 除 關 聯 表 所 有 部 分 相 依 (Partial Dependency) 的 屬 性
5-3-2 第 二 階 正 規 化 型 式 2NF( 範 例 ) 當 執 行 學 生 選 課 資 料 關 聯 表 的 第 一 階 正 規 化 後, 目 前 關 聯 表 已 經 分 割 成 Students 和 Classes 兩 個 關 聯 表 接 著 繼 續 執 行 Classes 關 聯 表 的 第 二 階 正 規 化, 如 下 圖 所 示 :
5-3-2 第 二 階 正 規 化 型 式 2NF ( 功 能 相 依 ) Classes 關 聯 表 的 主 鍵 是 (sid, c_no, eid), 關 聯 表 已 知 的 功 能 相 依, 如 下 所 示 : FD1:{ sid, c_no, eid } room FD2:c_no title FD3:eid { instructor, office }
5-3-2 第 二 階 正 規 化 型 式 2NF ( 正 規 化 結 果 ) 將 功 能 相 依 c_no title 和 eid { instructor, office } 兩 邊 屬 性 獨 立 成 關 聯 表, 左 邊 剩 下 的 屬 性 就 是 新 關 聯 表 的 主 鍵, 如 下 圖 所 示 :
5-3-3 第 三 階 正 規 化 型 式 3NF( 定 義 ) 第 三 階 正 規 化 的 目 的 是 移 除 哪 些 不 是 直 接 功 能 相 依 於 主 鍵 的 屬 性, 這 些 屬 性 是 借 由 另 一 個 屬 性 來 功 能 相 依 於 主 鍵 當 關 聯 表 符 合 2NF 後, 就 可 以 進 行 第 三 階 正 規 化, 其 定 義 如 下 所 示 : 定 義 5.4: 關 聯 表 R 符 合 第 三 階 正 規 化 型 式 (Third Normal Form;3NF) 是 指 關 聯 表 符 合 2NF, 而 且 所 有 非 主 鍵 ( Primary Key) 的 屬 性 都 只 能 功 能 相 依 (Functional Dependency) 於 主 鍵 ; 沒 有 功 能 相 依 於 其 他 非 主 鍵 的 屬 性, 即 刪 除 遞 移 相 依 的 屬 性 上 述 定 義 是 指 關 聯 表 中 不 屬 於 主 鍵 的 屬 性 都 只 能 功 能 相 依 於 主 鍵, 而 不 能 同 時 功 能 相 依 於 其 他 非 主 鍵 的 屬 性, 即 刪 除 關 聯 表 所 有 遞 移 相 依 (Transitive Dependency)
5-3-3 第 三 階 正 規 化 型 式 3NF( 範 例 ) 例 如 : 請 繼 續 第 5-3-2 節 的 關 聯 表, 執 行 Instructors 關 聯 表 的 第 三 階 正 規 化, 如 下 圖 所 示 :
5-3-3 第 三 階 正 規 化 型 式 3NF ( 找 出 遞 移 相 依 ) Instructors 關 聯 表 已 知 的 功 能 相 依, 如 下 所 示 : eid office eid o_no o_no office 上 述 第 office 屬 性 功 能 相 依 於 eid 主 鍵, 它 是 借 由 倒 數 第 o_no 屬 性 功 能 相 依 於 eid 和 office 屬 性 功 能 相 依 於 o_no 屬 性 所 得 到, 所 以 eid office 是 遞 移 相 依, 如 下 圖 所 示 :
5-3-3 第 三 階 正 規 化 型 式 3NF ( 正 規 化 結 果 ) 在 執 行 第 三 階 正 規 化 時, 就 是 將 造 成 遞 移 相 依 的 A B 功 能 相 依 兩 邊 的 屬 性 獨 立 成 關 聯 表, 左 邊 的 屬 性 就 是 新 關 聯 表 的 主 鍵, 如 下 圖 所 示 :
5-3-4 Boyce-Codd 正 規 化 型 式 BCNF( 說 明 ) Boyce-Codd 正 規 化 型 式 可 以 視 為 一 種 更 嚴 格 的 第 三 階 正 規 化 型 式, 其 目 的 是 保 證 關 聯 表 的 所 有 屬 性 都 功 能 相 依 於 侯 選 鍵 Boyce-Codd 正 規 化 可 以 讓 所 有 屬 性 都 完 全 功 能 相 依 於 候 選 鍵, 而 不 是 候 選 鍵 的 部 分 屬 性 換 句 話 說,Boyce-Codd 正 規 化 是 在 處 理 關 聯 表 擁 有 多 個 候 選 鍵 的 特 殊 情 況, 在 Boyce-Codd 正 規 化 處 理 的 關 聯 表 至 少 需 要 擁 有 二 個 或 更 多 個 候 選 鍵, 且 這 兩 個 候 選 鍵 是 : 複 合 候 選 鍵 在 複 合 候 選 鍵 之 間 擁 有 重 疊 屬 性, 也 就 是 說 至 少 擁 有 一 個 相 同 屬 性
5-3-4 Boyce-Codd 正 規 化 型 式 BCNF( 定 義 ) BCNF 正 規 化 型 式 的 定 義, 如 下 所 示 : 定 義 5.5: 關 聯 表 R 符 合 BCNF 正 規 化 型 式 (Boyce- Codd Normal Form;BCNF) 是 指 關 聯 表 所 有 主 要 的 功 能 相 依 A B,A 一 定 且 只 可 以 是 候 選 鍵 ( Candidate Keys), 也 就 是 刪 除 只 功 能 相 依 候 選 鍵 部 分 屬 性 的 功 能 相 依 簡 單 的 說, 上 述 定 義 是 指 關 聯 表 中, 主 要 功 能 相 依 A B 的 左 邊 屬 性 A 稱 為 決 定 屬 性 ( Determinant), 決 定 屬 性 一 定 是 候 選 鍵 或 主 鍵
5-3-4 Boyce-Codd 正 規 化 型 式 BCNF( 範 例 ) 例 如 : 學 生 身 份 證 字 號 (SSN) 與 成 績 (grade) 的 Students 關 聯 表, 在 Students 關 聯 表 擁 有 兩 個 候 選 鍵 ( sid, c_no ) 和 ( SSN, c_no ), 如 下 圖 所 示 : 兩 個 候 選 鍵 擁 有 重 疊 屬 性 c_no, 在 候 選 鍵 之 間 擁 有 功 能 相 依 SSN sid
5-3-4 Boyce-Codd 正 規 化 型 式 BCNF( 正 規 化 結 果 ) 例 如 : 執 行 前 述 的 關 聯 表 Students 關 聯 表 的 BCNF 正 規 化, 如 下 圖 所 示 : 使 用 sid 屬 性 進 行 分 割 使 用 SSN 屬 性 進 行 分 割
5-3-5 符 合 Boyce-Codd 正 規 化 型 式 的 範 例 說 明 Boyce-Codd 正 規 化 型 式 的 條 件 是 關 聯 表 至 少 擁 有 二 個 或 多 個 複 合 候 選 鍵, 不 過, 關 聯 表 擁 有 兩 個 複 合 候 選 鍵, 並 不 表 示 一 定 需 要 進 行 BCNF 正 規 化, 兩 個 複 合 候 選 鍵 的 關 聯 表 仍 然 可 能 符 合 BCNF 例 如 : 學 生 期 末 考 座 位 的 Exams 關 聯 表, 因 為 期 末 考 分 成 多 天 進 行 考 試, 所 以 學 生 各 科 的 考 試 座 位 可 能 不 同, 如 下 圖 所 示 :
5-3-5 符 合 Boyce-Codd 正 規 化 型 式 的 範 例 範 例 說 明 在 Exams 關 聯 表 有 兩 個 候 選 鍵, 如 下 所 示 : ( sid, c_no ) ( seat_no, c_no ) 兩 個 候 選 鍵 擁 有 重 疊 屬 性 c_no, 在 候 選 鍵 之 間 並 沒 有 功 能 相 依 seat_no sid, 例 如 :seat_no 屬 性 值 C121-03, 同 時 有 S001 和 S003 兩 位 學 生 在 不 同 科 目 進 行 考 試 在 Exams 關 聯 表 擁 有 的 功 能 相 依 清 單, 如 下 所 示 : { sid, c_no } seat_no { seat_no, c_no } sid 因 為 功 能 相 依 的 決 定 屬 性 都 是 候 選 鍵, 符 合 BCNF 定 義 Exams 關 聯 表 雖 然 擁 有 兩 組 候 選 鍵, 但 是 關 聯 表 不 但 符 合 3NF, 而 且 符 合 BCNF
5-4 多 重 值 相 依 與 第 四 階 正 規 化 型 式 5-4-1 多 重 值 相 依 (Multi-valued Dependency) 5-4-2 第 四 階 正 規 化 型 式 4NF
5-4-1 多 重 值 相 依 說 明 第 四 階 正 規 化 型 式 的 基 礎 是 多 重 值 相 依 ( Multi-valued Dependency;MVD), 這 是 1977 年 R. Fagin 所 提 出, 屬 於 功 能 相 依 的 推 廣 定 義, 或 是 說, 功 能 相 依 是 多 重 值 相 依 的 一 種 特 例 多 重 值 相 依 (Multi-valued Dependency) 的 先 決 條 件 是 關 聯 表 擁 有 3 個 以 上 屬 性, 因 為 多 重 值 相 依 的 定 義 有 些 複 雜, 我 們 準 備 使 用 一 個 關 聯 表 範 例 來 說 明 多 重 值 相 依
5-4-1 多 重 值 相 依 範 例 例 如 : 一 門 課 可 能 由 多 位 講 師 授 課, 每 一 門 課 可 以 使 用 多 本 教 課 書, 儲 存 這 些 資 料 的 Course_Instructor_Textbook 關 聯 表, 簡 稱 為 CIT, 如 下 圖 所 示 :
5-4-1 多 重 值 相 依 定 義 定 義 5.6: 多 重 值 相 依 (Multi-valued Dependency) 以 關 聯 表 R 為 例, 若 關 聯 表 R 是 由 屬 性 A B 與 C 組 成 R(A, B, C),A B 與 C 可 以 是 複 合 屬 性, 則 屬 性 B 多 重 值 相 依 (Multi-valued Dependent) 於 A, 或 A 多 重 決 定 ( Multi-determines) 於 B, 寫 成 : R.A R.B 或 簡 寫 成 : A B 若 關 聯 表 R 擁 有 A B 多 重 值 相 依, 則 表 示 存 在 t1 和 t2 值 組 且 t1(a) = t2(a), 且 另 外 存 在 二 個 值 組 t3 和 t4 滿 足 下 列 條 件 : t1(a) = t2(a) = t3(a) = t4(a) t3(b) = t1(b) t3(c) = t2(c) t4(b) = t2(b) t4(c) = t1(c)
5-4-1 多 重 值 相 依 定 義 說 明 多 重 值 相 依 的 定 義 有 些 複 雜, 以 CIT 關 聯 表 為 例, 因 為 關 聯 表 CIT 擁 有 course textbook(a B) 多 重 值 相 依, 所 以 在 關 聯 表 可 以 找 到 交 換 instructor 屬 性 的 2 對 值 組 t1 t2 和 t3 t4, 如 下 圖 所 示 :
5-4-1 多 重 值 相 依 異 常 狀 況 在 CIT 關 聯 表 擁 有 很 多 重 複 資 料, 這 些 重 複 資 料 會 導 致 新 增 或 更 新 異 常, 例 如 : 新 增 講 師 陳 慶 新 教 授 程 式 語 言 這 門 課, 雖 然 只 有 一 門 課, 但 是 需 要 同 時 新 增 3 筆 值 組, 每 一 個 值 組 是 一 本 教 課 書, 否 則 就 會 產 生 資 料 不 一 致 的 問 題
5-4-1 多 重 值 相 依 推 論 規 則 1 多 重 值 相 依 的 推 論 規 則 是 1977 年 C. Beeri R. Fagin 和 J. H. Howard 擴 充 自 阿 姆 斯 壯 推 論 規 則, 其 中 前 3 個 推 論 規 是 源 自 功 能 相 依, 如 下 所 示 : FD 反 身 規 則 (FD Reflexitivity): 如 果 屬 性 B 是 屬 性 A 的 子 集 合, 則 A B FD 擴 充 規 則 (FD Augmentation): 如 果 A B 且 屬 性 C 是 屬 性 D 的 子 集 合, 則 AD BC FD 遞 移 規 則 (FD Transitivity): 如 果 A B 且 B C, 則 A C MVD 擴 充 規 則 (MVD Augmentation): 如 果 A B 且 屬 性 C 是 屬 性 D 的 子 集 合, 則 AD BC MVD 遞 移 規 則 (MVD Transitivity): 如 果 A B 且 B C, 則 A C B
5-4-1 多 重 值 相 依 推 論 規 則 2 MVD 互 補 規 則 (MVD Complementation): 如 果 A B, 則 A (R A B) MVD 聯 集 規 則 (MVD Union): 如 果 A B 且 A C, 則 A BC MVD 分 解 規 則 (MVD Decomposition): 如 果 A BC, 則 A B C,A B - C 且 A C - B 複 製 規 則 (Replication 或 稱 為 升 級 Promotion): 如 果 A B, 則 A B 合 併 規 則 (Coalescence): 如 果 A B 且 C D,D 是 B 的 子 集 合,C 與 B 的 交 集 是 空 集 合, 則 A D 偽 裝 遞 移 規 則 (Pseudotransitivity): 如 果 A B 且 CB D, 則 CA D - CB 混 合 偽 裝 遞 移 規 則 (Mixed Pseudotransitivity): 如 果 A B 且 AB C, 則 A C - B
5-4-2 第 四 階 正 規 化 型 式 4NF( 定 義 ) 第 四 階 正 規 化 的 目 的 是 刪 除 多 重 值 相 依, 當 關 聯 表 符 合 BCNF 後, 如 果 關 聯 表 擁 有 多 重 值 相 依, 就 需 要 進 行 第 四 階 正 規 化, 其 定 義 如 下 所 示 : 定 義 5.7: 關 聯 表 R 符 合 第 四 階 正 規 化 型 式 (Fourth Normal Form;4NF) 是 指 關 聯 表 符 合 BCNF, 而 且 所 有 多 重 值 相 依 (Multi-valued Dependency) 都 是 功 能 相 依 (Functional Dependency), 也 就 是 刪 除 多 重 值 相 依 簡 單 的 說, 上 述 定 義 是 指 關 聯 表 沒 有 存 在 的 多 重 值 相 依, 只 有 功 能 相 依
5-4-2 第 四 階 正 規 化 型 式 4NF( 正 規 化 ) CIT 關 聯 表 擁 有 2 個 多 重 值 相 依, 如 下 所 示 : course textbook course instructor 執 行 第 四 階 正 規 化, 只 需 將 多 重 值 相 依 A B 兩 邊 的 屬 性 獨 立 成 關 聯 表 即 可, 如 下 圖 所 示 :
5-5 合 併 相 依 與 第 五 階 正 規 化 型 式 5-5-1 合 併 相 依 (Join Dependency) 5-5-2 第 五 階 正 規 化 型 式 5NF
5-5 合 併 相 依 與 第 五 階 正 規 化 型 式 第 五 階 正 規 化 型 式 的 基 礎 則 是 擴 充 多 重 值 相 依 的 合 併 相 依 (Join Dependency) 前 四 階 正 規 化 主 要 是 將 關 聯 表 每 一 種 相 依, 分 割 成 兩 個 關 聯 表 來 滿 足 各 階 的 正 規 化 型 式, 但 是, 一 些 非 常 特 殊 的 關 聯 表, 如 果 只 分 割 成 兩 個 並 不 能 解 決 資 料 重 複 和 異 常 操 作 問 題 此 時, 我 們 需 要 使 用 第 五 階 正 規 化 型 式, 將 關 聯 表 分 割 成 三 個 或 以 上 的 關 聯 表
5-5-1 合 併 相 依 說 明 與 範 例 合 併 相 依 (Join Dependency) 是 將 三 個 循 環 擁 有 關 聯 性 的 屬 性 置 於 同 一 個 關 聯 表 例 如 : 每 一 個 科 系 (department ) 開 多 門 課 ; 課 程 (course) 可 以 給 多 位 學 生 修 ; 學 生 ( student) 可 以 修 不 同 科 系 的 課 三 個 屬 性 循 環 擁 有 關 聯 性, 而 且 儲 存 在 同 一 個 關 聯 表 Department_Course_Student, 簡 稱 DCS, 如 下 圖 所 示 :
5-5-1 合 併 相 依 投 影 分 割 將 DCS 關 聯 表 使 用 投 影 運 算 ( 投 影 運 算 是 取 出 部 分 關 聯 表 的 屬 性, 即 關 聯 表 屬 性 集 合 的 子 集 合, 詳 見 第 16-2-2 節 的 說 明 ) 分 割 成 三 個 關 聯 表 :DC (department, course) CS (course, student) SD (student, department), 如 下 圖 所 示 :
5-5-1 合 併 相 依 自 然 合 併 先 執 行 DC 和 CS 的 自 然 合 併, 接 著 再 與 SD 進 行 自 然 合 併, 就 恢 復 成 原 始 關 聯 表 DCS, 如 右 圖 所 示 :
5-5-1 合 併 相 依 定 義 合 併 相 依 是 多 重 值 相 依 的 推 廣 定 義, 其 定 義 如 下 所 示 : 定 義 5.8: 合 併 相 依 (Join Dependency) 以 關 聯 表 R 為 例, 若 關 聯 表 R 等 於 自 然 合 併 運 算 R1 R2, Rn,Ri 是 關 聯 表 R 屬 性 的 子 集 合, 則, 關 聯 表 R 滿 足 合 併 相 依 (R1, R2,, Rn) 以 上 述 定 義 來 檢 視 關 聯 表 DCS, 關 聯 表 DCS 可 以 使 用 投 影 運 算 分 割 成 DC CS 和 SD 三 個 關 聯 表, 執 行 DC CS SD 自 然 合 併 運 算 就 可 以 還 原 成 原 始 DCS 關 聯 表, 所 以 關 聯 表 DCS 擁 有 合 併 相 依
5-5-1 合 併 相 依 異 常 狀 況 關 聯 表 如 果 擁 有 合 併 相 依, 在 新 增 和 刪 除 時 就 會 產 生 異 常 情 況, 例 如 :DCS 關 聯 表 擁 有 合 併 相 依, 我 們 準 備 以 新 增 異 常 為 例 來 說 明 合 併 相 依 產 生 的 異 常 情 況 在 DCS 關 聯 表 新 增 一 筆 值 組 { 資 管 系, 101, 江 小 魚 }, 如 下 圖 所 示 : 將 上 述 關 聯 表 以 投 影 運 算 分 割 成 DC CS 和 SD 三 個 關 聯 表, 再 將 它 使 用 合 併 運 算 結 合 起 來, 可 以 發 現 多 了 一 筆 值 組
5-5-2 第 五 階 正 規 化 型 式 5NF( 定 義 ) 第 五 階 正 規 化 是 在 處 理 合 併 相 依 的 問 題, 當 關 聯 表 符 合 4NF 後, 如 果 關 聯 表 擁 有 合 併 相 依, 就 需 要 進 行 第 五 階 正 規 化, 其 定 義 如 下 所 示 : 定 義 5.9: 關 聯 表 R 符 合 第 五 階 正 規 化 型 式 (Fifth Normal Form ;5NF), 也 稱 為 投 影 - 合 併 正 規 化 型 式 (Project-Join Normal Form;PJNF), 這 是 指 關 聯 表 符 合 4NF, 且 所 有 合 併 相 依 (R1, R2,, Rn) 的 關 聯 表 屬 性 子 集 Ri, 都 是 關 聯 表 R 的 超 鍵 (Superkey), 也 就 是 刪 除 合 併 相 依 上 述 定 義 是 指 關 聯 表 沒 有 存 在 合 併 相 依 以 上 一 節 的 DCS 關 聯 表 為 例,DCS 關 聯 表 擁 有 合 併 相 依, 所 以 不 符 合 5NF
5-5-2 第 五 階 正 規 化 型 式 5NF( 正 規 化 ) DCS 關 聯 表 擁 有 合 併 相 依, 可 以 使 用 投 影 運 算 進 行 再 次 分 割, 並 不 符 合 5NF, 所 以 需 要 執 行 第 五 階 正 規 化, 使 用 投 影 運 算 分 割 成 三 個 關 聯 表, 如 下 圖 所 示 :