数据库管理系统课程设计报告

目录t需求介林 tt.t采淤(淆要求=介林 tt.S殘部敲图 St.c殘部宅典 c S.t3-H图 「 S.c总3-H图 8c毁薛註胁迂行 t0 ?.c索悼的氢立 St?.〉K图的氢立 St?.?齐粉坛券的氢立 Sc?.o贓式器的氢立 c0o采淤司合(淆慨简 cSo.tK图(淆突贬慨简 cSo.S齐粉坛券突贬慨简 cco.c贓式器(淆突贬慨简 co 11.1系统功能要求与分析1.1.1总体功能学生功能1.1.2管理员功能1.1.3教师功能21.2数据流图21查询/统计345672概念结构设计管理管理员姓名管理员管理员编号密码教师编教师编号教师姓名所在院系性别职称密码教师8学号密码学号专业学生学学生班级性别所在院系性别课程编号课程名称课程编号可选人数可选人数课程课程分教师分上课时间上上课时间9密码密码管理员姓名管理员编号密码教师编号教师姓名职称所在院系性别教师ID课程IDN教师M开设课程编号课程名称教师ID选课开始选课结束管理员1管理NNN课程N学生M选课上课时间上课地点学分已选人数可选人数密码学号学生姓名所在院系专业班级性别学生ID课程ID 构设计4.1数据库文件构成4.2数据表构成4.2.1表汇总4.2.2管理员信息表数据类型(精度范围)空空空空空空数据类型(精度范围)4.2.4教师信息表数据类型(精度范围)空空空空4.2.5课程信息表数据类型(精度范围)har空空空空空空空空空数据类型(精度范围)4.3索引构成4.4视图构成4.5存储过程构成4.6触发器构成CREATECREATEDATABASEstu_sel_courNAME=stu_cour,FILENAME="F:\database\stu_cour.mdf",SIZE=3MB,MAXSIZE=500MB,)LOGON(NAME=stu_cour_log,FILENAME="F:\database\stu_cour_log.mdf",SIZE=3MB,MAXSIZE=UNLIMITED,)实现5.1数据库的建立5.2数据表的建立USEUSEstu_sel_courCREATETABLEadmin_info(admin_idCHAR(10)PRIMARYKEY,/*管理员编号为主键*/admin_nameVARCHAR(10)NOTNULL,PASSWORDCHAR)CREATETABLEstu_info(stu_idCHAR(10)PRIMARYKEY,/*学生编号为主键*/deptVARCHAR(20),majorVARCHAR(20),classINT,sexVARCHAR(2),PASSWORDCHAR)CREATETABLEteach_info(teach_idCHAR(10)PRIMARYKEY,/*教师编号为主键*/titleVARCHAR(10),sexVARCHAR(2),PASSWORDCHAR)CREATETABLEcour_info(cour_idCHAR(10)PRIMARYKEY,/*课程编号为主键*/teach_idCHAR(10),creditTINYINT,selected_numINT,total_numINT,cour_timeVARCHAR(10),cour_placeVARCHAR(10),start_timeSMALLDATETIME,end_timeSMALLDATETIME,CONSTRAINTteach_idfk/*添加teach_id为外键，与teach_info中的teach_id相关联*/FOREIGNKEY(teach_id)REFERENCESteach_info(teach_id))CREATETABLEsc(stu_idCHAR(10)NOTNULL,cour_idCHAR(10)NOTNULL,gradeTINYINT,CONSTRAINTsc_pk/*添加stu_id和cour_id为主键*/PRIMARYKEY(stu_id,cour_id),CONSTRAINTstu_idfk/*添加stu_id为外键，与stu_info中的stu_id相关联*/FOREIGNKEY(stu_id)REFERENCESstu_info(stu_id),CONSTRAINTcour_idfk/*添加cour_id为外键，与cour_info中的cour_id相关联*/FOREIGNKEY(cour_id)REFERENCEScour_info(cour_id))INSERTINTOadmin_info(admin_id,admin_name,password)VALUES('a000000001','赵青青','admin')INSERTINTOadmin_info(admin_id,admin_name,password)VALUES('a000000002','李勇','admin')INSERTINTOadmin_info(admin_id,admin_name,password)VALUES('a000000003','张力','admin')……/*插入管理员信息*/INSERTINTOstu_info(stu_id,stu_name,dept,major,class,sex,password)VALUES('s201101001','张向东','计算机学院','计算机科学与技术','1101','男','student')INSERTINTOstu_info(stu_id,stu_name,dept,major,class,sex,password)VALUES('s201101002','张祥丽','计算机学院','计算机科学与技术','1102','女','student')INSERTINTOstu_info(stu_id,stu_name,dept,major,class,sex,password)VALUES('s201101003','王芳','计算机学院','网络技术','1101','女','student')……/*插入学生信息*/INSERTINTOstu_info(stu_id,stu_name,dept,major,class,sex,password)VALUES('s201105025','钱多多','人文学院','心理学','1103','男','student')INSERTINTOteach_info(teach_id,teach_name,dept,title,sex,password)VALUES('t010000001','黎昕','计算机学院','教授','女','teacher')INSERTINTOteach_info(teach_id,teach_name,dept,title,sex,password)VALUES('t010000002','张洪涛','计算机学院','副教授','男','teacher')……/*插入教师信息*/INSERTINTOteach_info(teach_id,teach_name,dept,title,sex,password)VALUES('t050000012','王皓天','人文学院','讲师','男','teacher')INSERTINTOcour_info(cour_id,cour_name,teach_id,credit,total_num,cour_time,cour_place)VALUES('c010000001','C语言','t010000001','4','20','0315234','11303')INSERTINTOcour_info(cour_id,cour_name,teach_id,credit,total_num,cour_time,cour_place)VALUES('c010000002','计算机组成原理','t010000002','4','20','0315434','11304')INSERTINTOcour_info(cour_id,cour_name,teach_id,credit,total_num,cour_time,cour_place)VALUES('c010000003','计算机网络','t010000003','3','15','0312134','11305')……/*插入课程信息*/5.3索引的建立CREATEUNIQUEINDEXadminidONadmin_info(admin_id)CREATEUNIQUEINDEXstuidONstu_info(stu_id)CREATEUNIQUEINDEXteachidONteach_info(teach_id)CREATEUNIQUEINDEXcouridONcour_info(cour_id)CREATEUNIQUEINDEXscstucouridONsc(stu_id,cour_id)5.4视图的建立CREATEVIEWstu_infomationASSELECTstu_idAS'学生编号',stu_nameAS'学生姓名',deptAS'所在院系',PASSWORDAS'登录密码'FROMdbo.stu_infoCREATEVIEWteach_infomationASSELECTteach_idAS'学生编号',teach_nameAS'教师姓名',deptAS'所在院系',PASSWORDAS'登录密码'FROMteach_infoCREATEVIEWcour_infomationASSELECTcour_idAS'课程编号',cour_nameAS'课程名称',start_timeAS'选课开始时间',end_timeAS'选课结束时间'FROMcour_infoCREATEVIEWallcourASSELECT*FROMcour_infoCREATEVIEWstu_selASSELECTstu_info.stu_idAS'学生编号',stu_info.stu_nameAS'学生姓名',cour_info.cour_idAS'课程编号',cour_info.cour_nameAS'课程名称'scINNERJOINstu_infoONsc.stu_id=stu_info.stu_idINNERJOINcour_infoONsc.cour_id=cour_info.cour_idCREATEVIEWteach_courASSELECTteach_info.teach_idAS'教师编号',teach_info.teach_nameAS'教师姓名',cour_info.cour_idAS'课程编号',cour_info.cour_nameAS'课程名称',cour_info.selected_numAS'已选人数',cour_info.total_numAS'可选人数'dbo.cour_infoINNERJOINteach_infoONteach_info.teach_id=cour_info.teach_idINNERJOINscONsc.cour_id=cour_info.cour_id5.5存储过程的建立IFIFEXISTS(SELECTNAMEFROMsysobjectsWHEREname='set_time'ANDtype='p')DROPPROCEDUREset_timeCREATEPROCEDUREset_time@idornameVARCHAR(20),@startINT,@endINTASIFSUBSTRING(@idorname,1,1)='c'UPDATEdbo.cour_infoSETstart_time=@start,end_time=@endWHEREcour_id=@idornameELSEUPDATEdbo.cour_infoSETstart_time=@start,end_time=@endWHEREcour_name=@idornameSELECTcour_idAS'课程编号',cour_nameAS'课程名称',start_timeASAS'选课开始时间',end_timeAS'选课结束时间'FROMdbo.cour_infoWHEREcour_id=@idornameORcour_name=@idornameIFEXISTS(SELECTNAMEFROMsysobjectsWHEREname='select_cour'ANDtype='p')DROPPROCEDUREselect_courCREATEPROCEDUREselect_cour@stu_idornameVARCHAR(20),@cour_idornameVARCHAR(20)ASDECLARE@starttimeSMALLDATETIME,@endtimeSMALLDATETIME,@tempstuidVARCHAR(20),@tempcouridSET@starttime=WHENSUBSTRING(@cour_idorname,1,1)='c'cour_id=@cour_idorname)cour_id=@cour_idorname)cour_id=@cour_idorname)SET@tempstuid=CASEWHENSUBSTRING(@stu_idorname,1,1)='s'stu_id=@stu_idorname)INSERTINTOsc(cour_id,stu_id)SELECTstu_info.stu_idAS'学生编号',stu_info.stu_nameAS'学生姓名',dbo.cour_info.cour_idEND'课程编号',dbo.cour_info.cour_nameAS'课程名称','选课成功!'AS'状态'dbo.scINNERJOINdbo.cour_infoONsc.cour_id=cour_info.cour_idINNERJOINdbo.stu_infoONsc.stu_id=dbo.stu_info.stu_idWHEREsc.cour_id=THEN(SELECTcour_idFROMdbo.cour_infoWHEREcour_name=@cour_idorname)ANDsc.stu_id=CASEWHENSUBSTRING(@stu_idorname,1,1)='s'unameIFIFEXISTS(SELECTNAMEFROMsysobjectsWHEREname='delete_cour'ANDtype='p')DROPPROCEDUREdelete_courCREATEPROCEDUREdelete_cour@stu_idornameVARCHAR(20),@cour_idornameVARCHAR(20)ASDECLARE@stuidVARCHAR(20),@couridVARCHAR(20)SET@stuid=WHENSUBSTRING(@stu_idorname,1,1)='s',1,1)='c',1,1)!='c')WHEREcour_id=@couridANDstu_id=@stuidIFIFEXISTS(SELECTNAMEFROMsysobjectsWHEREname='stu_grade'ANDtype='p')DROPPROCEDUREstu_gradeCREATEPROCEDUREstu_grade@stu_idornameVARCHAR(20)ASDECLARE@stuidVARCHAR(20),@totalnumINT,@avgINT,@passnumINTSET@stuid=THEN(SELECTstu_idFROMdbo.stu_infoWHEREstu_name=SELECTstu_info.stu_idAS'学生编号',dbo.stu_info.stu_nameAS'学生姓名',dbo.cour_info.cour_idAS'课程编号',dbo.cour_info.cour_nameAS'课程名称',dbo.cour_info.creditAS'学分',dbo.sc.gradeAS'成绩'scINNERJOINdbo.stu_infoONdbo.stu_info.stu_id=dbo.sc.stu_idINNERJOINdbo.cour_infoONdbo.cour_info.cour_id=dbo.sc.cour_idWHEREdbo.sc.stu_id=@stuidSELECTAVG(grade)AS'平均成绩'FROMdbo.scINNERJOINdbo.stu_infoONdbo.stu_info.stu_id=dbo.sc.stu_idINNERJOINdbo.cour_infoONdbo.cour_info.cour_id=dbo.sc.cour_idWHEREdbo.sc.stu_id=@stuidGROUPBYdbo.stu_info.stu_idSELECTCOUNT(grade)AS'通过门数'FROMdbo.scINNERJOINdbo.stu_infoONdbo.stu_info.stu_id=dbo.sc.stu_idINNERJOINdbo.cour_infoONdbo.cour_info.cour_id=dbo.sc.cour_idWHEREdbo.sc.stu_id=@stuidANDgrade!<60GROUPGROUPBYdbo.stu_info.stu_idSELECTCOUNT(cour_id)AS'选课总数'FROMdbo.scWHEREstu_id=@stuid的IFEXISTS(SELECTNAMEFROMsysobjectsWHEREname='getcourinfo'ANDtype='p')DROPPROCEDUREgetcourinfoCREATEPROCEDUREgetcourinfo@couridornameVARCHAR(20)ASDECLARE@couridVARCHAR(20),@timeVARCHAR(20),@placeSET@courid=THEN(SELECTdbo.cour_info.cour_idFROMdbo.cour_infoWHERESET@time=(SELECTdbo.cour_info.cour_timeFROMdbo.cour_infoWHEREcour_id=@courid)SET@place=(SELECTdbo.cour_info.cour_placeFROMdbo.cour_infoWHEREcour_id=@courid)SELECTdbo.cour_info.cour_idAS'课程编号',dbo.cour_info.cour_nameAS'课程名称','第AS'上课时间',SUBSTRING(@place,1,2)+'栋教学楼'+SUBSTRING(@place,3,3)+'教室'AS'上课地点'FROMdbo.cour_infoWHEREWHEREcour_id=@couridIFEXISTS(SELECTNAMEFROMsysobjectsWHEREname='set_grade'ANDtype='p')DROPPROCEDUREset_gradeCREATEPROCEDUREset_grade@stuidornameVARCHAR(20),@couridornameVARCHAR(20),@gradeINTASDECLARE@stuidVARCHAR(20),@couridVARCHAR(20)SET@stuid=CASEWHENSUBSTRING(@stuidorname,1,1)='s'THEN@stuidornameWHENSUBSTRING(@stuidorname,1,1)!='s'THEN(SELECTstu_idFROMdbo.stu_infoWHEREstu_name=@stuidorname)SET@courid=CASEWHENSUBSTRING(@couridorname,1,1)='c'THEN@couridornameWHENSUBSTRING(@couridorname,1,1)!='c'THEN(SELECTcour_idFROMdbo.cour_infoWHEREcour_name=@couridorname)UPDATEdbo.scSETgrade=@gradeWHEREstu_id=@stuidANDcour_id=@couridSELECTdbo.stu_info.stu_idAS'学生编号',dbo.stu_info.stu_nameAS'学生姓名',dbo.cour_info.cour_idAS'课程编号',dbo.cour_info.cour_nameAS'课程名称',gradeAS'分数','修改成功！'AS'状态'FROMdbo.scINNERJOINdbo.stu_infoONdbo.sc.stu_id=dbo.stu_info.stu_idINNERINNERJOINdbo.cour_infoONdbo.cour_info.cour_id=dbo.sc.cour_idWHEREdbo.sc.stu_id=@stuidANDdbo.sc.cour_id=@courid相IFIFEXISTS(SELECTNAMEFROMsysobjectsWHEREname='teach_stat'ANDtype='p')DROPPROCEDUREteach_statCREATEPROCEDUREteach_stat@teachidornameVARCHAR(20)ASSET@teachid=THEN(SELECTteach_idFROMdbo.teach_infoWHERESELECTstu_idAS'学生编号',dbo.cour_info.cour_id号',cour_nameAS'课程名称',creditAS'学分',gradeAS'课程编AS'成绩',selected_numAS'已选人数',total_numFROMdbo.scINNERJOINdbo.cour_infodbo.cour_info.cour_id=dbo.sc.cour_idWHEREdbo.cour_info.teach_id=@teachidAS'可选人数'程5.6触发器的建立CREATECREATETRIGGERtr_delete_courONdbo.sceSELECT@sname=dbo.stu_info.stu_nameFROMdbo.stu_infoEstuidsidSELECT@cname=dbo.cour_info.cour_nameFROMdbo.cour_infoCREATECREATETRIGGERrightgradeONdbo.scASBEGINCREATECREATETRIGGERselectednumONdbo.scUPDATEdbo.cour_infodbo.stu_dbo