5 資料庫設計工具的使用 5-1 資料庫設計的基礎 5-2 啟動塑模工具與新增專案 5-3 新增實體 5-4 建立關聯性 5-5 產生資料庫設計報告和建立資料庫
5-1 資料庫設計的基礎 5-1-1 資料庫系統開發的生命周期 5-1-2 資料庫設計方法論 5-1-3 安裝資料庫設計工具
5-1 資料庫設計的基礎 「資料庫設計」(Database Design)是一項大工程,因為資料庫儲存的資料牽涉到公司或組織的標準化資訊、資料處理和儲存方式,資料庫應用程式開發不能只會寫程式,還需要擁有資料庫相關的技術背景。 關聯式資料庫設計(Relational Database Design)的目的就是在建立關聯式資料庫,更正確的說,這是建立關聯式資料庫綱要,也就是定義資料表、欄位和主索引等定義資料。
5-1-1 資料庫系統開發的生命周期-圖例 資料庫系統開發的生命周期就是資料庫系統的開發流程,它和其他應用程式的開發過程並沒有什麼不同。資料庫系統開發的生命周期可以分成五個階段,其流程圖如右圖所示:
5-1-1 資料庫系統開發的生命周期-說明 在資料庫系統的開發流程中,第一階段的初步計劃是描述資料庫系統的目的、功能和預期目標等資訊。第二階段依照初步計劃進行資料收集、訪查來確定資料庫系統的需求,在此階段注重的是問題,而不是系統本身,在完成需求的收集後,就可以開始進行分析。 在之後的三個階段是資料庫設計與實作部分,當分析完資料庫的需求後,就可以在第三階段進行資料庫設計,第四階段是在選擇的資料庫管理系統來實作資料庫,例如:SQL Server。最後第五階段,雖然資料庫系統已經設計完成,但是,還是需要定時維護資料庫系統,以維持資料庫系統的正常運作。
5-1-1 資料庫系統開發的生命周期- 完整資料庫設計 在本節主要是說明第三階段的資料庫設計,對比軟體系統開發,就是系統分析。事實上,完整資料庫設計分成兩個部分,如下所示: 資料庫設計(Database Design):依照一定程序、方法和技術,使用結構化方式將概念資料模型(詳見下一節的說明)轉換成資料庫的過程。 應用程式設計(Application Design):設計程式來建立使用者介面,並且將商業處理流程轉換成應用程式的執行流程,以便使用者能夠輕易存取所需的資訊,即所謂資料庫程式設計(Database Programming。
5-1-2 資料庫設計方法論-說明 「資料庫設計方法論」(Database Design Methodology)是使用特定程序、技術和工具的結構化設計方法,一種結構化的資料庫設計方法。簡單的說,這是一種計劃性、按部就班來進行資料庫設計。 對於小型資料庫系統來說,事實上,就算沒有使用任何資料庫設計方法論,資料庫設計者一樣可以依據經驗來建立所需的資料庫。但是,對於大型資料庫設計的專案計劃來說,資料庫設計方法論就十分重要。
5-1-2 資料庫設計方法論-圖例
5-1-2 資料庫設計方法論- 概念資料庫設計 概念資料庫設計(Conceptual Database Design) 概念資料庫設計是將資料庫需求轉換成概念資料模型的過程,並沒有針對特定資料庫管理系統或資料庫模型。簡單的說,概念資料模型是一種使用者了解的模型,用來描述真實世界的資料如何在資料庫中呈現。實體關聯圖是目前最廣泛使用的概念資料模型。
5-1-2 資料庫設計方法論- 邏輯資料庫設計 邏輯資料庫設計(Logical Database Design) 邏輯資料庫設計是將概念資料模型轉換成邏輯資料模型的過程,邏輯資料庫設計是針對特定的資料庫模型來建立邏輯資料模型,例如:關聯式資料庫模型。 邏輯資料模型是一種資料庫管理系統了解的資料模型,擁有完整資料庫綱要,我們可以使用第2章的外來鍵參考圖建立邏輯資料模型。事實上,實體關聯圖不只可以建立概念資料模型,它也可以用來建立邏輯資料模型,其最大差異在於邏輯資料模型是一個已經正規化的實體關聯圖。
5-1-2 資料庫設計方法論- 實體資料庫設計 實體資料庫設計(Physical Database Design) 實體資料庫設計是將邏輯資料模型轉換成關聯式資料庫管理系統的SQL指令敘述,以便建立資料庫。簡單的說,實體資料模型可以描述資料庫的關聯表、檔案組織、索引設計和額外的完整性限制條件。
5-1-3 安裝資料庫設計工具-說明 「資料庫設計工具」(Database Design Tools)也稱為資料庫塑模工具(Database Modeling Tools)或資料塑模工具(Data Modeling Tools),這是一套提供完整資料庫設計環境的應用程式,可以幫助我們執行資料庫設計、建立與維護資料庫。以關聯式資料庫來說,資料庫設計工具的最重要功能就是繪製實體關聯圖。 SQL Power Architect是SQL Power Group公司開發的一套專業資料庫塑模工具,可以幫助開發者、資料庫管理師或設計者有效的執行資料庫設計與開發過程,和建立和維護資料庫設計成果。
5-1-3 安裝資料庫設計工具- 安裝Java執行環境 SQL Power Architect是Java技術開發的資料庫塑模工具,需要JRE 7(Java Runtime Environment 7)或以上版本來執行,在執行前請在Windows作業系統安裝JRE,其下載網址為:http://www.oracle.com/technetwork/java/javase/downloads/index.html。
5-1-3 安裝資料庫設計工具- 安裝SQL Power Architect SQL Power Architect分為:企業(Enterprise)和社群(Community)兩種版本,社群版是Open Source完全免費的版本,我們可以在SQL Power Group公司網站免費下載最新版本,其下載網址是:http://www.sqlpower.ca/page/architect_download_os。
5-1-4 設定SQL Server伺服器和新增使用者-說明 在完成SQL Power Architect安裝後,啟動SQL Power Architect之前,我們需要針對SQL Server進行相關設定,因為SQL Power Architect可以連接SQL Server資料庫來產生模型、資料庫綱要的SQL指令,或比對存在資料庫來產生更新綱要的SQL指令。 請注意!SQL Power Architect需要連接已經存在的SQL Server資料庫,所以,我們需要自行使用SQL Server Management Studio建立目標資料庫,和新增登入與資料庫使用者,使用的是SQL Server驗證。
5-1-4 設定SQL Server伺服器和新增使用者-步驟 步驟二:啟用SQL Server的TCP/IP通訊協定 步驟三:建立連接的目標資料庫 步驟四:新增SQL Server驗證的使用者
5-2 啟動塑模工具與新增專案 5-2-1 啟動與結束SQL Power Architect 5-2-2 新增資料庫連接 5-2-3 新增與開啟專案
5-2-1 啟動與結束SQL Power Architect- 啟動 SQL Power Architect是一套視覺化圖形使用介面的資料庫塑模工具,在成功安裝SQL Power Architect後,即可啟動SQL Power Architect。 啟動SQL Power Architect 在Windows作業系統啟動SQL Power Architect的步驟,如下所示: 請執行「開始/所有程式/SQL Power Architect」指令,第一次啟動會看到「Missing PL.INI」訊息視窗。
5-2-1 啟動與結束SQL Power Architect- 結束 在完成資料庫設計後,請執行「File/Exit」指令結束SQL Power Architect資料庫塑模工具。
5-2-2 新增資料庫連接 SQL Power Architect第一次啟動SQL Power Architect後,預設建立新專案,我們可以新增SQL Server連接來連接指定資料庫。
5-2-3 新增與開啟專案-說明 在SQL Power Architect建立每一個實體關聯圖的模型是一個專案,SQL Power Architect支援建立資料庫設計的兩種資料模型,如下所示: 邏輯資料模型(Logical Data Model):沒有針對特定資料庫系統建立的實體關聯圖,也就是在專案右邊編輯視窗建立的資料庫模型,例如:SQL-92。 實體資料模型(Physical Data Model):將建立的資料庫模型輸出成SQL指令碼,可以連接目標資料庫來建立模型設計的資料表。
5-2-3 新增與開啟專案-新增專案 SQL Power Architect在啟動後預設建立新專案,如果已經開啟專案,我們也可以執行指令來建立專案,例如:建立名為Ch5_2_3的專案,請執行「File/New Project」指令,或按工具列第1個【New】鈕,可以在左邊資料庫樹看到新增的專案,名稱同樣是New Project,如下圖所示:
5-2-3 新增與開啟專案-刪除專案 現在有2個同名專案(目前的專案是橘紅色),我們準備刪除第1個專案,請移至項目最後,可以看到紅色圓形X圖示,點選圖示刪除專案(或選取專案,執行「File/Close Project」指令),可以看到一個警告訊息。
5-2-3 新增與開啟專案-開啟專案 對於已經存在的專案,或尚未完成需要修改的專案,我們可以啟動SQL Power Architect開啟存在的專案檔案,例如:之前建立的Ch5_2_3.architect,請重新啟動SQL Power Architect,執行「File/Open Project」指令或按工具列第2個【Open】鈕,可以看到「開啟」對話方塊。
5-3 新增實體 5-3-1 實體的圖形符號 5-3-2 新增與刪除實體 5-3-3 新增屬性清單和指定主鍵 5-3-4 編輯屬性 5-3-5 建立與編輯索引
5-3 新增實體 實體(Entities)是從真實世界的資料識別出的東西。例如:人、客戶、產品或觀念等。屬性(Attributes)是實體擁有的特性,例如:學生實體擁有學號、姓名、地址和電話等屬性。 在SQL Power Architect建立專案後,可以開始新增實體關聯圖的實體和屬性,以實體資料模型來說,就是建立SQL Server資料庫的資料表定義資料。
5-3-1 實體的圖形符號 SQL Power Architect的實體是使用長方形圖形符號表示,主鍵和屬性清單是直接置於長方形的方框中,如下圖所示:
5-3-2 新增與刪除實體-新增實體(步驟) 請按右邊垂直工具列第6個【New Table】鈕後,移動游標至右邊編輯區域的插入位置按一下,可以看到「Table Properties」對話方塊。 在【Logical Table Name】欄輸入實體名稱【學生】,【Physical Table Name】欄輸入資料表名稱【Students】(名稱只能是英文名稱),【Primary Key Name】欄位輸入【Students_pk】(名稱只能是英文名稱,此欄位是產生SQL指令時指定的主鍵名稱)。
「Table Properties」對話方塊 5-3-2 新增與刪除實體-新增實體(圖例) 按此按鈕 插入位置按一下 「Table Properties」對話方塊
5-3-2 新增與刪除實體-編輯與刪除實體 刪除實體 在編輯區域選取欲刪除的實體後,請執行【右】鍵快顯功能表的【Delete Selected】指令刪除實體。 編輯實體 在編輯區域選取實體後,按二下或執行【右】鍵快顯功能表的【Table Properties】指令,都可以開啟「Table Properties」對話方塊來重新編輯實體。
5-3-3 新增屬性清單和指定主鍵-說明 在SQL Power Architect新增Students實體後,可以替實體建立屬性清單(即欄位)和指定主鍵,即建立學生資料表的欄位定義資料。
5-3-3 新增屬性清單和指定主鍵-插入屬性 選【學生】實體,在右邊工具列按【Insert Column】鈕,可以看到「Column Properties of New Column」對話方塊來插入屬性。 按此按鈕
5-3-3 新增屬性清單和指定主鍵-指定主鍵 在【Logical Name】欄輸入【學號】,【Physical Name】欄輸入【sid】,勾選【In Primary Key】表示是主鍵欄位之一,在【Type】欄選【CHAR】,勾選下方【Precision】,輸入長度【4】,下方【Default】欄位可以輸入預設值,按【OK】鈕插入欄位(欄位是位在主鍵區段),如下圖所示:
5-3-4 編輯屬性-1 刪除屬性 對於實體不再需要或輸入錯誤的屬性,可以在實體圖形上,直接選取屬性後,執行【右】鍵快顯功能表的【Delete Selected】指令刪除屬性。 屬性排列順序 屬性清單的順序如果有問題,請直接在實體圖形上拖拉位置,即可調整屬性順序。
5-3-4 編輯屬性-2 重新編輯屬性內容 如果需要重新編輯屬性內容,請在屬性清單直接選取屬性後,按二下或執行【右】鍵快顯功能表的【Column Properties】指令,都可以開啟「Column Properties」對話方塊來編輯屬性內容。
勾選索引欄位姓名,如果不只一個,請重複勾選 5-3-5 建立與編輯索引-建立索引 在選取學生實體後,執行【右】鍵快顯功能表的【New Index】指令(或按右邊工具列第7個【New Index】鈕),可以看到「Index Properties」對話方塊。 勾選索引欄位姓名,如果不只一個,請重複勾選
5-3-5 建立與編輯索引-編輯索引 如果需要,我們可以編輯索引或複合主鍵的欄位,請在實體圖形上,按滑鼠【右】鍵顯示快顯功能表,在【Index Properties】指令的子選單是目前實體建立的索引清單,執行【Students_pk】指令編輯主索引,可以看到「Index Properties」對話方塊來編輯索引。
5-4 建立關聯性 5-4-1 關聯性的圖形符號 5-4-2 建立關聯性 5-4-3 編輯關聯性與參考完整性規則
5-4-1 關聯性的圖形符號-說明 關聯性(Relationships)是指二個或多個實體之間擁有的關係,在SQL Power Architect建立實體和新增屬性清單後,可以建立關聯性來完成實體關聯圖。 在SQL Power Architect關聯性的圖形符號是使用連接線表示實體之間是哪一種關聯性,在連接線端點是使用雞爪符號標示關聯性的限制條件。
5-4-1 關聯性的圖形符號-基數和參與條件1 SQL Power Architect支援關聯性的基數和參與條件,使用雞爪符號直接標示在連接線的兩個端點,其關聯性參與條件使用的術語和第3-1-5節不同。例如:講師實體可以上很多門課程實體,或沒有教任何一門課程的一對多關聯性,如下圖所示:
5-4-1 關聯性的圖形符號-基數和參與條件2 講師實體是強制參與;課程實體是選項參與名為上課的關聯型態,其說明如下所示: 強制參與(Mandatory Participation):即第3-1-5節的全部參與限制條件(Total Participation Constraints)。因為所有講師都需教課,所以講師實體完全參與上課關聯型態的強制參與。 選項參與(Optional Participation):即第3-1-5節的部分參與限制條件(Partial Participation Constraints)。因為不是所有課程都有講師教,所以課程實體只有部分參與上課關聯型態,即選項參與。
5-4-1 關聯性的圖形符號-種類1 可識別關聯性(Identifying Relationship) 外來鍵是實體的主鍵欄位之一。例如:當實體A關聯到實體B,實體A的主鍵k不只是實體B的外來鍵,還是主鍵欄位之一。在SQL Power Architect是使用實線來表示關聯性,例如:訂單實體的主鍵訂單編號不只是訂單明細實體的外來鍵,它還是主鍵欄位之一,如下圖所示:
5-4-1 關聯性的圖形符號-種類2 不可識別關聯性(Non-Identifying Relationship) 指外來鍵不是實體的主鍵欄位之一。例如:當實體A關聯到實體B時,實體A的主鍵k是實體B的外來鍵,但不是主鍵欄位之一。在SQL Power Architect是使用虛線表示不可識別關聯性,例如:訂單實體的外來鍵客戶編號並不是主鍵欄位之一,所以是一種不可識別關聯性,如下圖所示:
5-4-1 關聯性的圖形符號-種類3 多對多關聯性在SQL Power Architect是由兩個一對多關聯性來建立,例如:學生實體和測驗實體的多對多關聯性,是籍由結果關聯實體型態來建立2個一對多關聯性,如下圖所示:
5-4-2 建立關聯性-建立可識別關聯性 在右邊垂直工具列,按倒數第2個【New Identifying Relationship】鈕後,可以先選「一」端的學生實體,然後選「多」端的家長實體,建立預設黑色連接線的關聯性,如下圖所示:
5-4-2 建立關聯性- 建立多對多關聯性(說明) 在SQL Power Architect建立多對多關聯性需要新增一個結合實體,然後建立2個針對結合實體的一對多關聯性,請注意!我們需要自行建立這3個實體,和建立之間的2個一對多關聯性。 例如:結果實體是結合實體(沒有主鍵),我們先建立學生實體和結果實體之間的一對多關聯性,再建立測驗實體和結果實體之間的一對多關聯性,可以完成學生實體和測驗實體之間的多對多關聯性 。
5-4-2 建立關聯性- 建立多對多關聯性(圖例)
5-4-2 建立關聯性-建立自身關聯性 「自身關聯性」(Self Relationship)是指實體的外來鍵是參考同一實體自己的主鍵。例如:在員工實體建立自身關聯性,因為員工的直屬長官也是一位員工, 如下圖所示:
5-4-3 編輯關聯性與參考完整性規則- 說明 在選取關聯性連接線後,執行【右】鍵快顯功能表的【Delete Selected】指令可以刪除關聯性。按二下連接線或執行【右】鍵快顯功能表的【Relationship Properties】指令,都可以開啟「Relationship Properties」對話方塊來重新編輯關聯性,如右圖所示:
5-4-3 編輯關聯性與參考完整性規則- Type與Cardinality 選擇是可識別關聯性(Identifying Relationship)或不可識別關聯性(Non-Identifying Relationship)。 Cardinality 選擇關聯性的基數限制條件(Cardinality Constraints),可以選擇PK和FK實體參數的數量,可以只有1個(Exactly One)、0或1(Zero or One)、1或多(One or More)和0或多(Zero or More)。
5-4-3 編輯關聯性與參考完整性規則- Deferrability 選擇是否可延遲檢查外來鍵的參考完整性(不是每一種資料庫系統都支援),其設定值說明如下所示: Not Deferrable:外來鍵的參考完整性在每一次送出INSERT、UPDATE和DELETE指令都會馬上檢查。 Deferrable, Initially Deferred:如果資料庫交易沒有指明延遲檢查,執行INSERT、UPDATE和DELETE指令直到確認交易後才檢查。 Deferrable, Initially Immediate:如果資料庫交易沒有指明延遲檢查,每一次送出INSERT、UPDATE和DELETE指令都會馬上檢查。
5-4-3 編輯關聯性與參考完整性規則- Update Rule和Delete Rule 選擇2個實體在更新和刪除操作時使用的參考完整性規則(不是每一種資料庫系統都支援),其設定值的說明如下所示: No Action:沒有使用參考完整性規則。 Restrict:拒絕刪除或更新操作。 Cascade:連鎖性處理方式是當更新或刪除時,需要作用在所有影響的外來鍵,否則拒絕此操作。 Set NULL:將所有可能的外來鍵都設為空值,否則拒絕此操作。 Set Default:將所有可能的外來鍵都設為預設值,否則拒絕此操作。
5-5 產生資料庫設計報告和建立資料庫 5-5-1 匯出模型與設計報告 5-5-2 產生SQL指令建立資料庫
5-5 產生資料庫設計報告和建立資料庫 現在,我們可以使用SQL Power Architect重繪第3-1節【教務系統】範例資料庫的實體關聯圖(專案檔案是:SPASchool.architect),如右圖所示:
5-5-1 匯出模型與設計報告- 匯出PDF格式的模型圖 請啟動SQL Power Architect開啟SPASchool.architect專案檔案,執行「File/Export Palypen to PDF」指令,可以看到「儲存」對話方塊。在切換路徑,輸入檔名,按【儲存】鈕匯出成PDF檔,如下圖所示:
5-5-1 匯出模型與設計報告- 產生HTML格式的資料庫設計報告 SQL Power Architect提供功能產生資料庫報告,例如:將SPASchool.architect專案輸出成HTML格式的報告 ,如下圖所示:
5-5-2 產生SQL指令建立資料庫 SQL Power Architect能夠自動產生SQL指令來建立關聯式資料庫綱要。例如:將SPASchool.architect專案檔案輸出成名為SPASchool.sql的SQL指令碼檔案和建立目標資料庫的資料表,如右圖所示: