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

核心觀念

本題考查 EER（Enhanced ER）模型設計、物件導向的繼承／子型別、主鍵與外鍵，以及 BCNF 正規化。題目不是選擇題，因此不適用選項分析。

EER 可用「超型別—子型別」表示共同屬性與專屬屬性：會員是超型別，乘客、司機、車主是子型別。子型別繼承會員的主鍵及共同屬性。超型別的主鍵同時作為子型別的主鍵與外鍵。

BCNF 的判準是：對每個非平凡函數相依 $X \to Y$，決定因素 $X$ 必須是超鍵。將會員、車輛、車次、乘客搭乘與路線站點分開，可避免把不同實體與多值資料塞在同一張表，造成重複與更新異常。

解題方法

先依題意辨認實體，再將共同資料放入會員超型別、個別角色資料放入子型別；接著以車次表示一次行程，以搭乘關聯記錄乘客，以路線站點記錄依序經過的地點及時間。最後標出候選鍵與各關聯的最小／最大基數。

以下假設：每一車次由一位司機駕駛一部車；車次以「司機、車號、出發日期時間」識別；同一車次的路線站點順序不重複。題目未要求的會員偏好、付款、預約狀態等資料不列入。

EER 模型與資料表

1. 會員超型別與角色子型別

會員（Member）

欄位	說明
身分證字號	PK、候選鍵
姓名
Email	候選鍵
手機號碼	候選鍵

身分證字號、Email、手機號碼依會員資料的唯一性設定為候選鍵，選身分證字號作為主鍵。若系統規格未保證 Email 或手機號碼唯一，則兩者不應列為候選鍵。

乘客（Passenger）

欄位	說明
身分證字號	PK、FK → 會員.身分證字號
出生年月日
性別

司機（Driver）

欄位	說明
身分證字號	PK、FK → 會員.身分證字號
出生年月日
性別
駕照號碼	候選鍵
駕照核發單位
駕照核發日期

一個會員可同時具有乘客、司機或車主等角色；題目沒有規定角色互斥，因此採可重疊子型別。司機的駕照號碼依其識別性設為候選鍵。

車主（VehicleOwner）

欄位	說明
身分證字號	PK、FK → 會員.身分證字號

車主本身只需表示會員的車主角色；車輛資料另設車輛表。若一位會員提供一部車，則車主與車輛為一對零或一對一關聯。

2. 車輛

車輛（Vehicle）

欄位	說明
車號	PK、候選鍵
車主身分證字號	FK → 車主.身分證字號；UNIQUE
製造商
年份
汽缸量
最大載客數
登記單位

車號識別車輛。車主身分證字號加上 UNIQUE 約束，落實「一個會員最多提供一部車」。此處的最大載客數依題目用語照錄；若系統將司機另計，容量解讀需由業務規則另行明訂。

3. 車次

車次（Trip）

欄位	說明
司機身分證字號	PK 的一部分、FK → 司機.身分證字號
車號	PK 的一部分、FK → 車輛.車號
出發日期時間	PK 的一部分
起點
終點

複合主鍵為：

$$(\text{司機身分證字號},\ \text{車號},\ \text{出發日期時間})$$

此鍵符合題目所需查詢條件「特定司機、車號、日期、出發時間」。其中日期與出發時間可由出發日期時間欄位拆分查詢，亦可於實作時分欄儲存，但應維持同一識別規則。若起點、終點也以地點代碼表示，則應以地點代碼作外鍵連至地點資料表；題目未要求地點有其他屬性，故此處直接記錄地點。

4. 乘客搭乘

搭乘（TripPassenger）

欄位	說明
司機身分證字號	PK 的一部分、FK → 車次
車號	PK 的一部分、FK → 車次
出發日期時間	PK 的一部分、FK → 車次
乘客身分證字號	PK 的一部分、FK → 乘客.身分證字號

複合主鍵為：

$$(\text{司機身分證字號},\ \text{車號},\ \text{出發日期時間},\ \text{乘客身分證字號})$$

核心觀念

本題考查關聯式資料庫的資料定義語言（DDL）、主鍵與外鍵設計、資料表連結、分組統計、子查詢，以及刪除資料時維護參照完整性。

• Room、Department、Employee、UseRecord 各以代號作為主鍵。
• 員工到職年可由員工編號前四碼取得，因此 2009 年到職可用 LEFT(eID, 4) = '2009' 判斷。
• 「主管不是該部門員工」是比較主管的員工編號與該部門員工的 deptID，不可直接假設主管必為任職於其所主管的部門。
• 刪除會議室前，須先刪除以該會議室為外鍵的借用紀錄，才能維持參照完整性。

（一）建立資料庫與資料表

解題方法： 依外鍵參照方向先建立被參照的資料表，再建立參照它們的資料表。Department 與 Employee 互相參照，因此先建立 Department，暫不加入 managerID 外鍵；建立 Employee 後，再以 ALTER TABLE 加上該外鍵。

以下採用支援 CREATE DATABASE、ALTER TABLE 與 CHECK 的 SQL 方言；VARCHAR 長度及數值範圍屬於合理的資料域設定。

CREATE DATABASE MeetingRoomDB;
USE MeetingRoomDB;

CREATE TABLE Department (
 dID VARCHAR(10) NOT NULL,
 name VARCHAR(100) NOT NULL,
 managerID CHAR(7) NOT NULL,
 CONSTRAINT PK_Department PRIMARY KEY (dID),
 CONSTRAINT UQ_Department_name UNIQUE (name)
);

CREATE TABLE Employee (
 eID CHAR(7) NOT NULL,
 name VARCHAR(100) NOT NULL,
 deptID VARCHAR(10) NOT NULL,
 expertise VARCHAR(200) NOT NULL,
 CONSTRAINT PK_Employee PRIMARY KEY (eID),
 CONSTRAINT FK_Employee_Department
 FOREIGN KEY (deptID) REFERENCES Department(dID),
 CONSTRAINT CK_Employee_eID
 CHECK (eID NOT LIKE '%[^0-9]%')
);

ALTER TABLE Department
ADD CONSTRAINT FK_Department_Manager
 FOREIGN KEY (managerID) REFERENCES Employee(eID);

CREATE TABLE Room (
 rID VARCHAR(10) NOT NULL,
 type VARCHAR(50) NOT NULL,
 capacity INTEGER NOT NULL,
 manageDept VARCHAR(10) NOT NULL,
 CONSTRAINT PK_Room PRIMARY KEY (rID),
 CONSTRAINT FK_Room_Department
 FOREIGN KEY (manageDept) REFERENCES Department(dID),
 CONSTRAINT CK_Room_capacity CHECK (capacity > 0)
);

CREATE TABLE UseRecord (
 roomID VARCHAR(10) NOT NULL,
 employeeID CHAR(7) NOT NULL,
 date DATE NOT NULL,
 startHour INTEGER NOT NULL,
 endHour INTEGER NOT NULL,
 purpose VARCHAR(200) NOT NULL,
 CONSTRAINT PK_UseRecord
 PRIMARY KEY (roomID, employeeID, date, startHour),
 CONSTRAINT FK_UseRecord_Room
 FOREIGN KEY (roomID) REFERENCES Room(rID),
 CONSTRAINT FK_UseRecord_Employee
 FOREIGN KEY (employeeID) REFERENCES Employee(eID),
 CONSTRAINT CK_UseRecord_hours
 CHECK (startHour >= 0 AND endHour <= 24 AND startHour < endHour)
);

此處將 name 設為候選鍵，假設部門名稱不得重複；UseRecord 以會議室、借用人、日期及開始時間組成複合主鍵，假設同一員工不會在同一會議室的同一天同一開始時間登錄兩筆紀錄。各主鍵均為候選鍵；外鍵則確保參照的部門、員工及會議室確實存在。

核心觀念

本題考查函數相依、候選鍵與 Boyce–Codd 正規形（BCNF），以及將正規化後的關聯資料表示成 JSON。

若關聯中的每一個非平凡函數相依 $X \to Y$，其決定項 $X$ 都是超鍵，該關聯即符合 BCNF。分解時，應依照資料所代表的業務規則辨認函數相依，再將不同主題的資料拆成獨立資料表。

題目未明列所有業務規則；以下依資料欄位及樣本採用合理假設：群組名稱決定群組人數與取餐地點；組員代號決定組員暱稱；餐盒代號決定餐盒名稱、供餐形式與價格；每筆訂購以「群組、組員、餐盒、日期、時段」識別，數量為該筆訂購的屬性。

解題方法

先以原始資料中的業務語意建立函數相依：

• 群組名稱 $\to$ 群組人數、取餐地點
• 組員代號 $\to$ 組員暱稱
• 餐盒代號 $\to$ 餐盒名稱、供餐形式、價格
• 群組名稱、組員代號、餐盒代號、日期、時段 $\to$ 數量

依上述假設，原始關聯的候選鍵為：

$$(\text{群組名稱},\text{組員代號},\text{餐盒代號},\text{日期},\text{時段})$$

各個決定項都未包含其他決定項所需的全部訂購資料，因此此複合鍵可識別一筆訂購。原始關聯中，群組名稱、組員代號與餐盒代號各自只決定部分非鍵欄位，並非超鍵；因此原始關聯不符合 BCNF。

將各種函數相依拆成獨立資料表，並以訂購資料表保存訂購事件：

1. 群組（群組名稱，群組人數，取餐地點） 主鍵：群組名稱。

2. 組員（組員代號，組員暱稱） 主鍵：組員代號。

3. 餐盒（餐盒代號，餐盒名稱，供餐形式，價格） 主鍵：餐盒代號。

4. 訂購（群組名稱，組員代號，餐盒代號，日期，時段，數量） 複合主鍵：群組名稱、組員代號、餐盒代號、日期、時段。群組名稱、組員代號及餐盒代號分別為外鍵。

在各表中，非平凡函數相依的決定項都是該表的主鍵，故各表符合 BCNF。

JSON 表示

以下以 JSON 物件中的陣列表示各資料表。原始資料有兩筆同一組訂購鍵的紀錄，數量分別為 1 與 2；由於「訂購」表的複合鍵不允許重複，將兩筆合併為數量 3，並將數量視為可加總的訂購量。

核心觀念

本題考查分散式大數據架構中「儲存、資源管理與運算」的分工，以及 Hadoop MapReduce 與 Spark 的資料處理方式。重點是不要把 Hadoop、HDFS、MapReduce、Spark 當成同一層級的工具：

• Hadoop：分散式大數據處理生態系與架構，核心常包含 HDFS、YARN 與 MapReduce。
• HDFS（Hadoop Distributed File System）：分散式檔案系統，負責跨多台機器儲存大型檔案，並提供容錯能力。
• MapReduce：分散式批次運算模型，將工作拆成 Map 與 Reduce 階段，在多台機器上平行處理。
• Spark：分散式運算引擎，支援批次、互動式查詢、串流及機器學習等工作；可讀取 HDFS 的資料，但不等於 HDFS，也不必綁定 Hadoop 的 MapReduce。

可用「Hadoop 是整體架構、HDFS 管儲存、MapReduce 管批次運算、Spark 管分散式運算」掌握四者的關係。

解題方法

依「角色 → 儲存方式 → 處理流程 → 異同」逐一說明，最後比較 MapReduce 與 Spark 的執行特性。如此可同時交代各名詞的定位，也能避免只列功能、未回答差異。

一、Hadoop 的角色及主要功能

Hadoop 是用來處理大量資料的分散式平台與生態系，將資料分散儲存在多台普通伺服器，並把運算工作切分後平行執行。典型架構中：

• HDFS 負責分散式儲存。
• YARN 負責叢集資源管理及工作排程。
• MapReduce 負責分散式批次運算。

因此，Hadoop 本身不是單一的檔案系統或運算演算法，而是由多個元件共同構成的架構。

二、HDFS 的角色及資料儲存方式

HDFS 將大型檔案切分成區塊，分散存放於叢集中的多台 DataNode，並透過複本機制提升容錯能力。其主要元件包括：

• NameNode：管理檔案系統中繼資料，例如檔案與區塊的對應關係、區塊位置。
• DataNode：實際儲存資料區塊，並依 NameNode 的管理執行資料讀寫。

讀取資料時，客戶端先向 NameNode 查詢區塊位置，再直接向相關 DataNode 讀取資料；NameNode 管理位置資訊，並不負責傳送檔案內容。若某台機器故障，系統可利用其他節點上的複本繼續提供資料。

HDFS 適合大型檔案、批次存取與高吞吐量工作；對大量小檔案及低延遲隨機存取則較不理想。

三、MapReduce 的角色及處理方式

MapReduce 將運算工作拆成兩個主要階段：

1. Map：讀取輸入資料並產生鍵值對 $(key, value)$。
2. Shuffle and Sort：依 key 將中間結果分組、傳送至對應的 Reduce 工作節點。
3. Reduce：對同一 key 的資料彙整、計算，產生輸出結果。