数据库应用期末考试试题(A卷)(包括答案)..docVIP

浙江工商大学 2006 /2007 学年第 2 学期考试试卷 课程名称： 数据库应用 考试方式： 开卷 完成时限：120分钟 班级名称： 学号： 姓名： 题号 一 二 三 四 五 六 七 八 九 十 总分 分值 得分 阅卷人 Question 1(20 points): Land masses are either islands or continents. All land masses have a name and an area; the name is the key. Some continents are connected to each other, e.g., Asia is connected to Europe and if you ignore the Suez Canal to Africa. No island is connected to any other island or to a continent. Bodies of water are either oceans or straits. A body of water has a name (the key) and an area. Islands may be either located in one ocean (e.g., Hawaii is in the Pacific Ocean) or separated from a continent by a strait (e.g., Honshu is separated from Asia by the Sea of Japan; the latter is a strait for our purposes). You should not assume that a strait is adjacent to only one continent or to only one island. In the space below design an Entity/Relationship diagram suitable for representing the above information. Do not forget to indicate keys and the multiplicity of relationships. Question 2: In this exercises we shall write queries in various languages about our running “beer” example database: Beers (name, manf) Bars (name, addr, license) Drinkers (name, addr, phone) Likes (drinker, beer) Sells (bar, beer, price) Frequents (drinker, bar) This question is devoted to SQL queries, database modifications and declarations. Write the following in standard SQL being as succinct as possible. a) Find the name and address of all drinkers who frequent Joes Bar and like some beer that Joes Bar sells. Do not print any drinker more than once. (10 Points) b) Delete from Drinkers all drinkers in the 0571(Hangzhou) area code. You may assume that phone numbers are represented by character strings of the form ‘(xxxx) yyyyzzzz’, where xxxx is the area code. (10 Points) c) Find for each price (that appears in Sells), the number of bars that server at least o