四川大学期末考试试题（闭卷）
（2005-2006学年第2学期）
课程号：
课序号： 课程名称： 数据库系统原理（A卷）任课教师： 阮树骅、张天庆
学生人数： 400印题份数： 430 学号： 姓名：
适用专业年级：软件工程2004级
考试须知 四川大学学生参加由学校组织或由学校承办的各级各类考试，必须严格执行《四川大学考试工作管理办法》和《四川大学考场规则》。有考试违纪作弊行为的，一律按照《四川大学学生考试违纪作弊处罚条例》进行处理。 四川大学各级各类考试的监考人员，必须严格执行《四川大学考试工作管理办法》、《四川大学考场规则》和《四川大学监考人员职责》。有违反学校有关规定的，严格按照《四川大学教学事故认定及处理办法》进行处理。 题 号 得 分 阅卷教师 阅卷时间 总 成 绩 一 100 二 期末卷面 % 三 60 四 五 20 六 七 八 20 九 考勤成绩 % 十 单元测验 2次 % 平时练习 % 1. Multiple Choices. (2poits×10)
(1) The cardinality of a table is the number of _____ in the table.
A. keys B. foreign keys C. rows D. coloums (2) Consider the following functional dependency. {A, B} -> {C}
Regarding this dependency, which of the following statements is (are) true?
I. The values of C are uniquely determined by the values of A. II. The values of A are uniquely determined by the values of C.
A. None. B. I only. C. I and II. D. II only.
(3) The FD X -> Y is a full dependency in a relation R, if there is _____ attribute A that can be _____ X and the
dependency still holds. A. at least one, added to C. no, added to
B. at least one, removed from C. removed from
(4) An exclusive lock on a data item represents permission to perform which of the operations, read and write, on the
data item?
A. Both read and write. B. Write only. C. Read only. D. Neither read nor write.
(5) In a two-phase locking protocol, what happens during the shrinking phase?
A. The transaction cannot acquire locks, but can release its locks. B. The transaction can acquire locks, but cannot release its locks. C. The transaction can acquire locks, and can release its locks.
D. The transaction can acquire locks, and can release its locks as long as the number of released locks is greater
注：试题字迹务必清晰，书写工整。 本题4页，本页为第1页
教务处试题编号：
课程名称：数据库系统原理 任课教师：阮树骅、张天庆 学号： 姓名： than the number of acquired locks.
(6) The term physical data independence refers to the ability to change
A. the conceptual schema without changing the application programs
B. the physical layout of the data without changing the external schemas, the conceptual schemas, or the
application programs
C. the application programs without changing the conceptual schema D. the data without physically relocating the tables (7) What attributes does a subclass have?
A. Just the attributes from the superclass
B. All the attributes of its superclass, and possibly more C. A subset of the attributes of its superclass D. None of the attributes of its superclass (8) Hashing exhibits the best performance for
A. equality searches involving the key, provided the key is used for hashing B. range queries
C. sequential scan of the entire table
D. ordering query results based on the key used for hashing
(9) Which of the following can be used to measure hard disk drive performance?
I. II.
Seek time
Rotational latency
III. Block transfer time
A. I only B. II only
C. II and III D. I, II, and III
(10)In a two-phase locking protocol, what happens when a transaction requests a conflicting lock?
A. The transaction immediately acquires the lock from the current lock-holder. B. The transaction proceeds without acquiring the lock. C. The transaction is blocked to acquire the lock. D. The transaction is aborted immediately.
2. Write SQL statements.(5poits×6)
You will be working with the following schema: Employee (SSN, name, salary, DNo) Department (DNo, DeptName, MgrSSN) Project (PNo, location, ProjName) HourLog (SSN, PNo, hours)
The Employee relation provides a list of employees with their SSN, name, salary, and department number (DNo). The SSN is unique for each employee. Each employee belongs to only one department. The Department relation contains a list of the departments for the company. Its schema includes a unique department number called DNo. It also includes the name of the department (DeptName) and the social security number of the department's manager (MgrSSN). Each department has a only one manager. The Project relation includes a unique project number (PNo), location and the project name (ProjName). An
本题4页，本页为第2页 教务处试题编号：
课程名称：数据库系统原理 任课教师：阮树骅、张天庆 学号： 姓名： employee can be assigned to any number (including zero) projects. Each project has at least one person assigned to it. Finally, the HourLog relation lists for each project the number of hours of work for each employee who is assigned to that project. The key of this relation is SSN and PNo. Write SQL statements in SQL2 to perform the following commands. (1) (2) (3) (4) (5)
List all managers’ SSN, name and salary.
Find the name and the SSN of everyone who works more than 100 hours on one particular project. Find the name and the SSN of everyone who works on at least two projects. Find the name and the SSN of everyone who is not working on any projects.
List alphabetically the names and SSNs of employees whose total work hours on all project are more than the average hours.
(6) Assume the relation Employee and Department has been created using the following statements:
CREATE TABLE Employee ( SSN char (4) primary key, Name varchar (30), Salary float, Dno char (2) ); CREATE TABLE Department ( DNo char(2) primary key, Deptname varchar(30), MgrSSN char(4) )
Write ALTER statements to specify the referential integrity constraints on above two relations.
3. In this problem, we shall design a database involving cities, the countries they are in, and \领事馆) (15points×2) The relevant information:
?
Each city is in a unique country. Data about cities include the name of the city and its population. Names of cities are unique within countries, but two cities in different countries may have the same name (and possibly even the same population).
Information about a country includes its name and its head of state. You may assume no two countries have the same name.
Information about a consulate includes its name, which is unique (e.g., \consulate of the United States in Toronto\) and its street address (which is unique within a city, but might not be unique among all consulates in different cities). Cities and the country they are in are related by a relationship In.
Consulates and the city in which each is located are related by a relationship Location.
Countries and consulates are related by a representation Represents. Each country represented by a consulate has a consul, who is represented only by a name. A subtle point is that occasionally, a consulate will represent more that one country. For example, country A may not have diplomatic relations with country B, so A will ask country C to represent A's interests in country B at their own consulates. In that case, the consul for country A is actually a citizen of country C, and that person will likely not be the same as the consul for country C at the same consulate.
While you might assume that a country would have at most one consulate in a given city, which is not always the case. For example, the US retains two consulates in Jerusalem.
Draw an ER schema for the database. Do not forget your reasonable assumptions.
本题4页，本页为第3页 教务处试题编号：
? ? ? ? ?
?
课程名称：数据库系统原理 任课教师：阮树骅、张天庆 学号： 姓名： Convert the following E-R schema into a relational schema using the mapping algorithm specified in this course. Specify keys and referential integrity constraints, using directed arcs. Make sure you also identify alternate keys. Label each step of the mapping algorithm. （15poits）
4. SNo Name SecNo INo Name Student Dept Enrolls Offering Year Offered Teachess Semister Dept Instructor Phone Grade Prerequisite Requires CNo Title Course Credit Semister
MainCourse 5. The following table stores information about which members borrow books in library. Note that a book can be lent to many members at different time. (20points) Member_Borrow_Book: MemNo 3 3 5 MemName Avi Avi Susan Book_Id 8004 5001 5001 DueDate 2006-5-1 2006-4-20 2006-2-18 CallNo 80 50 50 Title C++ OS OS Perform the following tasks. (1) List the primary key. (2) List all the FDs.
(3) What normal form is the relation in? Explain.
(4) Apply normalization to it incrementally, carrying the normalization process through each of the higher normal
forms possible up to 3NF.
本题4页，本页为第4页 教务处试题编号：