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

华南农业大学期末考试试卷（A卷）2006学年第一学期考试科目：数据库原理与方法考试类型：（闭卷）考试时间：120 分钟参考答案：Question 1:a. A database-management system (DBMS) is a collection of interrelated dataand 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. Itprovides 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, takencollectivly, 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 toaccess 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 itsnormal execution.h.Concurrency schedule consists of a sequence of instructions from varioustransactions 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 activitiesin 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)product(pid, price)worker(worker_id, name, start_date)f_w(name, wid, number)--FK name reference factory--FK wid reference workshopf_d(name, did, number)--FK name reference factory--FK did reference depositorw_a(wid, aid, number)--FK wid reference workshop--FK aid reference accessoryw_p(wid, pid, number)--FK wid reference workshop--FK pid reference productd_a(did, aid, number)--FK did reference depositor--FK aid reference accessoryd_p(did, pid, number)--FK did reference depositor--FK pid reference producta_p(aid, pid, number)--FK aid reference accessory--FK pid reference productw_w(wid, worker_id, number)--FK wid reference workshop--FK worker_id reference workerQuestion 3:a.F={(S#,C#)→G,C#→TN,TN→D}b.1NFc.∵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,office δ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 iwhere m.mno=s.mno and s.ino=i.inoand m.city=’Guangzhou’ and i.ino=’abc2164’ and color = ‘red’b.select mnofrom saleswhere ino=’pc100’intersectselect mnofrom saleswhere ino=’jk375’c.select ino, iname, colorfrom itemwhere ino in ( select inofrom saleswhere mno=’rs225’exceptselect inofrom saleswhere mno=’az507’)d.select ino, max(price), min(price)from salsegroup by inohaving max(price)－min(price)＞100Question 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.Cfrom r1, r2where r1.B = r2. BQuestion 7:a. undo-list: T4redo-list: T2,T3b. 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.。