数据库试题参考.doc

（学生考试用）课程名称： 数据库系统(英) 学分： 3 大纲编号 06022505 试卷编号： A 考试方式： 闭卷 满分分值： 70 考试时间： 120 分钟组卷日期： 2010年1月20日 组卷教师（签字）： 审定人（签字）： 学生班级： 学生学号： 学生姓名： 说明：1.所有答案做在答卷纸上,并请标明题号 2.考试完毕后连考卷一起交上Question 1 (20 points, 1 per part)1. If a product can be manufactured in many plants, and a plant can manufacture many products, this is an example of which type of relationship from products to plants?A) Many-many B) Many-one C) One-one D) One-many2. If we use the “E/R” approach of converting the following E/R diagram to relations, which of the following would be one of the relation schemas we construct?A d eBfCgisaisaA) B(d,e,f) B) C(g,e) C) A(d,f,g) D) B(d,f)3. Which of the following four expressions of relational algebra is not equivalent to the other three? They are all based on the relations R(A,B) and S(B,C). A) B) C) D) 4. Assume R and S are each relations with attributes a and b only.Q1: Q2: Which of the following is correct? 第 1 页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 completely different answers.5. Suppose the relation R(A,B,C) has the following four tuples:ABC312412314414Which of the following functional dependencies can you infer does not hold in R?A) A-B B) BC-A C) C-B D) AB-AThe following two questions refer to a relation R(A, B, C, D,E) with functional dependencies ABC-DE, and D-AB.6. Which of the following is the highest normal form of R?A) 1NF B) 2NF C) 3NF D) BCNF7. The number of superkeys of R is:A) 2 B) 7 C) 10 D) 128. In the following, assume a is an attribute of some character-string type, e.g. CHAR(10), and that it may be NULL.Q1: SELECT * FROM R WHERE a IS NULL;Q2: SELECT * FROM R WHERE a NOT LIKE %;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.9. In this question, R(x) is the schema of relation R.Q1: SELECT x FROM R rr WHERE NOT EXISTS(SELECT * FROM R WHERE xrr.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. C) The answer to Q2 is always contained in the answer to Q1. D) Q1 and Q2 produce different answers. 共 5 页10. In SQL, an ALTER statement:A) May be used to add a view.B) May be used to add a constraint.C) May be used to drop a table.D) May be used to drop a view.11. In SQL, an UPDATE statement without a WHERE clause:A) Results in a Cartesian product.B) Updates no rows in a table.C) Updates every column in a table.D) Updates every row in a table.12. Consider relation G and the query given below:studentgrade1A2NULL3B4ASELECT grade,COUNT(*) FROM GGROUP BY grade;How many tuples are returned?A) 1 B) 2 C) 3 D) 413. In the 3-valued logic, the value of expressionR.a=R.b OR R.a=0can be:A) Only TRUE or FALSEB) Only FALSE or UNKNOWNC) Only TRUE or UNKNOWND) Any of TRUE, FALSE, or UNKNOWN14. Unique constraints:A) Require columns that have NOT NULL constraintsB) May only be defined once per tableC) May be violated by a insertionD) Are identical to primary key constraints第 2 页15. Suppose we have the following table declarations: CREATE TABLE A(w INT PRIMARY KEY); CREATE TABLE B(x INT PRIMARY KEY REFERENCES A(w) ON DELETE SET NULL);CREATE TABLE C(y INT REFERENCES A(w); CREATE TABLE D(z1 INT REFERENCES B(x) ON DELETE SET NULL, z2 INT REFERENCES A(w) ON UPDATE CASCADE);Consider the following operations:I. DELETE FROM C; DELETE FROM B; DELETE FROM A; DELETE FROM D;II. DELETE FROM C; DELETE FROM D; DELETE FROM A; DELETE FROM B;III. DELETE FROM B; DELETE FROM C; DELETE FROM D; DELETE FROM A;Which of the above operations will empty all four tables without error?A) III only B) I only C) II and III only D) I and III onlyThe following two questions concern the relations:Emps (id, name, dept, salary)Managers (dept, mgr)The first gives the employee ID, their name, department, and salary; the second gives for each department, the manager of that department, which is the employee ID of the person managing the department.16. We wish to constrain the relations so that in the mgr attribute of a Managers tuple there must appear the ID of an employee in Emps. Which of the following changes, by itself, enforces that constraint?A) In the declaration of Managers, add for attribute mgr the attribute-based check CHECK(EXISTS(SELECT * FROM Emps WHERE id=mgr).B) In the declaration of Emps, add the constraint FOREIGN KEY id REFERENCES Managers(mgr).C) In the declaration of Managers, add the constraint FOREIGN KEY mgr REFERENCES emps(id).D) More than one of the above.17. Suppose we wish to constrain the data so that in no department can the employees have a total salary greater than $1,000,000. The following is a framework for an assertion that will enforce this constraint:CREATE ASSERTION cheap CHECK(NOT EXISTS(Q);Which query Q best enforces this constraint? 共 5 页A) SELECT * FROM Emps WHERE SUM(salary)1000000B) SELECT dept,SUM(salary) FROM Emps GROUP BY deptC) SELECT SUM(salary) FROM Emps,Managers WHERE id=mgrGROUP BY Emps.dept HAVING SUM(salary)1000000D) SELECT dept FROM EmpsGROUP BY dept HAVING SUM(salary)100000018. A is a unary(one-column) relation declared by CREATE TABLE A(i INT);Below is an trigger:CREATE TRIGGER MysteryAFTER INSERT OR UPDATE ON AREFERENCING OLD AS OldRow, NEW AS NewRow FOR EACH ROWWHEN (10(SELECT MAX(i) FROM A)UPDATE A SET i=i+1;With A empty, we execute the statement:INSERT INTO A VALUES(1);The result of this statement is:A) A is left with a single tuple with value 10B) A is left with a single tuple with value 2C) A is left with tuples 2,3,10D) The trigger never stops modifying tuples19. Relation R(a,b,c) currently has the following instance:(1,2,3),(3,4,2),(2,6,1)We make the following view definitions:CREATE VIEW V AS SELECT a*b AS d, c FROM R;CREATE VIEW W AS SELECT d, SUM(c) AS e FROM V GROUP BY d;What is the sum of all the components of all the tuples of the following query?SELECT AVG(d),e FROM W GROUP BY e;A) 10 B) 17 C) 23 D)2820. Initially, user A is the owner of relation R, and no other user holds privileges on R.The following are executed:by A: GRANT UPDATE ON R TO Bby A: GRANT UPDATE(a) ON R TO C WITH GRANT OPTIONby C: GRANT UPDATE(a) ON R TO B WITH GRANT OPTIONby A: REVOKE UPDATE(a) ON R FROM C CASCADE第 3 页Which of the following best describes the status of Bs privileges on R?(A) B can update any attribute of R except a, but cannot grant that privilege.(B) B has no privileges on R and cannot grant any.(C) B can update any attribute of R except a, but can grant others the privilege to update R.a.(D) B can perform any update on R but cannot grant that privilege.Question 2 (22 points)1. (5 points)Consider the following two tables:T1:ADET2:ABC10a510b615b825c325a610b15Show the results of the following algebraic or SQL expressions:a) (1 points)b) SELECT * FROM T1 NATRUAL LEFT OUTER JOIN T2 ON EC; (2 points)c) SELECT * FROM T1 NATRUAL FULL OUTER JOIN T2 WHERE E docURL, docTitle, docDatedocID, linkedDocID - linkTextwordID - wordTexta) Specify all keys of R. (1 points)b) If we project these FDs onto relation S(docID, linkedDocID, linkText, wordID, wordText), give a minimal basis for the FDs that hold in S.(2 points)c) Give the highest normal form of R and S, respectively.(2 points)d) Use the 3NF synthesis algorithm to find a lossless-join, dependency-preserving decomposition of R into 3NF relations.(2 points) 共 5 页3. (5 points)The following set of requirements are for a university database that is used to keep track of students transcripts. I) The university keeps track of each students name, students number, social security number, current address, phone number, permanent address, birth date, sex, class, degree. Both social security number and student number have unique values for each student.II) Each department is described by a name, department code, office number, office phone and college. Both name and code have unique values for each department.III) Each course has a course name, description, course number, number of semester hours and offering department. The value of course number is unique.IV) Each section has an instructor, semester, year, course and section number. The section number distinguishes sections of the same course that are taught during the same semester/year; its values are 1, 2, 3 . upto the number of sections taught during each semester.V) A grade report has a student, a section, and a grade.Design an E/R diagram for above requirements, and specify the keys. 4. (5 points)Convert the following E/R diagram to a relational database schema, indicating the primary keys for the relations. PeopleSalesBanksHousesSellerBuyerLenderInvolvesAmountPriceNoSigndateStateCityStreetHIDNameAddressSSNoNameBuyerRealtorSellerRealtorRealtor: 经纪人第 4 页Question 3 (28 points)The following five tables are for a company management system:EMPLOYEE (SSN, Fname, Lname, Bdate, Address, Sex, Salary, Dnumber)DEPARTMENT (Dnumber, Dname, MgrSSN )PROJECT (Pnumber, Pname, Pcity)WORKS_ON (Pnumber, ESSN, Wdate, Hours)CHILD (ESSN, DSSN, D_Fname, D_Lname, Sex, Bdate)EMPLOYEE: 存储员工的信息，包括社会保险号、名、姓、生日、地址、性别、工资、部门编号DEPARTMENT: 存储部门的信息，包括编号、名称和经理员工号PROJECT: 存储项目的信息，包括项目编号、名称和所在城市WORKS_ON: 存储某员工在某日为某项目工作的时间（小时数）CHILD: 存储员工孩子的信息，包括孩子的社会保险号、名、姓、性别、生日。1. Based on the giving relations, Specify the following queries using the relational operators.(8 points)1) List the names of all employees with birthday earlier than 1970-1-1 and salary less than $5000. (1 point)2) List the names of all employees who have a child with the same first name as themselves. (1 point)3) List the names and cities of all projects which are located on same city. (2 points)4) List the names of all department managers who have no child. (2 points)5) Find the names of all employees in department 5 who have worked on both X project and Y project. (2 points)2. Write the above queries in SQL. Eliminate the duplication in result