2014数据库系统期末试题及答案

1、华南农业大学期末考试试卷（A卷）2014-2015学年第一学期考试科目：数据库系统考试类型：闭卷考试时间：120 分钟学号姓名年级专业题号-一一二二二-三四五六总分得分评阅人Question 1: true-false question (15 points) For each of the following statements, indicate whether it is TRUE or FALSE (Using V for TRUE and x for FALSE). You will get 1 point for each correct answer, -0.5 point f

2、or each incorrect answer, and 0 point for each answer left blank. Be sure to write your answer in the answer sheet.1. A primary key is a field (or group of fields) that uniquely describes each record in the database.2. Data redu ndancy improves the in tegrity of a database.3. SQL is the Ian guage us

3、ed by relati onal databases to create objects and to man ipulate and retrieve data.4. A relati onal database man ageme nt system does not in clude tools for back ing up & restori ng databases.5. An attribute is also known as a row in most databases.6. An associati on betwee n en tities is known as a

4、 relati on ship.7. Integrity constraints limit the number of entities that can be placed in a table or database.8. The En tity-Relati on ship data model is ofte n used in the physical desig n phase.9. The concept“relation ” in relation model is exactly the same as the concept“relati on ship” moER.10

5、. Most relati on ship sets in a database system invo Ives two en tity sets.11. The closure of an attribute set contains that attribute set.12. Lossless decomposition is necessary in a decomposition algorithm.13. If A f B and C D hold, then AC BD also holds.14. It is not necessary that a legal schedu

6、le preserves the order in which thein structi ons appear in each in dividual tran sact ion.15. Update operations in database must be written into log before updating database.Question 2 single-choice question (2 points for each problem, 30 points in total)1. is the only one incorrect description fro

7、m the followings:A. R=(R-S) U (R A S)B. R-S=R-(R A S)C. RA S=S-(R-S)D. RA S=S-(S-R)2. Choose the only one correct expression from the followings:.A. (式 some)三 in B. (= all)三 not in C. exists ru r 丰? D. X-Y wX 9 Y3. of the following four expressionsof relational algebra is notequivalent to the other

8、three? They are all based on the relations R(A,B) and S(B,C).A.Ea,b(R x S ) B. RE Db(S) C. a,r.b(恥 S) d.T!a(R) FIb(S)4. 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

9、 LIKE %;A. Q1 and Q2 produce the same an swer.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 differe nt an swers.5. The Entity-Relationship data model isA. DBMS dependent B. DBMS independent C. both A and B

10、 D. neither A nor B6. In SQL, an UPDATE statement without a WHERE clause:A. Updates every row in a table. B. Updates no rows in a table.C. Updates every column in a table. D. Results in a Cartesian product.7. If a course can be taught by many teachers, and a teacher can teach only one course, then t

11、he mapping cardinality from course to teacher isA. on e-to-oneB. on e-to-ma nyC. many-to-oneD. many-to-ma ny8. If there is a many-to-one relationship between entity A and B, thenA. there exists a functional dependency from the primary key in B to the primary key in A, i.e., PK(B) PK(A).B. there exis

12、ts a functional dependency from the primary key in A to the primary key in B, i.e., PK(A) PK(B).C. both A and B.D. neither A nor B9. If a functional dependency AB R holds on relation R(A, B, C), then (A, B) isdefinitely aof R.A. super key B. primary key C. can didate key D. foreig n key10. A relatio

13、nal schema R is inif the domains of all attributes of R areatomicA. 1NF B. 3NF C. BCNF D. 4NF11. Which one of the following statement is true?A. 3NF is more strict than BCNFB. 4NF is more strict than BCNFC. 1NF is more strict than BCNFD. BCNF is the most strict normal form12. If a transaction Ti has

14、 obtain an exclusive lock on data item Q, thentransition Tj can.A. obtain an exclusive lock on data item Q B. obtain a shared lock on data item QC. wait for lock granting on data item Q D. read or write Q without a lock13. If both and appear in the log after the nearestcheckpoint to system crash, th

15、en transaction Ti must beA. un do neB. redo neC. deleted D. Neither A or B14. is the final state in a life cycle of a transaction.A. committed B. aborted C. failed D. A or B15. in 2PL protocol, at stage, A transaction may obtain locks, but maynot release locks.A. Shrinking phase B. Grow ing phase C.

16、 Committed D. AbortedQuestion 3 (12 points) Consider the following database requirement:A hospital has properties like ID, name, location, rank, capacity. A doctor can be described by ID, n ame, age, skill. A patie nt has properties like ID, n ame, age, sex, address. The above three en tities must s

17、atisfy some con stra in ts: Each doctor can be un employed or employed by one hospital. If a doctor is employed, his salary n eeds to be recorded in the database. A patie nt can go to many hospitals.1. Draw ER diagram to illustrate the above database requiremeffi points.2. Tran slate your ER diagram

18、 into relatio nal databaseschemas, a nd point out the primary keys and foreig n keys. You can write your an swers in the followi ng format:R(a1, a2, a3, a4), primary key: a1, foreig n key: a4 4 points.Question 4. (24 points) The following five tables are for a company management system:EMPLOYEE (ID,

19、 Name, Birthday, Address, Sex, Salary, Dnumber) DEPARTMENT (Dnumber, Dname, MgrID )PROJECT (Pnumber, Pname, Pcity)WORKS_ON (Pnumber, E-ID, Wdate, Hours)CHILD (E-ID, CHD-ID, CHD_name, Sex, Birthday)1. Based on the giving relations, Specify the following queries using relational algebra (3 points for

20、each).1) List the n ames of all employees with birthday earlier tha n -1-1 and sdia9y7O less than $5000.2) List the n ames of all employees who have a child.3) List the cities and the total number of projects which are located on same city.2. Specify the following operations in SQL(3 points for each

21、).1) Define the table WORKS_ON, declare Pnu mber, E-ID, Wdate as the primary key, Pnumber as the foreign key referencing the primary key of project, E-ID as the foreig n key refere ncing the primary key of employee, and en sure that the values of Hours are non-n egative with default value 8.2) For e

22、ach employee working on the Network (Ppaojctincrease his/hersalary by 5%.3) List the n ames of all departme nt man agers who have no child.4) Find the n ames of all employees in departme nt 5 (Dnu mber) who have worked on both X project and Y project (Pn ame).5) For each project, find the project nu

23、 mber, project n ame and the total hours (by all employees) spe nt on that project in Oct. 2009.Question 5 （10 points） Consider a relation R（A, B, C, D, E, F） with the set of Functional DependenciesF = A BCD, BC DE, B D, D A 1. Compute the Closures of attribute sets A C+, E+ 3 points.2. Give one can

24、 didate key of R2 points.3. Is F equivale nt to A BC, BC E, B D, D A ? 2 points4. What is the highest normal form of R? Explain your reason$3 points.Question 6 （9 points） There are 3 transactions:Consider the following schedule S on transitions set T1, T2, T3, T 4, with R and W deno tes read and wri

25、te operati on respectively.S= Ri（A） R2（B）R3（A）R2（C）R4（D）W2（B）R1（B）W1（D）R3（B）W3（B）W2（C）1. List all conflict operation pairs in S 3 points.2. Swapping no-conflict operations to see whether it is a serializable schedule 3 points.3. Write all its equivale nt serial schedulesif S is con flict serializabl

26、e? Or show why if it is not conflict serializable 3 points.华南农业大学期末考试试卷（A卷-Answer Sheets ）2014-2015学年第1学期考试科目： Database system考试类型：（闭卷）考试时间：120 分钟学号姓名年级专业 题号一一一二二二-三四五六总分得分评阅人Instructions to candidates:1. Write your name, student number and class on both the question papers and the answer papers.2.

27、DO NOT write your answers on the question papers. Write them ALL ON THE ANSWER PAPERS.3. Write your answers in either Chinese or English.4. Hand in all papers (both the question papers and the answer papers).Question 1 (15 points)题号123456789101112131415得分VXVXXVXXXVVVVXVQuestion 2 (30 points)题号123456

28、789101112131415得分CCCDBABBAABCBDB得分patientname age sex addressdoctorQuestion 3 (12points)nameagekllllhospital(ID, name, location, rank, capacity), primary key: ID,doctor(ID, name, age, skill, hospitalID, salary), primary key: ID, foreign key: hospitalID refer to hospital(ID)patie nt (ID, n ame, age,

29、sex, address), primary key: ID,live in (patie ntID, hospitalID) primary key: (hospitalID, Patie ntID), foreig n key hospitalID refer to hospital(ID),得分foreig n key patie ntID refer to patie nt (ID)Question 4 24 points1:Name :- Birthday ：1970-1-1ANDSalary :：5000Employee2)二 Name Employee 二 id_id Child

30、3)citycou nt(Pn umber)(Project)2：1) CREATE TALBE Works_On(Pnu mber int,E-IDchar(15),Wdatedate,Hoursint default 8 CHECK (Hours= 0),PRIMARYKEY (Pnu mber,E-ID,Wdate),FOREIGN KEY (E-ID) REFERENCES Employee(ID),FOREIGN KEY (Pnu mber) REFERENCES Project(P nu mber), );2)UPDATE Employee SET Salary=Salary -1

31、.05WHERE ID IN(SELECT E-ID FROM Project n atural joi n Works_on WHERE Pname= Network );SELECT Name FROM Employee, Departme ntWHERE ID=MrgID AND ID NOT IN(SELECT E-ID FROM Child);SELECT Name FROM EmployeeWHERE Dnu mber=5 AND ID IN(SELECT E-ID FROM Project n atural joi n Works_on WHERE Pname= X)AND ID

32、 IN(SELECT E-ID FROM Project n atural joi n Works_on WHERE Pname= Y);5)SELECT Pnu mber, Pn ame, SUM(Hours)FROM Project NATURAL JOIN Works_OnWHERE Wdate BETWEEN 2009-10-1 AND 2-3(0031得分GROUP BY Pnu mber, Pn ame;Question 5 10 points 1 A+=ABCDE, C + =C, E+=E2. AF is a can didate key of R3. Yes F equivale nt to Af BC, BC f E, B f D, D f A 4. the highest normal form of R is 1NF. The reason lies in that (1) it is not in B