F、結構化分析與設計： 資料塑模(下) 資料塑模工具︰實體關係圖 實體關係圖之建構 實體關係圖轉關聯表 正規化 關聯表資料字典 七個轉換規則

Presentation on theme: "F、結構化分析與設計： 資料塑模(下) 資料塑模工具︰實體關係圖 實體關係圖之建構 實體關係圖轉關聯表 正規化 關聯表資料字典 七個轉換規則"— Presentation transcript:

1 F、結構化分析與設計： 資料塑模(下) 資料塑模工具︰實體關係圖 實體關係圖之建構 實體關係圖轉關聯表 正規化 關聯表資料字典 七個轉換規則
涵義與資料異常、相依、正規化實務 關聯表資料字典

2 註︰關係連到實體若為雙線，表示完全參與關係，即實體之所有案例均有對應
3. 實體關係圖轉關聯表 當一個E-R Model建立後，可根據7個轉換規則，將ERD轉成關聯表 (Relation;Table) ERD範例 部門編號 部門名稱 部門地點 員工姓名 1 員工薪水 工作 部門 1 員工編號 員工地址 N 1 1 控制 管理 員工 M N 開始日期 1 N 專案 親屬 負責 眷屬姓名 N 專案編號 專案名稱 工作時數 眷屬 眷屬關係 註︰關係連到實體若為雙線，表示完全參與關係，即實體之所有案例均有對應

3 3.1 步驟一 對每個一般性實體建立一個關聯表 屬性︰該實體所有屬性 主鍵︰根據主鍵選取原則， 從準鍵中選擇一個主鍵 以「員工」 實體為例
主鍵︰根據主鍵選取原則， 從準鍵中選擇一個主鍵 以「員工」 實體為例 該實體可被轉成一關聯表 原來實體上之屬性為該關聯表之屬性 可選擇員工編號屬性做為主鍵 員工關聯表： 員工姓名 員工薪水 員工編號 員工地址 員工 員工編號 員工姓名 員工地址 員工薪水

4 3.2 步驟二 對每個弱實體類型建立一個關聯表 屬性︰該實體所有屬性+擁有者實體主鍵 主鍵︰擁有者實體主鍵+弱實體不完全鍵
以「眷屬」 實體為例 眷屬關聯表： 員工姓名 員工薪水 眷屬姓名 眷屬關係 員工編號 員工地址 1 N 員工 親屬 眷屬 員工編號 眷屬姓名 眷屬關係

5 3.3 步驟三 對每個多值屬性建立一個關聯表 屬性︰該多值屬性+擁有者實體主鍵 主鍵︰該關聯表所有屬性 以「部門地點」之多值屬性為例
部門地點關聯表： 部門編號 部門名稱 部門地點 部門 部門編號 部門地點

6 3.4 步驟四 對每個M:N(多對多關係)建立一關聯表 屬性︰該關係所有屬性+兩實體主鍵 主鍵︰兩外鍵(foreign key)之集合
以「員工」及「專案」兩 實體之「負責」關係為例 負責關聯表： 員工姓名 員工薪水 工作時數 專案編號 專案名稱 員工編號 員工地址 M N 員工 負責 專案 員工編號 專案編號 工作時數

7 3.5 步驟五 對每個1:1(一對一關係)之處理 具完全關係的實體為S端，另一端為R端，對S端之關聯表進行擴充(不產生新關聯表)
以「員工」及「部門」的「管理」關係為例 部門關聯表： 員工 員工編號 員工姓名 員工地址 員工薪水 管理 1 部門 部門編號 部門名稱 部門地點 開始日期 註︰每個部門都有管理者； 並不是每個員工都擔任主管 部門編號 部門名稱 開始日期 員工編號

8 3.6 步驟六 對每個1:N(一對多關係)之處理 對N端之關聯表進行擴充(不產生新關聯表) 以「員工」及「部門」的「工作」關係為例
員工關聯表： 員工 員工編號 員工姓名 員工地址 員工薪水 工作 N 1 部門 部門編號 部門名稱 部門地點 員工編號 員工姓名 員工地址 員工薪水 部門編號

9 3.7 步驟七 對每個N元關係建立一個關聯表 每個N元(N3)關係都轉換為一個關聯表 以下圖之「供應」三元關係為例
屬性︰該關係所有屬性+所有參與實體的主鍵 主鍵︰所有外鍵 以下圖之「供應」三元關係為例 供應關聯表： 供應商 供應商編號 供應 專案 專案編號 零件 零件編號 供應數量 供應商編號 零件編號 專案編號 供應數量

10 4. 正規化 4.1 正規化的涵義與資料異常 Normalization：由E. F. Codd所提出
將資料屬性組合成一個具有良好結構的關聯表之過程 關聯表中含有最少的重複狀況 不會發生資料異常(Anomalies)，當； 插入關聯表中的資料 刪除關聯表中的資料 修改關聯表中的資料

11 未正規化之問題 例︰課程收費關聯表(未正規化) (1) 資料重複 (2) 插入異常
Ex: 課程代號MIS200之學費3000元在修課學員之資料中重複出現 (2) 插入異常 Ex: 若要增加一門新課程(例如MIS600)，除非至少有一個學員登記，否則此資料將無法加入

12 未正規化之問題 (Cont.) 例︰課程收費關聯表(未正規化) (3) 刪除重複 (4) 更改異常
Ex: 學員99425不再選擇MIS400課程，由於該課程只有此學員登記，刪除後，便失去該課程收費為6000元的資訊 (4) 更改異常 假設MIS200課程的學費由3000元漲為5000元，那麼在每一個修習MIS200課程的學員資料都必需修改，否則資料便會不一致。

13 正規化後 將課程收費正規化為2個關聯表 學員課程關聯表 課程收費關聯表

14 4.2 相依 (1)功能相依 Functional Dependency定義 實例：如右圖 假設有一關聯表R，而A與B是R的屬性
若B功能相依於A，(寫成R.AR.B) A屬性之值只會對應到一個B屬性值 實例：如右圖 {A,B}C BD BE DE A B C D E 主鍵

15 相依 (Cont.) (2)完全功能相依與部份功能相依 Full Functional Dependency
B完全功能相依於A屬性B功能相依於複合屬性A，但不功能相依於A的部份屬性 Partial Dependency B部份功能相依於A屬性B功能相依於複合屬性A的部份屬性 實例：如右圖 C完全功能相依於 主鍵；D部份功能 相依於主鍵{A,B}， 因去除A之後，仍 存在BD A B C D E 主鍵

16 相依 (Cont.) (3)遞移相依 Transitive Dependency 實例：如下圖
關聯表中，存在非鍵屬性功能相依於一個或多個非鍵屬性 實例：如下圖 非鍵屬性D非鍵屬性E 此因BE可以由BD及DE推得 A B C D E 主鍵

17 4.3 正規化實務 未經正規化的關聯表 除去重複群 第一正規化型式 除去部份相依 第二正規化型式 除去遞移相依 第三正規化型式

18 4.3.1 第一正規化型式 First Normal Form (1NF)
未正規化關聯表轉1NF：除去關聯表中任何的重複群，使關聯表中任一行與任一列的交叉格(Cell)上均只有一個值 實例：未正規化的關聯表(成績單)

19 第一正規化型式 (Cont.) 實例：第一正規化型式的關聯表(成績單) 將學生選修的每個課程均自成一列記錄
資料重複：例如學生學號、姓名、通訊處等 插入異常：若新開一課程，除非有人選修，否則無法將資料加入關聯表 刪除異常：例如盧正映不再選行銷學 更改異常：例如盧正映主修由企管轉資管，需同時更改多筆記錄

20 4.3.2 第二正規化型式 Second Normal Form (2NF) 1NF關聯表轉2NF：除去部分功能相依
必需先分析其資料之功能相依性，並選出該關聯表之主鍵(以底線表示) 實例之分析結果如下： 學生姓名 學生學號 通訊處 主修 成績 課程名稱 課程代號 授課老師 老師研究室 主鍵

21 第二正規化型式 (Cont.) 部份功能相依 因此需拆成三個關聯表： 學生學號學生姓名、通訊處、主修
課程代號課程名稱、授課老師、老師研究室 因此需拆成三個關聯表： (1)學生關聯表 (3NF)

22 第二正規化型式 (Cont.) (2)課程-老師關聯表(2NF) (3)選課關聯表(3NF)

23 4.3.3 第三正規化型式 Third Normal Form (3NF) 1NF關聯表轉2NF：除去資料的遞移相依 實例：課程-老師關聯表
授課老師(非主鍵)老師研究室(非主鍵) 因此必需再拆成兩個關聯表 課程代號 課程名稱 授課老師 老師 研究室

24 第三正規化型式 (Cont.) (1)課程關聯表(3NF) (2)老師關聯表(3NF)

25 5. 關聯表資料字典 完成關聯表正規化後，需記載成關聯表資料字典以便建立資料庫，內容包括： 實體中之資料元素名稱 資料欄位型態
長度格式/限制 範例 資料產生方式 例如由使用者(U)輸入，或系統(S)自動產生 是否為主鍵或外鍵等

26 關聯表資料字典範例

27 資料塑模總結 以ERD進行企業資料塑模，是關聯式資料庫設計之基礎 遵循科學化方法論以進行資料塑模之優點
可降低資料之重複性，並避免資料插入、刪除與更改之異常，對資料庫之維護有很大的幫助。

28 參考資料 吳仁和、林信惠， 「系統分析與設計：理論與實務應用」三版，智勝文化事業 有限公司，台北市，2004年1月。
第六章：結構化分析與設計： 資料塑模