114 一般警察特考二等 刑事警察人員類別數位鑑識組 資料庫管理與運用 考古題詳解

核心觀念

本題考查：

1. 實體關係圖（ERD）轉換為關聯式資料庫綱要。
2. 主鍵與功能相依（Functional Dependency, FD）。
3. 部分相依、傳遞相依與第三正規化（3NF）。
4. 多對多關係及弱實體（Weak Entity）的設計。

題目附圖中的 ERD 在提供的掃描頁面未顯示，因此以下依題目明確指出的主鍵 $TID、RNO、RBDate$，並採取最符合題意的解讀：

• $Team$ 實體具有 $TID、TName$。
• $Room$ 實體具有 $RNO、Space、Fee$。
• 團隊與房間的租借關係具有 $RBDate、REDate、Rent、Objective、Condition$。
• 同一房間同一租借開始日的租借，可由多個團隊共同參與。
• $Objective、Condition$ 視為個別團隊在該次租借中的關係屬性。

---

（一）功能相依

原租借關係的主鍵為：

$$(TID,RNO,RBDate)$$

根據題意可得下列非平凡功能相依：

1. 團隊編號決定團隊名稱

$$TID \rightarrow TName$$

2. 房間編號決定房間坪數與每日費率

$$RNO \rightarrow Space$$ $$RNO \rightarrow Fee$$

合併表示為：

$$RNO \rightarrow Space,Fee$$

3. 房間編號與租借開始日決定該次租借資料

由於同一房間同一租借期間可供多個團隊共同承租，因此該房間該期間的歸還日與總租金應相同：

$$(RNO,RBDate) \rightarrow REDate,Rent$$

4. 團隊在特定房間、特定租借期間的租借用途與條件

$$(TID,RNO,RBDate) \rightarrow Objective,Condition$$

因此最小功能相依集合為：

$$TID \rightarrow TName$$ $$RNO \rightarrow Space,Fee$$ $$(RNO,RBDate) \rightarrow REDate,Rent$$ $$(TID,RNO,RBDate) \rightarrow Objective,Condition$$

其中最後一項的左側為完整主鍵，因此屬於主鍵決定關係屬性的功能相依。

---

（二）原 ERD 轉換與第三正規化

1. 由原 ERD 轉為關聯式綱要

依原 ERD，可先形成一個租借關係：

$$LeaseRecord( TID,TName,RNO,Space,Fee,RBDate,REDate,Rent,Objective,Condition )$$

主鍵為：

$$\underline{TID,RNO,RBDate}$$

即：

• $TID$：團隊編號
• $TName$：團隊名稱
• $RNO$：房間編號
• $Space$：房間坪數
• $Fee$：每日費率
• $RBDate$：租借開始日
• $REDate$：歸還日
• $Rent$：該期間租金
• $Objective$：租借用途
• $Condition$：租借條件

2. 未正規化關係的問題

此關係不是第二正規化，原因如下：

• $TID \rightarrow TName$，只依賴主鍵的一部分。
• $RNO \rightarrow Space,Fee$，只依賴主鍵的一部分。
• $(RNO,RBDate) \rightarrow REDate,Rent$，也只依賴主鍵的一部分。

因此存在資料重複及異動異常：

• 同一團隊名稱會重複出現。
• 同一房間坪數與費率會重複出現。
• 同一房間同一租期的歸還日與租金會因共同承租團隊數量而重複出現。

3. 分解為第三正規化綱要

團隊關係

$$Team(\underline{TID},TName)$$

功能相依：

$$TID \rightarrow TName$$

房間關係

$$Room(\underline{RNO},Space,Fee)$$

功能相依：

$$RNO \rightarrow Space,Fee$$

房間租期關係

$$RoomLease(\underline{RNO,RBDate},REDate,Rent)$$

功能相依：

$$(RNO,RBDate) \rightarrow REDate,Rent$$

其中 $RNO$ 為參照 $Room$ 的外鍵。

團隊參與租借關係

$$TeamLease( \underline{TID,RNO,RBDate}, Objective,Condition )$$

其中：

• $TID$ 為參照 $Team$ 的外鍵。
• $(RNO,RBDate)$ 為參照 $RoomLease$ 的外鍵。
• 主鍵為 $(TID,RNO,RBDate)$。

因此第三正規化綱要為：

$$Team(\underline{TID},TName)$$ $$Room(\underline{RNO},Space,Fee)$$ $$RoomLease(\underline{RNO,RBDate},REDate,Rent)$$ $$TeamLease(\underline{TID,RNO,RBDate},Objective,Condition)$$

各關係中，非主鍵屬性均完全依賴其所在關係的主鍵，且沒有非主鍵屬性決定其他非主鍵屬性的情形，故符合 3NF。

---

（三）加入弱實體後重新設計

1. 弱實體的設計

將一次特定房間、特定開始日的租借視為一個弱實體 $Lease$。

原因是：

• 租借必須依附某一個房間存在。
• $Lease$ 本身沒有獨立且完整的識別屬性。
• $RBDate$ 只能作為部分鍵。
• 必須與擁有者實體 $Room$ 合併，才能唯一識別租借。

因此：

• 強實體：$Room$
• 弱實體：$Lease$
• 部分鍵：$RBDate$
• 識別鍵：$(RNO,RBDate)$

2. 新 ERD 的文字表示

Room 實體

$$Room(RNO,Space,Fee)$$

$RNO$ 為主鍵。

Lease 弱實體

核心觀念

本題考查多表連接、分組與條件彙總，以及以「全稱條件」查詢關聯資料。

• Prisoner 的 PID 是受刑人識別欄位；Performance.PNo 對應受刑人，Performance.TNo 對應訓練梯次。
• GROUP BY 可按訓練梯次彙總人數；HAVING 用來篩選彙總後的群組。
• COUNT(CASE WHEN ... THEN 1 END) 可計算符合條件的資料筆數。
• 「所有表現均為甲」可用 NOT EXISTS 排除任何一筆非甲的紀錄；「沒有任何一次為戊」則直接以 NOT EXISTS 排除戊的紀錄。

解題方法與 SQL

（一）梯次人數大於 5，並列出甲、乙評估人數

先依訓練梯次分組，以該梯次的表現紀錄筆數判斷人數是否大於 5；再用條件計數統計評估為甲或乙的人數。

SELECT T.TNo,
 T.TName,
 COUNT(CASE WHEN P.Evaluation IN ('甲', '乙') THEN 1 END) AS ABCount
FROM Training AS T
JOIN Performance AS P
 ON P.TNo = T.TNo
GROUP BY T.TNo, T.TName
HAVING COUNT(P.PNo) > 5;

此處以 Performance 中該梯次的紀錄數代表受訓人數，並假設每位受刑人在同一梯次至多有一筆表現紀錄。若資料允許同一受刑人同一梯次重複出現，應改以 COUNT(DISTINCT P.PNo) 計算人數；甲、乙人數也應依資料唯一性調整。

（二）所有訓練表現均為甲的偷竊犯姓名

以 NOT EXISTS 確認該受刑人不存在任何非甲的表現紀錄，再依犯罪類型篩選偷竊犯。

SELECT DISTINCT R.Name
FROM Prisoner AS R
WHERE R.CType = 'Theft'
 AND EXISTS (
 SELECT 1
 FROM Performance AS P
 WHERE P.PNo = R.PID
 )
 AND NOT EXISTS (
 SELECT 1
 FROM Performance AS P
 WHERE P.PNo = R.PID
 AND P.Evaluation <> '甲'
 );

核心觀念

本題考查功能相依（Functional Dependency, FD）的推演、屬性閉包、候選鍵、第二正規化（2NF），以及分解是否為無損連結（lossless join）。

• 屬性閉包 $X^+$：由功能相依可從屬性集 $X$ 推得的所有屬性。
• 超鍵：$X^+$ 包含關係的全部屬性。
• 候選鍵：最小超鍵；移除其中任一屬性後，就不再是超鍵。
• Prime attribute：至少出現在一個候選鍵中的屬性；其餘為 non-Prime attribute。
• 2NF：關係已符合 1NF，且每個非 Prime attribute 都完全相依於每一個候選鍵，不得只依賴複合候選鍵的一部分。
• 無損連結分解：分解後的各關係自然連結，恰好還原原關係，不會產生額外的虛假資料列。

（一）推演功能相依並找出候選鍵

已知：

$$AB\to DEG,\qquad J\to CHF,\qquad H\to B$$

依功能相依推演規則，可分別拆解右側屬性：

$$AB\to D,\quad AB\to E,\quad AB\to G$$ $$J\to C,\quad J\to H,\quad J\to F$$

再由傳遞律 $J\to H$、$H\to B$，得到：

$$J\to B$$

求候選鍵

先觀察未出現在任何功能相依右側的屬性。右側屬性為 $B,C,D,E,F,G,H$，因此 $A$、$J$ 不在右側。這兩個屬性無法由其他屬性推得，故每個候選鍵都必須包含 $A,J$。

計算閉包：

$$(AJ)^+=\{A,J\}$$

由 $J\to C,H,F$，加入 $C,H,F$；由 $H\to B$，加入 $B$；由 $AB\to D,E,G$，再加入 $D,E,G$。因此：

$$(AJ)^+=\{A,B,C,D,E,F,G,H,J\}=R$$

所以 $AJ$ 是超鍵。又 $A$ 與 $J$ 都必須出現在每個超鍵中，故 $AJ$ 不可再縮小，是候選鍵。

由此可得唯一候選鍵為：

$$\boxed{AJ}$$

因此：

• Prime attributes：$A,J$
• non-Prime attributes：$B,C,D,E,F,G,H$

（二）是否符合 2NF

不符合 2NF。

候選鍵 $AJ$ 是複合鍵。由 $J\to C,H,F$，可知非 Prime attributes $C,H,F$ 只依賴候選鍵的一部分 $J$，而非完整候選鍵 $AJ$。這構成部分相依，違反 2NF。

核心觀念

本題考資料庫交易的同時存取、更新遺失，以及以「優先次序圖」判斷衝突可序列化。

• 同時存取不要求電腦有多個 CPU；單一 CPU 也能藉由快速切換交易，讓交易的操作交錯執行。
• 衝突操作是來自不同交易、存取同一資料項目，且至少一個操作是寫入的操作組合：讀寫、寫讀或寫寫。讀讀不構成衝突。
• 優先次序圖以交易為節點。若交易 $T_i$ 的某項操作先於 $T_j$ 的衝突操作，就畫出邊 $T_i \to T_j$。
• 若圖中沒有有向環，排程即為衝突可序列化；任一拓樸排序代表一個等價的循序排程。

（一）單一 CPU 是否仍可能同時存取？

可能。 單一 CPU 同一瞬間只執行一個指令，但作業系統與資料庫管理系統可在交易操作之間切換執行。例如先執行交易 $T_1$ 的讀取，再切換執行 $T_2$ 的操作，形成交錯排程。這就是邏輯上的同時存取；不必是多個 CPU 在物理上同一瞬間執行。

（二）更新遺失的現象

假設資料項目 $x$ 原值為 $100$，交易 $T_1$ 要加 $20$，交易 $T_2$ 要減 $10$：

順序	操作	結果
1	$T_1$ 讀取 $x=100$	$T_1$ 暫存計算結果 $120$
2	$T_2$ 讀取 $x=100$	$T_2$ 暫存計算結果 $90$
3	$T_1$ 寫入 $x=120$	資料庫變成 $120$
4	$T_2$ 寫入 $x=90$	資料庫變成 $90$

正確循序執行時，先加後減或先減後加，最後都應為 $110$。但上述交錯執行中，$T_2$ 依舊值 $100$ 計算並寫回 $90$，覆蓋了 $T_1$ 已寫入的 $120$；因此 $T_1$ 的更新遺失，最終值錯誤地成為 $90$。

（三）優先次序圖與衝突可序列化

解題方法

逐一檢查不同交易對同一資料項目的操作。只要操作衝突，就依排程中的先後順序加入有向邊；同一對交易若由多個資料項目導出相同方向的邊，只需保留一條。

排程：

$$r_2(x);w_2(x);r_4(x);w_4(x);r_2(y);r_1(y);w_1(y);w_1(z);r_1(x);w_1(x);r_3(y);w_3(y);r_3(z);w_5(z)$$

逐項建立邊

• 資料項目 $x$：
• $T_2$ 的 $r_2(x)$、$w_2(x)$ 先於 $T_4$ 的 $r_4(x)$、$w_4(x)$，產生 $T_2 \to T_4$。