資 料 庫 正 規 化
正 規 化 的 概 念 何 謂 正 規 化 (Normalization)?? 就 是 結 構 化 分 析 與 設 計 中, 建 構 資 料 模 式 所 運 用 的 一 個 技 術, 其 目 的 是 為 了 降 低 資 料 的 重 覆 性 與 避 免 更 新 異 常 的 情 況 發 生 因 此, 就 必 須 將 整 個 資 料 表 中 重 複 性 的 資 料 剔 除, 否 則 在 關 聯 表 中 會 造 成 新 增 異 常 刪 除 異 常 修 改 異 常 的 狀 況 發 生 一 般 而 言, 正 規 化 的 精 神 就 是 讓 資 料 庫 中 重 複 的 欄 位 資 料 減 到 最 少, 並 且 能 快 速 的 找 到 資 料, 以 提 高 關 聯 性 資 料 庫 的 效 能 目 的 1. 降 低 資 料 重 複 性 (Data Redundancy) 2. 避 免 資 料 更 新 異 常 (Anomalies)
資 料 庫 正 規 化 (Normalization) 定 義 是 指 將 原 先 關 聯 ( 表 格 ) 的 所 有 資 訊, 在 分 解 之 後, 仍 能 由 數 個 新 關 聯 ( 表 格 ) 中 經 過 合 併 得 到 相 同 的 資 訊 即 所 謂 的 無 損 失 分 解 (Lossless decomposition) 的 觀 念 無 損 失 分 解 觀 念 當 關 聯 表 R 被 分 解 成 數 個 關 聯 表 R1, R2,, Rn 時, 則 可 以 再 透 過 合 併 R1 R2 Rn 得 到 相 同 的 資 訊 R 如 下 圖 所 示 分 解 合 併 合 併 合 併 分 解 分 解
正 規 化 的 規 則 資 料 庫 在 正 規 化 時 會 有 一 些 規 則, 並 且 每 條 規 則 都 稱 為 正 規 形 式 如 果 符 合 第 一 條 規 則, 則 資 料 庫 就 稱 為 第 一 正 規 化 形 式 (1NF) 如 果 符 合 前 二 條 規 則, 則 資 料 庫 就 被 視 為 屬 於 第 二 正 規 化 形 式 (2NF) 雖 然 資 料 庫 的 正 規 化 最 多 可 以 進 行 到 第 五 正 規 化 形 式, 但 是 在 實 務 上, BCNF 被 視 為 大 部 分 應 用 程 式 所 需 的 最 高 階 正 規 形 式
從 上 圖 中, 我 們 可 以 清 楚 得 知, 正 規 化 是 循 序 漸 進 的 過 程, 亦 即 資 料 表 必 須 滿 足 第 一 正 規 化 的 條 件 之 後, 才 能 進 行 第 二 正 規 化 換 言 之, 第 二 正 規 化 必 須 建 立 在 符 合 第 一 正 規 化 的 資 料 表 上, 依 此 類 推
正 規 化 步 驟 在 資 料 表 正 規 化 的 過 程 (1NF 到 BCNF) 中, 每 一 個 階 段 都 是 以 欄 位 的 相 依 性, 做 為 分 割 資 料 表 的 依 據 之 一 其 完 整 的 正 規 化 步 驟 如 下 圖 所 示 :
第 一 正 規 化 (1NF) 定 義 是 指 在 資 料 表 中 的 所 有 記 錄 之 屬 性 內 含 值 都 是 基 元 值 (Atomic Value) 亦 即 無 重 覆 項 目 群 實 例 假 設 現 在 有 一 份 某 某 科 技 大 學 的 學 生 選 課 資 料 表, 如 下 表 所 示 : 學 生 選 課 資 料 表
我 們 可 以 將 原 始 資 料 利 用 二 維 表 格 來 儲 存 學 生 選 課 資 料 表 未 正 規 化 的 資 料 表 : 學 生 選 課 資 料 報 表 二 維 表 格 來 儲 存
因 此, 我 們 發 現 有 許 多 屬 性 的 內 含 值 都 具 有 二 個 或 二 個 以 上 的 值 ( 亦 稱 為 重 複 資 料 項 目 ),, 其 原 因 : 尚 未 進 行 第 一 正 規 化 未 正 規 化 的 資 料 表 : 學 生 選 課 資 料 報 表 未 符 合 1NF 資 料 表 的 缺 點 以 上 資 料 表 中 的 課 程 代 碼 課 程 名 稱 學 分 數 必 選 修 成 績 老 師 編 號 及 老 師 姓 名 欄 位 的 長 度 無 法 確 定, 因 為 學 生 要 選 修 多 少 門 課 程, 無 法 事 先 得 知 ( 李 碩 安 同 學 選 了 2 門, 李 碩 崴 同 學 選 了 3 門 ),, 因 此, 必 須 要 預 留 很 大 的 空 間 給 這 七 個 欄 位, 如 此 反 而 造 成 儲 存 空 間 的 浪 費 重 複 資 料 項 目
第 一 正 規 化 的 規 則 1. 每 一 個 欄 位 只 能 有 一 個 基 元 值 (Atomic) 即 單 一 值 例 如 : 課 程 名 稱 欄 位 中 不 能 存 入 兩 科 或 兩 科 以 上 的 課 程 名 稱 2. 沒 有 任 何 兩 筆 以 上 的 資 料 是 完 全 重 覆 3. 資 料 表 中 有 主 鍵, 而 其 他 所 有 的 欄 位 都 相 依 於 主 鍵 例 如 1: 姓 名 與 性 別 欄 位 都 相 依 於 學 號 欄 位 例 如 2: 課 程 名 稱 學 分 數 必 選 修 老 師 編 號 及 老 師 姓 名 相 依 於 課 程 代 碼 欄 位 例 如 3: 成 績 欄 位 相 依 於 學 號 與 課 程 代 碼 欄 位
第 一 正 規 化 的 作 法 : 作 法 將 重 複 的 資 料 項 分 別 儲 存 到 不 同 的 記 錄 中, 並 加 上 適 當 的 主 鍵 步 驟 一 : 檢 查 是 否 存 在 重 複 資 料 項
步 驟 二 : 將 重 複 資 料 項 分 別 儲 存 到 不 同 的 記 錄 中, 並 加 上 適 當 的 主 鍵 未 經 正 規 化 前 的 學 生 選 課 表 重 複 資 料 項 經 過 正 規 化 後 的 學 生 選 課 表 (1NF) 儲 存 到 不 同 的 記 錄
經 過 正 規 化 後 的 學 生 選 課 表 (1NF) 在 經 由 第 一 正 規 化 之 後, 使 得 每 一 個 欄 位 內 只 能 有 一 個 資 料 ( 基 元 值 ) 雖 然 增 加 了 許 多 記 錄, 但 每 一 個 欄 位 的 長 度 及 數 目 都 可 以 固 定, 而 且 我 們 可 用 課 程 代 碼 欄 位 加 上 學 號 欄 位 當 作 主 鍵, 使 得 在 查 詢 某 學 生 修 某 課 程 的 成 績 時, 就 非 常 方 便 而 快 速 了
第 二 正 規 化 (2NF) 在 完 成 了 第 一 正 規 化 之 後, 是 否 發 現 在 資 料 表 中 產 生 許 多 重 複 的 資 料 如 此, 不 但 浪 費 儲 存 的 空 間, 更 容 易 造 成 新 增 修 改 及 刪 除 資 料 時 的 異 常 狀 況, 說 明 如 下 (1) 新 增 異 常 檢 查 (Insert Anomaly) 無 法 先 新 增 課 程 資 料, 如 課 程 代 碼 及 課 程 名 稱, 要 等 選 課 之 後, 才 能 新 增 原 因 : 以 上 的 新 增 動 作 違 反 實 體 完 整 性 規 則,, 因 為, 主 鍵 或 複 合 主 鍵 不 可 以 為 空 值 NULL
(2) 修 改 異 常 檢 查 (Update Anomaly) 網 頁 設 計 課 程 重 覆 多 次, 因 此, 修 改 網 頁 設 計 課 程 的 成 績 時, 可 能 有 些 記 錄 未 修 改 到, 造 成 資 料 的 不 一 致 現 象 例 如 : 有 選 網 頁 設 計 課 程 的 同 學 之 成 績 各 加 5 分, 可 能 會 有 些 同 學 有 加 分, 而 有 些 同 學 卻 沒 有 加 分, 導 致 資 料 不 一 致 的 情 況
(3) 刪 除 異 常 檢 查 (Delete Anomaly) 當 刪 除 #4 學 生 的 記 錄 時, 同 時 也 會 刪 除 課 程 名 稱 學 分 數 及 相 關 的 資 料 所 以 導 致 計 概 課 程 的 2 學 分 數 也 同 時 被 刪 除 了 綜 合 上 述 的 三 種 異 常 現 象, 所 以, 我 們 必 須 進 行 第 二 階 正 規 化, 來 消 除 這 些 問 題
第 二 正 規 化 的 規 則 如 果 資 料 表 符 合 以 下 的 條 件, 我 們 說 這 個 資 料 表 符 合 第 二 階 正 規 化 的 形 式 (Second Normal Form, 簡 稱 2NF): 符 合 1NF 每 一 非 鍵 屬 性 ( 如 : 姓 名 性 別 ) 必 須 完 全 相 依 於 主 鍵 ( 學 號 );; 即 不 可 部 分 功 能 相 依 於 主 鍵 換 言 之,, 部 分 功 能 相 依 只 有 當 主 鍵 是 由 多 個 欄 位 組 成 時 才 會 發 生 ( 亦 即 複 合 主 鍵 ),, 也 就 是 當 某 些 欄 位 只 與 主 鍵 中 的 部 分 欄 位 有 相 依 性, 而 與 另 一 部 分 的 欄 位 沒 有 相 依 性
第 二 正 規 化 的 作 法 分 割 資 料 表 ; 亦 即 將 部 分 功 能 相 依 的 欄 位 分 割 出 去, 再 另 外 組 成 新 的 資 料 表 其 步 驟 如 下 : 步 驟 一 : 檢 查 是 否 存 在 部 分 功 能 相 依 姓 名 只 相 依 於 學 號 課 程 名 稱 只 相 依 於 課 程 代 碼 在 上 面 的 資 料 表 中, 主 鍵 是 由 學 號 + 課 程 代 碼 兩 個 欄 位 所 組 成, 但 與 學 號 有 有 相 依 性, 亦 即 ( 姓 名, 性 別 ) 相 依 於 相 依 於 學 號, 而 兩 個 欄 位 所 組 成, 但 姓 名 和 性 別 只, 而 課 程 名 稱 只 與 相 依 性, 亦 即 ( 課 程 名 稱, 學 分 數, 必 選 修, 老 師 編 號, 老 師 姓 名 ) 相 依 於 只 與 課 程 代 碼 有 相 依 於 課 程 代 碼 因 此, 學 號 是 複 合 主 鍵 ( 學 號, 課 程 代 碼 ) 的 一 部 份 存 在 部 分 功 能 相 依
步 驟 二 : 將 部 分 功 能 相 依 的 欄 位 分 割 出 去, 再 另 外 組 成 新 的 資 料 表 我 們 將 選 課 資 料 表 分 割 成 三 個 較 小 的 資 料 表 ( 加 底 線 的 欄 位 為 主 鍵 ): 一 學 生 資 料 表 ( 學 號, 姓 名, 性 別 ) 學 號 姓 名 性 別 001 李 碩 安 002 李 碩 崴 男 男 二 成 績 資 料 表 ( 學 號, 課 程 代 碼, 成 績 ) 學 號 課 程 代 碼 成 績 001 C001 74 001 C002 93 002 C002 63 002 C003 82 002 C005 94
三 課 程 資 料 表 ( 課 程 代 碼, 課 程 名 稱, 學 分 數, 必 選 修, 老 師 編 號, 老 師 姓 名 ) 課 程 代 碼 課 程 名 稱 學 分 數 必 選 修 老 師 編 號 老 師 姓 名 C001 C002 程 式 語 言 4 必 T001 網 頁 設 計 3 選 T002 C003 計 概 2 必 T003 C005 網 路 教 學 4 選 T005 李 安 張 三 李 四 王 五 在 第 二 正 規 化 之 後, 產 生 三 個 資 料 表, 分 別 為 學 生 資 料 表 成 績 資 料 表 及 課 程 資 料 表, 除 了 課 程 資 料 表 之 外, 其 餘 兩 個 資 料 表 ( 學 生 資 料 表 與 成 績 資 料 表 ) 都 已 符 合 2NF, 3NF 及 BCNF
第 三 正 規 化 的 規 則 如 果 資 料 表 符 合 以 下 條 件, 我 們 就 說 這 個 資 料 表 符 合 第 三 階 正 規 化 的 形 式 (Third Normal Form, 簡 稱 3NF): 符 合 2NF 各 欄 位 與 主 鍵 之 間 沒 有 遞 移 相 依 的 關 係 如 何 找 遞 移 相 依 呢? 若 要 找 出 資 料 表 中 各 欄 位 與 主 鍵 之 間 的 遞 移 相 依 性, 最 簡 單 的 方 法 就 是 從 左 到 右 掃 瞄 資 料 表 中 各 欄 位 有 沒 有 與 主 鍵 無 關 的 相 依 性 存 在 可 能 的 情 況 如 下 : 1. 如 果 有 存 在 時, 則 代 表 有 遞 移 相 依 的 關 係 2. 如 果 有 不 存 在 時, 則 代 表 沒 有 遞 移 相 依 的 關 係
第 三 正 規 化 的 作 法 分 割 資 料 表 ; 亦 即 將 遞 移 相 依 或 間 接 相 依 的 欄 位 分 割 出 去, 再 另 外 組 成 新 的 資 料 表 其 步 驟 如 下 : 步 驟 一 : 檢 查 是 否 存 在 遞 移 相 依 由 於 每 一 門 課 程 都 會 有 授 課 的 老 師, 因 此,, 老 師 編 號 相 依 於 課 程 代 碼 並 且 老 師 姓 名 相 依 於 教 師 編 號, 因 此, 存 在 有 與 主 鍵 無 關 的 相 依 性 亦 即 存 在 老 師 姓 名 與 主 鍵 ( 課 程 代 碼 ) 無 關 的 相 依 性 存 在 遞 移 相 依
上 述 課 程 資 料 表 中 的 [ 課 程 名 稱 ] [ 學 分 數 ] [ 必 選 修 ] [ 老 師 編 號 ] 都 直 接 相 依 於 主 鍵 [ 課 程 代 碼 ]( 簡 單 的 說, 這 些 都 是 課 程 資 料 的 必 需 欄 位 ),, 而 [ 老 師 姓 名 ] 是 直 接 相 依 於 [ 老 師 編 號 ],, 然 後 才 間 接 相 依 於 [ 課 程 代 碼 ],, 它 並 不 是 直 接 相 依 於 [ 課 程 代 碼 ],, 稱 為 遞 移 相 依
步 驟 二 : 將 遞 移 相 依 的 欄 位 分 割 出 去, 再 另 外 組 成 新 的 資 料 表 因 此, 我 們 將 課 程 資 料 表 分 割 為 二 個 資 料 表, 並 且 利 用 外 鍵 (F.K.) 來 連 接 二 個 資 料 表 如 下 圖 所 示
第 三 正 規 化 後 的 四 個 表 格 在 我 們 完 成 第 三 正 規 化 後, 共 產 生 了 四 個 表 格, 如 下 表 所 示 : 第 二 正 規 化 產 生 的 表 格 第 三 正 規 化 產 生 的 表 格
BCNF 正 規 化 是 由 Boyce 和 Codd 於 1974 年 所 提 出 來 的 3NF 的 改 良 式 其 條 件 比 3NF 更 加 嚴 苛 因 此 每 一 個 符 合 BCNF 的 關 聯 一 定 也 是 3NF 對 於 大 部 分 資 料 庫 來 說, 通 常 只 需 要 執 行 到 第 三 階 段 的 正 規 化 就 足 夠 了 適 用 時 機 如 果 資 料 表 的 主 鍵 是 由 多 個 欄 位 組 成 的, 則 必 須 再 執 行 Boyce- Codd 正 規 化
BCNF 的 規 則 如 果 資 料 表 的 主 鍵 只 由 單 一 欄 位 組 合 而 成, 則 符 合 第 三 階 正 規 化 的 資 料 表, 亦 符 合 BCNF(Boyce-Codd Codd Normal Form) 正 規 化 如 果 資 料 表 的 主 鍵 由 多 個 欄 位 組 成 ( 又 稱 為 複 合 主 鍵 ), 則 資 料 表 就 必 須 要 符 合 以 下 條 件, 我 們 就 說 這 個 資 料 表 符 合 BCNF(Boyce- Codd Normal Form) 正 規 化 的 形 式 1. 符 合 3NF 的 格 式 2. 主 鍵 中 的 各 欄 位 不 可 以 相 依 於 其 他 非 主 鍵 的 欄 位
檢 驗 成 績 資 料 表 是 否 滿 足 BCNF 規 範 由 於 在 我 們 完 成 第 三 正 規 化 之 後, 已 經 分 割 成 四 個 資 料 表, 其 中 成 績 資 料 表 的 主 鍵 是 由 多 個 欄 位 組 成 ( 又 稱 為 複 合 主 鍵 ) 因 此, 我 們 利 用 BCNF(Boyce-Codd Codd Normal Form) 正 規 化 的 條 件, 來 檢 驗 成 績 資 料 表 : 成 績 資 料 表 ( 學 號, 課 程 代 碼, 成 績 ) 說 明 : 學 號 課 程 代 碼 成 績 001 C001 74 001 C002 93 002 C002 63 002 C003 82 002 C005 94 成 績 欄 位 相 依 於 課 程 代 碼 及 學 號 欄 位, 對 課 程 代 碼 欄 位 而 言, 並 沒 有 相 依 於 成 績 欄 位 ; 對 學 號 欄 位 而 言, 也 沒 有 相 依 於 成 績 欄 位 所 以 成 績 資 料 表 是 符 合 Boyce-Codd 正 規 化 的 形 式 的 資 料 表