國立東華大學試題 系所：資訊管理學系 科目：資料庫管理 第1頁/共4頁 國立東華大學試題 系所：資訊管理學系 科目：資料庫管理 第1頁/共4頁 1. [關於 QBE] (20%) (a) Fill in the following sketch to find all loan numbers at the Perryridge branch. (b) Find all loan numbers at the Perryridge branch. (c) Find the loan number of all loans with a loan amount of more than $700 (d) Find names of all branches that are not located in Brooklyn

國立東華大學試題 系所：資訊管理學系 科目：資料庫管理 第2頁/共4頁 國立東華大學試題 系所：資訊管理學系 科目：資料庫管理 第2頁/共4頁 1. branch 2. [關於 Integrity] (30%) Consider the relations: account, branch create table account (account-number char(10), branch-name char(15), balance integer, primary key (account-number), foreign key (branch-name) references branch) What is the primary key of account? What is the primary key of branch? What means “foreign key (branch-name) references branch” in above crate table account? If we have “foreign key (branch-name) references branch on delete cascade” then what will happen if we delete the first tuple of branch? In (d) if we delete the first tuple of account? (f) What about “on delete set null” in (d)? 3. account

國立東華大學試題 系所：資訊管理學系 科目：資料庫管理 第3頁/共4頁 國立東華大學試題 系所：資訊管理學系 科目：資料庫管理 第3頁/共4頁 3. [關於 Normal Forms] (30%) Consider the relation named SPData: 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 SPData What is the primary key of SPData? What means “atomic” in Def 1? Is this relation 1NF? Why? Is this relation 2NF? [just say yes or no] Draw the FD Diagram of SPData. Using FD Diagram above and Def 1 to explain your answer of (d). Please decompose the SPData into two tables to get next level’s normal form. What means “determinant” in Def 3? Check your tables in answer (g) and see whether they are in BCNF “If a table is in BCNF, then it is in 3NF.” True or False? Def 1: A relation R is in 2NF iff (1) R is in 1NF (i.e. atomic) (2) Non-key attributes are FD on primary key Def 2: A relation R is in 3NF iff non-key attributes are mutually independent Def 3: A relation R is in BCNF iff every determinant is a candidate key.

國立東華大學試題 系所：資訊管理學系 科目：資料庫管理 第4頁/共4頁 國立東華大學試題 系所：資訊管理學系 科目：資料庫管理 第4頁/共4頁 4. [關於 B+ Tree] (25%) 50 82 96 97 99 91 93 94 89 94 83 85 89 71 78 82 60 62 70 51 52 58 58 70 35 40 50 15 18 32 6 8 12 12 32 index set (nondense) Sequence set (dense) - How index are used? 有哪三項? 試以上圖解釋之。 Index 有 dense 與 nondense 之分，意義為何? How to get record with key = 62, and how many disk I/O are needed to get it? If we want to print out all records, at least, how many disk I/O are needed? In general, what are the advantages and disadvantages of using “index”?