113 關務特考三等 資訊處理(選試英文) 資料庫應用 考古題詳解

核心觀念

本題考查以需求反推 EER（Enhanced Entity-Relationship）資料模型，並將其轉換為符合 BCNF 的關聯式資料表。設計時須明確呈現：

• 實體與關係：旅客、航空公司、飛行器、航班、機場、飛行紀錄等。
• 主鍵與外鍵：主鍵唯一識別資料列；外鍵連結相關資料表。
• 最小／最大基數：說明一筆資料最少及最多可參與幾筆關係，例如 0..N、1..1。
• BCNF：對每個非平凡函數相依 $X \to Y$，決定因素 $X$ 必須是超鍵。將不同主題拆成獨立資料表，避免把製造商、航空公司、航班、旅客等資料反覆存放。
• EER 與 OO 概念：可用「人員」作為超型別，旅客及機組員作為子型別；子型別繼承人員的共通屬性。航空器則可視為具有型號、容量及服役期間等屬性的物件。

題目未提供個別旅客的機票費用、行李轉盤作業紀錄及飛行器銷毀日期等來源細節。以下以「每位旅客每個航班有一筆訂位／搭乘紀錄」、「行李轉盤卸載以航班及轉盤為紀錄單位」及「飛行器銷毀日期可為空值，未銷毀即代表仍在役」為建模假設。

解題方法

先依查詢需求找出必須保存的資料，再拆成穩定的主檔與會隨航班或日期改變的交易紀錄：

1. 旅客、機組員、製造商、航空公司、機場及飛行器屬於主檔。
2. 航班保存起降時間與機場；旅客搭乘、機組員執勤、行李卸載則各自成為關聯資料。
3. 容量由飛行器決定，不能放在航班或旅客搭乘紀錄中；票價屬旅客搭乘該航班的紀錄，放在搭乘表中。
4. 將性別、年齡、旅客國籍及航班國籍等比例所需資料保留，比例與統計結果可由查詢計算，不另存為欄位。

EER 模型與資料表

以下 PK 為主鍵，FK 為外鍵；0..N 表示零至多筆，1..1 表示恰好一筆。

1. 人員超型別與子型別

• 人員 Person person_id PK、姓名、聯絡電話、email、性別、出生日期、國籍代碼 FK

• 旅客 Passenger person_id PK, FK → Person.person_id

• 機組員 CrewMember person_id PK, FK → Person.person_id

• 駕駛員 Pilot person_id PK, FK → CrewMember.person_id

EER 的型別關係為 Person 超型別，Passenger、CrewMember 為子型別；Pilot 是 CrewMember 的子型別。旅客與機組員是否可重疊，題目未規定，故不限制兩者互斥。

2. 國家、航空公司與製造商

• 國家 Country country_code PK、country_name

• 航空公司 Airline airline_id PK、名稱、註冊日期、國籍代碼 FK → Country.country_code

• 製造商 Manufacturer manufacturer_id PK、名稱、國籍代碼 FK → Country.country_code

航空公司與製造商的國籍各由外鍵指向國家，避免在每筆航班或飛行器資料中重複儲存國名。

3. 飛行器與型號

• 飛行器型號 AircraftModel model_id PK、manufacturer_id FK → Manufacturer.manufacturer_id、型號名稱

候選鍵：(manufacturer_id, 型號名稱)。

• 飛行器 Aircraft aircraft_id PK、model_id FK → AircraftModel.model_id、airline_id FK → Airline.airline_id、出廠日期、載客數、銷毀日期（可空）

每架飛行器恰屬於一家航空公司、由一個製造商的特定型號製造；題目並明定載客數出廠後不變，因此容量放在 Aircraft，不放在航班資料中。

4. 機場與航班

• 機場 Airport airport_id PK、名稱、國籍代碼 FK → Country.country_code

• 航班 Flight flight_id PK、航班編號、aircraft_id FK → Aircraft.aircraft_id、起飛機場代碼 FK → Airport.airport_id、抵達機場代碼 FK → Airport.airport_id、起飛日期時間、抵達日期時間

flight_id 用來識別一次實際執行的航班；航班編號可在不同日期重複，不能單獨作為實際航班的主鍵。

5. 旅客搭乘與機組員執勤

• 搭乘紀錄 FlightPassenger flight_id PK, FK → Flight.flight_id、passenger_id PK, FK → Passenger.person_id、座位號、票價

複合主鍵為 (flight_id, passenger_id)，表示同一旅客在同一航班至多一筆搭乘紀錄。座位號 應對同一航班唯一，可另設唯一限制 (flight_id, 座位號)。

• 機組員執勤 FlightCrew flight_id PK, FK → Flight.flight_id、crew_id PK, FK → CrewMember.person_id、職務

職務 記錄正駕駛、副駕駛等航班上的職務；以 (flight_id, crew_id) 識別某機組員在某航班的執勤紀錄。若須保證每班恰有一位正駕駛及一位副駕駛，另對各職務加上每航班唯一限制。

6. 行李轉盤卸載

• 行李卸載 BaggageUnload flight_id PK, FK → Flight.flight_id、airport_id PK, FK → Airport.airport_id、carousel_no PK、行李數

核心觀念

本題考查多表連接、日期範圍篩選、分組彙總、HAVING 條件、排序與新增資料時的參照完整性。

• WHERE 先篩選個別資料列；GROUP BY 再依指定欄位分組；HAVING 篩選分組後的結果。
• 點播次數以 COUNT(*) 計算。歌曲資料要連接點播紀錄，發行者資料要透過歌曲中的 (nation, publisher_ID) 連接 Publisher。
• 第四小題新增點播紀錄前，必須先確保會員與歌曲都已存在，否則會違反外鍵限制。

解題方法

（一）查詢 2023 年點播率最高前兩名的英文歌曲

先以日期條件限縮 2023 年點播紀錄，再連接歌曲與發行者資料；依歌曲及發行者分組計算點播次數，最後依次數遞減排序並取前兩筆。

SELECT TOP 2
 s.name AS song_name,
 p.name AS publisher_name,
 s.publish_year AS publish_year,
 COUNT(*) AS demand_count
FROM OnDemand_Record AS r
JOIN Song AS s
 ON r.song_ID = s.song_ID
JOIN Publisher AS p
 ON s.nation = p.nation
 AND s.publisher_ID = p.company_ID
WHERE r.date_time >= '2023-01-01'
 AND r.date_time < '2024-01-01'
 AND s.language = 'English'
GROUP BY s.song_ID, s.name, p.name, s.publish_year
ORDER BY COUNT(*) DESC;

以半開區間篩選日期，能涵蓋 2023 年整年，也不會漏掉 12 月 31 日含時間的紀錄。TOP 2 取排序後的前兩筆；此寫法使用 SQL Server 語法。若採用支援 LIMIT 的資料庫，則可將 TOP 2 改為 LIMIT 2。

（二）查詢指定期間 Universal 的點播歌曲

題目期間為 2023 年第四季至 2024 年第三季，範圍是 2023/10/01 至 2024/09/30。依發行者名稱及點播日期篩選後，按歌曲分組；同一發行年份的點播歌曲數須大於 1，因此分組與 HAVING 條件以發行年份為準。

核心觀念

本題考查函數相依與 BCNF 正規化。若關係中的每一個非平凡函數相依 $X \to Y$，其決定項 $X$ 都是超鍵，該關係即符合 BCNF。

題目未明示函數相依與候選鍵，以下依據欄位意義採用這些假設：

• 技工編號決定技工姓名：技工編號 $\to$ 技工姓名。
• 客戶電話決定客戶姓名：客戶電話 $\to$ 客戶姓名。
• 車號、日期與時間合起來識別一筆保修紀錄：$(車號, 日期, 時間)$ 為保修紀錄的主鍵。
• 每筆保修紀錄只對應一位技工、一位客戶及一項保修內容。

解題方法

原始資料把技工、客戶與保修紀錄放在同一張表。技工姓名會隨技工編號重複，客戶姓名會隨客戶電話重複；若直接存於保修紀錄表，修改資料時容易發生更新異常。因此將技工與客戶資料分開，保修紀錄則以主鍵連結兩者。

正規化後的資料表如下：

RepairRecord 的複合主鍵為 $(car\_no, repair\_date, repair\_time)$。依據上述假設，技工姓名只由 mechanic_id 決定，客戶姓名只由 customer_phone 決定；因此兩項名稱資料分別移至各自的主檔。RepairRecord 中其他欄位由其複合主鍵決定，三張表均符合 BCNF。

正規化後資料：JSON

{
 "Technician": [
 { "mechanic_id": "M001", "mechanic_name": "Tom" },
 { "mechanic_id": "M002", "mechanic_name": "Mark" },
 { "mechanic_id": "M003", "mechanic_name": "Mark" }
 ],
 "Customer": [
 { "customer_phone": "90001", "customer_name": "Lisa" },
 { "customer_phone": "90002", "customer_name": "Janny" },
 { "customer_phone": "90003", "customer_name": "Janny" }
 ],
 "RepairRecord": [
 {
 "car_no": "C001",
 "repair_date": "2023-01-15",
 "repair_time": "10:00",
 "mechanic_id": "M001",
 "customer_phone": "90001",
 "amount": 8000,
 "service_content": "Wheel"
 },
 {
 "car_no": "C001",
 "repair_date": "2024-01-15",
 "repair_time": "13:00",
 "mechanic_id": "M001",
 "customer_phone": "90001",
 "amount": 2500,
 "service_content": "Battery"
 },
 {
 "car_no": "C001",
 "repair_date": "2024-02-15",
 "repair_time": "10:00",
 "mechanic_id": "M001",
 "customer_phone": "90002",
 "amount": 30000,
 "service_content": "Paint"
 },
 {
 "car_no": "C002",
 "repair_date": "2024-02-15",
 "repair_time": "10:00",
 "mechanic_id": "M002",
 "customer_phone": "90002",
 "amount": 5000,
 "service_content": "Paint"
 },
 {
 "car_no": "C003",
 "repair_date": "2024-03-15",
 "repair_time": "11:00",
 "mechanic_id": "M003",
 "customer_phone": "90001",
 "amount": 12000,
 "service_content": "Wheel"
 },
 {
 "car_no": "C001",
 "repair_date": "2024-03-15",
 "repair_time": "10:00",
 "mechanic_id": "M002",
 "customer_phone": "90003",
 "amount": 2500,
 "service_content": "Battery"
 }
 ]
}

正規化後資料：XML

核心觀念

兩個不同交易對同一資料項目執行操作，且至少一個操作是寫入，就構成衝突。若衝突操作的先後順序為 $T_i$ 在 $T_j$ 之前，便在優先圖中加入邊 $T_i \to T_j$。

若優先圖無環，排程具備衝突循序性；拓樸排序所得的交易順序，就是與原排程衝突等價的循序排程。若圖中有環，則不具備衝突循序性。

解題方法與執行排程

依題目給定的時間順序，排程如下：

逐一依資料項目整理衝突順序：

• 資料項目 $x$：$T2:R(x)$ 先於 $T1:W(x)$，得 $T2 \to T1$；$T1:W(x)$ 先於 $T3:R(x),W(x)$，得 $T1 \to T3$；$T3:R(x),W(x)$ 先於 $T2:W(x)$，得 $T3 \to T2$。此外，$T2:R(x)$ 先於 $T3:W(x)$，得 $T2 \to T3$；$T1:W(x)$ 先於 $T2:W(x)$，得 $T1 \to T2$。
• 資料項目 $y$：$T2:R(y)$ 先於 $T4:W(y)$，得 $T2 \to T4$；$T4:W(y)$ 先於 $T3:R(y),W(y)$，得 $T4 \to T3$；$T3:R(y),W(y)$ 先於 $T2:W(y)$，得 $T3 \to T2$。