數(shù)據(jù)庫系統(tǒng)期末試題及答案

1、華南農(nóng)業(yè)大學(xué)期末測試試卷A卷2021-2021學(xué)年第一學(xué)期測試科目：數(shù)據(jù)庫系統(tǒng)測試類型：閉卷測試時(shí)間：120分鐘學(xué)號(hào)姓名年級(jí)專業(yè)題號(hào)一二三四五六總分得分評(píng)閱人Question 1: true-false question (15 points) For each of the following statements, indicate whether it is TRUE or FALSE (UsingV for TRUE andx for FALSE). You will get 1 point for each correct answer, -0.5 pointfor each inc

2、orrect 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 redundancy improves the integrity of a database.3. SQLis the language used by relationa

3、l databases to create objects and to manipulate and retrieve data.4. A relational database management system does not include tools for backing up & restoring databases.5. An attribute is also known as a row in most databases.6. An association between entities is known as a relationship.7. Integrity

4、 constraints limit the number of entities that can be placed in a table or database.8. The Entity-Relationship data model is often used in the physical design phase.9. The concept relation in relation model is exactly the sameas the concept relationship in ER model.10. Most relationship sets in a da

5、tabase system involves two entity sets.11. The closure of an attribute set contains that attribute set.12. Lossless deposition is necessary in a deposition algorithm.13. If A t B and C t D hold, then AC t BD also holds.14. It is not necessary that a legal schedule preserves the order in which the in

6、structions appear in each individual transaction.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 from the followings:A. R=(R-S) U (R A S)B. R-

7、S=R-(R A S)C. R n S=S-(R-S)D. R n S=S-(S-R)2. Choose the only one correct expression from the followings:.A. ( = some)三 in B. (= all) 三 not in C. exists r :二 r = ? D. X-Y ?二 X 二 Y3. of the following four expressions of relational algebra is not equivalent to the other three? They are all based on th

8、e relations R(A,B) and S(B,C).A. Ea,b(R x S ) B. R x f(B(S ) C. 即舊勇)D. Rc(UA(RXrB(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 LIKE %;A. Q1 and Q2 produce the

9、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. The Entity-Relationship data model is A. DBMSdependent B. DBMSndependent C. both A and B D. neither A nor B6. In SQL, an UPDAT

10、E 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 Cartesianproduct.7. If a course can be taught by many teachers, and a teacher can teach only one course, then the mapping cardinality from course to tea

11、cherisA. one-to-one B. one-to-many C. many-to-one D. many-to-many8. If there is a many-to-one relationship between entity A and B, thendependency from the primary key in B to the t PK(A).dependency from the primary key in A to the t PK(B).A. there exists a functional primary key in A, i.e., PK(B)B.

12、there exists a functional primary key in B, i.e., PK(A)D. neither A nor BR holds on relation R(A, B, C), thenC. both A and B.9. If a functional dependency AB(A, B) is definitely a of R.A. super key B. primary key C. candidate key D. foreign key10. A relational schema R is in if the domains of all at

13、tributesof R are atomicA. 1NF B. 3NF C. BCNF D. 4NF11. Which one of the following statement is true?A. 3NF is more strict than BCNF B. 4NF is more strict than BCNF C. 1NF is more strict than BCNF D. BCNF is the most strict normal form12. If a transaction Ti has obtain an exclusive lock on data item

14、Q, then transition T j can.A. obtain an exclusive lock on data item Q B. obtain a shared lock ondata 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 nearest checkpoint to system crash, then transaction Ti must beA. undone B.

15、 redone C. deleted D. Neither A or B 14.is the final state in a life cycle of a transaction.A. mitted B. aborted C. failed D. A or B15. in 2PL protocol, at stage, A transaction may obtain locks, but may not release locks.A. Shrinking phase B. Growing phase C. mittedD.AbortedQuestion 3 (12 points) Co

16、nsider the following database requirement:A hospital has properties like ID, name, location, rank, capacity. Adoctor can be described by ID, name, age, skill. A patient has properties like ID, name, age, sex, address. The above three entities must satisfysome constraints: Each doctor can be unemploy

17、ed or employed by one hospital. If a doctor is employed, his salary needs to be recorded in the database. A patient can go to many hospitals.1. Draw ER diagram to illustrate the above database requirement8points.2. Translate your ERdiagram into relational database schemas, and pointout the primary k

18、eys and foreign keys. You can write your answers in the following format:“ R(a1, a2, a3, a4), primary key: a1, foreign key:a4 4 points.Question 4. (24 points) The following five tables are for a pany management system:EMPLOYEE (ID, Name, Birthday, Address, Sex, Salary, Dnumber)DEPARTMENT (Dnumber, D

19、name, 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 each).1) List the namesof all employees with birthday earlier than 197

20、0-1-1 and salary less than $5000.2) List the names 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).1) Define the table WORKS_Odeclare Pnumber, E-ID, Wdateas the primary

21、key, Pnumber as the foreign key referencing the primary key of project,E-ID as the foreign key referencing the primary key of employee, and ensure that the values of Hours are non-negative with default value 8.2) For each employee working on the Network project (Pnam, increase his/her salary by 5%.3

22、) List the names of all department managers who have no child.4) Find the names of all employees in department 5 (Dnumber) who haveworked on both X project and Y project (Pnamq.5) For each project, find the project number, project nameand the total hours (by all employees) spent on that project in O

23、ct. 2021.Question 5 (10 points) Consider a relation R(A, B, C, D, E, F) with theset of Functional DependenciesF = A t BCD, BC tDE, Bt D, Dt A 1. pute the Closures of attributesets A+,C +,E + 3points.2. Give one candidate key of R 2 points.3. Is F equivalent to A tBC, BCt E, BtD, Dt A ?2 points4. Wha

24、tis the highestnormalform ofR? Explainyourreasons3points.Question 6 (9 points) There are 3 transactions:Consider the following schedule S on transitions set T1, T2, T3, T4, with R and W denotes read and write operation respectively.S= R1(A) R 2(B)R3(A)R2(C)R4(D)W(B)R1(B)W(D)R3(B)W3(B)W2(C)1. List al

25、l conflict operation pairs in S3 points.2. Swapping no-conflict operations to see whether it is a serializable schedule 3 points.3. Write all its equivalent serial schedules if S is conflictserializable? Or show why if it is not conflict serializable3points.華南農(nóng)業(yè)大學(xué)期末測試試卷A卷-Answer Sheets 2021-2021學(xué)年第1

26、學(xué)期測試科目：Database system測試類型：閉卷測試時(shí)間： 120 分鐘學(xué)號(hào) 姓名 年級(jí)專業(yè) 題號(hào)一二三四五六總分得分評(píng)閱人Instructions to candidates:1. Write your name, student number and class on both the question papersand the answer papers.2. DO NOT write your answers on the question papers. Write them ALL ONTHE ANSWER PAPERS.3. Write your answers in

27、 either Chinese or English.4. Hand in all papers (both the question papers and the answer papers).Question 1 (15 points)題號(hào)123456789101112131415得分VXVXXVXXXVVVVXVQuestion 2 (30 points)題號(hào)123456789101112131415得分CCCDBABBAABCBDB得分Question 3 (12points)得分1：1)2)3) Name Birthday :：1970-1-1ANDSalary ：5000 Empl

28、oyee .1 J二 Name Employee 二 id =e _id Childcity G count (Pnumber)(Project)hospital(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)patient (ID, name, age, sex, address), primary key: I

29、D,livein(patientID, hospitalID) primary key: (hospitalID, PatientID), foreign key hospitalID refer to hospital(ID),foreign key patientID refer to patient (ID)Question 4 24 points2:1) CREATE TALBE Works_On(Pnumber int,E-ID char(15),Wdate date,Hours int default 8 CHECK (Hours = 0),PRIMARY KEY (Pnumber

30、, E-ID, Wdate),FOREIGN KEY (E-ID) REFERENCES Employee(ID),FOREIGN KEY (Pnumber) REFERENCES Project(Pnumber),；2)UPDATE Employee SET Salary=Salary 1.05WHERE IDIN(SELECT E-ID FROM Project natural join Works_on WHERE Pname = Network );3)SELECT Name FROM Employee, DepartmentWHERE ID=MrgID AND ID NOT IN(S

31、ELECT E-ID FROM Child);4)SELECT Name FROM EmployeeWHERE Dnumber=5 AND ID IN(SELECT E-ID FROM Project natural join Works_onWHERE Pname=X)AND ID IN(SELECT E-ID FROM Project natural join Works_onWHERE Pname=Y);5)SELECT Pnumber, Pname, SUM(Hours)FROM Project NATURAL JOIN Works_On得分WHERE Wdate BETWEE N 2021-10-1 AND 2021-10-31 GROUP BY Pnumber, Pname;Question 5 10 points 1 A+=ABCDE, C =C, E +=E2. AF is a candidate key of R3. Yes F equivalent to A BC, BC t E, B t D, D t A 4. the highest normal form of R is 1NF. The reason lies in that (1) itis not in BCNF, for A is not a super key but AtB