CNOVARCHAR(20) REFERENCES COURSES(CNO), GRADEINT CHECK (GRADE BETWEEN 0 AND 100), PRIMARY KEY(SNO,CNO) );

（2）将CS系学号为S17的学生张立（生日98-10-16）的记录录入STUDENTS表； INSERT INTO STUDENTS

VALUES(’S17’,’张立’, ’1998-10-16’,NULL,’CS’);

（3）将刘晨选修C03号课程的记录从REPORTS表中删除； DELETE FROM REPORTS WHERE CNO=’C03’

AND SNO=(SELECT SNO FROM STUDENTS WHERE SNAME=’刘晨’); （4）将生日在1990年7月1日以前的学生的SDEPT字段置为空值；

UPDATE STUDENTSSET SDEPT=NULL WHERE SBIRTH<'1990-7-1'; （5）创建S_G视图，查询平均分在90分以上的学生学号； CREATE VIEW S_G AS

SELECT SNO FROM REPORTS

GROUP BY SNO HAVING AVG(GRADE)>90; 试用关系代数表达式完成如下操作：

（6）查询至少选修了S16号学生选修的全部课程的学生学号； ∏SNO，CNO(REPORTS)÷∏CNO(σSNO=‘S16’(REPORTS))

（7）在STUDENTS表的SNAME上按降序建立唯一索引a1； CREATE UNIQUE INDEX a1 ON STUDENTS(SNAMEDESC);

（8）为“U1”用户授予COURSES表上更新记录的权限，并允许其传播此权限； GRANT UPDATE ON COURSES TO U1 WITH GRANT OPTION ; （9）修改COURSES表，将PRE_CNO字段设置为外码。

ALTER TABLE COURSES ADD FOREIGN KEY(PRE_CNO) REFERENCES COURSES(CNO);

6．设有一个SPJ数据库，包括S，P，J，SPJ四个关系模式，

供应商表S(供应商代码SNO，供应商姓名SNAME，供应商状态STATUS，所在城市CITY)； 零件表P(零件代码PNO，零件名PNAME，颜色COLOR，重量WEIGHT)； 工程项目表J(工程项目代码JNO，工程项目名JNAME，所在城市CITY)；

供应情况表SPJ(供应商代码SNO，零件代码PNO，工程项目代码JNO，供应数量QTY)； 试用SQL语言完成如下操作： （1）按如下要求创建S表；

表名 列名 数据类型 宽度 约束

主码 SNO VARCHAR 3

唯一 SNAME VARCHAR 20 S 限在集合{10,20,30}中取值 STATUS INT 默认值为“北京” CITY VARCHAR 10

CREATE TABLE S ( SNOVARCHAR(3) PRIMARY KEY, SNAME VARCHAR(20) UNIQUE,

STATUS INT CHECK (STATUS IN(10,20,30)), CITY VARCHAR(10) DEFAULT ’北京’ ); （2）将P1号零件的重量增加5，颜色改为蓝色； UPDATE P

SET WEIGHT=WEIGHT+5，COLOR=’蓝’ WHERE PNO=’P1’;

（3）写出与下列表达式等价的SELECT查询语句； ΠJNO(σCITY='上海'(SSPJ)) SELECT JNO FROM S,SPJ

WHERE S.SNO=SPJ.SNO AND CITY=’上海’; （4）查询没有使用上海产的零件的工程号； SELECT JNO FROM J WHERE JNO NOT IN (SELECT JNOFROM JSP, S

WHERE JSP.SNO=S.SNOAND CITY=’天津’);

- 25 -

（5）在J表的JNO上按降序建立聚簇索引a1。

CREATE CLUSTERED INDEX a1 ON J (JNO DESC);

7. 设一个图书借阅管理数据库中包括三个关系模式： 图书（图书编号，书名，作者，出版社，单价） 读者（借书证号，姓名，性别，单位，地址）

借阅（借书证号，图书编号，借阅日期，归还日期，备注）

（1）试用关系代数查询价格在50到60元之间的图书编号和图书名称。

Π 图书编号, 书名（σ单价≥50∧单价≤60（图书））

（2）试用关系代数表达式查询“张三”借阅“C语言”的日期和归还日期。 Π 借阅日期, 归还日期（σ姓名=‘张三’∧书名=‘C语言’（图书借阅读者））

（3）试用SQL语言查询价格在50到60元之间的图书编号和图书名称。 SELECT 图书编号, 书名 FROM 图书

WHERE 单价 BETWEEN 50 AND 60

（4）试用SQL语言查询有备注信息的借阅记录。 SELECT * FROM 借阅

WHERE 备注 IS NOT NULL

（5）试用SQL语言查询“张三”借阅“C语言”的日期和归还日期。

SELECT 借阅日期, 归还日期 FROM 图书,借阅,读者

WHERE 图书.图书编号=借阅.图书编号 AND 读者.借书证号=借阅.借书证号 AND

姓名=‘张三’ AND 书名=‘C语言’

（6）试用SQL语句统计每个出版社的书目数量和平均单价。

SELECT COUNT(图书编号) 书目数量，AVG(单价) 平均单价 FROM 图书

GROUP BY 出版社

（7）试用SQL语言查询借阅过“家”或“童年”或“飘”的读者的借书证号。

SELECT 借书证号 FROM 借阅，图书

WHERE图书.图书编号=借阅.图书编号 AND

书名 IN (‘家’,‘童年’, ‘飘’)

（8）试用SQL语言查询没有借阅过“家”、“童年”、“飘”的读者的借书证号。

SELECT 借书证号 FROM 借阅，图书

WHERE图书.图书编号=借阅.图书编号 AND 书名 NOT IN (‘家’,‘童年’, ‘飘’)

（9）试用SQL语句检索借阅过名字中包含“数据库”三个字的图书，且姓“张”的读者的姓名和借书证号，并按借书证号降序排列。

SELECT 姓名, 读者.借书证号 FROM 图书,借阅,读者

WHERE 图书.图书编号=借阅.图书编号 AND 读者.借书证号=借阅.借书证号 AND

书名 LIKE ‘%数据库%’ AND 姓名 LIKE ‘张%’ ORDER BY 读者.借书证号 DESC

（10）试用SQL语句将“机械工业出版社”的书籍单价降低5%。

UPDATE 图书

SET 单价=单价*0.95

WHERE 出版社=‘机械工业出版社’

（11）试用SQL语句将“张三”借阅“C语言”的记录删除。

DELETE FROM借阅

WHERE 借书证号=（SELECT 借书证号 FROM 读者 WHERE 姓名=‘张三’）

- 26 -

AND 图书编号=

（SELECT 图书编号 FROM 图书 WHERE 书名=‘C语言’）

（12）建立读者“张三”借阅名为“李四”的作者的图书的视图ST，包含书名、单价、借阅日期和归还日期。

CREATE VIEW ST AS

SELECT 书名，单价，借阅日期, 归还日期 FROM 图书,借阅,读者

WHERE 图书.图书编号=借阅.图书编号 AND 读者.借书证号=借阅.借书证号 AND

姓名=‘张三’ AND作者=‘李四’

（13）将“S1”用户在“借阅”关系上查询记录的权限级联收回。

REVOKE SELECT ON借阅FROM S1 CASCADE ; （14）修改“借阅”表，将“备注”字段删除。

ALTER TABLE 借阅 DROP COLUMN 备注；

五、综合题

1．根据下列给出的关系模式和函数依赖集，指出该关系模式是第几范式？并说明理由。 1）R（X，Y，Z）码：XY F={XY→Z}

答：R∈BCNF 因为不存在非主属性对码的部分函数依赖和传递函数依赖，所以R∈3NF， 又因为所有的函数依赖的决定因素都是码，所以R∈BCNF。 2）R（X，Y，Z）码：XY和XZ F={Y→Z，XZ→Y}

答：R∈3NF因为不存在非主属性对码的部分函数依赖和传递函数依赖，所以R∈3NF，又因为所有的函数依赖的决定因素不都是码，所以R不是BCNF 。

3）R（X，Y，Z）码：X F={X→Y，X→Z}

答：R∈BCNF 因为不存在非主属性对码的部分函数依赖和传递函数依赖，所以R∈3NF，又因为所有的函数依赖的决定因素都是码，所以R∈BCNF。

4）R（X，Y，Z）码：WX F={X→Z，WX→Y}

答：R∈1NF 因为存在非主属性对码的部分函数依赖，所以R不属于2NF，所以R∈1NF。

2．设有商业销售记账数据库，一个顾客（顾客姓名，单位，电话号码）可以买多种商品，一种商品（商品名称，型号，单价）供应多个顾客。试画出对应的E-R图并将其转换为关系模式。 答：E－R图 M N 顾客 商品 销售 顾客 m n 单商品姓名 电单型 数量 价 名称 话位 号 根据E-R图转换的关系模式为：

顾客（顾客姓名，单位，电话号码） 商品（商品名称，型号，单价）

销售（顾客姓名，商品名称，型号，数量）

3. 设一个海军基地要建立一个舰队管理信息系统，它包括四个实体：

舰队：舰队名、基地地点；舰艇：舰艇号、舰艇名；

武器：武器号、武器名称、生产日期；官兵：官兵证号、姓名、出生日期、身份证号。

其中，一个舰队拥有多艘舰艇，一艘舰艇属于一个舰队；一艘舰艇安装多种武器，一种武器可安装于多艘舰

- 27 -

艇上，武器安装在舰艇上要记安装日期；一艘舰艇有多个官兵，一个官兵只属于一艘舰艇； (1)根据以上叙述，建立ER模型，要求标注联系类型。

(2)将ER模型转换成关系模型，要求标明每个关系模式的主码和外码。

舰队（舰队名，基地地点）

舰艇（舰艇号，舰艇名，舰队名） 武器（武器号，武器名称，生产日期）

官兵（官兵证号，姓名，出生日期，身份证号，舰艇号） 安装（舰艇号，武器号，时间）

4．已知某个研究所要建立一个项目管理信息系统，它包括三个实体：

科室有：科室编号，科室名；

职员有：工号，职员名，出生日期； 项目有：项目号，项目名称，开始日期。

其中：每个科室有多个职员，每个职员只在一个科室中，每个职员参加多种产品的研发，而每种产品的研发可由多个职员参加，每个职员参加某个项目都记录参加该项目的时间。 (1)根据以上叙述，建立ER模型，要求标注联系类型。

(2)将ER模型转换成关系模型，要求标明每个关系模式的主码和外码。

科室（科室编号，科室名）

职员（工号，职员名，出生日期，科室编号） 项目（项目号，项目名称，开始日期） 参与（工号，项目号，时间）

5．为体育部门建立数据库，其中包含如下信息：

运动队：队名、主教练，其中队名惟一标识运动队。 运动员：运动员编号、姓名、性别、年龄。 运动项目：项目编号、项目名、所属类别。

其中：每个运动队有多名运动员，每名运动员只属于一个运动队；每名运动员可以参加多个项目，每个项目可以有多个运动员参加。系统要记录每名运动员参加每个项目所得的名次、成绩以及比赛日期。 (1)根据以上叙述，建立ER模型，要求标注联系类型。

- 28 -