数据库综合实验--设计某高校学生选课管理系统

来到golang学习网的大家，相信都是编程学习爱好者，希望在这里学习数据库相关编程知识。下面本篇文章就来带大家聊聊《数据库综合实验--设计某高校学生选课管理系统》，介绍一下MySQL、数据库、实验记录，希望对大家的知识积累有所帮助，助力实战开发！

数据库综合实验重做

因为期末的时候做数据库综合实验太匆忙，很多地方都是能用就好，做完之后突然想到可以改进的方法，所以现在寒假来重做一下

题目如下：

设计某高校学生选课管理系统

• 实现学生信息、课程学生管理；
• 实现教师及任课信息管理；
• 实现选课登记，要保证学生不能选修重复的课程（实用触发器实现）；
• 创建存储过程统计学生指定学期选修课程的总学分数；
• 创建视图查询2006－2007学年，没有被选修的课程；
• 创建存储过程统计各教师任课的学生人数；
• 创建表间关系

前言

这部分说的是我之前的实现，和改进思路，节省时间可跳过。

因为要求对于教师和课程等等的对应关系没有说明，所以可以自由发挥，我能找到 教师和课程之间是一对一关系的 实现，但是因为我想要实现教师课程多对多的数据库，所以就自己发挥了。

当时做实验的时候，对数据的描述如下：

一名学生可以选修多门课程，一门课程可被多个学生选修，每门课程对应一个老师，每门课程有一个成绩，且选修课程名不能重复；一名教师可以任教多门课程，一门课程可由多个老师教授，每个老师所任教的每门课程有一个课程评价；一个老师可教多名学生。

E-R图如下：

用Excel做的表格如下：

这个实现的问题就在于每个学生选的课程，是需要教师号和课程号一起才能共同确定的，这种情况实验要求就会很麻烦（而且E-R图上是三角多对多感觉很难受）改进思路就是将多一个临时班级实体，一个临时班级对应一个教师和一个课程，这样就可以将学生对老师和学生对课程这两个多对多关系化简成临时班级对老师，临时班级对课程这两个一对多的关系。

接下来，正文开始。

数据库描述和E-R图

有要求，可转化成如下数据间关系：

一名学生可以选修多门课程，一门课程可被多个学生选修，学生选修课程的实质是选临时班级。每个临时班级对应一门课程和一个老师，每门课程有一个成绩，且选修课程名不能重复；一名教师可以任教多门课程，一门课程可由多个老师教授。
E-R图如下：

这里临时班级多了一个人数的属性主要是为了接下来统计人数方便。

这里可以先按照E-R图在excel上把表建出来，输一些用于插入数据时做参考和验证。

数据库建立

需要建立的东西如下（加粗为主码）：

• 实体

  学生（学号，姓名，性别，生日，专业）

  教师（职工号，姓名，性别）

  课程（课程号，课程名，学分，开课学期）

  临时班级（班级号，课程号，教师号，人数）

• 关系

  选课（学号，班级号，成绩）

• 视图

  未选修06-07（班级号，课程名，教师名）

• Trigger

  学生不能选择课程号相同的课程。

• 存储过程

  查看指定学期的学生学分

  各教师任课的学生人数

下面是实现过程：

• 建立选课模式

  CREATE SCHEMA select_courses;

• 建立实体表

  -- student table
  CREATE TABLE students(
      stu_id INT PRIMARY KEY,
      name VARCHAR(40),
      birth_day DATE,
      sex VARCHAR(1),
      major VARCHAR(40)
  );
  
  -- teacher table
  CREATE TABLE teathers(
      tea_id INT PRIMARY KEY,
      name VARCHAR(40),
      sex VARCHAR(1)
  );
  
  -- courses table
  CREATE TABLE courses(
      course_id INT PRIMARY KEY,
      course_name VARCHAR(40),
      credit INT,
      open_time VARCHAR(20)
  );
  
  -- temp class table
  CREATE TABLE classes(
      class_id INT PRIMARY KEY,
      course_id INT,
      tea_id INT,
      number INT DEFAULT 0,
      FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE,
      FOREIGN KEY (tea_id) REFERENCES teathers(tea_id) ON DELETE CASCADE
  );

• 建立多对多关系表

  CREATE TABLE select_courses(
      stu_id INT,
      class_id INT,
      grade INT,
      PRIMARY KEY (stu_id, class_id),
      FOREIGN KEY (stu_id) REFERENCES students(stu_id) ON DELETE CASCADE,
      FOREIGN KEY (class_id) REFERENCES classes(class_id) ON DELETE CASCADE
  );

• 插入除

  -- insert data into students
  INSERT INTO students VALUES(101, 'Guanyu', '1996-10-01', 'M', 'physical'); 
  INSERT INTO students VALUES(102, 'Zhangfei', '1997-01-03', 'M', 'physical');
  INSERT INTO students VALUES(103, 'Liubei', '1995-9-18', 'M', 'politics');
  INSERT INTO students VALUES(104, 'Sunquan', '1998-08-30', 'M', 'politics');
  INSERT INTO students VALUES(105, 'Xiaoqiao', '1999-4-21', 'F', 'art');
  
  -- insert data into teachers
  INSERT INTO teathers VALUES(201, 'Zhugeliang','M');
  INSERT INTO teathers VALUES(202, 'Zhouyu','M');
  INSERT INTO teathers VALUES(203, 'Huangzhong','M');
  INSERT INTO teathers VALUES(204, 'Caocao','M');
  INSERT INTO teathers VALUES(205, 'Daqiao','F');
  
  -- insert data into courses
  INSERT INTO courses VALUES(3001, 'history', 2, '2006-2007-1');
  INSERT INTO courses VALUES(3002, 'art of war', 3, '2006-2007-1');
  INSERT INTO courses VALUES(3003, 'archery', 3, '2006-2007-2');
  INSERT INTO courses VALUES(3004, 'stitchwork', 2, '2006-2007-2');
  INSERT INTO courses VALUES(3005, 'appreciate poetry', 3, '2007-2008-1');
  INSERT INTO courses VALUES(3006, 'language art', 2, '2007-2008-1');
  
  -- insert data into classes
  INSERT INTO classes(class_id, course_id, tea_id) VALUES(6001, 3001, 201);
  INSERT INTO classes(class_id, course_id, tea_id) VALUES(6002, 3001, 204);
  INSERT INTO classes(class_id, course_id, tea_id) VALUES(6003, 3002, 201);
  INSERT INTO classes(class_id, course_id, tea_id) VALUES(6004, 3002, 202);
  INSERT INTO classes(class_id, course_id, tea_id) VALUES(6005, 3003, 203);
  INSERT INTO classes(class_id, course_id, tea_id) VALUES(6006, 3004, 205);
  INSERT INTO classes(class_id, course_id, tea_id) VALUES(6007, 3005, 202);
  INSERT INTO classes(class_id, course_id, tea_id) VALUES(6008, 3005, 204);
  INSERT INTO classes(class_id, course_id, tea_id) VALUES(6009, 3006, 201);
  INSERT INTO classes(class_id, course_id, tea_id) VALUES(6010, 3006, 202);

• 建立改变班级人数的辅助触发器
  因为用的是datagrip，所以就没有在头尾加

  CREATE TRIGGER class_number_insert AFTER INSERT
      ON select_courses
      FOR EACH ROW
      BEGIN
          IF NEW.class_id IN (SELECT class_id FROM classes) THEN
              UPDATE classes SET number = number + 1 
              WHERE class_id = NEW.class_id;
          END IF;
      END;
  
  CREATE TRIGGER class_number_delete AFTER DELETE
      ON select_courses
      FOR EACH ROW
      BEGIN
          IF OLD.class_id IN (SELECT class_id FROM classes) THEN
              UPDATE classes SET number = number - 1 
              WHERE class_id = OLD.class_id;
          END IF;
      END;

• 向

  INSERT INTO select_courses VALUES(101,6001,90);
  INSERT INTO select_courses VALUES(101,6003,87);
  INSERT INTO select_courses VALUES(101,6008,80);
  INSERT INTO select_courses VALUES(102,6001,75);
  INSERT INTO select_courses VALUES(102,6002,60);
  INSERT INTO select_courses VALUES(103,6001,93);
  INSERT INTO select_courses VALUES(103,6010,85);
  INSERT INTO select_courses VALUES(103,6008,70);
  INSERT INTO select_courses VALUES(104,6004,87);
  INSERT INTO select_courses VALUES(104,6007,85);
  INSERT INTO select_courses VALUES(104,6010,80);
  INSERT INTO select_courses VALUES(105,6006,90);

• 建立触发器，实现学生不能选择课程号相同的课程。
  若选择学生选择已选的课程，会抛出

  CREATE TRIGGER non_repetition BEFORE INSERT
      ON select_courses
      FOR EACH ROW
      BEGIN
          IF (SELECT course_id 
              FROM classes 
              WHERE NEW.class_id = class_id)
              IN 
              (SELECT classes.course_id
               FROM select_courses
               JOIN classes ON classes.class_id = select_courses.class_id
               WHERE stu_id = NEW.stu_id)
              THEN
              SIGNAL SQLSTATE 'HX000' SET MESSAGE_TEXT = 'course was selected';
          END IF;
      END;

  可以尝试插入一个错误数据，看看结果是否正确。
  6002班级的课程为3001，而101学生已选的6001班级也是教3001课程的，所以理论上这个数据不应该被插入。

  INSERT INTO select_courses VALUES(101,6002,90);

• 创建视图，查询2006－2007学年没有被选修的课程
  这里“未选修”对应到表上就是，课程人数为0。2006-2007学年即

  CREATE VIEW not_selected_2006_2007
      AS 
      SELECT class_id, course_name, name
      FROM classes
      JOIN courses c on classes.course_id = c.course_id
      JOIN teathers t on classes.tea_id = t.tea_id
      WHERE number = 0 AND open_time in ('2006-2007-1','2006-2007-2');

• 创建存储过程，查看指定学期的学生学分

  CREATE
      PROCEDURE get_total_credit(IN term VARCHAR(20))
      BEGIN
          SELECT students.stu_id, students.name, SUM(courses.credit)
          FROM select_courses
          JOIN classes ON classes.class_id = select_courses.class_id
          JOIN students ON select_courses.stu_id = students.stu_id
          JOIN courses ON classes.course_id = courses.course_id
          WHERE courses.open_time = term
          GROUP BY students.stu_id, name;
      END;

• 创建存储过程，各教师任课的学生人数

  CREATE PROCEDURE num_of_students()
  BEGIN
      SELECT t.name, c.course_name,  SUM(number) num_of_stu
      FROM classes
      JOIN courses c on classes.course_id = c.course_id
      JOIN teathers t on classes.tea_id = t.tea_id
      GROUP BY c.course_id, t.tea_id;
  END;

  这里会发现，结果和

  classes

  表一样，只有当你允许一个老师开好几个班上同一门课时（即班级号不同但教师号和课程号不同），才会不同。

至此，数据库建立完毕。

结语

这里的设计和原来相比，取巧的地方在于，多加了一个临时班级实体，不仅将学生对课程、学生对老师、老师对课程的三角多对多关系，转化成课程对班级、老师对班级的一对多关系和学生对班级的多对多关系；而且临时班级实体中加入的班级人数属性，可以被查询没有被选修的课程、统计教师任课的学生人数的实现加以利用，代码更简洁。

今天关于《数据库综合实验--设计某高校学生选课管理系统》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！