数据库原理（第三版）张红娟部分课后习题参考

第一章 17、 班级号 班级所在专业 入校年份 班级 班级人数 1 班长的学号 属 于 成绩 N M N 选修 学生 学号 性别 年龄 姓名 课程号

课程 课程名 学分

18、 教师号 班级号 班级所在专业 入校年份 姓名 班级 班级人数 教师 1 M 班长的学号 属 于 属 于 成绩 N N M N 选修 学生 课程 学号 性别 年龄 姓名 课程号 课程名 M 加入 入会年份 N 学会名称 学会 会费 职称 专业 学分 成立时间 负责人姓名

20、供应商（供应商代号，供应商名称，联系电话） 项目（项目代号，项目名称，项目负责人）

零件（零件号，零件名，价格）

供应商-项目（项目代号，供应商代号） 项目-零件（项目代号，零件号，零件数） 供应商-零件（供应商代号，零件号，零件数） 21、名称 单位号 电话 地址 设备号 N 位置 价格 设备处 1 设备 N 工作 职员号 N N 1 属于 部门名 1 电话 零件号 装配 M 零件 1 地址 规格 M 价格 名称 职员姓名 职员 工作 部门 职务 年龄 性别 部门号 属于 N 生产处 单位号 地址 电话 名称 N 生产

22、（1） 职工号 性别 工种 厂名 地址 姓名 年龄 N 1 N 包含 1 工厂 价格 车间号 电话 工人 属于 车间 M 1 生产 N 产品 N 组装 车间主任姓名 生产 零件号 N M 零件 重量 M 产品号 1 M 厂长姓名 包含 存放 存放 N N N 仓库 仓库保管员姓名 仓库号 电话

第三章

10、设有一数据库 GradeManager（成绩管理），包括四个表：学生表（Student）、课程表（Course）、班级表（Class）以及成绩表（Grade），其结构如表3-4所示，数据如表3-5所示。试用SQL语句创建四个表。 表3-4 成绩管理数据库的表结构 表一 Student 属性名 Sno Sname Ssex Sage Clno 表二 Course

数据类型 Char（7） VarChar（20） Char（2） Smallint Char（5） 可否为空 否 否 否 可 否 含义 学号（唯一） 学生姓名 性别 年龄 学生所在班级 价格 属性名 Cno Cname Credit 表三 Class 属性名 Clno Speciality Inyear Number Monitor 表四 Grade 属性名 Sno Cno Gmark 数据类型 Char（1） VarChar（20） Smallint 数据类型 Char（5） VarChar（20） Char（4） Integer Char（7） 数据类型 Char（7） Char（7） Numeric（4,1） 可否为空 否 否 可 可否为空 否 否 否 可 可 可否为空 否 否 可 含义 课程号（唯一） 课程名称 学分 含义 班级号（唯一） 班级所在专业 入校年份 班级人数 班长学号 含义 学号 课程号 成绩 表3-5 成绩管理数据库中的数据 表一 Student Sno 2000101 2000102 2000103 2000104 2001101 2001102 2001103 2001104 表二 Course Cno 1 2 3 4 5 6 7 表三 Class Clno 00311 00312 01311 表四 Grade Sno Cno Gmark Speciality 计算机软件 计算机应用 计算机软件 Inyear 2000 2000 2001 Number 120 140 220 Monitor 2000101 2000103 2001103 Cname 数据库 离散数学 管理信息系统 操作系统 数据结构 数据处理 C语言 Credit 4 3 2 4 4 2 4 Sname 李勇 刘诗晨 王一鸣 张婷婷 李勇敏 贾向东 陈宝玉 张逸凡 Ssex 男 女 男 女 女 男 男 男 Sage 20 19 20 21 19 22 20 21 Clno 00311 00311 00312 00312 01311 01311 01311 01311

2000101 2000101 2000101 2000102 2000102 2000103 2000103 2000103 2000104 2000104 2001101 2001101 2001102 2001102 2000103 2000103 2000103 1 3 5 1 6 3 6 5 1 6 2 4 2 4 2 4 7 92 88 86 78 55 65 78 66 54 83 70 65 80 90 76 56 88

答：CREATE TABLE Student

(Sno Char(7) NOT NULL UNIQUE, Sname VarChar(20) NOT NULL, Ssex Char(2) NOT NULL, Sage Smallint,

Clno Char(5) NOT NULL); CREATE TABLE Course (Cno Char(1) NOT NULL UNIQUE, Cname VarChar(20) NOT NULL, Credit Smallint);

CREATE TABLE Class

(Clno Char(5) NOT NULL UNIQUE, SpecialityVarChar(20) NOT NULL, Inyear Char(4) NOT NULL, Number Integer, Monitor Char(7));

CREATE TABLE Grade (Sno Char(7) NOT NULL, Cno Char(1) NOT NULL, Gmark Numeric(4,1)); INSERT INTO Student

VALUE(‘2000101’,’李勇’,’男’,20,’00311’);

INSERT INTO Student

VALUE(‘2000102’,’刘诗晨’,’女’,19,’00311’);

INSERT INTO Student

VALUE(‘2000103’,’王一鸣’,’男’,20,’00312’);

INSERT INTO Student

VALUE(‘2000104’,’张婷婷’,’女’,21,’00312’);

INSERT INTO Student

VALUE(‘2001101’,’李勇敏’,’女’,19,’01311’);

INSERT INTO Student

VALUE(‘2001102’,’贾向东’,’男’,22,’01311’);

INSERT INTO Student

VALUE(‘2001103’,’陈宝玉’,’男’,20,’01311’);

INSERT INTO Student

VALUE(‘2001104’,’张逸凡’,’男’,21,’01311’);

INSERT INTO Course VALUE(‘1’,’数据库’,4);

INSERT INTO Course VALUE(‘2’,’离散数学’,3);

INSERT INTO Course

VALUE(‘3’,’管理信息系统’,2);

INSERT INTO Course VALUE(‘4’,’操作系统’,4);

INSERT INTO Course VALUE(‘5’,’数据结构’,4);

INSERT INTO Course VALUE(‘6’,’数据处理’,2);

INSERT INTO Course VALUE(‘7’,’c语言’,4);

INSERT INTO Class

VALUE(‘00311’,’计算机软件’,’2000’,120,’2000101’);

INSERT INTO Class

VALUE(‘00312’,’计算机应用’,’2000’,140,’2000103’);

INSERT INTO Class

VALUE(‘01311’,’计算机软件’,’2001’,220,’2001103’);

INSERT INTO Grade VALUE(‘2000101’,’1’,92);

INSERT INTO Grade VALUE(‘2000101’,’3’,88);

INSERT INTO Grade VALUE(‘2000101’,’5’,86);

INSERT INTO Grade VALUE(‘2000102’,’1’,78);

INSERT INTO Grade VALUE(‘2000102’,’6’,55);

INSERT INTO Grade VALUE(‘2000103’,’3’,65);

INSERT INTO Grade VALUE(‘2000103’,’6’,78);

INSERT INTO Grade VALUE(‘2000103’,’5’,66);

INSERT INTO Grade VALUE(‘2000104’,’1’,54);

INSERT INTO Grade VALUE(‘2000104’,’6’,83);

INSERT INTO Grade VALUE(‘2001101’,’2’,70);

INSERT INTO Grade VALUE(‘2001101’,’4’,83);

INSERT INTO Grade VALUE(‘2001102’,’2’,80);

INSERT INTO Grade VALUE(‘2001102’,’4’,90);

INSERT INTO Grade VALUE(‘2000103’,’1’,83);

INSERT INTO Grade VALUE(‘2000103’,’2’,76);

INSERT INTO Grade VALUE(‘2000103’,’4’,56);

INSERT INTO Grade VALUE(‘2000103’,’7’,88);

11、针对T10的四个表，用SQL语言完成以下各项操作： （1）给学生表增加一属性Nation（民族），数据类型为VarChar（20）； ALTER TABLE Student ALTER COLUMN Nation VarChar(20); （2）删除学生表中新增的属性Nation； ALTER TABLE Student DROP COLUMN Nation; （3）向成绩表中插入记录（“2001110”，“3”，80）； INSERT INTO Grade VALUE(‘2001110’,’3’,80);

（4）将学号为“200110”的学生的成绩修改为70分； UPDATE Grade SET Gmark = 70 WHERE Sno = ‘2001110’;

（5）删除学号为“2001110”的学生的成绩记录； DELETE FROM Grade WHERE Sno = ‘2001110’;

（6）在学生表的Clno属性上创建一个名为IX_Class的索引，以班级号的升序排序； CREATE INDEX IX_Class ON Student(Clno); （7）删除IX_Class索引 DROP INDEX Student.IX_Class;

12、针对T10的四个表，用SQL语言完成以下各项操作： （1）找出所有被学生选修了的课程号；

SELECT DISTINCT Cno 课程号 FROM Grade

（2）找出01312班女生的个人信息；

SELECT * FROM Student

WHERE Clno='01311' and Ssex='女'

（3）找出01311班和01312班的学生姓名 性别 出生年份；

SELECT sname 学生姓名,ssex 性别,2011-sage 出生年份 FROM Student

WHERE Clno='01311' or Clno='01312' （4）找出所有姓李的学生的个人信息；

SELECT * FROM Student

WHERE Sname like '李%'

（5）找出李勇所在班级的学生人数；

SELECT COUNT(*) FROM Student WHERE Clno in (SELECT Clno FROM Student

WHERE Sname='李勇')

（6）找出课程名为操作系统的平均成绩 最高分 最低分；

SELECT AVG(Gmark) 平均成绩,MAX(Gmark) 最高分,MIN(Gmark) 最低分 FROM Grade WHERE Cno in (SELECT Cno FROM Course

WHERE Cname='操作系统') （7）选修了课程的学生人数；

SELECT COUNT(DISTINCT sno) 学生人数 FROM Grade

（8）选修了操作系统的学生人数；

SELECT COUNT(DISTINCT sno) 学生人数 FROM Grade WHERE Cno in (SELECT Cno FROM Course

WHERE Cname='操作系统')

（9）找出2000级计算机软件班的成绩为空的学生姓名。

SELECT Sname 学生姓名 FROM (SELECT * FROM Student WHERE Clno in (SELECT Clno FROM Class

WHERE Speciality='计算机软件' AND Inyear='2000') ) ruanjian WHERE ruanjian.Sno not in

(SELECT DISTINCT Sno FROMGrade)

with ruanjian as

(SELECT Sno,Sname FROM Student join Class on Student.Clno=Class.Clno

WHERE Class.Speciality='计算机软件' and Class.Inyear='2000') SELECT Sname 学生姓名 FROM ruanjian WHERE not exists (SELECT * FROM Grade

WHERE ruanjian.Sno=Grade.Sno)

13、针对T10的四个表，用SELECT的嵌套查询完成以下各项查询： （1）找出和李勇在同一个班级的学生信息；

SELECT * FROM Student

WHERE Sname<>'李勇' and Clno in (SELECT Clno FROM Student

WHERE Sname='李勇')

（2）找出所有与学生李勇有相同选修课程的学生信息；

SELECT * FROMS tudent

WHERE Sname<>'李勇' and exists (SELECT * FROM Grade

WHERE Student.Sno=Grade.Sno and Grade.Cno in (SELECT Cno FROMG rade WHERE Sno in (SELECT Sno FROM Student

WHERE Sname='李勇')))

（3）找出年龄介于学生李勇和25岁之间的学生信息；

SELECT * FROMStudent

WHERE Sage < 25 and Sage >

(SELECT Sage FROMStudent WHERE Sname='李勇') （4）找出选修了课程是操作系统的学生学号和姓名；

SELECT Sno 学号,Sname 姓名 FROM Student WHERE exists (SELECT * FROM Grade

WHERE Cno in(SELECT Cno

FROM Course

WHERE Cname='操作系统')

and Student.Sno=Grade.Sno)

（5）找出没有选修1号课程的所有学生姓名；

SELECT Sname 姓名 FROM Student WHERE not exists (SELECT * FROM Grade

WHERE Student.Sno=Grade.Sno and Cno=1) （6）找出选修了全部课程的学生姓名。

SELECT Sname 姓名 FROM Student WHERE not exists (SELECT Cno FROM Course except

SELECT Cno FROMGrade

WHERE Student.Sno=Grade.Sno)

14、针对T10的四个表，用SQL语言完成以下各项查询：

（1）查询选修了3号课程的学生学号及成绩,并按成绩的降序排列；

SELECT Sno 学号,Gmark 成绩 FROM Grade WHERE Cno=3

ORDER BY Gmark desc

（2）查询全体学生信息,要求查询结果按班级号升序排列,同一班级学生按年龄降序排列；

SELECT * FROM Student

ORDER BY Clno asc,Sage desc

（3）求每个课程号及相应的选课人数；

SELECT Grade.Cno 课程号,COUNT(Grade.Cno) 选课人数 FROM Grade join Course on Grade.Cno=Course.Cno GROUP BY Grade.Cno

（4）查询选修了3门以上课程的学生学号

SELECT Sno 学号 FROMGrade GROUP BY Sno

HAVING COUNT(Sno)>3

15、针对T10的四个表，用SQL语言完成以下各项操作： （1）将01311班的全体学生的成绩置零

UPDATE Grade SET Gmark=0 WHERE Sno in (SELECT Sno

FROM Student

WHERE Clno='01311')

（2）删除2001级计算机软件的全体学生的选课记录

DELETE FROM Grade WHERE Sno in (SELECT Sno FROM Student WHERE Clno in (SELECT Clno FROM Class

WHERE Speciality='计算机软件' and Inyear='2001')) （3）学生李勇已退学,从数据库中删除有关他的记录；

DELETE FROM Grade WHERE Sno in (SELECT Sno FROM Student

WHERE Sname='李勇')

UPDATE Class

SET Number=Number-1 WHERE Clno in (SELECT Clno FROM Student

WHERE Sname='李勇')

UPDATE Class SET Monitor=case WHEN Monitor=(SELECT Sno

FROM Student

WHERE Sname='李勇')

THEN '' end

FROM Class

WHERE Clno in(SELECT Clno

FROM Student

WHERE Sname='李勇')

DELETE FROMStudent WHERE Sname='李勇'

（4）对每个班,求学生的平均年龄,并把结果存入数据库。

ALTER TABLE Class

ADD HCage Smallint NULL UPDATE Class SET Cage=case

WHEN Clno='00311' THEN (SELECT AVG(Sage)

FROM Student

WHERE Clno='00311')

WHEN Clno='00312' THEN (SELECT AVG(Sage)

FROM Student

WHERE Clno='00312')

WHEN Clno='01311' THEN (SELECT AVG(Sage)

FROM Student

WHERE Clno='01311')

end

FROM Class 16、视图操作：

（1）建立01311班选修了1号课程的学生视图Stu_01311_2；

create view Stu_01311_1

as SELECT Student.Sno,Sname,Gmark FROM Student,Grade WHERE Cno = '1' and Clno = '01311'

and Student.Sno = Grade.Sno with check option

（2）建立01311班选修了1号课程并且成绩不合格的学生视图Stu_01311_2；

create view Stu_01311_2 as SELECT *

FROM Stu_01311_1 WHERE Gmark < 60

（3）建立视图Stu_year,由学生学号、姓名、出生年份组成；

create view Stu_year(Sno,Sname,years

as SELECT Sno,Sname,year(getdate()) - Sage FROM Student

（4）查询1990以后出生的学生姓名；

SELECT Sname FROM Stu_year

WHERE years > 1990;

（5）查询01311班选修了1号课程并且成绩不合格的学生的学号、姓名、出生年份。

SELECT * FROM Stu_year WHERE Sno in (SELECT Sno

FROM Stu_01311_2);

第七章

15、设有关系模式R(A,B,C,D)，函数依赖集F={A→B,B→C}，试求此关系的码，并指出在函数依赖的范围内，它达到了第几范式。

此关系的码是A；此函数依赖集没有部分函数依赖，有传递函数依赖，所以是2NF。

16、在函数依赖范围内，试问下列关系模式最高属于第几范式，并解释原因。 （1）R(A,B,C,D),F={B→D,AB→C};

（2）R(A,B,C,D,E),F={AB→CE,E→AB,C→D};

（3）R(A,B,C,D),F={B→D,D→B,AB→C};

（4）R(A,B,C),F={A→B,B→A,A→C};

1NF；存在部分函数依赖。 1NF；存在部分函数依赖。

2NF；不存在部分函数依赖，存在传递函数依赖。 1NF；存在部分函数依赖。

（5）R(A,B,C),F={A→B,B→A,C→A};

2NF；不存在部分函数依赖，存在传递函数依赖。

（6）R(A,B,C,D),F={A→C,D→B};

1NF；存在部分函数依赖。

（7）R(A,B,C,D),F={A→C,CD→B};

1NF；存在部分函数依赖。

22、现有一个未规划的表：项目部件表，包含了项目、部件和部件向项目已提供的数量信息。 （1）写出项目部件表中的函数依赖F，该表达到了第几范式？

F={部件号→部件名，部件号→现有数量，项目代号→项目内容，项目代号→项目负责人，

（部件号、项目代号）→已提供数量}

该表达到了1NF；

（2）请采用规范化方法，将该表分解到3NF要求，并说明理由。 项目部件表

部件号 205 205 210 210 210 …

部件名 现有数量 CAM CAM COG COG COG … 30 30 155 155 155 … 项目代号 12 20 12 25 30 … 项目内容 AAA BBB AAA CCC DDD … 项目负责人 01 02 01 11 12 … 已提供数量 10 15 30 25 15 … B1={部件号，部件名，现有数量} B2={项目代号，项目内容，项目负责人} B3={部件号，项目代号，已提供数量}

23、考虑如表所示的关系模式定义和样本数据；

Project(PID,Ename,Salary)，其中PID是项目名称，Ename是参加项目的雇员名，Salary是雇员的薪水。 职工参与项目表 PID 100A 100A 100B 200A 200B 200C 200C 200D Ename 胡一民 张小华 张小华 胡一民 胡一民 李红卫 张小华 李红卫 Salary 2400 2100 2100 2400 2400 1500 2100 1500 假设所有的函数依赖和约束都已显示在数据中。 （1） 写出该关系的函数依赖集。

F={Ename→Salary}

（2） 该关系的码是什么？

Ename，PID

（3） 该关系属于第几范式？为什么？

1NF，因为存在部分函数依赖。

本文来源：https://www.bwwdw.com/article/4yqh.html