115 移民特考三等 移民行政類科資訊組 資料庫應用 考古題詳解

核心觀念

本題主要考查：

1. 實體（Entity）、屬性（Attribute）與關係（Relationship）的辨識。
2. 一對多（1:N）與多對多（M:N）基數判斷。
3. ER 圖轉換為關聯式綱要表的方法。
4. 主鍵（Primary Key）與外來鍵（Foreign Key）的設定。
5. 多對多關係必須建立連接表。
6. SQL 的 INSERT 與多表 JOIN 查詢。
7. 單位換算：$1$ 公噸 $=1000$ 公斤。

---

解題方法

先從題目中的名詞辨識實體，再判斷各實體間的關係：

• 小農：Farmer
• 農地：Land
• 作物：Crop
• 買家：Buyer

再依題意判斷基數：

• 一位小農可擁有多塊農地，但一塊農地只能屬於一位小農：Farmer 1:N Land
• 一位小農可提供多種作物，同一作物也可由多位小農提供：Farmer M:N Crop
• 一位買家可購買多種作物，同一種作物也可被多位買家購買：Buyer M:N Crop

多對多關係轉換成關聯式綱要表時，必須建立新的連接表，並以兩端實體的主鍵組成複合主鍵。

---

（一）ER 圖

1. 實體及屬性

Farmer

• FID：小農編號，主鍵
• FNAME：姓名
• FADDR：地址
• FACC：銀行帳號

Land

• LID：地號，主鍵
• LSIZE：土地大小，單位為平方公尺

Crop

• CID：作物編號，主鍵
• CPRICE：作物價格
• CNAME：作物名稱

Buyer

• BID：買家編號，主鍵
• BNAME：姓名
• BADDR：地址
• BACC：銀行帳號

2. 關係與基數

Farmer 1 ───── 擁有 ───── N Land

Farmer M ───── 提供 ───── N Crop
 │
 PQTY（提供數量）

Buyer M ───── 購買 ───── N Crop

完整表示如下：

Farmer
(FID, FNAME, FADDR, FACC)
 │
 │ 擁有，1:N
 │
Land
(LID, LSIZE)

Farmer
 │
 │ 提供，M:N
 │
Crop
(CID, CPRICE, CNAME)

提供關係屬性：PQTY

Buyer
(BID, BNAME, BADDR, BACC)
 │
 │ 購買，M:N
 │
Crop
(CID, CPRICE, CNAME)

其中：

• PQTY 是「某一位小農提供某一種作物的數量」，因此屬於 Farmer 與 Crop 之間的「提供」關係，而不是 Farmer 或 Crop 的單獨屬性。
• CPRICE 與 CNAME 是同一種作物共用的資料，因此放在 Crop 實體。
• 題目未提供購買數量、購買日期或訂單編號，因此「購買」關係不增加其他屬性。

---

（二）關聯式綱要表

1. Farmer

Farmer(FID, FNAME, FADDR, FACC)

• 主鍵：FID

---

2. Land

Land(LID, LSIZE, FID)

• 主鍵：LID
• 外來鍵：FID 參照 Farmer(FID)

因為一位小農可以擁有多塊農地，所以將一方的主鍵 FID 放入多方的 Land 表中。

---

3. Crop

Crop(CID, CPRICE, CNAME)

• 主鍵：CID

題目明確指出同種作物的價格與名稱相同，因此每一個 CID 對應固定的 CPRICE 與 CNAME。

---

4. Farmer_Crop

Farmer_Crop(FID, CID, PQTY)

• 主鍵：(FID, CID)
• 外來鍵：FID 參照 Farmer(FID)
• 外來鍵：CID 參照 Crop(CID)

Farmer 與 Crop 是多對多關係，因此建立 Farmer_Crop 連接表。PQTY 放在此表，表示特定小農提供特定作物的數量。

---

5. Buyer

Buyer(BID, BNAME, BADDR, BACC)

• 主鍵：BID

---

6. Buyer_Crop

Buyer_Crop(BID, CID)

• 主鍵：(BID, CID)
• 外來鍵：BID 參照 Buyer(BID)
• 外來鍵：CID 參照 Crop(CID)

買家與作物為多對多關係，因此建立 Buyer_Crop 連接表。

綱要總整理

Farmer(FID, FNAME, FADDR, FACC)
主鍵：FID

Land(LID, LSIZE, FID)
主鍵：LID
外來鍵：FID

Crop(CID, CPRICE, CNAME)
主鍵：CID

Farmer_Crop(FID, CID, PQTY)
主鍵：(FID, CID)
外來鍵：FID、CID

Buyer(BID, BNAME, BADDR, BACC)
主鍵：BID

Buyer_Crop(BID, CID)
主鍵：(BID, CID)
外來鍵：BID、CID

---

（三）插入資料的 SQL 指令

1. 插入小農資料

核心觀念

本題考查：

1. 第一正規化（1NF）
2. 第二正規化（2NF）
3. 第三正規化（3NF）
4. 候選鍵、主鍵與功能相依
5. 部分功能相依與遞移功能相依

依題目設定，R1 的候選鍵為複合鍵：

$K=(OID,PID)$

已知功能相依為：

$OID\rightarrow SID$

$SID\rightarrow SName$

$RID\rightarrow Comment$

$RID\rightarrow Rating$

其中：

• $OID\rightarrow SID$：訂單編號決定商家編號。
• $SID\rightarrow SName$：商家編號決定商家名稱。
• $RID\rightarrow Comment,Rating$：評價紀錄編號決定評價內容與評價等級。

---

（一）轉換為第一正規化 R1

解題方法

第一正規化要求：

• 每個欄位只能存放單一、不可再分割的值。
• 不可使用合併儲存格表達同一欄位值。
• 每一筆資料必須形成獨立的資料列。

因此，原表格中空白的延續欄位，應填入前一筆相同的訂單、商品、商家與商家名稱資料；未提供的值以 NULL 表示。

R1 表格

OID	PID	SID	Rating	RID	Comment	SName
O101	P99	S01	NULL	R801	南陽大藷	NULL
O101	P98	S01	2	R802	貨超爛	NULL
O102	P99	S02	4	R403	包裝好	臺灣番薯
O102	P99	S02	5	NULL	出貨快	臺灣番薯

因此，R1 的綱要為：

$R1(\underline{OID},\underline{PID},SID,Rating,RID,Comment,SName)$

主鍵為：

$PK(R1)=(OID,PID)$

---

（二）轉換為第二正規化

第二正規化判定

第二正規化的條件是：

1. 必須先符合第一正規化。
2. 所有非主鍵屬性都必須完全功能相依於整個主鍵。
3. 不可存在非主鍵屬性只依賴複合主鍵的一部分。

R1 的主鍵為 $(OID,PID)$。

其中：

$OID\rightarrow SID$

表示 $SID$ 只依賴複合鍵中的 $OID$，不是依賴完整的 $(OID,PID)$，因此形成部分功能相依。

此外，$SName$ 透過 $SID$ 由 $OID$ 間接決定，應與訂單商家資料分離。

R11：訂單與商家資料

$R11(\underline{OID},SID,SName)$

主鍵：

$PK(R11)=OID$

在 R11 中：

$OID\rightarrow SID$

$SID\rightarrow SName$

此表已消除 R1 中由 $OID$ 對商家資料造成的部分相依。

R12：訂單商品與評價資料

$R12(\underline{OID},\underline{PID},Rating,RID,Comment)$

主鍵：

$PK(R12)=(OID,PID)$

R12 保留訂單、商品及其評價資料。依題目給定的候選鍵設定，$OID$ 或 $PID$ 單獨不能決定 $Rating$、$RID$ 或 $Comment$，因此不存在非主鍵屬性只依賴複合鍵一部分的情形。

第二正規化結果

$R11(\underline{OID},SID,SName)$

$R12(\underline{OID},\underline{PID},Rating,RID,Comment)$

---

（三）轉換為第三正規化

第三正規化判定

核心觀念

本題考查分散式資料庫的「資料分布細節隱藏」能力。分散式資料庫雖將資料分散在不同網路節點、不同資料片段及多份副本中，但使用者仍可透過全球性資料庫模式，以近似集中式資料庫的方式查詢與更新資料。

所謂「通透性」（Transparency），是指資料庫系統隱藏底層的分散式實作細節，使使用者不必知道資料實際存放位置、資料如何切割，以及資料是否存在多份副本。

本題三種通透性分別隱藏不同層面的資訊：

通透性	隱藏的資訊
位置通透性	資料實際存放在哪一個節點
分割通透性	資料如何被切割成多個片段
複製通透性	資料是否存在多份副本，以及副本位於何處

---

解題方法

作答時可依「資料放在哪裡、資料如何切割、資料是否複製」三個問題依序說明：

1. 只要使用者不必指定資料所在的伺服器，就是位置通透性。
2. 只要使用者不必知道資料被分成哪些片段，就是分割通透性。
3. 只要使用者不必知道資料有多少份副本，也不必自行維護副本，就是複製通透性。

每一項均應說明其定義、系統處理方式、使用者所見效果及主要特性。

---

1. 位置通透性（Location Transparency）

定義

位置通透性是指使用者不必知道資料實際儲存在哪一個資料庫節點、伺服器或地理位置，也不必在查詢中指定資料所在位置。

使用者只需依全球性資料庫模式中的資料表名稱進行查詢，分散式資料庫管理系統會自動完成：

• 將資料表名稱對應至實際儲存位置。
• 將查詢送往正確的節點。
• 整合各節點回傳的結果。
• 將結果以單一資料庫的形式呈現給使用者。

例如，資料表 Employee 分散儲存在台北、高雄兩個資料中心，使用者仍可直接執行：

SELECT *
FROM Employee
WHERE Department = '資訊室';

使用者不必寫出：

SELECT *
FROM TaipeiDB.Employee;

也不必知道資料位於台北節點、高雄節點，或兩者皆有。

主要特性

1. 隱藏實體位置

使用者不必知道資料位於哪一部伺服器、哪一個資料中心或哪一個網路節點。

2. 查詢語法維持一致

使用者使用全球性資料庫模式查詢，不必因資料位置改變而修改應用程式。

3. 提高系統彈性

若資料由一個節點搬移至另一個節點，應用程式仍可使用相同的資料表名稱查詢。

4. 由系統負責位置解析

分散式資料庫管理系統透過目錄、名稱服務及查詢處理器，將邏輯資料名稱轉換為實際節點位置。

5. 隱藏通訊細節

使用者不需處理節點間的網路傳輸、遠端查詢及結果整合。

常見陷阱

位置通透性只在於「不知道資料在哪裡」，不代表使用者不知道資料是否被切割，也不代表資料一定沒有副本。資料可能同時具有分割及複製特性，但位置通透性仍只負責隱藏實際儲存位置。

---

2. 分割通透性（Fragmentation Transparency）

定義

分割通透性是指資料庫管理系統將一個邏輯資料表分割成多個資料片段後，使用者不必知道資料被如何切割，也不必直接操作各個片段。

分割可分為下列類型：

（1）水平分割（Horizontal Fragmentation）

依資料列進行切割，每個片段包含完整的欄位，但只保存部分資料列。

例如，依地區將 Employee 分割為：

• Employee_Taipei
• Employee_Kaohsiung

其重建方式通常為聯集：

$$Employee = Employee\_Taipei \cup Employee\_Kaohsiung$$

使用者仍只查詢原本的 Employee 表：

SELECT *
FROM Employee
WHERE EmployeeID = 1001;

系統會自行判斷應查詢哪些片段，並整合查詢結果。

（2）垂直分割（Vertical Fragmentation）

依資料欄位進行切割，每個片段包含部分欄位，通常必須保留主鍵，以便日後透過連接重建原資料表。

例如：

• Employee_Basic(EmployeeID, Name, Department)
• Employee_Salary(EmployeeID, Salary)

其重建方式通常為自然連接：

$$Employee = Employee\_Basic \bowtie Employee\_Salary$$

使用者不必知道姓名與薪資資料分別儲存在不同片段。

（3）混合分割（Hybrid Fragmentation）

先進行水平分割，再進行垂直分割，或依實際需求交互使用兩種分割方式。

主要特性

1. 隱藏資料切割方式

使用者只看到邏輯上的完整資料表，不必知道資料被分成幾個片段。

2. 系統負責片段定位

查詢處理器會根據查詢條件，找出相關片段，避免使用者直接查詢每個片段。

3. 系統負責資料重建

核心觀念

本題考查交易的 ACID 性質與資料庫故障回復方法，重點是：

• 原子性（Atomicity）：交易中的所有操作必須全部完成，或全部取消；未完成的交易不能只留下部分結果。
• 持續性（Durability）：交易一旦 COMMIT，即使之後發生系統故障，其結果仍必須保存。
• 交易日誌（Log）：記錄交易的更新內容，故障後依據日誌進行 UNDO 或 REDO。
• Write-Ahead Logging（WAL）：資料頁寫入磁碟前，相關日誌必須先寫入穩定儲存設備，以確保故障後能夠復原。

本題所稱的兩種方法，是：

1. 延遲更新法（Deferred Update）
2. 立即更新法（Immediate Update）

---

解題方法

判斷兩種回復方法時，先看一個核心問題：

交易在尚未 COMMIT 前，更新結果是否已經寫入資料庫？

• 尚未寫入資料庫：故障時只需捨棄尚未提交的更新。
• 已經寫入資料庫：故障時必須利用日誌將未提交的更新復原。

因此，解題時可依下列順序判斷：

1. 更新是否能在 COMMIT 前寫入資料庫？
2. 若故障發生在 COMMIT 前，是否需要 UNDO？
3. 若故障發生在 COMMIT 後，是否需要 REDO 以確保持續性？

---

一、延遲更新法（Deferred Update）

1. 基本概念

延遲更新法又稱 deferred database modification。

交易執行期間，更新內容先保留在記憶體或交易專用區域，並記錄於日誌中，但在交易完成並執行 COMMIT 前，不將更新結果寫入正式資料庫。

其流程可表示如下：

$$\text{執行交易} \rightarrow \text{暫存更新} \rightarrow \text{COMMIT} \rightarrow \text{將更新寫入資料庫}$$

2. 尚未 COMMIT 前發生故障

因為尚未 COMMIT 的資料尚未寫入正式資料庫，所以資料庫原本的內容仍然維持不變。

故障發生時：

• 不需要對正式資料庫執行 UNDO。
• 只要捨棄該交易暫存在記憶體或日誌中的更新。
• 該交易視為未完成，不能保留部分結果。

這種方式直接確保了原子性。

3. 已經 COMMIT 後發生故障

若交易已完成 COMMIT，但資料庫頁面尚未全部寫入磁碟，故障可能導致部分更新尚未反映在資料庫中。

此時需要利用日誌執行 REDO：

• 找出已經 COMMIT 的交易。
• 將其更新重新套用至資料庫。
• 確保交易結果在故障後仍然存在。

這是維持持續性的主要方式。

4. 特性整理

故障時機	處理方式
COMMIT 前	正式資料庫尚未被更新，直接捨棄暫存資料
COMMIT 後	對已提交交易執行 REDO
是否需要 UNDO	通常不需要

---

二、立即更新法（Immediate Update）

1. 基本概念

立即更新法又稱 immediate database modification。

交易執行過程中，資料庫可以在 COMMIT 前就寫入更新結果。因此，尚未提交的交易，其部分或全部結果可能已經存在於正式資料庫中。

其流程可表示如下：

$$\text{執行交易} \rightarrow \text{立即寫入資料庫} \rightarrow \text{COMMIT}$$

由於未提交交易可能已經修改資料庫，因此必須透過日誌記錄更新前後的值。例如：

$$<T_i, X, old\_value, new\_value>$$

其中：