890 likes | 1.13k Views
第 4 章 資料定義與資料庫設計. 本章宗旨. 簡介 MySQL 基本資料型態與定義 說明 MySQL 合法的資料範圍 簡介資料庫之檔案組織 說明正規化原理與應用 介紹 MySQL 資料表定義和管理指令 實務案例應用說明與實作. 大 綱. 4-1 MySQL 欄位型態 4-2 合法資料範圍 4-3 資料庫簡介 4-4 關聯式資料庫正規化 4-5 資料表定義和管理 4-6 實作 . 4-1 MySQL 欄位型態 . 4-1-0 定義符號說明 4-1-1 數值型態總覽 4-1-2 日期與時間型態
E N D
第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
1. 檔案結構定義方式(續) | [CONSTRAINT [符號]] UNIQUE [INDEX] [索引表名稱] [索引型態]] (索引欄位,...) | [FULLTEXT|SPATIAL] [INDEX] [索引表名稱] (索引欄位,...) | [CONSTRAINT [符號]] FOREIGN KEY [索引表名稱] (索引欄位,...) [參照定義] | CHECK (運算式序述) 主從式資料庫系統 - CH4
2. 欄位定義方式 • 欄位名稱資料型態 [NOT NULL | NULL] [DEFAULT 欄位預設資料] [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT '註解'] [參照定義] 主從式資料庫系統 - CH4
3. 資料型態選項 TINYINT[(寬度)] [UNSIGNED] [ZEROFILL] | SMALLINT[(寬度)] [UNSIGNED] [ZEROFILL] | MEDIUMINT[(寬度)] [UNSIGNED] [ZEROFILL] | INT[(寬度)] [UNSIGNED] [ZEROFILL] | INTEGER[(寬度)] [UNSIGNED] [ZEROFILL] | BIGINT[(寬度)] [UNSIGNED] [ZEROFILL] 主從式資料庫系統 - CH4