資料庫概論.

Presentation on theme: "資料庫概論."— Presentation transcript:

1 資料庫概論

2 資料 在日常生活中，我們會面對許多不同型態的資料，如親朋好友的通訊資料、個人行程計畫等等。為了保存這些資料，我們會自定一些規則，將它們有組織地記錄在紙張或電腦上，以便將來取用。我們用這樣的方法長期保存資料，就算是一種「資料庫」。

3 資料模型 我們會自定一些資料記錄的規則，將資料庫以固定的架構來組成；而用來表示資料庫如何組成的架構，稱為資料模型。關於資料模型的理論不少，其中最為著名的是「關聯式資料模型」（relational model of data），這是 E. F. Codd 博士（數學家，IBM 的研究人員）於 1970 年在「A Relational Model of Data for Large Shared Data Banks」這篇論文中所提出的。　這項理論隨後不斷地被討論與修正，到 1980 年前後開始有「關連式」的資料庫產品上市；自此之後，資料庫方面的發展與研究幾乎都是「關連式」的天下了。

4 關聯式系統 簡單地說，「關連式系統」就是：
使用者看到的都是表格。 使用者可使用的運算子，都是從舊表格中產生新表格。這些運算子至少包括 RESTRICT(SELECT)、PROJECT 與 JOIN。 換句話說，「關連式系統」的特徵就是其利用表格來呈現資料，然後將表格視為集合來進行處理。當要操作資料時，便是針對表格去執行以集合理論為基礎的數學運算，而其執行結果還是表格。

5 何謂表格 下圖是一個「表格」（table），其中縱向的稱為「行」（column），或是稱為「欄」（field），存放著相同性質的資料。橫向的稱為「列」（row），或是「記錄」（record），裡頭包含許多不同性質的資料項目。這個表格有 4 欄 3 列。

6 Index 針對絕不會重覆的欄位建立索引「Index」，系統中會產生一個Index table，在Index table中，該欄位將會被排序過，因此尋找資料時，就不被自表格開頭循序尋找，而是先以「二分法」在Index table找到需要的欄位，再對應至原來的表格，得到所需的記錄。如圖

7

8 index 對搜尋速度的幫助 以數學的觀點來看這個問題。假如某個表格有 N 筆記錄，在沒有 index 的情況下，想找到特定記錄，最快的是 1 次（目標恰好在開頭處），最慢的則是 N 次（目標恰好在末尾處），平均得花上 N/2 次才能找到目標。 有了 index，再使用「二分法」來搜尋的話，因為每找一次，即可去除一半的資料，所以平均只要 Log2N 次即可。

9 Index 的缺點 需要更多資料儲存的空間 增加資料異動所需的時間
本來只有一個原始表格，當我們將「學號」設為 index 之後，就多了一個 index table；若再將「姓名」設為 index 的話，又會多出一個 index table 來。在儲存成本日漸下滑的今日，您或許不太介意這種問題，但第二項缺點則是您不可忽視的。 增加資料異動所需的時間 當我們在原始表格中加入一筆「75121」的成績記錄時，相關的 index table 也需要跟著同步更新。越多欄位被設為 index 的話，需要同步更新的 index table 也就更多，這樣一來，就會花費更多資料處理的時間。

10 Key 候選鍵（Candidate Key）
具有資格成為 primary key 的「候選人」。「候選鍵」可以由一至多個欄位組成，但它必須同時符合以下兩項條件才行。 「唯一性」（uniqueness）： 在被選定為「候選鍵」的欄位中，沒有任何兩組相同的記錄。 「最簡性」（irreducibility）： 由多欄位組成的「候選鍵」，其中的任何欄位組合都不能具有唯一性。

11 Key 主鍵（Primary Key）與候補鍵（Alternate Key）
可在一個表格中挑選一組「候選鍵」做為「主鍵」，其他沒被選上的「候選鍵」，則被稱為「候補鍵」。 有了「候選鍵」之後，我們可以用來區別每筆記錄，不致弄錯欲異動的對象。因此，雖說每個表格不見得一定要有「主鍵」，但一定至少要有一組「候選鍵」。

12 Key 外鍵（Foreign Key） SP 的 S# 與 P# 是「外鍵」，它們分別對應到 S 的 S# 與 P 的 P#。

13 外鍵（Foreign Key） 當 SP 表的 S# 值為 S1 時，在 S 表的 S# 之中，一定要有 S1 這個值。若將 S 表的 S1 值給刪除了，則 SP 表中 S# 為 S1 的所有資料列需要一併刪除。也就是說，資料庫中不能含有任何未相配的外鍵值，這樣才能維持參考完整性（referential integrity）。 在異動「外鍵」所對應的內容時，必須連帶異動「外鍵」本身的值，或是做一些必要的限制。例如，欲異動 S 之 S# 的某筆資料時，需連帶異動 SP 之 S# 中相關的資料；或者是限制只能異動還沒被 SP 對應到的其它 S#（如 S4）。

14 資料正規化 何謂正規化 將表格細分成多個更小的表格，直到每個表格只描述一種事實為止，這一連串的調整過程就稱為資料正規化（Normalization）。 目的 簡單的說就是要將資料的重覆性降至最低（避免資料重複的狀況發生）。倘若在不同的表格中都有學生的姓名時，一旦有個學生改名了，則必須同步更改多個表格的內容；修改的過程中若稍有遺漏，有些資料沒更正，就會發生不一致的狀況。因此，避免資料重複是相當重要的。

15 步驟 第一正規化（First Normal Form，簡稱 1NF。由 E. F. Codd 提出）
第二正規化（Second Normal Form，簡稱 2NF。由 E. F. Codd 提出）　 第三正規化（Third Normal Form，簡稱 3NF。由 E. F. Codd 提出）　 Boyce/Codd 正規化（Boyce/Codd Normal Form，簡稱 BCNF。由 R. F. Boyce 與 E. F. Codd 共同提出）　 第四正規化（Fourth Normal Form，簡稱 4NF。由 R. Fagin 提出）　 第五正規化（Fifth Normal Form，簡稱 5NF。由 R. Fagin 提出）

16 第一正規化（First Normal Form）
第一正規化的表格最重要的是能滿足「每個欄位只能含有一個值」這個條件。 但有可能產生： 無法單獨新增一筆學生資料。因為 Subject_no 是 Primary key 之一，不能為空值（Null）；因此，一個未修習任何課程學生的資料，將無法寫入 A。 　 無法單獨刪除一筆成績資料。如果我們打算刪除（75524, S5302）這筆資料的話，該生的地址資料也將一併消失。 　 需要同步異動的資料太多。假如 這個學生搬家了，那麼我們得異動其中的 6 筆紀錄。

17 第二正規化（Second Normal Form）
一個表格必須滿意第一正規化的條件，並且非主鍵的欄位都要對主鍵有「完全地功能性相依（Fully Functional Dependency）」關係，才能算是達到第二正規化。 在一個表格中，如果某一欄位值可決定其他欄位值；而這些欄位中又存在某一欄位可以決定剩餘欄位的值，稱為「遞移相依性（Transitive Dependency）」。若有此一情況發生，在異動資料時，可能會造成其他資料不一致的現象。

18 第三正規化（Third Normal Form）
一個表格必須滿意第二正規化的條件，並且消除「遞移相依」現象，意即非主鍵的欄位之間沒有「完全地功能性相依」關係，才能算是達到第三正規化。

19 資料的完整性 對於關聯式資料庫來說, 還有一個重要的觀念就是資料完整性。所謂資料完整性 (Data Integrity) 是用來確保資料庫中資料的正確性及可靠性。 例如在某一個資料表中更新了一筆資料, 則所有用到此資料的地方也都要更新。 尤其在多人使用的系統中, 許多資料都是共用的, 倘若資料不正確或不一致, 那就麻煩了。

20 資料完整性的幾種類型 實體完整性 (Entity Integrity), 是為了確保資料表中的記錄是“ 唯一” 的。我們設定主鍵就是為了達成實體完整性。 例如每一本書都有一個書籍編號, 不同的書若使用相同的編號是不被允許的, 會被 Access 拒絕。

21 資料完整性的幾種類型 區域完整性 (Domain Integrity), 是為了確保資料在允許的範圍中。例如限制某一個整數值欄位的資料範圍在 100~999 之間, 若輸入的資料不在此範圍內, 即不符合區域完整性, 會被 Access 拒絕。 若輸入的資料不在此範圍內, 即不符合區域完整性, 會被 Access 拒絕：

22 資料完整性的幾種類型 參考完整性 (Referential Integrity), 是用來確保相關資料表間的資料一致, 避免因一個資料表的記錄改變, 而造成另一個資料表的內容變成無效值。

23 資料完整性的幾種類型 使用者定義的完整性 (User-defined Integrity), 顧名思義, 這是由使用者自行定義, 而又不屬於前面三種的完整性。例如某個客戶欠款超過 6 個月, 則下次再下訂單時就不賣他, 這就是由使用者定義的完整性限制。

24 資料表的關聯種類 一對一關聯 一對多關聯 多對多關聯

25 一對一關聯 當兩個資料表之間是一對一關聯時, 表示甲資料表中的一筆記錄, 只能對應到乙資料表中的一筆記錄, 而乙資料表中的一筆記錄也只能對應到甲資料表中的一筆記錄。 例如對『員工資料』來說, 我們可以將之分為 "可公開" 與 "機密" 二類, 然後分別存放在二個資料表中：

26 一對多關聯 這是最常見的一種關聯, 當兩個資料表之間是一對多關聯時, 表示甲資料表中的一筆記錄可對應到乙資料表中的多筆記錄；而乙資料表中的一筆記錄只能對應甲資料表中的一筆記錄。例如：

27 一對多關聯 在客戶資料表中每個客戶都只有一筆記錄, 但可以對應到訂單資料表中的多筆記錄, 這便是一對多的關聯。利用這種關聯, 我們可以得到以下的好處： 從客戶資料中, 可找出任一個客戶的所有訂單資料。 從訂單資料中, 可找出該訂單所屬客戶的相關資料。

28 多對多關聯 當兩個資料表之間是多對多關聯時, 表示甲資料表的一筆記錄能夠對應到乙資料表中的多筆記錄；而乙資料表中的一筆記錄也能對應到甲資料表中的多筆記錄。 例如一個客戶可訂購好幾種書, 而一本書也可賣給好幾個客戶, 若要將兩者建立關聯, 那就是多對多關聯了。

29 多對多關聯 資料庫在處理多對多關聯時, 因為彼此間的關係太複雜, 較容易發生問題, 因此通常會將這兩個資料表重新設計, 或是在這 2 個資料表之間在加上一個資料表, 使得它們之間成為 2 個一對多的關聯, 以避免發生問題。例如：

30 認識 SQL SQL 是「結構化查詢語言」（Structured Query Language）的簡稱，讀作「Ess Que Ell」或「sequel」。SQL 最初是由 IBM 的研究中心在 1970 年代初期所開發的，是專門用於關連式資料庫的一種查詢語言。利用 SQL 可以用來定義資料庫結構、建立表格、指定欄位型態與長度，也能新增、異動或查詢資料，它已經成為關聯式資料庫的標準語言。

31 SQL 的分類 資料定義語言（Data Definition Language，DDL）
可以用來建立、更改或刪除 table、schema、domain、index 與 view 。主要指令有三：CREATE、ALTER 與 DROP。 資料操作語言（Data Manipulation Language，DML） DML 係用來操作資料。主要指令有四：SELECT、INSERT、UPDATE 和 DELETE。 資料控制語言（Data Control Language，DCL） DCL 提供資料庫的安全性。主要指令有四：COMMIT、ROLLBACK、GRANT 和 REVOKE。

32 SQL語法 建立、移除與選擇資料庫 CREATE DATABASE：建立資料庫 DROP DATABASE：移除資料庫
ALTER TABLE：修改現有的資料表 DROP TABLE：移除資料表

33 SELECT 語法 讀取所有資料，但不設定條件 讀取部份欄位的資料，且指定條件 希望查詢所得的資料能依序排列的話
SELECT * FROM friend 讀取部份欄位的資料，且指定條件 SELECT realname, address FROM friend WHERE age > 20 希望查詢所得的資料能依序排列的話 SELECT first_name, address FROM friend ORDER BY city ASC (升冪排序 ) SELECT first_name, address FROM friend ORDER BY city DESC (降冪排序 )

34 SELECT 語法 使用 GROUP BY 來指定某個特定欄位，以便將查詢結區分為若干個群組，然後對這些群組進行計算。
SELECT city, COUNT(*) FROM friend GROUP BY city HAVING 的功能類似於 WHERE，可以用來規範資料被讀取的條件，但它必須與 GROUP BY 搭配，不能用來取代 WHERE。而且 HAVING 是在資料被取出之後，才再次進行篩選的動作。 SELECT group_num, AVG(score) FROM exam GROUP BY group_num HAVING AVG(score) > 60

35 SELECT 語法 使用 DISTINCT 來剔除欄位中重複的值 使用 LIMIT 來限制資料被讀取的筆數
SELECT DISTINCT city FROM friend 使用 LIMIT 來限制資料被讀取的筆數 SELECT realname FROM student ORDER BY num LIMIT 0, 5 在此例中，LIMIT 後方的 0 代表「從第 0 筆資料開始」，5 表示「取出 5 筆資料」。

36 新增與異動資料 INSERT 語法：將資料寫入資料表中 UPDATE 語法 ：用來異動資料表中現存的資料
INSERT INTO salary VALUES ( '01', '3000' ), ( '02', '2000' ) UPDATE 語法 ：用來異動資料表中現存的資料 UPDATE friend SET phone = ' ', city = 'Kaohsiung' WHERE realname = '陳信宏' DELETE 語法 ：自資料表中刪除部份的資料列 DELETE FROM friend WHERE realname = '陳信宏'