<< 會計資訊系統課程講義 >> 關聯式資料庫與個體關係圖 (RDB & ERD) 周國華屏東商業技術學院會計系 2007 Autumn
RDBMS RDBMS: 關聯式資料庫管理系統 RDB model 係由 E. F. Codd 在 1970 年建立 資料儲存在二維表格中, 每個表格有一欄為主索引 (pk), 二份表格藉由 foreign key 建立關聯 RDBMS 使用 SQL 語言建立 修改 移除資料庫物件及新增 刪除 修改資料 表格之間的關聯性不由儲存表格的實體位置來代表 Oracle DB 是第一個商用 RDBMS 屏東商業技術學院周國華 2
SQL SQL:Structured Query Language Data Definition Language (DDL): 用來建立及修改資料庫物件, 包含 create, alter, drop, rename, truncate 等敘述 Data Manipulation Language (DML): 用來修改資料庫表格中的資料, 包含 insert, update, delete, commit, rollback, savepoint, select 等敘述 Data Control Language (DCL): 用來配置安全性, 以執行資料庫工作和操作資料庫物件, 包含 commit, rollback, grant, revoke 等敘述 屏東商業技術學院周國華 3
SQL 範例 建立表格 查詢 確認及撤銷 插入資料 屏東商業技術學院周國華 4
MS Access vs. Free DBMS MS Access:MS Office 軟體內的套件之一, 主要提供單機版的關聯式資料庫功能 以下為可免費取得之伺服器版關聯式資料庫軟體 : Oracle DB XE 10g Release 2 (* 收費版已至 11g) IBM DB2 9 Express-C MS SQL Server 2005 Express MySQL Community Server (* MYSQL 是 LAMP 的成員之一 :Linux, Apache, MySQL, PHP/Perl/Python) 屏東商業技術學院周國華 5
ORDBMS ORDBMS: 物件關聯式資料庫管理系統 特性 : 支援使用者定義資料類型 支援多媒體和其他大型物件, 可在單一資料欄中儲存影音 圖片 大量文字 與 RDBMS 概念完全相容 Oracle DB 自 Oracle 8 開始融入 ORDBMS 概念,Oracle 10g 已成為標準 ORDMBS 屏東商業技術學院周國華 6
XML DB 一般而言,RDB 處理 XML 文件可分成三種模式 : Textual fidelity: 將 XML 文件內容以純文字型態儲存在 RDB 的欄位內 Relational fidelity (shredding + XML publishing): 將 XML 文件的內容及格式儲存在 RDB 的欄位內 XML fidelity: 將 XML 文件按照 schema 結構以樹狀方式儲存 此模式稱為 Native XML 目前, 主要的 DBMS 軟體都已在 RDB 中提供處理 XML 文件的機制, 可透過一般 SQL 語法查詢 XML 文 件 ; 某些高階產品 ( 例如 Oracle DB 10g r2) 更進一步提供符合 XQuery 標準的資料庫查詢功能 屏東商業技術學院周國華 7
Data Modeling Data Modeling: 資料塑模, 係指依據任何一種資料模式理論來建立資料模式 (data model) 的過程 Data model: 資料模式, 又稱為綱要 (schema), 可分成三種 概念資料模式 (conceptual data model): 以簡化的模型描述組織的運作內涵, 例如, 以 ERD 的個體 (E) 來代表欲蒐集資料的對象, 並在兩兩個體之間建立關係 (R), 以描述個體之間的互動 邏輯資料模式 (logical l data model): 以詳細的書面表格將概念模式中的個體結構呈現出來, 並藉由表格內的主索引及外來鍵呈現表格之間的關係 實體資料模式 (physical data model): 在特定 RDBMS 軟體中實作出邏輯資料模式之內涵 屏東商業技術學院周國華 8
ERD ERD: 個體關係圖 (entity-relationship diagram) ER model 是由 Peter Chen 在 1976 年發表, 是一種概念資料模式 (conceptual data model) 根據 ER model 所建立的概念圖形稱為 ERD ERD 內通常包含四種元素 : 個體 (entity, 即 E): 代表欲蒐集資料的對象, 以矩形表示 關係 (relationship, 即 R): 在兩兩個體之間建立合乎邏輯的關係, 以菱形表示 在簡化的 ERD 中, 通常會將關係圖形省略 屬性 (attributes): 個體或關係內的資料項目, 以圓矩形表示 當描述稍微複雜的系統時,ERD 內通常會省略此元素 基數性 (cardinality):1 對 1, 1 對 N, M 對 N 屏東商業技術學院周國華 9
ERD 範例 : 銷貨 屏東商業技術學院周國華 10
基數性 :1 對 1 兩個個體之間若具有特定關係, 可用基數性表示其關係之內涵 1 對 1 關係 : 表示兩個個體都僅能以一個實例 (instance) 參與此關係 例如 : 在前述 ERD 範例之 銷貨發票 與 出貨 之間的基數性關係為 1 對 1, 意涵 一張銷貨發票只能包含一次出貨的內容, 一次出貨僅能包含一張銷貨發票的內容 屏東商業技術學院周國華 11
基數性 :1 對多 1 對多關係 (1:N):1 方的一個實例, 可以面對多方的多個實例 ; 多方的一個實例, 只能面對 1 方的一個實例 例如 : 在前述 ERD 範例之 客戶 與 銷貨訂單 之間的基數性關係為 1 對多, 意涵 一位客戶可發出多次 ( 張 ) 銷貨訂單, 一張銷貨訂單只能來自一位客戶 屏東商業技術學院周國華 12
基數性 : 多對多 多對多關係 : 一邊多方的一個實例, 可以面對另邊多方的多個實例 ; 反之亦然 例如 : 在前述 ERD 範例之 銷貨訂單 與 存貨 之間的關係為多對多, 意涵 一張銷貨訂單內可包含多種存貨, 一種存貨可出現在多張銷貨訂單內 屏東商業技術學院周國華 13
基數性的相對性 兩個個體之間的基數性, 並非絕對的 其內涵受到企業政策之左右 例如 : 公務車 與 業務員 之間 1 對 1: 表示 一部公務車只能讓一位業務員使用, 一位業務員只能使用一部公務車 1 對多 : 表示 一部公務車可讓多位業務員使用, 每位業務員只能使用一部公務車 多對 1: 表示 一部公務車只能讓一位業務員使用, 一位業務員可使用多部公務車 多對多 : 表示 一部公務車可讓多位業務員使用, 一位業務員可使用多部公務車 屏東商業技術學院周國華 14
最小基數性 最小基數性 (minimum cardinality) 又稱為參與限制 (participating constraint), 用以指明個體在特定關係中的最低參與程度 例如, 員工 與 已完成工作 之間 (1, 1) 對 (0, N), 表示一名員工可能沒完成任何工作 ( 新進員工 ), 也可能已完成許多工作 ; 一份工作僅能由一位員工完成 ( 無法自動完成 也不能由超過一位員工合作完成 ) 屏東商業技術學院周國華 15
屬性 屬性是個體或關係內的資料項目 在將個體或關係轉成資料表後, 屬性就是資料表內的各個欄位 只有多對多之間的關係才有屬性, 這些屬性將成為關係資料表 ( 通常是將兩個多方的主索引納入, 成為複合主索引 ) 的一部份 每個個體都有多個屬性, 有些屬性的值可決定其他數個屬性的值, 此種屬性稱為決定性屬性 (determinant) 資料表經過正規化 (normalization) 後, 剩下的決定性屬性稱為候選鍵 (candidate key), 此屬性的值具有不重複性 且可決定所有其他屬性的值 候選鍵若不只一個,DBA 須挑選一個做為主鍵 (primary key, 或稱主索引 ) 例如, 員工編號 及 身分證字號 都是 員工 資料表的候選鍵, 可任選一個做為主索引 在 ERD 中主鍵名稱應加上底線 屏東商業技術學院周國華 16
屬性標示範例 : 訂單 & 存貨 屏東商業技術學院周國華 17
將 ERD 轉換成邏輯模式 ERD 描繪完成後, 可按以下步驟轉成邏輯模式 : 1. 為每個個體編製一張資料表 ( 稱為 relation 或 table),erd 內之屬性即為資料表內的各個欄位, 且每張資料表內須包含一個主索引欄位 2. 在 1 對多關係中, 在多方資料表內包含 1 方的主索引, 此欄位稱為外來鍵 (foreign key) 表格間的關聯即藉此建立 3. 在多對多關係中, 另外增加一個關係資料表, 將兩個個體的主索引納入此資料表, 成為複合主索引 (composite PK) 亦即, 將原本的一組多對多關係, 轉成兩組 1 對多關係 ( 關係資料表為多方 ) 4. 在 1 對 1 關係中, 把未來最有可能轉成多方的個體暫時當成多方, 然後按照 1 對多關係處理 若將前述 ERD 範例轉成邏輯模式, 需包含 8 張資料表, 其中兩張為關係資料表 屏東商業技術學院周國華 18
外來鍵 & 參考完整性 多方資料表內任一筆資料列的外來鍵值, 可連結至一方資料表內的特定資料列 為確保此外來鍵值所指向的資料列確實存在 未被刪除, 可在 DBMS 內設定外來鍵的參考完整性 (referential integrity) 一經設定後, 一方的特定資料列的主索引值只要被多方特定資料列的外來鍵引用, 該一方特定資料列即無法被刪除 ( 除非修改多方的外來鍵值 ) 有時, 多方特定資料列的外來鍵的確無法在一方找到對應的主索引值, 此外來鍵可設定為空值 (null) 例如, 在前述 ERD 範例之 銷貨發票 與 客戶 之間的關係為多對 1, 因此, 銷貨發票資料表內會有一欄位為客戶編號 ( 客戶資料表之主索引 ) 在現銷的情境下, 許多客戶並不願留下任何記錄, 故此類交易之銷貨發票內客戶欄會留白 屏東商業技術學院周國華 19
邏輯資料模式範例 將前述 ERD 範例轉成邏輯資料模式如下 ( 共八張表格 ): 屏東商業技術學院周國華 20
實體資料模式 :Access 將前述邏輯資料模式實作在 Access 資料庫所產生之資料庫關聯圖如下 : 屏東商業技術學院周國華 21