課程名稱︰資料庫管理
課程性質︰系必修
課程教師︰李瑞庭
開課學院：管理學院
開課系所︰資訊管理學系
考試日期（年月日）︰2011/11/16
考試時限（分鐘）：09:10-12:00
是否需發放獎勵金：是
（如未明確表示，則不予發放）
試題 :
Database Management System
-Midterm, Nov, 16, 2011-
1. (20%)
(a) Describe the relationships among ER-diagram, relational schema,
relational algebra, SQL.
(b) What role does the relational algebra play for a database programmer
and a database vendor, respectively?
2. (10%)
Why is SQL required for a database system?
3. (20%)
In this course, we introduce two database design approaches. What are they?
Use a flowchart to describe the relationships between the major components.
4. (25%)
Consider the following database schema for a supply chain, where the
primary attributes are underlined, and a tuple in the Order relation
denotes that sno1 ( a supplier ) orders pno ( a product ) from sno2
( another supplier ).
Product(pno, pname, price)
---
Supplier(sno, name, address, city)
---
Order(sno1, sno2, pno, date, quantity, discount)
---- ---- ---
Answer the following queries with respect to the above database schema.
(a) Write a relational algebra to find the suppliers who supply all
products supplied by Supplier S1.
(b) Write a relational algebra to find all the direct/indirect suppliers
of Supplier S1.
Assume that there are 4 levels of supply hierachy for S1.
(c) Write an SQL to retrieve the suppliers where the discounts of the
orders placed by the suppliers are all greater than 30%.
(d) Write an SQL to retrieve the suppliers who do not place any order from
2011/1/1 to 2011/10/30.
(e) Write an SQL to retrieve the suppliers supplying every product whose
price is less than $100.
5. (15%)
Consider the following update statements with respect to the relational
schema shown in question 4. What constraints counld it violate?
(a) Insert <P1, 'Product A', $10> into Product.
(b) Insert <S1, S2, P1, 2011/11/16, 100, 10%> into Order.
(c) Delete a supplier S1 from Supplier.
6. (10%)
Given a relation R(A,B,C,D,E,F,G,H) with the following dependencies,
AB→CDEFGH, C→BG, DE→FH, normalize it into 3NF and BCNF.
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 140.112.245.126
※ 編輯: EternalChaos 來自: 140.112.245.126 (11/16 21:15)