数据库试题 英文卷

2008－2009学年第一学期考试试题 A卷 考试科目: 数据库系统(双语) 考试时间: 120分钟 试卷总分: 100分

考试班级：软件07-1、2、3、4、5、6班 题号 I II III IV 总分 得分 评卷教师 I、

Blank-filling questions. (2 marks × 10 = 20 marks)

1. Given two original values A=1000, B=2000;

compute both the values of A and B after the transactions T1 and T2 with the right side schedule.

A= ;

B= .

2. The three basic steps in query processing include: parsing and translation,

__________ and . 3. In physical level, the database is stored as a collection of , each of which is a sequence of records. 4. In parallel database systems, a parallel machine consists of a small

number of powerful processors. 5. There are two classical approaches to ensure atomicity despite failures which

are log-based recovery and . 6. A ________ of an entity set is a set of one or more attributes whose values

uniquely determine each entity. 7. Two relational algebra expressions are said to be equivalent if on every

legal database instance the two expressions generate the same set of tuples. 8. To design a trigger mechanism, we must specify the under

which the trigger is to be executed; then specify the actions to be taken when the trigger executes.

软件学院、软件工程系 出题教师：崔香、刘欢 系主任：

第 1 页 共 3 页

II、 Briefly description questions. (30 marks）

1. What is the purpose of the index mechanism in database system? And give the

definitions of primary index and secondary index. (5 marks) 2. List the advantages of using Database System to store data. (5 marks) 3. Compute the closure of the following set F of functional dependencies for relation schema R = (A, B, C, D, E). F ={A →BC, CD →E, B → D, E → A },

a) list the candidate keys for R. (5 marks) b) compute the canonical cover Fc. (5 marks)

4. Compute the two relational algebra expressions based on the following two relations R and S: (10 marks)

a) b)

A1，A2A2\"a\"R (5 marks)

A1\"a\"(RS) (5 marks)

III、 Query questions. (4 marks × 10 = 40 marks) Consider the academic database contains three relations as the following 3 tables, and then give an expression in SQL for each of the following queries.

Table ‘S’ Attribute name Data type Primary key Description Sno int √ Student numbers Sname Char(20) Student names AGE int Student ages SEX Char(4) ‘M’ for male, ‘F’ for female Table ‘SC’ Attribute name Data type Primary key Description Sno int √ Student numbers Cno int √ Course numbers Grade int Student grades for courses 2009年 1月7日

第 2 页 共 3 页

哈尔滨理工大学

2008－2009学年第一学期考试试题 A卷 Table ‘C’ Attribute name Data type Primary key Description Cno int √ Course numbers Cname Char(35) Course names TEACHER Char(20) Teacher names Give an expression in the relational algebra to express each of queries 1, 2, 3 and give an expression in SQL for each of queries 4, 5, 6, 7, 8, 9, 10.

1. Search the course numbers and course names of all the courses which are

taught by teacher LIU.

2. Search the names of the students, each of who takes at least one course taught

by teacher LIU.

3. Search the student numbers of the students, each of who takes the entire

courses taught by teacher LIU. 4. Define the relation ‘SC’ in SQL. Tip: Describe primary keys, foreign keys and

check constrains if necessary. 5. Search the course names and teacher names of the student with number S3. 6. Count the courses taken by some student(s).

7. Search the average age of the female students who take course C4.

8. Search the names and ages of all the students whose names all begin with ‘L’. 9. Insert in relation C a new course which is ‘VC++’ and taught by ‘BAO’ with

course number C8. 10. Change all the failed grade records of course ‘MATHS’ into 60. IV、 E-R Designing questions. (10 marks.)

A car insurance company maintains data about the following entities: (a) person, including driver id, name and address; (b) car, including car license, car model, year of made;

(c) accident, including report numbers, accident date and location.

Construct an E-R diagram for a car insurance company whose customer owns one or more cars each. Each car has associated with it zero to any number of recorded accidents. The user should be able to search the cars of a certain driver. And for each accident, the user of the company should record the damage amount of each car own by a specific driver.

软件学院、软件工程系 出题教师：崔香、刘欢 系主任：

第 3 页 共 3 页