第1页 / 共8页
第2页 / 共8页
第3页 / 共8页
第4页 / 共8页
第5页 / 共8页
第6页 / 共8页
第7页 / 共8页
第8页 / 共8页
c语言数据库课程设计.doc
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
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
