國立東華大學試題 系所：資訊管理學系 科目：資料庫管理 第1頁/共4頁

Presentation on theme: "國立東華大學試題 系所：資訊管理學系 科目：資料庫管理 第1頁/共4頁"— Presentation transcript:

1 國立東華大學試題 系所：資訊管理學系 科目：資料庫管理 第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 國立東華大學試題 系所：資訊管理學系 科目：資料庫管理 第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 國立東華大學試題 系所：資訊管理學系 科目：資料庫管理 第3頁/共4頁
國立東華大學試題 系所：資訊管理學系 科目：資料庫管理 第3頁/共4頁 3. [關於 Normal Forms] (30%) Consider the relation named SPData: S# STATUS CITY P# QTY S London P S London P S London P S London P S London P S London P S Paris P S Paris P S Paris P S London P S London P S London P 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頁 4. [關於 B+ Tree] (25%) 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”?