課程名稱︰資料庫管理 課程性質︰系必修 課程教師︰李瑞庭 開課學院：管理學院 開課系所︰資訊管理學系 考試日期（年月日）︰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)