第九章 : 資料庫設計 資料庫之邏輯設計 : 僅考量檔案間之關連、 正規化及包含之相關屬性、主鍵、外鍵 … 資料庫之實體設計 : 考量採用何種檔案結構 、資料欄位內涵屬性、處理速度考量、何種 廠牌之資料庫系統、 …
邏輯設計 1. 資料庫之 邏輯設計 概述 Step1 : 取得每一位使用者之資料需求
Step1 : 取得每一位使用者之資料需求 ( 續 )
Step 2 : 整合成一個整體企業之資料需求 Step 3 : 將資料實體以 E-R 模型 呈現 ; 並將 其轉換成 關連式表格 Step 4 : 將 關連式表格 予以正規化
==> E-R 模型 ==> 關連式表格
2. 關連式表格相關詞彙定義 員工編號姓名部門薪資 張三 李四 王五 趙龍 吳鳳 行銷部 會計部 資訊部 財務部 行銷部 42,000 39,000 41,500 38,000 38,500
關連式表格 之 特色 1) 每一行及列之相交儲存一個屬性值 2) 每一行 (Column) 代表同一類之資料值 3) 每一列 (Row) 代表一筆獨一無二的資料 4) 任何兩行對調不影響整個表格 5) 任何兩列對調不影響整個表格
優質化之表格 ( Well-Structured Relation ) 避免資料重複儲存 免除資料更新時之後遺症 三階正規化之目的
優質化表格 範例
非優質化表格 範例 重覆資料之儲存 ; 資料更新較困難
經由 正規化 程序 將表格予以優質化 唯一重複 的資料
屬性間之相依性 (Functional Dependency) Emp_ID Name 相依性範例 ( Emp_ID 決定 Name ) AB 非相依性範例 ( A 無法決定 B )
與 正規化 有關之 屬性相依 完全相依 ( Fully Functional Dependency ) ( 每一個非主鍵屬性均和整個主鍵相依 ) 部份相依 ( Partial Functional Dependency ) 遞移相依 ( Transitive Dependency ) ( 非主鍵屬性間具有欄位相依性 )
正規化表格之定義 一階正規化 ( 1st NF ) : 表格中未包含重複 之屬性 二階正規化 ( 2nd NF ) : 表格中任何非主鍵 屬性均與整體主鍵相依 三階正規化 ( 3rd NF ) : 表格中任何非主鍵 屬性間均不具相依性
表格中包含 重複屬性 之範例 員工編號姓名住址 技能
三階正規化過程
未三階正規化之表格 在處理時會遭遇困擾 新增刪除修改 ( 資料之新增 刪除 修改 時之不方便 )
將 二階正規化表格 轉化成 三階正規化 將具相依之屬性區 隔成一表格 利用外鍵建立二個 表格之關聯
主鍵外鍵 表格中之 主鍵 與 外鍵 主鍵 ( Primary Key ) : 識別用屬性 ( 屬性欄位下方畫實線 ) 外鍵 ( Foreign Key ) : 連結用屬性 ( 另一個關連表格之主鍵 ) ( 屬性欄位下方畫虛線 )
關連表格 3. 將 E-R Diagram 轉化成 關連表格 3.1. 將 每一 Entity 轉換成一個 Relation
3.2. 建立關連表格間之關聯 ( 增添外鍵欄位 ) 一對多 一對多 模型 之轉換 另一個實 體的主鍵
一對一 一對一 模型 之轉換 一對多 可視為 一對多 模型之特例 三種建立外鍵的方式
多對多 多對多 模型 之轉換 外鍵兼主鍵
一對多 ) 單一實體 ( 一對多 ) 模型 之轉換 EmployeeManager Manages Mgr_IDNameBirthdateNameBirthdate Emp_ID MANAGER (Mgr_ID, Name, Birthdate ) EMPLOYEE (Emp_ID, Name, Birthdate, Mgr_ID) EMPLOYEE (Emp_ID, Name, Birthdate, Mgr_ID)
對多 ) 單一實體 ( 多對多 ) 模型 之轉換 ComponentItem contains Quantity ITEM (Item-Number, Name, Cost ) ITEM-BILL (Item-Number, Component_Number,Quantity ) ITEM (Item-Number, … ) ITEM-BILL (Item-Number, Component_Number,Quantity ) COMPONENT ( Component_Number, … )
3.3. 將所有關連表格予以整合合併 二個 關連表格 代表相同之實體 EMPLOYEE1 (EMP_ID, Name, Address, Phone) EMPLOYEE2 (EMP_ID, Name, Address, Jobcode, Number-of-Year) 合併成一個 關連表格 EMPLOYEE (EMP_ID, Name, Address, Phone, Jobcode, Number-of-Year)
合併時問題 表格合併時可能產生之問題 1) 異名同義之屬性 ( Synonyms) 2) 一詞多義之屬性 ( Homonyms) 範例 : 銀行之帳戶 ; 客戶之 住址 3) 非鍵值屬性因整合而產生相依 ( 未正規化 ) 3.4. 將所有表格三階正規化
4. 個案探討 : 漢堡速食店 原 E-R Diagram 此一關聯和其 他二組關聯是 否相同 ? 原料購 入發票 產品 產品銷 售資料 原料
使用者新需求的報表 ( 產生 一個新屬性 : Vendor Name )
新 E-R Diagram ( 增加一個 Vendor 實體 )
邏輯設計步驟 資料庫 邏輯設計 之處理 步驟 1) 取得擬儲存之資料或檔案 ( Chap. 4 & 5 ) ( 經由 DFD 、現行作業單據、報表、手冊、檔案 …) 2) 繪製 資料庫 E-R Diagram 圖形 ( Chap. 6 ) 3) 將圖形轉換成表格檔並建立關聯 ( p ) 4) 整合表格檔並予以三階正規化 ( p )
實體設計 5. 檔案或資料庫之 實體設計 5.1. 欄位設計 欄位 ( Field ) : 應用系統資料之最小單位 ( 一個屬性可對應一至多個欄位 ) 各種欄位特質 ( Data Type ) : 文字、數值、 日期、時間、邏輯 … ( 參考 Table 9-2 )
選擇 欄位特質 應注意之事項 1) 儘量節省儲存空間 2) 不允許欄位值出現溢位 ( 2000 年軟體危機 ) ( 考量系統使用年限、業務成長空間 ) 3) 確保資料之完整性 ( 不准出現負值 ) 4) 各種資料運算或處理後不致產生困擾
控制 資料內涵 正確性 之方法 1) 使用預設值 ( Default Value ) ( 使用者未輸入客戶城市名稱時以交易商店所在 地城市名稱儲存在檔案內 ) 2) 使用特定的格式 ( Picture / Template ) ( 產品名稱之 第一位為文字 後三位為數值 ) 3) 範圍控制 ( Range Control ) ( 月份欄位僅能儲存 12 種月份縮寫 )
4) 欄位間之相關聯 ( Referential Integrity ) 5) 允許應輸入而未輸入值 ( Null Value )
6. 實體表格 ( 檔案 ) 之設計 6.1. 反正規化 ( Denormalization ) : 將相似 性高的欄位或資料予以分解或整合表格 Note : 1) 此一做法可能破壞原來之正規化表格 目的 2) 目的 : 減少磁碟 I/O 之次數 ( 加快處理時間 )
依 產品使用部門 切割成 多個表格
依 客戶住宅區域 重新分割表格
三個反正規化實體表格 ( 檔案 ) 設計 個案 1 : 1-1 表格關聯 二個實體 均建立外 鍵
個案 2 : 多對多 表格關聯 此表格是否為 2 NF ?
個案 3 : 常須參考之資料重複地儲存 以儲存空間 取代處理速 度 保留一對多之多的表格
6.2. 檔案內記錄 ( record ; row ) 之結構 原則 選擇 檔案結構 ( file organization ) 之七個原則 1) 能提供快速的資料索取服務 2) 增強單位時間內之交易處理筆數 3) 有效地使用儲存體空間 4) 應避免資料之流失或儲存失敗 5) 資料重新整理之需求愈少愈好 6) 能配合業務之成長 7) 避免資料被非法使用
三種常見之檔案結構 1) 順序檔 ( Sequential File ) 必須從第一筆 資料讀或寫 欲處理 Hoosiers 這筆資料
2) 索引檔 ( Index File ) ( 查國語字典時可由部首、筆劃、注音 … 找到欲查 的字 ) 欲尋找 之資料 鍵值 資料搜尋 之路徑
3) 隨機檔 ( Hash File ) ( 經由一個隨機函數計算出資料儲存的位置 ) 欲尋找 之資料 鍵值
三種常見檔案結構之優缺點比較 資料儲存空間逐筆順序 索取資料 使用主鍵值單筆 隨機尋找資料 使用多種鍵值單 筆隨機尋找資料
檔案設計時之控制機制 1) 定期備份檔案 2) 將交易輸入資料儲存於流水檔中 ( 凡改變必留下痕跡 ) 3) 將修改前後之檔案記錄保留於系統中 ( 可供系統復原使用 )
4) 將檔案內之資料予以亂碼化 ( Encrypting ) 5) 確認使用者身份及密碼後才准予索取資料 6) 每一位使用者索取資料權限之控管
7. 個案探討 7.1. 漢堡速食店 收據檔 之 欄位設計 欄位名稱欄位長度 與特質 欄位格式預設值 檢測正 確性 是否必 須輸入
7.2. PVF 公司之設計 客戶資料檔 之主要欄位 三類 客戶 之共 通欄 位 三類 客戶 之特 殊欄 位
三個重要實體檔案之 屬性、主鍵與外鍵 購物車訂單庫存商品