四川大学期末考试试题（闭卷）
（2007-2008学年第2学期）
课程号： 31113740 课序号： 课程名称 数据库系统原理（A卷） 适用专业年级：软件工程2006级
学生人数： 340印题份数： 350 学号： 姓名：
任课教师：
考试须知
四川大学学生参加由学校组织或由学校承办的各级各类考试，必须严格执行《四川大学考试工作管理办法》和《四川大学考场规则》。有考试违纪作弊行为的，一律按照《四川大学学生考试违纪作弊处罚条例》进行处理。
四川大学各级各类考试的监考人员，必须严格执行《四川大学考试工作管理办法》、《四川大学考场规则》和《四川大学监考人员职责》。有违反学校有关规定的，严格按照《四川大学教学事故认定及处理办法》进行处理。
题 号 得 分 阅卷教师 阅卷时间 总 成 绩
一 100
二 期末卷面
50 %
三
四
五
六
七
八
九 项目练习 30 %
十
半期测验 10 % 平时练习 10 %
1. Multiple Choices. (2points×10)
(1) If relation R has n tuples and relation S has m tuples, the maximum number of tupes that R─S can contain is _____.
A. n B. m C. n+m D. |n-m| (2) Which of the following statements are true. I. 4NF implies BCNF II. BCNF implies 3NF. III. 3NF implies BCNF
A. None B. I only C. I and II
D. I and III
(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) For two tables to be union compatible, corresponding columns from each table should have which of the following?
A. different domains B. different names C. the same domain D. the same name
注：试题字迹务必清晰，书写工整。 本题4页，本页为第1页
教务处试题编号：
课程名称：数据库系统原理 任课教师： 张天庆 学号： 姓名： (6) Just to avoid “dirty read”, which SQL Isolation level should be set?
A. READ UNCOMMITTED B. READ COMMITTED C. REPEATABLE READ D. SERIALIZABLE
(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) After a system failure, what actions are used as part of the crash recovery or restart procedure?
A. undo B. redo
C. both undo and redo D. backup copy and redo (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.(5points×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
本题4页，本页为第2页 教务处试题编号：
课程名称：数据库系统原理 任课教师： 张天庆 学号： 姓名：
one manager. The Project relation includes a unique project number (PNo), location and the project name (ProjName). An 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. Design an E/R diagram for the following situation. (15points) It is about books, authors, book publishers, book sellers, and book stores.
? ? ? ? ? ?
Books have an ISBN number (the key), a name, and price. A book can be written by many authors and on author can write many books.
Authors have a social security number (the key) and a name.
Publishers are the companies that manage the printing and production of books. Publishers have a name (key), a phone number, and address.
A given book has a unique publisher.
Book sellers maintain book stores to sell books; a given book seller can have many stores (and must have at least one store, to qualify as a book seller).
A book store has a name (key), address and a phone number.
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. （15points）
4.
本题4页，本页为第3页 教务处试题编号：
课程名称：数据库系统原理 任课教师： 张天庆 学号： 姓名：
ENo Name SNo PNo Name Employee Salary Maintain Flight Date Execute Hours Fly Pilot Phone Grade Works Plane_type Model TID Requires Plane PNo Seats
5. The following table stores information about librarians and their working sections. (20points) Librarian_Section: SSN 1001 1002 2001 Name John Hill Lussi Susan City NY SF NY SectionNo 1 1 2 SectionName Art Art Story 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页 教务处试题编号：