课程名称︰资料库管理 课程性质︰系必修 课程教师︰李瑞庭 开课学院：管理学院 开课系所︰资管系 考试日期（年月日）︰101/11/14 考试时限（分钟）：170min 是否需发放奖励金：是 （如未明确表示，则不予发放） 试题 : 1.(20%) What does the following ER-diagram mean? Map it into a relational database schema. 2.(10%) What is superkey? What is database state? 3.(10%) What is normalization in a database system? What is the difference between 3NF and BCNF? 4.(10%) Given a relation R(O,P,Q,R,S,T) with the following depandencies, OP->QRST, P->Q, R->OST, norrmalize it into 3NF and BCNF. 5.(40%) Consider the following database schema for an online selling website, where the primary attributes are underlined. Product(PNo, PName, category, price) ￣￣ 　　　 Customer(CNo, CName, city) ￣￣ Buy(CNo, PNo, date, quantity) ￣￣ ￣￣ Answer the following queries with respect to the above database schema. (a)Write a relational algebra to find every product whose price is greater than $100. (b)Write a relational algebra to find the city with most customers. (c)Write a relational algebra to find the customers who buy every product bought by last year. (d)Write an SQL to retrieve every product with least price in its category. (e)Write an SQL to compute the amount of every product sold this year. (f)Write an SQL to retrieve the customers who buy all products bought by customer C1. (g)Write an SQL to retrieve the customers who do not buy any product this year. (h)Write an SQL to retrieve the customers who buy every product with the highest price in each category. 6.(10%) Cosider the following update statements with respect to the database schema shown in question 5. What constraints could be violated? (a)Insert<C1, P1, 2012/11/14, 100> into buy. (b)Delete a customer C1 from Customer. --

※ 发信站: 批踢踢实业坊(ptt.cc) ◆ From: 140.112.25.97