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

核心觀念

本題重點在 EER 建模、BCNF 正規化、複合主鍵與外鍵，以及以時間區間表達人員任職、單位主管任命等歷史資料。

BCNF 的判準是：對關係中的每個非平凡函數相依 $X \to Y$，決定項 $X$ 都必須是超鍵。建模時應將單位、人員、案件、案件類型及車輛等不同實體分開；會隨時間改變的隸屬與任命資料另設關聯實體，避免把歷史資料覆寫成目前狀態。

題目未指定時間區間端點規則，以下採用「起始時間含、結束時間不含」的半開區間 $[開始, 結束)$；目前有效的區間以 結束時間 = NULL 表示。

解題方法

一、EER 概念模型

受理單位 1 ── 0..N 隸屬紀錄 N..1 ── 警政人員
受理單位 1 ── 0..N 主管任命紀錄 N..1 ── 警政人員
受理單位 1 ── 0..N 案件 N..1 ── 案件類型
警政人員 1 ── 0..N 案件（受理人員）
警政人員 1 ── 0..N 案件（報案人）
案件 1 ── 0..1 車輛協尋資料 1 ── 1 車輛
警政人員 1 ── 0..N 車輛（車主）

案件使用 EER 的「類型子類別」概念：案件為超類別，車輛協尋案件為子類別。關聯式實作時，以 案件類型 和 車輛協尋資料 兩張表呈現；只有類型為「車（牌）輛協尋」的案件才有對應協尋資料。

二、關聯式資料表設計

PK 為主鍵，FK 為外鍵，UQ 為唯一限制。

案件案號由 (單位代號, 發生日期, 流水號) 組成，三欄共同構成案件主鍵。受理案件資訊所需的受理單位名稱，可由案件的 單位代號 連接 受理單位 取得；單位名稱不重複儲存在案件表。

案件的 地址 表示案件發生地；電話 表示報案人聯絡電話。特殊註記欄位依題意保留，存放「告訴乃論罪」等註記；車輛協尋案件的車牌號碼則以 車輛協尋資料 連接車輛，不將車牌字串重複當作車輛資料存於案件表。

三、基數與完整性限制

核心觀念

本題考查多表連接、跨表條件篩選、日期區間、分組彙總、群組條件，以及視窗函數。

• 跨區域就醫：以病人居住區域是否不同於診所所在區域判斷。題目註明每個區域至多一家診所，因此比較區域即可。
• 年資：題目將年資定義為「2024 年各月份－醫師執照發照日的年月」，依年月差計算，不以任職日計算；另以 hire_date 判斷是否已就職滿一年。
• 業績：依題意以自費金額加健保申報點數計算。以下 SQL 假設健保申報點數與自費金額可直接相加，且採用 PostgreSQL 日期與視窗函數語法。
• 群組條件與業績王：彙總後的門檻使用 HAVING；各診所業績王以 RANK 分區排名，並保留同分者。

解題方法與 SQL

(一) 跨區域就醫資訊

連接 Performance、Clinic、Doctor、Patient，再篩選病人居住區域與診所區域不同的紀錄。日期與時間合併排序，確保依就診時間先後排列。

SELECT
 c.name AS 診所名稱,
 c.area AS 診所區域,
 d.name AS 醫師姓名,
 p.name AS 病人姓名,
 p.area AS 居住區域,
 pf.visit_date AS 就診日期,
 pf.visit_time AS 就診時間,
 pf.self_paid_amount AS 自費金額,
 pf.NHI_claim_point AS 健保申報點數
FROM Performance AS pf
JOIN Clinic AS c
 ON pf.Clinic_No = c.Clinic_No
JOIN Doctor AS d
 ON pf.Doctor_ID = d.ID
JOIN Patient AS p
 ON pf.patient_ID = p.ID
WHERE p.area <> c.area
ORDER BY pf.visit_date, pf.visit_time;

(二) 就職滿一年醫師於 2024 年各月業績低於 80,000

先將每筆業績依月份與醫師彙總，再篩選月業績小於 80,000。以月份首日作為月份鍵，將 2024 年每月的業績與醫師資料連接；用年月差計算題目指定的年資。

WITH months AS (
 SELECT
 make_date(2024, m, 1) AS month_start
 FROM generate_series(1, 12) AS g(m)
),
monthly_performance AS (
 SELECT
 date_trunc('month', pf.visit_date)::date AS month_start,
 pf.Doctor_ID,
 SUM(pf.self_paid_amount + pf.NHI_claim_point) AS performance
 FROM Performance AS pf
 WHERE pf.visit_date >= DATE '2024-01-01'
 AND pf.visit_date < DATE '2025-01-01'
 GROUP BY date_trunc('month', pf.visit_date)::date, pf.Doctor_ID
)
SELECT
 TO_CHAR(m.month_start, 'YYYY-MM') AS 月份,
 d.name AS 醫師姓名,
 d.specialty AS 專長領域,
 (
 (EXTRACT(YEAR FROM m.month_start)::int -
 EXTRACT(YEAR FROM d.issuing_date)::int) * 12
 + EXTRACT(MONTH FROM m.month_start)::int
 - EXTRACT(MONTH FROM d.issuing_date)::int
 ) / 12 AS 年資,
 mp.performance AS 業績
FROM months AS m
JOIN monthly_performance AS mp
 ON mp.month_start = m.month_start
JOIN Doctor AS d
 ON d.ID = mp.Doctor_ID
WHERE d.hire_date <= m.month_start - INTERVAL '1 year'
 AND mp.performance < 80000
ORDER BY m.month_start ASC, mp.performance DESC;

年資先以年月差換算成月數，再除以 12。此寫法依題目指定的「年月」計算，故不另依發照日的「日」判斷是否滿整年。months 產生 12 個月份；若某醫師某月沒有業績紀錄，該月不會列出。

(三) 2025 年 5 月營業額超過 1 千萬診所的業績王

核心觀念

本題考查函數相依（functional dependency, FD）與 BCNF 正規化。關係模式符合 BCNF 的條件是：對每一個非平凡函數相依 $X \to Y$，決定項 $X$ 都必須是超鍵。

依題目資料，可辨認出下列業務規則：

• 銀行代號與分行代號合在一起，才能識別分行：$(銀行代號, 分行代號) \to 銀行代號, 分行代號$。
• 題目未提供全行唯一的行員代號，因此以「銀行代號、分行代號、行員代號」識別任職分行的行員；該組合決定行員姓名。
• 換匯時間與幣別在題目資料中共同決定匯率；換匯數量與匯率決定金額。
• 為識別每筆換匯交易，將銀行代號、分行代號、行員代號、幣別與換匯時間組合作為交易的複合主鍵。

題目沒有另行提供交易編號，也未明示匯率的適用範圍；以下依資料呈現的規則，假設同一換匯時間、同一幣別共用匯率，且同一行員在同一分行同一時間對同一幣別至多有一筆交易。

解題方法

先從資料找出重複描述的事實，再依其決定項拆成各自的關係：

1. 銀行與分行資料放在分行表，避免每筆交易重複記錄分行所屬銀行。
2. 行員姓名由行員識別資料決定，放在行員表，避免姓名在多筆交易中重複。
3. 匯率由換匯時間與幣別決定，放在匯率表。
4. 交易表只保留交易識別欄位及交易數量；金額可由數量乘匯率計算，因此不另存金額，以免重複資料造成不一致。

BCNF 關係模式與資料

以下各表均已標示主鍵（PK）與外鍵（FK）。

1. 分行（Branch）

主鍵為 $(銀行代號, 分行代號)$。此表沒有非鍵欄位，因此符合 BCNF。

2. 行員（Employee）

外鍵 $(銀行代號, 分行代號)$ 參照「分行」表的複合主鍵。主鍵為 $(銀行代號, 分行代號, 行員代號)$，並依題目資料假設此組合決定行員名字，故主鍵是名字欄位的決定項，符合 BCNF。

3. 匯率（ExchangeRate）

核心觀念

本題考查 log-based immediate update（日誌式立即更新）在系統故障後的復原原則。立即更新允許交易尚未提交時，就將資料頁寫回磁碟，因此故障後必須同時考慮：

• Redo：重做故障前已提交的交易，確保其更新結果留在資料庫中。
• Undo：撤銷故障時尚未提交的交易，確保未完成交易的更新不會留在資料庫中。

日誌通常記錄交易識別碼與更新前、後的值，例如 $⟨T_i, X, old, new⟩$。依 write-ahead logging 原則，資料頁寫回磁碟前，相關日誌須先寫入穩定儲存體。

解題方法

對每一個故障時間，逐一判斷 $T1$ 至 $T6$ 在故障發生前是否已提交：

核心觀念

本題比較四種資料庫並行控制方法的同步機制，重點是辨別它們如何處理交易間的衝突，以及是否會因等待鎖定資源而形成死結（deadlock）。

• 加鎖（locking）：交易取得資料項目的鎖，限制其他交易同時讀寫。
• 死結（deadlock）：數個交易互相等待對方持有的資源，形成循環等待。若系統不讓交易因等待鎖而形成這種循環，便可避免死結。
• **時間戳記排序（Timestamp Ordering, TO）與樂觀式並行控制（Optimistic Concurrency Control, OCC）**依序列化規則或驗證結果處理衝突，不以交易間等待鎖作為主要控制方式。
• **多版本並行控制（Multi-version Concurrency Control, MVCC）**以多個資料版本支援讀寫並行；讀取通常不必等待寫入鎖，但寫入端仍可能使用鎖或其他衝突控制機制。

解題方法

逐一判斷各方法的並行控制手段：若交易須取得鎖，並可能因鎖而等待，就要進一步判斷是否可能產生循環等待；若不靠交易間等待鎖處理衝突，通常可避免傳統鎖定死結。

需特別注意，MVCC 是一種以多版本處理讀寫衝突的架構；不同資料庫的實作可能搭配鎖。因此，判斷「是否需要加鎖」時，應說明 MVCC 的讀取特性與寫入實作差異，避免把「讀取不阻塞」誤解成「系統完全不用鎖」。

作答表格