数据库原理及应用期末考试复习题库+2017(1)(3) 下载本文

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 -