【数据挖掘】国科大苏桂平老师数据库新技术课程作业 —— 第三次作业

part 1

设计一个学籍管理小系统。系统包含以下信息：

学号、学生姓名、性别、出生日、学生所在系名、学生所在系号、课程名、课程号、课程类型（必修、选修、任选）、学分、任课教师姓名、教师编号、教师职称、教师所属系名、系号、学生所选课程号、学生所选上课教师姓名、选课成绩。

两种不同表达方式的 E-R 图分别如图 $1$ 和图 $2$ 所示。

图 1    E-R 图（未注明了键、外键、类型等信息）

图 2    E-R 图（注明了键、外键、类型等信息）

part 2

完成在表上的操作 (用 SQL 的 SELECT 语句)：

根据描述内容创建 student 表、course 表、department 表、selectcourse 表、teacher 表、teach 表，分别如图 $1$、$2$、$3$、$4$、$5$、$6$ 所示。

学号	学生姓名	性别	出生日	所在系号
0	张三	男	19870610	0
1	李四	男	19870610	1
2	王五	女	19870610	2
3	赵六	男	19870610	3
4	李德	男	19870610	4
5	王和	男	19870610	5
6	田亮	男	19870610	6
7	周润发	男	19870610	7
8	刘德华	男	19870610	8
9	谢霆锋	男	19870610	9
10	邓超	男	19870610	0
11	刘翔	男	19860214	1
12	姚明	男	19860214	2
13	高峰	男	19860214	3
14	史冬鹏	男	19860214	4
15	郭晶晶	女	19860214	5
16	汤唯	女	19860214	6
17	范冰冰	女	19860214	7
18	李冰冰	女	19860214	8
19	赵本山	男	19860214	9
20	郭德纲	男	19860214	0
21	于谦	男	19860214	1
22	岳云鹏	男	19870814	2
23	姜昆	男	19870905	3
24	李金斗	男	19870905	4
25	刘青云	男	19870905	5
26	张靓颖	女	19870905	6
27	韩寒	男	19870905	7
28	李宇春	女	19870905	8
29	张静初	女	19870905	9
30	莫文蔚	女	19870905	9

表 1    student 关系表

课程号	课程名	学分	课程类型
0	模式识别	2	讲授
1	C++编程	2	讨论
2	计算机视觉	1	讨论
3	密码学	3	讲授
4	数理分析	2	讨论
5	英语	3	讲授
6	声乐	1	讨论
7	控制理论	3	讲授
8	信号处理	2	讨论
9	物理学	3	讲授
10	化学分析	1	讨论
11	遗传学	3	讲授
12	数据结构	3	讲授
13	通信工程	2	讲授
14	随机数学	3	讲授
15	日语	2	讲授
16	美术	1	讨论
17	解剖学	1	讨论
18	有机化学	1	讨论
19	材料学	2	讲授
20	流体力学	3	讲授
21	复变函数	2	讲授
22	市场营销	3	讲授
23	人力资源管理	3	讲授
24	组织理论与设计	2	讨论
25	证券投资学	2	讲授
26	宏观经济学	1	讨论
27	微观经济学	2	讲授
28	管理学	2	讲授
29	公司战略	1	讨论
30	德语	3	讲授
31	西班牙语	2	讲授

表 2    course 关系表

系号	系名
0	计算机系
1	自动化系
2	艺术系
3	通信系
4	外语系
5	物理系
6	化学系
7	数学系
8	生物系
9	管理系

表 3    department 关系表

课程号	学号	教师编号	选课成绩
0	0	0	99
1	0	1	99
2	1	2	99
3	1	3	99
4	2	4	99
5	2	5	99
6	3	6	99
7	3	7	99
8	4	8	99
9	4	9	99
10	5	0	99
11	5	1	99
12	6	2	99
13	6	3	88
14	7	4	88
15	7	5	88
16	8	6	88
17	8	7	88
18	9	8	88
19	9	9	88
20	10	0	88
21	10	1	88
22	11	2	88
23	11	3	88
24	12	4	88
25	12	5	88
26	13	6	88
27	13	7	77
28	14	8	77
29	14	9	77
30	15	0	77
31	15	1	77
0	16	0	77
1	16	1	77
2	17	2	77
3	17	3	77
4	18	4	77
5	18	5	77
6	19	6	77
7	19	7	77
8	20	8	77
9	20	9	66
10	21	0	66
11	21	1	66
12	22	2	66
13	22	3	66
14	23	4	66
15	23	5	66
16	24	6	66
17	24	7	66
18	25	8	66
19	25	9	66
20	26	0	66
21	26	1	66
22	27	2	66
23	28	3	66
24	28	4	66
25	29	5	66
26	29	6	66
27	30	7	66
28	30	8	55
29	27	9	55
30	26	0	55
31	30	1	55

表 4    selectcourse 关系表

教师编号	教师姓名	职称	所在院系号
0	牛根生	教授	0
1	王石	教授	1
2	马化腾	副教授	2
3	周鸿祎	副教授	3
4	马云	教授	4
5	柳传志	教授	5
6	史玉柱	副教授	6
7	王建宙	教授	7
8	李彦宏	副教授	8
9	周小川	教授	9
10	王健林	教授	0
11	李开复	教授	1

表 5    teacher 关系表

教师编号	课程号
0	0
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
0	10
1	11
2	12
3	13
4	14
5	15
6	16
7	17
8	18
9	19
0	20
1	21
2	22
3	23
4	24
5	25
6	26
7	27
8	28
9	29
0	30
1	31

表 6    teach 关系表

1. 查询所有学生所选的所有课程名、成绩

查询语句如下：

SELECT C.学生姓名, B.课程名, A.选课成绩 
FROM selectcourse AS A, course AS B, student AS C 
WHERE A.课程号=B.课程号 AND A.学号=C.学号

查询结果如表 $7$ 所示：

学生姓名	课程名	选课成绩
张三	模式识别	99
汤唯	模式识别	77
张三	C++编程	99
汤唯	C++编程	77
李四	计算机视觉	99
范冰冰	计算机视觉	77
李四	密码学	99
范冰冰	密码学	77
王五	数理分析	99
李冰冰	数理分析	77
王五	英语	99
李冰冰	英语	77
赵六	声乐	99
赵本山	声乐	77
赵六	控制理论	99
赵本山	控制理论	77
李德	信号处理	99
郭德纲	信号处理	77
李德	物理学	99
郭德纲	物理学	66
王和	化学分析	99
于谦	化学分析	66
王和	遗传学	99
于谦	遗传学	66
田亮	数据结构	99
岳云鹏	数据结构	66
田亮	通信工程	88
岳云鹏	通信工程	66
周润发	随机数学	88
姜昆	随机数学	66
周润发	日语	88
姜昆	日语	66
刘德华	美术	88
李金斗	美术	66
刘德华	解剖学	88
李金斗	解剖学	66
谢霆锋	有机化学	88
刘青云	有机化学	66
谢霆锋	材料学	88
刘青云	材料学	66
邓超	流体力学	88
张靓颖	流体力学	66
邓超	复变函数	88
张靓颖	复变函数	66
刘翔	市场营销	88
韩寒	市场营销	66
刘翔	人力资源管理	88
李宇春	人力资源管理	66
姚明	组织理论与设计	88
李宇春	组织理论与设计	66
姚明	证券投资学	88
张静初	证券投资学	66
高峰	宏观经济学	88
张静初	宏观经济学	66
高峰	微观经济学	77
莫文蔚	微观经济学	66
史东鹏	管理学	77
莫文蔚	管理学	55
史东鹏	公司战略	77
韩寒	公司战略	55
郭晶晶	德语	77
张靓颖	德语	55
郭晶晶	西班牙语	77
莫文蔚	西班牙语	55

表 7    所有学生所选的所有课程名、成绩查询结果

2. 查询 somestucent 表中学生所选的所有课程名、成绩

somestudent 表给出学生名：

学生姓名

韩寒

李宇春

莫文蔚

表 8    somestudent 关系表

查询语句如下：

SELECT D.学生姓名, B.课程名, A.选课成绩 
FROM selectcourse AS A, course AS B, student AS C, somestudent AS D 
WHERE A.课程号=B.课程号 AND C.学生姓名=D.学生姓名 AND A.学号=C.学号

查询结果如表 $9$ 所示：

学生姓名	课程名	选课成绩
韩寒	市场营销	66
李宇春	人力资源管理	66
李宇春	组织理论与设计	66
莫文蔚	微观经济学	66
莫文蔚	管理学	55
韩寒	公司战略	55
莫文蔚	西班牙语	55

表 9    somestucent 表中学生所选的所有课程名、成绩查询结果

3. 查询 somestudent 表中学生的累计学分（未及格的不算）

查询语句如下：

SELECT D.学生姓名, SUM(B.学分) AS '累计学分' 
FROM selectcourse AS A, course AS B, student AS C, somestudent AS D 
WHERE A.课程号=B.课程号 AND C.学生姓名=D.学生姓名 AND A.学号=C.学号 AND A.选课成绩 >= 60 
GROUP BY C.学号

查询结果如表 $10$ 所示：

学生姓名	累积学分
韩寒	3
李宇春	5
莫文蔚	2

表 10    somestudent 表中学生的累计学分查询结果

4. 查询 somecourse 表中课程的平均成绩

somecourse 表给出课程名：

课程名

模式识别

数据结构

英语

表 11    somescourse 关系表

查询语句如下：

SELECT C.课程名, AVG(A.选课成绩) AS '课程平均成绩' 
FROM selectcourse AS A, course AS B, somecourse AS C
WHERE A.课程号=B.课程号 AND B.课程名=C.课程名
GROUP BY C.课程名

查询结果如表 $12$ 所示：

课程名	课程平均成绩
模式识别	88.0
数据结构	82.5
英语	88.0

表 12    somecourse 表中课程的平均成绩查询结果

5. 各门课按平均成绩排序（降序，若改为升序，只需要将 DESC 换为 ASC）

查询语句如下：

SELECT B.课程名, AVG(A.选课成绩) AS '平均成绩' 
FROM selectcourse AS A, course AS B
WHERE A.课程号=B.课程号
GROUP BY A.课程号
ORDER BY 平均成绩 DESC

查询结果如表 $13$ 所示：

课程名	平均成绩
信号处理	88.0
模式识别	88.0
C++编程	88.0
计算机视觉	88.0
密码学	88.0
数理分析	88.0
英语	88.0
声乐	88.0
控制理论	88.0
物理学	82.5
化学分析	82.5
遗传学	82.5
数据结构	82.5
宏观经济学	77.0
解剖学	77.0
有机化学	77.0
材料学	77.0
流体力学	77.0
复变函数	77.0
市场营销	77.0
通信工程	77.0
人力资源管理	77.0
随机数学	77.0
组织理论与设计	77.0
日语	77.0
证券投资学	77.0
美术	77.0
微观经济学	71.5
管理学	66.0
公司战略	66.0
德语	66.0
西班牙语	66.0

表 13    各门课按平均成绩排序查询结果

6. 显示所有的课程名和讲课教师名

查询语句如下：

SELECT B.课程名, C.教师姓名
FROM teach AS A, course AS B, teacher AS C
WHERE A.课程号=B.课程号 AND A.教师编号=C.教师编号

查询结果如表 $14$ 所示：

课程名	教师姓名
模式识别	牛根生
C++编程	王石
计算机视觉	马化腾
密码学	周鸿祎
数理分析	马云
英语	柳传志
声乐	史玉柱
控制理论	王建宙
信号处理	李彦宏
物理学	周小川
化学分析	牛根生
遗传学	王石
数据结构	马化腾
通信工程	周鸿祎
随机数学	马云
日语	柳传志
美术	史玉柱
解剖学	王建宙
有机化学	李彦宏
材料学	周小川
流体力学	牛根生
复变函数	王石
市场营销	马化腾
人力资源管理	周鸿祎
组织理论与设计	马云
证券投资学	柳传志
宏观经济学	史玉柱
微观经济学	王建宙
管理学	李彦宏
公司战略	周小川
德语	牛根生
西班牙语	王石

表 14    所有的课程名和讲课教师名查询结果