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

XX大学XXXX学院课程设计 课题题目：教务管理系统 学部：XX学部专业：XXXX班级：X班 学号： 姓名XXX 指导老师：XXX2011年6月23日

成绩： 一系统功能分析与设计1.需求分析

制作教务管理信息系统的目的是为学校学生提供一个方便创立教师、学生信息的工具，实现能够方便、快捷的查询信息。管理信息系统是进行信息的采集,存储,修改,维护,备份和使用的系统,它是随着管理科学和技术科学的开展而形成的.教务管理信息系统是一个教育单位不可缺少的局部,它的内容对于学校的决策者和管理者来说都至关重要,所以教务管理信息系统应该能够为用户提供充足的信息和快捷的查询手段,对学生来说可以轻松的查阅自己在校的信息以及选课等。

2.功能模块图分析说明2.1总体功能模块图教务信息系统教务信息系统学生学籍管理教学信息管理教师信息管理教材信息管理查询学生信息添加学生信息删除学生信息更新学生信息查询教学信息添加教学信息删除教学信息更新教师信息查询教师信息添加教师信息删除教师信息更新教师信息更新教材信息删除教材信息添加教材信息查询教材信息2.2各个模块分析说明学生学籍管理该模块主要实现学生信息的根本管理，便于管理员添加学生、删除学生、查询学生等管理学生学籍的功能。教学管理该模块实现对课程的根本管理。课程管理主要实现对课程信息的查询，添加课程，替学生选课等功能。教师管理该模块实现教师情况的管理，如：对教师的信息查询，添加，删除，修改。教材管理该模块主要实现对教材的查询，添加，修改管理。二、数据库设计1.全局E-R图选课选课nppm选用教材教材发行码页码教材编号教材名称出版社主编l单价专业编码姓名性别出生年月入学时间籍贯学生学号教师教师名性别出生年月部门编号职称籍贯m教师编号课程学时学分课程名考试／考查课程号2.关系模式student(studentnum,studentname,ssecret,sex,stuyear,stumon,studay,inyear,inmon,inday,specialnum,phone,city);teacher(teachernum,teachername,ssecret,sex,teayear,teamon,teaday,classnum,position,phone,city);book(booknum,bookname,edition,number,editor,rate,pagenum,);class(classnum,classname,exam,knowledge,credits);stc(classnum,studentnum,teachernum);boocla(classnum,booknum);3.数据字典

3.1student〔学生表〕：学号studentnumvarchar(10)notnullprimarykey姓名studentnamevarchar(10)notnull密码ssecretvarchar(10)notnull性别sexvarchar(10)nullcheck(sexin(‘男’,’女’))出生年stuyearvarchar(10)null出生月stumonvarchar(10)null出生日studayvarchar(10)null入学年inyearvarchar(10)null入学月inmonvarchar(10)null入学日indayvarchar(10)null专业编码specialnumvarchar(10)notnullphonevarchar(10)null籍贯cityvarchar(10)null3.2 teacher〔教师表〕：教师编号teachernumvarchar(10)notnullprimarykey教师名teachernamevarchar(10)notnull密码ssecretvarchar(10)notnull性别sexvarchar(10)nullcheck(sexin('男','女'))出生年teayearvarchar(10)null出生月teamonvarchar(4)null出生日teadayvarchar(4)null部门编号classnumvarchar(10)notnull职称positionvarchar(10)notnullphonevarchar(10)null籍贯cityvarchar(10)null3.3book〔教材表〕：

教材编号booknumvarchar(10)notnullprimarykey教材名称booknamevarchar(10)notnull出版社editionvarchar(10)null发行码numbervarchar(10)notnull主编editorvarchar(10)null单价ratevarchar(10)notnull页码pagenumvarchar(10)null3.4class〔课程表〕：学时knowledgevarchar(10)null学分creditsvarchar(10)null课程号classnumvarchar(10)notnullprimarykey课程名classnamevarchar(10)notnull考试方式examvarchar(10)nullcheck(examin('考试','考查'))3.5stc〔选课表〕：课序号coursenumvarchar(10)notnullprimarykey课程号classnumvarchar(10)notnullforeignkey(classnum)referencesclass(classnum)学号studentnumvarchar(10)notnullforeignkey(studentnum)referencesstudent(studentnum)教师编号teachernumvarchar(10)notnullforeignkey(teachernum)referencesteacher(teachernum)3.6ookcla〔选教材表〕：课程号classnumvarchar(10)notnullprimarykey教材编号booknumvarchar(10)notnullforeignkey(booknum)referencesbook(booknum)foreignkey(coursenum)referencescourse(coursenum)三系统实现管理员登陆如图1所示，可对学生学籍、教师、教学、教材进行管理图1管理员登陆后的界面如图2所示，初始页面为“学生学籍管理”页面，点击“教学管理”、“教学管理”、“教材管理”链接进入相应的页面图2在“学生学籍管理”页面，输入需要查找的相应信息，点击查找按钮即可对此进行搜索，并列出符合条件的学生列表，如图3所示，性别选择女，点击查找按钮，列出性别为女的学生图3点击查找按钮后面的“添加”链接，跳转到“添加学生”页面，界面如图4所示图4填好需要添加的学生的相应信息后，点击提交按钮，弹出“添加成功！”，说明该学生信息已成功添加，如图5所示图5在返回的“学生学籍管理”主页面，输入该学生的学号0838010301进行查找，结果列出该生的全部信息，如图6所示图6点击该学生信息后的“修改”链接，跳转到“学生信息修改”页面，可对除学号外所有信息进行修改，如图7所示图7修改好相应信息后，点击“修改”按钮，弹出“修改成功！”提示框，说明修改已成功，如图8所示图8在“学生学籍管理”主页面，输入该学生的学号0838010301进行查找，结果列出该生的全部信息，如图9所示，发现已有修改图9点击“修改”链接后的删除链接，可对学生的信息进行删除处理，弹出“删除成功！”提示框，说明已删除成功，如图10所示图10输入学生的学号和密码后可登陆到“学生信息查询”页面，如图11所示图11如果学号和密码正确，那么再弹出“登陆成功！”提示框，如图12所示图12然后跳转到“学生资料查询”页面，如图13所示图13点击“查询课程”链接后，跳转到“学生个人课表查询”页面，如图14所示图14点击“修改”按钮跳转到学生资料修改页面，可对密码、、籍贯进行修改，如图15所示图15点击“修改”，弹出“修改成功！”提示框，说明修改资料成功，如图16所示图16四小结全组组员在设计数据库前期规划和设计上都有很突出的奉献，画E-R图，转逻辑关系，设计数据字典，几乎是一气呵成。五开发工具和参考文献1.开发工具AdobeDreamweaverCS3AdobePhotoshopCS5ExtendedMicrosoftSQLServer2000PersonalSP42.参考文献2.1刘涛.动态网站开发：ASPSQLServerDreamweaver.北京：高等教育出版社，20082.2王珊，萨师煊.数据库系统概论〔第4版〕.北京：高等教育出版社，2006〔2010重印〕2.32.42.5刘浩增，张坤，宋强等.ASP动态网站开发教程.北京：清华大学出版社，20092.6李晓黎，张巍.ASP+SQLServer网络应用系统开发与实例.北京：人民邮电出版社，2004〔2005重印〕六源程序清单1.建表代码createtableteacher(teachernum varchar(10) notnull primarykey ,teachername varchar(10) notnull ,ssecret varchar(10) notnull ,sex varchar(10) null check(sexin('男','女')) ,teayear varchar(10) null ,teamon varchar(4) null ,teaday varchar(4) null ,classnum varchar(10) notnull ,position varchar(10) notnull ,phone varchar(11) null ,city varchar(20) null ,);createtablebook(booknum varchar(10) notnull primarykey ,bookname varchar(20) notnull ,edition varchar(20) null ,number varchar(10) notnull ,editor varchar(10) null ,rate varchar(10) notnull ,pagenum varchar(10) null ,);createtableclass(knowledge varchar(10) null ,credits varchar(10) null ,classnum varchar(10) notnull primarykey ,classname varchar(10) notnull ,exam varchar(10) null check(examin('考试','考查')) ,);createtablestudent(studentnum varchar(10) notnull primarykey ,studentname varchar(10) notnull ,ssecret varchar(10) notnull ,sex varchar(10) null check(sexin('男','女')) ,stuyear varchar(10) null ,stumon varchar(10) null ,studay varchar(10) null ,inyear varchar(10) null ,inmon varchar(10) null ,inday varchar(10) null ,specialnum varchar(10) notnull ,phone varchar(11) null ,city varchar(20) null ,);createtablestc(classnum varchar(10) notnull ,studentnum varchar(10) notnull ,teachernum varchar(10) notnull , primarykey(studentnum,teachernum,classnum), foreignkey(studentnum)referencesstudent(studentnum) , foreignkey(teachernum)referencesteacher(teachernum) , foreignkey(classnum)referencesclass(classnum) ,);createtableboocla(classnum varchar(10) notnull ,booknum varchar(10) notnull , primarykey(classnum,booknum) , foreignkey(booknum)referencesbook(booknum) , foreignkey(classnum)referencesclass(classnum) ,);2.网页关键代码2.1链接数据库核心代码〔Connections/conn.asp〕<%DimMM_conn_STRINGMM_conn_STRING="Provider=SQLOLEDB;datasource=(local);initialcatalog=teachers;uid=sa;pwd=;"%>2.2网页通用调用方法<!--#includefile="/Connections/conn.asp"-->2.3管理员查询和删除页面关键代码〔index_stu.asp〕<%Setconn=Server.Createobject("ADODB.Connection")conn.openMM_conn_STRING%><% del=request.QueryString("studentnum")if(del<>"")then conn.execute("deletefromstudentwherestudentnum='"&del&"'") Response.Write("<script>alert('删除成功！');window.location.href='index_stu.asp';</script>") endif studentnum=request.form("studentnum") studentname=request.form("studentname") ssecret=request.form("ssecret") sex=request.form("sex") stuyear=request.form("stuyear") stumon=request.form("stumon") studay=request.form("studay") inyear=request.form("inyear") inmon=request.form("inmon") inday=request.form("inday") specialnum=request.form("specialnum") phone=request.form("phone")dimrssetrs=server.CreateObject("adodb.recordset")sql="select*fromstudentwherestudentnumlike('"&studentnum&"%')andstudentnamelike('"&studentname&"%')andstudentnumlike('"&studentnum&"%')andssecretlike('"&ssecret&"%')andsexlike('"&sex&"%')andstuyearlike('"&stuyear&"%')andstumonlike('"&stumon&"%')andstudaylike('"&studay&"%')andinyearlike('"&inyear&"%')andinmonlike('"&inmon&"%')andindaylike('"&inday&"%')andspecialnumlike('"&specialnum&"%')andphonelike('"&phone&"%')"rs.opensql,conn,1,3 ifnotrs.eofthen constmaxperpage=10'定义每一页显示的数据记录的常量 dimcurrentpage'定义当前页的变量 rs.pagesize=maxperpage currentpage=request("page") ifcurrentpage=""ornotIsnumeric(currentpage)then currentpage=1 elseifcurrentpage<1then c1urrentpage=1 else currentpage=clng(currentpage) ifcurrentpage>rs.pagecountthen currentpage=rs.pagecount endif endif '如果变量currentpage的数据类型不是数值型 '就1赋给变量currentpage ifnotisnumeric(currentpage)then currentpage=1 endif dimtotalput,n'定义变量 totalput=rs.recordcount iftotalputmodmaxperpage=0then n=totalput\maxperpage else n=totalput\maxperpage+1 endif ifn=0then n=1 endif rs.move(current1)*maxperpage i=0 dowhilei<maxperpageandnotrs.eof i=i+1 %><% rs.movenext loop endif rs.close setrs=nothing%>2.4添加页面关键代码(student_add.asp)<%DimMM_editActionMM_editAction=CStr(Request.ServerVariables("SCRIPT_NAME"))If(Request.QueryString<>"")ThenMM_editAction=MM_editAction&"?"&Server.HTMLEncode(Request.QueryString)EndIf'booleantoabortrecordeditDimMM_abortEditMM_abortEdit=false%><%If(CStr(Request("MM_insert"))="form1")ThenIf(NotMM_abortEdit)Then'executetheinsertDimMM_editCmdSetMM_editCmd=Server.CreateObject("ADODB.Command")MM_editCmd.ActiveConnection=MM_conn_STRINGMM_editCmd.CommandText="INSERTINTOdbo.student(studentnum,studentname,ssecret,sex,stuyear,stumon,studay,inyear,inmon,inday,specialnum,phone,city)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)"MM_editCmd.Prepared=trueMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param1",201,1,10,Request.Form("studentnum"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param2",201,1,10,Request.Form("studentname"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param3",201,1,10,Request.Form("ssecret"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param4",201,1,10,Request.Form("sex"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param5",201,1,10,Request.Form("stuyear"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param6",201,1,10,Request.Form("stumon"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param7",201,1,10,Request.Form("studay"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param8",201,1,10,Request.Form("inyear"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param9",201,1,10,Request.Form("inmon"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param10",201,1,10,Request.Form("inday"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param11",201,1,10,Request.Form("specialnum"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param12",201,1,10,Request.Form("phone"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param13",201,1,10,Request.Form("city"))'adLongVarCharMM_editCmd.Execute Response.Write("<script>alert('添加成功！');window.location.href='index_stu.asp';</script>") Response.endEndIfEndIf%><%DimrcDimrc_cmdDimrc_numRowsSetrc_cmd=Server.CreateObject("ADODB.Command")rc_cmd.ActiveConnection=MM_conn_STRINGrc_cmd.CommandText="SELECT*FROMdbo.student"rc_cmd.Prepared=trueSetrc=rc_cmd.Executerc_numRows=0%>2.5管理员修改页面关键代码(modify_stu.asp)<%DimMM_editActionMM_editAction=CStr(Request.ServerVariables("SCRIPT_NAME"))If(Request.QueryString<>"")ThenMM_editAction=MM_editAction&"?"&Server.HTMLEncode(Request.QueryString)EndIf'booleantoabortrecordeditDimMM_abortEditMM_abortEdit=false%><%If(CStr(Request("MM_update"))="form1")ThenIf(NotMM_abortEdit)Then'executetheupdateDimMM_editCmdSetMM_editCmd=Server.CreateObject("ADODB.Command")MM_editCmd.ActiveConnection=MM_conn_STRINGMM_editCmd.CommandText="UPDATEdbo.studentSETstudentname=?,ssecret=?,sex=?,stuyear=?,stumon=?,studay=?,inyear=?,inmon=?,inday=?,specialnum=?,phone=?,city=?WHEREstudentnum=?"MM_editCmd.Prepared=trueMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param1",201,1,10,Request.Form("studentname"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param2",201,1,10,Request.Form("ssecret"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param3",201,1,10,Request.Form("sex"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param4",201,1,10,Request.Form("stuyear"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param5",201,1,10,Request.Form("stumon"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param6",201,1,10,Request.Form("studay"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param7",201,1,10,Request.Form("inyear"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param8",201,1,10,Request.Form("inmon"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param9",201,1,10,Request.Form("inday"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param10",201,1,10,Request.Form("specialnum"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param11",201,1,10,Request.Form("phone"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param12",201,1,10,Request.Form("city"))'adLongVarCharMM_editCmd.Parameters.AppendMM_editCmd.CreateParameter("param13",200,1,10,Request.Form("MM_recordId"))'adVarCharMM_editCmd.Execute Response.Write("<script>alert('修改成功！');window.location.href='index_stu.asp';</script>") Response.endEndIfEndIf%><%DimRecordset1__MMColParamRecordset1__MMColParam="1"If(Request.QueryString("studentnum")<>"")ThenRecordset1__MMColParam=Request.QueryString("studentnum")EndIf%><%DimRecordset1DimRecordset1_cmdDimRecordset1_numRowsSetRecordset1_cmd=Server.CreateObject("ADODB.Command")Recordset1_cmd.ActiveConnection=MM_conn_STRINGRecordset1_cmd.CommandText="SELECT*FROMdbo.studentWHEREstudentnum=?"Recordset1_cmd.Prepared=trueRecordset1_cmd.Parameters.AppendRecordset1_cmd.CreateParameter("param1",200,1,10,Recordset1__MMColParam)'adVarCharSetRecordset1=Recordset1_cmd.ExecuteRecordset1_numRows=0%>2.6学生查询信息页面关键代码〔student_index.asp〕<%'***Logoutthecurrentuser.MM_Logout=CStr(Request.ServerVariables("URL"))&"?MM_Logoutnow=1"If(CStr(Request("MM_Logoutnow"))="1")ThenSession.Contents.Remove("MM_Username")Session.Contents.Remove("MM_UserAuthorization")MM_logoutRedirectPage="index.asp"'redirectwithURLparameters(removethe"MM_Logoutnow"queryparam).if(MM_logoutRedirectPage="")ThenMM_logoutRedirectPage=CStr(Request.ServerVariables("URL"))If(InStr(1,UC_redirectPage,"?",vbTextCompare)=0AndRequest.QueryString<>"")ThenMM_newQS="?"ForEachItemInRequest.QueryStringIf(Item<>"MM_Logoutnow")ThenIf(Len(MM_newQS)>1)ThenMM_newQS=MM_newQS&"&"MM_newQS=MM_newQS&Item&"="&Server.URLencode(Request.QueryString(Item))EndIfNextif(Len(MM_newQS)>1)ThenMM_logoutRedirectPage=MM_logoutRedirectPage&MM_newQSEndIfResponse.Redirect(MM_logoutRedirectPage)EndIf%><!--#includefile="Connections/conn.asp"--><%DimRecordset1__MMColParamRecordset1__MMColParam="1"If(session("username")<>"")ThenRecordset1__MMColParam=session("username")EndIf%><%DimRecordset1DimRecordset1_cmdDimRecordset1_numRowsSetRecordset1_cmd=Server.CreateObject("ADODB.Command")Recordset1_cmd.ActiveConnection=MM_conn_STRINGRecordset1_cmd.CommandText="SELECT*FROMdbo.studentWHEREstudentnum=?"Recordset1_cmd.Prepared=trueRecordset1_cmd.Parameters.AppendRecordset1_cmd.CreateParameter("param1",200,1,10,Recordset1__MMColParam)'adVarCharSetRecordset1=Recordset1_cmd.ExecuteRecordset1_numRows=0%><%DimMM_paramName%><%'***GoToRecordandMoveToRecord:createstringsformaintainingURLandFormparametersDimMM_keepNoneDimMM_keepURLDimMM_keepFormDimMM_keepBothDimMM_removeListDimMM_itemDimMM_nextItem'createthelistofparameterswhichshouldnotbemaintainedMM_removeList="&index="If(MM_paramName<>"")ThenMM_removeList=MM_removeList&"&"&MM_paramName&"="EndIfMM_keepURL=""MM_keepForm=""MM_keepBoth=""MM_keepNone=""'addtheURLparameterstotheMM_keepURLstringForEachMM_itemInRequest.QueryStringMM_nextItem="&"&MM_item&"="If(InStr(1,MM_removeList,MM_nextItem,1)=0)ThenMM_keepURL=MM_keepURL&MM_nextItem&Server.URLencode(Request.QueryString(MM_item))EndIfNext'addtheFormvariablestotheMM_keepFormstringForEachMM_itemInRequest.FormMM_nextItem="&"&MM_item&"="If(InStr(1,MM_removeList,MM_nextItem,1)=0)ThenMM_keepForm=MM_keepForm&MM_nextItem&Server.URLencode(Request.Form(MM_item))EndIfNext'createtheForm+URLstringandremovetheintial'&'fromeachofthestringsMM_keepBoth=MM_keepURL&MM_keepFormIf(MM_keepBoth<>"")ThenMM_keepBoth=Right(MM_keepBoth,Len(MM_keepBoth)-1)EndIfIf(MM_keepURL<>"")ThenMM_keepURL=Right(MM_keepURL,Len(MM_keepURL)-1)EndIfIf(MM_keepForm<>"")ThenMM_keepForm=Right(MM_keepForm,Len(MM_keepForm)-1)EndIf'autilityfunctionusedforaddingadditionalparameterstothesestringsFunctionMM_joinChar(firstItem)If(firstItem<>"")ThenMM_joinChar="&"ElseMM_joinChar=""EndIfEndFunction%>2.7学生查询课表页面关键代码〔student_class.asp〕<%Setrs=conn.execute("Select*fromclasswhereclassnum=(Selectclassnumfromstcwherestudentnum='"&session("username")&"')") constmaxperpage=20'定义每一页显示的数据记录的常量 dimcurrentpage'定义当前页的变量 currentpage=1 rs.pagesize=maxperpage rs.move(current1)*maxperpage dowhilei<maxperpag