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

数据库综合实验重做

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

题目如下:

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

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

前言

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

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

当时做实验的时候,对数据的描述如下:

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

E-R图如下:
原方案E-R图.png

用Excel做的表格如下:
原方案数据.png

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

接下来,正文开始。

数据库描述和E-R图

有要求,可转化成如下数据间关系:

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

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

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

数据库建立

需要建立的东西如下(加粗为主码):

  • 实体

    学生(学号,姓名,性别,生日,专业)

    教师(职工号,姓名,性别)

    课程(课程号,课程名,学分,开课学期)

    临时班级(班级号,课程号,教师号,人数)

  • 关系

    选课(学号班级号,成绩)

  • 视图

    未选修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
    );
  • 插入除select_courses表以外的数据。
    这里要注意,在向classes表插入数据时,不要插入人数的数据。人数将会通过触发器,每当有人选课时(即向select_courses插入数据时),对应班级人数加一,删除同理。

    -- 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,所以就没有在头尾加delimiter

    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;
  • select_courses表插入数据

    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);
  • 建立触发器,实现学生不能选择课程号相同的课程。
    若选择学生选择已选的课程,会抛出course was selected的异常。

    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学年即2006-2007-12006-2007-2两个学期。

    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表一样,只有当你允许一个老师开好几个班上同一门课时(即班级号不同但教师号和课程号不同),才会不同。

至此,数据库建立完毕。

结语

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

阅读 784

推荐阅读