《苏州大学数据库课程设计.doc》由会员分享，可在线阅读，更多相关《苏州大学数据库课程设计.doc（35页珍藏版）》请在三一文库上搜索。
1、数据库课程设计实验报告专业 计算机科学与技术年级15级 姓名 学号 指导老师 使用日期苏州大学计算机科学与技术学院统一印制二零一七年三月：概述 项目背景：数据库课程设计 系统名称：教务管理系统 开发环境： SQL Server 2016 ：需求分析1. 系统概述根据要求设计一个数据库教务管理系统2. 教务系统的ER图3. 数据库表(1) Student 表列名称类型约束条件键说明StudVarchar(20)主键学号Stu_n ameVarchar(20)姓名Stu_sexVarchar(10)男或女性别Class_idVarchar(20)外键班级编号Stude ntityVarchar(2
2、0)身份证号Stun takedate入学时间Stu_birthdate出生日期Major_idVarchar(20)外键专业编号College_idVarchar(20)外键学院编号(2) Teacher 表列名称类型约束条件键说明Teacher_idVarchar(20)主键教师编号Teacher_ nameVarchar(20)教师姓名Teacher_sexVarchar(10)男或女教师性别Teacher_use nameVarchar(20)教师用户名Teacher_passwordVarchar(20)教师密码(3) Class 表列名称类型约束条件键说明Class_idVarch
3、ar(20)主键班级编号Class_ nameVarchar(20)班级名称Class_stuCo untint大于等于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)主键秘书编号Secret
4、ary, nameVarchar(20)秘书姓名Secretary_sexVarchar(10)男或女秘书性别Secretary_use nameVarchar(20)秘书用户名Secretary.passwordVarchar(20)秘书密码(6) Major 表列名称类型约束条件键说明Major_idVarchar(20)主键专业编号Major_ nameVarchar(20)专业名称Class_c ountint大于等于0专业班级数目College_idVarchar(20)外键学院编号(7) Course 表列名称类型约束条件键说明Course_idVarchar(20)主键课程编号C
5、ourse_ nameVarchar(20)课程名称Course_creditint大于等于0课程学分Course_hourint大于等于0课程学时(8) Teachi ng 表列名称类型约束条件键说明Teacher_idVarchar(20)主键教师编号Teacher_ nameVarchar(20)教师姓名Class_idVarchar(10)主键班级编号Course_idVarchar(20)主键课程编号Major_idVarchar(20)外键专业编号Termint大于0学期(9) TPIan 表列名称类型约束条件键说明Major_idVarchar(20)主键专业编号Course_i
6、dVarchar(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_idVarch
7、ar(20)外键专业编号(11) TempScore 表列名称类型约束条件键说明StudVarchar(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
8、大于等于0GPA(12) FinalScore 表列名称类型约束条件键说明StudVarchar(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 表列名称类型
9、约束条件键说明StudVarchar(20)主键学号Course_idVarchar(20)主键课程编号Class_idVarchar(20)外键班级编号termint大于0学期Rebuilddint主键第几次重修Score_ordtimeint大于等于0平时成绩Score_midint大于等于0期中成绩Score_termialint大于等于0期末成绩Score_expeint大于等于0实验成绩Score_finalint大于等于0最终总评成绩Course_creditint大于等于0学分GPAfloat大于等于0GPA(14) CourseSelect 表列名称类型约束条件键说明StudV
10、archar(20)主键学号Course_idVarchar(20)主键课程编号Termint大于0学期Stu_fullbit是否人数已满Textbookbit是否预定教材(15) ChangeLog 表列名称类型约束条件键说明StudVarchar(20)主键学号Course_idVarchar(20)主课程编号键Update_timeVarchar(20)外键班级编号Score_pri_ordtimeint大于等于0原始平时成绩Score_pri_midint大于等于0原始期中成绩Score_pri_termialint大于等于0原始期末成绩Score_pri_expeint大于等于0原始
11、实验成绩Score_pri_fi nalint大于等于0原始最终总评成绩Pri_course_creditint大于等于0原始学分Pri_GPAfloat大于等于0原始GPAScore_cur_ordtimeint大于等于0现在平时成绩Score_cur_midint大于等于0现在期中成绩Score_cur_termialint大于等于现在期末成绩0Score_cur_expeint大于等于0现在实验成绩Score_c ur_finalint大于等于0现在最终总评成绩Cur_course_creditint大于等于0现在学分Cur_GPAfloat大于等于0现在GPA二程序代码1. SQL建表
12、代码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),-用户名)/*秘书表*/create table Secretary(secretary_id varchar(20) primary key,secretary, name varchar(20),secretary_sex varchar
13、(IO) check(secretary_sex in (男,女),secretary_use name 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,-专业数目foreig n key(secretary_id) refere nces Secretary(secreta
14、ry_id)/*专业表*/create table Major(major_id varchar(20) primary key,major_n ame varchar(20),class_co unt int check(class_c ount = 0),college_id varchar(20) not null,foreig n key(college_id) refere nces College(college_id) )/*班级表*/create table Class(class_id varchar(20) primary key,class_ name varchar(2
15、0),class_stuCo unt int check(class_stuCo unt = 0),-major_id varchar(20) not n ull,grade varchar(10),-班级数班级人数-年级foreig n key(major_id) refere nces Major(major_id)/*学生表*/ create table Stude nt(stud varchar(20) primary key,stu_ name varchar(20),stu_sex varchar(IO) check(stu_sex in ( 男,女),class_id varch
16、ar(20) not n ull,stu_ide ntity varchar(20) not nu II,-身份证号stun take date,-入学时间stu_birth date,-出生日期major_id varchar(20) not n ull,college_id varchar(20) not null,foreig n key(class_id) refere nces Class(class_id),foreign key(major_id) references Major(major_id), foreig n key(college_id) refere nces C
17、ollege(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 nu II, class_id varchar(20) not n ull, teacher_id varchar(20) not nu II,class_time varchar(20),-上
18、课时间class_place varchar(20),-上课地点class_week varchar(IO) check(class_week in ( 单周,双周,单双周),-上课周数major_id varchar(20) not n ull,primary key(course_id,class_id),foreig n key(course_id) referen ces Course(course_id), foreig n key(class_id) refere nces Class(class_id),foreig n key(teacher_id) refere nces T
19、eacher(teacher_id), foreig n key(major_id) refere nces Major(major_id)/*教学计划表*/create table TPla nmajor_id varchar(20) not null,course_id varchar(20) not nu II,grade varchar(IO),-年级term int check(term 0),-学期college_id varchar(20) not null,max_size int,primary key(major_id,course_id),foreign key(majo
20、r_id) references Major(major_id), foreig n key(course_id) referen ces Course(course_id), foreig n key(college_id) refere nces College(college_id) )/*选课表*/create table CourseSelect(stu_id varchar(20) not nu II,course_id varchar(20) not nu II,term int,-学期stu_full bit,-该课程是否人数已满textbook bit,-是否预定教材prim
21、ary key(stud,course_id),foreig n key(stu_id) refere nces Stude nt(stud), foreig n key(course_id) referen ces Course(course_id)/*教学表*/create table Teachi ng (teacher_id varchar(20) not nu II, teacher_ name varchar(20), class_id varchar(20) not n ull, course_id varchar(20) not nu II, term int,major_id
22、 varchar(20),primary key(teacher_id,class_id,course_id),foreig n key(teacher_id) refere nces Teacher(teacher_id), foreig n key(class_id) refere nces Class(class_id), foreig n key(course_id) referen ces Course(course_id), foreig n key(major_id) refere nces Major(major_id)/*临时成绩表*/ create table TempSc
23、ore (stu_id varchar(20) not nu II, course_id varchar(20) not nu II,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),-实验
24、成绩score_final int check(score_final = 0),-最终总评成绩course_credit int check(course_credit = 0),-所得学分GPA float,primary key(stud,course_id),foreig n key(stu_id) refere nces Stude nt(stud),foreig n key(course_id) referen ces Course(course_id),foreig n key(class_id) refere nces Class(class_id)/*重修成绩表*/creat
25、e table RebuildScore(stu_id varchar(20) not nu II,course_id varchar(20) not nu II,class_id varchar(20) not n ull,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(s
26、core_expe = 0),-实验成绩score_final int check(score_final = 0),-最终总评成绩course_credit int check(course_credit = 0),-所得学分GPA float,primary key(stud,course_id,rebuildd),foreig n key(stu_id) refere nces Stude nt(stud),foreig n key(course_id) referen ces Course(course_id),foreig n key(class_id) refere nces Cl
27、ass(class_id)/*成绩表*/create table Fin alScore(stu_id varchar(20) not nu II,course_id varchar(20) not nu II,class_id varchar(20) not n ull,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 =
28、0), score_fi nal int check(score_fi nal = 0), course_credit int check(course_credit = 0), GPA float, primary key(stud,course_id), foreig n key(stu_id) refere nces Stude nt(stud), foreig n key(course_id) referen ces Course(course_id), foreig n key(class_id) refere nces Class(class_id)/*成绩变动记录表*/creat
29、e table Chan geLog (stu_id varchar(20) not nu II,course_id varchar(20) not nu II,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), scor
30、e_pri_fi nal int check(score_pri_fi nal = 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 =
31、0), score_c ur_final int check(score_c ur_final = 0), cur_course_credit int check(cur_course_credit = 0), cur_GPA float, primary key(stud,course_id), foreig n key(stu_id) refere nces Stude nt(stud), foreig n key(course_id) referen ces Course(course_id), )2.触发器代码/*对FinalScore进行插入、删除、修改操作时把数据备份到Change
32、log 中 */gocreate trigger In sertScoreon Fin alScorefor 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_fi nal int, course_credit int,GPA floatselect stud = stud, course_id = course_id, c
33、lass_id=class_id,term = term, score_ordtime = score_ordtime, score_mid = score_mid, score_termial =score_termial,score_expe = score_expe, score_fi nal = score_fi nal, course_credit = course_credit, GPA = GPA from in sertedin sert intoCha ngeLog(stu_id,course_id,score_cur_ordtime,score_cur_ mid,score
34、_cur_termial,score_cur_expe,score_cur_fi nal,cur_c ourse_credit,cur_GPA,update_time)values(stud,course_id,score_ordtime,score_mid, score_termial,score_expe,scre_fi nal,course_credit,GPA,GETDATE()gogocreate trigger UpdateScoreon Fin alScorefor updateasdeclare stu_id varchar(20), course_id varchar(20)
35、, class_id varchar(20),term int, score_ordtime int, score_mid int, score_termial in t,score_expe int, score_fi nal int, course_credit int, GPA floatselect stud = stud, course_id = course_id, class_id=class_id,term = term, score_ordtime = score_ordtime, score_mid = score_mid, score_termial =score_ter
36、mial,score_expe = score_expe, score_fi nal = score_fi nal, course_credit = course_credit, GPA = GPA from in sertedif(not exists(select stud from Fin alScore where stud =stud)beg ininsert intoCha ngeLog(stu _id,course_id,score_cur_ordtime,score_c ur_mid,score_cur_termial,score_cur_expe,score_cur_fi n
37、al,cur_course_credit,cur_GPA,update_time)values(stu _id,course_id,score_ordtime,score_mi d,score_termial,score_expe,score_fi nal,course_c redit,GPA,GETDATE()endelsebeg inupdate Chan geLogset score_cur_ordtime = score_ordtime, score_cur_mid = score_mid, score_cur_termial = score_termial, score_cur_ex
38、pe = score_expe, score_cur_fi nal = score_fi nal, cur_course_credit = course_credit, cur_GPA = GPA, update_time = GETDATE()where stud = stud and course_id = course_id endselect stud = stud, course_id = course_id, class_id=class_id,term = term, score_ordtime = score_ordtime,score_mid = score_mid, sco
39、re_termial二score_termial,score_expe = score_expe, score_fi nal = score_fi nal, course_credit = course_credit, GPA = GPA from deletedupdate Chan geLogset score_pri_ordtime = score_ordtime, score_pri_mid= score_mid, score_cur_termial = score_termial, score_pri_expe= score_expe, score_pri_fi nal = scor
40、e_fi nal,pri_course_credit = course_credit, pri_GPA = GPAwhere stud = stud and course_id = course_idgo create trigger DeleteScoreon Fin alScorefor deleteasdeclare stu_id varchar(20), course_id varchar(20), class_id varchar(20),term int, score_ordtime int, score_mid int, score_termial in t,score_expe
41、 int, score_fi nal int, course_credit int, GPA floatselect stud = stud, 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_fi nal = score_fi nal, course_credit = course_credit, GPA = G
42、PA from in sertedif(not exists(select stud from Fin alScore where stud =stud)beg ininsert intoCha ngeLog(stu_id,course_id,score_cur_ordtime,score_cur_mid,score_cur_termial,score_cur_expe,score_cur_fin al,cur_course_credit,cur_GPA,update_time) values(stu_id,course_id ,n ull ,nu II ,n ull ,nu II ,n ull ,nu II ,n ull,G ETDATE()endelseb