Transcript D - 國立東華大學

國立東華大學試題: 資料庫管理
資訊管理學系2008.4.22
Example:
Banking
Database
1. branch
4. borrower
分公司
貸款戶
Wei-Pang Yang, IM.NDHU, 2008
2. customer 客戶(存款戶,貸款戶) 3. depositor 存款戶
5. account
存款帳
Introduction to Database System
6. loan
貸款帳
2008 Midterm Test-1
Question 1: Database System vs. File System


a)
b)
c)
d)
e)
(15%)
In the early days, database applications were built on top of file
systems
Drawbacks of using file systems to store data:
Data redundancy and inconsistency, why?
Difficulty in accessing data, why?
Integrity problems, why?
Security problems, why?
Database systems offer solutions to all the above problems, why?
Wei-Pang Yang, IM.NDHU, 2008
Introduction to Database System
2008 Midterm Test-2
Question 2: About Keys
(12%)
a) Explain what is the Superkey?
b) List all possible superkeys from relation table account in the page 1.
c) Explain what is the Candidate key?
d) List all possible Primary Key from relation table account.
e) Explain what is the Foreign key?
f) Refer to page 1, what attribute in account should define as a Foreign
Key? Why?
5. account 存款帳
Wei-Pang Yang, IM.NDHU, 2008
Introduction to Database System
2008 Midterm Test-3
Question 3: Definition of Relation (15%)

Relation:

Formally, given sets D1, D2, …. Dn



a) Write

D1 x D2 x … x Dn = {(a1, a2, …, an)
| where each ai  Di}
D1 = {
}

D2 = {
}

a Relation r is a subset of D1 x D2
x … x Dn
D3 = {
}

D1 x D2 x D3 =
Thus a relation is a set of n-tuples
(a1, a2, …, an) where
each ai  Di
b) Write
account =
account
c) Why the Relation r is a subset
of D1 x D2 x … x Dn , please
explain it by using above
example.
Wei-Pang Yang, IM.NDHU, 2008
Introduction to Database System
2008 Midterm Test-4
Question 4: Composition of Operations (10%)
We can build expressions by using multiple operations
Example:
A
B
C
D
E
expression: A=C(r x s)


r
s
A
B

1

2
C
D
E
10
10
20
10
a
a
b
b




op1: r x s
op2: A=C(r x s)
Wei-Pang Yang, IM.NDHU, 2008








1
1
1
1
2
2
2
2
 10

10
 20

a)
？10
 10
 10
 20
 10
a
a
b
b
a
a
b
b
A
B
C
D
E



1
2
2
 10
b)
 ？
20
 20
a
a
b
Introduction to Database System
2008 Midterm Test-5
Question 5: Comparison (9%)

Query: “Find the names of all customers who have a loan at the Perryridge
branch.
 Query 1
customer-name (branch-name = “Perryridge” (
borrower.loan-number = loan.loan-number (borrower x loan)))
a) ？
 Query 2
customer-name (loan.loan-number = borrower.loan-number (
(branch-name = “Perryridge” (loan)) x borrower))
b) ？
c) Which one is better? Why?
Wei-Pang Yang, IM.NDHU, 2008
Introduction to Database System
2008 Midterm Test-6
Question 6: View

(15%)
Assume we have two queries: Q1 and Q2 as follows:

Q1.
create view big-customer as
(select account-number, branch-name
from account
where balance > 500

Q2
select *
from big-customer
a) Draw the result of Q2
b) If we want to add a new tuple to big-customer
insert into big-customer values (‘A-999’, ‘Hualien’)
Where the insertion values will be placed? Draw a table to show
your answer.
c) What are the advantages of the View?
Wei-Pang Yang, IM.NDHU, 2008
Introduction to Database System
2008 Midterm Test-7
Question 7: Fill in the following components to the Overall
System Structure and explain them in details
(24%)
Query Processor
2. Storage Manager
3. Database administrator
4. Application
programmers
5. DDL interpreter
6. Compiler and Linker
7. Buffer manager
8. Data dictionary
9. Index
10. Data
11. Naïve users
12. Application programs
1.
low-level data stored
database
Wei-Pang Yang, IM.NDHU, 2008
Introduction to Database System
2008 Midterm Test-8