第 11 章 建立關聯式資料庫 著作權所有 © 旗標出版股份有限公司

本章提要 如何設計一個完善的資料庫 從客戶分析到建立實體 - 關係圖 整合為全區的概念模型 將實體 - 關係圖轉換為關聯式的資料表

如何設計一個完善的資料庫 資料庫的設計流程 了解客戶需求 概念設計階段 邏輯設計階段 建立資料庫

如何設計一個完善的資料庫 操作介面設計：就 Access 而言, 操作介面設計就是表單的設計, 或是以程式語言 所撰寫的操作介面。讓使用者不必接觸資料庫的結構, 就能操作資料庫, 如新增、刪除資料...等等工作。 結構設計：結構設計是指設計出適當且最佳化的資料表。一個結構良好的資料庫可提升其整體的存取效率及儲存效率。

資料庫的設計流程 資料庫發展初期, 資料庫規劃的完善與否, 通常依設計者的經驗、方法及知識水準不同而有所差別。但最後的成果未必能符合使用者的需求。 資料庫的規劃過程大致可分為 4 個階段：

了解客戶需求 針對客戶需求, 確定設計範圍 在規劃資料庫之前, 當然要先拜訪客戶, 了解他們實際的工作流程、各部門執掌範圍及資料的處理方式。確定資料庫設計的範圍及應具備的功能。

了解客戶需求 收集和分析資料 在調查過程中, 除了要明確而具體地找出客戶的需求外, 還要盡量收集他們平時使用的各類表單、報表、檔案..., 這些都是規劃資料庫的重要依據。

了解客戶需求

概念設計階段 在此階段, 設計者不需考慮資料的儲存及處理等與電腦有關的問題。主要工作是將收集的資料, 經過分析及整理後, 產生一個能符合使用者需求的資料庫模型, 並以簡單的形式表現出來 (例如實體-關係圖)。主要流程如下：

概念設計階段

建立分區概念設計圖 概念設計的第一個步驟要分別針對不同需求的使用者, 確定使用範圍。例如公司的資料庫系統必須面對業務部、財務部、產品部...等不同部門的使用者, 這些使用者牽涉到資料庫中的資料及處理的方式各不相同, 所以應針對不同的需求, 設計不同的概念模型。

整合為全區概念設計圖 解決各分區概念設計之間不一致的情形：由於分區概念設計所面對的使用者不同, 所以對於共同事務的看法及重要性有時會出現差異, 而此步驟最主要的工作就是要消糜各分區模型之間的不一致。 刪除概念設計中重複或多餘的物件, 以免造成後續設計時的困擾。

邏輯設計階段

邏輯設計階段 邏輯設計階段的主要工作, 是將概念設計階段產生的結果, 轉換為實際使用的資料表。 以實體 - 關係圖來說, 此階段的工作可分為轉換為資料表及資料表正規化等兩項。

邏輯設計階段 轉換為資料表 完成概念設計階段後, 我們還必須遵循規則, 將實體 - 關係圖正確無誤地轉換為實際使用的資料表, 才能為資料庫所使用。

邏輯設計階段 資料表正規化 為了達到資料庫最佳化的目的, 在轉換資料表後, 能依照正規化的步驟重新檢驗一次, 最好讓每一個資料表都能符合 BCNF (Boyce-Codd Normal Form) 的規範 (我們將在下一章中為您介紹資料表的正規化步驟)。

建立資料庫 經過邏輯設計階段之後, 紙上的分析工作即已完成。接著要將結果建到資料庫中 (例如 Access)。

從客戶分析到建立實體 – 關係圖 Step 1：收集資料, 確定設計範圍 Step 2：依照不同的使用者訂出分區的設 計範圍 從客戶分析到建立實體 – 關係圖 Step 1：收集資料, 確定設計範圍 Step 2：依照不同的使用者訂出分區的設　　　計範圍 Step 3：列出系統中的實體及其屬性 Step 4：建立實體之間的關係 Step 5：加入屬性

收集資料, 確定設計範圍 假設某圖書公司要開發公司的資料庫系統, 經過評估和詳細的調查後, 決定要建立倉庫管理、書籍銷售和人事管理等系統 (在此我們僅說明書籍銷售系統的建立步驟)。經過了設計者調查整理後, 規劃出該系統的主要工作為：

收集資料, 確定設計範圍 處理客戶訂單, 產生出貨單交倉庫出貨。 將出貨單中詳列的書籍產品包裝後, 運送到客戶手中。 依照出貨單上的書籍產品產生請款單, 送到客戶手中。 客戶依照請款單上的金額繳付, 公司收到客戶的帳款後, 便開立發票寄送到客戶處。

收集資料, 確定設計範圍

依照不同的使用者訂出分區的設計範圍 此工作範圍內牽涉到 3 個不同部門的使用者：分別為業務部門 (負責處理訂單事宜)、發行部門 (負責依照業務人員開立的出貨單, 將書籍送到客戶手上) 及財務部門 (負責處理開立發票及催收帳款事宜), 所以設計時, 必須針對不同需求, 分別設計。以下為各部門的工作描述：

依照不同的使用者訂出分區的設計範圍

列出系統中的實體及其屬性 規劃出設計範圍後, 就要先確定實體。實體通常是整理資料中的名詞, 例如地點、人物、概念、事件及設備等。若從業務部門的描述中, 可得知實體為：

列出系統中的實體及其屬性 訂單：包含 (*訂單編號)、客戶名稱、聯絡人、地址、電話、訂單日期、訂單細目、總金額及備註等屬性。 書籍：包含 (* 書籍編號)、書籍名稱及單價等屬性。 客戶：包含 (* 客戶編號)、客戶名稱、聯絡人、地址及電話等屬性。 出貨單：包含 (*出貨單編號)、書籍名稱、地址、電話、客戶名稱及聯絡人等屬性。

建立實體之間的關係 實體及屬性確定後, 接下來就要討論各實體之間的關係。以業務部門為例, 其實體之間的關係： 訂單和書籍的關係 客戶和訂單的關係 出貨單和訂單的關係

建立實體之間的關係 – 訂單和書籍的關係 訂單是由訂單編號、客戶資料及訂單細目組成。我們必須透過訂單上的訂單細目才能了解客戶訂購了哪些書籍, 所以訂單和書籍的關係必須細分為訂單和訂單細目的關係及訂單細目和書籍 (亦即將訂單細目視為一個實體, 屬性包括細目編號、書籍編號、書籍名稱、單價及數量)。以下就 3 者之間的關係探討：

建立實體之間的關係 – 訂單和書籍的關係 訂單細目與訂單之間的關係：一個訂單可包含多個訂單細目, 一份訂單細目只能屬於一份訂單, 是一對多的組成關係。 訂單細目與書籍之間的關係：一個訂單細目只可訂購一種書籍, 但一種書籍卻能屬於多個訂單細目, 所以是為一對多的包含關係。

建立實體之間的關係 – 客戶和訂單的關係 一個客戶可填寫多份訂單, 但一份訂單只能屬於一個客戶, 所以訂單與客戶之間的關係是一對多的填寫關係。

建立實體之間的關係 – 出貨單和訂單的關係 一份出貨單可由多份訂單組成, 但一份訂單只能屬於一張出貨單, 否則客戶就會收到好幾份相同的書籍。所以訂單與出貨單之間的關係, 也是一對多的產生關係。

建立實體之間的關係 綜合以上說明, 可得到如下的關係：

加入屬性

將其他流程圖轉變為實體 – 關係圖

將其他流程圖轉變為實體 – 關係圖

整合為全區的概念模型 由於各分區實體 - 關係模型所面對的問題不同, 且通常由不同的設計者設計, 如此容易導致各個分區圖存在許多不一致的地方。因此在整合時, 最重要的工作就是要消除各分區圖的不一致, 產生一個能被所有使用者接受的概念模型。

整合實體 - 關係圖時 可能遭遇的問題 – 屬性不一致 有時各分區圖, 對於相同實體的屬性類型、範圍、單位...會有不同。例如不同部門, 對於相同零件的編號方式不同；員工的年齡屬性, 有的部門以出生日期表示, 有的則直接用數字來表示；或產品的重量, 有的部門以公斤為單位, 有的卻以公克為單位。

整合實體 - 關係圖時 可能遭遇的問題 – 命名不一致 同名不同義：不同的物件, 在不同的分區圖中, 具有相同的名稱。 同義不同名：相同的物件, 在不同的分區圖中, 具有不同的名稱。

整合實體 - 關係圖時 可能遭遇的問題 – 結構不一致 同一物件在不同的分區圖中的表現的方式不同。例如員工在某個分區圖中被視為實體, 但在另一個分區圖中卻被視為屬性。 解決之道：必須檢查該物件在實體-關係圖中是否擁有屬性, 或和其他實體產生關係。若是, 則可將之設定為實體。

整合實體 - 關係圖時 可能遭遇的問題 – 結構不一致 同一個實體在不同的分區圖中,所包含的屬性不相同。 這類情形相當常見, 因為每個分區圖所重視的物件屬性並不相同, 解決的方式是將所有的屬性都納入。

整合實體 - 關係圖時 可能遭遇的問題 – 結構不一致 實體間的關係在不同的分區圖中不相同。例如甲實體和乙實體在某一個分區圖中是多對多的關係, 但是在另一個分區圖中卻是一對多的關係；或是甲實體和乙實體在某一分區概念圖中具有關係, 但在其他分區圖中卻沒有關係。

整合實體 - 關係圖時 可能遭遇的問題 – 結構不一致 例如有的系所規定學生只能參加一個社團, 而有的系所可讓學生參加多個社團。則學生和社團的關係上就產生了一對多及多對多的關係。為了滿足不同的需求, 在整合時, 就可使用多對多的關係來表示。

以『書籍銷售系統』為例, 說明整合全區的概念設計 以『書籍銷售系統』為例, 說明整合全區的概念設計 Step 1：整合實體 Step 2：整合關係 Step 3：整合屬性 Step 4：消除不必要的實體、關係及屬性

整合實體 實體的整合主要是檢討各分區圖中, 實體的名稱及其代表的意義是否有衝突。在書籍銷售系統中, 可整合得到以下的實體：

整合關係 關係的整合主要是針對不同分區圖中相同實體之間的關係整合, 看看是否有不一致的地方。

整合屬性 屬性的整合主要是檢討各實體中的屬性名稱及其代表的意義是否有衝突。

消除不必要的實體、關係及 屬性 從初步整合的實體 - 關係圖中, 可發現許多實體、關係及屬性可由其他的實體、關係及屬性推導出來。因為這些實體和關係留在資料庫的結構內, 可能會破壞資料庫的完整性, 增加維護的困難度, 因此必須將其刪除。

消除不必要的實體、關係及 屬性 以下從書籍銷售圖中列舉幾個例子來說明： 出貨單及請款單都是依據訂單所產生, 所以我們可利用訂單來產生出貨單及請款單。因此, 便可將這兩個實體刪除, 當然它的關係也就跟著刪除了。 訂單中的客戶名稱、聯絡人、地址...等屬性亦可由客戶實體中取得, 因此可將它們刪除。

消除不必 要的實體 、關係及 屬性

將實體 - 關係圖轉換為關聯式的資料表 實體及屬性的轉換 弱實體的轉換 多值屬性的轉換 實體間關係的轉換 超類型和子類型的轉換 以『書籍銷售系統』為例, 說明如何轉換為關聯式的資料表

實體及屬性的轉換 實體 - 關係圖中所有的實體都用資料表來表示；而屬性則轉換成為資料表的欄位, 若為鍵屬性, 則會成為該資料表的主鍵。如下圖所示：

複合屬性的轉換 若該屬性為複合屬性時, 必須將複合屬性中的所有屬性, 都轉換為該資料表的欄位。如下圖所示：

複合屬性的轉換 在轉換時, 必須將聯絡人屬性中的姓氏及名字屬性當作是客戶實體的屬性之一。轉換後的資料表如右：

弱實體的轉換 如果是弱實體, 轉換時, 必須將其依賴實體的鍵屬性加入, 做為該弱實體的連外鍵, 並與該弱實體的識別屬性合起來, 成為弱實體的主鍵。如下圖所示：

弱實體的轉換 作者弱實體在轉換過程中, 必須加入書籍實體的鍵屬性作為連外鍵, 並結合作者編號成為作者資料表的主鍵, 如下表所示：

多值屬性的轉換 若實體中的屬性為多值屬性, 則在轉換時, 必須為該屬性另外建立資料表, 假設書籍實體中的單價屬性為多值屬性。如下圖所示：

多值屬性的轉換 我們必須為單價屬性另外建立一個單價資料表, 然後將書籍實體的鍵屬性 (書籍編號) 加入單價資料表中, 成為該實體的連外鍵, 並且與單價編號 (識別屬性) 合起來成為單價資料表的主鍵。

實體間關係的轉換 『一對一』關係的轉換 將部份參與實體的主鍵放入全部參與的實體中, 做為連外鍵。例如公司員工 (全部參與, 假設所有的員工都會分配到汽車) 和汽車 (部分參與, 並非所有的汽車都分配給員工) 之間為一對一的關係：

實體間關係的轉換 首先, 將員工及汽車實體分別轉換成資料表, 然後再將汽車資料表的主鍵放到員工資料表中, 做為連外鍵), 如下所示：

實體間關係的轉換 『一對多』關係的轉換 在實體-關係圖中, 一對多的關係是將一個父資料表 ("一" 的這一方) 中的主鍵放入子資料表 ("多" 的這一方) 中, 做為子資料表的連外鍵。 我們將客戶及訂單的一對多關係轉換後, 可產生下列的結果：

實體間關係的轉換

實體間關係的轉換 『多對多』關係的轉換 當實體間是多對多的關係時, 就必須為關係另外建立一個資料表, 且該資料表要包含它所關聯的實體的主鍵。例如員工和專案的關係, 可得到如下的結果：

實體間關係的轉換

超類型和子類型的轉換 假設公司的員工依照特殊的屬性, 再細分為業務部員工及生產部員工兩個子類型：

超類型和子類型的轉換 則在轉換時, 必須將員工實體轉換為員工資料表, 所有員工實體的屬性轉換為員工資料表的欄位；再將生產部員工及業務部員工實體轉換為生產部員工及業務部員工兩個資料表, 最後將員工的主鍵分別加入生產部員工及業務部員工資料表做為該資料表的主鍵。如下圖所示：

超類型和子類型的轉換

超類型和子類型的另一種轉換模式 若該公司員工只有生產部員工及業務部員工, 則可將員工資料表中的所有屬性全部移至生產部員工及業務部員工兩個資料表中, 做為這兩個資料表的欄位：

Disjoint 子類型的轉換 如果子類型為兩個不相交的情況, 如下圖所示：

Disjoint 子類型的轉換 則我們會建立一個員工資料表, 其中包含員工、生產部員工及業務部員工所有屬性的資料表, 不 過, 在員工資料 表中還需要建立 一個特殊屬性, 來說明員工隸屬 的部門：

Overlap 子類型的轉換 如果子類型是屬於 Overlap (重疊) 的關係, 如右圖所示：

Overlap 子類型的轉換 則我們會建立一個客戶資料表, 其中包含客戶、租屋者及買屋者所有屬性的資料表, 不過, 在客戶資料表中還需要建立二個特殊屬性, 來 說明客戶隸屬 的類型：

以『書籍銷售系統』為例, 說明如何轉換為關聯式的資料表 Step 1：將實體轉換為資料表、屬性轉換 　　　為欄位

以『書籍銷售系統』為例, 說明如何轉換為關聯式的資料表 Step 2：建立資料表之間的關聯 客戶與訂單實體 訂單與訂單細目實體 訂單細目與書籍實體 客戶與款項實體

以『書籍銷售系統』為例, 說明如何轉換為關聯式的資料表 客戶與訂單實體：兩者的關係是一對多關係, 所以要將客戶資料表的主鍵加入訂單資料表中, 做為訂單資料表的連外鍵。

以『書籍銷售系統』為例, 說明如何轉換為關聯式的資料表 訂單與訂單細目實體：兩者的關係是一對多關係, 所以要將訂單資料表的主鍵加入訂單細目資料表中, 做為連外鍵。

以『書籍銷售系統』為例, 說明如何轉換為關聯式的資料表 訂單細目與書籍實體：兩者的關係是多對一關係, 所以要將書籍資料表的主鍵加入訂單細目資料表中, 做為連外鍵。

以『書籍銷售系統』為例, 說明如何轉換為關聯式的資料表 客戶與款項實體：兩者的關係是一對多關係, 所以要將客戶資料表的主鍵放入款項資料表中, 做為連外鍵。

以『書籍銷售系統』為例, 說明如何轉換為關聯式的資料表 總合這些關係圖, 我們可得到書籍銷售資料庫的資料表之間的關聯圖：