2012FinalTest-

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

2、uestion 1: Terms Explanation (20%)1)Logical Database Design2)Update Anomalies3)Minimize the number of Disk Access4)Weak Entity5)Draw the Functional Dependency Diagram for table account in page 1.6)Total participator vs. Partial participator in ER-Model7)Many-to-Many Relationship Set8)blob: binary la

3、rge object9)foreign key (branch-name) references branch on delete cascade10) Generalization vs. Specialization2012 Final Test-2Introduction to Database SystemWei-Pang Yang, IM.NDHU, 2010Question 2: Real-world vs. E-R Model vs. Tables (10%) 1. branch 2. customern客戶(存款戶,貸款戶) 3. depositorn存款戶n分公司Semant

4、ic Data Model:Entity-Relationship (E-R) Data ModelThe real-world enterprisen 試討論 Real-world, E-R Model , 及Tables 之關係2012 Final Test-3Introduction to Database SystemWei-Pang Yang, IM.NDHU, 2010 Question 3: 1NF 2NF (10%) S# STATUS CITY S1 20 London S2 10 Paris S3 10 Paris S4 20 London S5 30 Athens SEC

5、OND 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 400SP 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

6、10 Paris P2 200 S4 20 London P2 200 S4 20 London P4 300 S4 20 London P5 400FIRST從下面幾點討論把 FIRST 拆成 SECOND 及 SP 的好處:1)Update? S1 moves from London to Paris2)Insertion: (S5 30 Athens) 3)Delete S3 supplies P2 200,2012 Final Test-4Introduction to Database SystemWei-Pang Yang, IM.NDHU, 2010 Question 4: 2N

7、F 3NF (10%) S# STATUS CITY S1 20 London S2 10 Paris S3 10 Paris S4 20 London S5 30 Athens SECOND (in 2NF)1)SECOND 還有缺點嗎? 2)SECOND是3NF嗎? Why?從下面幾點討論把 SECOND拆成 CS及 SC 的好處3)Update4)Insertion5)Delete CITY STATUS Athens 30 London 20 Paris 10 Rome 50 CS (in 3NF) SC (in 3NF) S# CITY S1 London S2 Paris S3 P

8、aris S4 London S5 Athens 2012 Final Test-5Introduction to Database SystemWei-Pang Yang, IM.NDHU, 2010nConsider the Supplier table, S.n假設 Index 整個放在一個 page, S Table 每一筆 tuple 各放一個 pageS1S2S3S4S5Smith Jones Blake Clark Adams2010302030LondonParis Paris London Athens City-Index (index) Athens London Lon

9、don Paris Paris S (indexed file) Question 5: Indexing (10%) 1)要列印所有住在 “Athens” 的Suppliers Name, 需多少Disk I/O? 解釋之。2)要列印所有住在 “London” 的Suppliers Name, 需多少Disk I/O? 解釋之。3)要列印所有住在 “Taipei” 的Suppliers Name, 需多少Disk I/O? 解釋之。4)若插入 “S6 Yang 25 Hualien”, 重畫 S 及 City-Index 二 Tables.5)如果沒有City-Index, 要列印所有住在

10、“London” 的Suppliers Name, 需多少Disk I/O? 解釋之。2012 Final Test-6Introduction to Database SystemWei-Pang Yang, IM.NDHU, 2010 Question 6: B+-tree (10%) 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)-1)B+ tre

11、e 如何提供 “fast direct access” 的服務? 以找 key= “62”為例說明2)B+ tree 如何提供 “fast sequential access” 的服務?3)若一個 node 可放 100 key 時，第三層共可放幾個 key?4)Sequence set (with pointers to data records) 是什麼意思?2012 Final Test-7Introduction to Database SystemWei-Pang Yang, IM.NDHU, 2010nAccording to your final project as “Desi

12、gn and implement a useful database application system”1)What is the title of your project?2)Names of members in your team.3)Draw the E-R Diagram of your application system. (You can just give a similar diagram.)4)Draw a table to show one relation used in the system5)Check your answer in d) to see wh

13、ether it is in the 1NF? Why? Please answer “why” by using the definition of the 1NF.6)Same as e) to see whether it is in the 2NF? Why?7)Same as e) to see whether it is in the 3NF? Why?Question 7: About Your Final Term Project (10%)2012 Final Test-8Introduction to Database SystemWei-Pang Yang, IM.NDH

14、U, 2010Host Language+ DSLHost Language+ DSLHost Language+ DSLHost Language+ DSLHost Language+ DSLUser A1User A2User B1User B2User B3External View # &External ViewBExternal/conceptualmapping AConceptualViewExternal/conceptualmapping BConceptual/internalmappingStored database (Internal View)Databa

15、semanagementsystem dictionary(DBMS) e.g. system catalogDBAStoragestructuredefinition(Internalschema)ConceptualschemaExternalschemaAExternalschemaB(Build andmaintainschemasandmappings)#& DSL (Data Sub Language)C, Pascale.g. SQLSQL123123.100Question 8: 儘量解釋右圖儘量解釋右圖 (10%)第8 、9 、10任選二題做答2012 Final T

16、est-9Introduction to Database SystemWei-Pang Yang, IM.NDHU, 2010Major System Components: DB2SourceModuleModifiedSourceModuleDBRMObjectModuleLoadModuleApplicationPlanPL/I-CompilerBindPre-compilerLinkageEditor(Load Module)(Application Plan)Runtime SupervisorData ManagerBuffer Manager(Other)DBPL/I + SQ

17、L:EXEC SQL CREATE 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. Runtime Supervisor4. Data Manager5. Buffer Manager Question 9: 儘量解釋下圖儘量解釋下圖 (10%)第8 、9 、10任選二題做答2012 Final Test-10Introduction to Database SystemWei-Pang Yang, IM