113 地方特考三等 資訊處理 資料庫應用 考古題詳解

核心觀念

本題考查關聯式資料表的綱要定義（Schema），以及如何選擇主鍵。CREATE TABLE 用來建立資料表，欄位定義包含欄位名稱與資料型態；主鍵用來唯一識別每一筆資料，並且不得為 NULL。

解題方法

依題目列出的五類資料建立五個欄位。學號是每位學生的識別碼，適合作為主鍵；姓名、系別、電話與地址可能重複或變動，不適合單獨作為主鍵。

CREATE TABLE Student (
 ID VARCHAR(20) NOT NULL,
 Name VARCHAR(100) NOT NULL,
 Major VARCHAR(100),
 Tel VARCHAR(30),
 Address VARCHAR(255),
 PRIMARY KEY (ID)
);

核心觀念

本題考查關聯式資料庫的基本 SQL 查詢，包括：

• 使用 WHERE 篩選符合條件的資料列。
• 以多個條件搭配 AND，同時限制系所與學期。
• 使用彙總函數 AVG、MAX，並以 GROUP BY 按系所分組。
• 判斷查詢所需資料表：教授資料來自 Professor；課程與學期資料來自 Teach。

解題方法

（一）資工系中薪水大於 10 萬元的教授名字

姓名與系所、薪水皆在 Professor 表中，因此只需查詢該表，以 WHERE 同時篩選系所及薪水，並用 SELECT 指定輸出姓名。

SELECT name
FROM Professor
WHERE department = '資工系'
 AND salary > 100000;

題目指定薪水是整數型態，所以門檻寫為 100000；「大於」使用嚴格比較運算子 >，不包含剛好 100000 元的教授。

（二）資管系教授於 1131 學期開設的課程

系所資訊在 Professor 表，而授課課程與學期在 Teach 表。兩表以 ID 對應，因此需依 ID 連接，再篩選資管系與 1131 學期，最後輸出課程名稱。

SELECT T.course
FROM Professor AS P
JOIN Teach AS T
 ON P.ID = T.ID
WHERE P.department = '資管系'
 AND T.semester = 1131;

semester 為整數型態，因此條件值寫成 1131，不加引號。此查詢會列出符合條件的授課紀錄；若同一門課有多筆符合紀錄，結果也會出現多次。

核心觀念

本題考查 ER model 的基本構成：

• 實體集合（Entity Set）
• 關係集合（Relationship Set）
• 屬性（Attribute）
• 主鍵（Primary Key）
• Mapping Cardinality，即 $1:1$、$1:N$、$M:N$
• 關係集合本身的屬性設計

題目中的「收聽」具有「收聽日期時間」，因此日期時間應屬於「收聽」關係，而不是會員或歌曲的屬性。

解題方法

先將題目中的名詞判斷為實體集合：

1. 歌曲：具有歌曲編號、歌名、歌手、作曲人、作詞人。
2. 會員：具有會員編號、姓名、地址、電話。
3. 類別：如抒情、搖滾、古典。
4. 收聽紀錄：表示會員在特定日期時間收聽某首歌曲。

歌曲與會員之間是多對多關係：

• 一位會員可以收聽多首歌曲。
• 一首歌曲也可以被多位會員收聽。
• 因此為 $M:N$。
• 收聽日期時間是此關係的屬性。

歌曲與類別之間也是多對多關係：

• 一首歌曲可以屬於多個類別。
• 一個類別可以包含多首歌曲。
• 因此為 $M:N$。

ER 圖文字表示

下列以矩形表示實體集合、菱形表示關係集合，底線表示主鍵。

┌────────────────────────────┐
│ 歌曲 │
│ PK 歌曲編號 │
│ 歌名 │
│ 歌手 │
│ 作曲人 │
│ 作詞人 │
└─────────────┬──────────────┘
 │ 0..N
 │
 ◆─────┴─────◆
 │ 歸類 │
 ◆─────┬─────◆
 │ 1..N
 │
┌─────────────┴──────────────┐
│ 類別 │
│ PK 類別編號 │
│ 類別名稱 │
└────────────────────────────┘

┌────────────────────────────┐
│ 會員 │
│ PK 會員編號 │
│ 姓名 │
│ 地址 │
│ 電話 │
└─────────────┬──────────────┘
 │ 0..N
 │
 ◆─────┴─────◆
 │ 收聽 │
 │ 收聽日期時間│
 ◆─────┬─────◆
 │ 0..N
 │
┌─────────────┴──────────────┐
│ 歌曲 │
│ PK 歌曲編號 │
│ 歌名 │
│ 歌手 │
│ 作曲人 │
│ 作詞人 │
└────────────────────────────┘

其中「歌曲」實體在圖中同時參與「歸類」與「收聽」兩個關係集合，代表同一個歌曲實體集合。

實體與關係說明

1. 歌曲實體集合 Song

屬性如下：

• 主鍵：歌曲編號
• 歌名
• 歌手
• 作曲人
• 作詞人

可表示為：

題目只要求記錄歌手、作曲人與作詞人，因此將其視為歌曲的屬性即可。

2. 會員實體集合 Member

屬性如下：

• 主鍵：會員編號
• 姓名
• 地址
• 電話

可表示為：

3. 類別實體集合 Category

題目列出抒情、搖滾、古典等類別。為使每個類別可被識別，設計：

• 主鍵：類別編號
• 類別名稱

可表示為：

4. 歸類關係集合 Classify

歌曲與類別為多對多關係：

若每首歌曲至少要有一個類別，歌曲端為全參與，基數為 $1..N$；類別端可暫時沒有歌曲，基數為 $0..N$。

其關聯表可表示為：

核心觀念

BCNF（Boyce-Codd Normal Form，鮑依斯－科德正規化）要求：對關係中的每一個非平凡函數相依 $X \to Y$，決定項 $X$ 都必須是超鍵。非平凡函數相依是指 $Y$ 不包含於 $X$。

題目已指定 Register 的主鍵為 $(ID, Title)$，因此這組合能唯一識別一筆修課紀錄。BCNF 的判斷重點，是檢查每個函數相依的決定項是否都能唯一識別整筆資料。

解題方法

由欄位意義可得以下函數相依：

• $ID \to Name$：同一學號對應同一學生姓名。
• $Title \to Credit$：同一課名對應該課的學分數。
• $(ID, Title) \to Grade$：同一學生修同一課，對應一筆成績。

題目指定主鍵為 $(ID, Title)$，所以它是超鍵；但 $ID$ 和 $Title$ 個別都不是超鍵，因為一位學生可修多門課，同一門課也可由多位學生修習。

因此，$ID \to Name$ 與 $Title \to Credit$ 的決定項都不是超鍵，違反 BCNF。表格中的例子也呈現此問題：同一學號的姓名重複出現，同一課名的學分數也重複出現。

正規化結果

依違反 BCNF 的函數相依拆分，得到三個資料表：

Student（學生）

Course（課程）