課程名稱︰資料庫管理
課程性質︰必修
課程教師︰楊立偉
開課學院：管理學院
開課系所︰工商管理學系科技管理組
考試日期（年月日）︰2009.11.11
考試時限（分鐘）：120
是否需發放獎勵金：是
（如未明確表示，則不予發放）
試題 :
Part I. E-R diagram (20%)
1. List 2 conditions when a relationship should be modeled as an associative
entity instead.
2. List the 4 types of cardinality constraints, and draw an example of each.
3. Give an example for each of the following:
(a) Derived attribute
(b) Multivalued attribute
(c) Composite attribute
4. Draw an example for each of the following:
(a) Ternary relationship
(b) Unary relationship
Part II. Relational Database and SQL (20%)
5. Define each of the following terms briefly:
(a) Referential integrity
(b) Correlated subquery
6. In a relational database, describe 3 types of anomalies that may arise in a
table, and give an example of each.
7. Referential integrity is supported in SOL. Explain how the ON UPDATE
RESTRICT, ON UPDATE CASCADE, and ON UPDATE SET NULL clauses differ.
8. In SQL, under what conditions can a UNION clause be used?
Part III. Exercise
9. According to the following business rules, please draw an E-R diagram,
which includes entities, relationships, and cardinalities.
‧ There are schools(學院), students(學生), and clubs(社團).
‧ Every student attends one and only one school; every school has more than
one student.
‧ Every student may optionally join one club; every club has more han one
student.
‧ Every club belongs to one school; some schools have more than one club,
some schools have none.
‧ Any student may work as an assistants(助理) for only one school; every
school may have one assistant at most.
10. Transform each of the following E-R diagrams to a relational schema that
shows referential integrity constraints, and functional dependencies. If
any of the relations are not in 3NF, transform those relations to 3NF.(20%)
(a) Fig 3-11a
┌───────┐
│Date_Completed│
└───────┘
|
┌───────┐ | ┌───────┐
│ EMPLOYEE │Ｂ | Ａ│ COURSE │
│Employee_ID │╲ | ／│Course_ID │
│￣￣￣￣￣￣ ├──────────────────┤￣￣￣￣￣ │
│Employee_Name │／ Completes ＼│Course_Title │
│Birth_Date │ │{Topic} │
└───────┘ └───────┘
(b) Fig 3-15b
┌─────────────┐
│ EPLOYEE │
│Employee_ID │
│￣￣￣￣￣￣ │
│Employee_Name │
│{Skill (Skill_Code, │
│Skill_Title, Skill_Type)} │
└─────────────┘
11. The following relation called GRADE Report for a university. (10%)
┌──────────────────────────────────────
│GRADE REPORT
├──────────────────────────────────────
│Student_ID Student_Name Campus_Address Major Course_ID Course_Title
├──────────────────────────────────────
│168300458 Williams 208 Brooks IS IS 350 Database Mgt
│168300458 Williams 208 Brooks IS IS 465 Systems Analysis
│543291073 Baker 104 Phillips Acctg IS 350 Database Mgt
│543291073 Baker 104 Phillips Acctg Acct 201 Fund Acctg
│543291073 Baker 104 Phillips Acctg Mkgt 300 Intro Mktg
└──────────────────────────────────────
───────────────────────┐
│
───────────────────────┤
Instructor_Name Instructor_Location Grade │
───────────────────────┤
Codd B 104 A │
Parsons B 317 B │
Codd B 104 C │
Miller H 310 B │
Bennett B 212 A │
───────────────────────┘
(a) Please indicate the functional dependencies in the relation.
(b) Decompose the relation into a set of 3NF relations.
12. 某電信公司徵求顧客關係管理人員1位，需熟稔顧客資料操作。顧客資料表格如下，請
以SQL進行以下7個查詢動作。 (20%)
顧客 分店
Customer branch
門號 姓名 生日 居住地區 分店代號 名稱 所在地區
phone_no name birthdate area branch_code name area
char(10) char(10) char(8) char(8) char(2) char(10) char(8)
0935000003 張三 20001020 台北 1 台北總店 台北
0935000004 李四 20010120 台北 2 新竹店 新竹
0918000005 王五 20001120 新竹 3 高雄店 高雄
0939000006 陳六 20001220 高雄
異動 資費
action reteplan
異動代號 名稱 資費代號 名稱 單價(秒)
action_code name rate_code name unit_price
char(2) char(10) char(2) char(10) integer
1 新裝 A 學生族 2
2 變更資費 B 商務人士 1
3 退裝
通話記錄
log_call
發話門號 受話門號 開始日期 開始時間 持續秒數
phone_no1 phone_no2 date time duration
char(10) char(10) char(8) char(4) integer
0935000003 0935000004 20091121 0800 60
0935000003 0918000005 20091122 0900 120
0935000003 0939000006 20091122 1000 120
0918000005 0939000006 20091125 1300 180
0935000004 0918000005 20091127 0800 180
0939000006 0932000007 20091129 1400 120
異動記錄
log_action
門號 分店代號 異動代號 資費代號 異動日期
phone_no branch_code action_code rate_code date
char(10) char(2) char(2) char(2) char(8)
0935000003 1 1 A 20091005
0935000004 2 1 B 20091015
0918000005 2 1 A 20091025
0939000006 3 1 B 20091105
0935000004 1 2 A 20091115
0939000006 3 3 20091130
(a) 列出住在台北的顧客姓名
(b) 列出門號開頭為0935的顧客姓名
(c) 列出各居住地區的顧客人數，由人數多至少排序
(d) 列出曾發話给2個門號以上的發話門號
(e) 列出所有曾「新裝」為「學生族」資費的門號與異動日期
(f) 列出不是在居住地區「新裝」門號的顧客姓名
(g) 列出曾發話給網外(不是本公司門號)的發話門號
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 140.112.247.63