08-09 A卷试题

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 T 1 and T 2 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 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.

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 F c . (5 marks)

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

a) ()()R a A ""2A 2

A 1=∏

δ， (5 marks)

b)

)(""1S R a A ∞=δ (5 marks)

III 、 Query questions. (4 mark s × 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

2008－2009学年第一学期考试试题A 卷

Table ‘SC’

Attribute name Data type Primary key Description

Sno int √Student numbers

Cno int √Course numbers

Grade int Student grades for courses

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.