第 4 章資料定義與資料庫設計

第4章資料定義與資料庫設計 主從式資料庫系統 - CH4

本章宗旨 • 簡介MySQL基本資料型態與定義 • 說明MySQL合法的資料範圍 • 簡介資料庫之檔案組織 • 說明正規化原理與應用 • 介紹MySQL資料表定義和管理指令 • 實務案例應用說明與實作主從式資料庫系統 - CH4

大綱 4-1 MySQL欄位型態 4-2 合法資料範圍 4-3 資料庫簡介 4-4 關聯式資料庫正規化 4-5 資料表定義和管理 4-6 實作主從式資料庫系統 - CH4

4-1 MySQL欄位型態 4-1-0 定義符號說明 4-1-1 數值型態總覽 4-1-2 日期與時間型態 4-1-3 字串型態主從式資料庫系統 - CH4

4-1 MySQL欄位型態 • MySQL資料型態定義符號 • M：表示最大資料顯示寬度，合法的最大寬度為255 • D：表示浮點數和固定小數系列之有效位數，代表小數點後之位數 • [ ]：中括弧內之修飾辭為一選用項目，由使用者自行決定是否需要此部份主從式資料庫系統 - CH4

4-1 MySQL欄位型態(續) 4-1-1 數值型態總覽 • TINYINT[(M)] [UNSIGNED] [ZEROFILL] ，1Bytes短整數。 • BIT：位元 • BOOL：布林值 • BOOLEAN：布林值，此型式與TINYINT(1)等效，其中以0表False，非0值表True • SMALLINT[(M)] [UNSIGNED] [ZEROFILL]：2Bytes短整數主從式資料庫系統 - CH4

4-1-1 數值型態總覽 • MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] ：3Bytes整數 • INT[(M)] [UNSIGNED] [ZEROFILL]：4Bytes整數 • INTEGER[(M)] [UNSIGNED] [ZEROFILL]：此型式與INT等效 • BIGINT[(M)] [UNSIGNED] [ZEROFILL] ：8Bytes長整數主從式資料庫系統 - CH4

4-1-1 數值型態總覽 • FLOAT(p) [UNSIGNED] [ZEROFILL]：浮點數，其中p表精確度。若0≦ p ≦ 23，以單精數處理；若24≦ p ≦ 53，以倍精數處理 • FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]：浮點數，無參數之格式，將以單精數處理主從式資料庫系統 - CH4

4-1-1 數值型態總覽 • DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]：倍精數。 • DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL] ：倍精數 • REAL[(M,D)] [UNSIGNED] [ZEROFILL]：實數，與Double等效。SQL模式設定為：REAL_ASFLOAT，則與Float等效主從式資料庫系統 - CH4

4-1-1 數值型態總覽 • DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]： • unpacked固定位數小數型 • 所謂unpacked，指數值資料以字元型式儲存 • 其中，M用於定義總寬度，包含小數寬度D，但不包含正負號和小數點 • M可缺設，預設值為10。D若省略，預設值為0 主從式資料庫系統 - CH4

4-1-1 數值型態總覽 • DEC[(M[,D])] [UNSIGNED] [ZEROFILL]：unpacked固定位數小數型 • NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]：unpacked式固定位數小數型 • FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]：unpacked固定位數小數型，第16~18項與Decimal等效主從式資料庫系統 - CH4

4-1-1 數值型態總覽 運算結果 • 若所有運算元都是整數之算術運算式，運算結果將以長整數方式處理 • 若運算值超過長整數之上限值時，將產生錯誤結果 • 運算式中有非整數者，以倍精數（Double）處理主從式資料庫系統 - CH4

4-1-2 日期與時間型態 • DATE：日期，以【YYYY-MM-DD】格式表示，合法格式從'1000-01-01' 至 '9999-12-31'之字串 • DATETIME：日期時間，以【YYYY-MM-DD HH:MM:SS】格式表示，合法格式從'1000-01-01 00:00:00'至'9999-12-31 23:59:59'之字串 • TIMESTAMP：時戳，格式為【YYYY-MM-DD HH:MM:SS】，合法範圍從'1970-01-01 00:00:00'到公元2037年。進行INSERT或UPDATE程序時，MySQL自動更新時戳為當下之日期和時間主從式資料庫系統 - CH4

4-1-2 日期與時間型態 • TIME：時間型，格式為'HH:MM:SS'，時間資料允許以字串或數值型式表示 • YEAR[(2|4)]：年度型，格式為’YYYY’ ，四位數合法範圍從1901到2155年，兩位數之合法範圍從70到69年(1970 - 2069)。年度資料允許以字串或數值型式表示入主從式資料庫系統 - CH4

4-1-3 字串型態 • [NATIONAL] CHAR(M)[BINARY | ASCII | UNICODE]：指定長度字串，NATIONAL用於字元資料須搭配預設的字元集 • CHAR：單Byte字元，與CHAR(1)等效。 • [NATIONAL] VARCHAR(M) [BINARY]：可變長度字串 • BINARY(M)：與CHAR同，但區分大小寫主從式資料庫系統 - CH4

4-1-3 字串型態 • VARBINARY(M) ：與VARCHAR相同，但區分大小寫 • TINYBLOB/TINYTEXT：小型長字串，最大長度為255 (2^8 - 1) 字元 • BLOB/TEXT：中小型長字串，最大長度為65,535 (2^16 -1) 字元 • MEDIUMBLOB/MEDIUMTEXT：中型長字串，最大長度為16,777,215 (2^24 - 1) 字元主從式資料庫系統 - CH4

4-1-3 字串型態 • LONGBLOB /LONGTEXT：中型長字串，最大長度為4GB (2^32 - 1)字元註：BLOB（BINARY LARGE OBJECT）與TEXT之差別，在於BLOB有區分大小寫，除了文字資料外，也可以儲存二進制檔資料，如聲音和影像主從式資料庫系統 - CH4

4-1-3 字串型態 • ENUM('value1','value2',...)：列舉型字串，最多允許設定65,535個元素，儲存資料是以整數型索引值來對應字串儲存位置 • SET('value1','value2',...)：集合型字串，最多允許設定64個元素，儲存資料是以整數型數值資料來對應字串儲存組合主從式資料庫系統 - CH4

4-2 合法資料範圍 - 數值型 主從式資料庫系統 - CH4

4-2 合法資料範圍 -日期時間型 主從式資料庫系統 - CH4

4-2 合法資料範圍 -日期時間型 • 合法格式: • YYYY-MM-DD, YY-MM-DD • YYYY.MM.DD, YY.MM.DD • YYYY/MM/DD, YY/MM/DD • YYYYMMDD, YYMMDD • YYYYMMDDHHMMSS, YYMMDDHHMMSS 主從式資料庫系統 - CH4

4-2 合法資料範圍 -字串型 主從式資料庫系統 - CH4

4-2 合法資料範圍 -字串型 • CHAR與VARCHAR儲存資料所需空間比較主從式資料庫系統 - CH4

4-3 資料庫簡介 4-3-1 資料庫定義 4-3-2 資料庫管理系統 4-3-3 相關辭彙 4-3-4 資料模式主從式資料庫系統 - CH4

4-3-1 資料庫定義 具有固定屬性及重複性質的資料，為了便於管理和維護，以及提供多人同時使用，可將這些資料儲存在經過有系統規劃的檔案內，這些檔案之集合即稱為資料庫（Database）。主從式資料庫系統 - CH4

4-3-2 資料庫管理系統 • 一個用來定義、組織和管理資料、紀錄和檔案的應用軟體，通常包含下列三個模組: • 資料定義模組：用於定易義和維護資料庫之結構 • 資料操作模組：用於操作資料庫之資料，例如新增、修改、刪除、查尋等程序 • 資料控制模組：用於維護和控制資料庫，例如安全控管、交易管理、備援、當機回復等程序主從式資料庫系統 - CH4

4-3-3 相關辭彙 • 欄位：用來儲存具有相同或類似屬性物件之特徵值，例如通訊錄檔之姓名、電話、地址等，此為資料庫系統最基礎之單元 • 紀錄：用來儲存特定物件內各個元素之所有特徵值，例如通訊錄檔內某人之個人資料，稱之為一筆紀錄，一筆紀錄通常是由一個以上之欄位所組成 • 檔案：用來儲存具有相同或類似屬性物件之所有記錄，這些紀錄的集合即稱為檔案主從式資料庫系統 - CH4

4-3-3 相關辭彙 • 循序檔：按照資料發生之先後次序，依序儲存資料之檔案 • 索引檔：以檔案內某些欄位當鍵值，將資料按遞增或遞減方式儲存之檔案。索引檔之功能，在於快速擷取一個大型檔案內之特定紀錄主從式資料庫系統 - CH4

4-3-4 MySQL資料模式 • MySQL和Access之資料模式均屬於關聯式關聯式資料庫（Relational Database）儲存資料的方式，是採用正規化（Normalization）處理手法，將大型資料分散在不同但具有特定關聯關係的資料表內，而不是將所有資料儲存在一個大表單 • 關聯式資料庫系統可以增進資料處理的速度和彈性，也可增進資料之維護性（Maintainability）主從式資料庫系統 - CH4

4-4 關聯式資料庫正規化 (Normalization) 4-4-1 定義 4-4-2 未經正規化之案例 4-4-3 正規化程序 4-4-4 功能相依性定義 4-4-5 第一正規化 4-4-6 第二正規化 4-4-7 第三正規化 4-4-8 正規化處理規則主從式資料庫系統 - CH4

4-4-1 定義 • 所謂正規化(Normalization)，就是將資料庫結構予以合理化的一種改善措施，其目的在於增加資料存取的速度和資料庫之完整性，良好的資料庫設計，可以改善速度和完整性的平衡 • 一般說來，經過正規化處理之資料庫，可以減少資料的重複性，可以減少資料因插入、修改與刪除所造成之錯誤，也可以提昇資料檢視的效率主從式資料庫系統 - CH4

4-4-2 未經正規化之案例 主從式資料庫系統 - CH4

4-4-2 未經正規化之案例 • 插入問題：若要新增一家經銷商{5, 大同資訊, 40, 中壢市, ,}，但該經銷商尚無訂購資料，使得ID及NO為虛值，因而造成該筆資料無法插入 • 刪除問題：欲刪除神風資訊之訂單資料，但該經銷商只有一筆資料，因而連帶將該經銷商的資料一併刪除了 • 更新問題：若要將正典資訊之所在地從台北市改為臺中市，則同時要改變五筆資料，有可能遺漏修改之情形，因而造成「異動更新異常的問題」(Update Abnormality) 主從式資料庫系統 - CH4

4-4-3 正規化程序 • 著名的正規劃處理程序： • 第一正規化(First Normal Form，簡稱1NF) – 由E.F. Codd所提出 • 第二正規化(Second Normal Form，簡稱2NF) – 由E.F. Codd所提出 • 第三正規化(Third Normal Form，簡稱3NF) – 由E.F. Codd所提出主從式資料庫系統 - CH4

4-4-3 正規化程序(續) • Boyce/Codd正規化(Boyce/Codd Normal Form，簡稱BCNF) – 由R.F. Boyce和E.F. Codd所提出 • 第四正規化(Fourth Normal Form，簡稱4NF) – 由R. Fagin所提出 • 第五正規化(Fifth Normal Form，簡稱5NF；或Projection Join Normal Form, 簡稱PJ/NF) – 由R. Fagin所提出主從式資料庫系統 - CH4

4-4-4 功能相依性定義(Function Dependency) • 對於一個關聯表R，若R中之屬性B具有功能性相依於屬性A (B Funtionally Dependent on A)，或稱屬性A決定屬性B (A Funtionally Determines B) • 一般化表示方式：R.A→R.B，若且唯若任何時候R中的屬性A只會對應到一個屬性B 主從式資料庫系統 - CH4

4-4-5 第一正規化 • 一個關聯表屬於1NF型式，若且唯若他所有屬性的值域(Domain)都只包含單元值(Atomic Value) • 亦即去除多值正規化案例未正規化案例主從式資料庫系統 - CH4

4-4-6 第二正規化 • 一個關聯表屬於2NF型式，若且唯若他屬於1NF，所有不屬於主鍵之屬性都功能相依於該關聯表之主鍵（只儲存同類資料），亦即去除部份相依 • 前述PO 資料表經2NF處理後，可拆成兩個關聯表：Orders及Dealers 主從式資料庫系統 - CH4

4-4-6 第二正規化 –案例 Orders (2NF) Dealers (2NF) 主從式資料庫系統 - CH4

4-4-7 第三正規化 • 一個關聯表屬於3NF型式，若且唯若他屬於2NF，所有不屬於主鍵之屬性，都只功能相依於該關聯表之主鍵(不存在部分相依關係，亦即間接相依) 主從式資料庫系統 - CH4

4-4-7 第三正規化 –案例 City_Grade (3NF) No_Name_City (3NF) 主從式資料庫系統 - CH4

4-4-8 正規化處理規則 • 規則一：刪除重複群組，為每一組相關的欄位建立個別的資料表，並且給每一個資料表一個主鍵(1NF) • 規則二：除去多餘資料，如果一個欄位只依照一個多值鍵值(Mutivalued Key)的部分值，可將此欄位移至另一個資料表。例如：為確認一筆紀錄而需填寫兩個欄位的話(例如員工代號，員工姓名)，而這些欄位只有一個需要放在此資料表，此類欄位需要建立一個新的資料表(2NF) 主從式資料庫系統 - CH4

4-4-8 正規化處理規則 • 規則三：除去與主鍵欄位無相依關係之欄位，如果一個欄位完全和索引鍵沒有關聯，需將此類欄位移至其他資料表(3NF) • 規則四：隔離獨立的多重關係，沒有一個資料表可以包含數個一對多或多對多之直接關係，例如一位員工具有多項技能，則必須將員工資料和技能資料分別建在兩個不同的資料表(4NF) 主從式資料庫系統 - CH4

4-4-8 正規化處理規則 • 規則五：隔離相關的多重關係，如果資料表內存在數個複雜的關係，那麼必須將每項關係分別放在不同的資料表(5NF)。例如下述關係： • 每個部門均有數筆設備 • 每種設備可能分由數個供應商提供 • 每個供應商可提供數種設備 • 每個部門有一份資格限制的供應商名單主從式資料庫系統 - CH4

4-5 資料表定義和管理 4-5-1 建檔指令語法 4-5-2 表建檔指令說明 4-5-3 檔案組織 4-5-4 資料儲存引擎 4-5-5 建表指令範例 4-5-6 修改資料表 4-5-7 建議事項 4-5-8 MySQL與Access資料型態對照主從式資料庫系統 - CH4

4-5-1 建檔指令語法 • 語法一：CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 資料表名稱 (檔案結構定義,...)[資料表選擇項目] [選取查詢] • 語法二：CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 資料表名稱【（】LIKE舊資料表名稱【）】; 主從式資料庫系統 - CH4

4-5-1 建檔指令語法(續) 1. 檔案結構定義方式：包含下列定義，各項定義必須包含在()內，定義間必須以逗號間隔欄位定義 | [CONSTRAINT [符號]] PRIMARY KEY [索引型態] (索引欄位,...) | KEY [索引表名稱] [索引型態] (索引欄位,...) | INDEX [索引表名稱] [索引型態] (索引欄位,...) 主從式資料庫系統 - CH4

2. 欄位定義方式 • 欄位名稱資料型態 [NOT NULL | NULL] [DEFAULT 欄位預設資料] [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT '註解'] [參照定義] 主從式資料庫系統 - CH4

第 4 章 資料定義與資料庫設計