华南农业大学期末考试试卷(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 respectively.  for each course a student enrolls, he receives a grade.

 each course can only be taught by one teacher, but a teacher can teach multiple courses.

 each teacher only can work for one department.

2

a. Define the set of non trivial functional dependencies. [6 marks] b. Suppose all the five attributes form a relation R, indicate in which normal form the relation is. [3 marks]

c. Give a lossless, dependency-preserving decomposition in to 3NF of schema R. [6 marks]

Question 4 :(12 marks) Consider the following relational schema:

Employee (empno, name, office, age) Books (isbn, title, authors, publisher) Loan (empno, isbn, year)

Write the following queries in relational algebra. All queries carry equal marks.

a. Find the empno of employees who have borrowed a book published by McGraw-Hill.

b. Find the name and age of employees who have borrowed a book published by McGraw-Hill.

c. Find the name and office of employees who have borrowed a book published by McGraw-Hill since 2000.

d. Assume each employee only can borrow one book. Find the empno of employees who have not borrowed a book published by McGraw-Hill.

Question 5: (16 marks) Consider the following schema of a sales database: market(mno, street, city) item(ino, iname, color) sales(mno, ino, price)

The market relation provides a list of markets with market number(mno), street and city where the market lies. The mno is unique for each market. The item relation provides a list of items with item number(ino), item name(iname) and the color. The ino is unique for each item. The sales relation lists for the price of each item in each market. The key of this relation is mno and ino.

Write the following queries in SQL. All queries carry equal marks.

a. Find the average price of the item with ino ‘abc21’ which is sold in Guangzhou and color is red.

b. Find the mnos of the markets which hold the items ’pc100’ and ‘jk375’.

c. Find the inos, inames, and colors of the items which are sold in market ‘rs225’ but not in market ’az507’.

d. Find the ino, the highest price and the lowest price of each item whose highest

3

price is greater than its lowest price by 100.

Question 6: (8 marks) Let R = (A, B, C), and let r1 and r2 both be relations on schema R, give an expression in SQL that is equivalent to each of the following queries.

a. r1 ∪ r2 b. r1 ∩ r2 c. r1 - r2

d. ∏AB(r1) ∏BC(r2)

Question 7: (8 marks) Figure 1 indicates the log of serial transactions. A is recorded when time is Tc and the system failure occurs when time is Tf. When the system recovers from the crash, the undo and redo operations should be applied.

TcT1T2T3T4Tfcheck pointsystem failure

Figure 1: the log of serial transactions

a. Construct the undo-list and redo-list.

b. Explain the difference of undo and redo operation.

4

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

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

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

学号 姓名 年级专业 2004 题号 得分 评阅人 Question 1: 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

5

一 二 三 四 五 六 七 总分