資料庫設計 Database Design

大綱 資料庫管理系統簡介 資料庫設計的方法 ERD轉關聯資料表 資料正規化 資料型態 資料庫完整性

設計資料庫

資料庫管理系統(DBMS)產品

關聯資料庫之一例

關聯資料庫之重要名詞

資料庫設計的方法 檢視概念資料模型，將ERD轉成關聯資料表 資料正規化 資料庫建置: 建立每一個實體的資料表。 針對每一屬性建立欄位。 針對每一個主鍵、次要鍵及分組條件建立索引。 為關係指定外鍵。 資料正規化 第一、二、三階及其他正規化。 資料庫建置: 對每一屬性，定義資料型態，大小，設定是否可以是空的，值域，以及預設值。 對於超／次型態結構，以結合資料表或另建立新的資料表來完成建置。 評估與定義參考完整性限制。 Teaching Notes Referential integrity is discussed on the next slide.

資料庫設計的原則 Simple簡單 Least redundant儘量減少重複 The data attributes that describe an entity should describe only that entity（RELATION)描述一實體(或關聯表）之屬性應僅有描述該實體(或關聯表） Least redundant儘量減少重複 Each data attribute exists in at most one entity (except for foreign keys)除了外鍵，每個屬性最多只能存在一實體(或關聯表） Flexible and adaptable to future needs彈性且可 調整 No additional notes

實體關係圖轉關聯表 實體關係圖(E-R模式)除了可瞭解資料庫的概念性架構外，最主要的可根據規則，轉換成關聯資料表 （Relational Data Table）。

實體關係圖轉關聯表(Rule 1.1) 對每個一般實體建立一個關聯表 其屬性是所有簡單屬性與合成屬性之集合，從準鍵中選擇一主鍵。 以EMPLOYEE 實體類型為例，可轉成一關聯表，實體之屬性為該關聯表之屬性，並選擇SSN（身分證字號）為其主鍵。如下： SSN BDATE FNAME MNAME LNAME SEX ADDRESS SALARY

實體關係圖轉關聯表(Rule 1.2) 對弱實體建立一個關聯表 其屬性集合所有的簡單屬性、合成屬性與擁有者實體之主鍵， 該關聯表之主鍵由擁有者實體之主鍵與弱實體的不完全鍵所構成。

實體關係圖轉關聯表(Rule 1.3) 對多值屬性建立關聯表 將實體關係圖上的每一個多值屬性建立一個關聯表，其屬性是該多值屬性與擁有者實體類型之主鍵的集合，且其主鍵是由該關聯表之所有屬性所構成。

實體關係圖轉關聯表(Rule 2.1) 對兩實體間之1：1關係 選擇任一實體類型，例如 S，將另一實體類型，例如 R 的主鍵包含進Ｓ中當成外鍵。 S 端最好選擇具有完全參與關係的一端。 將關係上之所有屬性包含入 S 端。

實體關係圖轉關聯表 (Rule 2.2) 對兩實體間之1：N關係 選擇N端當作S端，將R端的主鍵包含進S端中當成外鍵。

Rule 2.2 Example

實體關係圖轉關聯表(Rule 2.3) 對M：N （多對多）關係建立一個關聯表

實體關係圖轉關聯表(Rule 2.4) 對N元關係建立一個關聯表 其屬性是該關係上之所有屬性與所有參與的實體類型之主鍵的集合，且其主鍵為所有外鍵的集合。

Rule 2.4 Example

一元關係 (Rule 2.5) EMPLOYEE (Emp_ID, Name, Birthdate, Manager_ID) ITEM (Item_Number, Name, Cost) ITEM-BILL (Item_Number, component _ Number, quantity)

ER圖轉關聯資料表簡述

ER圖轉關聯資料表簡述(cont’d)

正規化(Normalization) 將資料屬性組合成為一個具有良好結構的關聯表的過程 ERD轉成關聯表的設計步驟，必須包含正規化的處理，否則關聯表中仍可能存在一些重複的資料。 雖然正規化常與ERD相結合，但它也是一種邏輯設計的技術，可以獨立於關聯式資料庫管理系統之外而獨被使用。

未正規化可能造成資料之異常 如下之課程收費表並不是一個良好結構化的關聯表，因為該表中含有重複的資料，可能會造成錯誤或不一致的情況，此現象稱「異常」 (Anomalies) 。

三種可能異常狀況(Anomalies) 新增異常：假設考慮加入一項新課程（例如MIS600），除非至少有一個學員登記了這門課程，否則這個課程將無法加入該表中，因為表中每一列至少要有一學員的學號。 刪除異常：假設學員99425不再選擇MIS400的課程，由於該課程只有該學員登記，刪除後，便失去了MIS400課程收費是6,000元的資訊。 更改異常：假設MIS200課程的學費由3,000元增加至5,000元，那麼在每一包含MIS200課程的列中都必須進行這項改變，否則資料便會不一致。

正規化 採用正規化定理將課程收費關聯表分解為學員課程與課程收費兩項關聯表，以避免上述的異常情形。

正規化原理 介紹正規化前須先瞭解 功能相依(Functional Dependency) 假設有一關聯表 R，且 A 與 B 是 R 的屬性。B 功能相依於 A，或稱 A 在功能上決定 B，寫成 R.A→R.B，若且唯若 A 屬性之值只會對應到一個 B 屬性之值。 其中，A 與 B 都可以是複合屬性。若屬性 B 功能相依於複合屬性 A，但不功能相依於 A 的部分屬性，則稱 B完全功能相依於 A。 部分功能相依 若 B 功能相依於 A 的某些部分，也就是說，若把 A 中之部分屬性刪除，而 B 仍然功能相依於 A，則R.A→R.B 是部分功能相依。 遞移相依 指關聯表中存在非鍵屬性功能相依於一個或多個非鍵屬性

那些是功能相依?部分相依?遞移相依? 功能相依 A, B->C 部分相依 B->D, E 遞移相依 D->E

正規化的步驟

未正規化的關聯表：成績單 主要除去關聯表中任何的重複群，使關聯表中任一行與任一列的交叉格上均只有一個值。

第一正規化型式： 成績單關聯表

第二正規化型式 必須分析成績關聯表之屬性之功能相依，並選出該關聯表之鍵（加底線表示）如下： 必須去除中之部分功能相依，分割成三個關聯表： 學生學號→學生姓名、通訊處、主修 課程代號→課程名稱、授課老師、老師研究室 學生學號、課程代號→成績 授課老師→老師研究室 必須去除中之部分功能相依，分割成三個關聯表： 學生：包括學生學號（鍵）、學生姓名、通訊處和主修。 課程－老師：包括課程代號（鍵）、課程名稱、授課老師和老師研究室。 選課：包括組合鍵（學生學號、課程代號）和成績。

2nd NF另一例

第三正規型式 學生和選課兩關聯表已符合第三正規化型式，但課程－老師關聯表仍為第二正規化型式，因為老師研究室（非鍵屬性）也功能相依於授課老師（非鍵屬性） 課程－老師關聯表須去除遞移相依的情形，分成課程和老師兩個關聯表才符合第三正規化型式。

老師

3rd NF另一例

資料欄位設計 欄位：系統中可辨識的已命名最小應用資料單元。 資料型態：一種由系統軟體識別，用來表示組織性資料的編碼結構。 可以將正規化關聯中的每一個屬性以一個或多個欄位來呈現。 資料型態：一種由系統軟體識別，用來表示組織性資料的編碼結構。 選擇資料型態需要在下列四個目標中取得平衡 最節省存取空間。 可呈現欄位所有可能數值。 提升欄位的資料完整性。 支援欄位所需要的資料操作。

Data Types for Different Database Technologies Logical Data Type to be stored in field) Physical Data Type MS Access Physical Data Type Microsoft SQL Server Physical Data Type Oracle Fixed length character data (use for fields with relatively fixed length character data) TEXT CHAR (size) or character (size) CHAR (size) Variable length character data (use for fields that require character data but for which size varies greatly--such as ADDRESS) VARCHAR (max size) or character varying (max size) VARCHAR (max size) Very long character data (use for long descriptions and notes--usually no more than one such field per record) MEMO LONG VARCHAR or LONG VARCHAR2 Integer number NUMBER INT (size) or integer or smallinteger or tinuinteger INTEGER (size) or NUMBER (size) Decimal number DECIMAL (size, decimal places) or NUMERIC (size, decimal places) NUMERIC (size, decimal places) or Teaching Notes Comparing the data types of different databases underscores database-specific data types vs. conceptual data types and broadens students’ understanding of databases If your school uses another database, add a fourth column

Data Types for Different Database Technologies (cont.) Logical Data Type to be stored in field) Physical Data Type MS Access Physical Data Type Microsoft SQL Server Physical Data Type Oracle Financial Number CURRENCY MONEY see decimal number Date (with time) DATE/TIME DATETIME or SMALLDATETIME Depending on precision needed DATE Current time (use to store the data and time from the computer’s system clock) not supported TIMESTAMP Yes or No; or True or False YES/NO BIT use CHAR(1) and set a yes or no domain Image OLE OBJECT IMAGE LONGRAW Hyperlink HYPERLINK VARBINARY RAW Can designer define new data types? NO YES Teaching Notes Comparing the data types of different databases underscores database-specific data types vs. conceptual data types and broadens students’ understanding of databases If your school uses another database, add a fourth column

資料庫完整性Database Integrity 主鍵完整性Key integrity 　　　　　　　　　　　　　　　　　　　　　　　　每一資料表應該有一個主鍵。 值域完整性Domain integrity 　　　　　　　　　　　　　　　　　　　　 必須設計適當的控制來確保沒有欄位接受不正確的值。 參考完整性Referential integrity 　 確定一個資料表中的外鍵值在其關聯的資料表中有一個相同的主鍵 值。 沒有限制的 刪除：串連 刪除：限制 刪除：設成空白 No additional notes

資料值域完整性的控制 預設值：除非有其他數值輸入該欄位，否則欄位數值會先被預設一個數值。 範圍控制：針對數量或字母類型的資料都有可允許輸入數值的限制。 空值(null value)是一個很特別的欄位數值，可以為0、空白或是其他數值，代表欄位數值遺失或是未知。

參考完整性一例

參考完整性一例