107 一般警察特考三等 警察資訊管理人員 資料庫應用 考古題詳解

核心觀念

本題評量關聯式資料庫系統的核心分析與設計能力，涵蓋兩個核心主題：

1. 概念資料模型與實體關聯圖（Entity-Relationship Diagram, ERD）：考驗考生能否依據題目給定的關聯結構（Relational Schema），逆向反推實體（Entity）、關聯（Relationship）、基數比（Cardinality Ratio），並正確標示主鍵（Primary Key, PK）與外來鍵（Foreign Key, FK）。國家考試標準評分以「陳氏記號法（Chen's Notation）」為主流規範。
2. 關聯式模型完整性限制（Integrity Constraints）：考驗科德（E. F. Codd）所奠定的關聯式模型兩大基石——「鍵值／實體完整性限制（Key / Entity Integrity Constraint）」與「參考完整性限制（Referential Integrity Constraint）」的定義、運作機制、空值（$\text{NULL}$）規則及維護資料一致性之策略。

---

解題方法

（一）實體關聯圖（ERD）繪製與鍵值判定

1. 實體與主鍵判定：

• 題目明確給出四個資料表，對應四個實體類型：
• 顧客：以「顧客編號」作為主鍵（PK），能唯一識別一位顧客。
• 供應商：以「廠商編號」作為主鍵（PK），能唯一識別一家供應商。
• 產品：以「產品編號」作為主鍵（PK），能唯一識別一項產品。
• 訂單：以「訂單編號」作為主鍵（PK），能唯一識別一張訂單。

2. 外來鍵與關聯基數判定：

• 產品與供應商：「產品」資料表內含「廠商編號」，其值參照自「供應商」之「廠商編號」。因此「產品.廠商編號」為外來鍵（FK）。一家供應商可供應多項產品，一項產品由一家供應商提供，故兩者為 $1:N$ 之「供應（Supply）」關聯。
• 訂單與顧客：「訂單」資料表內含「顧客編號」，其值參照自「顧客」之「顧客編號」。因此「訂單.顧客編號」為外來鍵（FK）。一位顧客可下達多筆訂單，一筆訂單僅屬於一位顧客，故兩者為 $1:N$ 之「下單（Place）」關聯。
• 訂單與產品：「訂單」資料表內含「產品編號」與「數量」，其值參照自「產品」之「產品編號」。因此「訂單.產品編號」為外來鍵（FK）。在此綱要設計下，一筆訂單對應單一產品品項，一項產品可出現在多筆訂單中，故兩者為 $1:N$ 之「訂購（Contain）」關聯。

3. 符號繪製規範（陳氏記號法）：

• 實體（Entity）：矩形。
• 關聯（Relationship）：菱形。
• 屬性（Attribute）：橢圓形。
• 主鍵（Primary Key）：屬性文字下方加實線底線。
• 外來鍵（Foreign Key）：屬性文字下方加虛線底線（或於綱要標記 FK）。

（二）鍵值完整性限制與參考完整性限制特性分析

1. 鍵值完整性限制（Key Integrity / Entity Integrity）：

• 探討候選鍵（Candidate Key）與主鍵（Primary Key）之唯一性限制（Uniqueness）以及不可為空值（Not $\text{NULL}$）之特性。
• 闡述其存在目的：保證資料庫內每一筆元組（Tuple）具備客觀識別性，對應現實世界的獨立實體。

2. 參考完整性限制（Referential Integrity）：

• 探討外來鍵（Foreign Key）與被參照主鍵（Referenced Primary Key）之間的參照關係。
• 闡述其取值規則：外來鍵的值必須「等於被參照資料表之主鍵值」或「為空值（$\text{NULL}$）」。
• 闡述違規防護機制：當被參照表發生刪除（$\text{ON DELETE}$）或更新（$\text{ON UPDATE}$）時之應變動作（如 $\text{CASCADE}$、$\text{RESTRICT / NO ACTION}$、$\text{SET NULL}$、$\text{SET DEFAULT}$）。

---

解題技巧

1. 忠於題意給定的綱要，切勿過度正規化： 現實系統中，「訂單」通常會拆分為「訂單主檔」與「訂單明細」，但本題題目已明文規定「訂單（訂單編號、訂單日期、數量、付款方式、備註、顧客編號、產品編號）」。作答時必須嚴格按照題目給予的綱要繪製，不可自行增刪欄位或自行拆表，否則會因偏離題目要求而失分。
2. 國考 ERD 作圖拿分要訣：

• 實體與關聯之間的連線必須明確標示基數比（$1$ 與 $N$）。
• 屬性必須全部畫出，且主鍵實線底線不可遺漏。
• 建議在圖形下方補充「關聯綱要（Relational Schema）標記」，將 PK 劃實線、FK 劃虛線並加上箭頭指向參照來源，讓閱卷老師一眼看出架構邏輯。

3. 完整性限制答題架構： 論述完整性限制時，建議從「定義」、「規則條件」、「存在目的」、「違規處理動作」四個構面作答，層次分明，可確保取得完整分數。

---

【答案】

（一）實體關聯圖（ERD）與鍵值標示

1. 資料庫綱要與鍵值定義

• 顧客（<u>顧客編號</u>、姓名、住址、性別、手機號碼）
• 主鍵（PK）：顧客編號
• 供應商（<u>廠商編號</u>、廠商名稱、廠商住址、廠商電話）
• 主鍵（PK）：廠商編號
• 產品（<u>產品編號</u>、產品名稱、產品類別、顏色、單價、庫存量、<ins style="text-decoration: dashed underline;">廠商編號</ins>）
• 主鍵（PK）：產品編號
• 外來鍵（FK）：廠商編號（參照至「供應商」之廠商編號）

核心觀念

本題評量關聯式資料庫的資料庫設計與正規化理論（Normalization Theory），主要核心在於辨識函數相依（Functional Dependency, FD），並依序檢驗第一至第三正規化（1NF、2NF、3NF）之規範：

1. 第一正規化（1NF）：資料表中所有屬性（Attributes）之值皆必須為單一值（Atomic value），不允許具有重複群集（Repeating Groups）。若一筆訂單可購買多種玩具，則玩具項目不可作為複合集合欄位儲存於單一列中。
2. 第二正規化（2NF）：資料表必須符合 1NF，且所有非主鍵屬性必須完全函數相依（Fully Functionally Dependent）​於主鍵，亦即消除部分函數相依（Partial Functional Dependency）。當主鍵為複合主鍵（Composite Primary Key）時，非主鍵屬性不可僅相依於主鍵的一部分。
3. 第三正規化（3NF）：資料表必須符合 2NF，且所有非主鍵屬性皆不遞移相依（Transitive Dependency）​於主鍵。若存在函數相依 $X \rightarrow Y$ 且 $Y \rightarrow Z$（其中 $X$ 為主鍵，$Y$ 與 $Z$ 為非主鍵屬性，且 $Y$ 不能決定 $X$），則 $X \rightarrow Z$ 即為遞移相依，必須將 $Y$ 與 $Z$ 分離為獨立的資料表。

---

解題方法

本題解題步驟分為三個階段：實體與函數相依分析、正規化違規檢視與拆解、產出符合 3NF 的資料表綱要。

步驟一：分析各原始需求之函數相依（FD）

1. 顧客：

• 包含屬性：{顧客編號, 顧客姓名, 顧客住址, 手機號碼}
• 函數相依： $\text{顧客編號} \rightarrow \{\text{顧客姓名}, \text{顧客住址}, \text{手機號碼}\}$
• 分析：主鍵為 顧客編號（單一主鍵）。不存在部分函數相依；非主鍵屬性之間亦無相依性，故已符合 1NF、2NF 與 3NF。

2. 玩具商品：

• 包含屬性：{玩具編號, 玩具名稱, 類別編號, 類別名稱, 玩具單價}
• 函數相依： $\text{玩具編號} \rightarrow \{\text{玩具名稱}, \text{玩具單價}, \text{類別編號}\}$ $\text{類別編號} \rightarrow \{\text{類別名稱}\}$
• 分析：
• 主鍵為 玩具編號。
• 存在遞移函數相依：$\text{玩具編號} \rightarrow \text{類別編號} \rightarrow \text{類別名稱}$。
• 此相依違反 3NF，必須拆分為「商品類別」與「玩具商品」兩個關聯表。

3. 訂單：

• 包含屬性：{訂單編號, 訂單日期, 玩具編號, 玩具名稱, 數量, 總金額, 備註, 顧客編號, 顧客名稱}
• 業務邏輯中，一張訂單可訂購多種玩具商品（一對多），若維持扁平資料結構，其候選鍵（Candidate Key）/ 複合主鍵為： $(\text{訂單編號}, \text{玩具編號})$
• 函數相依檢視：
• 完全相依於複合主鍵： $(\text{訂單編號}, \text{玩具編號}) \rightarrow \{\text{數量}\}$
• 部分相依於 訂單編號： $\text{訂單編號} \rightarrow \{\text{訂單日期}, \text{總金額}, \text{備註}, \text{顧客編號}, \text{顧客名稱}\}$
• 部分相依於 玩具編號： $\text{玩具編號} \rightarrow \{\text{玩具名稱}\}$
• 遞移相依：

核心觀念

本題為典型的關聯式資料庫（Relational Database）綜合應用試題，涵蓋了 DQL（資料查詢語言）、DML（資料操作語言）以及高階程序化 SQL（Procedural SQL）核心架構的比較，主要評量以下三大核心觀念：

1. 分群彙總與排序（Aggregation, Grouping, and Sorting）：

• 使用 GROUP BY 子句將資料依指定欄位（屬性）劃分群組。
• 運用聚合函數 $COUNT$ 統計群組內記錄筆數。
• 使用 ORDER BY 子句進行排序（由小到大為遞增排序 ASC）。

2. 多資料表關聯更新（Multi-Table Conditional Update）：

• 目標更新資料表為「訂單」，但篩選條件「姓名」位於「顧客」資料表。
• 須運用外部鍵（Foreign Key）關聯，透過巢狀子查詢（Subquery）​或資料表連接（JOIN）​完成跨表參照更新。為符合 ANSI SQL 標準並兼顧所有資料庫管理系統（DBMS）之相容性，以 WHERE ... IN (SELECT ...) 最為嚴謹且不易扣分。

3. 集合導向與逐列處理機制（Set-based vs. Row-by-row / Cursor-based）：

• SELECT：關聯式代數（Relational Algebra）的本質體現，屬於宣告式（Declarative）語法，採集合導向（Set-based）處理整個結果集（Result Set），由查詢最佳化器（Query Optimizer）負責執行路徑。
• CURSOR（游標）：程序式程式設計（Procedural Programming）的延伸，屬於命令式（Imperative）概念，採逐列導向（Row-by-row / RBAR: Row By Agonizing Row）處理機制，具有完整生命週期（宣告、開啟、提取、關閉、釋放）。

---

解題方法

（一）水果商品購買數量之分群與排序

• 切入點：檢視「訂單」資料表綱要：訂單（訂單編號、日期、金額、付款方式、商品編號、顧客編號）。綱要中並無獨立的「數量」欄位，且題幹明述「每筆訂單只有一位下訂單的客戶」，代表每筆訂單記錄即對應一項特定商品的訂購行為。因此，「購買數量」即為該商品在「訂單」資料表中出現的訂單筆數。
• 步驟推導：

1. 選取欄位：商品編號 與計數結果 COUNT(*) AS 購買數量。
2. 資料來源：訂單 資料表。
3. 分群依據：GROUP BY 商品編號。
4. 排序規則：按商品編號由小到大排序，即 ORDER BY 商品編號 ASC（ASC 可省略，顯式寫出更精確）。 (註：若欲嚴謹納入「從未被訂購、銷售量為 0」之水果，亦可使用 水果 LEFT JOIN 訂單 並以 COUNT(訂單.訂單編號) 統計，兩種解法皆合乎關聯邏輯。)

（二）依顧客姓名條件修改訂單付款方式

• 切入點：欲修改的欄位為「訂單.付款方式」，但限制條件是「顧客.姓名 = '陳小宇'」。
• 步驟推導：

1. 更新主體：UPDATE 訂單。
2. 設定數值：SET 付款方式 = '貨到付款'。
3. 關聯篩選：顧客與訂單係透過 顧客編號 建立外來鍵關聯。由於同名同姓在實務上可能存在多筆，使用子查詢 WHERE 顧客編號 IN (SELECT 顧客編號 FROM 顧客 WHERE 姓名 = '陳小宇') 最為穩健。

（三）CURSOR 與 SELECT 之使用差異比較

• 切入點：本小題為觀念比較題，應從「處理導向與原理」、「語法結構與生命週期」、「效能與資源鎖定」、「適用開發情境」四個維度進行結構化對比。
• 論述架構：

1. 處理模型：集合導向（Set-based）對比逐筆處理（Procedural / Row-by-row）。

核心觀念

本題為典型的公職資料庫考試題型，涵蓋「分散式資料庫架構」與「交易並行處理之狀態追蹤」兩大核心：

1. 分散式資料庫管理系統（Distributed Database Management System, DDBMS）特性： 考查分散式架構的核心精神——「實體資料分散儲存、邏輯概念統整為一」。包含節點自主性（Local Autonomy）、分散透明性（Distribution Transparency：位置透明、分割透明、複製透明等）、高可用性（Availability）與容錯能力（Fault Tolerance）。
2. 交易（Transaction）私有工作區與資料庫狀態追蹤： 交易執行時，CPU 會在記憶體中為該交易配置私有工作區（Local Workspace / Private Buffer）。

• $\text{Read}(X)$：將資料庫（DB）中 $X$ 的值複製一份載入該交易的本機私有變數。
• 運算賦值（如 $F = E - G$）：僅在交易私有變數內部進行計算與修改，不影響資料庫實體數值。
• $\text{Write}(X)$：將交易私有變數中 $X$ 的最新值寫回資料庫，此時資料庫數值才會更新。
• 當排程交錯執行時，若無並行控制機制，極易引發「遺失更新」（Lost Update）等不一致現象。

---

解題方法

（一）分散式資料庫管理系統之特性切入點

從「資料實體與邏輯架構」、「各分部節點之獨立運作權」、「對使用者呈現之透明度」及「系統整體的可靠度與擴充性」四個層面條列剖析，緊扣跨國企業分散布局與總部集中整合分析的情境。

（二）交易與 DB 數值追蹤推導步驟

給定初始狀態：$\text{DB}(E=16, F=5, G=3)$。

1. $t_0$ 時刻：

• $P_1$ 執行 $\text{Read}(F)$：從 DB 讀入 $F=5$ 至 $P_1$ 私有變數。
• $P_1$ 狀態：$F=5$；$P_2$ 尚未啟動；DB 維持 $E=16, F=5, G=3$。

2. $t_1$ 時刻：

• $P_1$ 執行運算 $F = E - G$：取用環境初始值 $E=16, G=3$ 進行運算，$F = 16 - 3 = 13$。
• $P_1$ 狀態：$F=13$；$P_2$ 無動作；DB 未被寫入，維持 $E=16, F=5, G=3$。

3. $t_2$ 時刻：

• 題目說明「按照時間先後順序輪流進入 CPU 執行」，故同一時槽內左側之 $P_1$ 先執行，右側之 $P_2$ 後執行。
• $P_1$ 執行 $\text{Write}(F)$：將 $P_1$ 的 $F=13$ 寫回 DB，DB 之 $F$ 更新為 $13$。
• 隨後 $P_2$ 執行 $\text{Read}(F)$：從已更新的 DB 讀取 $F=13$ 至 $P_2$ 私有變數。
• $P_1$ 狀態：$F=13$；$P_2$ 狀態：$F=13$；DB 狀態：$E=16, F=13, G=3$。

4. $t_3$ 時刻：

• $P_2$ 執行運算 $F = E + G$：取用環境數值 $E=16, G=3$ 進行運算，$F = 16 + 3 = 19$。
• $P_1$ 狀態：$F=13$；$P_2$ 狀態：$F=19$；DB 未被寫入，維持 $E=16, F=13, G=3$。

5. $t_4$ 時刻：

• $P_1$ 執行 $\text{Read}(E)$：從 DB 讀入 $E=16$ 至 $P_1$ 私有變數。
• $P_1$ 狀態：$E=16, F=13$；$P_2$ 狀態：$F=19$；DB 維持 $E=16, F=13, G=3$。

6. $t_5$ 時刻：

• $P_2$ 執行 $\text{Write}(F)$：將 $P_2$ 的 $F=19$ 寫回 DB，DB 之 $F$ 更新為 $19$。
• $P_1$ 狀態：$E=16, F=13$；$P_2$ 狀態：$F=19$；DB 狀態：$E=16, F=19, G=3$。

7. $t_6$ 時刻：

• $P_1$ 執行 $\text{Read}(G)$：從 DB 讀入 $G=3$ 至 $P_1$ 私有變數。
• $P_1$ 狀態：$E=16, F=13, G=3$；$P_2$ 狀態：$F=19$；DB 維持 $E=16, F=19, G=3$。

8. $t_7$ 與 $t_8$ 時刻：

• 兩交易皆無後續操作，各變數與 DB 數值均保持 $t_6$ 結束時的最終狀態。

---

選項分析（申論與追蹤細節剖析）

本題為非選擇題，針對答題得分關鍵剖析如下：

1. 第一小題得分結構評析：

• 分散式資料庫的問答題，切忌只泛稱「資料分散在很多主機」。閱卷委員的給分標準建立在「透明性（Transparency）」與「節點自主性（Local Autonomy）」等專有名詞上。必須明確區分出位置透明、分割透明與複製透明，才能拿滿 10 分。

2. 第二小題狀態追蹤細節評析：

• DB 值何時變動：整個流程中，只有在 $t_2$（$P_1$ 執行 $\text{Write}(F)$）與 $t_5$（$P_2$ 執行 $\text{Write}(F)$）這兩個時間點，DB 的數值才會發生變化。其餘純讀取或本機運算時間點，DB 值一概不變。
• $t_2$ 的並行讀寫順序：表格在 $t_2$ 同時列出 $P_1$ 的 $\text{Write}(F)$ 與 $P_2$ 的 $\text{Read}(F)$。按分時輪流進入 CPU 的規範，依序先寫後讀，故 $P_2$ 讀入的是新值 $13$；