1、數據庫設計實例數據庫設計是數據庫應用系統設計的一個組成部分，其核心是針對于特定的應用環境，設計合理的數 據模型，創建數據庫及其應用系統，使之能夠有效地存儲和處理數據，以滿足用戶的應用需求。從實用角 度出發，數據庫設計可分為如下幾個步驟：第一步：創建概念數據模型確定實體和關系確定屬性 規化數據第二步：生成物理數據模型第三步：驗證設計為便于學習者理解和掌握，下面結合具體的實例來講解和展示數據庫設計的詳細過程。假定我們要開 發一個小型的ERP系統，以管理公司部資源，其應用業務場景描述如下：V512工作室由IT業界專業人士組成，在提供高端IT培訓業務的同時，還自主制作并免費發布大量公 益性學習資源，工

2、作室以公司形式運營，目前共擁有18名員工，這些員工分屬于4個部門，且員工之間存在上下級管理關系。計劃將來根據業務的發展設立更多的部門，聘用更多的員工。為保證質量，工作室 對其成員的各項專業技能進行了級別評定。確定實體和關系1. 確定咼級別的活動要確定本ERP系統數據庫設計中的實體和實體間關系，首先應明確要基于該數據庫執行的高級別活動，這里所謂的高級別活動是指從用戶的視角出發，確定本數據庫設計中系統所涉及到的業務活動。比如，存 儲和維護員工的個人信息等。在前述的應用業務場景中，v512工作室需要考慮的高級別活動包括：- 聘用新員工- 解雇現有員工- 維護員工的個人信息- 增設新部門- 裁撤現有部

3、門- 維護部門信息- 維護工作室業務相關的技能信息- 維護各員工的業務技能掌握情況2. 確定實體接下來要確定的是，針對上述的高級別活動需要記錄和維護有關哪些事物的信息，這些事物將被轉換 為實體。其中，員工相關信息可抽象為"Employee”實體、部門相關信息可抽象為"Department ”實體、技能相關信息抽象為“ Skill ”實體，為規和方便起見，這些實體均采用英文命名，并盡量在名稱中體現 其含義。3. 確定關系進一步對上述高級活動進行分析，以確定實體間存在何種關系。具體包括：- Employee-Department實體之間存在隸屬關系員工必須且只能隸屬于某一個特定

4、的部門，一個部門可以包含0多名員工，此為一對多關系。這種從兩個方向上對同一個關系的細化描述被稱為關系的角色，每個關系都對應兩種角色。- Employee-Department實體之間存在管理關系每一名員工可以管理01各部門，每個部門必須由一名員工負責管理（其管理者不必須隸屬于本部門），此為一對一關系。-Employee-Skill實體之間存在掌握關系每一名員工均應掌握 1多項業務技能，每項技能可能被0多名員工掌握，此為多對多關系。- Employee-Employee實體之間存在管理關系每位員工由01位上級員工負責管理，有的員工可能沒有上司（比如公司經理），但有的話只 能有一位直接上級。上級員

5、工可以管理0多位為下級員工。經分析而得的上述實體間關系如圖8-14所示。圖8-14數據庫設計實例CDM之 14. 將多對多關系更改為實體前文中已講過，在多對多關系中，可能會出現有屬性與關系相關聯、而不是單純的與實體相關聯的情 況，將這樣的屬性添加到任何一個實體中都是不合理的，此時應將該關系轉換為、或者說定義為實體。我 們這里的Employee與Skill實體之間就存在這種情況一一員工所掌握的技能項目及其評定等級信息就與 兩個實體之間的多對多關系相關聯，因此將此多對多關系定義為“人力資源”（HR實體，轉換后的實體-關系如圖8-15所示。圖8-15數據庫設計實例 CDM之 2在實際的數據庫應用設計

6、中，更簡單而可行的處理原則是，將一切多對多關系均轉換為實體，而不必 逐個對其進行細化分析，這樣可以很好地解決違背數據庫規化第二式的問題。5. 分解活動接下來，需要對前述的高級別業務活動做進一步分析，看是否可以將其中的部分或全部項目分解為相 對低級別、或者說更細致的業務活動，比如，其中“維護工作室業務相關的技能信息”這一高級別的活動 可繼續分解為：-添加新的技能項目- 刪除不再使用的技能項目- 維護/更新現有技能項目的詳細詳細最后一項高級活動“維護各員工的業務技能掌握情況”也可以進行類似的分解。需要說明的是高級業 務活動的確定以及這里的細化分解并沒有一成不變的標準，這就好比實現某一預期功能的編程

7、工作是沒有 標準答案的（有的只是參考實現），其目的都是為后續的確定業務規則和實體屬性等步驟提供便利，如果 業務邏輯不是很復雜的話也可以一步完成，具體由數據庫設計者自行把握即可。6. 確定業務規則接下來要做的是，對前述業務活動進行再次分析，確定其應遵守的具體規則。比如，一名員工必須且 只能隸屬于某一個部門就是一個業務規則。業務規則通常可以表示為一對一、一對多和多對多關系，或者 相應的約束條件，這些規則將來會體現到數據庫的結構之中。本實例中相關的業務規則如下：- 員工必須隸屬于某一個部門- 員工編號一經確定不得更改- 員工的、性別、職務、所屬部門等其它個人信息可以更改- 員工所掌握的技能項目及其評

8、定等級可以更改- 每個部門允許設置 0多部和前述業務活動的確定及分解情況類似，業務規則的確定以滿足實際業務需要為準，注意不要搞得過 于繁瑣而失去其實用價值，具體詳略程度需設計者根據經驗自行把握，也可以待后續環節暴露出問題后再 追溯回來，進行迭代處理。確定屬性首先，根據前述分析的業務活動的需要確定所有要記錄和維護的數據條目，然后再將這些數據條目做 為屬性關聯到相應的實體或關系中，比如：- 員工實體員工編號、員工、性別、職務、上司工號、工資、出生日期、所屬部門- 部門實體部門編號、部門名稱、部門經理、部門地址、- 技能實體技能編號、技能名稱- 人力資源實體員工編號、技能編號、掌握程度 設置屬性后的

9、實體-關系如圖8-16所示。圖8-16數據庫設計實例 CDM之 3其中，帶有下劃線的屬性為所在實體的標識符。為實體或關系設置屬性時，應盡量采用規的命名規則一一屬性的名稱應體現其含義、且應遵循統一的 命名格式，以便于將來的使用和維護，比如，假定部門編號使用縮略名稱dept_name,那么部門名稱等相應的其它屬性就不應該使用完整名稱，如department_name，而應是其名稱保持一致，如dept_name。在本階段，設計者只需根據自己的經驗和判斷進行設計即可，而不必強求數據（屬性）與實體間的關 聯關系一定是正確或合理的，后續的環節還將對現有的數據模型進行規化處理，以發現可能存在的問題并進行修正

10、。規化數據前文中已經講過，規化數據的目的在于避免出現數據冗余和操作異常，進而節省存儲空間并更好地確 保數據的一致性，實際上就是對所設計的數據模型進行一系列的規化測試（判斷其是否滿足指定的式要 求）、發現問題并進行整改的過程。在進行數據的規化測試之前，應先簡單地羅列出各實體中的數據，并為每個實體確定一個唯一的標識 符、以唯一地標識實體集中的每一個實體，標識符可以是一個屬性、也可以由多個屬性組合而成。實際上 這一工作我們已經完成了，參見圖 8-16，其中，使用下劃線標記的即為標識符屬性，HR實體（多對多關系轉換而成的實體）的標識符由其所依賴的兩個實體的標識符（emp_id和skill_id ）組合

11、而成。1. 第一式測試根據第一式的要求，檢查各實體的屬性是否存在多值的情況，這里的“多值”指的是同一個屬性在同 一個實體上可以有幾個不同的取值，如果有則應將這樣的屬性從其所屬的實體中刪除掉，并用這些刪除掉 的屬性創建新的實體和關系。在我們的設計實例中，假定一個部門可以有 0多個，則Department實體中的phone屬性就存在多值 的情況，解決辦法是，將 phone屬性從Department實體中刪除，并創建一個單獨的 Phone實體（嚴格來 說是實體集）。此時， Department實體和Phone實體間存在一對多的關聯關系 每個部門可以有0多個，每個必須也只能隸屬于某一個特定的部門。修正

12、后的實體-關系如圖8-17所示。圖8-17數據庫設計實例 CDM之 42. 第二式測試第二式要求各實體中所有非標識符屬性都必須完全依賴于實體的標識符，如果存在非標識符屬性部分 依賴（而不是完全依賴）于該實體的標識符的情況，則應從當前實體中刪除這些屬性、并將之加入到適合 的實體中，其相關原理分析及具體做法前文中已有詳細講解。實際上，由于單個屬性做為標識符的實體中不可能出現非標識符屬性對標識符部分依賴的情況，我們 只需檢查那些由多個屬性聯合組成標識符的實體。本設計實例中，只有HR實體中存在兩個屬性（emp_id和skill_id ）組合構成標識符的情況，而其skill_level屬性的確完全依賴于

13、這二者，故本設計已符合第二式的要求。3. 第三式測試第二式要體中的屬性間不得存在傳遞依賴，即所有的非標識符屬性必須完全依賴于標識符、且不得依 賴于實體中的其它（非標識符）屬性，如果存在傳遞性依賴的情況，則應從當前實體中將相關屬性刪除， 有必要的話，也可以將這些屬性添加到適合的其它實體中。經逐個分析，前述各實體的屬性之間均不存在傳遞性依賴，故本設計已符合第三式的要求。生成物理數據模型在確定概念數據模型并完成了數據庫的規化之后，數據庫設計就基本完成了，接下來要做的是生成與 前述概念數據模型相對應的物理數據模型。這個過程也稱作解析關系，因為其主要工作就是將模型中的實 體轉換為表、并將實體間關系轉換為

14、表間的外鍵關系。具體原則如下：1. 將實體轉換為表實體中的標識符轉換為表的主鍵，實體中的屬性轉換為表中的字段，屬性的數據類型轉換/具體化為特定數據庫管理系統所支持的數據類型。2. 解析關系概念數據模型中實體間關系最終將被轉換為物理數據模型中表的外鍵，具體可分為如下三種情況： 解析一對多關系在解析一對多關系時，“一”方表中的主鍵字段（由其對應實體中的標識符轉換得來）將成為“多” 方表中的外鍵字段。例如，圖8-18所示的CDM中, Department實體和Phone實體間存在一對多關系一個部門可以擁有 0多部，但一部必須也只能隸屬于一個部門。圖8-18 一對多關系CDM在轉換后的PDM中, De

15、partment表中的主鍵字段（dept_id ）成為Phone表中的外鍵字段，具體如圖 8-19所示。Departme ntFK PHONE DEPART* 一 一Pho nedept_idVARCHAR2(20) <pk>dept_ name VARCHAR2(30) addressVARCHAR2(50)MEMdept idVARCHAR2(20) <fk>phone_code VARCHAR2(20) <pk>圖8-19 一對多關系 PDM解析一對一關系在解析一對一關系時，可以在其中任意一方（表中）設置外鍵。如果該一對一關系在一個方向是強制 性的，而

16、在另一方向是可選的，則應在可選的一方設置外鍵。例如，圖8-20所示的CDM中, Department實體和Employee實體間存在一對一關系一一一個部門必須也只能由一名員工（部門經理）負責管理，此 為強制性關系；一名員工至多可以管理一個部門，此為非強制性關系。圖8-20 一對一關系 CDM在轉換后的PDM中, Employee表中的主鍵字段（emp_id）成為Department表中的外鍵字段，具體如 圖8-21所示。圖8-21 一對多關系PDM解析多對多關系對于多對多關系，應將之轉換為實體，以便于在數據庫中實現，否則很容易出現違背數據庫規化第二 式和第三式的情況。例如，圖 8-22所示的C

17、DM中，Employee實體和Skill實體間存在多對多關系一一每 位員工均掌握一門以上的業務技能項目，此為強制性關系；每門業務技能項目可能被0多名員工掌握（有的技能項目被為公司業務所需，但尚未招聘到掌握該技能項目的員工），此為非強制性關系。圖8-22多對多關系CDM上述的多對多關系可轉換為實體，具體如圖8-23所示。圖8-23多對多關系轉換為實體 CDM這實際上是將一個多對多關系轉換成了兩個一對多關系。轉換而得的HR實體集中的每個實體都必須在Employee、Skill實體集中各存在一個對應的實體（兩個強制關系），因為員工對技能項目的掌握關系 只有在與一個特定的員工和特定的技能項目實體相關聯

18、時才有意義。換句話說，新建的HR實體既依賴于Employee實體、同時又依賴于 Skill實體，后兩者中的標識符組合起來則可以唯一地標識HR實體集中的一個實體，HR實體集方框兩側小圓圈和鳥爪狀標記中間的三角形就是用來標明這種依賴性的。將多對多關系轉換為實體后更適合其相關屬性信息在數據庫中的存儲，這樣可以很好地避免數據冗 余。比如，如果需要記錄員工對技能項目的掌握程度，則可以在新實體 HR中添加相應的屬性（skill_level ）,確定屬性后的 CDM如圖8-24所示。圖8-24多對多關系轉換為實體 CDM之二說明，被依賴的Employee實體和Skill實體中的標識符將自動成為HR實體的聯合

19、標識符，以唯一標識每一個HR實體，由于在當前的 CDM中已使用三角形標記標明了這種依賴關系，因此不需要HR實體集方框中再顯式添加 emp_id和skill_id 屬性。基于圖8-24所示的CDh而生成的PDM如圖8-25所示。圖8-25多對多關系轉換為實體 PDM多對多關系轉換為實體后，需要再對所得到的新實體和兩個一對多關系進行規化檢查，最可取的做法 是在創建概念數據模型階段就將多對多關系轉換為實體，以避免到生成物理數據模型階段進行關系解析時 的迭代規化處理。比如，在本設計實例中，由于在前述步驟（創建概念數據模型-> 確定實體和關系）中已經將多對多關系轉換為實體，因此這里已不存在待解析的

20、多對多關系。物理數據模型與特定的數據庫管理系統相關，比如表中字段的數據類型會隨數據庫種類和版本號而存 在差異，基于 Oraclellg數據庫，圖8-17所示的概念數據模型所轉換成的物理數據模型如圖8-26所示,本數據庫設計滿足數據庫規化第三式的要求。FK_EMP_EMPDepartmentFK DEP EMPEmployeedept_idVARCHAR2(20)<pk> Aemp_idVARCHAR2(20) <pk>mgr_idVARCHAR2(20)vfk>emp_name VARCHAR2(20)dept_nameVARCHAR2(30)sexCHAR(4)

21、addressVARCHAR2(50)jobVARCHAR2(20)FK_EMP_DEPmgr_idVARCHAR2(20) <fk2>salaryNUMBER(8,2)birthDATEjLhiredateDATE1dept_idVARCHAR2(20) <fk1>FK_PHONE_DEp1十 FK HR EMPPhoneHRdept_idVARCHAR2(20)<fk>emp_idVARCHAR2(20) <pk,fk1>phone_codeVARCHAR2(20)<pk>skill_idVARCHAR2(20) <pk,f

22、k2>skill_levelNUMBER(2)FK HR SKILLI Skillskill_idVARCHAR2(20) <pk>skill_nameVARCHAR2(30)圖8-26數據庫設計實例PDM8.5.5 驗證設計結果在實施上述設計結果之前，需要對齊進行最后的驗證、以確保該設計能夠滿足應用開發的需要。具體 來說，就是再次檢查在設計過程之初確定的各項業務活動，以確保基于當前的數據庫設計可以訪問和操作 業務活動所需要的全部數據。比如，是否可以滿足應用開發中聘用新員工、解雇現有員工、維護員工的個 人信息等業務活動對數據的訪問和操作要求，在本設計中，答案是肯定的。例如：如

23、果經驗沒有問題，那么就可以實施本設計結果了。這里所謂的“實施”，具體來說就是根據先前的 物理數據模型設計結果，運用目標數據庫管理系統(DBMS所支持的數據語言、客戶端或服務器端工具、以及應用開發宿主語言 (如Java)，在目標數據庫管理系統中創建各種數據庫對象，編制與調試應用程序，組織數據入庫，并進行調試運行。基于Oraclellg數據庫，實施本數據庫設計的最終結果(圖8-26所示的PDM的對應SQL腳本文件如下：-丟棄已創建數據表(供調試之用)DROP TABLE hr;DROP TABLE skill;DROP TABLE pho ne;DROP TABLE departme nt CAS

24、CADE CONSTRAINTS;DROP TABLE employee;-創建部門信息表CREATE TABLE departme nt(dept_id VARCHAR2(20) PRIMARY KEY,mgr_id VARCHAR2(20),dept_ name VARCHAR2(30), address VARCHAR2(50);-創建員工信息表CREATE TABLE employee(emp_id VARCHAR2(20) PRIMARY KEY,emp_ name VARCHAR2(20),sex char(4),job VARCHAR2(20),mgr_id VARCHAR2(20),salary NUMBER(8,2),birth DATE,hiredate DATE,dept_id VARCHAR2(20);-創建部門信息表CREATE TABLE pho ne(pho ne_code VARCHAR2(20) PRIMARY KEY,dept_id VARCHAR2(20);-創建技能項目信息表CREATE TA