資訊處理 105 年高等資料庫設計考古題

若某公司業務資料庫中包含了客戶、訂單、訂單明細、品項等資料表（Tables）如下： CUSTOMER(CustNum, Name) ORDER(OrderNum, CustNum, OrderDate, Amt) ORDER_ITEM(OrderNum, ItemNum, Qty) ITEM(ItemNum, Name, UnitPrice) 各資料表中的屬性分別為： CUSTOMER：CustNum:客戶代碼、Name:客戶名稱，主鍵（Primary Key, PK）:CustNum ORDER：OrderNum:訂單號碼、CustNum:客戶代碼、OrderDate:訂單日期、 Amt:訂單金額，主鍵:OrderNum ORDER_ITEM：OrderNum：訂單號碼、ItemNum:品項代碼、Qty:數量， 主鍵:(OrderNum, ItemNum) ITEM：ItemNum:品項代碼、Name:品名、UnitPrice:單價，主鍵:ItemNum 且有以下外來鍵（Foreign Key, FK）Æ主鍵（PK）的關係： ORDER.CustNumÆ CUSTOMER.CustNum ORDER_ITEM.OrderNumÆ ORDER.OrderNum ORDER_ITEM. ItemNum Æ ITEM. ItemNum 假設各屬性（attribute）之資料型態都已有適當定義。請寫出SQL-99（或Qracle、 SQL-Server、MySQL…，但須註明）命令完成以下查詢： 總購買金額超過100 萬的客戶之客戶名稱及其總購買金額。（10 分） 2016 年9 月份每種品項的總銷售量，列出各品項的品名及銷售數量（Qty）。（10 分）

若資料庫中包含下列兩個資料表（Tables）： R(Rid, RA1) S(Sid, SA1, FRid) Rid 是R 的主鍵（PK），Sid 是S 的主鍵，且FRid 是S 中的外來鍵（FK），必須參照 到R 的Rid。 關於實體完全限制（Entity Integrity Constraint）及參照完全限制（Referential Integrity Constraint），請分別就對R 或S 進行資料新增（insert）、刪除（delete）、修改（update） 是否會違反這兩個限制（Constraint），以回答下列問題。作答時須針對新增、刪除、 修改分別作答，如果不會違反限制，以「不會違反」字樣作答，如果會違反，須運 用R 或S 中的屬性具體說明會違反限制的狀況。 對R 做新增（insert）、刪除（delete）、修改（update）是否會違反實體完全限制（Entity Integrity Constraint）。（3 分） 對R 做新增（insert）、刪除（delete）、修改（update）是否會違反參照完全限制 （Referential Integrity Constraint）。（7 分） 對S 做新增（insert）、刪除（delete）、修改（update）是否會違反實體完全限制（Entity Integrity Constraint）。（3 分） 對S 做新增（insert）、刪除（delete）、修改（update）是否會違反參照完全限制 （Referential Integrity Constraint）。（7 分） （請接第二頁） 105年公務人員高等考試一級暨二級考試試題 代號：22740 全三頁 第二頁 等 別：高考二級 類 科：資訊處理 科 目：高等資料庫設計

下列ER 圖（ER-Diagram）採用Chen style 表示法（ER-Diagram 最原始表示法）繪製， 實體型態 Entity Type 屬性 Attribute 關係型態 Relationship Type 多值屬性 Multivalued Attribute 部分參與 Partial Participation 完全參與 Total Participation 請改用（min, max）表示法表示其中的結構限制（Structural Constraints），以重新 繪製此ER 圖。（10 分） 若欲依據此綱目（Schema）進行邏輯設計（Logical Design），請將其轉換成最適當 （請仔細觀察ER 圖）的關聯式綱目（Relational Schema），並將轉換結果以關聯式 綱目圖（Relational Schema Diagram）繪出，圖中須表達主鍵（PK）及外來鍵（FK）。 （10 分）

正規化（Normalization）在關聯式資料庫（Relational Database）設計時是很重要的工作。 請說明未達到特定正規形式（Normal Form）的資料庫綱目（Database Schema）會 導致什麼問題？（5 分） 何謂二階正規形式（Second Normal Form, 2NF）、三階正規形式（Third Normal Form, 3NF）、 Boyce-Codd 正規形式（Boyce-Codd Normal Form, BCNF）？請用廣義定義（General Definition）說明，也就是在定義是否符合正規形式時，所有的鍵（Key）地位均等， 主鍵（PK）相較於其他的鍵並無特殊之處。（5 分） 請舉一個符合二階正規形式但不符合三階正規形式的關聯（Relation）例子，作答時 畫出關聯式綱目圖，並註明所有的功能相依（Functional Dependency）關係，各屬性 名稱用英文字母命名即可。（5 分） 請舉一個符合三階正規形式但不符合Boyce-Codd 正規形式的關聯例子，作答時畫出關 聯式綱目圖，並註明所有的功能相依關係，各屬性名稱用英文字母命名即可。（5 分） Course Department Teacher CanTeach Offer Belongs Manages Cid Name Tid Name Did Name Location M N Start N 1 N 1 1 1 （請接第三頁） 105年公務人員高等考試一級暨二級考試試題 代號：22740 全三頁 第三頁 等 別：高考二級 類 科：資訊處理 科 目：高等資料庫設計 （請接背面）

考慮下列交易（Transaction）： ReadLock(X); ReadItem(X); UnLock(X); WriteLock(Y); ReadItem(Y); Y := Y + X; WriteItem(Y); UnLock(Y); 此交易不符合兩階段鎖定（Two-Phase Lock, 2PL）協定（Protocol），請在不改變交 易邏輯的條件下修改此交易，使其符合兩階段鎖定協定。（10 分） 在兩階段鎖定協定系統下，眾多交易同時（Concurrent）執行時，若有交易不遵循 兩階段鎖定協定，將導致什麼後果？（10 分）