C语言学习系统的数据库设计（原创）

create database teaching  /*创建数据库*/

create table single_select /*单项选择题*/
(  single_select_id int identity(1,1) not null, /*序号,主键*/
   single_select_content varchar(500) not null, /*题目内容*/
   answer_A varchar(100) not null,              /*选项*/
   answer_B varchar(100) not null,
   answer_C varchar(100) not null,
   answer_D varchar(100) not null,
   standard_answer varchar(8) not null,          /*标准答案*/
)

create table mutiple_select /*多项选择题*/
(   mutiple_select_id int identity(1001,1) not null,/*主键*/
    mutiple_select_content varchar(500) not null,
    answer_A varchar(100) not null,   
    answer_B varchar(100) not null,
    answer_C varchar(100) not null,   
    answer_D varchar(100) not null,
    answer_E varchar(100),   
    answer_F varchar(100),
    answer_G varchar(100),   
    answer_H varchar(100),
    standard_answer varchar(16) not null,
)

create table estimation   /*判断题*/
(
    estimation_id int identity(2001,1) not null,/*主键*/
    estimation_content varchar(500) not null,
    standard_answer varchar(10) not null,
)

create table fill_blank    /*填空题*/
(   fill_blank_id int identity(3001,1) not null,/*主键*/
    fill_blank_content varchar(500) not null,
    blank_1_standard_answer varchar(20),
    blank_2_standard_answer varchar(20),
    blank_3_standard_answer varchar(20),
    blank_4_standard_answer varchar(20),
    blank_5_standard_answer varchar(20),
)

create table simple_interlocution  /*简答题*/
(   simple_interlocution_id int identity(4001,1) not null,/*主键*/
    simple_interlocution_content varchar(500) not null,
    standard_answer varchar(1000) not null,
)

create table student_exercise      /*学生个人作业信息(学生查看)*/
(   exercise_id int not null,        /*外键*/
    exercise_content_id int not null,
    student_id varchar(20) not null,/*修改过*/
    class_name varchar(20) not null,
    exercise_receive_mark int not null,
    student_answer varchar(500) not null,
    standard_answer varchar(500) not null,
)

create table student_exam          /*学生个人考试信息(学生查看)*/
(   exam_id int not null, /*外键*/
    exam_content_id int not null,
    student_id varchar(20) not null, /*修改过*/
    class_name varchar(20) not null,
    exam_receive_mark int not null,
    student_answer varchar(500) not null,
    standard_answer varchar(500) not null,
)  

create table exercise_content_info /*学生总的作业信息(老师查看)*/
(   exercise_id int not null,       /*外键*/
    student_id varchar(20) not null,    /*外键*/
    class_name varchar(20) not null,
    exercise_total_mark int not null, /*该次作业的成绩*/
    exercise_date datetime,
)

create table exam_content_info    /*学生总的考试信息(老师查看)*/
(   exam_id int not null,         /*外键*/
    student_id varchar(20) not null,   /*外键*/
    class_name varchar(20) not null,
    exam_total_mark int not null, /*该次考试的成绩*/
    exam_date datetime,
)
    
create table exercise_content    /*学生作业题目*/
(   exercise_id int not null,      /*主键*/
    exercise_content_id int not null,
    exercise_content varchar(500) not null,
    standard_answer varchar(500),  /*修改过*/
    exercise_content_mark int not null,
    class_name varchar(20) not null,/*作业所属的班级，每个班级的作业是不一样的*/
    state_1 int not null,  /*不能连续布置作业，必须等学生做完一组作业，再布置下一组*/
    state_2 int not null,/*改作业是否需要老师批改,0表示不用批改*/
    state_3 int not null,/*该作业是否修改过了，0表示还没修改，1表示已经修改*/
)

create table exam_content        /*学生考试题目*/
(   exam_id int not null,        /*主键*/
    exam_content_id int not null,
    exam_content varchar(500) not null,
    standard_answer varchar(500), /*修改过*/
    exam_content_mark int not null,
    class_name varchar(20) not null,/*改试卷所属的班级，每个班级的试卷是不同的*/
    state_1 int not null,/*0表示学生还没有做过该试卷，1表示已经有做过 了*/
    state_2 int not null,/*该试卷是否已经修改,0表示还没修改，1表示已修改*/
)

create table question
(   question_id int identity(1,1) not null,  /*问题自动编号*/
    question_chapter int not null, /*章*/
    question_section int not null, /*节*/
    student_id varchar(20), /*学号*/
    ask_question_date datetime, /*提问时间*/
    /*answer_question_date datetime,解答时间*/
    question_content varchar(500) not null, /*问题内容*/
    question_answer varchar(500),   /*问题答案*/
    /*answer_id int int,*/
)   

create table chapter_section   /*章节对照表*/
(   question_chapter int not null,
    question_chapter_content varchar(100) not null,
    question_section int not null,
    question_section_content varchar(100) not null,
)

create table student_login     /*学生登陆表*/
(   student_id varchar(20) not null,
    student_password varchar(20) not null,
)

create table teacher_login   /*教师登陆表*/
(   teacher_id varchar(20) not null,
    teacher_password varchar(20) not null,
)  

create table student_info        /*学生基本情况表*/
(   xymch varchar(20) not null,/*学院*/
    class_id int identity(1,1) not null,/*班级编号*/
    class_name varchar(20) not null,/*班级名称，学生所属班级*/
    student_id varchar(20) not null,
    student_name varchar(20) not null,
    sex varchar(2) not null,
    tel varchar(20) not null,
    mail varchar(50),
)  

create table event        /*学生点名情况表*/
(   event_date char(30),
    event_type tinyint,
    event_id int identity(1,1) not null,
    student_id varchar(20) not null,
    class_name varchar(20) not null,
)

/*主键设置*/
ALTER TABLE single_select ADD CONSTRAINT single_select_id_pk PRIMARY KEY(single_select_id);
ALTER TABLE mutiple_select ADD CONSTRAINT mutiple_select_id_pk PRIMARY KEY(mutiple_select_id);
ALTER TABLE estimation ADD CONSTRAINT estimation_id_pk PRIMARY KEY(estimation_id);
ALTER TABLE fill_blank ADD CONSTRAINT fill_blank_id_pk PRIMARY KEY(fill_blank_id);
ALTER TABLE simple_interlocution ADD CONSTRAINT simple_interlocution_id_pk PRIMARY KEY(simple_interlocution_id);

/*ALTER TABLE exercise_content ADD CONSTRAINT exercise_id_pk PRIMARY KEY(exercise_id);
//ALTER TABLE exam_content ADD CONSTRAINT exam_id_pk PRIMARY KEY(exam_id);*/
ALTER TABLE student_info ADD CONSTRAINT student_id_pk PRIMARY KEY(student_id);

/*外键设置*/
/*ALTER TABLE exercise_content_info ADD CONSTRAINT exercise_id_fk FOREIGN KEY(exercise_id) REFERENCES exercise_content;
//ALTER TABLE exam_content_info ADD CONSTRAINT exam_id_fk FOREIGN KEY(exam_id) REFERENCES exam_content;*/
ALTER TABLE exercise_content_info ADD CONSTRAINT student_id_fk1 FOREIGN KEY(student_id) REFERENCES student_info;
ALTER TABLE exam_content_info ADD CONSTRAINT student_id_fk2 FOREIGN KEY(student_id) REFERENCES student_info;

/*ALTER TABLE student_exercise ADD CONSTRAINT exercise_id_fk1 FOREIGN KEY(exercise_id) REFERENCES exercise_content;
//ALTER TABLE student_exam ADD CONSTRAINT exam_id_fk1 FOREIGN KEY(exam_id) REFERENCES exam_content;*/
ALTER TABLE student_login ADD CONSTRAINT student_id_fk FOREIGN KEY(student_id) REFERENCES student_info;
ALTER TABLE event ADD CONSTRAINT student_id_fk3 FOREIGN KEY(student_id) REFERENCES student_info;

/*数据插入*/

insert into teacher_login values('a','a');

/*触发器
If exists(select * from sysobjects where name='on_count_total' and xtype='TR')
drop trigger on_count_total;

create trigger on_count_total on student_exercise
after insert
as
  declare @stu_id varchar(20)
  declare @exer_id int
  declare @sco int
begin
  set @stu_id=(select student_id from inserted)
  set @exer_id=(select exercise_id from inserted)
  set @sco=(select count(exercise_receive_mark) from student_exercise where exercise_id=@exer_id and student_id=@stu_id)
  update exercise_content_info set exercise_total_mark=@sco where student_id=@stu_id and exercise_id=@exer_id
end
*/

If exists(select * from sysobjects where name='on_count_total1' and xtype='TR')
drop trigger on_count_total1;

create trigger on_count_total1 on student_exercise
after update
as
  declare @stu_id varchar(20)
  declare @exer_id int
  declare @sco int
begin
  set @stu_id=(select student_id from updated)
  set @exer_id=(select exercise_id from updated)
  set @sco=(select count(exercise_receive_mark) from student_exercise where exercise_id=@exer_id and student_id=@stu_id)
  update exercise_content_info set exercise_total_mark=@sco where student_id=@stu_id and exercise_id=@exer_id
end

/*保证每次作业的总分都为100，没道题的分数是一样的*/
If exists(select * from sysobjects where name='insert_exercise_content' and xtype='TR')
drop trigger insert_exercise_content; 

create trigger insert_exercise_content on exercise_content
after insert
as
  declare @count1 int
  declare @count2 int
  declare @score int
  declare @class_name varchar(20)
begin
  set @class_name=(select class_name from inserted)
  set @count1=(select count(distinct exercise_id) from exercise_content where class_name=@class_name)
  set @count2=(select count(exercise_id) from exercise_content where exercise_id=@count1 and class_name=@class_name)
  set @score=100/@count2
  update exercise_content set exercise_content_mark=@score where exercise_id=@count1
end  

If exists(select * from sysobjects where name='delete_exercise_content' and xtype='TR')
drop trigger delete_exercise_content; 

create trigger delete_exercise_content on exercise_content
after delete
as
  declare @count1 int
  declare @count2 int
  declare @score int
  declare @class_name varchar(20)
begin
  set @class_name=(select class_name from deleted)
  set @count1=(select count(distinct exercise_id) from exercise_content where class_name=@class_name)
  set @count2=(select count(exercise_id) from exercise_content where exercise_id=@count1 and class_name=@class_name)
  set @score=100/@count2
  update exercise_content set exercise_content_mark=@score where exercise_id=@count1
end

/*student_info的信息与student_login表的对应*/
If exists(select * from sysobjects where name='insert_student_info' and xtype='TR')
drop trigger insert_student_info;

create trigger insert_student_info on student_info
after insert
as
 declare @student_id char(20)
begin
 set @student_id=(select student_id from inserted)
 insert into student_login values(@student_id,@student_id)
end

If exists(select * from sysobjects where name='delete_student_info' and xtype='TR')
drop trigger delete_student_info;

create trigger delete_student_info on student_info
after delete
as
 declare @student_id char(20)
begin
 set @student_id=(select student_id from deleted)
 delete from student_login where student_id=@student_id
end

/*exam_content_info中的exam_total_mark总是等于student_exam中相对应的exam_receive_mark的和*/
If exists(select * from sysobjects where name='insert_student_exam' and xtype='TR')
drop trigger insert_student_exam;

create trigger insert_student_exam on student_exam
after insert
as
  declare @exam_id int
  declare @student_id varchar(20)
  declare @exam_total_mark float
begin
  set @exam_id =(select exam_id from inserted)
  set @student_id =(select student_id from inserted)
  set @exam_total_mark =(select sum(exam_receive_mark) from student_exam where exam_id=@exam_id and student_id=@student_id)
  update exam_content_info set exam_total_mark=@exam_total_mark where exam_id=@exam_id and student_id=@student_id
end

/*exercise_content_info中的exercise_total_mark总是等于student_exercise中相对应的exercise_receive_mark的和*/
If exists(select * from sysobjects where name='insert_student_exercise' and xtype='TR')
drop trigger insert_student_exercise;

create trigger insert_student_exercise on student_exercise
after insert
as
  declare @exercise_id int
  declare @student_id varchar(20)
  declare @exercise_total_mark float
begin
  set @exercise_id =(select exercise_id from inserted)
  set @student_id =(select student_id from inserted)
  set @exercise_total_mark =(select sum(exercise_receive_mark) from student_exercise where exercise_id=@exercise_id and student_id=@student_id)
  update exercise_content_info set exercise_total_mark=@exercise_total_mark where exercise_id=@exercise_id and student_id=@student_id
end