四川大学期末考试试题(闭卷)
(2007-2008 学年第 2 学期)
课程号: 31113740 课序号: 课程名称 数据库系统原理(A 卷) 任课教师:
张天庆
适用专业年级:软件工程 2006 级
学生人数: 340 印题份数: 350 学号:
姓名:
考试须知
四川大学学生参加由学校组织或由学校承办的各级各类考试,必须严格执行《四川大学考试工作管理办法》和《四川大学考场规则》。
有考试违纪作弊行为的,一律按照《四川大学学生考试违纪作弊处罚条例》进行处理。
四川大学各级各类考试的监考人员,必须严格执行《四川大学考试工作管理办法》、《四川大学考场规则》和《四川大学监考人员职
责》。有违反学校有关规定的,严格按照《四川大学教学事故认定及处理办法》进行处理。
题 号
一
二
三
四
五
六
七
八
九
十
得 分
阅卷教师
阅卷时间
总 成 绩
100
期末卷面
50 %
半期测验
10 %
平时练习
10 %
项目练习
30 %
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
the same domain
C.
the same name
D.
注:试题字迹务必清晰,书写工整。
本题 4页,本页为第 1页
教务处试题编号:
课程名称:数据库系统原理 任课教师:
张天庆
学号:
姓名:
(6) Just to avoid “dirty read”, which SQLIsolation level should be set?
A. READ UNCOMMITTED
B. READ COMMITTED
C. REPEATABLE READ
D. SERIALIZABLE
(7) What attributes does a subclass have?
Just the attributes from the superclass
A.
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
redo
B.
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.
Seek time
II.
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 SQLstatements.(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) List all managers’ SSN, name and salary.
(2) Find the name and the SSN of everyone who works more than 100 hours on one particular project.
(3) Find the name and the SSN of everyone who works on at least two projects.
(4) Find the name and the SSN of everyone who is not working on any projects.
(5) 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:
CREATETABLE Employee
( SSN char (4) primary key,
Name varchar (30),
Salary float,
Dno char (2)
);
CREATETABLE Department
( DNo char(2) primary key,
Deptname varchar(30),
MgrSSN char(4)
)
WriteALTER 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.
4. 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页,本页为第 3页
教务处试题编号:
课程名称:数据库系统原理 任课教师:
张天庆
学号:
姓名:
ENo
Name
Employee
Maintain
SNo
Flight
PNo
Name
Fly
Pilot
Salary
Grade
Date
Hours
Phone
Works
Execute
Plane_type
Requires
Plane
Model
TID
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页
教务处试题编号: