苏州大学数据库课程设计.docx

数据库课程设计实验报告专业 计算机科学与技术 年级 15 级 姓名 学号 指导老师 使用日期 苏州大学计算机科学与技术学院统一印制二零一七年三月一：概述 项目背景：数据库课程设计系统名称：教务管理系统开发环境：SQL Server 2016二：需求分析1. 系统概述根据要求设计一个数据库教务管理系统2. 教务系统的ER图3. 数据库表（1）Student表列名称类型约束条件 键说明Stu_idVarchar(20)主键学号Stu_nameVarchar(20)姓名Stu_sexVarchar(10)男或女性别Class_idVarchar(20)外键班级编号Stu_identityVarchar(20)身份证号Stu_intakedate入学时间Stu_birthdate出生日期Major_idVarchar(20)外键专业编号College_idVarchar(20)外键学院编号（2）Teacher表列名称类型约束条件 键说明Teacher_idVarchar(20)主键教师编号Teacher_nameVarchar(20)教师姓名Teacher_sexVarchar(10)男或女教师性别Teacher_usenameVarchar(20)教师用户名Teacher_passwordVarchar(20)教师密码（3）Class表列名称类型约束条件 键说明Class_idVarchar(20)主键班级编号Class_nameVarchar(20)班级名称Class_stuCountint大于等于0班级人数Major_idVarchar(20)外键专业编号gradeVarchar(10)年级（4）College表列名称类型约束条件 键说明College_idVarchar(20)主键学院编号College_nameVarchar(20)学院名称Secretary_idVarchar(20)外键秘书编号Major_numint大于等于0专业数（5）Secretary表列名称类型约束条件 键说明Secretary_idVarchar(20)主键秘书编号Secretary_nameVarchar(20)秘书姓名Secretary_sexVarchar(10)男或女秘书性别Secretary _usenameVarchar(20)秘书用户名Secretary _passwordVarchar(20)秘书密码（6）Major表列名称类型约束条件 键说明Major_idVarchar(20)主键专业编号Major_nameVarchar(20)专业名称Class_countint大于等于0专业班级数目College_idVarchar(20)外键学院编号（7）Course表列名称类型约束条件 键说明Course_idVarchar(20)主键课程编号Course_nameVarchar(20)课程名称Course_creditint大于等于0课程学分Course_hourint大于等于0课程学时（8）Teaching表列名称类型约束条件 键说明Teacher_idVarchar(20)主键教师编号Teacher_nameVarchar(20)教师姓名Class_idVarchar(10)主键班级编号Course_idVarchar(20)主键课程编号Major_idVarchar(20)外键专业编号Termint大于0学期（9）TPlan表列名称类型约束条件 键说明Major_idVarchar(20)主键专业编号Course_idVarchar(20)主键课程编号gradeVarchar(10)年级termint大于0学期College_idVarchar(20)外键学员编号Max_sizeint大于等于0最大容量（10）CourseClass表列名称类型约束条件 键说明Course_idVarchar(20)主键课程编号Class_idVarchar(20)主键班级编号Teacher_idVarchar(20)外键教师编号Class_timeVarchar(20)上课时间Class_placeVarchar(20)上课地点Class_weekVarchar(10)单周、双周、单双周上课周Major_idVarchar(20)外键专业编号（11）TempScore表列名称类型约束条件 键说明Stu_idVarchar(20)主键学号Course_idVarchar(20)主键课程编号Class_idVarchar(20)外键班级编号termint大于0学期Rebuild_flagbit是否是重修TempScoreScore_ordtimeint大于等于0平时成绩Score_midint大于等于0期中成绩Score_termialint大于等于0期末成绩Score_expeint大于等于0实验成绩Score_finalint大于等于0最终总评成绩Course_creditint大于等于0学分GPAfloat大于等于0GPA（12）FinalScore表列名称类型约束条件 键说明Stu_idVarchar(20)主键学号Course_idVarchar(20)主键课程编号Class_idVarchar(20)外键班级编号termint大于0学期Score_ordtimeint大于等于0平时成绩Score_midint大于等于0期中成绩Score_termialint大于等于0期末成绩Score_expeint大于等于0实验成绩Score_finalint大于等于0最终总评成绩Course_creditint大于等于0学分GPAfloat大于等于0GPA（13）RebuildScore表列名称类型约束条件 键说明Stu_idVarchar(20)主键学号Course_idVarchar(20)主键课程编号Class_idVarchar(20)外键班级编号termint大于0学期Rebuild_idint主键第几次重修Score_ordtimeint大于等于0平时成绩Score_midint大于等于0期中成绩Score_termialint大于等于0期末成绩Score_expeint大于等于0实验成绩Score_finalint大于等于0最终总评成绩Course_creditint大于等于0学分GPAfloat大于等于0GPA（14）CourseSelect表列名称类型约束条件 键说明Stu_idVarchar(20)主键学号Course_idVarchar(20)主键课程编号Termint大于0学期Stu_fullbit是否人数已满Textbookbit是否预定教材（15）ChangeLog表列名称类型约束条件 键说明Stu_idVarchar(20)主键学号Course_idVarchar(20)主键课程编号Update_timeVarchar(20)外键班级编号Score_pri_ordtimeint大于等于0原始平时成绩Score_pri_midint大于等于0原始期中成绩Score_pri_termialint大于等于0原始期末成绩Score_pri_expeint大于等于0原始实验成绩Score_pri_finalint大于等于0原始最终总评成绩Pri_course_creditint大于等于0原始学分Pri_GPAfloat大于等于0原始GPAScore_cur_ordtimeint大于等于0现在平时成绩Score_cur_midint大于等于0现在期中成绩Score_cur_termialint大于等于0现在期末成绩Score_cur_expeint大于等于0现在实验成绩Score_cur_finalint大于等于0现在最终总评成绩Cur_course_creditint大于等于0现在学分Cur_GPAfloat大于等于0现在GPA三：程序代码1. SQL建表代码use EduMS/*教师表*/create table Teacher(teacher_id varchar(20) primary key,teacher_name varchar(20),teacher_sex varchar(10) check(teacher_sex in (男,女),teacher_usename varchar(20), -用户名teacher_password varchar(20) -密码)/*秘书表*/create table Secretary(secretary_id varchar(20) primary key,secretary_name varchar(20),secretary_sex varchar(10) check(secretary_sex in (男,女),secretary_usename varchar(20),secretary_password varchar(20)/*学院表*/create table College(college_id varchar(20) primary key,college_name varchar(20),secretary_id varchar(20) not null, -教务秘书编号major_num int, -专业数目foreign key(secretary_id) references Secretary(secretary_id)/*专业表*/create table Major(major_id varchar(20) primary key,major_name varchar(20),class_count int check(class_count = 0), -班级数college_id varchar(20) not null,foreign key(college_id) references College(college_id)/*班级表*/create table Class(class_id varchar(20) primary key,class_name varchar(20),class_stuCount int check(class_stuCount = 0),-班级人数major_id varchar(20) not null,grade varchar(10), -年级foreign key(major_id) references Major(major_id)/*学生表*/create table Student(stu_id varchar(20) primary key,stu_name varchar(20),stu_sex varchar(10) check(stu_sex in (男,女),class_id varchar(20) not null,stu_identity varchar(20) not null, -身份证号stu_intake date, -入学时间stu_birth date, -出生日期major_id varchar(20) not null,college_id varchar(20) not null,foreign key(class_id) references Class(class_id),foreign key(major_id) references Major(major_id),foreign key(college_id) references College(college_id)/*课程表*/create table Course(course_id varchar(20) primary key,course_name varchar(20),course_credit int, -学分course_hour int -学时)/*班级课程表*/create table CourseClass(course_id varchar(20) not null,class_id varchar(20) not null,teacher_id varchar(20) not null,class_time varchar(20), -上课时间class_place varchar(20), -上课地点class_week varchar(10) check(class_week in (单周,双周,单双周), -上课周数major_id varchar(20) not null,primary key(course_id,class_id),foreign key(course_id) references Course(course_id),foreign key(class_id) references Class(class_id),foreign key(teacher_id) references Teacher(teacher_id),foreign key(major_id) references Major(major_id)/*教学计划表*/create table TPlan(major_id varchar(20) not null,course_id varchar(20) not null,grade varchar(10), -年级term int check(term 0), -学期college_id varchar(20) not null, max_size int,primary key(major_id,course_id),foreign key(major_id) references Major(major_id),foreign key(course_id) references Course(course_id),foreign key(college_id) references College(college_id)/*选课表*/create table CourseSelect(stu_id varchar(20) not null,course_id varchar(20) not null,term int, -学期stu_full bit, -该课程是否人数已满textbook bit, -是否预定教材primary key(stu_id,course_id),foreign key(stu_id) references Student(stu_id),foreign key(course_id) references Course(course_id) /*教学表*/create table Teaching(teacher_id varchar(20) not null,teacher_name varchar(20),class_id varchar(20) not null,course_id varchar(20) not null,term int,major_id varchar(20),primary 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)/*临时成绩表*/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), -平时成绩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)/*重修成绩表*/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), -平时成绩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,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)/*成绩表*/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)/*成绩变动记录表*/create table ChangeLog(stu_id varchar(20) not null,course_id varchar(20) not null,update_time date, -改动时间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),)2.触发器代码/*对FinalScore进行插入、删除、修改操作时把数据备份到Changelog中*/gocreate trigger InsertScoreon FinalScorefor insertasdeclare 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 floatselect 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 = GPAfrom insertedinsert into ChangeLog(stu_id,course_id,score_cur_ordtime,score_cur_mid,score_cur_termial,score_cur_expe,score_cur_final,cur_course_credit,cur_GPA,update_time)values(stu_id,course_id,score_ordtime,score_mid,score_termial,score_expe, scre_final,course_credit,GPA,GETDATE()gogocreate trigger UpdateScoreon FinalScorefor updateasdeclare 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 floatselect 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 = GPAfrom insertedif(not exists(select stu_id from FinalScore where stu_id = stu_id)begininsert into ChangeLog(stu_id,course_id,score_cur_ordtime,score_cur_mid,score_cur_termial,score_cur_expe,score_cur_final,cur_course_credit,cur_GPA,update_time) values(stu_id,course_id,score_ordtime,score_mid,score_termial,score_expe,score_final,course_credit,GPA,GETDATE()endelsebeginupdate ChangeLogset score_cur_ordtime = score_ordtime, score_cur_mid = score_mid, score_cur_termial = score_termial, score_cur_expe = score_expe,score_cur_final = score_final, cur_course_credit = course_credit, cur_GPA = GPA, update_time = GETDATE()where stu_id = stu_id and course_id = course_idendselect 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 = GPAfrom deletedupdate ChangeLogset score_pri_ordtime = score_ordtime, score_pri_mid = score_mid, score_cur_termial = score_termial,score_pri_expe= score_expe, score_pri_final = score_final,pri_course_credit = course_credit, pri_GPA = GPAwhere stu_id = stu_id and course_id = course_idgocreate trigger DeleteScoreon FinalScorefor deleteasdeclare 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 floatselect 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 = GPAfrom insertedif(not exists(select stu_id from FinalScore where stu_id = stu_id)begininsert into ChangeLog(stu_id,course_id,score_cur_ordtime,score_cur_mid,score_cur_termial,score_cur_expe,score_cur_final,cur_course_credit,cur_GPA,update_time)values(stu_id,course_id,null,null,null,null,null,null,null,GETDATE()endelsebeginupdate ChangeLogset score_cur_ordtime = null, score_cur_mid = null, score_cur_termial = null, score_cur_expe = null,score_cur_final = null, cur_course_credit = null, cur_GPA = null, update_time = GETDATE() where stu_id = stu_id and course_id = course_idendselect 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 = GPAfrom deletedupdate ChangeLogset score_pri_ordtime = score_ordtime, score_pri_mid = score_mid, score_cur_termial = score_termial,score_pri_expe= score_expe, score_pri_final = score_final, pri_course_credit = course_credit, pri_GPA = GPAwhere stu_id = stu_id and course_id = course_idgo /*当更新重修成绩时，自动比较之前的成绩，把最高的分数替换到FinalScore表中*/gocreate trigger InsertRebuildScoreon RebuildScorefor insertas declare stu_id varchar(20), course_id varchar(20), class_id varchar(20),term int, rebuild_id int, score_ordtime int, score_mid int, score_termial int,score_expe int, score_final int, course_credit int, GPA floatdeclare score intselect stu_id = stu_id, course_id = course_id, class_id = class_id,term = term, rebuild_id = rebuild_id, 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 = GPAfrom insertedselect score = score_finalfrom FinalScorewhere stu_id = stu_id and course_id = course_idif(score score_final)beginupdate FinalScoreset 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 = GPAwhere stu_id = stu_id and course_id = course_idendgogocreate trigger UpdateRebuildScoreon RebuildScorefor updateas declare stu_id varchar(20), course_id varchar(20), class_id varchar(20),term int, rebuild_id int, score_ordtime int, score_mid int, score_termial int,score_expe int, score_final int, course_credit int, GPA floatdeclare score intselect stu_id = stu_id, course_id = course_id, class_id = class_id,term = term, rebuild_id = rebuild_id, 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 =