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

1、“学生成绩管理”数据库设计文档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、 需求分析阶段说明：学生成绩管理系统需要实现以下功能：一个学生可以选修多门课程，一门课程

2、可以由多个学生选修，学生选修一门课会有一个成绩。一个教师可以教授多个班级，一个教师也可以教授多门课程，一个班级有多个学生，一门课程也可以由多个老师来上，一个老师给一个班级上一门课有确定的时间和地点。不同的用户根据身份不同拥有不同的权限。（1 ） 数据流图老师-成绩管理，学生信息管理，权限管理-学生成绩管理系统 成绩查询- 学生（要求：用 visio 实现第一层数据流图，第二层数据流图，第三层数据流图）p121老师学生学生成绩管理系统成绩管理信息管理权限管理成绩查询修改密码第一层数据流图老师学生1 、 修改密码2 、 查询成绩F 1 用户表F 2 成绩表修改查询 返回3 、 用户管理4 、 课程

3、管理5 、 成绩管理6 、 授课管理F 3 学生表F 4 教师表F 5 课程表F 6 成绩表F 7 授课表第二层数据流图第三层数据流图（略）（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基表中文名称：成绩表字段编号 英文字段名 中文字段名字段类型

4、 备注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 性别 Bit4 CSSJ 出生时间 Datetime5 Bj 班级 Varchar(50)6 Zy 专业 Char(12)7 ZXH 总学分 Int8 Lxfs 联系方式 Varchar(50)9 zp 照片 Varbina

5、ry(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 参照课程表的

6、 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 Tinyint4 XS 学生 tinyint5 XF

7、学分 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

8、性别 Bit2 XBM 性别名 Char(4)说明： 2、 概念设计阶段（1 ） 分 ER 图（两个分 ER 图，1）学生和课程，2）教师，课程，班级）学生课程选课教师课程授课班级nmmnp（2 ） 总 ER 图（由分 ER 图画出总 ER 图）学生选课教师课程授课班级nmmnp3、 逻辑设计阶段（1 ） 表关系图（看是否可以画出）（2 ） 表结构图Xsb 结构Kcb 结构Cjb 结构Yhb 结构Jsb 结构Skb 结构Tjb 结构Cxb 结构（3 ） 表优化（判断每个关系是否达到 3NF 要求，如果没有达到，则继续规范）按照（1nf-2nf-3nf 进行判断和优化）Cjb（xh,kch,cj

9、）码：（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

10、,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(x

11、h,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 语句贴在下面

12、）（1 ）创建数据库（把 sql 语句贴在下面）create database pxscj1on(name=pxscj1_data,filename=f:pxscj1_data.mdf,size=3mb,filegrowth=10%)log on(name=pxscj1_log,filename=f:pxscj1_log.ldf,size=4mb,maxsize=6mb,filegrowth=1mb)（2 ）创建表（把 sql 语句贴在下面）create table xsb(xh char(6) not null primary key,xm char(8) not null,xb bit,c

13、ssj datetime,bj varchar(50) not null,zy char(12),zxf int,bz varchar(500),lxfs varchar(50),zp varbinary(max)create table kcb (kch char(3) not null primary key,kcm char(16) not null,kkxq tinyint,xs tinyint,xf tinyint)create table cjb(xh char(6),kch char(6),cj int,constraint c1 primary key(xh,kch)creat

14、e table yhb(yhbh char(6) primary key not null,yhm char(8),sf varchar(20),mm char(6),bz varchar(5)create table jsb(jsbh char(6) not null primary key,jsxm char(8),zy varchar(30),fy varchar(30),zc varchar(20),zw varchar(20),bz varchar(50)create table skb(jsbh char(6) not null,kch char(3) not null,bj va

15、rchar(50) not null,sj varchar(20),dd varchar(50),constraint c2 primary key(jsbh,kch,bj)create table tjb(kch char(3) primary key not null,rs1 int,rs2 int,rs3 int,rs4 int,rs5 int) create table cxb(xb bit not null,xbm char(4) not null primary key)（3 ）数据库完整性2）视图学生选课情况视图（学号，姓名，课程号，课程名，成绩，学分，总学分，教师）create

16、 view xs_xk_view asselect xsb.XH,XM,kcb.KCH,kcm,CJ,xf,zxf,jsb.jsbh,jsxmfrom xsb,KCB,CJB,jsb,skbwhere xsb.XH=cjb.XH and kcb.KCH=cjb.KCH and cjb.KCH=skb.kch and skb.jsbh=jsb.jsbhselect * from xs_xk_view3）自定义数据库类型学号，课程号可以考虑用自定义的数据类型。sp_addtype xh,char(6)sP_addtype kch,char(3)4）默认值对象Xsb:性别默认为 1，总学分默认为 0

17、。Kcb:学生人数默认为 40，学分默认为 2。Jsb:zy 计算机，fy 信息，zc 讲师，zw 无。Yhb:mm123456Xsb:性别默认为 1， alter table xsb add constraint b1 DEFAULT 1 for xb总学分默认为 0。alter table xsb add constraint b2 DEFAULT 0 for zxfKcb:学生人数默认为 40，alter table kcb add constraint b3 DEFAULT 40 for xs学分默认为 2。alter table kcb add constraint b4 DEFAU

18、LT 2 for xfJsb:zy 计算机，alter table jsb add constraint b5 DEFAULT 计算机 for zyfy 信息，alter table jsb add constraint b6 DEFAULT 信息 for fyzc 讲师，alter table jsb add constraint b7 DEFAULT 讲师 for zczw 无。alter table jsb add constraint b8 DEFAULT 无 for zwYhb:mm123456alter table yhb add constraint b9 DEFAULT 123

19、456 for mm5）规则和 check 约束Xsb:zxf 范围在 0-160 之间。Kcb:kkxq 范围在 1-8 之间，xf 在 1-15 之间Cjb:cj 范围在 0-100 之间Xsb:zxf 在 0-160 之间。alter table xsbadd constraint pk_1 check (zxf between 0 and 160)Kcb:kkxq 在 1-8 之间，xf 在 1-15 之间alter table kcbadd constraint pk_2 check (kkxq between 1 and 8)alter table kcbadd constrain

20、t pk_3 check (xf between 1 and 15)Cjb:cj 在 0-100 之间alter table cjbadd constraint pk_4 check (cj between 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)alter table cjb add constraint fk_

21、1 foreign key (xh) references xsb (xh)cjb(kch)kcb(kch)alter table cjb add constraint fk_2 foreign key (kch) references kcb (kch)skb(jsbh)jsb(jsbh)alter table skb add constraint fk_4 foreign key (jsbh) references jsb (jsbh)skb(kch)kcb(kch)alter table skb add constraint fk_6 foreign key (kch) referenc

22、es kcb (kch)skb(bj)xsb(bj) -用触发器实现create trigger t1 on skbfor insertasbeginif not exists(select * from xsb where bj=(select bj from inserted)beginprint 插入的班级不存在！rollback transactionend endyhb(yhbh)xsb(xh)和jsb(jsbh) -必须用触发器实现alter trigger t2 on yhb for insertasbeginif not exists(select * from xsb whe

23、re xh=(select yhbh from inserted)begin if not exists(select * from jsb where jsbh=(select yhbh from inserted)beginprint 插入的编号出错！rollback transactionend endend（4 ） 用户自定义函数（把 sql 语句贴在下面）（5 ） 用户自定义存储过程1） 根据学号查询学生的选课情况（把 sql 语句贴在下面）CREATE PROCEDURE xhcxxs xh char(6) asbeginif exists(select * from cjb wh

24、ere xh=xh)select * from cjb where xh=xhelseprint 无此学生选课信息end2） 根据课程号查询课程的选修情况（把 sql 语句贴在下面）CREATE PROCEDURE kchcxxk kch char(3) asbeginif exists(select * from cjb where kch=kch)select * from cjb where kch=kchelseprint 无此课程选课信息end根据教师编号查询授课情况（把 sql 语句贴在下面）CREATE PROCEDURE jsbhcxsk jsbh char(6) asbegi

25、nif exists(select * from skb where jsbh=jsbh)select * from skb where jsbh=jsbhelseprint 无此教师授课信息end3） 根据班级查询该班级的授课情况（把 sql 语句贴在下面）CREATE PROCEDURE bjcxsk bj varchar(50) asbeginif exists(select * from skb where bj=bj)select * from skb where bj=bjelseprint 无此班级授课信息end4） 根据课程号号查看授课情况（把 sql 语句贴在下面）CREAT

26、E PROCEDURE kchcxsk kch char(3) asbeginif exists(select * from skb where kch=kch)select * from skb where kch=kchelseprint 无此课程授课信息end5） 课程成绩分布统计。存储过程名称 TJ_CJ。参数：课程号（kch） 。实现功能：把成绩表（CJB）中指定课程按照分数段人数进行统计，放入统计表（TJB ）中。编写思路：（1）清空 TJB 表，插入一行所有分数段的人数都为 0 的所要查找的课程的记录。 （2 ）判断所查的课程号在 CJB 表中是否有记录，若有则查找出各个分数段的

27、人数并且更新到 TJB 表中。方法一：create PROCEDURE dbo.TJ_CJ(kch char(3)astruncate table TJBinsert into TJB values(kch,0,0,0,0,0)if exists (select * from CJB where KCH=kch)beginupdate TJB set RS1=(select count(*) from CJB where CJ=0 and CJ=60 and CJ=70 and CJ=80 and CJ=90 and CJ= 60 and CJ = 70 and CJ = 80 and CJ

28、= 90 and CJ =60)beginupdate xsb set zxf=zxf+xfwhere xh=xhend endcreate trigger cjb_deletigger on cjb for deleteasbegindeclare xh varchar(6)declare cj intdeclare xf intdeclare kch char(3)select xh=xh,cj=cj,kch=kch from deleted select xf=xf from kcb where kch=kch if(cj=60)beginupdate xsb set zxf=zxf-x

29、fwhere xh=xhend end6）通过 instead of 触发器实现对学生选课情况视图（学号，姓名，课程号，课程名，成绩，学分，总学分，教师）的插入数据操作、更新数据操作、删除数据操作。（把 sql 语句贴在下面）create view v1 asselect cjb.xh,xm,xsb.bj,cjb.kch,kcm,CJ,xf,zxf,jsb.jsbh,jsxmfrom XSB,KCB,CJB,jsb,skbwhere xsb.XH=CJB.XH and kcb.KCH=cjb.KCH and jsb.jsbh=skb.jsbh and skb.kch=CJB.KCHcreate

30、 trigger v1_t 有问题，待完善on v1instead of insertasbegindeclare xh char(6),xm char(8),kch char(3),kcm char(16),cj int,jsbh char(6),jsxm char(8),bj varchar(50)set xh=(select xh from inserted)set xm=(select XM from inserted)set kch=(select kch from inserted)set kcm=(select kcm from inserted)set cj=(select C

31、J from inserted)set jsbh=(select jsbh from inserted)set jsxm=(select jsxm from inserted)set bj=(select bj from inserted)if not exists(select * from XSB where xh=xh)begininsert into XSB(XH,XM,bj)values(xh,xm,bj)endif not exists(select * from KCB where KCH=kch)begininsert into KCB(KCH,KCM)values(kch,k

32、cm)endif not exists(select * from CJB where XH=xh and KCH=kch)begininsert into CJB(XH,KCH,CJ)values(xh,kch,cj)endif not exists(select * from jsb where jsbh=jsbh)begininsert into jsb(jsbh,jsxm)values(jsbh,jsxm)endif not exists(select * from skb where jsbh=jsbh and kch=kch and bj=bj)begininsert into skb(jsbh,kch,bj)values(jsbh,kch,bj)endEnd6、运行与维护用 java 和 sql server 2008 把系统功能实现。