Example: Banking Database

Presentation on theme: "Example: Banking Database"— Presentation transcript:

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

2 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

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

4 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 好? 為什麼?

5 Question 4: Normal Form FIRST S# STATUS CITY P# QTY
S London P S London P S London P S London P S London P S London P S Paris P S Paris P S Paris P S London P S London P S London P FIRST S# STATUS CITY S London S Paris S Paris S London S Athens SECOND (in 2NF) S# P# QTY) S P S P S P S P S P S P S P S P S P S P SP (in 2NF) 從下面幾點討論把 FIRST 拆成 SECOND 及 SP 的好處: Update? S1 moves from London to Paris Insertion: (S Athens) Delete "S3 supplies P2 200", SECOND 還有缺點嗎? 它是3NF嗎? Why?

6 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.

7 Question 6: B+-tree (15%) index set - Sequence set
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?