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

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

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

2006 学年第一学期 考试科目： 数据库原理与方法

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

学号 姓名 年级专业 2004 题号 得分 评阅人 一 二 三 四 五 六 七 总分 _____________________________________________________________________________ Instructions to candidates:

1. This paper consists of 7 questions.

2. Answer all questions on the answer sheets.

3. Hand in the answer sheets, and this paper can be taken away.

Question 1: (20 marks) Define the following terms. Each term is worth 2 marks.

a. DBMS b. Metadata

c. Entity-relationship data model d. Candidate Key e. DDL and DML f. ACID g. Deadlock

h. Concurrency schedule i. Timestamp j. Log

Question 2: (21 marks) Consider a factory management database including the following data objects:

? factory: Each factory has a name, a location, and a factory director

name. A factory has workshops and depositories.

? workshop: Each workshop has a unique id, a workshop director, a

location and a telephone number. Accessories and products are manufactured in the workshops.

? depository: Each depository has a unique id, a depository director and

a telephone number. Accessories and products are stored in the depository.

? accessory: Each accessory has a unique id, a weight and a price. ? product: Each product has a unique id and a price.

? worker: Each worker has a unique id, a name, an age, a type of work.

Start_date indicates the date of starting to work in a workshop, and employee_length indicates the length of working in a workshop;

The system requirements are:

? A factory has more than one workshop and more than one depository. ? A workshop has more than one workers and each worker only works

for one workshop.

? A workshop can make various accessories and products. The number

of accessories and the number of products manufactured in each workshop is recorded respectively.

? A depositor stores various accessories and products. The number of

accessories and the number of products stored in each depositor is recorded respectively.

? Each accessory can be manufactured by more than one workshop.

Similarly, each product also can be manufactured by more than one workshop.

? A product can be made by various accessories and a kind of accessory

can be used to make various products. The number of accessories each product is made by should be recorded.

a. Draw an E-R diagram design for this database. [7 marks] b. Represent this database design as a set of relation schemas. Select a primary key for each relation (this can be indicated by underlining the selected attributes) and indicate foreign key for each relation as follows:

product_accessory (product_id, accessory_id, accessory_numbers) -- FK product_id reference product -- FK accessory_id reference accessory

[14 marks]

Question 3 (15 marks) Consider a teaching database including student number(S#), course number(C#), grade(G), teacher number(TN), department of teacher(D). The database has the following information:

? student number and course number represent student and course

3