华南农业大学期末考试试卷(A)2006数据库答案

华南农业大学期末考试试卷（A卷）d 2006学年第一学期考试科目：数据库原理与方法考试类型：（闭卷）考试时间：120 分钟

参考答案：

Question 1:

a.A database-management system (DBMS) is a collection of interrelated data

and a set of programs to access those data.

b.Metadata is data about data.

c.The E-R data model is a widely used data model for database design. It

provides a convenient grapical representation to view data, relationships, and constraints.

d.A superkey of an entity set is a set of one or more attributes that, taken

collectivly, allows us to identify uniquely an entity in the entity set. Such minnimal superkeys are called candidate keys.

e.A data-manipulation language (DML) is a language than enables users to

access or manipulate data. A data-definition language (DDL) is a language for specifying the database schema and as well as other properties of the data.

f.Transactions are required to have the ACID properties: atomicity,

consistency, isolation, and durability. Atomicity ensures that either all the effects of transaction are reflected in the database, or none are; Consistency ensures that, if the database is initially consistent, the execution of the transaction leaves the database in a consistent state. Isolation ensures that concurrently executing transactions are isolated from on another. Durablity ensures that, once a transaction has been committed, that transaction’s udpates do not get lost, even if there is a system failure.

g.It is a state where neither of these transactions can ever proceed with its

normal execution.

h.Concurrency schedule consists of a sequence of instructions from various

transactions which run concurrently.

i.A unique fixed timestamp is associated with each transaction in the system.

The timestamp of the transaction determine the serializability order.

j.The most widely used structure for recording database modifications is the log. The log is a sequence of log records, recording all the update activities

in the database.

Question 2:

a.

b: Relation schemas:

factory(name, director, location) workshop(wid, director, location, telephone) depositor(did, director, telephone) accessory(aid, weight, price)

--FK name reference factory

--FK name reference factory

--FK wid reference workshop

--FK wid reference workshop

--FK did reference depositor

--FK did reference depositor

--FK aid reference accessory

--FK wid reference workshop

--FK worker_id reference worker

Question 3:

a.F={(S#,C#)→G,C#→TN,TN→D}

b.1NF

c.∵Fm={(S#, C#)→G,C#→TN,TN→D}

∴P={R1,R2,R3}

The definition of R1,R2 and R3 is:

R1=(S#, C#, G)

R2=(C#, TN)

R3=(TN, D)

Question 4:

a. ∏empnoδpublisher=’McGraw-Hill’(Loan Books)

b. ∏name,age δpublisher=’McGraw-Hill’(Employee Loan Books)

c. ∏name,o ffice δpublisher=’McGraw-Hill’∧ year>2000 (Employee Loan Books)

d. ∏empno (Loan)－∏empnoδpublisher=’McGraw-Hill’(Loan Books)

Question 5:

a.select avg(price)

from market m, sales s, item i

where m.mno=s.mno and s.ino=i.ino

and m.city=’Guangzhou’ and i.ino=’abc2164’ and color = ‘red’b.select mno

from sales

where ino=’pc100’

intersect

select mno

from sales

where ino=’jk375’

c.select ino, iname, color

from item

where ino in ( select ino

from sales

where mno=’rs225’

except

select ino

from sales

where mno=’az507’)

d.select ino, max(price), min(price)

from salse

group by ino

having max(price)－min(price)＞100

Question 6:

a.(select * from r1) union (select * from r2)

b.(select * from r1) intersect (select * from r2)

c.(select * from r1) except (select * from r2)

d.select r1.A, r2.B, r1.C

from r1, r2

where r1.B = r2. B

Question 7:

a. undo-list: T4

redo-list: T2,T3

b. undo(T i) restores the value of all data items updated by transaction T i to the old values.

redo(T i) sets the value of all data items updated by transaction T i to the new values.