蘇州大學(xué)數(shù)據(jù)庫(kù)課程設(shè)計(jì)(2)(編輯修改稿)
2025-06-12 21:17 本頁(yè)面
【文章內(nèi)容簡(jiǎn)介】 rimary key(teacher_id,class_id,course_id), foreign key(teacher_id) references Teacher(teacher_id), foreign key(class_id) references Class(class_id), foreign key(course_id) references Course(course_id), foreign key(major_id) references Major(major_id) ) /*臨時(shí)成績(jī)表 */ create table TempScore ( stu_id varchar(20) not null, course_id varchar(20) not null, class_id varchar(20) not null, term int, rebuild_flag bit, score_ordtime int check(score_ordtime = 0), 平時(shí)成績(jī) score_mid int check(score_mid =0), 期中成績(jī) score_termial int check(score_termial = 0), 期末成績(jī) score_expe int check(score_expe = 0), 實(shí)驗(yàn)成績(jī) score_final int check(score_final = 0), 最終總評(píng)成績(jī) course_credit int check(course_credit = 0), 所得學(xué)分 GPA float, primary key(stu_id,course_id), foreign key(stu_id) references Student(stu_id), foreign key(course_id) references Course(course_id), foreign key(class_id) references Class(class_id) ) /*重修成績(jī)表 */ create table RebuildScore ( stu_id varchar(20) not null, course_id varchar(20) not null, class_id varchar(20) not null, term int, rebuild_id int, 第幾次重修 score_ordtime int check(score_ordtime = 0), 平時(shí)成績(jī) score_mid int check(score_mid =0), 期中成績(jī) score_termial int check(score_termial = 0), 期末成績(jī) score_expe int check(score_expe = 0), 實(shí)驗(yàn)成績(jī) score_final int check(score_final = 0), 最終總評(píng)成績(jī) course_credit int check(course_credit = 0), 所得學(xué)分 GPA float, primary key(stu_id,course_id,rebuild_id), foreign key(stu_id) references Student(stu_id), foreign key(course_id) references Course(course_id), foreign key(class_id) references Class(class_id) ) /*成績(jī)表 */ create table FinalScore ( stu_id varchar(20) not null, course_id varchar(20) not null, class_id varchar(20) not null, term int, score_ordtime int check(score_ordtime = 0), score_mid int check(score_mid =0), score_termial int check(score_termial = 0), score_expe int check(score_expe = 0), score_final int check(score_final = 0), course_credit int check(course_credit = 0), GPA float, primary key(stu_id,course_id), foreign key(stu_id) references Student(stu_id), foreign key(course_id) references Course(course_id), foreign key(class_id) references Class(class_id) ) /*成績(jī)變動(dòng)記錄表 */ create table ChangeLog ( stu_id varchar(20) not null, course_id varchar(20) not null, update_time date, 改動(dòng)時(shí)間 score_pri_ordtime int check(score_pri_ordtime = 0), score_pri_mid int check(score_pri_mid =0), score_pri_termial int check(score_pri_termial = 0), score_pri_expe int check(score_pri_expe = 0), score_pri_final int check(score_pri_final = 0), pri_course_credit int check(pri_course_credit = 0), pri_GPA float, score_cur_ordtime int check(score_cur_ordtime = 0), score_cur_mid int check(score_cur_mid =0), score_cur_termial int check(score_cur_termial = 0), score_cur_expe int check(score_cur_expe = 0), score_cur_final int check(score_cur_final = 0), cur_course_credit int check(cur_course_credit = 0), cur_GPA float, primary key(stu_id,course_id), foreign key(stu_id) references Student(stu_id), foreign key(course_id) references Course(course_id), ) /*對(duì) FinalScore進(jìn)行插入、刪除、修改操作時(shí)把數(shù)據(jù)備份到 Changelog中 */ go create trigger InsertScore on FinalScore for insert as declare @stu_id varchar(20), @course_id varchar(20), @class_id varchar(20), @term int, @score_ordtime int, @score_mid int, @score_termial int, @score_expe int, @score_final int, @course_credit int, @GPA float select @stu_id = stu_id, @course_id = course_id, @class_id = class_id,@term = term, @score_ordtime = score_ordtime, @score_mid = score_mid, @score_termial =score_termial,@score_expe = score_expe, @score_final = score_final, @course_credit = course_credit, @GPA = GPA from inserted insert into ChangeLog(stu_id,course_id,score_cur_ordtime,score_cur_mid,score_cur_termial,score_cur_
教學(xué)課件相關(guān)推薦
鄂ICP備17016276號(hào)-1