苏州大学数据库课程设计

1、精品数据库课程设计专业计算机科学与技术年级15级姓名学号指导老师使用日期感谢下载载苏州大学计算机科学与技术学院统一印制二零一七年三月一：概述项目背景：数据库课程设计系统名称：教务管理系统开发环境：SQLServer2016二：需求分析1. 系统概述根据要求设计一个数据库教务管理系统2. 教务系统的ER图3. 数据库表(1)Student表列名称约束条件键说明Stu_idVarchar(20)主键学号Stu_nameVarchar(20)姓名Stu_sexVarchar(10)男或女性别Class_idVarchar(20)外键班级编号Stu_identityVarchar(20)身份证号Stu

2、_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_

3、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)秘书姓名Secreta

4、ry_sexVarchar(10)男或女秘书性别Secretary_usenameVarchar(20)秘书用户名Secretary_passwordVarchar(20)秘书密他(6)Major表列名称约束条件键说明Major_idVarchar(20)主键专业编号Major_nameVarchar(20)专业名称Class_countint大于等于0专业班级数目College_idVarchar(20)外键学院编号Course表列名称约束条件键说明Course_idVarchar(20)主键课程编号Course_nameVarchar(20)课程名称Course_creditint大于等于

5、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_

6、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)主键学号Cou

7、rse_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_i

8、dVarchar(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_idVarcha

9、r(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是否人

10、数已满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_

11、GPAfloat大于等于0原始GPAScore_cur_ordtimeint大于等于0现在平时成绩Score_cur_midint大于等于0现在期中成绩Score_cur_termialint大于等于现在期末成绩0Score_cur_expeint大于等于0现在实验成绩Score_cur_finalint大于等于0现在最终总评成绩Cur_course_creditint大于等于0现在学分Cur_GPAfloat大于等于0现在GPA三：程序代码1 .SQL建表代码useEduMS/*教师表*/createtableTeacher(teacher_idvarchar(20)primarykey,t

12、eacher_namevarchar(20),teacher_sexvarchar(10)check(teacher_sexin(男,女),teacher_usenamevarchar(20),-用户名teacher_passwordvarchar(20)-密码)/*秘书表*/createtableSecretary(secretary_idvarchar(20)primarykey,secretary_namevarchar(20),secretary_sexvarchar(10)check(secretary_sexin(男,女),secretary_usenamevarchar(20),

13、secretary_passwordvarchar(20)/*学院表*/createtableCollege(college_idvarchar(20)primarykey,college_namevarchar(20),secretary_idvarchar(20)notnull,-教务秘书编号major_numint,-专业数目foreignkey(secretary_id)referencesSecretary(secretary_id)/*专业表*/createtableMajor(major_idvarchar(20)primarykey,major_namevarchar(20),

14、class_countintcheck(class_count=0),-班级数college_idvarchar(20)notnull,foreignkey(college_id)referencesCollege(college_id)/*班级表*/createtableClass(class_idvarchar(20)primarykey,class_namevarchar(20),class_stuCountintcheck(class_stuCount=0),-班级人数major_idvarchar(20)notnull,gradevarchar(10),-年级foreignkey(m

15、ajor_id)referencesMajor(major_id)/*学生表*/createtableStudent(stu_idvarchar(20)primarykey,stu_namevarchar(20),stu_sexvarchar(10)check(stu_sexin(男,女)，class_idvarchar(20)notnull,stu_identityvarchar(20)notnull,-身份证号stu_intakedate,-入学时间stu_birthdate,-出生日期major_idvarchar(20)notnull,college_idvarchar(20)notn

16、ull,foreignkey(class_id)referencesClass(class_id),foreignkey(major_id)referencesMajor(major_id),foreignkey(college_id)referencesCollege(college_id)/*课程表*/createtableCourse(course_idvarchar(20)primarykey,course_namevarchar(20),course_creditint,-学分course_hourint-学时/*班级课程表*/createtableCourseClass(cours

17、e_idvarchar(20)notnull,class_idvarchar(20)notnull,teacher_idvarchar(20)notnull,class_timevarchar(20),-上课时间class_placevarchar(20),-上课地点class_weekvarchar(10)check(class_weekin(单周,双周,单双周)，上课周数major_idvarchar(20)notnull,primarykey(course_id,class_id),foreignkey(course_id)referencesCourse(course_id),fore

18、ignkey(class_id)referencesClass(class_id),foreignkey(teacher_id)referencesTeacher(teacher_id),foreignkey(major_id)referencesMajor(major_id)/*教学计划表*/createtableTPlanmajor_idvarchar(20)notnull,course_idvarchar(20)notnull,gradevarchar(10),-年级termintcheck(term0),-学期college_idvarchar(20)notnull,max_sizei

19、nt,primarykey(major_id,course_id),foreignkey(major_id)referencesMajor(major_id),foreignkey(course_id)referencesCourse(course_id),foreignkey(college_id)referencesCollege(college_id)/*选课表*/createtableCourseSelect(stu_idvarchar(20)notnull,course_idvarchar(20)notnull,termint,-学期stu_fullbit,-该课程是否人数已满tex

20、tbookbit,-是否预定教材primarykey(stu_id,course_id),foreignkey(stu_id)referencesStudent(stu_id),foreignkey(course_id)referencesCourse(course_id)/*教学表*/createtableTeaching(teacher_idvarchar(20)notnull,teacher_namevarchar(20),class_idvarchar(20)notnull,course_idvarchar(20)notnull,termint,major_idvarchar(20),

21、primarykey(teacher_id,class_id,course_id),foreignkey(teacher_id)referencesTeacher(teacher_id),foreignkey(class_id)referencesClass(class_id),foreignkey(course_id)referencesCourse(course_id),foreignkey(major_id)referencesMajor(major_id)/*临时成绩表*/createtableTempScore(stu_idvarchar(20)notnull,course_idva

22、rchar(20)notnull,class_idvarchar(20)notnull,termint,rebuild_flagbit,score_ordtimeintcheck(score_ordtime=0),-平时成绩score_midintcheck(score_mid=0),-期中成绩score_termialintcheck(score_termial=0),-期末成绩score_expeintcheck(score_expe=0),-实验成绩score_finalintcheck(score_final=0),-最终总评成绩course_creditintcheck(course

23、_credit=0),-所得学分GPAfloat,primarykey(stu_id,course_id),foreignkey(stu_id)referencesStudent(stu_id),foreignkey(course_id)referencesCourse(course_id),foreignkey(class_id)referencesClass(class_id)/*重修成绩表*/createtableRebuildScore(stu_idvarchar(20)notnull,course_idvarchar(20)notnull,class_idvarchar(20)not

24、null,-第几次重修termint,rebuild_idint,score_ordtimeintcheck(score_ordtime=0),-平时成绩score_midintcheck(score_mid=0),-期中成绩score_termialintcheck(score_termial=0),-期末成绩score_expeintcheck(score_expe=0),-实验成绩score_finalintcheck(score_final=0),-最终总评成绩course_creditintcheck(course_credit=0),-所得学分GPAfloat,primarykey

25、(stu_id,course_id,rebuild_id),foreignkey(stu_id)referencesStudent(stu_id),foreignkey(course_id)referencesCourse(course_id),foreignkey(class_id)referencesClass(class_id)/*成绩表*/createtableFinalScore(stu_idvarchar(20)notnull,course_idvarchar(20)notnull,class_idvarchar(20)notnull,termint,score_ordtimein

26、tcheck(score_ordtime=0),score_midintcheck(score_mid=0),score_termialintcheck(score_termial=0),score_expeintcheck(score_expe=0),score_finalintcheck(score_final=0),course_creditintcheck(course_credit=0),GPAfloat,primarykey(stu_id,course_id),foreignkey(stu_id)referencesStudent(stu_id),foreignkey(course

27、_id)referencesCourse(course_id),foreignkey(class_id)referencesClass(class_id)/*成绩变动记录表*/createtableChangeLog(stu_idvarchar(20)notnull,course_idvarchar(20)notnull,update_timedate,-改动时间score_pri_ordtimeintcheck(score_pri_ordtime=0),score_pri_midintcheck(score_pri_mid=0),score_pri_termialintcheck(score

28、_pri_termial=0),score_pri_expeintcheck(score_pri_expe=0),score_pri_finalintcheck(score_pri_final=0),pri_course_creditintcheck(pri_course_credit=0),pri_GPAfloat,score_cur_ordtimeintcheck(score_cur_ordtime=0),score_cur_midintcheck(score_cur_mid=0),score_cur_termialintcheck(score_cur_termial=0),score_c

29、ur_expeintcheck(score_cur_expe=0),score_cur_finalintcheck(score_cur_final=0),cur_course_creditintcheck(cur_course_credit=0),cur_GPAfloat,primarykey(stu_id,course_id),foreignkey(stu_id)referencesStudent(stu_id),foreignkey(course_id)referencesCourse(course_id),)2 .触发器代码/*对FinalScore进行插入、删除、修改操作时把数据备份到

30、Changelog中*/gocreatetriggerInsertScoreonFinalScoreforinsertasdeclarestu_idvarchar(20),course_idvarchar(20),class_idvarchar(20),termint,score_ordtimeint,score_midint,score_termialint,score_expeint,score_finalint,course_creditint,GPAfloatselectstu_id=stu_id,course_id=course_id,class_id=class_id,term=t

31、erm,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=GPAfrominsertedinsertintoChangeLog(stu_id,course_id,score_cur_ordtime,score_cur_mid,score_cur_termial,score_cur_expe,score_cur_final,cur_cours

32、e_credit,cur_GPA,update_time)values(stu_id,course_id,score_ordtime,score_mid,score_termial,score_expe,scre_final,course_credit,GPA,GETDATE()gogocreatetriggerUpdateScoreonFinalScoreforupdateasdeclarestu_idvarchar(20),course_idvarchar(20),class_idvarchar(20),termint,score_ordtimeint,score_midint,score

33、_termialint,score_expeint,score_finalint,course_creditint,GPAfloatselectstu_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=GPAfrominser

34、tedif(notexists(selectstu_idfromFinalScorewherestu_id=stu_id)begininsertintoChangeLog(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

35、_final,course_credit,GPA,GETDATE()endelsebeginupdateChangeLogsetscore_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()wherestu_id=stu_idandcourse_i

36、d=course_idendselectstu_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=GPAfromdeletedupdateChangeLogsetscore_pri_ordtime=score_ordtime,

37、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=GPAwherestu_id=stu_idandcourse_id=course_idgocreatetriggerDeleteScoreonFinalScorefordeleteasdeclarestu_idvarchar(20),course_idvarchar(20),class_idvarc

38、har(20),termint,score_ordtimeint,score_midint,score_termialint,score_expeint,score_finalint,course_creditint,GPAfloatselectstu_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

39、_final,course_credit=course_credit,GPA=GPAfrominsertedif(notexists(selectstu_idfromFinalScorewherestu_id=stu_id)begininsertintoChangeLog(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,nul

40、l,null,null,null,null,null,null,GETDATE()endelsebeginupdateChangeLogsetscore_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()wherestu_id=stu_idandcourse_id=course_idendselectstu_id=stu_id,cou

41、rse_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=GPAfromdeletedupdateChangeLogsetscore_pri_ordtime=score_ordtime,score_pri_mid=score_mid,score_cur_ter

42、mial=score_termial,score_pri_expe=score_expe,score_pri_final=score_final,pri_course_credit=course_credit,pri_GPA=GPAwherestu_id=stu_idandcourse_id=course_idgo/*当更新重修成绩时，自动比较之前的成绩，把最高的分数替换到FinalScore表中*/gocreatetriggerInsertRebuildScoreonRebuildScoreforinsertasdeclarestu_idvarchar(20),course_idvarcha

43、r(20),class_idvarchar(20),termint,rebuild_idint,score_ordtimeint,score_midint,score_termialint,score_expeint,score_finalint,course_creditint,GPAfloatdeclarescoreintselectstu_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=GPAfrominsertedselectscore=score_finalfromFinalScorewherestu_id=stu_idandcourse_id=course_idif(scorescore_final)beginupdateFinal