学生成绩管理系统数据库设计文档---(全).doc
“学生成绩管理"数据库设计文档0、前言(一些必要的说明。)0.1 数据库说明数据库名：PXSCJ 逻辑名称:学生成绩数据库数据文件：PXSCJ.mdf日志文件:PXSCJ_Log登录名：admin,密码:1234560.2表命名说明Cjb：成绩表,保存选课信息Cxb：查询表，记录boolean值对应信息，1代表男,0代表女。Kcb:课程表。Tjb：统计表,统计成绩段分布。Xsb：学生表.Yhb：用户表,保存系统用户信息。Jsb： 教师表。Skb：授课表,记录授课信息.0.3 系统功能模块图1、 需求分析阶段说明:学生成绩管理系统需要实现以下功能:一个学生可以选修多门课程,一门课程可以由多个学生选修，学生选修一门课会有一个成绩.一个教师可以教授多个班级，一个教师也可以教授多门课程，一个班级有多个学生，一门课程也可以由多个老师来上，一个老师给一个班级上一门课有确定的时间和地点.不同的用户根据身份不同拥有不同的权限。（1） 数据流图老师-成绩管理,学生信息管理，权限管理-学生成绩管理系统成绩查询-学生（要求：用visio实现第一层数据流图，第二层数据流图，第三层数据流图）p121第一层数据流图第二层数据流图第三层数据流图（略）（2） 数据字典（每个实体的详细说明）数据库编号：pxscj基表编号：f1基表英文名称:yhb基表中文名称:用户表字段编号英文字段名中文字段名字段类型备注1Yhbh用户编号Char(6）2yhm用户名Char(8）3sf身份Varcha（20）4mm密码Char（6)5bz备注Varcha(50）说明：数据库编号：pxscj基表编号：f2基表英文名称:cjb基表中文名称：成绩表字段编号英文字段名中文字段名字段类型备注1Xh学号Char（6)2Kch课程号Char(3）3Cj成绩Int说明：xh参照学生表的xh；kch参照课程表的kch；cj介于0100之间。数据库编号：pxscj基表编号：f3基表英文名称：kcb基表中文名称:课程表字段编号英文字段名中文字段名字段类型备注1Xh学号Char（6）2XM姓名Char（8）3XB性别Bit4CSSJ出生时间Datetime5Bj班级Varchar（50）6Zy专业Char(12）7ZXH总学分Int8Lxfs联系方式Varchar(50)9zp照片Varbinary（MAX)10BZVarchar(500)说明：xh参照学生表的xh；kch参照课程表的kch；cj介于0-100之间。xb参照查询表的xb数据库编号：pxscj基表编号:f4基表英文名称：jsb基表中文名称：教师表字段编号英文字段名中文字段名字段类型备注1Jsbh教师编号Char(6)2Jsxm教师姓名Char（8)3zy专业Varchar（30）4Fy分院Varchar（30）5Zc职称Varchar(20）6Zc职务Varchar（20）7BzVarchar（50）说明：xh参照学生表的xh;kch参照课程表的kch；cj介于0100之间。数据库编号:pxscj基表编号：f5基表英文名称：kcb基表中文名称：课程表字段编号英文字段名中文字段名字段类型备注1KCH课程号Char（3）2KCM课程名Char(16）3Cj成绩Int说明：xh参照学生表的xh；kch参照课程表的kch;cj介于0-100之间.数据库编号：pxscj基表编号:f6基表英文名称：cjb基表中文名称：课程表字段编号英文字段名中文字段名字段类型备注1Xh学号Char（6）2Kch课程号Char（3）3KKXQTinyint4XS学生tinyint5XF学分tinyint说明:xh参照学生表的xh;kch参照课程表的kch；数据库编号：pxscj基表编号：f7基表英文名称:skb基表中文名称:授课表字段编号英文字段名中文字段名字段类型备注1Jsbh教师编号Char(6)2Kch课程号Char（3）3Bj班级Varchar（50）4Sj时间Varchar（50)5dd地点Varchar（50）说明：jsbh参照教师表的jsbh;kch参照课程表的kch；数据库编号:pxscj基表编号：f8基表英文名称：CXB基表中文名称：查询表字段编号英文字段名中文字段名字段类型备注1XB性别Bit2XBM性别名Char（4）说明：2、 概念设计阶段（1） 分ER图（两个分ER图，1）学生和课程,2)教师，课程,班级）（2） 总ER图(由分ER图画出总ER图)3、 逻辑设计阶段（1） 表关系图（看是否可以画出)（2） 表结构图Xsb结构Kcb结构Cjb结构Yhb结构Jsb结构Skb结构Tjb结构Cxb结构（3） 表优化（判断每个关系是否达到3NF要求，如果没有达到，则继续规范）按照(1nf-2nf>3nf进行判断和优化)Cjb(xh，kch,cj）码：(xh，kch)非主属性:cj因为cj完全依赖于（xh，kch),所以属于2NF因为不存在传递函数依赖，所以属于3NFCxb(xb,xbm)码：(xbm）非主属性:xb因为xb完全依赖于（kch)，所以属于2NF因为不存在传递函数依赖,所以属于3NFJsb（jsbh，jsxm，zy，fy，zc，zw，bz）码：(jsbh）非主属性:jsxm，zy，fy,zc,zw，bz因为jsxm，zy，fy，zc，zw，bz完全依赖于(kch)，所以属于2NF因为不存在传递函数依赖，所以属于3NFKcb（kch，kcm,kkxq,xs，xf）码：（kch）非主属性:kcm，kkxq，xs,xf因为kcm，kkxq，xs,xf完全依赖于（kch），所以属于2NF因为不存在传递函数依赖，所以属于3NFSkb（jsbh，kch，bj，sj，dd）码：（jsbh，kch，bj）非主属性：sj，dd因为sj,dd完全依赖于（jsbh,kch,bj)，所以属于2NF因为不存在传递函数依赖，所以属于3NFTjb（kch，rs1，rs2，rs3，rs4，rs5）码：（kch）非主属性：rs1,rs2，rs3,rs4，rs5因为rs1，rs2，rs3，rs4，rs5完全依赖于（kch)，所以属于2NF因为不存在传递函数依赖，所以属于3NFXsb(xh，xm,xb,cssj，bj,zy，zxf,bz，lxfs，zp)码：(xh）非主属性：xm，xb,cssj，bj，zy，zxf，bz，lxfs,zp因为xm，xb,cssj，bj，zy,zxf，bz,lxfs，zp完全依赖于（xh)，所以属于2NF因为不存在传递函数依赖，所以属于3NFYhb(yhbh，yhm,mm,bz）码:(yhbh）非主属性：yhm,mm,bz因为yhm，mm，bz完全依赖于（yhbh)，所以属于2NF因为不存在传递函数依赖,所以属于3NF4、物理设计 选择合适的DBMS（要求用sql server 2008）5、实施(把sql语句贴在下面)（1)创建数据库（把sql语句贴在下面）createdatabasepxscj1on（name=pxscj1_data,filename='f：pxscj1_data。mdf,size=3mb，filegrowth=10）logon（name='pxscj1_log,filename='f:pxscj1_log。ldf，size=4mb，maxsize=6mb，filegrowth=1mb）（2)创建表(把sql语句贴在下面)createtablexsb(xhchar(6)notnullprimarykey,xmchar（8)notnull,xbbit，cssjdatetime,bjvarchar(50）notnull,zychar（12），zxfint，bzvarchar(500)，lxfsvarchar（50）,zpvarbinary（max）createtablekcb（kchchar(3)notnullprimarykey，kcmchar（16）notnull，kkxqtinyint,xstinyint，xftinyint)createtablecjb（xhchar（6)，kchchar（6）,cjint，constraintc1primarykey（xh，kch)）createtableyhb(yhbhchar（6)primarykeynotnull,yhmchar(8），sfvarchar(20），mmchar(6)，bzvarchar(5）)createtablejsb(jsbhchar（6）notnullprimarykey，jsxmchar（8），zyvarchar(30),fyvarchar（30），zcvarchar（20),zwvarchar(20)，bzvarchar（50）createtableskb(jsbhchar（6)notnull，kchchar（3）notnull，bjvarchar(50)notnull，sjvarchar（20）,ddvarchar(50），constraintc2primarykey(jsbh,kch，bj)）createtabletjb(kchchar(3）primarykeynotnull，rs1int,rs2int,rs3int，rs4int，rs5int）createtablecxb（xbbitnotnull，xbmchar(4)notnullprimarykey）（3)数据库完整性2）视图学生选课情况视图(学号，姓名，课程号，课程名，成绩,学分，总学分，教师)createviewxs_xk_viewasselectxsb。XH，XM，kcb.KCH,kcm，CJ,xf，zxf,jsb.jsbh,jsxmfromxsb,KCB，CJB,jsb，skbwherexsb。XH=cjb。XHandkcb。KCH=cjb。KCHandcjb。KCH=skb.kchandskb。jsbh=jsb。jsbhselect*fromxs_xk_view3）自定义数据库类型学号，课程号可以考虑用自定义的数据类型.sp_addtypexh，'char(6)sP_addtypekch'，char（3）'4)默认值对象Xsb：性别默认为1，总学分默认为0.Kcb：学生人数默认为40，学分默认为2。Jsb：zy计算机，fy信息,zc讲师,zw无。Yhb:mm123456Xsb：性别默认为1，altertablexsbaddconstraintb1DEFAULT 1 forxb总学分默认为0.altertablexsbaddconstraintb2DEFAULT 0 forzxfKcb：学生人数默认为40，altertablekcbaddconstraintb3DEFAULT 40 forxs学分默认为2.altertablekcbaddconstraintb4DEFAULT 2 forxfJsb：zy计算机，altertablejsbaddconstraintb5DEFAULT计算机forzyfy信息,altertablejsbaddconstraintb6DEFAULT信息forfyzc讲师，altertablejsbaddconstraintb7DEFAULT讲师forzczw无。altertablejsbaddconstraintb8DEFAULT无forzwYhb：mm123456altertableyhbaddconstraintb9DEFAULT'123456'formm5)规则和check约束Xsb：zxf范围在0160之间。Kcb：kkxq范围在18之间，xf在1-15之间Cjb:cj范围在0100之间Xsb:zxf在0-160之间.altertablexsbaddconstraintpk_1check （zxfbetween 0 and 160）Kcb:kkxq在18之间，xf在115之间altertablekcbaddconstraintpk_2check （kkxqbetween 1 and 8)altertablekcbaddconstraintpk_3check （xfbetween 1 and 15)Cjb:cj在0-100之间altertablecjbaddconstraintpk_4check （cjbetween 0 and 100）6)参照关系cjb（xh）参照xsb（xh）cjb（kch)参照kcb(kch)yhb(yhbh）参照xsb（xh)和jsb（jsbh)必须用触发器实现skb（jsbh）参照jsb（jsbh)skb（bj)参照xsb（bj)skb（kch）参照kcb（kch）cjb（xh）-xsb（xh）altertablecjbaddconstraintfk_1foreignkey (xh)referencesxsb（xh)cjb(kch）-kcb(kch)altertablecjbaddconstraintfk_2foreignkey (kch)referenceskcb(kch）skb(jsbh）-jsb(jsbh）altertableskbaddconstraintfk_4foreignkey （jsbh）referencesjsb(jsbh)skb（kch）kcb（kch）altertableskbaddconstraintfk_6foreignkey （kch)referenceskcb（kch）skb（bj)xsb(bj）-用触发器实现createtriggert1onskbforinsertasbeginifnotexists（select*fromxsbwherebj=(selectbjfrominserted）beginprint插入的班级不存在！rollbacktransactionendendyhb（yhbh）-xsb(xh）和jsb（jsbh）必须用触发器实现altertriggert2onyhbforinsertasbeginifnotexists(select*fromxsbwherexh=（selectyhbhfrominserted)）beginifnotexists(selectfromjsbwherejsbh=(selectyhbhfrominserted）beginprint插入的编号出错！rollbacktransactionendendend（4） 用户自定义函数（把sql语句贴在下面)（5） 用户自定义存储过程1） 根据学号查询学生的选课情况（把sql语句贴在下面）CREATEPROCEDURExhcxxsxhchar（6）asbeginifexists(selectfromcjbwherexh=xh）select*fromcjbwherexh=xhelseprint无此学生选课信息end2） 根据课程号查询课程的选修情况（把sql语句贴在下面）CREATEPROCEDUREkchcxxkkchchar（3）asbeginifexists（selectfromcjbwherekch=kch）selectfromcjbwherekch=kchelseprint无此课程选课信息end根据教师编号查询授课情况（把sql语句贴在下面）CREATEPROCEDUREjsbhcxskjsbhchar（6)asbeginifexists(selectfromskbwherejsbh=jsbh)select*fromskbwherejsbh=jsbhelseprint无此教师授课信息'end3） 根据班级查询该班级的授课情况（把sql语句贴在下面）CREATEPROCEDUREbjcxskbjvarchar（50）asbeginifexists(select*fromskbwherebj=bj）selectfromskbwherebj=bjelseprint'无此班级授课信息end4） 根据课程号号查看授课情况（把sql语句贴在下面）CREATEPROCEDUREkchcxskkchchar（3)asbeginifexists（select*fromskbwherekch=kch）select*fromskbwherekch=kchelseprint无此课程授课信息end5） 课程成绩分布统计。存储过程名称TJ_CJ。参数：课程号(kch）.实现功能：把成绩表（CJB）中指定课程按照分数段人数进行统计，放入统计表(TJB）中.编写思路:(1）清空TJB表，插入一行所有分数段的人数都为0的所要查找的课程的记录。(2）判断所查的课程号在CJB表中是否有记录，若有则查找出各个分数段的人数并且更新到TJB表中。方法一：createPROCEDUREdbo。TJ_CJ(kchchar（3）astruncatetableTJBinsertintoTJBvalues(kch,0，0，0，0，0）ifexists(select*fromCJBwhereKCH=kch）beginupdateTJBsetRS1=(selectcount()fromCJBwhereCJ=0 andCJ<60 andKCH=kch)updateTJBsetRS2=（selectcount()fromCJBwhereCJ=60 andCJ70 andKCH=kch）updateTJBsetRS3=（selectcount(）fromCJBwhereCJ=70 andCJ<80 andKCH=kch）updateTJBsetRS4=（selectcount（*）fromCJBwhereCJ>=80 andCJ90 andKCH=kch)updateTJBsetRS5=(selectcount（*）fromCJBwhereCJ>=90 andCJ<=100 andKCH=kch）endexecTJ_CJ101'方法二:如果选择统计的课程在成绩表(CJB)已经存在，可以采用下列代码完成统计功能，并且代码效率较高.但CJB表中没有所选择的课程记录,执行的结果是各个分数段的人数为null。createproceduredbo.TJ_CJ（KCHvarchar（3）)asbegindeletefromTJBinsertintoTJB(KCH，RS1，RS2,RS3,RS4，RS5)selectKCH,sum(casewhenCJ 60 then 1 else 0 end），sum（casewhenCJ>= 60 andCJ= 69 then 1 else 0 end），sum（casewhenCJ= 70 andCJ<= 79 then 1 else 0 end），sum（casewhenCJ>= 80 andCJ<= 89 then 1 else 0 end），sum（casewhenCJ>= 90 andCJ= 100 then 1 else 0 end）fromCJBwhereKCH=KCHend（6） 触发器1） 实现yhb（yhbh）xsb（xh）和jsb(jsbh）参照关系必须用触发器实现（把sql语句贴在下面)createtriggert2onyhbforinsertasbeginifnotexists(selectfromxsbwherexh=（selectyhbhfrominserted)）beginifnotexists(selectfromjsbwherejsbh=（selectyhbhfrominserted)）beginprint'插入的编号出错！rollbacktransactionendendend2） 实现更新学生信息和老师信息的时候用户表中的信息可以实现级联修改。(把sql语句贴在下面）createtriggerxsb_tiggeronxsbforupdateasifupdate（xh）begindeclarenew_numvarchar(6），old_numvarchar（6)selectnew_num=xhfrominsertedselectold_num=xhfromdeletedupdateyhbsetyhbh=new_numwhereyhbh=old_numendcreatetriggerjsb_tiggeronjsbforupdateasifupdate（jsbh)begindeclarenew_numvarchar（6)，old_numvarchar（6)selectnew_num=jsbhfrominsertedselectold_num=jsbhfromdeletedupdateyhbsetyhbh=new_numwhereyhbh=old_numend3） 当删除学生信息的时候选课记录同时删除,同时用户表的记录也删除。（把sql语句贴在下面)createtriggerxsb_d1onxsbfordeleteasbegindeclarexhvarchar(6)selectxh=xhfromdeleteddeletefromcjbwherexh=xhdeletefromyhbwhereyhbh=xhend4） 当删除老师信息的时候授课记录也同时删除,同时用户表中的记录也删除。（把sql语句贴在下面）createtriggerjsb_deletiggeronjsbfordeleteasbegindeclarejsbhvarchar（6）selectjsbh=jsbhfromdeleteddeletefromskbwherejsbh=jsbhdeletefromyhbwhereyhbh=jsbhend5） 增加成绩时成绩大于60应该给学生总学分加上这门课的学分.删除成绩的时候，如果成绩小于60分，应该把学生的总学分去掉这门课的学分。（把sql语句贴在下面）createtriggercjb_insetiggeroncjbforinsertasbegindeclarexhvarchar(6)declarecjintdeclarexfintdeclarekchchar(3）selectxh=xh,cj=cj，kch=kchfrominsertedselectxf=xffromkcbwherekch=kchif（cj=60）beginupdatexsbsetzxf=zxf+xfwherexh=xhendendcreatetriggercjb_deletiggeroncjbfordeleteasbegindeclarexhvarchar（6）declarecjintdeclarexfintdeclarekchchar(3）selectxh=xh，cj=cj,kch=kchfromdeletedselectxf=xffromkcbwherekch=kchif(cj=60）beginupdatexsbsetzxf=zxf-xfwherexh=xhendend6）通过instead of触发器实现对学生选课情况视图(学号，姓名,课程号,课程名,成绩，学分，总学分,教师）的插入数据操作、更新数据操作、删除数据操作。（把sql语句贴在下面)createviewv1asselectcjb.xh，xm,xsb。bj，cjb。kch，kcm,CJ,xf,zxf,jsb.jsbh，jsxmfromXSB，KCB,CJB,jsb，skbwherexsb.XH=CJB.XHandkcb。KCH=cjb。KCHandjsb。jsbh=skb。jsbhandskb。kch=CJB。KCHcreatetriggerv1_t 有问题，待完善onv1insteadofinsertasbegindeclarexhchar（6)，xmchar（8），kchchar（3），kcmchar（16）,cjint,jsbhchar（6），jsxmchar(8），bjvarchar(50）setxh=（selectxhfrominserted）setxm=（selectXMfrominserted)setkch=（selectkchfrominserted)setkcm=（selectkcmfrominserted)setcj=（selectCJfrominserted）setjsbh=（selectjsbhfrominserted)setjsxm=（selectjsxmfrominserted）setbj=（selectbjfrominserted）ifnotexists（selectfromXSBwherexh=xh）begininsertintoXSB（XH，XM，bj)values(xh，xm，bj）endifnotexists(selectfromKCBwhereKCH=kch)begininsertintoKCB(KCH,KCM）values（kch,kcm)endifnotexists（select*fromCJBwhereXH=xhandKCH=kch）begininsertintoCJB（XH,KCH,CJ）values(xh,kch，cj）endifnotexists(selectfromjsbwherejsbh=jsbh)begininsertintojsb(jsbh，jsxm)values（jsbh，jsxm）endifnotexists（select*fromskbwherejsbh=jsbhandkch=kchandbj=bj）begininsertintoskb(jsbh，kch,bj）values（jsbh,kch,bj)endEnd6、运行与维护 用java和sql server 2008把系统功能实现.
