2002-2003数据库系统概论
期中试题，2002年11月6日
姓名__________ 学号___________
班级__________
一、填空题（10分）
1． 通常，数据库中的数据，至少在大型系统中，既是_________，又是________。
2． 数据模型的三要素是指_________, __________, __________。
3． 数据库系统一般由________, _________, __________, _________构成。
4． 文件系统与数据库系统的本质区别在于___________________。
5． 目前各商业系统普遍采用的SQL标准是___________。
6． SQL语言主要由六个子句构成，其执行上的顺序为________, _______, ________, _________, _________,
_________。
7． QBE是一种基于_________________的语言。
8． 当数据库的_________改变了，由数据库管理员对_________映像作相应改变，可以使_________保持不变，从而保证了数据的物理独立性。
9． 最常见的数据模型_________, __________, __________, ___________。
10．
TP
Monitor的主要作用是__________________________________。
二、（20分）简答题
1．说明实体完整性和参照完整性的重要性。说明两者之间的关系和与主码的关系。说明系统中增删改操作如何受两者的控制。
2．举例说明视图对数据独立性的支持。
3．基于以下表，找出其所有可能的候选码：.
A
|
B
|
C
|
D
|
E
|
|
a1 |
b1 |
c1 |
d1 |
e4 |
|
a1 |
b1 |
c2 |
d2 |
e3 |
|
a1 |
b2 |
c3 |
d1 |
e1 |
|
a1 |
b2 |
c4 |
d2 |
e2 |
4．Given two tables R and S below, display the
results of R natural join S, R outer
join S and R right outer join S.
R S
A |
B |
C |
|
1 |
2 |
3 |
|
2 |
2 |
2 |
|
3 |
2 |
1 |
D |
B |
C |
|
1 |
2 |
3 |
|
2 |
1 |
2 |
|
3 |
2 |
2 |
三、(20 points) Write relational algebra
expressions to answer the following queries against the given tables:
Employees (SSN, Name, Dno, Age,
Salary)
Departments (Dno, Name,
Location, Manager_SSN)
Projects (Proj_no, Name, Proj_Location, Manager_name)
Works_on (SSN, Proj_no, hours)
(a)
Find the names of all employees in department 5 who
work more than 10 hours per week on the ProductX project.
(b)
Find the names of all employees who are directly
supervised by Franklin Wong.
(c)
Find the names of all employees who earn the second
highest salary.
(d)
Find the names of all employees who work on every
project.
(e)
Find the names and addresses of all employees who
work on at least one project located in
四、(20分) Write SQL
statements to answer the following queries against the given tables：
(a)
Find the SSN, name and annual salary of each
employee；
(b)
Find the names of those projects managed by Smith
that are participated in by all employees under 40
（ Employees (SSN, Name, Age, Salary)
Projects
(Proj_no, Name, Manager_name)
Works
(SSN, Proj_no, Start-Date) ）
(c) Find the names
and GPAs of all students who take database systems;
(d)
Find the names of those students who are 18 or
younger and whose GPA is higher than the GPA of some students who are 25 or
older;
(e) Find the SSN,
name and the number of credit hours each student still needs to graduate. Assume that each
student needs 120 credit hours to graduate.
（Students( SSN, Name, Age,
GPA)
Courses
(Course_no, Title, Dept_Name, Credit_Hour)
Enrollment (SSN,
Course_no, Grade, Semester) ）
五、(10分) 对查询“找出至少选修了一门CS系所开设课程的所有学生”。
现有三种可完成上述查询的SQL语句，请分析三者属于何种类型的SQL查询，三者间的转换关系和在执行上的差异如何。
(a) select Name
from Students s,
Enrollment e
where
s.SSN = e.SSN and e.Course_no in
(select Course_no from
Courses
where Dept_Name = 'CS')
(b) select Name
from Students s, Enrollment e, Courses c
where s.SSN = e.SSN and e.Course_no =
c.Course_no and c.Dept_Name = 'CS’
(c) select Name from
Students where SSN in
(select SSN from Enrollment
where Course_no in
(select Course_no from Courses
where Dept_Name = 'CS'))
六、（10分）Complete
the following requests using SQL：
(a)
Create a view for top students without the age
attribute against the following table:
Students( SSN, Name, Age,
GPA)
(b)
Find the names and GPAs of those top students whose
name starts with `K'. How does the DBMS process the view query?
(c)
For table Students, limit the access of John to
select and insert and only attributes SSN, Name and Age and tuples with GPA
value higher than 3.8 can be accessed.
七、（10 points）Under what condition do we have R1 ¥ R2 = R1 Ç
R2? Why?