ExampleBankingDatabase

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

2、IM.NDHU, 20092009 Final Test-2 Question 1: Major System ComponentsSourceModuleModifiedSourceModuleDBRMObjectModuleLoadModuleApplicationPlanPL/I-CompilerBindPre-compilerLinkageEditor(Load Module)(Application Plan)Runtime SupervisorData ManagerBuffer Manager(Other)DBPL/I + SQL:(P.2- 20)EXEC SQL CREATE

3、 TABLE S ( S# CHAR(5), .)EXEC SQL SELECT SNAME INTO :SNAME FROM SIF SQLCODE 0 THEN .CALL CREATE( .)CALL SELECT( .)IF SQLCODE 0 THEN .PL/I sourceparse-tree versionof SQLrequestmodulea DB Compileroptimized machinecodes of SQL statements in system Catalog123341st time1) Pre-compiled2) Bind3) Buffer Man

4、ager4) DBRM nExplain the following major system componentsIntroduction to Database SystemWei-Pang Yang, IM.NDHU, 20092009 Final Test-3 Question 2: Functional Dependency5)What is Functional Dependency ?6)Draw the Functional Dependency Diagram for table laon in page 1.Introduction to Database SystemWe

5、i-Pang Yang, IM.NDHU, 20092009 Final Test-4 Question 3: Good and Bad DecompositionnConsiderS#CITYSTATUStransitive FDS#CITYCITYSTATUSDecomposition A:SC:CS:S#CITYS#STATUS Decomposition B:SC:CS:S#STATUSCITYS#STATUSCITYSuppose 1. CITY is the main office of the supplier. 2. STATUS is some factor of CITY7

6、)討論把上圖拆成二個小 table 之好處8)二種拆法，A 好? 還是 B 好? 為什麼? Introduction to Database SystemWei-Pang Yang, IM.NDHU, 20092009 Final Test-5 Question 4: Normal FormS# 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

7、 S2 P1 300 S2 P2 400 S3 P2 200 S4 P4 300 S4 P5 400SP (in 2NF) 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

8、 S4 20 London P5 400FIRST從下面幾點討論把 FIRST 拆成 SECOND 及 SP 的好處:9)Update? S1 moves from London to Paris10) Insertion: (S5 30 Athens) 11) Delete S3 supplies P2 200,12) SECOND 還有缺點嗎? 它是3NF嗎? Why?Introduction to Database SystemWei-Pang Yang, IM.NDHU, 20092009 Final Test-6nConsider the Supplier table, S.n假設

9、Index 整個放在一個 page, S Table 每一筆 tuple 各放一個 pageS1S2S3S4S5Smith Jones Blake Clark Adams2010302030LondonParis Paris London Athens City-Index (index) Athens London London Paris Paris S (indexed file) Question 5: Indexing (12%) 13) 要列印所有住在 “Athens” 的Suppliers Name, 需多少Disk I/O? 解釋之。14) 要列印所有住在 “London” 的

10、Suppliers Name, 需多少Disk I/O? 解釋之。15) 要列印所有住在 “Taipei” 的Suppliers Name, 需多少Disk I/O? 解釋之。16) 若插入 “S6 Yang 25 Hualien”, 重畫 S 及 City-Index 二 Tables.Introduction to Database SystemWei-Pang Yang, IM.NDHU, 20092009 Final Test-7 Question 6: B+-tree (15%) 50 8296 97 9991 93 9489 9483 85 8971 78 8260 62 7051 52 5858 7035 40 5015 18 326 8 1212 32 index set Sequence set (with pointers to data records)-17) What is the index set for?18) Wha