学生选课系统用例图，以及部分代码实现

上学期软件导论做的文档，学生选课系统，在文档的基础上，再代码实现以下

背景——用例图：一个基础的学生选课系统

ER图设计如下：(学生和课程是n - m的关系，可修改的原图找不到了，悉知)

库表设计：

---专业表

CREATE TABLE major(

major_id INT PRIMARY KEY AUTO_INCREMENT,

major_name VARCHAR(20)

);

 

---学生表

CREATE TABLE student(

student_id INT PRIMARY KEY AUTO_INCREMENT,

student_name VARCHAR(20),

grade VARCHAR(10),

pwd VARCHAR(20),

major_id INT,

CONSTRAINT FOREIGN KEY(major_id) REFERENCES major(major_id) ON DELETE CASCADE

);

 

---教师表

CREATE TABLE teacher(

teacher_id INT PRIMARY KEY AUTO_INCREMENT,

teacher_name VARCHAR(20),

major_id INT,

grade VARCHAR(10),

pwd VARCHAR(20),

CONSTRAINT FOREIGN KEY(major_id) REFERENCES major(major_id) ON DELETE CASCADE

);

 

---课程表

CREATE TABLE course(

course_id INT PRIMARY KEY AUTO_INCREMENT,

course_name VARCHAR(20),

grade VARCHAR(10),

start int,

end int,

deadline DATE,

teacher_id INT,

stu_number int,

CONSTRAINT FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ON DELETE CASCADE

);

 

---选课表

CREATE TABLE choose(

student_id INT,

course_id INT,

score INT,

CONSTRAINT FOREIGN KEY (student_id) REFERENCES student(student_id) ON DELETE CASCADE,

CONSTRAINT FOREIGN KEY (course_id) REFERENCES course(course_id) ON DELETE CASCADE

);

触发器设计：

/*

   ----当学生选择课程时，自动给该课程的人数加1

   名称： incre_trigger_SC

*/

create trigger incre_trigger_SC on choose for insert

as

declare @num int;

declare @teacher_id char(12);

declare @courseno char(4);

set @courseno  = (select course_id from inserted);

set @num = 0;

set @num =(select stu_number from course where course_id = @courseno)+1;

update course set stu_number=@num where course_id = @courseno ;

/*

   ----当学生退选课程时，自动给该课程的人数减1

   名称： decre_trigger_SC

*/

create trigger decre_trigger_SC on choose for delete

as

declare @num int;

declare @teacherno char(12);

declare @courseno char(4);

set @teacherno =  (select teacher_id from inserted);

set @courseno  = (select course_id from inserted);

set @num = 0;

set @num =(select stu_number from course where course_id = @courseno)-1;

update course set stu_number=@num where course_id = @courseno;