Assignment 3 - Normalization to 3NF

Finding the Candidate Key（尋找候選鍵）

We need to find a minimal set of attributes that can determine ALL other attributes.（找出能決定所有其他屬性的最小屬性集合。）

From FD2: ISBN → Title, Category, Price, Publication-Date, Publisher-Name
From FD4: Publisher-Name → Publisher-Address (via transitivity with FD2)
So ISBN alone determines: Title, Category, Price, Publication-Date, Publisher-Name, Publisher-Address
But ISBN alone cannot determine SSN or Author-Name (M:N relationship)
From FD3: SSN → Author-Name

Therefore, Candidate Key = {ISBN, SSN}（候選鍵 = {ISBN, SSN}）

Together, {ISBN, SSN} can determine all attributes in the relation (FD1).（{ISBN, SSN} 合在一起可以決定 relation 中的所有屬性。）

Step 1: First Normal Form / 1NF（第一正規化）

Check: Are all attributes atomic?（檢查：所有屬性是否都是原子值？）

The business rule states: "A book can have many authors, and an author can write many books."（業務規則：一本書可以有多個作者，一個作者也可以寫多本書。）

If the original table stores multiple authors per book row (repeating group), it violates 1NF.（如果原始表格在一筆記錄中存放多個作者（重複群組），就違反 1NF。）

✗ Violation（違反）: SSN and Author-Name may be multi-valued (a book has multiple authors).（SSN 和 Author-Name 可能是多值的 — 一本書有多個作者。）

Solution（解法）: The relation schema already includes SSN as part of the composite key {ISBN, SSN}, which means each row represents one (book, author) pair. This resolves the multi-valued issue.（Relation schema 已經把 SSN 納入複合鍵 {ISBN, SSN}，每一列代表一組「書-作者」配對，解決了多值問題。）

✓ 1NF satisfied（滿足 1NF）: With {ISBN, SSN} as the composite key, every attribute is atomic and each row is unique.（以 {ISBN, SSN} 為複合鍵，每個屬性都是原子值，每列都是唯一的。）

Book (ISBN, Title, Category, Price, Publication-Date, SSN, Author-Name, Publisher-Name, Publisher-Address)

Step 2: Second Normal Form / 2NF（第二正規化）

Check: Any partial dependencies?（檢查：是否有部分相依？）

A relation is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent on the entire candidate key.（2NF 要求：每個非鍵屬性必須完全功能相依於整個候選鍵，不能只依賴鍵的一部分。）

Candidate Key = {ISBN, SSN} (composite), so we check for partial dependencies:（候選鍵是複合鍵 {ISBN, SSN}，需要檢查部分相依：）

✗ Partial Dependency 1 / 部分相依 1 (FD2):
ISBN → Title, Category, Price, Publication-Date, Publisher-Name
These attributes depend on only part of the key (ISBN alone), not the full key {ISBN, SSN}.（這些屬性只依賴鍵的一部分 ISBN，而非完整的 {ISBN, SSN}。）

✗ Partial Dependency 2 / 部分相依 2 (FD3):
SSN → Author-Name
Author-Name depends on only part of the key (SSN alone), not the full key {ISBN, SSN}.（Author-Name 只依賴鍵的一部分 SSN，而非完整的 {ISBN, SSN}。）

Solution（解法）: Decompose to remove partial dependencies.（分解以消除部分相依。）

Note: Publisher-Address is transitively dependent (ISBN → Publisher-Name → Publisher-Address), so it stays with Publisher-Name in R1 for now.（注意：Publisher-Address 是遞移相依，先留在 R1，下一步再處理。）

R1 — Book (ISBN, Title, Category, Price, Publication-Date, Publisher-Name, Publisher-Address)
FD2 + FD4 (via transitivity): ISBN → all non-key attributes in R1（ISBN 決定 R1 中所有非鍵屬性）

R2 — Author (SSN, Author-Name)
FD3: SSN → Author-Name（SSN 決定 Author-Name）

R3 — Book_Author (ISBN, SSN)
Preserves the M:N relationship between books and authors. FK: ISBN references R1, SSN references R2.（保留書與作者的 M:N 關係。外鍵：ISBN 參照 R1，SSN 參照 R2。）

✓ 2NF satisfied（滿足 2NF）: No partial dependencies remain in any relation.（所有 relation 中不再有部分相依。）

Step 3: Third Normal Form / 3NF（第三正規化）

Check: Any transitive dependencies?（檢查：是否有遞移相依？）

A relation is in 3NF if it is in 2NF and no non-key attribute is transitively dependent on the primary key.（3NF 要求：沒有非鍵屬性透過其他非鍵屬性間接相依於主鍵。）

Check R1 (Book):

✗ Transitive Dependency / 遞移相依 (FD4):
ISBN → Publisher-Name → Publisher-Address
Publisher-Address depends on Publisher-Name, which is not a candidate key of R1. This is a transitive dependency.（Publisher-Address 相依於 Publisher-Name，而 Publisher-Name 不是 R1 的候選鍵，這是遞移相依。）

Check R2 (Author):

✓ SSN → Author-Name. No transitive dependency.（無遞移相依。）

Check R3 (Book_Author)（檢查 R3）:

✓ No non-key attributes. Automatically in 3NF.（沒有非鍵屬性，自動滿足 3NF。）

Solution（解法）: Decompose R1 to remove the transitive dependency.（分解 R1 以消除遞移相依。）

R1a — Book (ISBN, Title, Category, Price, Publication-Date, Publisher-Name)
FD2: ISBN → Title, Category, Price, Publication-Date, Publisher-Name. Publisher-Name is FK referencing R1b.（Publisher-Name 是外鍵，參照 Publisher relation。）

R1b — Publisher (Publisher-Name, Publisher-Address)
FD4: Publisher-Name → Publisher-Address（Publisher-Name 決定 Publisher-Address）

✓ 3NF satisfied（滿足 3NF）: No transitive dependencies remain in any relation.（所有 relation 中不再有遞移相依。）

Final Result: 3NF Relations（最終結果：3NF Relations）

Relation	Schema	Primary Key	Foreign Key	Based on FD
Book	ISBN, Title, Category, Price, Publication-Date, Publisher-Name	{ISBN}	Publisher-Name → Publisher	FD2
Author	SSN, Author-Name	{SSN}	—	FD3
Book_Author	ISBN, SSN	{ISBN, SSN}	ISBN → Book, SSN → Author	FD1
Publisher	Publisher-Name, Publisher-Address	{Publisher-Name}	—	FD4

Verification（驗證）

Lossless Join（無損連接）: The decomposition preserves all data — the original relation can be reconstructed by joining the four relations.（分解後的資料可以透過 JOIN 還原成原始 relation，不會遺失資料。）✓
Dependency Preservation（相依性保留）: All four FDs are preserved in the decomposed relations.（所有四個 FD 都保留在分解後的 relation 中。）✓
No Partial Dependencies（無部分相依）: Each non-key attribute is fully dependent on the entire primary key.（每個非鍵屬性都完全相依於整個主鍵。）✓
No Transitive Dependencies（無遞移相依）: No non-key attribute depends on another non-key attribute.（沒有非鍵屬性透過其他非鍵屬性間接相依於主鍵。）✓

Step 4: Boyce-Codd Normal Form (BCNF)

Check: Is every determinant a superkey?（檢查：每個決定因子是否都是 superkey？）

BCNF 的條件比 3NF 更嚴格：對於每個 non-trivial FD X → Y，X 必須是 superkey。

A relation is in BCNF if for every non-trivial functional dependency X → Y, X is a superkey.

Check each relation（逐一檢查）:

✓ Book (ISBN, Title, Category, Price, Publication-Date, Publisher-Name)
FD2: ISBN → Title, Category, Price, Publication-Date, Publisher-Name
ISBN is the PK (superkey). ✓（ISBN 是主鍵，也是 superkey。）

✓ Author (SSN, Author-Name)
FD3: SSN → Author-Name
SSN is the PK (superkey). ✓（SSN 是主鍵，也是 superkey。）

✓ Book_Author (ISBN, SSN)
No non-key attributes, no non-trivial FDs. ✓（沒有非鍵屬性，自動滿足 BCNF。）

✓ Publisher (Publisher-Name, Publisher-Address)
FD4: Publisher-Name → Publisher-Address
Publisher-Name is the PK (superkey). ✓（Publisher-Name 是主鍵，也是 superkey。）

✓ Conclusion（結論）: The resulting 3NF relations are also in BCNF, since every determinant in each relation is a superkey. No further decomposition is needed.
所有 3NF 的 relation 同時也滿足 BCNF，因為每個 relation 中的決定因子都是 superkey，不需要再進一步分解。