📒 資料庫系統 CH3：關聯式資料庫

1. 關聯式資料庫(Relation Database)

1.1 定義與基本概念（3-1）

關聯式資料庫（Relation Database）是由兩個或兩個以上的資料表組合而成，資料表之間透過相同的欄位值（即「外鍵」參考「主鍵」）來連結。簡單來說，它是一種將資料分散到多個表格中，並利用表格之間的關聯來組織資料的方式。

與傳統的檔案系統不同，關聯式資料庫的目的是為了節省重複輸入的時間與儲存空間，並確保異動資料（新增、修改、刪除）時的一致性及完整性。例如，在「員工資料表」中，如果每個員工的部門資訊都重複儲存，會造成大量的資料重複。透過關聯式資料庫，我們可以將員工資訊和部門資訊分別儲存在不同的表格中，並利用「部碼」這個欄位將兩者連結，達到資料的一致性和節省空間的目的。

舉例來說，我們有一個「員工資料表」和一個「部門代碼表」，員工資料表包含員工的編號、姓名和部碼，部門代碼表包含部門的部碼和部門名稱。員工資料表的「部碼」欄位是外鍵，參考部門代碼表的主鍵，表示每個員工都屬於一個部門，而部門資訊儲存在部門代碼表中。

1.2 關聯式資料庫的優點（3-1）

• 節省記憶體空間：避免資料重複儲存，減少儲存空間需求。
• 提高行政效率：資料集中管理，方便查詢和維護。
• 達成資料的一致性：透過關聯約束，確保資料之間的關聯正確無誤。

1.3 建立關聯式資料庫的步驟（3-2、3-3）

建立關聯式資料庫通常需要以下步驟：

1. 定義資料表結構：決定每個資料表包含哪些欄位，以及每個欄位的資料類型。
2. 定義主鍵：每個資料表都需要一個主鍵，用來唯一識別記錄。例如，在「員工資料表」中，「編號」欄位可以作為主鍵。
3. 定義外鍵：如果資料表之間存在關聯，需要定義外鍵。外鍵指向另一個資料表的主鍵，建立資料表之間的連結。例如，在「員工資料表」中，「部碼」欄位是外鍵，參考「部門代碼表」的「部碼」主鍵。
4. 使用資料定義語言 (DDL) 撰寫 SQL 指令：使用 SQL 指令來建立資料表和定義它們之間的關聯。

例如，建立「ch3_DB」資料庫和兩個資料表（「員工資料表」和「部門代碼表」）的 SQL 指令如下：

CREATE DATABASE ch3_DB;

USE ch3_DB;

CREATE TABLE 部門代碼表 (
    部碼 CHAR(4) PRIMARY KEY,
    部碼 NVARCHAR(4) NOT NULL
);

CREATE TABLE 員工資料表 (
    編號 CHAR(5) PRIMARY KEY,
    姓名 NVARCHAR(4) NOT NULL,
    部碼 CHAR(4),
    FOREIGN KEY (部碼) REFERENCES 部門代碼表 (部碼)
);

（補充：SQL Server Management Studio 是一個常用的資料庫管理工具，可以方便地撰寫和執行 SQL 指令。）

1.4 範例：員工資料表與部門代碼表（3-1、3-2）

以下是一個「員工資料表」和一個「部門代碼表」的範例：

在這個範例中，「員工資料表」的「部碼」欄位是外鍵，它參考「部門代碼表」的「部碼」主鍵。這表示每個員工都屬於一個部門，而部門資訊儲存在「部門代碼表」中。

2. 鍵值屬性（一）：屬性、超鍵、主鍵

本節將介紹關聯式資料庫中鍵值屬性的概念，包括屬性、超鍵和主鍵。理解這些概念對於設計和管理資料庫至關重要。

2.1 屬性 (Attribute)（3-2-1）

屬性 (Attribute) 指的是用來描述實體的性質。換句話說，它代表了資料表中每一列的欄位，用來儲存關於實體的資訊。例如，在員工資料表中，姓名、年齡、地址等都是屬性。

舉例來說，員工資料表包含以下屬性：

簡單屬性 (Simple Attribute) 已經無法再繼續切割成其他有意義的單位。例如，「編號」屬性通常是唯一的識別碼，無法再細分。複合屬性 (Composite Attribute) 由兩個或兩個以上的其他屬性的值所組成。例如，「地址」可以拆解為「縣市」、「區」、「路」等更細的屬性。衍生屬性 (Derived Attribute) 是指可以經由某種方式的計算或推論而獲得的屬性。例如，「年齡」可以由「目前的系統時間」減去「生日」屬性的值來計算。

公式： 年齡 = 目前的系統時間 – 生日

2.2 超鍵 (Super Key)（3-2-2）

超鍵 (Super Key) 指的是在資料表中，選出兩個或兩個以上的欄位組合起來，以作為唯一識別資料的欄位。換句話說，它是一個或多個屬性的組合，可以唯一地確定資料表中的每一筆記錄。

舉例來說，在員工資料表中，如果我們只用「年齡」來識別員工，雖然可以區分不同的員工，但因為年齡可能重複，所以「年齡」本身不是超鍵。但是，如果我們將「年齡」和「地址」兩個屬性組合起來，就可以唯一地識別每位員工，那麼「年齡」和「地址」的組合就構成了一個超鍵。

最大的超鍵是所有屬性的集合。例如，在員工資料表中，包含「編號」、「姓名」、「年齡」和「地址」的所有屬性組合，就構成了一個超鍵。

在上面的例子中，最大的超鍵是包含所有屬性的集合：(編號, 姓名, 年齡, 地址)。

2.3 主鍵 (Primary Key)（3-2-3）

主鍵 (Primary Key) 是從超鍵中選出的最小的超鍵，用來唯一識別資料表中的每一筆記錄。它必須滿足以下條件：

• 唯一性：主鍵的值必須是唯一的，不能有重複的值。
• 不可為空值：主鍵的值不能是空值 (NULL)。

在資料表中，主鍵欄位名稱下方通常會加上一個底線，以表示它是主鍵。

舉例來說，在員工資料表中，「編號」通常被設定為主鍵，因為每個員工的編號都是唯一的，並且不能是空值。

挑選主鍵的三原則：

• 固定不會再變更的值：主鍵的值應該是永久且不會變動的，例如員工編號。
• 單一欄位：主鍵通常是單一欄位，而不是多個欄位的組合。
• 不可重複或空值：主鍵的值必須是唯一的，並且不能是空值。

例如，如果我們在員工資料表中，使用「身份證字號」作為主鍵，雖然每個人的身份證字號都是唯一的，但身份證字號有變更的可能性，因此不符合「固定不會再變更的值」這個原則。

2.4 補充重點

• 主鍵之鍵值「不可以重複」也「不可為空值」(Null Value)。 (第 23 頁)

3. 鍵值屬性（二）：複合鍵、候選鍵、外鍵

3.1 複合鍵(Composite Key)

複合鍵是指資料表中的主鍵，是由兩個或兩個以上的欄位以上所組成。當表格中某一欄位的值無法區分資料記錄時，可以使用複合鍵。例如，在「銷售記錄表」中，「編號」與「品號」的欄位值皆有重複，無法單獨用任一欄位來區分每筆記錄，因此需要將這兩個欄位組合在一起，作為主鍵。

舉例來說，假設我們有一個銷售記錄表，單獨使用「編號」欄位無法唯一識別每一筆記錄，因為同一個員工可能銷售多種產品。將「編號」和「品號」組合起來，就能唯一確定每一筆銷售記錄。

在這個例子中，(S0001, P0001) 和 (S0003, P0002) 等組合可以唯一識別每一筆銷售記錄。

3.2 候選鍵(Candidate Key)

候選鍵是指主鍵的候選人，並且也是關聯表的屬性子集所組成。候選鍵必須具備兩個條件：

• 具有唯一性：是指候選鍵中的值不能重複。
• 具有最小性：是指欄位個數要最少。

例如，在員工資料表中，「編號」欄位可以作為候選主鍵，因為它具有唯一性。但是，如果「身份證字號」欄位也具有唯一性，那麼「身份證字號」也是候選主鍵。在這些候選主鍵中，我們需要選擇一個作為主鍵，其他的候選主鍵則稱為替代鍵（Alternate Key）（補充）。

在這個例子中，「編號」和「身份證字號」都是候選主鍵，因為它們都具有唯一性。但是，為了簡潔起見，我們通常選擇欄位數最少的「編號」作為主鍵。

3.3 外鍵(Foreign Key)

外鍵是指「父關聯表嵌入的鍵」，並且外鍵在父關聯表中扮演「主鍵」的角色。外鍵一定會存放另一個資料表的主鍵，主要目的是用來確定資料的參考完整性。當父關聯表的「主鍵」值不存在時，則「子關聯表」的「外鍵」值也不可能存在。

例如，在「員工資料表」中，「部碼」欄位是外鍵，它參考「部門代碼表」中的「部碼」主鍵。這表示員工所屬的部門必須存在於部門代碼表中。如果部門代碼表中不存在某個部碼，那麼員工資料表中就不能有對應的部碼值。

外鍵的特性包括：

• 子關聯表的外鍵必須對應父關聯表的主鍵。
• 外鍵是用來建立子關聯表與父關聯表的連結關係。
• 外鍵的欄位值可以是重複值。
• 外鍵和「父關聯表」的主鍵欄位必須要具有相同定義域，亦即相同的資料型態和欄位長度，但名稱則可以不相同。

例如，如果部門代碼表的「部碼」欄位名稱要改為「部門代碼」，員工資料表的外鍵仍然可以參考部門代碼表中的「部門代碼」，因為重要的是值的對應關係，而不是欄位名稱。

4. 關聯式資料庫的種類

關聯式資料庫的種類主要根據資料表之間的關係來區分，常見的有一對一 (1:1)、一對多 (1:M) 和 多對多 (M:N) 關聯。了解這些關係類型，有助於我們設計更有效率且符合需求的資料庫結構。

4.1 一對一關聯(1：1)（3-3-1）

一對一關聯指的是兩個資料表中的每一筆記錄，只能對應到另一個資料表中的一筆記錄。換句話說，一個記錄在 A 表中，只能對應 B 表中的唯一一個記錄，反之亦然。這種關係較為罕見，通常發生在需要將資料分散到多個表中，但又需要保持高度關聯的情況。

為什麼使用一對一關聯？ 通常是因為為了將一個實體拆分成多個表格，例如，為了避免一個表格資料過於冗長，或者為了將一些不常變動的資訊與經常變動的資訊分開儲存。

如何運作？ 兩個資料表透過共同的主鍵欄位建立關聯。例如，員工資料表和考績資料表，員工編號在兩者中都是主鍵，表示每位員工都只有一份考績記錄，且每份考績記錄也只對應一位員工。

例子： 考績處理系統。假設我們需要記錄員工的個人資料和考績資訊。為了方便管理，我們可以將員工的個人資料放在「員工資料表」中，而將考績資訊放在「考績資料表」中。兩者之間透過「員工編號」建立一對一的關聯。

合併範例：如果需要將兩個資料表合併，可以透過「員工編號」進行匹配，將相關資訊整合到一個表中，如下所示：

4.2 一對多關聯(1：M)（3-3-2）

一對多關聯指的是一個資料表中的一筆記錄，可以對應到另一個資料表中的多筆記錄，但另一個資料表中的每一筆記錄，只能對應到第一個資料表中的一筆記錄。這種關係是最常見的關聯類型之一。

為什麼使用一對多關聯？ 通常是因為一個實體可以有多個相關的子實體。例如，一個員工可以有多筆銷售記錄。

如何運作？ 父資料表（一端）的主鍵作為子資料表（多端）的外鍵，建立關聯。子資料表的外鍵指向父資料表的主鍵，表示子資料表中的記錄與父資料表中的某筆記錄相關聯。

例子： 銷售管理系統。一個員工可以有多筆銷售記錄，但每筆銷售記錄只能由一位員工負責。因此，員工資料表是父資料表，銷售記錄表是子資料表，銷售記錄表的「員工編號」欄位是外鍵，參考員工資料表的「編號」主鍵。

SQL 範例：

-- 建立員工資料表
CREATE TABLE 員工資料表 (
    編號 CHAR(5) PRIMARY KEY,
    姓名 NVARCHAR(4) NOT NULL,
    員工部門 CHAR(4)
);

-- 建立銷售記錄表
CREATE TABLE 銷售記錄表 (
    # INT PRIMARY KEY,
    產品號碼 CHAR(4),
    數量 INT,
    員工編號 CHAR(5),
    FOREIGN KEY (員工編號) REFERENCES 員工資料表(編號)
);

-- 新增員工資料
INSERT INTO 員工資料表 (編號, 姓名, 員工部門) VALUES ('S0001', '張三', '銷售部');
INSERT INTO 員工資料表 (編號, 姓名, 員工部門) VALUES ('S0002', '李四', '銷售部');

-- 新增銷售記錄
INSERT INTO 銷售記錄表 (#, 產品號碼, 數量, 員工編號) VALUES (1, 'P0001', 4, 'S0001');
INSERT INTO 銷售記錄表 (#, 產品號碼, 數量, 員工編號) VALUES (2, 'P0002', 4, 'S0001');

4.3 多對多關聯(M：N)（3-3-3）

多對多關聯指的是一個資料表中的一筆記錄，可以對應到另一個資料表中的多筆記錄，反之亦然。這種關係比較複雜，通常需要借助第三個資料表（聯合資料表）來實現。

為什麼使用多對多關聯？ 通常是因為一個實體可以與多個其他實體相關聯，反之亦然。例如，一個員工可以銷售多種產品，而一個產品也可以被多個員工銷售。

如何運作？ 建立一個聯合資料表，該資料表包含兩個父資料表的主鍵作為外鍵。例如，在員工和產品之間建立多對多關聯，可以建立一個「銷售資料表」，該表包含「員工編號」和「產品代碼」兩個外鍵，分別參考員工資料表和產品資料表的主鍵。

例子： 產品銷售系統。一個員工可以銷售多種產品，而一個產品也可以被多個員工銷售。因此，需要建立一個銷售資料表，記錄每位員工銷售了哪些產品。

SQL 範例：

-- 建立員工資料表
CREATE TABLE 員工資料表 (
    編號 CHAR(5) PRIMARY KEY,
    姓名 NVARCHAR(4) NOT NULL,
    員工部門 CHAR(4)
);

-- 建立產品資料表
CREATE TABLE 產品資料表 (
    產品號碼 CHAR(4) PRIMARY KEY,
    產品號稱 NVARCHAR(4) NOT NULL,
    定價 INT
);

-- 建立銷售資料表 (聯合資料表)
CREATE TABLE 銷售資料表 (
    員工編號 CHAR(5),
    產品號碼 CHAR(4),
    PRIMARY KEY (員工編號, 產品號碼),
    FOREIGN KEY (員工編號) REFERENCES 員工資料表(編號),
    FOREIGN KEY (產品號碼) REFERENCES 產品資料表(產品號碼)
);

-- 新增員工資料
INSERT INTO 員工資料表 (編號, 姓名, 員工部門) VALUES ('S0001', '張三', '銷售部');

-- 新增產品資料
INSERT INTO 產品資料表 (產品號碼, 產品號稱, 定價) VALUES ('P0001', '筆電', 25000);

-- 新增銷售記錄
INSERT INTO 銷售資料表 (員工編號, 產品號碼) VALUES ('S0001', 'P0001');

4.4 補充重點

• 多對多關聯在實務上如果只有兩個資料表來建置，難度較高，並且容易出問題。（第 53 頁）
• 多對多關聯的解決方法是利用「三個資料表」來建置。（第 53 頁）

5. 關聯式資料完整性規則（一）：實體完整性規則、參考完整性規則、值域完整性規則

關聯式資料庫的完整性規則是為了確保資料的一致性與完整性，避免資料在新增、修改及刪除等運算之後，產生異常現象。簡單來說，完整性規則就像是資料庫的「守則」，用來限制使用者輸入的資料，確保資料的品質。

5.1 實體完整性規則（3-4-1）

實體完整性規則是指確保資料表中的每一筆記錄都是可區別的，也就是說，每一筆記錄都必須有唯一性。這主要透過主鍵來實現。

• 是什麼：確保資料表中的每一筆記錄都是可區別的，避免重複的記錄。
• 為什麼：重複的記錄會造成資料分析錯誤，影響決策的準確性。
• 如何運作：透過主鍵來確保唯一性，主鍵的值必須是唯一的，且不能為空值。
• 例子：在「員工資料表」中，「編號」欄位設定為主鍵，表示每位員工的編號都必須是唯一的，且不能為空值。

如果主鍵允許重複或為空值，就違反了實體完整性規則，會導致資料表中的記錄無法被唯一識別。例如，如果「員工資料表」中有多筆記錄的「編號」相同，就無法區分這些員工。

複合主鍵是指由兩個或兩個以上的欄位組合起來作為主鍵。複合主鍵的每個屬性值都不能為空值。例如，在「銷售記錄表」中，如果「編號」和「品號」組合起來作為主鍵，那麼「編號」和「品號」都不能為空值。

範例：

-- 建立銷售記錄表
CREATE TABLE 銷售記錄表 (
  編號 CHAR(5) NOT NULL,
  品號 CHAR(4) NOT NULL,
  數量 INT,
  PRIMARY KEY (編號, 品號)
);

5.2 參考完整性規則（3-4-2）

參考完整性規則是指用來確保兩個資料表之間的資料一致性。當一個資料表的外鍵參考另一個資料表的主鍵時，子關聯表的外鍵值必須存在於父關聯表的主鍵中。簡單來說，就是子資料表中的外鍵值必須對應到父資料表的主鍵值。

• 是什麼：確保子關聯表的外鍵值存在於父關聯表的主鍵中。
• 為什麼：避免子資料表中的記錄指向不存在的父資料表記錄，確保資料的一致性。
• 如何運作：透過外鍵與主鍵之間的關聯來實現。
• 例子：在「員工資料表」中，「部碼」是外鍵，參考「部門代碼表」的「部碼」主鍵。因此，「員工資料表」中的「部碼」值必須存在於「部門代碼表」的「部碼」中。

如果子關聯表的外鍵值不存在於父關聯表的主鍵中，就違反了參考完整性規則，會導致子資料表中的記錄指向不存在的父資料表記錄，造成資料不一致。

範例：

-- 建立部門代碼表
CREATE TABLE 部門代碼表 (
  部碼 CHAR(4) PRIMARY KEY,
  部碼 NVARCHAR(4) NOT NULL
);

-- 建立員工資料表
CREATE TABLE 員工資料表 (
  編號 CHAR(5) PRIMARY KEY,
  姓名 NVARCHAR(4) NOT NULL,
  部碼 CHAR(4),
  FOREIGN KEY (部碼) REFERENCES 部門代碼表(部碼)
);

-- 插入部門代碼表資料
INSERT INTO 部門代碼表 (部碼, 部碼) VALUES ('D001', '生產部');
INSERT INTO 部門代碼表 (部碼, 部碼) VALUES ('D002', '銷售部');

-- 插入員工資料表資料
INSERT INTO 員工資料表 (編號, 姓名, 部碼) VALUES ('S0001', '一心', 'D001');
INSERT INTO 員工資料表 (編號, 姓名, 部碼) VALUES ('S0002', '二聖', 'D001');
-- 嘗試插入一個不存在的部碼
-- INSERT INTO 員工資料表 (編號, 姓名, 部碼) VALUES ('S0003', '三多', 'D003'); -- 錯誤，D003 不存在於部門代碼表

5.3 值域完整性規則（3-4-3）

值域完整性規則是指確保資料表中的每個屬性都包含有效的值。每個屬性都有一個預定義的值域，資料只能從這個值域中選擇。

• 是什麼：確保資料表中的每個屬性都包含有效的值。
• 為什麼：避免資料表中的記錄包含無效的值，確保資料的準確性。
• 如何運作：透過設定資料類型、CHECK 條件等方式來實現。
• 例子：在「員工資料表」中，「年齡」欄位的值必須是正整數，且在合理的範圍內（例如 18 到 65 歲）。

值域完整性規則可以透過設定資料類型、CHECK 條件等方式來實現。例如，可以設定「年齡」欄位的資料類型為整數，並設定 CHECK 條件為 年齡 BETWEEN 18 AND 65。

範例：

-- 建立員工資料表
CREATE TABLE 員工資料表 (
  編號 CHAR(5) PRIMARY KEY,
  姓名 NVARCHAR(4) NOT NULL,
  年齡 INT CHECK (年齡 BETWEEN 18 AND 65),
  部碼 CHAR(4)
);

5.4 補充重點

• 資料庫管理師 (DBA)：負責定義資料庫的資料表結構，並設定限制條件。（第 61 頁）
• 唯一鍵 (Unique Key)：是一種限制條件，確保資料表中的每個值都是唯一的，但允許空值。（第 63 頁）
• 檢視表 (View)：是基於一個或多個資料表的虛擬資料表，不儲存資料，只定義資料的查詢方式。實體完整性規則只適用於基本關聯(Base Relation)，不考慮檢視表(View) 特性。（第 65 頁）
• 條件約束檢查 (Check)：是一種限制條件，用來定義資料的值域，確保資料的有效性。（第 63 頁）
• 不能空值 (Not Null)：是一種限制條件，表示欄位的值不能為空值。（第 63 頁）

6. 關聯式資料完整性規則（二）：空值、非空值

6.1 空值(NULL Values)（3-4-4）

空值是指資料庫中用來表示「未知」、「不適用」或「沒有資料」的特殊記號。它與空白格或零值是完全不同的概念。例如，一位員工剛入職，部門尚未確定，此時該員工的「部碼」欄位就可以設定為空值。

• 是什麼：空值是一種特殊的值，代表資料缺失或未知。
• 為什麼：在某些情況下，我們無法得知資料的確切值，例如新員工尚未分配部門。
• 如何運作：在資料庫中，空值會被視為一個獨立的值，與其他資料值區分開來。SQL 語法中，通常使用 NULL 關鍵字來表示空值。
• 例子：

  欄位名稱	資料類型	是否 Null
  總奖	char(5)	☐
  姓名	nvarchar	☐
  部碼	char(4)	☑ (代表可以為空)

  在上面的員工資料表中，如果一位新員工尚未分配部門，其「部碼」欄位可以設定為空值。

6.2 非空值(NOT NULL)（3-4-5）

非空值是指資料庫中資料欄位必須要有值的約束。如果某個欄位被定義為 NOT NULL，則在新增或更新資料時，該欄位必須提供一個有效的值，不能是空值。例如，員工的姓名是必填資訊，因此「姓名」欄位通常會被設定為 NOT NULL。

• 是什麼：NOT NULL 是一種約束，確保資料欄位必須包含有效的值。
• 為什麼：某些資料欄位對於記錄的完整性至關重要，例如姓名、編號等，不能允許其值為空。
• 如何運作：在建立資料表時，可以使用 NOT NULL 關鍵字來定義欄位。如果嘗試在 NOT NULL 欄位中插入空值，資料庫會拋出錯誤，交易會中止。
• 例子：

  運行行名稱	資料類型	允許 Null
  編號	char(5)	□
  姓名	nvarchar(4)	□
  部碼	char(4)	☒

  在上面的員工資料表中，「部碼」欄位被設定為 NOT NULL，表示每個員工都必須有部門資訊。

總結來說，空值和非空值是資料庫完整性管理中兩個重要的概念。合理使用空值可以處理資料缺失的情況，而設定 NOT NULL 則可以確保關鍵資料欄位的完整性。

6.3 補充重點

• 資料行必須有正確的資料值，不可為虛值（第 77 頁）。
• 在「員工資料表」中的「姓名」欄位值必須確定，不可為虛值，因此在建立資料表時就必須宣告為NOT NULL（第 77 頁）。