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

“学生成绩管理”数据库设计文档0、前言（一些必要的说明.）0.1数据库说明数据库名：PXSCJ逻辑名称:学生成绩数据库数据文件：PXSCJ o mdf日志文件：PXSQ_Log登录名：admin,密码:1234560.2表命名说明Cjb：成绩表，保存选课信息Cxb：查询表，记录boolean值对应信息，1代表男,。代表女。Kcb：课程表.Tjb:统计表，统计成绩段分布。Xsb:学生表。Yhb：用户表，保存系统用户信息。Jsb:教师表。Skb:授课表，记录授课信息。0.3系统功能模块图1、需求分析阶段说明：学生成绩管理系统需要实现以下功能：一个学生可以选修多门课程，一门课程可 以由多个学生选修，学生选修一门课会有一个成绩.一个教师可以教授多个班级，一个教师也 可以教授多门课程，一个班级有多个学生，一门课程也可以由多个老师来上，一个老师给一 个班级上一门课有确定的时间和地点。不同的用户根据身份不同拥有不同的权限.（1） 数据流图老师一一成绩管理，学生信息管理，权限管理一-学生成绩管理系统一成绩查询-一学生（要求：用visi。实现第一层数据流图，第二层数据流图，第三层数据流图）pl21 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 cl primary key (xh,kch) )create table yhb (yhbh char (6) primary key not null, yhm char (8), sf varchar (20) z mm char ，bz varchar (5) )create table j sb (j sbh char (6) not null primary key,j sxm char (8),zy varchar (30)，fyvarchar (30),zc varchar (20),zw varchar (20), bz varchar (50) )create table skb (j sbh char (6) not null,kch char (3) not null,bj varchar (50) not null,sj varchar (20),dd varchar (50),constraint c2 primary key (jsbhz kch,bj) )create table tjb (kch char (3) primary key not null, rsl int,rs2 int,rs3 int,rs4 int, rs5 int ) create table cxb ( xb bit not null.（3）数据库完整性2）视图学生选课情况视图（学号，姓名，课程号，课程名，成绩，学分，总学分，教师） create view xs_xk_viewasselect xsbXH, XM,kcbo KCH,kcm, CJ,xf,zxf fjsb.j sbh,j sxmfrom xsb, KCB, CJB,jsb.skbwhere xsbo XH=cjbo XH and kcbo KCH=cjb.KCHand cjb.KCH=skb.kch and skb。 j sbh=j sb.j sbhselect * from xs_xk_view3）自定义数据库类型学号，课程号可以考虑用自定义的数据类型.sp_addtype 'xh' , rchar（6）'sP_addtype 'kch I rchar （3）'4）默认值对象Xsb：性别默认为X总学分默认为OoKcb:学生人数默认为40,学分默认为2oJsb:zy计算机，fy信息，zc讲师，zw无。Yhb:mm,123456,Xsb：性别默认为1,alter table xsb add constraint bl DEFAULT 1 for xb总学分默认为Ooalter table xsb add constraint b2 DEFAULT 0 for zxfKcb:学生人数默认为40,alter table kcb add constraint b3 DEFAULT 4 0 for xs学分默认为2oalter table kcb add constraint b4 DEFAULT 2 for xfJsb:zy计算机，alter table j sb add constraint b5 DEFAULT '计算机' for zyfy信息，alter table j sb add constraint b6 DEFAULT '信息， for fyzc讲师，alter table j sb add constraint b7 DEFAULT '讲师'for zczw无。alter table j sb add constraint b8 DEFAULT '无' for zwYhb ： mm，123456,alter table yhb add constraint b9 DEFAULT '123456' for mm5)规则和check约束Xsb:zxf范围在0160之间。Kcb： kkxq范围在K8之间,xf在115之间Cjb:cj范围在0100之间Xsb:zxf 在 0160 之间。alter table xsbadd constraint pk_l check (zxf between 0 and 16 0)Kcb:kkxq在1-8之间，xf在115之间alter table kcbadd constraint pk_2 check (kkxq between 1 and 8)alter table kcbadd constraint 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 cjbadd constraint fk_l foreign key (xh) references xsb (xh)cjb (kch) -kcb (kch)alter table cjbadd constraint fk_2 foreign key (kch) references kcb (kch) skb (jsbh)jsb (jsbh)alter table skbadd constraint fk_4 foreign key (j sbh) references j sb (j sbh) skb (kch)kcb (kch)alter table skbadd constraint fk_6 foreign key (kch) references kcb (kch) skb (bj) -xsb (bj) -一用触发器实现 create trigger tl on skbfor insertasbeginif not exists ( select * from xsb where bj= (select bj from inserted)beginprint，插入的班级不存在！，rollback transactionendend必须用触发器实现yhb(yhbh) xsb (xh)和jsb(jsbh)alter trigger t2 on yhb for insert as beginif not exists(select * from xsb where xh= ( select yhbh from inserted)beginif not exists ( select * from j sb where j sbh= (select yhbh from inserted)beginprint，插入的编号出错！'rollback transaction end end end（4） 用户自定义函数（把sql语句贴在下面）（5） 用户自定义存储过程1）根据学号查询学生的选课情况（把sql语句贴在下面）CREATE PROCEDURE xhcxxs xh char （6）asbeginif exists （select * from cjb where 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 f无此课程选课信息，end根据教师编号查询授课情况（把sql语句贴在下面）CREATE PROCEDURE jsbhcxsk jsbh char （6）asbeginif exists ( select * from skb where j sbh=j sbh)select * from skb where j sbh= j sbhelseprint，无此教师授课信息，end3)根据班级查询该班级的授课情况(把sql语句贴在下面)CREATE PROCEDURE bjcxsk bj varchar (50)asbeginif exists ( select * from skb where bj=bj)select * from skb where bj=bjelseprint，无此班级授课信息，end4)根据课程号号查看授课情况(把sql语句贴在下面)CREATE 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表中是否有记录，若有则查找出各个分数段的人数并且更新 到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 KCH=kch)update TJB sat RS2= ( s1ct count (") from CJB where CJ)=60 and CJ <70 and KCH=kch)update TJB sat RS3= ( slect count (*) from CJB where CJ)=70 and CJ <80 and KCH=kch)update TJB set RS4=(select count (*) from CJB where CJ)=80 and CJ 90 and KCH=kch)update TJB set RS5= (select count (*) from CJB where CJ)=90 and CJ ( = 100 and KCH=kch)endexec TJ_CJ '101方法二：如果选择统计的课程在成绩表(QB)已经存在,可以采用下列代码完成统计功能，并且代 码效率较高。但CJB表中没有所选择的课程记录，执行的结果是各个分数段的人数为null。create procedure dbo。 TJ_Cj(KCH varchar (3)as begin delete from TJBinsert into TJB (KCH, RSI, RS2,RS3,RS4, RS5)select KCH,sum( case when CJ 60 then1 else 0 end),sum(case when CJ >= 60 andCJ <= 69 then1else0end),sum(case when CJ ) = 70 andCJ <= 79 then1else0end),sum( case when CJ ) = 80 andCJ = 89 then 1else0end),sum (case when CJ >= 90 and CJ = 100 then 1 else 0 end) from CJB where KCH = KCH end (6)触发器1)实现yhb (yhbh) -xsb(xh)和jsb(jsbh)参照关系-必须用触发器实现(把sql语句贴在下面)create trigger t2 on yhb for insert as beginif not exists(select 大 from xsb where xh= ( select yhbh from inserted)beginif not exists (select * from jsb where jsbh= ( select yhbh from inserted)第一层数据流图 第二层数据流图第三层数据流图（略）（2） 数据字典（每一个实体的详细说明）数据库编号：pxscj基表编号：f1基表英文名称：yhb基表中文名称:用户表字段编号英文字段名中文字段 名字段类型备注1Yhbh用户编号Char (6)2yhm用户名Char 3sf身份Varcha (20)4mm密码Char (6)5bz备注Varcha (50)说明：数据库编号：pxscj基表编号：f2基表英文名称：cjb基表中文名称：成绩表字段编号英文字段名中文字段 名字段类型备注1Xh学号Char(6)2Kch课程号Char(3)print，插入的编号出错！ 1rollback transactionendendend2）实现更新学生信息和老师信息的时候用户表中的信息可以实现级联修改。（把sql语句贴在下面）create trigger xsb_tigger on xsbfor updateasif update （xh）begindeclare new_num varchar （6） , old_num varchar （6）select new_num=xh from insertedselect old_num=xh from deletedupdate yhbset yhbh= new_numwhere yhbh=old_numendcreate trigger j sb_tigger on j sb for updateasif update（j sbh）begindeclare new_num varchar （6）rold_num varchar （6）select new num=j sbh from insertedselect old num=j sbh from deleted update yhbset yhbh=new_numwhere yhbh=old_num end3）当删除学生信息的时候选课记录同时删除，同时用户表的记录也删除.（把sql语句贴在下面）create trigger xsb_dl on xsb for delete asbegindeclare xh varchar （6）select xh=xh from deleteddelete from cjb where xh= xhdelete from yhb where yhbh= xh end4）当删除老师信息的时候授课记录也同时删除，同时用户表中的记录也删除。 （把sql语句贴在下面）create trigger j sb_deletigger on j sb for delete asbegindeclare j sbh varchar （6）select j sbh=j sbh from deleted delete from skb where j sbh= j sbh delete from yhb where yhbh= j sbhend5）增加成绩时成绩大于60应该给学生总学分加之这门课的学分。删除成绩的时候, 如果成绩小于60分，应该把学生的总学分去掉这门课的学分。（把sql语句贴在下面）create trigger cjb_insetigger on cjb for insertasbegindeclare xh varchar （6）declare cj intdeclare xf intdeclare kch char （3）select xh=xh, cj=cj, kch=kch from insertedselect xf=xf from kcb where kch=kchif（cj>=60）beginupdate xsbset zxf=zxf+xfwhere xh=xhendendcreate 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 deletedselect xf=xf from kcb where kch=kchif （cj>=60）beginupdate xsbset zxf=zxfxfwhere xh=xh end end6）通过instead of触发器实现对学生选课情况视图（学号，姓名，课程号，课程名，成绩， 学分，总学分，教师）的插入数据操作、更新数据操作、删除数据操作。（把sql语句贴在下面）cratview vl asselect cjb.xhz xm, xsb.bj,cjbo kch,kcmz CJ, xf, zxf,j sbo j sbh,j sxm from XSB, KCB, CJB,j sb,skbwhere xsb。 XH=C JB. XH and kcb。 KCH=c jbo KCH and j sb. j sbh=skbo j sbh and skbo kch=CJB.KCHcreate trigger vl_t -有问题，待完善 on vl instead of insert asdeclare xh char (6) , xm char (8), kch char (3) , kcm char (16)c j int, j sbh char (6) , j sxm char (8 ) , b j 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 CJ from inserted)set j sbh= ( select j sbh from inserted)set j sxm=(select j sxm 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) begin insert into KCB (KCH, KCM)values (kch, kcm) 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 j sb where j sbh= j sbh)begininsert into j sb (j sbh,j sxm)values (j shhrj sxm)endif not exists ( select * from skb where j sbh= j sbh and kch=kch and bj = bj)begininsert into skb (j sbh, kch, bj )values ( j sbh, kch, bj)endEnd6、运行与维护用java和sql server 2022把系统功能实现。3Cj成绩Int说明：xh参照学生表的xh； kch参照课程表的kch;cj介于0-100之间。数据库编号：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介于0-100之间。数据库编号：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( )62Kch课程号Char ()33Bj班级Varchar(50)4Sj时间Varchar(50)5dd地点Varchar(50)说明:jsbh参照教师表的jsbh； kch参照课程表的kch;数据库编号:pxscj基表编号:f8基表英文名称:CXB基表中文名称：查询表字段编号英文字段名中文字段名字段类型备注1XB性别Bit2XBM性别名Char(4)说明：2、概念设计阶段（1） 分ER图（两个分ER图）学生和课程，2）教师，课程，班级）（2） 总ER图（由分ER图画出总ER图）3、逻辑设计阶段（1） 表关系图（看是否可以画出）（2） 表结构图Xsb结构Kcb结构Cjb结构Yhb结构Jsb结构Skb结构Tjb结构Cxb结构(3)表优化(判断每一个关系是否达到3NF要求，如果没有达到，则继续规范)按照(Inf2nf-> 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, hj)非主属性：sj, dd因为s j, dd彻底依赖于（上加i, kdx bj）,所以属于2NF因为不存在传递函数依赖，所以属于3NFTjb （kch? rsl, rs2, rs3, rs4, rs5）码：（kch）非主属性：rsl, rs2, rs3, rs4, rs5因为rsl, rs2, rs3, rs4, rs5彻底依赖于（kch）,所以属于2NF因为不存在传递函数依赖，所以属于3NFXsb（xh?xm, xb,cssj, bj, zy, zxf, bz,Ixfs, zp）码：他）非主属性：xm, xb, cssj, bj, zy, zxf, bz, Ixfs, zp因为xm, xb, cssj, bj, zy, zxf, bz, Ixfs, zp彻底依赖于（仙，所以属于2NF因为不存在传递函数依赖，所以属于3NFYhb（vhbh, yhm, mm, bz）码:（yhhh）非主属性：yhm, mm, bz因为yhm, mm, bz彻底依赖于（vhbh）,所以属于2NF因为不存在传递函数依赖，所以属于3NF4、物理设计选择合适的DBMS（要求用sql server 2022）5、实施（把sql语句贴在下面）（1）创建数据库（把sql语句贴在下面）create database pxscj1onname=zpxscjl_data1,f ilename= ' f: pxsc j l_data . mdf ', size=3mb,filegrowth=l0 %)log on(name='pxscjl_log1,filename=' f:pxscjl_logo Idf , , size=4mbz maxsize= 6mbzfilegrowth=lmb)(2)创建表(把sql语句贴在下面) create table xsb ( xh char (6) not null primary key, xm char (8) not null, xb bit, cssj datetime, bj varchar (50) not null,zy char (12), zxf int,bz varchar (500),Ixfs varchar (50),