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