444 MATH 52000 Finally, suppose we issue the following SQL command: DELETE FROM Emps WHERE dept = ‘EE’; Then after this command and any triggered actions, the average salary of the remaining employees is: A) 59,000 B) 59,500 C) 59,750 D) 60,250 19. There are two relations: R(a,b,c) and S(d,e,f). Suppose we declare the following view: CREATE VIEW V AS SELECT a, ’06022505’ AS class, f FROM R,S WHERE R.b=S.e; and then issue the following query Q: SELECT a FROM V WHERE V.f>10; Which of the following queries produces same answer as Q? A) SELECT a FROM R,S WHERE R.class=’06022505’ AND S.f>10; B) SELECT a FROM R NATURAL JOIN S WHERE S.f>10; C) SELECT a FROM R,S WHERE R.b=S.e AND S.f>10; D) SELECT a FROM V,S WHERE S.f>10; 20. Initially, user A is the owner of the relation to which privilege p refers, and no other user holds privilege p. The following are executed:
A) ∏ A (R >< S ) B) ∏ A (R ) − ∏ A R − ∏ A, B (R >< S ) C) ∏ A (R ∩ (∏ A (R ) × ∏ B (S ))) D) ∏ A ((R × ∏ C (S )) ∩ (∏ A (R ) × S ))
( (
))
13. Let R and S be the relation respectively: Relation R: Relation S: A 1 6 9 B 2 7 7 C 3 9 8 B 2 2 6 C 3 3 8 D 4 5 1
A) {d,e,f} 3. A) B) C) D)
B) {d,e,g}
C) {d,f,g}
D) {d,e}
Schema normalization is not for reducing the number of tables in the schema. eliminating uncontrolled redundancy of data. eliminating anomalies that could otherwise occur with inserts. eliminating anomalies that could otherwise occur with deletes.
D) 11
8. Which of the following functional dependencies does not hold in R? A) A→D B) AC→B C) C→B D) CD→D 9. Which is the highest normal form of R? B)2NF C)3NF D)BCNF A) 1NF 10. If we project R onto S(B,C,D), which of the following functional dependencies does not hold in S? A) BC→D B) B→D C) C→D D) D→C 11. Given two relations R and S with the same set of attributes, which of the following equality equation does not hold for every instance of R and S? A) B) C) D) R R R R ∩S = R - (R - S) ∞ S = S ∞ R ∩ S = R ∞ S ∩S = R × S
Ο
The number of tuples that the result of the expression R >< S (Here is the natural full outer join of relations R and S) is: A) 2 B) 3 C) 4 D) 5 14. Consider the relation R and the query given below: A B null 1 null SELECT B,COUNT(A) as CA FROM R GROUP BY B; What does the query return? A) B) B CA null 1 C) B null 1 CA 1 null 2 1 D) B null 1 CA 1 0 null null 1
南京理工大学课程考试试卷 课程名称： 课程名称：
试卷编号： 组卷日期： 学生班级：
（学生考试用）
数据库系统(英 数据库系统 英)
A
Hale Waihona Puke 学分： 3大纲编号
06022505
考试方式： 闭卷 满分分值： 70 考试时间： 120 分钟 组卷教师（签字） ： 学生学号： 审定人（签字） ： 学生姓名：
2010 年 6 月 23 日
说明：1.所有答案做在答卷纸上,并请标明题号 2.考试完毕后连考卷一起交上 Question 1 (20 points, 1 per part) 1. A relational database management system (DBMS) does not do which of the following? A) Ensure that relational schemas are in at least 3NF. B) Make concurrent transactions to be executed in isolation. C) Facilitate crash recovery of the database in case of hardware failure. D) Optimize query evaluation for SQL queries. 2. If we use the “object-oriented” approach to converting this diagram to relations, which set of attributes would not be the schema of some relation? d A isa f B C isa g e
The following four questions refer to a relation R(A,B,C,D) with functional dependencies A→B, B→D, and D→C. 7. The number of keys of R is: A) 1 B) 2 C) 8
B 1
CA 0
第 2 页
15. A referential constraint: A) must have primary key and foreign key columns that have identical names B) defines a many-to-many relationship between two tables C) ensures that a primary key does not have duplicate values in a table D) ensures that a foreign key value always refers to an existing primary key value in the referenced table 16. Suppose we have a relation declared by CREATE TABLE Foo( name VARCHAR(50) PRIMARY KEY, salary INT ); Initially, the contents of Foo is: name salary ‘Sally’ ‘Joe’ 10000 20000
共 5 页
18. Suppose we have relation Emps(empID, dept, salary) on which where is the following trigger. CREATE TRIGGER Trigger1 AFTER DELETE ON Emps FOR EACH ROW WHEN (500000 < (SELECT AVG(salary) FROM Emps)) UPDATE Emps SET salary = salary + 1000 WHERE dept = ‘MATH’; Suppose further that the current relation Emps is: empID dept salary 111 222 333 CS EE EE 65000 60000 62000
12. Consider the relations R(A,B) and S(B, C). Which of the following four expressions of relational algebra is not equivalent to the other three?