Example: Banking Database

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

2. 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 ....... Source Module CALL CREATE( ......) CALL SELECT( ......) IF SQLCODE < 0 THEN ....... Modified Source Module Pre-compiler DBRM request module parse-tree version of SQL PL/I source PL/I-Compiler a DB Compiler Bind optimized machine codes of SQL statements Object Module Application Plan in system Catalog Linkage Editor (Load Module) (Application Plan) Runtime Supervisor Data Manager Buffer Manager (Other) 2 3 1st time 3' 1 Load Module 4 DB Question 1: Major System Components Explain the following major system components • Pre-compiled • Bind • Buffer Manager • DBRM

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

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

5. 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 SP (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 SECOND (in 2NF) S# STATUS CITY S1 20 London S2 10 Paris S3 10 Paris S4 20 London S5 30 Athens Question 4: Normal Form 從下面幾點討論把 FIRST 拆成 SECOND 及 SP 的好處: Update? S1 moves from London to Paris Insertion: (S5 30 Athens) Delete "S3 supplies P2 200", SECOND 還有缺點嗎? 它是3NF嗎? Why?

6. S (indexed file) City-Index (index) Athens S1 London Smith 20 London S2 Paris Jones 10 London S3 Paris Blake 30 Paris S4 London 20 Clark Paris Athens S5 Adams 30 Question 5: Indexing(12%) • Consider the Supplier table, S. • 假設 Index 整個放在一個 page, S Table 每一筆 tuple 各放一個 page 要列印所有住在 “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.

7. 96 97 99 89 94 91 93 94 83 85 89 71 78 82 50 82 58 70 60 62 70 51 52 58 35 40 50 12 32 15 18 32 6 8 12 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?