112 調查局特考三等 資訊科學組 資料庫應用 考古題詳解

核心觀念

本題考查增強實體關係模型（EER）、關聯式資料庫正規化至 BCNF，以及依需求設定主鍵、外鍵與最小／最大基數。題目未涉及法條，無須適用法規版本說明。

BCNF 的判斷原則是：對關係中的每一個非平凡函數相依 $X \to Y$，決定項 $X$ 必須是超鍵。設計時應將「案件、庭訊、地點、當事者」等不同概念分開，避免把可重複資料放在同一張表，並以關聯表表示多對多關係。

解題方法

先從查詢需求找出必要實體與屬性：

• 查詢案件字號、類型、案由、審別及審次，需要「案件」與「庭訊」資料。
• 查詢庭訊日期時間及地點，需要「庭訊」及「庭訊室」資料。
• 查詢原告、被告與所有當事者資料，需要「當事者」及案件與當事者間的角色關聯。
• 查詢未成年女性原告及庭訊時年齡，需要自然人出生日期、性別，以及庭訊日期。
• 查詢公司資料，需要公司註冊日期、資本額及營業項目。
• 查詢庭訊室容量，需要庭訊室容量屬性。

庭訊是案件在特定日期時間、特定庭訊室進行的一次活動；同一案件可有多次庭訊，因此「審別、審次」描述庭訊紀錄，而非案件本身。以下假設同一庭訊室同一時間最多安排一場庭訊，且以「案件字號＋審別＋審次」識別一筆庭訊紀錄。

EER 資料模型

以文字表示關聯與基數如下；基數採「最小值..最大值」。

1. 案件（Case）

• case_no PK：案件字號
• case_type：案件類型
• cause：案由

2. 庭訊（Hearing）

• case_no PK、FK → Case.case_no
• appeal_level PK：審別
• hearing_no PK：該審次序
• hearing_datetime：庭訊日期時間
• courtroom_id FK → Courtroom.courtroom_id

複合主鍵為 (case_no, appeal_level, hearing_no)。案件字號決定案件類型與案由；庭訊複合鍵決定庭訊日期時間與庭訊室，因此各資料依其所屬鍵存放，符合 BCNF。

3. 庭訊大樓（Building）

• building_id PK
• building_name：庭訊大樓名稱

4. 庭訊室（Courtroom）

• courtroom_id PK
• building_id FK → Building.building_id
• floor：樓層
• room_no：庭訊室序號
• capacity：人數容量
• 候選鍵：(building_id, floor, room_no)

假設庭訊室序號在同一大樓、同一樓層內唯一。若使用自然鍵作主鍵，也可將上述候選鍵直接設為複合主鍵。

5. 當事者（Party）：EER 超類別

• party_id PK
• party_type：PERSON 或 COMPANY
• phone：聯絡電話
• address：地址

設定 完全且互斥（total, disjoint） 特化：每一個當事者必須且只能屬於自然人或公司其中一種。

6. 自然人（Person）：當事者子類別

• party_id PK、FK → Party.party_id
• person_name：姓名
• gender：性別
• birth_date：出生日期

7. 公司（Company）：當事者子類別

• party_id PK、FK → Party.party_id
• company_name：公司名稱
• registration_date：註冊日期
• capital_amount：資本額
• business_item：營業項目

若一家公司有多項營業項目，應另設 CompanyBusinessItem(party_id, business_item)，以兩欄組成主鍵，避免在單一欄位存放多個值。

核心觀念

本題主要考查多表連接、日期範圍篩選、彙總函數、分組後取最大值，以及以 GROUP BY、HAVING 篩選群組。

• Member 與 Buy_Record 以 fb_account 連接；Buy_Record 與 Group 以 group_code 連接。
• 消費金額以「訂購數量 × 團購單價」計算。
• WHERE 篩選明細列；GROUP BY 彙總資料；HAVING 篩選彙總後的群組。
• SQL 的 GROUP 是保留字，題目中的資料表名稱若直接採用，部分資料庫需加上識別字引號，例如 "Group"（PostgreSQL、標準 SQL）或 [Group]（SQL Server）。

以下以標準 SQL 的 "Group" 表示資料表名稱。日期範圍採半開區間，亦即包含 2023 年 6 月 30 日整日。

解題方法與 SQL

（一）指定期間內採購綜合維他命的成員與消費明細

以三表連接取得成員、訂購與團購資訊，再用訂購日期和物品名稱篩選。依單筆消費金額由高至低排序。

SELECT M.name AS 成員名稱,
 M.mobile AS 手機號碼,
 B.group_code AS 團號,
 G.item AS 物品,
 G.start_datetime AS 開團日期時間,
 G.end_datetime AS 結團日期時間,
 B.order_datetime AS 訂購日期,
 B.quantity AS 數量,
 B.quantity * G.unit_price AS 消費金額
FROM Member AS M
JOIN Buy_Record AS B
 ON M.fb_account = B.fb_account
JOIN "Group" AS G
 ON B.group_code = G.group_code
WHERE G.item = '綜合維他命'
 AND B.order_datetime >= '2023-01-01'
 AND B.order_datetime < '2023-07-01'
ORDER BY 消費金額 DESC;

用 < '2023-07-01' 可涵蓋 6 月 30 日任何時間的訂購紀錄，不會因日期欄位含時間而漏列。

（二）每團採購數量最多的成員及採購訊息

先從每筆紀錄計算該成員在該團的訂購數量，再找出同團的最大數量。若同團有多人並列最多，應一併列出，因此使用相關子查詢比較該團最大值，不用只會回傳單一列的 ROW_NUMBER。

核心觀念

BCNF（Boyce-Codd Normal Form）的判斷方式是：對每一個非平凡函數相依 $X \to Y$，決定因素 $X$ 必須是該關係的超鍵。若某個非鍵欄位由非超鍵決定，就應拆分資料表，避免更新異常、插入異常與刪除異常。

本題題幹提到「如下外籍移工聘任契約的表格資料」，但目前未附原始表格、欄位或資料內容。因此，無法由題目直接確認實際函數相依或唯一的正規化結果。以下明確採用合理假設示範完整解法；實際作答時，應依題目提供的欄位與函數相依調整。

解題方法

假設原始資料表包含下列欄位：

$R(\text{契約編號},\text{移工編號},\text{移工姓名},\text{國籍},\text{雇主統編},\text{雇主名稱},\text{仲介編號},\text{仲介名稱},\text{工作內容},\text{薪資},\text{起日},\text{迄日})$

並假設一份契約對應一名移工、一位雇主及一家仲介，且有下列函數相依：

• $\text{契約編號} \to \text{移工編號},\text{雇主統編},\text{仲介編號},\text{工作內容},\text{薪資},\text{起日},\text{迄日}$
• $\text{移工編號} \to \text{移工姓名},\text{國籍}$
• $\text{雇主統編} \to \text{雇主名稱}$
• $\text{仲介編號} \to \text{仲介名稱}$

由此可知，契約編號決定一筆契約的內容；移工編號、雇主統編與仲介編號則各自決定其名稱或基本資料。若把所有欄位放在同一張表，移工姓名、雇主名稱及仲介名稱都會重複出現在多筆契約中。

BCNF 正規化後的資料表

移工

移工編號（PK）	移工姓名	國籍
M001	示例移工甲	越南

雇主

雇主統編（PK）	雇主名稱
E001	示例雇主甲

仲介

仲介編號（PK）	仲介名稱
A001	示例仲介甲

聘任契約

契約編號（PK）	移工編號（FK）	雇主統編（FK）	仲介編號（FK）	工作內容	薪資	起日	迄日
C001	M001	E001	A001	示例工作	30000	2023-01-01	2025-12-31

其中，聘任契約.移工編號 參照 移工.移工編號；聘任契約.雇主統編 參照 雇主.雇主統編；聘任契約.仲介編號 參照 仲介.仲介編號。

核心觀念

本題考查以 Log-Based Recovery（以日誌為基礎的復原） 處理系統故障時，如何根據故障前的交易日誌，判斷各交易要 復做（redo）、復原（undo） 或 不處理。

判斷依據是故障發生時交易是否已提交，以及更新模式是否允許已提交資料尚未寫入資料庫：

• Deferred Update（延遲更新）：交易提交前不將更新寫入資料庫。故障後只需復做已提交交易，未提交交易不處理。
• Immediate Update（立即更新）：交易執行期間即可將更新寫入資料庫，因此未提交交易可能已改動資料，復原時必須視更新模式決定是否撤銷。
• Undo/Redo：日誌保存更新前、後的值；復原時撤銷未提交交易，並重做已提交交易。
• Undo/No-Redo：日誌保存更新前的值；系統保證已提交交易的更新已寫入資料庫，因此只需撤銷未提交交易。

題目資訊限制

題目所稱「如下的交易紀錄」及「請參考原卷」中的日誌內容未附於題目文字，因此缺少 $T1$ 至 $T6$ 的開始、更新、提交紀錄及其相對於 $t5$、$t9$ 的位置；無法據此逐一判定各交易在兩個故障時間點的實際動作。以下先列出可直接套用到原卷日誌的判定表。

解題方法

對每一個故障時間點，將該時間點視為獨立復原情境，只檢查故障前已出現在日誌中的紀錄：

1. 找出在故障前已出現 <Ti, start> 的交易。
2. 檢查每個交易在故障前是否已出現 <Ti, commit>。
3. 依更新模式套用復原規則。
4. $t5$ 與 $t9$ 分開判斷；兩次故障是獨立事件，不能把 $t5$ 的復原結果當成 $t9$ 的復原起點。

故障時間點的交易狀態	Deferred Update	Immediate Update：Undo/Redo	Immediate Update：Undo/No-Redo
故障前尚未開始	不處理	不處理	不處理
已開始，故障前尚未提交	不處理；未提交更新尚未寫入資料庫	Undo；撤銷該交易已寫入的更新	Undo；撤銷該交易已寫入的更新
故障前已提交	Redo；重做該交易更新	Redo；重做該交易更新	不需 Redo；已提交更新已寫入資料庫

在 Undo/Redo 模式下，復原一般先撤銷未提交交易，再重做已提交交易。撤銷時依日誌的反向順序處理更新，以回到交易開始前的狀態。

套用至 $t5$ 與 $t9$

將原卷中各交易的提交狀態填入下表，即可列出 $T1$ 至 $T6$ 的動作：