Example: Banking Database 國立東華大學試題: 資料庫管理 (每小題5分；滿分:105) 資訊管理學系2009.06.23 1. branch 2. customer 3. depositor 存款戶 Example: Banking Database 分公司 客戶(存款戶,貸款戶) 4. borrower 貸款戶 5. account 存款帳 6. loan 貸款帳

Question 1: Major System Components Explain the following major system components Source Module Modified DBRM Object Load Application Plan PL/I-Compiler Bind Pre-compiler Linkage Editor (Load Module) (Application Plan) Runtime Supervisor Data Manager Buffer Manager (Other) DB PL/I + SQL:(P.2- 20) EXEC SQL CREATE TABLE S ( S# CHAR(5), ......) EXEC SQL SELECT SNAME INTO :SNAME FROM S IF SQLCODE < 0 THEN ....... CALL CREATE( ......) CALL SELECT( ......) IF SQLCODE < 0 THEN ....... PL/I source parse-tree version of SQL request module a DB Compiler optimized machine codes of SQL statements in system Catalog 1 2 3 3' 4 1st time Pre-compiled Bind Buffer Manager DBRM

Question 2: Functional Dependency What is Functional Dependency ? Draw the Functional Dependency Diagram for table laon in page 1.

Question 3: Good and Bad Decomposition Consider transitive FD STATUS Suppose 1. CITY is the main office of the supplier. 2. STATUS is some factor of CITY S# CITY STATUS STATUS S# S# CITY CITY ② Decomposition B: ①Decomposition A: SC: SC: S# CITY STATUS S# CITY CS: CS: S# STATUS 討論把上圖拆成二個小 table 之好處 二種拆法，A 好? 還是 B 好? 為什麼?

Question 4: Normal Form FIRST S# STATUS CITY P# QTY S1 20 London P1 300 S1 20 London P2 200 S1 20 London P3 400 S1 20 London P4 200 S1 20 London P5 100 S1 20 London P6 100 S2 10 Paris P1 300 S2 10 Paris P2 400 S3 10 Paris P2 200 S4 20 London P2 200 S4 20 London P4 300 S4 20 London P5 400 FIRST S# STATUS CITY S1 20 London S2 10 Paris S3 10 Paris S4 20 London S5 30 Athens SECOND (in 2NF) S# P# QTY) S1 P1 300 S1 P2 200 S1 P3 400 S1 P4 200 S1 P5 100 S2 P1 300 S2 P2 400 S3 P2 200 S4 P4 300 S4 P5 400 SP (in 2NF) 從下面幾點討論把 FIRST 拆成 SECOND 及 SP 的好處: Update? S1 moves from London to Paris Insertion: (S5 30 Athens) Delete "S3 supplies P2 200", SECOND 還有缺點嗎? 它是3NF嗎? Why?

Question 5: Indexing (12%) Consider the Supplier table, S. 假設 Index 整個放在一個 page, S Table 每一筆 tuple 各放一個 page S1 S2 S3 S4 S5 Smith Jones Blake Clark Adams 20 10 30 London Paris Athens City-Index (index) S (indexed file) 要列印所有住在 “Athens” 的Supplier’s Name, 需多少Disk I/O? 解釋之。 要列印所有住在 “London” 的Supplier’s Name, 需多少Disk I/O? 解釋之。 要列印所有住在 “Taipei” 的Supplier’s Name, 需多少Disk I/O? 解釋之。 若插入 “S6 Yang 25 Hualien”, 重畫 S 及 City-Index 二 Tables.

Question 6: B+-tree (15%) index set - Sequence set 50 82 96 97 99 91 93 94 89 94 83 85 89 71 78 82 60 62 70 51 52 58 58 70 35 40 50 15 18 32 6 8 12 12 32 Question 6: B+-tree (15%) index set - Sequence set (with pointers to data records) What is the index set for? What is the sequence set for ? Write down the detailed procedures for searching “62”. 解釋 “with pointers to data records”? 若一個 node 可放 100 key 時，第三層共可放幾個 key?