107 地方特考三等 資訊處理 資料庫應用 考古題詳解

核心觀念

1. 資料庫設計生命週期（Phases of Database Design）： 資料庫設計是將現實世界的資訊需求，轉化為特定資料庫管理系統（DBMS）可有效儲存、查詢與管理之結構的系統化過程。經典架構（如 Elmasri & Navathe, Silberschatz 等教科書）通常劃分為六個主要階段：需求收集與分析、概念資料庫設計、邏輯資料庫設計、實體資料庫設計、資料庫建置與實作、資料庫運作與維護。針對 AI 與大量資料（Big Data）情境，各階段更需著重於資料特徵（特徵向量、標註資料、訓練／驗證集劃分）、擴展性（Scalability）與高吞吐量（High Throughput）之需求。
2. 關聯式綱要設計非正式指引（Informal Design Guidelines for Relation Schemas）： 在關聯式模型中，衡量關聯綱要（Relation Schema）設計「好壞」的標準，直接源於關聯式資料庫經典設計理論（Elmasri & Navathe 所提之四大設計準則）。其核心目標為：屬性語意單純、避免資料重複引發的更新異常、極小化空值（NULL values），以及確保自然連接時不產生虛假元組（Spurious Tuples）。

解題方法

• 第 (一) 小題： 採取「階層式推導與系統化說明」。先列出完整的資料庫設計六大階段，說明各階段的「輸入」、「核心工作」與「產出成果（Deliverables）」，並融入題目所指的「AI 巨量訓練與測試資料」之實務特性（例如：特徵多樣性、標註品質、循序或批次讀取效能），展現資深考生的實務與理論整合能力。
• 第 (二) 小題： 本題考點為關聯式資料庫理論中標準的「Four Informal Design Guidelines for Relation Schemas」。不可憑空發揮，應精準依據規範答出四大原則：

1. 屬性語意明確性（Semantics of Attributes）
2. 減少重複資訊以避免更新異常（Reducing Redundant Values and Anomalies）
3. 減少元組中的空值（Reducing NULL Values in Tuples）
4. 禁止產生虛假元組（Disallowing Spurious Tuples） 並逐一扼要說明其違反時的後果（如各類異常、儲存浪費、連接錯誤）。

解題技巧

1. 第 (一) 題口訣與層次： 記憶口訣：「需・概・邏・實・建・維」（需求收集 $\to$ 概念設計 $\to$ 邏輯設計 $\to$ 實體設計 $\to$ 建置實作 $\to$ 運作維護）。寫申論題時，每一階段務必標出「主要產出（如 ER Model、Relational Schema、DDL Script）」，閱卷老師通常採關鍵字評分。
2. 第 (二) 題防陷阱提示： 許多考生會將此題誤答為「ACID 特性」、「正規化階段（1NF/2NF/3NF/BCNF）」或「安全性／完整性限制」。題目問的是「好的關聯式綱要設計原則（Guidelines）」，正是正規化理論的前置理論——Elmasri 聖經本第 14 章開宗明義的四大 Guideline，寫出標準英文術語可大幅提升評分。

【答案】

(一) 資料庫設計的階段（Phases of Database Design）

建立儲存大量 AI 訓練與測試資料之資料庫，標準設計流程分為以下六個階段：

1. 需求收集與分析階段（Requirements Collection and Analysis）

• 說明：深入訪談領域專家（如資料科學家、AI 工程師），分析資料來源、資料格式（文字、數值、影像特徵、標籤）、資料量規模（Volume）及資料更新頻率。釐清 AI 訓練所需的特徵萃取邏輯、資料集切分比例（Train/Validation/Test Split）與查詢模式。
• 主要產出：軟體需求規格書（SRS）與資料需求清單（Data Requirements Specification）。

2. 概念資料庫設計階段（Conceptual Database Design）

• 說明：完全獨立於具體的 DBMS 軟體與硬體限制，使用高階語意資料模型（Semantic Data Model）將真實世界實體抽象化。定義資料實體（如模型、資料集、特徵向量、標註結果）、實體屬性及其相互間之基數比（Cardinality Ratio，如 1:1, 1:N, M:N）。
• 主要產出：實體關係圖（Entity-Relationship Diagram, ERD）或延伸實體關係圖（EERD）。

3. 邏輯資料庫設計階段（Logical Database Design / Data Model Mapping）

• 說明：將概念設計階段的 ER 圖，對應轉換至目標資料庫模型（如關聯式模型 Relational Model）。

核心觀念

本題評量將「概念資料模型」（Entity-Relationship Model, ER 模型）轉換為「邏輯資料模型」（關聯式資料庫綱要，Relational Database Schema）的標準規則（Elmasri & Navathe 模型轉換法則），以及「完整性限制條件」（Integrity Constraints）在關聯式模型中的實作機制：

1. 正規實體型態（Strong/Regular Entity Types）的轉換：各實體轉換為獨立關聯表，實體之識別屬性（Identifier）轉換為關聯表之主鍵（Primary Key, PK）。
2. 多值屬性（Multivalued Attributes）的轉換：為滿足第一正規化（1NF）的原子性要求，多值屬性不可直接併入原實體關聯表，必須獨立建表，以「原實體主鍵 + 該多值屬性」組成複合主鍵（Composite PK），並以外鍵參照原實體。
3. 二元 $1:N$ 關係型態的轉換（外鍵法）：將「$1$ 端」實體的主鍵，置入「$N$ 端」實體轉換後的關聯表中作為外鍵（Foreign Key, FK）。
4. 二元 $M:N$ 關係型態的轉換（關聯表法）：必須建立獨立的關聯表（Relationship Relation），納入兩端實體的主鍵作為外鍵，並以兩端外鍵之組合形成複合主鍵；若該關係附帶關係屬性（Relationship Attributes），一併置入此關聯表中。
5. 參與限制（Participation Constraints）的關聯式對應：

• 選擇性參與（Optional / Partial Participation）：個體不一定要參與該關係。
• 必須參與（Mandatory / Total Participation）：個體必須至少參與一次該關係（存在依存）。
• 在關聯式模型中，主要透過欄位的 NULL / NOT NULL 網域限制、複合主鍵定義、外鍵參照完整性（Referential Integrity），以及跨表格觸發程序（Triggers）或斷言（Assertions）來實現。

解題方法

(一) 轉換包含多值屬性與 $1:N$ 關係之 ER 圖

1. 實體轉換：

• 實體 EMPLOYEE 具有單值屬性 ssn、ename，其識別者為 ssn。
• 實體 DEPARTMENT 具有單值屬性 dnum、dname，其識別者為 dnum。多值屬性 locations 另行處理。

2. 處理 $1:N$ 關係「works-for」：

• 一個部門有許多員工（$N$ 端），一個員工只能屬於某一部門（$1$ 端）。
• 依照 $1:N$ 轉換法則，將 $1$ 端 DEPARTMENT 的主鍵 dnum 置入 $N$ 端 EMPLOYEE 表中，作為外鍵（FK），命名為 dnum（或 dno）。
• 此時 EMPLOYEE 關聯綱要為： $\text{EMPLOYEE}(\underline{\text{ssn}}, \text{ename}, \text{dnum})$
• 主鍵（PK）：ssn
• 外鍵（FK）：dnum 參照 DEPARTMENT(dnum)
• DEPARTMENT 關聯綱要（單值屬性部分）為： $\text{DEPARTMENT}(\underline{\text{dnum}}, \text{dname})$
• 主鍵（PK）：dnum

3. 處理多值屬性 locations：

• 為符合第一正規化（1NF），建立獨立關聯表 DEPT_LOCATIONS。
• 屬性包含所屬部門主鍵 dnum 與地點屬性 location（或 dlocation）。
• 複合主鍵為 $\{\text{dnum}, \text{location}\}$，外鍵 dnum 參照 DEPARTMENT(dnum)。
• 關聯綱要為： $\text{DEPT\_LOCATIONS}(\underline{\text{dnum}, \text{location}})$

(二) 轉換包含關係屬性之 $M:N$ 關係 ER 圖

1. 實體轉換：

• 實體 EMPLOYEE 轉換為關聯表： $\text{EMPLOYEE}(\underline{\text{ssn}}, \text{ename})$
• 主鍵（PK）：ssn
• 實體 PROJECT 轉換為關聯表： $\text{PROJECT}(\underline{\text{pnum}}, \text{pname})$
• 主鍵（PK）：pnum

2. 處理 $M:N$ 關係「works-on」：

• 員工與計畫為多對多關係，且含有關係屬性 hours，無法直接在任一實體表中置入外鍵，必須獨立建構關聯表，命名為 WORKS_ON。
• 關聯表中包含：參與兩端實體的主鍵 ssn 與 pnum，以及關係屬性 hours。
• 複合主鍵（Composite PK）由兩端外鍵共同組成：$\{\text{ssn}, \text{pnum}\}$。
• 關聯綱要為： $\text{WORKS\_ON}(\underline{\text{ssn}, \text{pnum}}, \text{hours})$
• 主鍵（PK）：$\{\text{ssn}, \text{pnum}\}$
• 外鍵（FK 1）：ssn 參照 EMPLOYEE(ssn)
• 外鍵（FK 2）：pnum 參照 PROJECT(pnum)

(三) 參與限制（Optional 與 Mandatory）在關聯式資料庫模型的表示方式

在關聯式資料庫模型中，依關係實作方式的不同，表示選擇性（Optional）與必須（Mandatory）限制的機制如下：

1. 以「外鍵法」實作之關係（如 $1:N$ 或 $1:1$ 關係）：

• 選擇性限制（Optional Participation / 部分參與）：
• 表示方式：在含有外鍵的關聯表中，將該外鍵欄位設定為允許為空值（NULL）。

核心觀念

本題評量關聯式資料庫中 SQL 查詢的執行邏輯與子句處理順序，核心考點包含：

1. SQL 語句邏輯執行順序（Logical Query Processing Phase）： $\text{FROM} \;\to\; \text{WHERE} \;\to\; \text{GROUP BY} \;\to\; \text{HAVING} \;\to\; \text{SELECT}$
2. WHERE 與 HAVING 的過濾層級差異：

• WHERE 子句作用於個別記錄（Row-level），在資料分組（GROUP BY）前進行過濾；不可直接在 WHERE 中使用聚合函數（Aggregate Functions）。
• HAVING 子句作用於群組（Group-level），在資料分組後對各群組的聚合計算結果進行過濾。

3. 獨立子查詢（Non-correlated Subquery）與 IN 述詞：

• 子查詢先行獨立運算並回傳一集合，外部查詢以該集合為條件進行列層級過濾。

4. 聚合計算母體範圍（Population of Aggregation）：

• 同樣是 COUNT(*)，會因為置於不同子查詢或不同過濾階段，而計算完全不同的資料母體（例如「高薪員工人數」vs.「部門全體員工人數」）。

解題方法

解題切入點在於建立兩張資料表的對照分布，先統計各部門的「總員工人數」與「薪資大於 25,000 元之人數」：

• 各部門統計彙整表：

• 部門 $1$：成員包含 $\text{ssn } 8, 9, 10$（薪水分別為 $55000, 45000, 30000$）。
• 總人數 $= 3$
• 薪水 $> 25000$ 人數 $= 3$
• 部門 $2$：成員包含 $\text{ssn } 11, 12$（薪水分別為 $50000, 25000$）。
• 總人數 $= 2$
• 薪水 $> 25000$ 人數 $= 1$
• 部門 $3$：成員包含 $\text{ssn } 13$（薪水為 $30000$）。
• 總人數 $= 1$
• 薪水 $> 25000$ 人數 $= 1$
• 部門 $4$：成員包含 $\text{ssn } 5, 6, 7$（薪水分別為 $25000, 43000, 25000$）。
• 總人數 $= 3$
• 薪水 $> 25000$ 人數 $= 1$
• 部門 $5$：成員包含 $\text{ssn } 1, 2, 3, 4$（薪水分別為 $30000, 40000, 38000, 25000$）。
• 總人數 $= 4$
• 薪水 $> 25000$ 人數 $= 3$

子題（1）逐步推導

SELECT dno, COUNT(*)
FROM DEPARTMENT, EMPLOYEE
WHERE dnum = dno AND salary > 25000
GROUP BY dno
HAVING COUNT(*) > 2

1. FROM 與 WHERE：

• 執行內部關聯 DEPARTMENT.dnum = EMPLOYEE.dno。
• 篩選 salary > 25000（注意：等於 $25000$ 者不符合條件，$\text{ssn } 4, 5, 7, 12$ 均被剔除）。
• 保留的記錄為：$\text{ssn } 1, 2, 3, 6, 8, 9, 10, 11, 13$。

2. GROUP BY dno：

• 將篩選後的記錄依部門編號分組，計算各群組的筆數 $\text{COUNT}(*)$：
• dno = 1：計數 $= 3$（$\text{ssn } 8, 9, 10$）
• dno = 2：計數 $= 1$（$\text{ssn } 11$）
• dno = 3：計數 $= 1$（$\text{ssn } 13$）
• dno = 4：計數 $= 1$（$\text{ssn } 6$）
• dno = 5：計數 $= 3$（$\text{ssn } 1, 2, 3$）

3. HAVING COUNT(*) > 2：

• 對上述分組後的計數進行過濾，僅保留計數大於 $2$ 的群組：
• dno = 1（計數 $3 > 2$，符合）
• dno = 5（計數 $3 > 2$，符合）

4. 意義： 查詢​「薪水大於 25,000 元之員工人數超過 2 人」​的部門編號（dno），以及該部門內​「薪水大於 25,000 元的員工人數」。

子題（2）逐步推導

SELECT dno, COUNT(*)
FROM DEPARTMENT, EMPLOYEE
WHERE dnum = dno AND salary > 25000 AND
 dno IN (SELECT dno
 FROM EMPLOYEE
 GROUP BY dno
 HAVING COUNT(*) > 2)
GROUP BY dno

核心觀念

本題評量關聯式資料庫正規化（Normalization）的核心理論，包含候選鍵求解、屬性分類，以及第二正規化（2NF）與第三正規化（3NF）的定義與無失真分解（Lossless-join Decomposition）。

1. 候選鍵（Candidate Key, CK）： 若屬性集合 $K$ 能決定關聯綱要中的所有屬性（即閉包 $K^+ = R$），且其任何真子集皆無法決定所有屬性（具備極小性 Minimal），則 $K$ 為候選鍵。
2. 主屬性與非主屬性：

• 主屬性（Prime Attribute）：包含於任一候選鍵中的屬性。
• 非主屬性（Non-prime Attribute）：不包含於任何候選鍵中的屬性。

3. 第二正規化（2NF）：

• 關聯綱要符合 1NF。
• 每一個非主屬性皆完全功能依附（Fully Functionally Dependent）於每一個候選鍵；亦即不可存在非主屬性對候選鍵的「部分功能依附」（Partial Functional Dependency）。

4. 第三正規化（3NF）：

• 關聯綱要符合 2NF。
• 消除非主屬性對候選鍵的「遞移功能依附」（Transitive Functional Dependency）。
• 形式化定義：對於關聯中每一個非平庸的功能依附 $X \rightarrow Y$，必須滿足下列條件之一：

1. $X$ 為超鍵（Superkey）。
2. $Y$ 中的每一個屬性皆為主屬性（Prime Attribute）。

解題方法

步驟一：求關聯綱要 $R$ 的所有候選鍵與屬性分類

給定 $R(A, B, C, D, E, F)$ 及功能依附集：

計算屬性閉包（Attribute Closure）：

• 計算 $A$ 的閉包：$A^+ = \{A, B, C, D, E, F\} = R$，且 $A$ 為單一屬性，故 $A$ 為候選鍵。
• 計算 $BC$ 的閉包：$BC^+ = \{B, C, A, D, E, F\} = R$。
• 檢驗極小性：
• $B^+ = \{B, F\} \neq R$
• $C^+ = \{C\} \neq R$
• 由於 $BC$ 的任何真子集皆無法決定全體屬性，故 $BC$ 亦為候選鍵。
• 檢驗其餘屬性組合：$D^+ = \{D, E\}$、$E^+ = \{E\}$、$F^+ = \{F\}$，均無法衍生出 $A$ 或 $BC$。

結論：

• 候選鍵：$A$、$\{B, C\}$
• 主屬性：$\{A, B, C\}$
• 非主屬性：$\{D, E, F\}$

步驟二：分割成 2NF 關聯綱要

檢視所有決定非主屬性的功能依附，確認是否存在部分功能依附：

• $A \rightarrow BCDEF$：$A$ 為單一屬性候選鍵，不存在真子集，無部分功能依附。
• $BC \rightarrow ADEF$：非主屬性 $\{D, E\}$ 完全依附於複合鍵 $BC$。
• $B \rightarrow F$：$B \subset \{B, C\}$（$B$ 為候選鍵 $BC$ 的真子集），而 $F$ 為非主屬性。此處存在非主屬性 $F$ 部分功能依附於候選鍵 $BC$，違反 2NF。
• $D \rightarrow E$：決定性屬性 $D$ 不是候選鍵的真子集，屬於非主屬性之間的依附，不違反 2NF。

2NF 分割步驟：

1. 將造成部分功能依附的屬性獨立成立新關聯： $R_1(B, F)$

• 功能依附：$B \rightarrow F$
• 候選鍵（主鍵）：$B$

2. 從原關聯中移除被部分依附的屬性 $F$： $R_2(A, B, C, D, E)$

• 功能依附：$A \rightarrow BCDE,\ BC \rightarrow ADE,\ D \rightarrow E$
• 候選鍵：$A$、$\{B, C\}$
• 非主屬性 $\{D, E\}$ 對候選鍵 $A$ 與 $BC$ 皆為完全功能依附，符合 2NF。

步驟三：分割成 3NF 關聯綱要

檢視 2NF 的各子關聯綱要是否符合 3NF：

• 關聯 $R_1(B, F)$：
• 功能依附為 $B \rightarrow F$，$B$ 為超鍵，符合 3NF。
• 關聯 $R_2(A, B, C, D, E)$：

核心觀念

本題評量分散式資料庫系統（DDBMS）的架構特性，以及關聯式資料庫交易處理（Transaction Processing）的基石理論：

1. 分散式資料庫的透明度（Transparency）： 指系統隱藏資料在實體網路上儲存、分散、切割與複製的實作細節，使終端使用者或應用程式在操作分散式資料庫時，體驗與操作單一集中式資料庫完全相同。依資料獨立性層次，常見有片段透明度（Fragmentation Transparency）、位置透明度（Location Transparency）、複本透明度（Replication Transparency）等。

2. 交易的 ACID 特性與 DBMS 機制對應： 交易（Transaction）是資料庫執行邏輯處理的最小工作單元。為維持資料的正確性與健全性，DBMS 必須保證交易具備 ACID 四大特性：

• A（Atomicity，單元性／原子性）：由 回復機制（Recovery Methods） 透過日誌檔（Log-based Recovery）或重做／還原（UNDO/REDO）演算法負責。
• C（Consistency，一致性）：由 DBMS 的完整性限制式（Integrity Constraints）以及 並行控制 與 回復機制 共同維護。
• I（Isolation，隔離性／獨立性）：由 並行控制（Concurrency Control） 透過鎖定協定（2PL）、時間戳記（Timestamp Ordering）或多版本並行控制（MVCC）負責。
• D（Durability，持久性／耐久性）：由 回復機制（Recovery Methods） 透過預寫日誌（WAL, Write-Ahead Logging）及檢查點（Checkpoint）技術負責。

解題方法

1. 第 (一) 子題： 選取分散式資料庫中最核心、層級最高的三種透明度：片段透明度、位置透明度、複本透明度。解題時必須採取「由高抽象層次至低抽象層次」的邏輯順序定義，並說明其在「使用者查詢」時所隱藏的細節，方能拿滿 8 分。

2. 第 (二) 子題： 題目指名「並行控制」與「回復方式」應具備的四項特性，直接對應交易管理核心「ACID」。作答切入點不能僅僅默寫名詞與白話解釋，必須明確闡述各項特性與題目所提的「並行控制」及「回復方式」之間的機制連結，展示出專業深度的因果關係，方能在 12 分中取得滿分。

解題技巧

1. 透明度分層記憶法則： 分散式透明度由高至低的遮蔽順序為： $\text{片段透明度（最高）} \longrightarrow \text{位置透明度} \longrightarrow \text{複本透明度} \longrightarrow \text{區域對映透明度（最低）}$

• 片段透明度：使用者只需提供全域名稱（Global Relation Name），連資料是否有被水平／垂直切割都不必知道。
• 位置透明度：使用者需知道片段名稱，但不必知道存放在哪一個節點（Node / Site）。
• 複本透明度：使用者不需知道該資料在各節點存有幾份副本，由系統自動同步更新。

2. ACID 與 DBMS 子系統精準配對陷阱： 閱卷委員在此題常設的扣分點是「考生只背出 ACID 的字面解釋，卻未緊扣並行控制與回復方式」。切記：

• 並行控制（Concurrency Control）主要負責：I（隔離性）與 C（一致性）。
• 回復方式（Recovery Methods）主要負責：A（單元性）與 D（持久性）。

【答案】

(一) 分散式資料庫的三種透明度與說明