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 ofwhich is a sequence of records. 4. In parallel database systems, a parallel machine consists of asmall number of powerful processors. 5. There are two classical approaches to ensure atomicity despite failureswhich are log-based recovery and . 6. A ________ of an entity set is a set of one or more attributes whose valuesuniquely determine each entity. 7. Two relational algebra expressions are said tobe if on everylegal database instance the two expressions generate the same set of tuples.装订线班级：学号：姓名：8. To design a trigger mechanism, we must specify the underwhich 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 givethe 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 2A 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 female2008－2009学年第一学期考试试题A 卷Table ‘SC’Attribute name Data type Primary key DescriptionSno int √Student numbersCno int √Course numbersGrade int Student grades for coursesTable ‘C’Attribute name Data type Primary key DescriptionCno int √Course numbersCname Char(35) Course namesTEACHER Char(20) Teacher namesGive 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 aretaught by teacher LIU.2.Search the names of the students, each of who takes at least one coursetaught by teacher LIU.3.Search the student numbers of the students, each of who takes the entirecourses taught by teacher LIU.4.Define the relation ‘SC’ in SQL. Tip: Describe primary keys, foreign keysand 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.。