学生成绩管理系统数据库设计文档---(全).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介于0100之间。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介于0-100之间.数据库编号:pxscj基表编号：f5基表英文名称：kcb基表中文名称：课程表字段编号英文字段名中文字段名字段类型备注1KCH课程号Char（3）2KCM课程名Char(16）3Cj成绩Int说明：xh参照学生表的xh;kch参照课程表的kch;cj介于0100之间.数据库编号：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要求,如果没有达到，则继续规范）按照（1nf2nf-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:mm123456altertableyhbaddconstraintb9DEFAULT123456formm5）规则和check约束Xsb：zxf范围在0-160之间。Kcb：kkxq范围在1-8之间，xf在115之间Cjb：cj范围在0100之间Xsb：zxf在0-160之间。altertablexsbaddconstraintpk_1check （zxfbetween 0 and 160)Kcb：kkxq在1-8之间,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(selectfromxsbwherebj=（selectbjfrominserted）beginprint插入的班级不存在！rollbacktransactionendendyhb（yhbh）xsb（xh）和jsb（jsbh)-必须用触发器实现altertriggert2onyhbforinsertasbeginifnotexists（selectfromxsbwherexh=（selectyhbhfrominserted)beginifnotexists（select*fromjsbwherejsbh=（selectyhbhfrominserted)）beginprint插入的编号出错!rollbacktransactionendendend（4） 用户自定义函数（把sql语句贴在下面）（5） 用户自定义存储过程1） 根据学号查询学生的选课情况（把sql语句贴在下面)CREATEPROCEDURExhcxxsxhchar（6)asbeginifexists（select*fromcjbwherexh=xh）select*fromcjbwherexh=xhelseprint无此学生选课信息end2） 根据课程号查询课程的选修情况（把sql语句贴在下面）CREATEPROCEDUREkchcxxkkchchar（3)asbeginifexists（selectfromcjbwherekch=kch）selectfromcjbwherekch=kchelseprint无此课程选课信息end根据教师编号查询授课情况(把sql语句贴在下面）CREATEPROCEDUREjsbhcxskjsbhchar（6）asbeginifexists(select*fromskbwherejsbh=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 andCJ60 andKCH=kch）updateTJBsetRS2=（selectcount（)fromCJBwhereCJ=60 andCJ=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 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=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（select*fromXSBwherexh=xh)begininsertintoXSB(XH,XM,bj）values(xh，xm，bj)endifnotexists（select*fromKCBwhereKCH=kch）begininsertintoKCB（KCH,KCM）values（kch，kcm）endifnotexists（selectfromCJBwhereXH=xhandKCH=kch）begininsertintoCJB(XH，KCH,CJ）values(xh,kch,cj）endifnotexists（select*fromjsbwherejsbh=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把系统功能实现.