2022年苏州大学数据库课程设计 .pdf

数据库课程设计实验报告专业计算机科学与技术年级 15 级姓名学号指导老师使用日期苏州大学计算机科学与技术学院统一印制二零一七年三月名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 34 页 -一：概述项目背景：数据库课程设计系统名称：教务管理系统开发环境：SQLServer 2016 二：需求分析1.系统概述根据要求设计一个数据库教务管理系统2.教务系统的 ER 图名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 34 页 -3.数据库表（1）Student表列名称类型约束条件键说明Stu_id Varchar(20)主键学号Stu_name Varchar(20)姓名Stu_sex Varchar(10)男或女性别Class_id Varchar(20)外键班级编号Stu_identity Varchar(20)身份证号Stu_intake date 入学时间Stu_birth date 出生日期Major_id Varchar(20)外键专业编号College_id Varchar(20)外键学院编号（2）Teacher表列名称类型约束条件键说明Teacher_id Varchar(20)主键教师编号Teacher_name Varchar(20)教师姓名Teacher_sex Varchar(10)男或女教师性别Teacher_usename Varchar(20)教师用户名Teacher_password Varchar(20)教师密码名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 34 页 -（3）Class表列名称类型约束条件键说明Class_id Varchar(20)主键班级编号Class_name Varchar(20)班级名称Class_stuCount int 大于等于 0 班级人数Major_id Varchar(20)外键专业编号grade Varchar(10)年级（4）College 表列名称类型约束条件键说明College_id Varchar(20)主键学院编号College_name Varchar(20)学院名称Secretary_id Varchar(20)外键秘书编号Major_num int 大于等于 0 专业数（5）Secretary表列名称类型约束条件键说明Secretary_id Varchar(20)主键秘书编号Secretary_name Varchar(20)秘书姓名Secretary_sex Varchar(10)男或女秘书性别Secretary _usename Varchar(20)秘书用户名Secretary _password Varchar(20)秘书密码名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 34 页 -（6）Major 表列名称类型约束条件键说明Major_id Varchar(20)主键专业编号Major_name Varchar(20)专业名称Class_count int 大于等于 0 专业班级数目College_id Varchar(20)外键学院编号（7）Course表列名称类型约束条件键说明Course_id Varchar(20)主键课程编号Course_name Varchar(20)课程名称Course_credit int 大于等于 0 课程学分Course_hour int 大于等于 0 课程学时（8）Teaching表列名称类型约束条件键说明Teacher_id Varchar(20)主键教师编号Teacher_name Varchar(20)教师姓名Class_id Varchar(10)主键班级编号Course_id Varchar(20)主键课程编号Major_id Varchar(20)外键专业编号Term int 大于 0 学期名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 34 页 -（9）TPlan 表列名称类型约束条件键说明Major_id Varchar(20)主键专业编号Course_id Varchar(20)主键课程编号grade Varchar(10)年级term int 大于 0 学期College_id Varchar(20)外键学员编号Max_size int 大于等于 0 最大容量（10）CourseClass表列名称类型约束条件键说明Course_id Varchar(20)主键课程编号Class_id Varchar(20)主键班级编号Teacher_id Varchar(20)外键教师编号Class_time Varchar(20)上课时间Class_place Varchar(20)上课地点Class_week Varchar(10)单周、双周、单双周上课周Major_id Varchar(20)外键专业编号（11）TempScore表名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 34 页 -列名称类型约束条件键说明Stu_id Varchar(20)主键学号Course_id Varchar(20)主键课程编号Class_id Varchar(20)外键班级编号term int 大于 0 学期Rebuild_flag bit 是 否 是 重 修TempScore Score_ordtime int 大于等于 0 平时成绩Score_mid int 大于等于 0 期中成绩Score_termial int 大于等于 0 期末成绩Score_expe int 大于等于 0 实验成绩Score_final int 大于等于 0 最终总评成绩Course_credit int 大于等于 0 学分GPA float 大于等于 0 GPA（12）FinalScore表列名称类型约束条件键说明Stu_id Varchar(20)主键学号Course_id Varchar(20)主键课程编号Class_id Varchar(20)外键班级编号term int 大于 0 学期Score_ordtime int 大于等于 0 平时成绩名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 34 页 -Score_mid int 大于等于 0 期中成绩Score_termial int 大于等于 0 期末成绩Score_expe int 大于等于 0 实验成绩Score_final int 大于等于 0 最终总评成绩Course_credit int 大于等于 0 学分GPA float 大于等于 0 GPA（13）RebuildScore表列名称类型约束条件键说明Stu_id Varchar(20)主键学号Course_id Varchar(20)主键课程编号Class_id Varchar(20)外键班级编号term int 大于 0 学期Rebuild_id int 主键第几次重修Score_ordtime int 大于等于 0 平时成绩Score_mid int 大于等于 0 期中成绩Score_termial int 大于等于 0 期末成绩Score_expe int 大于等于 0 实验成绩Score_final int 大于等于 0 最终总评成绩Course_credit int 大于等于 0 学分GPA float 大于等于 0 GPA 名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 34 页 -（14）CourseSelect表列名称类型约束条件键说明Stu_id Varchar(20)主键学号Course_id Varchar(20)主键课程编号Term int 大于 0 学期Stu_full bit 是否人数已满Textbook bit 是否预定教材（15）ChangeLog表列名称类型约束条件键说明Stu_id Varchar(20)主键学号Course_id Varchar(20)主键课程编号Update_time Varchar(20)外键班级编号Score_pri_ordtime int 大于等于 0 原始平时成绩Score_pri_mid int 大于等于 0 原始期中成绩Score_pri_termial int 大于等于 0 原始期末成绩Score_pri_expe int 大于等于 0 原始实验成绩Score_pri_final int 大于等于 0 原 始最 终 总 评成绩Pri_course_credit int 大于等于 0 原始学分名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 34 页 -Pri_GPA float 大于等于 0 原始 GPA Score_cur_ordtime int 大于等于 0 现在平时成绩Score_cur_mid int 大于等于 0 现在期中成绩Score_cur_termial int 大于等于 0 现在期末成绩Score_cur_expe int 大于等于 0 现在实验成绩Score_cur_final int 大于等于 0 现 在最 终 总 评成绩Cur_course_credit int 大于等于 0 现在学分Cur_GPA float 大于等于 0 现在 GPA 三：程序代码1.SQL 建表代码use EduMS/*教师表*/createtable Teacher(teacher_id varchar(20)primarykey,teacher_name varchar(20),teacher_sex varchar(10)check(teacher_sex in(男,女),teacher_usename varchar(20),-用户名teacher_password varchar(20)-密码名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 34 页 -)/*秘书表*/createtable Secretary(secretary_id varchar(20)primarykey,secretary_name varchar(20),secretary_sex varchar(10)check(secretary_sex in(男,女),secretary_usename varchar(20),secretary_password varchar(20)/*学院表*/createtable College(college_id varchar(20)primarykey,college_name varchar(20),secretary_id varchar(20)notnull,-教务秘书编号major_num int,-专业数目foreignkey(secretary_id)references Secretary(secretary_id)/*专业表*/名师资料总结-精品资料欢迎下载-名师精心整理-第 11 页，共 34 页 -createtable Major(major_id varchar(20)primarykey,major_name varchar(20),class_count intcheck(class_count=0),-班级数college_id varchar(20)notnull,foreignkey(college_id)references College(college_id)/*班级表*/createtable Class(class_id varchar(20)primarykey,class_name varchar(20),class_stuCount intcheck(class_stuCount=0),-班级人数major_id varchar(20)notnull,grade varchar(10),-年级foreignkey(major_id)references Major(major_id)/*学生表*/createtable Student 名师资料总结-精品资料欢迎下载-名师精心整理-第 12 页，共 34 页 -(stu_id varchar(20)primarykey,stu_name varchar(20),stu_sex varchar(10)check(stu_sex in(男,女),class_id varchar(20)notnull,stu_identity varchar(20)notnull,-身份证号stu_intake date,-入学时间stu_birth date,-出生日期major_id varchar(20)notnull,college_id varchar(20)notnull,foreignkey(class_id)references Class(class_id),foreignkey(major_id)references Major(major_id),foreignkey(college_id)references College(college_id)/*课程表*/createtable Course(course_id varchar(20)primarykey,course_name varchar(20),course_credit int,-学分course_hour int-学时)名师资料总结-精品资料欢迎下载-名师精心整理-第 13 页，共 34 页 -/*班级课程表*/createtable CourseClass(course_id varchar(20)notnull,class_id varchar(20)notnull,teacher_id varchar(20)notnull,class_time varchar(20),-上课时间class_place varchar(20),-上课地点class_week varchar(10)check(class_week in(单周,双周,单双周 ),-上课周数major_id varchar(20)notnull,primarykey(course_id,class_id),foreignkey(course_id)references Course(course_id),foreignkey(class_id)references Class(class_id),foreignkey(teacher_id)references Teacher(teacher_id),foreignkey(major_id)references Major(major_id)/*教学计划表*/createtable TPlan(名师资料总结-精品资料欢迎下载-名师精心整理-第 14 页，共 34 页 -major_id varchar(20)notnull,course_id varchar(20)notnull,grade varchar(10),-年级term intcheck(term 0),-学期college_id varchar(20)notnull,max_size int,primarykey(major_id,course_id),foreignkey(major_id)references Major(major_id),foreignkey(course_id)references Course(course_id),foreignkey(college_id)references College(college_id)/*选课表*/createtable CourseSelect(stu_id varchar(20)notnull,course_id varchar(20)notnull,term int,-学期stu_full bit,-该课程是否人数已满textbook bit,-是否预定教材primarykey(stu_id,course_id),foreignkey(stu_id)references Student(stu_id),foreignkey(course_id)references Course(course_id)名师资料总结-精品资料欢迎下载-名师精心整理-第 15 页，共 34 页 -)/*教学表*/createtable Teaching(teacher_id varchar(20)notnull,teacher_name varchar(20),class_id varchar(20)notnull,course_id varchar(20)notnull,term int,major_id varchar(20),primarykey(teacher_id,class_id,course_id),foreignkey(teacher_id)references Teacher(teacher_id),foreignkey(class_id)references Class(class_id),foreignkey(course_id)references Course(course_id),foreignkey(major_id)references Major(major_id)/*临时成绩表*/createtable TempScore(stu_id varchar(20)notnull,course_id varchar(20)notnull,class_id varchar(20)notnull,名师资料总结-精品资料欢迎下载-名师精心整理-第 16 页，共 34 页 -term int,rebuild_flag bit,score_ordtime intcheck(score_ordtime=0),-平时成绩score_mid intcheck(score_mid=0),-期中成绩score_termial intcheck(score_termial=0),-期末成绩score_expe intcheck(score_expe=0),-实验成绩score_final intcheck(score_final=0),-最终总评成绩course_credit intcheck(course_credit=0),-所得学分GPA float,primarykey(stu_id,course_id),foreignkey(stu_id)references Student(stu_id),foreignkey(course_id)references Course(course_id),foreignkey(class_id)references Class(class_id)/*重修成绩表*/createtable RebuildScore(stu_id varchar(20)notnull,course_id varchar(20)notnull,class_id varchar(20)notnull,term int,rebuild_id int,-第几次重修名师资料总结-精品资料欢迎下载-名师精心整理-第 17 页，共 34 页 -score_ordtime intcheck(score_ordtime=0),-平时成绩score_mid intcheck(score_mid=0),-期中成绩score_termial intcheck(score_termial=0),-期末成绩score_expe intcheck(score_expe=0),-实验成绩score_final intcheck(score_final=0),-最终总评成绩course_credit intcheck(course_credit=0),-所得学分GPA float,primarykey(stu_id,course_id,rebuild_id),foreignkey(stu_id)references Student(stu_id),foreignkey(course_id)references Course(course_id),foreignkey(class_id)references Class(class_id)/*成绩表*/createtable FinalScore(stu_id varchar(20)notnull,course_id varchar(20)notnull,class_id varchar(20)notnull,term int,score_ordtime intcheck(score_ordtime=0),score_mid intcheck(score_mid=0),score_termial intcheck(score_termial=0),名师资料总结-精品资料欢迎下载-名师精心整理-第 18 页，共 34 页 -score_expe intcheck(score_expe=0),score_final intcheck(score_final=0),course_credit intcheck(course_credit=0),GPA float,primarykey(stu_id,course_id),foreignkey(stu_id)references Student(stu_id),foreignkey(course_id)references Course(course_id),foreignkey(class_id)references Class(class_id)/*成绩变动记录表*/createtable ChangeLog(stu_id varchar(20)notnull,course_id varchar(20)notnull,update_time date,-改动时间score_pri_ordtime intcheck(score_pri_ordtime=0),score_pri_mid intcheck(score_pri_mid=0),score_pri_termial intcheck(score_pri_termial=0),score_pri_expe intcheck(score_pri_expe=0),score_pri_final intcheck(score_pri_final=0),-pri_course_credit intcheck(pri_course_credit=0),pri_GPA float,名师资料总结-精品资料欢迎下载-名师精心整理-第 19 页，共 34 页 -score_cur_ordtime intcheck(score_cur_ordtime=0),score_cur_mid intcheck(score_cur_mid=0),score_cur_termial intcheck(score_cur_termial=0),score_cur_expe intcheck(score_cur_expe=0),score_cur_final intcheck(score_cur_final=0),cur_course_credit intcheck(cur_course_credit=0),cur_GPA float,primarykey(stu_id,course_id),foreignkey(stu_id)references Student(stu_id),foreignkey(course_id)references Course(course_id),)2.触发器代码/*对FinalScore 进行插入、删除、修改操作时把数据备份到Changelog中*/gocreatetrigger InsertScore on FinalScore forinsertasdeclare stu_id varchar(20),course_id varchar(20),名师资料总结-精品资料欢迎下载-名师精心整理-第 20 页，共 34 页 -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=GPA from inserted insertintoChangeLog(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()go名师资料总结-精品资料欢迎下载-名师精心整理-第 21 页，共 34 页 -gocreatetrigger UpdateScore on FinalScore for 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=GPA from inserted if(notexists(select stu_id from FinalScore where stu_id=stu_id)begininsertintoChangeLog(stu_id,course_id,score_cur_ordtime,score_cu名师资料总结-精品资料欢迎下载-名师精心整理-第 22 页，共 34 页 -r_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 ChangeLog set 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_id endselect stu_id=stu_id,course_id=course_id,class_id=class_id,term=term,score_ordtime=score_ordtime,score_mid=score_mid,score_termial 名师资料总结-精品资料欢迎下载-名师精心整理-第 23 页，共 34 页 -=score_termial,score_expe=score_expe,score_final=score_final,course_credit=course_credit,GPA=GPA from deleted update ChangeLog set 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_id gocreatetrigger DeleteScore on FinalScore fordeleteasdeclare 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名师资料总结-精品资料欢迎下载-名师精心整理-第 24 页，共 34 页 -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 if(notexists(select stu_id from FinalScore where stu_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,null,null,null,null,null,null,null,GETDATE()endelsebeginupdate ChangeLog set score_cur_ordtime=null,score_cur_mid =null,score_cur_termial=null,score_cur_expe 名师资料总结-精品资料欢迎下载-名师精心整理-第 25 页，共 34 页 -=null,score_cur_final=null,cur_course_credit=null,cur_GPA=null,update_time=GETDATE()where stu_id=stu_id and course_id=course_id endselect 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 deleted update ChangeLog set 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=GPA where stu_id=stu_id and course_id=course_id go/*当更新重修成绩时，自动比较之前的成绩，把最高的分数替换到名师资料总结-精品资料欢迎下载-名师精心整理-第 26 页，共 34 页 -FinalScore 表中*/gocreatetrigger InsertRebuildScore on RebuildScore forinsertasdeclare 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=GPA from inserted 名师资料总结-精品资料欢迎下载-名师精心整理-第 27 页，共 34 页 -select score=score_final from FinalScore where stu_id=stu_id and course_id=course_id if(score score_final)beginupdate FinalScore set 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 where stu_id=stu_id and course_id=cou