2012 0110 Question 1 Explain the following terms

國立東華大學 試題: 高等資料庫系統 系所：資管碩士班 日期： 2012年 01月10日 Question 1: Explain the following terms 第 1頁/共 4頁 (40 %) 1) Weak entities and Regular entities in Entity/Relationship Model 2) Total participant and Partial participant in Entity/Relationship Model 3) Integrity rules in DBMS 4) Security on SQL: Authorization subsystem 5) Statistical database 6) Public encryption: RSA 7) Location independence in distributed DBMS 8) Fragmentation independence in distributed DBMS 9) Replication independence in distributed DBMS 10) Query optimization is more important in a distributed system, why? Wei-Pang Yang, IM. NDHU 高等資料庫系統 Final Test-1

Question 2: To Evaluate a given Query (20 %) Suppose: |S| = 100, |SP| = 10, 000, and there are 50 tuples in SP with p# = 'p 2'? Results are placed in Main Memory. S SP STATUS CITY S# SNAME . . 2 S 5 . . . 1 100 S 1 S# P# QTY 1 S 3 . . 2 S 1 . . . 10, 000 S 2 . . Query in SQL: SELECT S. * FROM S, SP WHERE S. S# = SP. S# AND SP. P# = 'p 2‘ Method 1: Iteration Join Method 2: Restriction + Iteration Join Method 3: Sort-Merge Join + Restrict (Suppose S, SP are sorted on S#). 試討論以上三種方法之做法並分別計算出其“比較”之次數 Wei-Pang Yang, IM. NDHU 高等資料庫系統 Final Test-2

Question 3: The Query Optimization Process (20 %) SQL n The query optimization process includes the following four steps, please use some examples to explain these steps in detailed. Step 1: Cast the query into some internal representation. Step 2: Convert to equivalent and efficient form. Algebra Canonical Form e. g. ( I)) Optimizer step 3 : access path selection Step 3: Choose candidate low-level procedures. Step 4: Generate query plans and choose the cheapest. (C 6 choose 2 One or more candidate procedures for each operator e. g. , 2 3 , 2 Step 4 Wei-Pang Yang, IM. NDHU 高等資料庫系統 Final Test-3

Question 4: Normal Forms (20 %) n Suppose 1. CITY is the main office of the supplier. 2. STATUS is some factor of CITY 1) FIRST 是 1 NF嗎? 為什麼? 2) FIRST 的 key 是什麼? 3) FIRST 的 non-key 是什麼? 4) 討論 此 table 之 Update 異常現象 5) 畫出 FD diagram of FIRST 6) 從 FD diagram of FIRST 探討它不 2 NF 之理由 7) 將 FD diagram of FIRST 拆成二個 FD diagrams 8) 將 table FIRST 拆成二個 2 NF 的 tables 9) 你拆成的這二個 table 是 3 NF 嗎? 為什麼? 10) 你拆成的這二個 table 的 key 是什麼? Wei-Pang Yang, IM. NDHU 高等資料庫系統 FIRST S# STATUS CITY P# QTY S 1 S 1 S 1 S 2 S 3 S 4 S 4 20 20 20 10 10 10 20 20 20 London London Paris London P 1 P 2 P 3 P 4 P 5 P 6 P 1 P 2 P 2 P 4 P 5 300 200 400 200 100 300 400 200 300 400 Final Test-4