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

华南农业大学期末考试试卷（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 √ for TRUE and × for FALSE). You will get 1 point for each correct answer, -0.5 point for 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 redundancy improves the integrity of a database.
3. SQL is the language used by relational 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 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 same as the concept “relationship” in ER model.
10. Most relationship sets in a database 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 → B and C → D hold, then AC → BD also holds.
14. It is not necessary that a legal schedule preserves the order in which the instructions 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)∪(R∩S) B. R-S=R-(R∩S)
C. R∩S=S-(R-S) D. R∩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 Í Y
3. 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. 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.
5. The Entity-Relationship data model is
A. DBMS dependent B. DBMS independent C. both A and B D. neither A nor B
6. 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 the mapping cardinality from course to teacher is
A. one-to-one B. one-to-many C. many-to-one D. many-to-many
8. If there is a many-to-one relationship between entity A and B, then
A. 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 exists 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 B
9. If a functional dependency AB→R holds on relation R(A, B, C), then (A, B) is definitely a ______ of R.
A. super key B. primary key C. candidate key D. foreign key
10. A relational schema R is in _____ if the domains of all attributes of R are atomic
A. 1NF B. 3NF C. BCNF D. 4NF
11. 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 form
12. If a transaction Ti has obtain an exclusive lock on data item Q, then transition Tj can ______.
A. obtain an exclusive lock on data item Q B. obtain a shared lock on data item Q
C. wait for lock granting on data item Q D. read or write Q without a lock
13. If both <Ti start> and <Ti mit> appear in the log after the nearest checkpoint to system crash, then transaction Ti must be ______
A. undone B. 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 B
15. in 2PL protocol, at stage, A transaction may obtain locks, but may not release locks.
A. Shrinking phase B. Growing phase C. mitted D. Aborted

Question 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, name, age, skill. A patient has properties like ID, name, age, sex, address. The above three entities must satisfy some constraints: Each doctor can be unemployed 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 requirement [8 points].
2. Translate your ER diagram into relational database schemas, and point out the primary keys 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, 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 each).
1) List the names of all employees with birthday earlier than ‘1970-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_ON, declare Pnumber, E-ID, Wdate as the primary 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 (Pname), increase his/her salary by 5%.
3) List the names of all department managers who have no child.
4) Find the names of all employees in department 5 (Dnumber) who have worked on both 'X' project and 'Y' project (Pname).
5) For each project, find the project number, project name and the total hours (by all employees) spent 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 Dependencies
F = { A → BCD, BC → DE, B → D, D → A }
1. pute the Closures of attribute sets A+, C+, E+ [3 points].
2. Give one candidate key of R [2 points].
3. Is F equivalent to { A → BC, BC → E, B → D, D → A } ? [2 points]
4. What is the highest normal form of R? Explain your reasons [3 points].

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) 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 equivalent serial schedules if S is conflict serializable? 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. 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)
题号
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
得分
√
×
√
×
×
√
×
×
×
√
√
√
√
×
√

Question 2 (30 points)
题号
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
得分
C
C
C
D
B
A
B
B
A
A
B
C
B
D
B

得分

Question 3 (12points)

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: ID,
livein(patientID, hospitalID) primary key: (hospitalID, PatientID), foreign key hospitalID refer to hospital(ID)，
foreign key patientID refer to patient (ID)

得分

Question 4 [24 points]

1：
1)
2)
3)

2：
1) CREATE TALBE Works_On(
Pnumber int,
E-ID char(15),
Wdate date,
Hours int default 8 CHECK (Hours >= 0),
PRIMARY KEY (Pnumber, E-ID, Wdate),
FOREIGN KEY (E-ID) REFERENCES Employee(ID),
FOREIGN KEY (Pnumber) REFERENCES Project(Pnumber),
);
2)
UPDATE Employee SET Salary=Salary*1.05
WHERE ID IN
(SELECT E-ID FROM Project natural join Works_on
WHERE Pname=’Network’);

3)
SELECT Name FROM Employee, Department
WHERE ID=MrgID AND ID NOT IN
(SELECT E-ID FROM Child);
4)
SELECT Name FROM Employee
WHERE Dnumber=5 AND ID IN
(SELECT E-ID FROM Project natural join Works_on
WHERE Pname=’X’)
AND ID IN
(SELECT E-ID FROM Project natural join Works_on
WHERE Pname=’Y’);
5)
SELECT Pnumber, Pname, SUM(Hours)
FROM Project NATURAL JOIN Works_On
WHERE Wdate BETWEEN ‘2009-10-1’ AND ‘2009-10-31’
GROUP BY Pnumber, Pname;

得分

Question 5 [10 points]

1 A+=ABCDE, C+ =C, E+=E
2. AF is a candidate key of R
3. Yes F equivalent to { A → BC, BC → E, B → D, D → A }
4. the highest normal form of R is 1NF. The reason lies in that (1) it is not in BCNF, for A is not a super key but A → BCD. (2) It is not in 3NF for all candidate keys are: DF, AF, BF, so that primary attributes are ABDF. To A → BC, A is not a super key and c is not included in any candidate key therefore it violate rules of 3NF. (3) all attribute are atomic.

得分

Question 6 [9 points]

1. conflict operation pairs (1)W2(B)R1(B) (2) W2(B)R3(B) (3)W2(B)W3(B) (4) R1(B)W3(B) (5) R2(B) W3(B) (6) R4(D) W1(D)
2. S= R1(A) R2(B)R3(A)R2(C)R4(D)W2(B)R1(B)W1(D)R3(B)W3(B)W2(C)
=> R2(B)R1(A) R2(C) R3(A) W2(B) R4(D) R1(B)W1(D)R3(B) W2(C)W3(B)
=> R2(B) R2(C)R1(A) W2(B) R3(A) R4(D) R1(B)W1(D) W2(C)R3(B) W3(B)
=> R2(B) R2(C) W2(B) R1(A) R4(D) R3(A) R1(B) W2(C)W1(D) R3(B) W3(B)
=> R2(B) R2(C) W2(B) R4(D)R1(A) R1(B) R3(A) W2(C) W1(D) R3(B) W3(B)
=> R2(B) R2(C) W2(B) R4(D) R1(A) R1(B) W2(C) R3(A) W1(D) R3(B) W3(B)
=> R2(B) R2(C) W2(B) R4(D) R1(A) W2(C)R1(B) W1(D) R3(A) R3(B) W3(B)
=> R2(B) R2(C) W2(B) R4(D) W2(C)R1(A) R1(B) W1(D) R3(A) R3(B) W3(B)
=> R2(B) R2(C) W2(B) W2(C)R4(D) R1(B) W1(D) R3(A) R3(B) W3(B)
3. it is a conflict serializable schedule. The equivalent serial schedules are:
T2, T4, T1, T3 or T4, T2, T1, T3