Download
1 / 11
110 likes | 144 Views
6. loan. 3. depositor. 5. account. 存款帳. 4. borrower. 存款戶. 貸款帳. 貸款戶. 國立東華大學試題 : 資料庫管理 資訊管理學系 2012.06.19. Example: Banking Database. 1. branch. 2. customer. 客戶(存款戶,貸款戶). 分公司. Question 1: Terms Explanation (20%). Logical Database Design
E N D
6. loan 3. depositor 5. account 存款帳 4. borrower 存款戶 貸款帳 貸款戶 國立東華大學試題: 資料庫管理 資訊管理學系2012.06.19 Example:Banking Database 1. branch 2. customer 客戶(存款戶,貸款戶) 分公司
Question 1: Terms Explanation(20%) • Logical Database Design • Update Anomalies • Minimize the number of Disk Access • Weak Entity • Draw the Functional Dependency Diagram for table account in page 1. • Total participator vs. Partial participator in ER-Model • Many-to-Many Relationship Set • blob: binary large object • foreign key (branch-name)references branchon delete cascade • Generalization vs. Specialization
3. depositor • 存款戶 Question 2: Real-world vs. E-R Model vs. Tables (10%) • 試討論 Real-world, E-R Model , 及Tables 之關係 The real-world enterprise Semantic Data Model: Entity-Relationship(E-R) Data Model 1. branch 2. customer • 客戶(存款戶,貸款戶) • 分公司
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 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 S# STATUS CITY S1 20 London S2 10 Paris S3 10 Paris S4 20 London S5 30 Athens Question 3: 1NF 2NF (10%) 從下面幾點討論把 FIRST 拆成 SECOND 及 SP 的好處: • Update? S1 moves from London to Paris • Insertion: (S5 30 Athens) • Delete "S3 supplies P2 200",
SECOND (in 2NF) CS (in 3NF) S# CITY S1 London S2 Paris S3 Paris S4 London S5 Athens CITY STATUS Athens 30 London 20 Paris 10 Rome 50 S# STATUS CITY S1 20 London S2 10 Paris S3 10 Paris S4 20 London S5 30 Athens Question 4: 2NF 3NF (10%) SC (in 3NF) • SECOND 還有缺點嗎? • SECOND是3NF嗎? Why? 從下面幾點討論把 SECOND拆成 CS及 SC 的好處 • Update • Insertion • Delete
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 (10%) • 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. • 如果沒有City-Index, 要列印所有住在 “London” 的Supplier’s Name, 需多少Disk I/O? 解釋之。
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 (10%) index set - Sequence set (with pointers to data records) • B+ tree 如何提供 “fast direct access” 的服務? 以找 key= “62”為例說明 • B+ tree 如何提供 “fast sequential access” 的服務? • 若一個 node 可放 100 key 時，第三層共可放幾個 key? • Sequence set (with pointers to data records) 是什麼意思?
Question 7: About Your Final Term Project(10%) • According to your final project as “Design and implement a useful database application system” • What is the title of your project? • Names of members in your team. • Draw the E-R Diagram of your application system. (You can just give a similar diagram.) • Draw a table to show one relation used in the system • Check your answer in d) to see whether it is in the 1NF? Why? Please answer “why” by using the definition of the 1NF. • Same as e) to see whether it is in the 2NF? Why? • Same as e) to see whether it is in the 3NF? Why?
User A1 User A2 User B1 User B2 User B3 Host Language + DSL Host Language + DSL Host Language + DSL Host Language + DSL Host Language + DSL C, Pascal DSL (Data Sub Language) e.g. SQL 3 1 2 External View B External View @ # & External schema A External schema B External/conceptual mapping B External/conceptual mapping A Database management system dictionary (DBMS) e.g. system catalog Conceptual View Conceptual schema < DBA Conceptual/internal mapping (Build and maintain schemas and mappings) Storage structure definition (Internal schema) ... 1 3 2 100 Stored database (Internal View) # @ & Question 8: 儘量解釋右圖 (10%) 第8 、9 、10任選二題做答
Major System Components: DB2 Question 9: 儘量解釋下圖 (10%) PL/I + SQL: EXEC SQL CREATE TABLE S ( S# CHAR(5), ......) EXEC SQL SELECT SNAME INTO :SNAME FROM S IF SQLCODE < 0 THEN ....... Source Module 1. Pre-compiled 2. Bind 3. Runtime Supervisor 4. Data Manager 5. Buffer Manager 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 第8 、9 、10任選二題做答 Linkage Editor (Load Module) (Application Plan) Runtime Supervisor Data Manager Buffer Manager (Other) 2 3 1st time 3' 1 Load Module 4 DB
DB2’s Major System Components: Execution time Question 10: 儘量解釋下圖 (10%) PL / 1 LOAD MODULE P If - - CALL - Catalog or Dictionary "data about data" Runtime Supervisor 第8 、9 、10任選二題做答 Application Plan ( for P ) Stored Data Manager Buffer Manager Database "data"
