数据库原理与应用期末试卷（1）SCAU

华南农业大学期末考试试卷（A卷）

2019-2020学年第一学期　             考试科目： 数据库系统（双语）

考试类型：闭卷               考试时间： 120   分钟

学号              姓名               年级专业                     

题号	一	二	三	四	五	总分
得分
评阅人

Instructions to candidates:

1. Write your name, student number and class on both the question papers and the answer papers. And write ALL YOUR ANSWERS ON THE ANSWER PAPERS.

2. Write your answers in either Chinese or English.

3. Hand in all papers (both the question papers and the answer papers).            

得分

Question 1 (40 points, 2 points for each problem) single-choice question, select the most appropriate answer please.

1. Which one of the following statements is equal to RÇS ? ____________

R-(R-S)

R-(S-R)

S-(R-S)

S-R

1. The ______ operation in relational algebra requires the two participating relations have the same number of attributes.

Outer join

Natural join

Cartesian product

union

1. The PROJECTION operation in relational algebra corresponds to the ______ clause in a SQL statement.

HAVING

SELECT

FROM

WHERE

4.  The relation R(A,B,C) consists of 10 tuples. The relation S(A,D,E,F) consists of 15 tuples. Then result of the operation "R natural full outer join S" IMPOSSIBLY consists of      .

A．	20 rows and 6 columns	B．	10 rows and 6 columns
C．	25 rows and 6 columns	D．	15 rows and 6 columns

5.  ____________is the command to remove a table.

A．DELETE TABLE	B．DROP TABLE
C．REVOKE TABLE	D．REMOVE TABLE

6.  ____________is the correct statement to find all the students whose family name is NOT 'Zhang'.

A．SELECT name FROM student WHERE name != 'Zhang_'

B．SELECT name FROM student WHERE name < > 'Zhang%'

C．SELECT name FROM student WHERE name not like 'Zhang%'

D．SELECT name FROM student WHERE name is not like 'Zhang%'

7.  In SQL,               is an equivalent operator to “NOT IN”

< > ALL

< > SOME

=ALL

=SOME

8.  Which statement(s) in the followings is/are true?________________

“7 > null” evaluates to null

“null = null” evaluates to true

(‘A’,null), (‘A’,null) are treated as being identical.

All of the above

9. Programmers interact with data in the _______ level.

A. physical    B. logical   C. view   D. all of the above

10. If the mapping cardinality from entity set A to entity set B is many to one, then an entity of A is associated with ____ entity (entities) of B.

A. at most one     B. at least one   C. arbitrary   D. one and only one

11. A minimal set of one or more attributes that suffice to uniquely distinguish each entity is called        

A. super key    B. candidate key  C. primary key D. foreign key

12.         is a nonprocedural language used in commercial database systems.

   A. Relational algebra               B. Tuple relational calculus  

C. SQL                          D. Domain relational calculus  

13. A relation schema is a set of __________.  

A. tuples      B. attributes   C. columns      D. rows

14. Which attribute is characterized in relational database system?

A. Simple attribute        B. Composite attribute

C. Multi-valued attribute      D. Derived attribute

15. E-R diagrams use _________ to represent relationship sets.  

  A. Rectangle     B. Diamond       C. Circle       D. Line

16._______ is a possible functional dependency for the following relation.

A.  A ---> (D,E)   B. D--> A

C.  E---> D      D. E--> (A,D)

17.  Suppose that we decompose the schema r(A,B,C) into r1(A,B) and r2(A,C). If  functional dependencies ____ holds, this decomposition is a lossless decomposition.

A.  B --->A or C--->A     B. (B,C) --->A

C.  A --->B or A--->C      D. None

18. Suppose R(X,Y,Z) has three single attributes X, Y and Z. If (X,Y,Z) is a candidate key, the statement ______ holds.

A.  R

BCNF            B.  R

BCNF

C.  It cannot be determined that whether R

BCNF or not

D.  It cannot be determined that whether R

BCNF or not. However, R

3NF

19. ______ ensures that either all the effects of a transaction are reflected in the database, or none are.

A.  Atomicity   B.  Consistency

C.  Isolation    D.  Durability

20. A checkpoint is not performed through ____

A. Output onto stable storage all log records currently residing in main memory

B. Output to the disk all modified buffer blocks

C. Write to a buffer block

D. Output onto stable storage a log record of the form <checkpoint L>

得分

Question 2 

(22 points) Consider the following schemes in a library database:

member(no, name, age)

book(isbn, title, author, publisher)

borrowed(mem_no, isbn, date)

1. Find the isbn of all books that were borrowed on '2019-12-25' in relational algebra. (2 points)
2. Find the names of members who borrowed the book 'Database System' in relational algebra and SQL. (6 points)
3. Find the amount of books written by 'Lu Xun' for each publisher in relational algebra and SQL. (6 points)
4. Find the member no.(s) who have borrowed more books than the member with no. '1001’ in SQL (4 points)
5. Find the name of members who have borrowed all the book published by 'SCAU Press' in SQL (4 points)

得分

Question 3

(13 points)

(1) Draw an E-R diagram for the following situation: A novel is written by one and only one writer. After the novel is written, it’s published by one and only one publisher. Each novel can be sold in many different bookstores with different prices. A novel has attributes like ID, name, words, type. A writer has attributes like ID, name, nationality, birthday. A publisher has attributes like ID, name, asset, phone number. A bookstore has attributes like ID, name, location, phone number. (7 points)

(2) Translate your E-R diagram to relational schemas. Specify the primary and foreign keys of each schema. You can write your answers in the following format:  “R(a1, a2, a3, a4), foreign key: a4”. (6 points)

得分

Question 

4 (12 points)

 Consider the following set F of functional dependencies on the relation schema R(A,B,C,D):

A

C

C

A

B

AC

D

AC

1. Compute (AD)+. （2 points）
2. Compute a canonical cover for the above set of functional dependencies F; give each step of your derivation with an explanation.  (2 points)
3. List the candidate keys for R.  (2 points)
4. Give a decomposition of R with only one time of BCNF decomposition using the original set of functional dependencies. (3 points)
5. Give a 3NF decomposition of R based on the canonical cover. (3points)

得分

Question 5 (13 points) Consider the following two transactions:

    T1: read(A)

       read(B)

       If A=1 then B:=B*2;

       write(B)

    T2: read(B)

       read(A)

       If B=1 then A:=A*2;

       write(A)

   Let the consistency requirement be A=1 or B=1, with A=B=1 the initial values.

1. 

   

   Show that every serial execution involving these two transactions preserves the consistency of the database and compute the final results of A and B. (2 points)
2. Add lock and unlock instructions to transaction T1 and T2, so that they observe the two-phase locking protocol. Can the execution of these transactions result in a deadlock? (4 points)
3. Show a concurrent execution of T1 and T2 that produces a nonserializable schedule. (4 points)
4. Explain the distinction between the terms serial schedule and serializable schedule. (3 points)

华南农业大学期末考试试卷（A卷）答案

Question 1.

题号	1	2	3	4	5	6	7	8	9	10
得分	A	D	B	B	B	C	A	C	B	A
题号	11	12	13	14	15	16	17	18	19	20
得分	B	C	B	A	B	A	C	A	A	C
评阅人

Question 2.

1. ∏isbn(σdate='2019-12-25'(borrowed))

2.  ∏name(σno=mem_no⋀ title='Database System'(member×(borrowed⋈book))

select name

from member, borrowed natural join book

where no=mem_no and title='database system'

3.   publisherGcount(isbn) (σauthor='Lu Xun' (book))

select publisher, count(isbn)

from book

where author = 'Lu Xun'

group by publisher

4. select mem_no

from borrowed

group by mem_no

having count(isbn)>(select count(isbn)

from borrowed

where mem_no='1001')

5. select name

from member as m

where not exist ( (select isbn

from book

where publisher='SCAU press')

except

(select isbn

from borrowed

where mem_no=m.no) )

Question 3.

Novel(ID, name, words, type, publisherID, writerID), foreign key: publisherID (reference publisher(ID)), writerID (reference writer(ID))

Writer(ID, name, nationality, birthday)

Publisher(ID, name, asset, phone)

Bookstore(ID, name, location, phone)

Sold(novel_ID, bookstore_ID, price), foreign key: novel_ID(reference novel(ID)), bookstore_ID(reference bookstore(ID))

Question 4.

(1)(AD)+=ACD

(2)

注多个答案：Fc={ A

C, C

A, B

C, D

A } 或 Fc={ A

C, C

A, B

A, D

C } 或Fc={ A

C, C

A, B

C, D

C }

(3)BD

(4)（三个答案）考虑A

C 或 C

A, 将ABCD分解为AC和ABD

考虑B

AC, 将ABCD分解为ABC和BD

考虑D

AC, 将ABCD分解为ACD和BD

(5)对Fc={ A

C, C

A, B

A, D

A}分解为AC，BA，DA，BD.

（注：对其它正则覆盖，可得到不同答案。）

Question 5.

（1）两个串行执行序列T1T2或T2T1 扫行结果分别是 A=1，B=2 和A=2，B=1。两个结果都满足A=1或B =1的一致性条件。

（2）添加加锁和解锁操作，使其满足2PL：

T1:

lock-S(A)

read(A)

lock-X(B)

read(B)

if A = 1

then B := B*2

write(B)

unlock(A)

unlock(B)

T2：

lock-S(B)

read(B)

lock-X(A)

read(A)

if B = 1

then A := A *2

write(A)

unlock(B)

unlock(A)

添加加锁和解锁操作后有可能会死锁。例如：

T1        T2

此时T1，T2 互相等待，死锁。

（3）Any interleaving of T1 and T2 results in a non-serializable schedule.

T1                        T2

read(A)

read(B)

read(A)

read(B)

if A = 0 then B = B + 1

if B = 0 then A = A + 1

write(A)

write(B)

（4）A schedule in which all the instructions belonging to one single transaction appear together is called a serial schedule. A serializable schedule is a concurrent schedule which has a weaker restriction that it should be equivalent to some serial schedule.