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

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

2、ntityVarchar(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表列名称类型约束条件 键说明Cl

3、ass_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)主键秘书编号S

4、ecretary_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)主键课程编号

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

6、char(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

7、)外键专业编号（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大于等于

8、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表列名称类型约束条件

9、键说明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_id

10、Varchar(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

11、原始实验成绩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建

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), -用户名 teacher_password varchar(20) -密码)/*秘书表*/create table Secretary( secretary_id varchar(20) primary key, secretary

13、_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 k

14、ey(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

15、_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 var

16、char(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 Maj

17、or(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, tea

18、cher_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) referenc

19、es 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,

20、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, t

21、erm 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)

22、 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)

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

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

25、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), -

26、期中成绩 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(sc