~资料库正规化~.pptVIP

* * * * * * * * * 會計資訊系統課程講義Access 2007 DBMS~資料庫正規化~ 周國華 屏東商業技術學院會計系 2007/11/23 大雜燴資料表 假設某家出版社的訂單資料表結構如下： 上述資料表結構的潛在問題如下： 一張訂單可能包含多種書籍，則書籍欄位及其他相關欄位(訂購數量、銷貨定價)會出現多重值。 若將同一訂單的每種書籍分割在不同資料列中，則相同訂單編號會重複出現，不符合主索引的規範。 許多欄位的值需一再重複輸入，不但浪費儲存空間，也很容易出現輸入內容不一致的情況。 這種大雜燴資料表所潛藏的問題，通稱為更新時的異常現象(update anomalies)，可透過資料庫正規化程序來排除。 屏東商業技術學院 周國華 * 訂單編號 訂單日期 客戶名稱 送貨地址 客戶電話 書籍編號 書籍名稱 訂購數量 銷貨定價 收款條件 資料庫正規化 資料庫正規化(normalization)是從關聯式資料模式衍生出來的理論，目的是為大雜燴型資料表的分割提供一套可遵循的模式。它可分成六種形式(normal form)： 第一正規化形式(First Normal Form, 1NF) 第二正規化形式(2NF) 第三正規化形式(3NF) Boyce/Codd 正規化形式(BCNF) 第四正規化形式(4NF) 第五正規化形式(5NF) 隨著正規化形式逐漸複雜(1NF?5NF)，被分割出來的資料表數量越多。且每一種較複雜的形式，都完全符合較簡單形式的規範(一個5NF，一定也是4NF…)，見上圖。 屏東商業技術學院 周國華 * 5NF 4NF BCNF 3NF 2NF 1NF 越分越細不一定好 資料表分割得越細，雖然可進一步減少更新時的異常現象，但使用者在查詢時，系統卻需要用到更多的合併(join)運算，而這種運算是很花時間的。 所以，大部分資料庫管理實務上，資料表的正規化只需要達到3NF即可(有複合主索引的資料表需達到BCNF)，以便在「減少異常現象」與「確保執行效能」之間取得適當的平衡。 屏東商業技術學院 周國華 * 功能相依性 從1NF到BCNF等四種正規化形式，都是以功能相依性(functional dependency)的概念做為思考起點。 功能相依性：在R資料表中的A、B兩個欄位，若A欄位的一個值在任何時點下可對應至B欄位的唯一一個值，則稱B功能相依於A (B is functionally dependent on A)，或A在功能上決定B (A functionally determines B)。 資料表經過正規化程序後，主索引欄位在功能上決定所有其他欄位，或者，所有其他欄位功能相依於主索引欄位。 主索引可能由兩個或多個欄位構成(複合主索引)，因此上述的A，也可以由兩個或多個欄位所構成(: A欄位的一組值可決定B欄位的唯一一個值)。 屏東商業技術學院 周國華 * 1NF 1NF定義：一份資料表內所有資料列的每個欄位，都只包含一個資料項，則該資料表即達到1NF。 以前述訂單資料表為例，一張訂單若包含多種書籍，則每種書籍需個別儲存在不同資料列內。 前述訂單資料表轉成1NF後，訂單編號這個主索引會出現重複值，不符合主索引的條件，因此需要增加一個欄位來構成複合主索引(composite primary key)。 新的(複合)主索引：訂單編號 + 書籍編號 屏東商業技術學院 周國華 * 訂單編號 書籍編號 訂單日期 客戶名稱 送貨地址 客戶電話 書籍名稱 訂購數量 銷貨定價 收款條件 多重值欄位 大部分資料庫軟體都不允許在資料列的個別欄位內儲存多重值，因此資料表在一開始就符合1NF規範。 在某些特殊情況下，使用者可能需要在單一儲存格中存放或閱覽多個資料項，Access 2007針對此需求有兩項新設計： 附件資料類型：某個欄位的資料類型若設定為「附件」，即可在該欄位中儲存多個OLE資料檔案。 多重值欄位：欄位若設定為「附件」以外的資料類型，就只能儲存一筆資料項。但透過「查閱精靈」的設定，讓特定欄位轉成查閱欄後，就可在資料表的單一欄位內呈現多個資料項的內容。 屏東商業技術學院 周國華 * 1NF的問題 資料表達到1NF後，雖然消除了多重值，卻產生一個大問題：某些其他欄位只相依於部分主索引欄位。 例如，在前述訂單資料表中，訂單日期、客戶名稱、送貨地址、客戶電話、收款條件等欄位只相依於訂單編號，書籍名稱、銷貨定價等欄位只相依於書籍編號。 1NF所產生的部分相依性(partial dependency) 會阻礙欄位資料的新增及刪除。 例如，想要新增書籍的相關資料(書籍編號、書籍名稱、銷貨定價)，必須等到拿到訂單後才能進行；想要刪除特定書籍的資料，會連帶把訂單內的其他珍貴資料也刪除掉。 此外，許多敘述性