Transcript 2009題目 - 國立東華大學

國立東華大學試題: 資料庫管理
(每小題5分；滿分:105)
資訊管理學系2009.06.23
Example:
Banking
Database
1. branch
4. borrower
分公司
貸款戶
Wei-Pang Yang, IM.NDHU, 2009
2. customer 客戶(存款戶,貸款戶) 3. depositor 存款戶
5. account
存款帳
Introduction to Database System
6. loan
貸款帳
2009 Final Test-1
Question 1: Major System Components

Explain the following
PL/I + SQL:(P.2- 20)
EXEC SQL CREATE TABLE S ( S# CHAR(5), ......)
EXEC SQL SELECT SNAME INTO :SNAME FROM
S
IF SQLCODE < 0 THEN .......
Source
Module
major system components
1)
Pre-compiled
2)
Bind
3)
Buffer Manager
4)
DBRM
CALL CREATE( ......)
CALL SELECT( ......)
IF SQLCODE < 0 THEN
.......
Modifie
d
Source
Module
Pre-compiler
DBRM
request
module
PL/I source
PL/I-Compiler
Bind
Object
Module
Application
Plan
parse-tree version
of SQL
a DB Compiler
optimized machine
codes of SQL
statements
in system Catalog
Linkage
Editor
(Load Module)
2
(Application Plan) 3'
Load
Module
1
3 1st time
Runtime Supervisor
4
Data Manager
Buffer Manager
DB
(Other)
Wei-Pang Yang, IM.NDHU, 2009
Introduction to Database System
2009 Final Test-2
Question 2: Functional Dependency
5) What is Functional Dependency ?
6) Draw the Functional Dependency Diagram for table laon in
page 1.
Wei-Pang Yang, IM.NDHU, 2009
Introduction to Database System
2009 Final Test-3
Question 3: Good and Bad Decomposition

Consider
transitive FD
STATUS
Suppose 1. CITY is the main office of the supplier.
2. STATUS is some factor of CITY
S#
CITY
STATUS
STATUS
S#
S#
CITY
CITY
② Decomposition B:
①Decomposition A:
SC:
SC:
S#
CITY
CITY
STATUS
CS:
CS:
S#
CITY
S#
STATUS
7)
討論把上圖拆成二個小 table 之好處
8)
二種拆法，A 好? 還是 B 好? 為什麼?
Wei-Pang Yang, IM.NDHU, 2009
Introduction to Database System
2009 Final Test-4
Question 4: Normal Form
FIRST
S#
S1
S1
S1
S1
S1
S1
S2
S2
S3
S4
S4
S4
STATUS
20
20
20
20
20
20
10
10
10
20
20
20
SECOND (in 2NF)
CITY
London
London
London
London
London
London
Paris
Paris
Paris
London
London
London
P#
P1
P2
P3
P4
P5
P6
P1
P2
P2
P2
P4
P5
QTY
300
200
400
200
100
100
300
400
200
200
300
400
從下面幾點討論把 FIRST 拆成 SECOND 及 SP 的好處:
9)
Update? S1 moves from London to Paris
S# STATUS CITY
S1
S2
S3
S4
S5
20
10
10
20
30
London
Paris
Paris
London
Athens
SP (in 2NF)
S#
P#
QTY)
S1
S1
S1
S1
S1
S2
S2
S3
S4
S4
P1
P2
P3
P4
P5
P1
P2
P2
P4
P5
300
200
400
200
100
300
400
200
300
400
10) Insertion: (S5 30 Athens)
11) Delete "S3 supplies P2 200",
12) SECOND 還有缺點嗎? 它是3NF嗎? Why?
Wei-Pang Yang, IM.NDHU, 2009
Introduction to Database System
2009 Final Test-5
Question 5: Indexing
(12%)

Consider the Supplier table, S.

假設 Index 整個放在一個 page, S Table 每一筆 tuple 各放一個 page
City-Index (index)
S (indexed file)
Athens
S1
Smith
20
London
London
S2
Jones
10
Paris
London
S3
Blake
30
Paris
Paris
S4
Clark
20
London
Paris
S5
Adams
30 Athens
13) 要列印所有住在 “Athens” 的Supplier’s Name, 需多少Disk I/O? 解釋之。
14) 要列印所有住在 “London” 的Supplier’s Name, 需多少Disk I/O? 解釋之。
15) 要列印所有住在 “Taipei” 的Supplier’s Name, 需多少Disk I/O? 解釋之。
16) 若插入 “S6 Yang 25 Hualien”, 重畫 S 及 City-Index 二 Tables.
Wei-Pang Yang, IM.NDHU, 2009
Introduction to Database System
2009 Final Test-6
Question 6: B+-tree
50
(15%)
82
index set
12
32
58
70
89
94
6
8 12
15 18 32
35 40 50
51 52 58
60 62 70
71 78 82
83 85 89
17) What is the index set for?
18) What is the sequence set for ?
91 93 94
96 97 99
Sequence set
(with pointers
to data records)
19) Write down the detailed procedures for searching “62”.
20) 解釋 “with pointers to data records”?
21) 若一個 node 可放 100 key 時，第三層共可放幾個 key?
Wei-Pang Yang, IM.NDHU, 2009
Introduction to Database System
2009 Final Test-7