国科大数据库新技术第三次作业

把Mysql实现和华为openGauss实现都尝试了一遍，如下所示：

题目要求

 设计一个学籍管理小系统。系统包含以下信息：学号、学生姓名、性别、出生日、学生所在系名、学生所在系号、课程名、课程号、课程类型（必修、选修、任选）、学分、任课教师姓名、教师编号、教师职称、教师所属系名、系号、学生所选课程号、学生所选上课教师姓名、选课成绩。作业所需资料和数据已经放在课程网站上。

要求1：

用E-R图表示概念模型设计出合理的关系模式：给出表名、列名、数据类型指出每个关系的键和外键（如果有的话）

要求2：

完成在表上的操作 (用SQL的SELECT语句)： 1.给出学生名，查询学生所选的所有课程名、成绩，累计学分。 2.给出课程名，查询学生的平均成绩 3.各门课按平均成绩排序 4.显示所有的课程名和讲课教师名

要求3

内容中的ER图在软件上实现，关系模式用具体数据库软件实现，并拷屏。要求2内容在具体数据库软件上实现，并将实验结果拷贝到作业当中。实验数据在课程网站的excel表格中。

1.Mysql实现

要求1

用E-R图表示概念模型

设计出合理的关系模式

• student：学号，学生姓名, 性别, 出生日, 所在系号
• teacher：教师编号, 教师姓名, 职称, 所在系名
• course：课程号,课程名, 学分, 课程类型
• department：系号, 系名
• teach：教师编号, 课程号
• selectcourse：课程号, 学号, 教师编号, 选课成绩

主键与外键

• student的主键为学号；外键为学号、所在系号
• course的主键为课程号；外键为课程号
• teacher的主键为教师编号；外键为教师编号、所在院系号
• department的主键为系号
• selectcourse的主键为课程号, 学号；外键为教师编、学号、课程号
• teach的主键为课程号、教师编号；外键为课程号、教师编号

数据库的具体实现(包括数据类型)

• course
  
• department
  
• selectcourse
  
• student
  
• teach
  
• teacher
  

要求2：

1.给出学生名，查询学生所选的所有课程名、成绩，累计学分

代码:

SELECT * 
FROM 
(
SELECT course.`课程名` ,selectcourse.`选课成绩`,student.`学生姓名` ,course.`学分`
	FROM course,student, selectcourse
	WHERE
		student.`学生姓名` = '张三' AND
		course.`课程号` = selectcourse.`课程号` AND
		selectcourse.`学号` = student.`学号`
) AS tmp1,
(
	SELECT SUM(course.`学分`) AS '该生累计学分'  
		FROM course,student, selectcourse
		WHERE
			student.`学生姓名` = '张三' AND
			course.`课程号` = selectcourse.`课程号` AND
			selectcourse.`学号` = student.`学号`
) AS tmp2

运行结果:

2.给出课程名，查询学生的平均成绩

代码:

SELECT course.`课程号`,course.`课程名`,AVG(selectcourse.`选课成绩`) AS 平均成绩
FROM course,selectcourse
WHERE
	course.`课程名` = '模式识别'  AND
	course.`课程号` = selectcourse.`课程号`;

运行结果:

3.各门课按平均成绩排序

代码：

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

运行结果：

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

代码:

SELECT course.`课程名` ,teacher.`教师姓名`
FROM course,teach,teacher
WHERE
	course.`课程号` = teach.`课程号` AND
	teach.`教师编号` = teacher.`教师编号`
ORDER BY teacher.`教师姓名`

运行结果:

2.openGauss实现

要求1

用E-R图表示概念模型

设计出合理的关系模式

• student：学号，学生姓名, 性别, 出生日, 所在系号
• teacher：教师编号, 教师姓名, 职称, 所在系名
• course：课程号,课程名, 学分, 课程类型
• department：系号, 系名
• teach：教师编号, 课程号
• selectcourse：课程号, 学号, 教师编号, 选课成绩

主键与外键

• student的主键为学号；外键为学号、所在系号
• course的主键为课程号；外键为课程号
• teacher的主键为教师编号；外键为教师编号、所在院系号
• department的主键为系号
• selectcourse的主键为课程号, 学号；外键为教师编、学号、课程号
• teach的主键为课程号、教师编号；外键为课程号、教师编号

数据库的具体实现(包括数据类型)

• course
  courseid即课程号

  

• department
  departmentid即系号

  

• student
  studentid即学号
  

• selectcourse
  

• teacher

  teacherId即教师编号

• teach

  

要求2

1.给出学生名，查询学生所选的所有courseId、成绩，累计学分

TotalCredits即累计学分

SELECT * 
FROM 
(
SELECT course.courseName ,selectcourse.grade,student.studentName ,course.credit
	FROM course,student, selectcourse
	WHERE
		student.studentName = '张三' AND
		course.courseId = selectcourse.courseId AND
		selectcourse.studentId = student.studentId
) AS tmp1,
(
	SELECT SUM(course.credit) AS TotalCredits  
		FROM course,student, selectcourse
		WHERE
			student.studentName = '张三' AND
			course.courseId = selectcourse.courseId AND
			selectcourse.studentId = student.studentId
) AS tmp2;

2.给出课程名，查询学生的avgScore

代码：

SELECT course.courseName,AVG(grade) AS avgScore
FROM course,selectcourse
WHERE
	course.courseName = '模式识别' AND
	course.courseId = selectcourse.courseId
GROUP BY course.courseId;

查询结果

3.各门课按平均成绩排序

代码

SELECT course.courseName,course.courseId,AVG(selectcourse.grade) AS avgScore
FROM selectcourse,course
WHERE course.courseId = selectcourse.courseId
GROUP BY course.courseId 
ORDER BY avgScore;

查询结果

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

代码

SELECT course.courseName ,teacher.teacherName
FROM course,teach,teacher
WHERE
	course.courseId = teach.courseId AND
	teach.teacherId = teacher.teacherId
ORDER BY teacher.teacherName

查询结果