课程名称︰资料库管理 课程性质︰系必修 课程教师︰李瑞庭 开课学院：管理学院 开课系所︰资讯管理学系 考试日期（年月日）︰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)