Transcript No Slide Title

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