学生成绩管理系统数据库设计文档---(全).doc

“学生成绩管理"数据库设计文档
0、前言（一些必要的说明。）
0。1 数据库说明
数据库名：PXSCJ
逻辑名称:学生成绩数据库
数据文件：PXSCJ.mdf
日志文件:PXSCJ_Log
登录名：admin,密码：123456
0.2表命名说明
Cjb：成绩表，保存选课信息
Cxb:查询表，记录boolean值对应信息，1代表男,0代表女.
Kcb:课程表。
Tjb：统计表，统计成绩段分布。
Xsb:学生表.
Yhb：用户表，保存系统用户信息.
Jsb： 教师表。
Skb:授课表,记录授课信息。
0。3 系统功能模块图
1、 需求分析阶段
说明：学生成绩管理系统需要实现以下功能：一个学生可以选修多门课程，一门课程可以由多个学生选修,学生选修一门课会有一个成绩.一个教师可以教授多个班级，一个教师也可以教授多门课程，一个班级有多个学生，一门课程也可以由多个老师来上，一个老师给一个班级上一门课有确定的时间和地点。不同的用户根据身份不同拥有不同的权限.
（1） 数据流图
老师—--—成绩管理,学生信息管理，权限管理—-—学生成绩管理系统—成绩查询——学生
(要求：用visio实现第一层数据流图,第二层数据流图，第三层数据流图)p121
第一层数据流图
第二层数据流图
第三层数据流图（略)
（2） 数据字典
（每个实体的详细说明）
数据库编号:pxscj
基表编号：f1
基表英文名称：yhb
基表中文名称：用户表
字段编号
英文字段名
中文字段名
字段类型
备注
1
Yhbh
用户编号
Char(6)

2
yhm
用户名
Char（8）

3
sf
身份
Varcha（20）

4
mm
密码
Char（6）

5
bz
备注
Varcha（50）

说明：
数据库编号：pxscj
基表编号：f2
基表英文名称:cjb
基表中文名称:成绩表
字段编号
英文字段名
中文字段名
字段类型
备注
1
Xh
学号
Char（6）

2
Kch
课程号
Char（3）

3
Cj
成绩
Int

说明：xh参照学生表的xh;kch参照课程表的kch;cj介于0—100之间。
数据库编号：pxscj
基表编号：f3
基表英文名称：kcb
基表中文名称:课程表
字段编号
英文字段名
中文字段名
字段类型
备注
1
Xh
学号
Char（6)

2
XM
姓名
Char（8)

3
XB
性别
Bit

4
CSSJ
出生时间
Datetime

5
Bj
班级
Varchar（50)

6
Zy
专业
Char（12)

7
ZXH
总学分
Int

8
Lxfs
联系方式
Varchar（50)

9
zp
照片
Varbinary（MAX)

10
BZ

Varchar(500）

说明：xh参照学生表的xh；kch参照课程表的kch；cj介于0—100之间。xb参照查询表的xb
数据库编号:pxscj
基表编号：f4
基表英文名称:jsb
基表中文名称：教师表
字段编号
英文字段名
中文字段名
字段类型
备注
1
Jsbh
教师编号
Char(6)

2
Jsxm
教师姓名
Char(8）

3
zy
专业
Varchar(30）

4
Fy
分院
Varchar(30）

5
Zc
职称
Varchar（20）

6
Zc
职务
Varchar(20)

7
Bz

Varchar（50）

说明:xh参照学生表的xh；kch参照课程表的kch;cj介于0-100之间.
数据库编号:pxscj
基表编号：f5
基表英文名称：kcb
基表中文名称：课程表
字段编号
英文字段名
中文字段名
字段类型
备注
1
KCH
课程号
Char（3）

2
KCM
课程名
Char(16）

3
Cj
成绩
Int

说明：xh参照学生表的xh;kch参照课程表的kch;cj介于0—100之间.
数据库编号：pxscj
基表编号：f6
基表英文名称：cjb
基表中文名称：课程表
字段编号
英文字段名
中文字段名
字段类型
备注
1
Xh
学号
Char(6）

2
Kch
课程号
Char（3）

3
KKXQ

Tinyint

4
XS
学生
tinyint

5
XF
学分
tinyint

说明：xh参照学生表的xh;kch参照课程表的kch；
数据库编号：pxscj
基表编号：f7
基表英文名称：skb
基表中文名称:授课表
字段编号
英文字段名
中文字段名
字段类型
备注
1
Jsbh
教师编号
Char(6)

2
Kch
课程号
Char（3)

3
Bj
班级
Varchar(50）

4
Sj
时间
Varchar(50）

5
dd
地点
Varchar（50）

说明:jsbh参照教师表的jsbh；kch参照课程表的kch;
数据库编号：pxscj
基表编号:f8
基表英文名称：CXB
基表中文名称：查询表
字段编号
英文字段名
中文字段名
字段类型
备注
1
XB
性别
Bit

2
XBM
性别名
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
因为不存在传递函数依赖，所以属于3NF
Cxb(xb，xbm）
码：（xbm）
非主属性:xb
因为xb完全依赖于（kch)，所以属于2NF
因为不存在传递函数依赖，所以属于3NF
Jsb（jsbh，jsxm，zy，fy，zc,zw，bz）
码：（jsbh)
非主属性：jsxm,zy，fy，zc,zw，bz
因为jsxm，zy，fy,zc,zw，bz完全依赖于（kch）,所以属于2NF
因为不存在传递函数依赖，所以属于3NF
Kcb(kch，kcm，kkxq,xs,xf)
码：（kch)
非主属性：kcm，kkxq，xs，xf
因为kcm，kkxq，xs，xf完全依赖于(kch）,所以属于2NF
因为不存在传递函数依赖，所以属于3NF
Skb（jsbh,kch，bj,sj，dd)
码:（jsbh,kch,bj）
非主属性：sj,dd
因为sj,dd完全依赖于(jsbh,kch，bj）,所以属于2NF
因为不存在传递函数依赖,所以属于3NF
Tjb（kch，rs1，rs2，rs3,rs4，rs5）
码：（kch）
非主属性:rs1，rs2，rs3,rs4,rs5
因为rs1,rs2,rs3，rs4，rs5完全依赖于(kch），所以属于2NF
因为不存在传递函数依赖，所以属于3NF
Xsb（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
因为不存在传递函数依赖，所以属于3NF
Yhb(yhbh,yhm,mm，bz)
码:（yhbh)
非主属性:yhm，mm，bz
因为yhm，mm,bz完全依赖于（yhbh），所以属于2NF
因为不存在传递函数依赖，所以属于3NF
4、物理设计
选择合适的DBMS（要求用sql server 2008）
5、实施(把sql语句贴在下面）
(1）创建数据库
(把sql语句贴在下面）
createdatabasepxscj1
on
（
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_view
as
selectxsb。XH，XM,kcb。KCH，kcm,CJ，xf，zxf,jsb.jsbh，jsxm
fromxsb，KCB，CJB,jsb，skb
wherexsb。XH=cjb。XHandkcb。KCH=cjb。KCH
andcjb.KCH=skb。kchandskb。jsbh=jsb.jsbh
select*fromxs_xk_view
3）自定义数据库类型
学号，课程号可以考虑用自定义的数据类型。
sp_addtype’xh’，’char(6)'
sP_addtype'kch’，'char(3）'
4)默认值对象
Xsb:性别默认为1，总学分默认为0。
Kcb:学生人数默认为40，学分默认为2。
Jsb：zy计算机，fy信息，zc讲师，zw无。
Yhb:mm'123456’
Xsb：性别默认为1，
altertablexsbaddconstraintb1DEFAULT 1 forxb
总学分默认为0.
altertablexsbaddconstraintb2DEFAULT 0 forzxf
Kcb：学生人数默认为40，
altertablekcbaddconstraintb3DEFAULT 40 forxs
学分默认为2.
altertablekcbaddconstraintb4DEFAULT 2 forxf
Jsb：zy计算机，
altertablejsbaddconstraintb5DEFAULT’计算机’forzy
fy信息，
altertablejsbaddconstraintb6DEFAULT’信息’forfy
zc讲师，
altertablejsbaddconstraintb7DEFAULT’讲师'forzc
zw无。
altertablejsbaddconstraintb8DEFAULT'无’forzw
Yhb:mm'123456’
altertableyhbaddconstraintb9DEFAULT'123456’formm
5）规则和check约束
Xsb：zxf范围在0-160之间。
Kcb：kkxq范围在1-8之间，xf在1—15之间
Cjb：cj范围在0—100之间
Xsb：zxf在0-160之间。
altertablexsb
addconstraintpk_1check （zxfbetween 0 and 160)
Kcb：kkxq在1-8之间,xf在1—15之间
altertablekcb
addconstraintpk_2check （kkxqbetween 1 and 8)
altertablekcb
addconstraintpk_3check （xfbetween 1 and 15）
Cjb：cj在0-100之间
altertablecjb
addconstraintpk_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)
altertablecjb
addconstraintfk_1foreignkey （xh）referencesxsb(xh)
cjb(kch）-kcb（kch）
altertablecjb
addconstraintfk_2foreignkey (kch）referenceskcb(kch)
skb(jsbh）—jsb(jsbh）
altertableskb
addconstraintfk_4foreignkey （jsbh）referencesjsb(jsbh）
skb（kch）—kcb（kch）
altertableskb
addconstraintfk_6foreignkey （kch)referenceskcb（kch）
skb(bj）-xsb（bj)——用触发器实现
createtriggert1onskb
forinsert
as
begin
ifnotexists(select＊fromxsbwherebj=（selectbjfrominserted））
begin
print’插入的班级不存在！'
rollbacktransaction
end
end
yhb（yhbh）—xsb（xh）和jsb（jsbh)--必须用触发器实现
altertriggert2onyhb
forinsert
as
begin
ifnotexists（select＊fromxsbwherexh=（selectyhbhfrominserted))
begin
ifnotexists（select*fromjsbwherejsbh=（selectyhbhfrominserted)）
begin
print'插入的编号出错!’
rollbacktransaction
end
end
end
（4） 用户自定义函数
（把sql语句贴在下面）
（5） 用户自定义存储过程
1） 根据学号查询学生的选课情况
（把sql语句贴在下面)
CREATEPROCEDURExhcxxs＠xhchar（6)
as
begin
ifexists（select*fromcjbwherexh=＠xh）
select*fromcjbwherexh=@xh
else
print’无此学生选课信息’
end
2） 根据课程号查询课程的选修情况
（把sql语句贴在下面）
CREATEPROCEDUREkchcxxk＠kchchar（3)
as
begin
ifexists（select＊fromcjbwherekch=@kch）
select＊fromcjbwherekch=＠kch
else
print’无此课程选课信息’
end
根据教师编号查询授课情况
(把sql语句贴在下面）
CREATEPROCEDUREjsbhcxsk@jsbhchar（6）
as
begin
ifexists(select*fromskbwherejsbh=＠jsbh）
select*fromskbwherejsbh=＠jsbh
else
print'无此教师授课信息’
end
3） 根据班级查询该班级的授课情况
（把sql语句贴在下面)
CREATEPROCEDUREbjcxsk@bjvarchar（50）
as
begin
ifexists(select*fromskbwherebj=@bj）
select＊fromskbwherebj=@bj
else
print'无此班级授课信息'
end
4） 根据课程号号查看授课情况
（把sql语句贴在下面）
CREATEPROCEDUREkchcxsk＠kchchar(3）
as
begin
ifexists（select*fromskbwherekch=@kch)
select*fromskbwherekch=@kch
else
print’无此课程授课信息’
end
5） 课程成绩分布统计。存储过程名称TJ_CJ。参数：课程号（＠kch)。实现功能：把成绩表(CJB)中指定课程按照分数段人数进行统计，放入统计表（TJB）中。
编写思路:（1）清空TJB表，插入一行所有分数段的人数都为0的所要查找的课程的记录.（2）判断所查的课程号在CJB表中是否有记录，若有则查找出各个分数段的人数并且更新到TJB表中。
方法一:
createPROCEDURE[dbo].[TJ_CJ］（@kchchar（3））
as
truncatetableTJB
insertintoTJBvalues（@kch,0,0，0,0，0）
ifexists(select*fromCJBwhereKCH=＠kch)
begin
updateTJBsetRS1=（selectcount(＊)fromCJBwhereCJ>=0 andCJ〈60 andKCH=@kch）
updateTJBsetRS2=（selectcount（＊)fromCJBwhereCJ>=60 andCJ<70 andKCH=@kch)
updateTJBsetRS3=（selectcount(*)fromCJBwhereCJ〉=70 andCJ〈80 andKCH=@kch）
updateTJBsetRS4=（selectcount(＊)fromCJBwhereCJ>=80 andCJ〈90 andKCH=@kch)
updateTJBsetRS5=（selectcount（＊）fromCJBwhereCJ>=90 andCJ<=100 andKCH=＠kch）
end
execTJ_CJ'101'
方法二：
如果选择统计的课程在成绩表(CJB)已经存在，可以采用下列代码完成统计功能，并且代码效率较高。但CJB表中没有所选择的课程记录，执行的结果是各个分数段的人数为null。
createprocedure［dbo］。［TJ_CJ］（＠KCHvarchar(3)）
as
begin
deletefromTJB
insertintoTJB（KCH,RS1,RS2,RS3，RS4，RS5）
select@KCH，
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=@KCH
end
（6） 触发器
1） 实现yhb(yhbh)—xsb（xh）和jsb（jsbh)参照关系–必须用触发器实现
（把sql语句贴在下面)
createtriggert2onyhb
forinsert
as
begin
ifnotexists(select*fromxsbwherexh=(selectyhbhfrominserted））
begin
ifnotexists(select＊fromjsbwherejsbh=(selectyhbhfrominserted)）
begin
print'插入的编号出错!’
rollbacktransaction
end
end
end
2） 实现更新学生信息和老师信息的时候用户表中的信息可以实现级联修改.
(把sql语句贴在下面）
createtriggerxsb_tiggeronxsb
forupdate
as
ifupdate(xh）
begin
declare＠new_numvarchar(6），@old_numvarchar（6）
select＠new_num=xhfrominserted
select＠old_num=xhfromdeleted
updateyhb
setyhbh=＠new_num
whereyhbh=＠old_num
end
createtriggerjsb_tiggeronjsbforupdate
as
ifupdate（jsbh)
begin
declare＠new_numvarchar（6）,＠old_numvarchar（6)
select＠new_num=jsbhfrominserted
select＠old_num=jsbhfromdeleted
updateyhb
setyhbh=@new_num
whereyhbh=＠old_num
end
3） 当删除学生信息的时候选课记录同时删除,同时用户表的记录也删除。
（把sql语句贴在下面）
createtriggerxsb_d1onxsb
fordelete
as
begin
declare@xhvarchar(6)
select＠xh=xhfromdeleted
deletefromcjbwherexh=＠xh
deletefromyhbwhereyhbh=＠xh
end
4） 当删除老师信息的时候授课记录也同时删除，同时用户表中的记录也删除。
（把sql语句贴在下面）
createtriggerjsb_deletiggeronjsb
fordelete
as
begin
declare＠jsbhvarchar（6）
select@jsbh=jsbhfromdeleted
deletefromskbwherejsbh=＠jsbh
deletefromyhbwhereyhbh=@jsbh
end
5） 增加成绩时成绩大于60应该给学生总学分加上这门课的学分.删除成绩的时候，如果成绩小于60分，应该把学生的总学分去掉这门课的学分。
(把sql语句贴在下面)
createtriggercjb_insetiggeroncjbforinsert
as
begin
declare＠xhvarchar（6)
declare@cjint
declare@xfint
declare@kchchar（3)
select@xh=xh，＠cj=cj，@kch=kchfrominserted
select＠xf=xffromkcbwherekch=@kch
if（@cj〉=60）
begin
updatexsb
setzxf=zxf+＠xf
wherexh=＠xh
end
end
createtriggercjb_deletiggeroncjbfordelete
as
begin
declare@xhvarchar(6）
declare＠cjint
declare＠xfint
declare@kchchar(3)
select@xh=xh，@cj=cj，＠kch=kchfromdeleted
select＠xf=xffromkcbwherekch=＠kch
if（＠cj>=60)
begin
updatexsb
setzxf=zxf-@xf
wherexh=@xh
end
end
6)通过instead of触发器实现对学生选课情况视图（学号，姓名,课程号,课程名，成绩，学分,总学分,教师)的插入数据操作、更新数据操作、删除数据操作.
（把sql语句贴在下面）
createviewv1
as
selectcjb。xh,xm，xsb.bj，cjb.kch，kcm,CJ,xf，zxf，jsb。jsbh,jsxm
fromXSB,KCB,CJB，jsb,skb
wherexsb。XH=CJB.XHandkcb。KCH=cjb。KCHandjsb.jsbh=skb。jsbhandskb。kch=CJB.KCH
createtriggerv1_t –有问题，待完善
onv1
insteadofinsert
as
begin
declare＠xhchar(6）,@xmchar（8），＠kchchar(3）,@kcmchar（16），＠cjint,@jsbhchar（6），＠jsxmchar（8)，@bjvarchar（50）
set＠xh=(selectxhfrominserted）
set@xm=（selectXMfrominserted)
set@kch=(selectkchfrominserted）
set＠kcm=(selectkcmfrominserted）
set@cj=（selectCJfrominserted)
set＠jsbh=（selectjsbhfrominserted）
set＠jsxm=(selectjsxmfrominserted)
set@bj=（selectbjfrominserted）
ifnotexists（select*fromXSBwherexh=@xh)
begin
insertintoXSB(XH,XM,bj）
values(@xh，＠xm，@bj)
end
ifnotexists（select*fromKCBwhereKCH=@kch）
begin
insertintoKCB（KCH,KCM）
values（＠kch，@kcm）
end
ifnotexists（select＊fromCJBwhereXH=＠xhandKCH=@kch）
begin
insertintoCJB(XH，KCH,CJ）
values(＠xh,＠kch,＠cj）
end
ifnotexists（select*fromjsbwherejsbh=＠jsbh)
begin
insertintojsb（jsbh,jsxm）
values(＠jsbh，＠jsxm）
end
ifnotexists（select*fromskbwherejsbh=@jsbhandkch=@kchandbj=＠bj)
begin
insertintoskb(jsbh，kch,bj)
values（@jsbh，@kch,＠bj）
end
End
6、运行与维护
用java和sql server 2008把系统功能实现.