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

核心觀念

本題要求同時完成三件事：由需求辨識實體與關聯、以函數相依檢查並達到 BCNF，以及用 EER 表達物件導向的繼承與類別特色。題目不是只要列出可查詢的欄位；還要避免把可推導的總價、庫存量等資料重複儲存，並標明鍵與關聯基數。

BCNF 的判準是：對關係中的每一個非平凡函數相依 $X \to Y$，$X$ 都必須是該關係的超鍵。若一筆交易明細由「交易編號、項次」識別，則交易編號決定交易日期、經手員工等表頭資料；藥品編號決定藥品名稱、種類等藥品資料。將表頭、明細與藥品資料分開，可避免非鍵欄位依賴部分鍵或其他非鍵欄位。

解題方法

先將需求分為藥品主檔、供應商與藥廠、員工、進銷貨交易、處方箋領藥、庫存查詢等資料。再判斷每項資料的識別方式與函數相依，將多值資料（如藥品副作用）拆成獨立關聯，最後以 EER 的子類別呈現國內／國外供應商及進貨／銷貨交易等特性。

以下假設：每筆進貨或銷貨有唯一交易編號；一張交易可包含多項藥品；每項交易明細記錄當次數量與單價；供應商與藥廠視為同一組織實體，可分別參與供貨關係。庫存以藥品與供應商為粒度，並由進貨、銷貨及領藥異動計算；若業務要求保存每日結存快照，再另設庫存快照表。

EER 實體與資料表

藥品與藥品類別

• 藥品類別（CategoryID PK，CategoryName UNIQUE）
• 藥品（DrugID PK，DrugName，CategoryID FK，IsControlled，UsageInfo）

藥品名稱未假設全系統唯一，因此以 DrugID 作為主鍵。若業務規則明定名稱唯一，DrugName 可另列候選鍵；題目未提供此規則，故不自行設定。

一個藥品類別可包含零至多種藥品；每種藥品恰屬一個類別，故為「類別 $1$ 對藥品 $0..N$」，藥品端必須參照一個類別。

副作用

• 藥品副作用（DrugID FK，SideEffect；PK：(DrugID, SideEffect)）

一種藥品可有零至多項副作用，每項副作用紀錄只屬於一種藥品。將副作用拆表可避免一欄存放多個值，並符合第一正規化。

供應商與藥廠

• 組織（OrgID PK，OrgName）
• 國家（CountryCode PK，CountryName UNIQUE，Region）
• 國內供應商：OrgID PK／FK → 組織
• 國外供應商：OrgID PK／FK → 組織，CountryCode FK → 國家
• 藥品供應關係（DrugID FK，OrgID FK；PK：(DrugID, OrgID)）

供應商與藥廠以組織作為共同超類別；國內供應商、國外供應商是子類別，且假設每一供應商恰屬其中一類。國內與國外供應商的子類別互斥。國外供應商透過國家取得國別與區域，不在供應商表重複存放國名或區域名稱。

藥品與供應商為多對多：一種藥品可由零至多家組織供應，一家組織也可供應零至多種藥品，透過「藥品供應關係」表示。

員工與交易

• 員工（EmployeeID PK，EmployeeName）
• 交易表頭（TransactionID PK，TransactionDate，EmployeeID FK）
• 進貨交易：TransactionID PK／FK，SupplierID FK → 組織
• 銷貨交易：TransactionID PK／FK
• 交易明細（TransactionID FK，LineNo，DrugID FK，SupplierID FK〔銷貨可為 NULL〕，Quantity，UnitPrice；PK：(TransactionID, LineNo)）

核心觀念

本題考查 衝突等價（conflict equivalence） 與 衝突可序列化（conflict serializability）。兩個操作若符合以下條件，便構成衝突：

1. 操作來自不同的 transaction。
2. 存取同一筆資料。
3. 至少一個操作是寫入。

因此，$R_i(X)$ 與 $R_j(X)$ 不衝突；$R_i(X)$ 與 $W_j(X)$、$W_i(X)$ 與 $R_j(X)$，以及 $W_i(X)$ 與 $W_j(X)$ 都衝突。衝突操作的先後順序會形成 transaction 間的先行關係。

判斷方式是建立優先圖（precedence graph）：每個 transaction 是一個節點；若 $T_i$ 有一項衝突操作先於 $T_j$ 的操作，便畫出邊 $T_i \to T_j$。若圖中沒有有向 cycle，排程具衝突可序列性；若有有向 cycle，則不具衝突可序列性。

解題方法

核心觀念

BCNF（Boyce–Codd 正規形）的判定方式是：對資料表中每一個非平凡函數相依 $X \to Y$，決定因素 $X$ 都必須是超鍵。違反 BCNF 時，將資料表依該函數相依分解，直到各表均符合 BCNF。

題目沒有提供明確的函數相依或鍵值，以下依資料欄位的業務意義採用這些假設：

• 店家名稱唯一識別一家店，且決定店家地址、店家電話。
• 外送員編唯一識別外送員，且決定外送員手機。
• 一筆訂單由「店家名稱、外送平台、取貨日期時間」識別，並決定餐飲類型、外送員編及訂單總價。
• 店家可提供不同餐飲類型；平台與外送員不是固定配對。因此「店家名稱 $\to$ 餐飲類型」及「外送平台 $\to$ 外送員編」不成立。

解題方法

先將欄位以簡稱表示：

• 店家名稱 $S$、店家地址 $A$、店家電話 $T$
• 餐飲類型 $C$、外送平台 $P$
• 外送員編 $D$、外送員手機 $M$
• 取貨日期時間 $K$、訂單總價 $V$

依上述假設，主要函數相依為：

$$S \to A,T$$ $$D \to M$$ $$(S,P,K) \to C,D,V$$

原始資料表的候選鍵為 $(S,P,K)$。但 $S \to A,T$ 中，$S$ 不是超鍵；$D \to M$ 中，$D$ 也不是超鍵。因此原表違反 BCNF。

依這些相依關係分解為四個資料表：

1. 店家表：處理店家名稱決定店家地址及電話。
2. 外送員表：處理外送員編決定手機。
3. 訂單表：保留訂單識別欄位及訂單本身的資料。
4. 訂單餐飲類型表：依題目資料，店家可對應多種餐飲類型，故將餐飲類型與訂單識別欄位另存。

分解後，店家表的鍵為店家名稱，外送員表的鍵為外送員編，訂單表的鍵為 $(店家名稱, 外送平台, 取貨日期時間)$，訂單餐飲類型表的鍵亦為 $(店家名稱, 外送平台, 取貨日期時間)$。各表中每一個非平凡函數相依的決定因素都是超鍵，符合 BCNF。

BCNF 正規化後的資料表（含內容）

店家

店家名稱（PK）	店家地址	店家電話
Store1	Addr.1	Tel.1
Store2	Addr.2	Tel.2
Store3	Addr.3	Tel.3

外送員

外送員編（PK）	外送員手機
DE1	900
DE2	911
DE3	922
DE4	933
DE5	944

訂單

店家名稱（PK、FK）	外送平台（PK）	取貨日期時間（PK）	外送員編（FK）	訂單總價
Store1	Uber	2021-05-25 12:00:00 PM	DE1	150
Store2	Uber	2021-05-25 12:00:00 PM	DE2	200
Store2	Food Panda	2021-05-25 12:00:00 PM	DE3	250
Store3	Food Panda	2021-05-26 12:00:00 PM	DE3	300
Store3	Food Panda	2021-05-26 12:00:00 PM	DE4	250
Store3	Food Panda	2021-05-26 06:00:00 PM	DE4	200
Store3	Uber	2021-05-26 12:00:00 PM	DE2	150
Store3	Uber	2021-05-26 06:00:00 PM	DE1	200
Store3	Uber	2021-05-26 06:00:00 PM	DE5	250

訂單餐飲類型

店家名稱（PK、FK）	外送平台（PK）	取貨日期時間（PK）	餐飲類型
Store1	Uber	2021-05-25 12:00:00 PM	咖哩專賣
Store2	Uber	2021-05-25 12:00:00 PM	咖哩專賣
Store2	Food Panda	2021-05-25 12:00:00 PM	咖哩專賣
Store3	Food Panda	2021-05-26 12:00:00 PM	傳統麵食
Store3	Food Panda	2021-05-26 12:00:00 PM	傳統麵食
Store3	Food Panda	2021-05-26 06:00:00 PM	傳統麵食
Store3	Uber	2021-05-26 12:00:00 PM	傳統麵食
Store3	Uber	2021-05-26 06:00:00 PM	熱炒小吃
Store3	Uber	2021-05-26 06:00:00 PM	熱炒小吃

**鍵值說明：**題目中 Store3、Food Panda、2021-05-26 12:00:00 PM 出現兩筆不同外送員及價格的資料；Store3、Uber、2021-05-26 06:00:00 PM 也出現兩筆。這表示假設的訂單鍵不足以唯一識別每筆資料。實際 RDB 應增加「訂單編號」作為主鍵，並以訂單編號連結訂單餐飲類型表；若不新增欄位，這兩組重複鍵資料便無法在關聯表中完整區分。

對應 JSON 檔案格式

store.json

核心觀念

本題考查關聯式資料庫的資料定義與 SQL 查詢，重點包括主鍵、外鍵與參照完整性，以及多表連接、條件篩選、分組彙總與排序。題目未指定資料庫管理系統，以下以通用 SQL 撰寫；DATE、TIME 為日期與時間型別。

（一）建立資料表

Place 與 Person 分別以 placeID、mobile 作為主鍵。EnterRecord 記錄某人於特定時間進出某場所，因此以四個欄位組成複合主鍵，並以外鍵連接場所與人員資料。

CREATE TABLE Place (
 placeID VARCHAR(20) NOT NULL,
 placeName VARCHAR(100) NOT NULL,
 countyCity VARCHAR(50) NOT NULL,
 district VARCHAR(50) NOT NULL,
 address VARCHAR(200),
 CONSTRAINT pk_place PRIMARY KEY (placeID)
);

CREATE TABLE Person (
 mobile VARCHAR(20) NOT NULL,
 name VARCHAR(50) NOT NULL,
 telecomProvider VARCHAR(50),
 CONSTRAINT pk_person PRIMARY KEY (mobile)
);

CREATE TABLE EnterRecord (
 placeID VARCHAR(20) NOT NULL,
 mobile VARCHAR(20) NOT NULL,
 date DATE NOT NULL,
 time TIME NOT NULL,
 CONSTRAINT pk_enterrecord
 PRIMARY KEY (placeID, mobile, date, time),
 CONSTRAINT fk_enterrecord_place
 FOREIGN KEY (placeID) REFERENCES Place (placeID),
 CONSTRAINT fk_enterrecord_person
 FOREIGN KEY (mobile) REFERENCES Person (mobile)
);

主鍵欄位不得重複且不得為 NULL；外鍵則要求每筆進出紀錄所指向的場所與手機，必須已存在於 Place 與 Person 中，藉此維持參照完整性。

（二）依行政區統計公共場所進出人次

先連接 EnterRecord 與 Place，篩選日期區間，再依縣市與行政區分組。每筆進出紀錄代表一人次，因此以 COUNT(*) 計算，並按人次由多至少排序。

SELECT p.countyCity,
 p.district,
 COUNT(*) AS totalEntries
FROM EnterRecord AS e
JOIN Place AS p
 ON e.placeID = p.placeID
WHERE e.date BETWEEN DATE '2021-05-11' AND DATE '2021-05-31'
GROUP BY p.countyCity, p.district
ORDER BY totalEntries DESC;

BETWEEN 會包含起日與迄日。此處統計的是進出紀錄筆數，也就是人次；同一人於不同時間進出，會分別計入。

（三）查詢萬華區指定市場的進出人員