資訊處理 108 年資料庫應用考古題

在現今大數據（Big Data）盛行的時代，已經有許多大數據平台提供各種 不同的服務，而在這些平台上運行的軟體多半包括有Data Warehouse、Data Mining、OLTP（Online Transaction Processing）、OLAP（Online Analytical Processing）、ETL（Extract, Transform, Load）等：  試說明此五個軟體的主要功用。（15 分）  試說明此五個軟體的相互運作關係，以達成提供大數據服務的目的。 （10 分）

請具體舉例說明關聯式資料庫模型（relational data model）的資料結構 （data structures）、資料整合限制（integrity constraints）與基本的關聯式 代數運算（relational algebra operations）。（15 分）

資料模式（Data Model）很多，就其特性大致可以歸類為三類：高階 （High-Level, 或觀念（Conceptual））資料模式、低階（Low-Level, 或 實體（Physical ））資料模式、表示（Representational, 或實作 （Implementation））資料模式。請分別說明這三類資料模式為何？並為 每一類資料模式各舉至少2 個資料模式的例子。（20 分）

某一資料庫系統內有以下資料表（實底線欄位為主鍵）： 課程 教師 課本 資料庫應用 王大明 CS 資料庫應用 王大明 BA 資料庫應用 李大華 CS 資料庫應用 李大華 BA 資料庫實務 王大明 CS 資料庫實務 王大明 CE 試說明此資料表為何符合BCNF 正規化形式、但不符合第四階正規化形 式？（15 分）應如何修改使之符合第四階正規化形式？（10 分）

公路資訊管理中心的資料庫系統所設計的關聯式表格（table）名稱、欄位 屬性（attribute）名稱與資料範例如以下4 個表格所示，用以記載各公路 的養護單位，並明瞭最新公路狀況與維護情形。（每小題5 分，共25 分） ROAD [公路] MAINTAIN [公路養護] 公路編號 公路名稱 總長 公路編號 養護單位 維護起點 維護終點 R_ID R_NAME R_LEN R_ID D_ID START END A010 台1 線 461.1 A010 A00 0 89 A030 台3 線 436.3 A010 B00 0 93 A170 台17 線 273.5 A030 A00 90 228 A190 台19 線 140.2 A170 B00 0 71 A170 E00 72 164 A190 E00 37 140 DEPT [養護單位] R_STUCK [通阻情況] 單位代碼 單位名稱 聯絡電話 公路編號 路況里程 狀況 發生日期 預計通車日 D_ID D_NAME TEL_NO R_ID KM STATUS E_DAY P_DAY A00 第一區工程處 02-86875111 A010 43 埋設水管工程 2018/06/11 2018/07/01 B00 第二區工程處 04-23715030 A030 117 邊坡土石滑落 2018/07/21 2018/07/22 E00 第五區工程處 05-2782861 A170 152 路面刨鋪施工 2018/11/20 2018/11/23 根據以上表格欄位名稱的語意與範例資料，請指出每一表格最適合的主 鍵（primary key）欄位集合與相關的外鍵（foreign key）欄位。 寫出建立資料表格MAINTAIN 綱要的SQL 語法（含主鍵與外鍵的指 定）。 寫出新增一筆資料（"A190", 65, " 路面積水", "2018/11/20", "2018/11/20"）至R_STUCK 表格的SQL 語法。 寫出查詢「"第一區工程處" 所養護的公路名稱與該公路所有的路況里 程及狀況」的SQL 語法。 寫出查詢「"台1 線" 101 公里處的養護單位名稱與聯絡電話」的SQL 語法。

某出版社要用關聯式資料模式建立可記錄其出版書籍的資料庫，設計了 包括可記錄書籍的資料表（Table）BOOK 及記錄作者的資料表 AUTHOR。BOOK 中包含BookId（書籍編號）、Title（書名）、PubDate （出版日期）、Category（分類）等屬性（Attribute）欄位，且BookId 是 其主鍵（Primary Key, PK）；AUTHOR 中包含AuthId（作者編號）、Name （姓名）、Birthday（出生年月日）、Tel（電話）等屬性欄位，且AuthId 是其主鍵。即資料庫中包含 BOOK（BookId, Title, PubDate, Category） AUTHOR（AuthId, Name, Birthday, Tel） 兩個資料表。請依序回答下列問題： 如果每本書可以有好幾位作者，每一位作者也可以出版好幾本書，請 對前項資料庫設計進行增修，使得出版社的資料庫可記錄每本書的作 者，也可記錄每個作者所出版的書。作答時可仿效題目的描述方式說 明您的設計，注意必須以英文命名明確表達資料表名稱及屬性欄位名 稱，並明確指出主鍵及外來鍵（Foreign Key, FK），外來鍵並須指出與 其對應的主鍵。（10 分） 承續並運用前一子題之資料庫增修，請寫出可查得屬於「’資料庫’」 分類（Category）的所有書籍之SQL 命令，此命令必須列出書籍的 BookId, Title 及作者人數，並依BookId 由小而大依序列出。（15 分） 請運用子題之資料庫增修寫出可查得曾經與名字叫「’張山峰’」的 作者共同著作過的作者之SQL 命令，列出其姓名及電話。（15 分）

當資料庫系統發生故障時，回復處理常採用交易記錄回復處理（Log-based Recovery）。試說明此種回復處理方式的原理以及運作過程。（25 分）

某資訊系統經資料庫系統分析師分析後獲得以下E-R model，有底線者 為主鍵屬性（primary key）： 請將以上E-R Model 轉成符合第二正規化（second normal form）格式 以上的關聯式表格綱要（relational schema），並明確表達出或畫出各 表格間所關聯的參考屬性以及各關聯綱要的主鍵與外鍵。（15 分） 假設實體C 的屬性中，另有以下二個功能相依（functional dependency） 存在： C3 →C2 ，C3 →C4 請將本題中所得到的關聯式表格綱要轉成符合第三正規化（third normal form）格式以上的表格綱要。（10 分）

某企業要建立自己的電子商務平台銷售自家商品，分析統整所有商品 後，將商品分為若干大分類，每一大分類又分為若干次分類，每一次分 類又分為若干細分類。假設此分類系統非常理想，每一個細分類號都是 唯一的，且不會有同一個細分類同屬於不同次分類的情形；每一個次分 類號也都唯一，且不會有同一個次分類同屬於不同大分類的情形，一個 商品也只會歸類為單一細分類，不會同時歸類到不同細分類。依據這樣 的分類系統，該公司的資料庫設計師設計出關聯式資料庫的資料表 PRODUCT 來儲存所有商品的資料，其中包含MajorCate（大分類號）、 SubCate（次分類號）、FineCate（細分類號）、SeqNo（商品編號）、ProdName （商品名稱）、Price（單價），且任何一個商品的MajorCate、SubCate、 FineCate 所代表的該商品的各級分類號都是明確的，SeqNo 是商品所屬 細分類之流水號，在同一細分類中不會重複，但在不同細分類間會有相 同的SeqNo。並以（MajorCate, SubCate, FineCate, SeqNo）為主鍵。也 就是資料表PRODUCT 為 PRODUCT（MajorCate, SubCate, FineCate, SeqNo, ProdName, Price） 請檢視此一設計，指出其設計錯誤或不良之處，並在不更動屬性設計的 前提下修正之。（20 分）

某一資料庫系統內有以下三個資料表： 教師資料表（實底線欄位為主鍵） 教師編號 姓名 科系 P001 王大明 CS P002 李大華 BA 課程資料表（實底線欄位為主鍵） 課程編號 名稱 學分數 C101 資料庫應用 3 C102 網路概論 3 C103 多媒體導論 4 修課資料表（實底線欄位為主鍵） 教師編號 課程編號 學生編號 教室 時間 成績 P001 C101 S2003 A513 13：30 86 P002 C103 S6121 A226 14：30 72 P002 C103 S6186 A226 14：30 80  試寫出一個SQL 指令，建立一個視界（View），以顯示每位教師的開 課資料以及修習這些課程的學生資料。（10 分）  試寫出一個SQL 指令，建立一個統計性摘要視界（View），以顯示每 位教師的開課數以及開課總學分數。（15 分）

請分別就系統目標、對象資料、常用的資料模型、資料處理相 關運算、系統使用者等項目，製表具體說明資料庫管理系統（Database management system）和資料倉儲（Data warehouse）系統之間有何不同。 （15 分） A B C R1 R2 R4 B1 B2 A1 B3 A2 C1 C2 D2 D1 1 N M N N M R2a C3 C4 D R3a C5 實體(entity) 屬性(attribute) 部分參與 (partial participation) 全部參與 (total participation) 多值屬性 (multivalued attribute) 關係 (relationship) R3 R4a N 1

兩階段鎖定（Two-Phase Locking）技術可以用來做並行控制（Concurrency Control），請詳細說明符合兩階段鎖定協定的交易應遵循的規範為何？ 時程（Schedule）是指多個交易（Transaction）並行（Concurrency）執 行時，各交易內的操作（Operation）間的執行順序。請以此觀點說明若 所有交易都遵循兩階段鎖定協定撰寫，則這些交易並行執行時，時程必 定是什麼時程？並說明為何遵循這種時程執行可以達到並行控制的目 的？（20 分）

有關交易管理中並行控制（concurrency control）的主要目的，是維持各 別不同交易在共時情況下，在完成後仍然保有可循序性（serializability）， 用以保證交易的正確無誤。（每小題10 分，共20 分） 請說明何謂交易的可循序性？並用以下交易1 與交易2 二筆交易所進 行的動作，具體舉例在何種共時狀況下會違反可循序性，造成問題。 請您提出一種控制機制可以保證共時交易的可循序性。並請使用該控 制機制，以交易1 與交易2 二筆交易為例，簡要說明如何達成共時交 易的可循序性。 交易1 交易2 read(x) read(x) x = x + 1000 if x >=100 write(x) x = x - 100 Commit write(x) Commit