軟體開發概論 第六章、資料庫

1. 軟體開發概論第六章、資料庫 Understanding Databases

2. Objective Domain Matrix

3. 資料 VS. 資訊 • 資料可以合成為資訊 • 資料：80，20 • 資訊的呈現要經過適當的詮釋 • 80% 的營收來自 20% 產品的銷售

4. 資料層級 • 位元（Bit） • 位元組（Byte） • 字元（Character） • 字串（String） • 資料欄位（Field） • 資料記錄（Record） • 檔案（File） • 資料庫（Database） • 資料超市（Data Mart） • 資料倉儲（Data Warehouse）

5. 資料庫 • 資料庫是資料的集中儲存位置，藉由關聯的定義，將多筆、多樣的不同資料，成為一個緊密結合的處理單元 • 高效率的資料存取 • 多人同時共用 • 容量 • 資料型別 • 使用者介面 • 應用程式介面

6. 展現層 邏輯層 資料層

7. 資料庫 • 資料庫系統(Database Systems)的組成 • 資料庫（Database） • 資料庫管理系統（Database Management System，DBMS） • 應用系統（Application System） • 關聯式資料庫 • Relational Database • 關聯式資料庫管理系統（Relational DBMS，RDBMS） • 物件導向式資料庫 • Object-Oriented Database（OODB） • 物件導向式資料庫管理系統（Object DBMS, ODBMS）

8. 常見的資料庫系統 • 單機： • Microsoft Access (JET) • 嵌入式 • SQLite • MySQL Embedded • Microsoft SQL Server Compact • 伺服器： • Microsoft SQL Server • Oracle Database • IBM DB2 • Sybase Advantage Database Server (ADS) • MySQL • PostgreSQL

9. 關聯式資料庫 • 常見的 SQL Server、Access、Oracle、MySQL 等都是 RDBMS • 資料儲存在資料表（Table）之中 • 一個資料庫系統之中可以有很多個資料庫 • 一個資料庫之中可以有很多個資料表 • 資料表之間存在著某些關係（Relationship）

10. 資料表的結構 • 橫向：資料列（Row） • 又稱為資料紀錄（Record） • 縱向：資料行（Column） • 又稱為資料欄位（Field）

11. 資料表之間的關係

12. 資料表之間的關係 Note the Price column, which has been added.

13. 資料表之間的關係

14. 索引鍵（Key） • 識別的依據（Identifier） • 唯一性（Uniqueness） • 例如： • 身份證字號 • 學號 • 可供識別的依據不只一個 • 候選鍵（Candidate Keys） • 主鍵（Primary Key） • 主索引 • 資料排序的依據

15. 索引鍵（Key） 家庭成員的主鍵是什麼？ 社團會員的主鍵是什麼？ 學生基本資料的主鍵是什麼？ 客戶基本資料的主鍵是什麼？ 產品基本資料的主鍵是什麼？

16. 實體關係圖（Entity Relationship Diagrams） • 以圖形的方式，呈現關聯式設計的結果 • 實體（Entity）：系統運作所需的個體 • 屬性（Attribute）：對實體的描述，形容實體如何存在於資料庫內 • 關係（Relationship）：實體之間的關係

17. 實體關係圖的常見符號 • 矩形代表實體 • Entity • 橢圓代表屬性 • Attribute • 菱形代表關係 • Relationship

18. 數量關係： 1 對 1 1 對 多 多 對 多

19. 關聯式資料庫的設計 • 列出或寫下資料庫的任務 • 那些資料需要儲存？那些不需要儲存？需要儲存的資料中，又應使用什麼資料型態？ • 將屬性類似的資料集中，成為一個資料表 • 在多個欄位中，選擇一個主索引 • 主鍵（Primary Key） • 組合鍵（Composite Key）：由多個欄位組成 • 在多個資料表之間，找出應該建立的關聯 • 將前步驟的結果，根據正規化的要求，予以正規化

20. 將實體關係圖轉換至關聯式資料庫 • 處理實體 • 一個實體轉換成一個或多個資料表 • 處理關係 • 關係轉換成外來鍵（Foreign Key） • 外部索引鍵 • 不是我的主鍵，是因為和別人建立關係才有的索引鍵 • 用途：查表

21. 資料庫設計範例 • 銷售系統需求描述 • 本公司是腳踏車的中盤商，以批發為主，偶爾也賣給散客 • 銷售系統的主要目的為提供每日銷售狀況，讓主管掌握營收金額、了解庫存現狀、知道貨賣到哪裡去了

22. 資料庫設計範例 • 銷售系統需求描述 • 客戶購買產品時會有訂單，訂單上註明客戶的名稱、聯絡電話、送貨地址、預定交貨日期以及總金額。 • 一張訂單可以購買許多項產品，每一項產品註明其產品編號、產品名稱、單價、數量以及小計（小計=單價＊數量）

23. 資料庫設計範例

24. 正規化處理 正規化是針對前一階段的分析結果，再進一步的處理 目的是檢視分析結果中有沒有可能導致資料不一致、違反完整性的情況，若有則予以更正 要點：消除重複的資訊 動作：分割資料表

25. Data Anomalies資料異常 • 新增異常（Insert Anomaly） • You cannot insert new data because of unrelated dependency • 刪除異常（Delete Anomaly） • Deleting one piece of data causes unintended loss of other data • 更新異常（Update Anomaly） • Updating a single data value requires multiple rows to be updated

26. 資料異常再一例 • 處女座的設備管理員不接受不完整的紀錄 • 新買一台： 400,車床,5000，沒有維修紀錄無法登錄？ • 刪除銑床 2012/4/19 的維修紀錄，銑床的進貨價不見了！？ • 更新鑽床的進貨價 3560，到底是要改那幾個地方？

27. Data Normalization資料正規化 • 消除資料異常，確保資料完整性（Integrity） • 第一階正規化（First Normal Form） • 1NF • 第二階正規化（Second Normal Form） • 2NF • 第三階正規化（Third Normal Form） • 3NF

28. First Normal Form (1NF) 單一值（Atomic） 一格只能存放一個值

29. Second Normal Form (2NF) 必先符合 1NF 2NF all non-key columns are functionally dependent on the entire primary key 非鍵欄位必須相依於『整個』主鍵

30. Third Normal Form (3NF) 不符合 3NF 因為 ReorderFax取決於 SupplierId而不是主鍵 ItemId 欲符合 3NF 必先符合 2NF 3NF 不是鍵值的欄位（ non-key attributes ）之間，不得存在功能相依性（Functional Dependency）

31. 功能相依性（Functional Dependency） 表格中有C→D的功能相依性，因為只要知道欄位C的值，就能決定欄位D的值。只要C是吐司，D必定是30，若C是牛奶，則D必是50；我們可以把D看成是物品C的價格。 欄位A與欄位B若有存在功能相依性，而此關係為A→B時，則任何記錄的欄位A的值將只有唯一的欄位B的值與其對應，此關係也可表示成「A determines B」。

32. 功能相依性（Functional Dependency） {A,B}  C? {A,B}  C?

33. 結構化查詢語言Structured Query Language (SQL) • 用途：SQL 查詢關聯式資料庫 • T-SQL: Transact SQL • 微軟的『交易式』SQL • 執行 T-SQL 的方式 • 自訂的 SQL 查詢敘述 • 預存程序（Stored Procedure)

34. SQL 查詢 • 增刪查改 • INSERT、DELETE、SELECT、UPDATE • SELECT 撈（選取）資料 • INSERT 新增（插入）資料 • UPDATE 更新（改）資料 • DELETE 刪除資料

35. 執行 SQL 查詢 • 連接 SQL Server 執行 SQL： • 透過 Visual Studio IDE • 寫程式（C＃/VBN.ET） • SQL Query Analyzer • osql命令列工具

36. SELECT SELECTlist_of_fields列出欄位，* 代表所有欄位 FROMlist_of_tables列出資料表 WHEREwhere_clause列出條件（過濾資料） GROUP BY group_by_clause分組依據（欄位） HAVINGhaving_clause分組後的條件（過濾資料） ORDER BY order_by_clause排序方式

37. SELECT 範例 查詢訂單與客戶資料合併後的結果： SELECTOrderID, Customers.CustomerId, ContactName FROM Orders INNER JOIN Customers ONOrders.CustomerId = Customers.CustomerId 列出交貨地點為加拿大的訂單： SELECT * FROM Orders WHEREShipCountry = 'Canada'

38. UPDATE • 更改舊資料 • 何謂舊資料？ • 更改客戶名稱： UPDATE Customers SETContactName = 'Maria Anderson' WHERECustomerId = 'ALFKI'

39. INSERT • 插入新資料 • 何謂新資料？ • 新增訂單明細： INSERT INTO [Order Details] (OrderId, ProductId, UnitPrice, Quantity, Discount) VALUES (10248, 2, 19.00, 2, 0)

40. DELETE 刪除舊資料 刪除指定的客戶資料： DELETEFROM Customers WHERECustomerId = 'ALFKI'

41. 預存程序Stored Procedures • 儲存在資料庫裡的副程式 • 可以從外部呼叫 • 優點 • 儲存複雜的 SQL，可供重複使用 • SQL Server 事先編譯預存程序，執行速度更快 • 減少網路傳輸資料量

42. 建立預存程序 • 指令：CREATE PROCEDURE • 列出法國客戶： CREATE PROCEDURE GetCustomersFromFrance AS SELECT * FROM Customers WHERE Country = 'France' RETURN

43. 預存程序帶參數 • 變數與參數第一個字符必須為 @ CREATE PROCEDURE dbo.GetCustomerSales ( @CustomerId char(5), @TotalSales money OUTPUT ) AS SELECT @TotalSales = SUM(Quantity * UnitPrice) FROM (Customers INNER JOIN Orders ONCustomers.CustomerId = Orders.CustomerId) INNER JOIN [Order Details] ONOrders.OrderId = [Order Details].OrderId WHERECustomers.CustomerId = @CustomerId RETURN

44. 使用平坦檔案（Flat File） • 平坦檔案：沒有階層結構的檔案 • 文字檔案（Text File） • 二進位檔案（Binary File） • 使用 StreamReader、StreamWriter存取文字檔 • 使用 BinaryReader、BinaryWriter存取二進位檔

45. Extensible Markup Language (XML) 具有階層式結構的文字檔

46. 使用 XML • 主要工具都在 System.Xml命名空間內 • XmlReader、XmlWriter： • 快速 • 無快取（non-cached） • 單向、向前（forward-only） • XmlDocument： • 檔案載入記憶體，建立樹狀結構 • 可以來回查詢與編輯

47. 使用 DataSet • 資料集DataSet • 儲存在記憶體內的關聯式資料庫 • 內含資料表（DataTable）、關聯、資料完整性約束條件（單一值索引、外來鍵） • DataAdapter為資料來源和DataSet之間的橋樑 • DataAdapter儲存資料庫連線和 SQL 指令 • 包括查詢用的 SQL • 使用CommandBuilder自動產生增刪改的 SQL

48. 總結 • 關聯式資料庫設計 • 個體關係圖 Entity Relationship Diagram • 資料正規化 （1NF, 2NF, 3NF） • 資料查詢 • SQL • 預存程序 • 資料庫連結方式 • 平坦檔案（Flat File）、XML、DataSet