浙江工商大学《数据库应用》课程考试试卷，适用专业：电子、通信
浙江工商大学 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.
第 1 页 共 7页
浙江工商大学《数据库应用》课程考试试卷，适用专业：电子、通信
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 one beer at
that price. (10 Points)
d) Insert into Bars (with default values for addr and license) all those bars that are mentioned in Frequents but not in Bars. (10 Points)
Question 3:
Q1: SELECT a
FROM R
第 2 页 共 7页
浙江工商大学《数据库应用》课程考试试卷，适用专业：电子、通信
WHERE R.b > ALL(SELECT c FROM S);Q2: SELECT a
FROM R
WHERE R.b > ANY(SELECT c FROM S);
(a) Q1 and Q2 produce the same answer.
(b) The answer to Q1 is always contained in the answer to Q2.(c) The answer to Q2 is always contained in the answer to Q1.(d) Q1 and Q2 produce different answers.The answer is (d)
I threatened to put this on the exam, and here it is problem #1. In general, ALL is a more stringent requirement than ANY, so we expect that Q1 is contained in Q2. However, if SELECT c FROM S is empty, then it is impossible to satisfy ANY, while ALL is trivially satisfied. Then, Q2 is contained in Q1. Since both containments could be proper, the queries are different.
Question 4: In the following, R is a relation with schema R (a, b). The result of each sequence of modifications is the value of R at the end.
Q1: UPDATE R SET b =3 WHERE b = 2;Q2: INSERT INTO R
SELECT a, 3 FROM R WHERE b = 2;DELETE FROM R WHERE b = 2;(a) Q1 and Q2 produce the same answer.
(b) The answer to Q1 is always contained in the answer to Q2.(c) The answer to Q2 is always contained in the answer to Q1.(d) Q1 and Q2 produce different answers.The Answer is (a)
Each of these modifications has the effect of replacing every tuple of the form (a,3) for some a by the tuple (a,2). If such a tuple appears more than once in R, the count is preserved as well.Question 5: In this question R(x) is the schema of relation R.Q1: SELECT x
FROM R rr
WHERE NOT EXISTS(
SELECT * FROM R WHERE x >rr.x);
Q2: SELECT MAX(x) FROM R.
(a) Q1 and Q2 produce the same answer.
(b) The answer to Q1 is always contained in the answer to Q2.
第 3 页 共 7页
浙江工商大学《数据库应用》课程考试试卷，适用专业：电子、通信
(c) The answer to Q2 is always contained in the answer to Q1.(d) Q1 and Q2 produce different answers.
The Answer is (c)
Q1 produces the largest element of R as many times as it appears in R. Q2 produces the same element, but only once.
Question 6 (5 points): Suppose we have an SQL relation declared by
CREATE TABLE Foo(
name VARCHAR(50) PRIMARY KEY,salary INT CHECK(salary <=
(SELECT AVG(salary) FROM Foo))
);
Initially, the Contents of Foo is:
namesalary‘Sally’1,000‘Joe’2,000‘Sue’3,000
We now execute the following sequence of modifications:
INSERT INTO Foo VALUES (‘Fred’, 1200);
UPDATE Foo SET salary =2000 WHERE name =’Sue’;INSERT INTO Foo VALUES (‘Sally’,1300);DELETE FROM Foo WHERE name = ‘Joe’;
At the end of these statements, the sum of the salaries over all the tuples then in Foo is:
(a) 5,200(b) 6,200(c) 6,500 (d) 7,200
The Answer is (a).
The last problem also fooled a lot of people. I suspect you were calculating averages so fiercely that you forgot to check for a primary-key violation. The sequence of events is as follows: ?
The insert of Fred succeeds, because his salary is less than the current average of 2,000. The new average salary is 1,800, and the total is 7,200.
The update of Sue's salary is rejected because it is higher than the average.
The insertion of a tuple for Sally is rejected because there is already a tuple with the key value 'Sally'.
The deletion of Joe's tuple succeeds; neither of the constraints on Foo affects a deletion. The total salary is decreased by Joe's 2,000 salary, to 5,200
第 4 页 共 7页
???
浙江工商大学《数据库应用》课程考试试卷，适用专业：电子、通信
Question 7: Consider the following E/R diagram:
ARBCIf A has 100 entities, B has 1000 entities, and C has 10 entities, what is the maximum number of triples of entities that could be in the relationship set for R?
(a) 100(b) 1000(c) 100,000(d) 1,000,000The Answer is (B)
entity and C entity, there is a unique B entity. Since there are only 1000 possible A-C pairs, there cannot be more than this number of tuples. However, we could also have 1000 triples in the relationship set. Suppose the A values are 0-99, and the C values are 0-9. Let the associated B value be 10*A+C. Then all B values from 0 to 999 appear exactly once, so A and BB surely determine at most one CThe stronger of the conditions represented by the arrows is that for a given A
Question 8: Suppose R (a, b) contains the tuples {(1, 2),(3,4)} and S(b, c) contains the
tuples{(2,5),(2,6),(7,8)}. The natural outerjoin of R and S contains how many tuples?
(a)2(b)3(c)4(d)5
Question 9: Relation R (a, b, c) currently has the following instance:
{(1,2,3),(3,4,2),(2,6,1)}
第 5 页 共 7页
Removed - 数据库应用期末考试试题（A卷）（包括答案）
