发布时间 : 星期五 文章数据库原理及应用期末考试复习题库+2017(1)(3)更新完毕开始阅读dece24b59f3143323968011ca300a6c30d22f153
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 -