109 關務特考三等 資訊處理 資料庫應用 考古題詳解

核心觀念

本題重點是將需求敘述轉成符合 BCNF 的 EER 資料模型，並清楚標示鍵值、關聯及其最小／最大基數。題目不是選擇題，也沒有法條適用。

• BCNF（Boyce–Codd Normal Form）：對每個非平凡函數相依 $X \to Y$，決定因素 $X$ 都必須是超鍵。設計時應避免把機構地址、開放時段、檢驗結果、確診資訊等彼此獨立的事實塞在同一張表，造成更新異常。
• EER（Enhanced ER）：在 ER 模型上加入子型別／超型別等概念，適合表達「人員」分為一般受檢者與確診者等不同角色。
• 候選鍵（candidate key）：能唯一識別資料列、且不含多餘屬性的鍵。選定其中一個作為主鍵，其餘仍須維持唯一性。
• 最小／最大基數：以 $(min,max)$ 表示參與關聯的數量範圍；例如 $(0,N)$ 代表可不參與，也可參與多次。

解題方法

先從需求找出各自獨立的事實，再以實體與關聯拆表：

1. 醫事機構及地址、電話屬於機構資料。
2. 機構的開放時段是多值資料；星期與時段組成開放規則，不能直接重複塞在機構欄位。
3. 每次採檢是一筆事件，須記錄受檢者、採檢機構與時間。
4. 確診者才有感染類型、感染源、症狀及收治機構等確診資料，因此以「受檢者」為超型別、「確診者」為子型別。
5. 未確診且需追蹤的人員可有一筆或多筆追蹤事由，追蹤資料應獨立於確診資料。
6. 縣市是可供機構地址、人員居住地址查詢的共同資料，獨立建模，避免縣市名稱重複造成不一致。

EER 資料模型

以下以 ID 表示系統產生的識別碼；代碼欄位均須設定唯一性。

超型別與子型別：Person 為超型別，Case 為其子型別。每位確診者必須對應一位受檢者；一位受檢者至多對應一筆確診資料。若系統規則限定「確診」須有採檢紀錄，則 Case 中的每位人員也必須至少有一筆 Test 且結果為確診。

基數與參與條件

核心觀念

本題考查關聯式資料庫的資料定義與查詢，包括主鍵、外鍵、資料完整性、分組統計、連接查詢，以及依參考完整性要求安排新增資料的順序。

Person 存放人員，Flight 以航班代號與日期共同識別一筆航班，TravelRecord 記錄人員搭乘哪一航班及座位。TravelRecord 的兩組外鍵分別參照 Person 與 Flight，所以新增搭乘紀錄前，對應的人員和航班都必須已存在。

解題方法

（一）以 DDL 定義資料表

Flight 的主鍵是 (flightID, date)，而 TravelRecord 對應該主鍵的外鍵也必須使用相同欄位組合。以下以標準 SQL 型別表示欄位：

CREATE TABLE Person (
 personID VARCHAR(9) NOT NULL,
 name VARCHAR(100) NOT NULL,
 nationality VARCHAR(3) NOT NULL,
 gender VARCHAR(10) NOT NULL,
 PRIMARY KEY (personID)
);

CREATE TABLE Flight (
 flightID VARCHAR(10) NOT NULL,
 date DATE NOT NULL,
 flightNation VARCHAR(3) NOT NULL,
 takeoff VARCHAR(100) NOT NULL,
 arrival VARCHAR(100) NOT NULL,
 PRIMARY KEY (flightID, date)
);

CREATE TABLE TravelRecord (
 personID VARCHAR(9) NOT NULL,
 flightID VARCHAR(10) NOT NULL,
 date DATE NOT NULL,
 seat VARCHAR(5) NOT NULL,
 PRIMARY KEY (personID, flightID, date),
 FOREIGN KEY (personID) REFERENCES Person (personID),
 FOREIGN KEY (flightID, date) REFERENCES Flight (flightID, date)
);

題目未指定欄位長度及性別、國籍的資料型態，因此採用適合存放代碼與文字的 VARCHAR；各欄位長度可依實際系統規格調整。

（二）依國籍及性別統計指定航班的搭機人數

先由 TravelRecord 找出搭乘紀錄，再連接 Flight 篩選出 2020 年 5 月自 USA New York 起飛的班機，並連接 Person 取得國籍與性別。最後依國籍及性別分組計數：

SELECT p.nationality, p.gender, COUNT(*) AS passengerCount
FROM TravelRecord AS t
JOIN Person AS p
 ON t.personID = p.personID
JOIN Flight AS f
 ON t.flightID = f.flightID
 AND t.date = f.date
WHERE f.date >= DATE '2020-05-01'
 AND f.date < DATE '2020-06-01'
 AND f.flightNation = 'USA'
 AND f.takeoff = 'New York'
GROUP BY p.nationality, p.gender;

核心觀念

本題考查函數相依、候選鍵與 BCNF 分解。關係模式符合 BCNF 的條件是：對每個非平凡函數相依 $X \to Y$，決定項 $X$ 都必須是超鍵。

依表格欄位與資料意義，可採用下列函數相依：

• ProjectID → ProjectName, Budget：專案代號決定專案名稱與預算。
• EmployeeID → EmployeeName, Gender：員工代號決定員工姓名與性別。
• EmployeeID → Mobile, Email：依題目重複出現的員工資料，採用員工代號識別員工聯絡資料。
• (ProjectID, EmployeeID) → StartDate, EndDate：員工被指派至某專案時，該筆指派決定起訖日期。

其中，專案代號與員工代號的組合可識別一筆指派；但 ProjectID、EmployeeID 各自都不能決定所有欄位，因此原表不符合 BCNF。

解題方法

以「專案資料、員工資料、專案指派資料」分解原表，讓每種事實只存放在對應資料表中：

1. 專案名稱與預算只依專案代號決定，存入專案表。
2. 員工姓名、性別與聯絡方式只依員工代號決定，存入員工表。
3. 員工參與專案及其起訖日期，存入指派表。

分解後，每個資料表的決定項都是該表的鍵，符合 BCNF。

BCNF 正規化後的資料表

專案表 Project

主鍵：ProjectID

員工表 Employee

主鍵：EmployeeID

指派表 Assignment

複合主鍵：(ProjectID, EmployeeID)；ProjectID、EmployeeID 分別為外鍵。

JSON 檔案格式

以下以 JSON 陣列表示各資料表資料列；每個物件代表一筆資料，空白欄位以 null 表示。日期統一採 YYYY-MM-DD 格式。

Project.json

核心觀念

本題考的是時間戳記排序法（Timestamp Ordering Protocol）。每個交易在開始時取得時間戳記 $TS(T_i)$；每筆資料 $Q$ 則記錄：

• $R\_TS(Q)$：曾讀取 $Q$ 的交易中，最大的時間戳記。
• $W\_TS(Q)$：曾寫入 $Q$ 的交易中，最大的時間戳記。

初始時，資料 $X$、$Y$ 的 $R\_TS$ 與 $W\_TS$ 都設為 $0$。

讀取 $Q$ 的規則：

• 若 $TS(T_i)<W\_TS(Q)$，拒絕讀取並回復交易。
• 否則允許讀取，並更新 $R\_TS(Q)=\max(R\_TS(Q),TS(T_i))$。

寫入 $Q$ 的規則：

• 若 $TS(T_i)<R\_TS(Q)$ 或 $TS(T_i)<W\_TS(Q)$，拒絕寫入並回復交易。
• 否則允許寫入，並更新 $W\_TS(Q)=TS(T_i)$。

解題方法

依照原頁左圖，時間順序為：$T1$ 在時間 1 開始，$T2$ 在時間 3 開始。時間戳記取交易開始時間，因此 $TS(T1)=1$、$TS(T2)=3$。逐一處理讀寫動作，並依規則更新資料的時間戳記。