学生档案管理系统数据库设计书样本.doc

目录21系统需求分析21.1课程设计目21.2 开发背景31.3需求分析31.4 开发环境32系统总体设计32.1系统功能描述42.2功能模块图42.3关系模式图43 数据库设计53.1 E-R图53.2关系图53.3创立表、视图、索引脚本93.4完整性约束、自定义约束93.5存储过程53.6触发器54总结361.1课程设计目编写这个再就业服务中心管理信息系统，第一是为了学以致用，将学到知识运用到实际中去，第二也是为了学到更多东西，加深自己对C#和SQL理解。1.2 开发背景随着国内市场经济迅速发展和信息化水平不断提高和网络运用不断普及，如何运用先进管理手段，提高高校管理水平，是当今社会所面临一种课题。各种各样网上管理系统应运而生。1.3 需求分析1.4 开发环境Visual Studio 是微软公司推出开发环境。是当前最流行 Windows 平台应用程序开发环境。Visual Studio 可以用来创立 Windows 平台下 Windows 应用程序和网络应用程序，也可以用来创立网络服务、智能设备应用程序和 Office 插件。 2.1系统功能描述学生档案管理系统涉及顾客管理模块、档案管理模块、成绩管理模块，奖惩管理模块、基本信息管理模块、协助模块。顾客管理模块：对系统顾客进行管理，涉及添加新顾客，对已有顾客查询修改，顾客密码采用md5加密办法进行加密，以提高顾客帐号安全性。档案管理模块：对学生档案信息进行管理，涉及档案录入，档案查询，档案修改，以便管理者进行管理，无需老式手填表式管理，减少了出错也许性。成绩管理模块：对学生成绩进行管理，涉及成绩录入，成绩修改，成绩查询，以便管理人员适时对学生成绩进行操作，同步也以便了管理人员对学生成绩理解。奖惩管理模块：对学生奖惩信息进行记录，修改，查询，让每个人信息可以及时体当前档案上，以便后来查询。基本信息管理模块：对学生某些基本信息进行管理，及时修改学生基本信息，可做到及时性，精确性。协助模块：对学生档案管理系统进行简介，对某些基本信息和用法向顾客做初步简介，以便顾客使用和减少顾客摸索时间，提高顾客工作效率。2.2功能模块图2.3关系模式图3.1 E-R图3.2 关系图班级表ClassClassIDchar(8)不容许空主键classNamechar(20)不容许空DeptIDchar(8)容许空学院表-CollegeCollegeIDchar(8)不容许空主键CollegeNamechar(20)不容许空CollegeSummarynchar(10)容许空课程表CourseCnochar(10)不容许空主键Cnamechar(10)不容许空keshiint不容许空xuefenint不容许空ksxschar(8)不容许空院系表-DeptDeptIDchar(8)不容许空主键DeptNamechar(30)不容许空CollegeIDchar(8)容许空获奖表-HuoJiangSnochar(10)不容许空主键RewardIDchar(8)不容许空主键Timedatetime容许空惩罚表-PunishPunishIDchar(8)不容许空主键PunishNamechar(10)容许空奖励表-RewardRewardIDchar(8)不容许空主键RewardNamechar(10)不容许空RewardLevelchar(10)不容许空住宿表-RoomRoomIDchar(8)不容许空主键RoomAddchar(20)不容许空RoomNumint不容许空成绩表-SCSnochar(10)不容许空主键Cnochar(10)不容许空主键Scoreint容许空学生表-StudentSnochar(10)不容许空主键S_namechar(8)不容许空S_xmpychar(20)不容许空S_cymchar(8)容许空S_sexchar(2)不容许空S_ageint不容许空S_mzchar(10)不容许空S_jgchar(100)不容许空S_csrqdatetime不容许空RoomIDchar(8)容许空ClassIDchar(8)容许空S_sfzhchar(20)不容许空S_imageimage容许空S_zzmmchar(4)不容许空S_csdchar(100)容许空S_sydchar(100)不容许空S_rxnfdatetime不容许空S_byxxchar(20)不容许空S_pyccchar(10)不容许空S_jtzzchar(100)不容许空S_jtlxdhchar(15)容许空S_sjchar(11)容许空顾客表-UsersUidint不容许空主键Unamechar(20)不容许空PassWordchar(20)不容许空违纪表-WeiJiSnochar(10)不容许空主键PunishIDchar(8)不容许空主键Timedatetime不容许空chulijieguotext容许空3.3创立表、视图、索引脚本一、数据库创立：CREATE DATABASE StudentRecordDBON(NAME=StudentRecordDB_data,FILENAME=f:studentrecorddbStudentRecordDB _data.mdf)LOG ON(NAME=StudentRecordDB_log,FILENAME=f:studentrecorddbStudentRecordDB_log.ldf)GO2、班级表：CREATE TABLE Class(ClassID CHAR(8) NOT NULL,ClassName CHAR(20) NOT NULL,DeptID CHAR(8) NULL,)3、院系表CREATE TABLE College(CollegeID CHAR(8) NOT NULL,CollegeName CHAR(20) NOT NULL,CollegeSummary CHAR(10) NULL,)4、课程表CREATE TABLE Course(Cno CHAR(10) NOT NULL,Cname CHAR(10) NOT NULL,keshi int NOT NULL,xuefen int NOT NULL,ksxs char(8) NOT NULL)5、系表：CREATE TABLE Dept(DeptID CHAR(8) NOT NULL,DeptName CHAR(30) NOT NULL,CollegeID CHAR(8) NULL)6、获奖表：CREATE TABLE HuoJiang(Sno CHAR(10) NOT NULL,RewardID CHAR(8) NOT NULL,Time datetime NULL)7、惩罚表：CREATE TABLE Punish(PunishID char(8) NOT NULL,PunishName char(10) NULL)8、奖励表：CREATE TABLE Reward(RewardID char(8) NOT NULL,RewardName char(10) NOT NULL,RewardLevel char(10) NOT NULL)9、宿舍表：CREATE TABLE Room(RoomID char(8) NOT NULL,RoomAdd char(20) NOT NULL,RoomNum int NOT NULL)10、选课表：CREATE TABLE SC(Sno char(10) NOT NULL,Cno char(10) NOT NULL,Score int NULL)11、学生表：CREATE TABLE Student(Sno char(10) NOT NULL,S_name char(8) NOT NULL,S_xmpy char(20) NOT NULL,S_cym char(8) NULL,S_sex char(2) NOT NULL,S_age int NOT NULL,S_mz char(10) NOT NULL,S_jg char(100) NOT NULL,S_csrq datetime NOT NULL,RommID int NULL,ClassID int NULL,S_sfzh char(15) NOT NULL,S_image image NULL,S_zzmm char(4) NOT NULL,S_csd char(100) NULL,S_syd char(100) NOT NULL,S_rxnf datetime NOT NULL,S_byxx char(20) NOT NULL,S_pycc char(10) NOT NULL,S_jtzz char(100) NOT NULL,S_jtlxdh char(15) NULL,S_sj char(11) NULL)12、顾客表：CREATE TABLE Users(Uid int NOT NULL,Uname char(20) NOT NULL,PassWord char(20) NOT NULL)13、违纪登记表：CREATE TABLE WeiJi(Sno char(10) NOT NULL,PunishID char(8) NOT NULL,Time datetime NOT NULL,chulijieguo text NULL)GO二、视图-查询成绩USE StudentRecordDBGOCREATE VIEW SCORE_VIEWAS SELECT Student.Sno，S_name,Cname，Score FROM Student,Course,SC WHERE Student.Sno=sc.Sno AND SC.Cno=Course.CnoGO-查询基本信息USE StudentRecordDBGOCREATE VIEW jbxx_VIEWAS SELECT Student.Sno,S_name,S_sex,S_age,S_jg,S_zzmm,S_rxnf,S_jtzz,S_sj,Class.ClassID,Dept.DeptID,Room.RoomIDFROM Student,Class,Dept,RoomWHERE Student.ClassID=Class.ClassID AND Class.DeptID=Dept.DeptID AND Student.RoomID=Room.RoomIDGO-查询获奖状况USE StudentRecordDBGOCREATE VIEW HJ_VIEWAS SELECT Student.Sno，S_name，RewardName FROM Student,HuoJiang,Reward WHERE Student.Sno=HuoJiang.Sno AND HuoJiang.RewardID=Reward.RewardIDGO-查询违纪状况USE StudentRecordDBGOCREATE VIEW CF_VIEWAS SELECT Student.Sno，S_name，PunishName,chulijieguo FROM Student,WeiJi,Punish WHERE Student.Sno=WeiJi.Sno AND WeiJi.PunishID=Punish.PunishIDGO-通过姓名和课程名查询该生该课程成绩IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=Pro_S_name AND TYPE=p)DROP PROCEDURE Pro_S_nameGOcreate procedure Pro_QScoreS_name_in char(8),Cname_in char(10),Score_out int outputAs select Score_out=Score from student,course,scwhere student.Sno=sc.Sno and course.Cno=sc.Cnoand S_name=S_name_in and Cname=Cname_in三、索引-创立索引use StudentRecordDBgocreate index ind_ClassID on Class(ClassID)create index ind_Cno on Course(Cno)create index ind_DeptID on Dept(DeptID)create index ind_huojiang on HuoJiang(Sno,RewardID)create index ind_PunishID on Punish(PunishID)create index ind_RewardID on Reward(RewardID)create index ind_RoomID on Room(RoomID)create index ind_sc on SC(Sno,Cno)create index ind_Sno on Student(Sno)create index ind_Uid on Users(Uid)create index ind_weiji on WeiJi(Sno,PunishID)go3.4完整性约束、自定义约束-class表约束use StudentRecordDBgoalter table Classadd constraint pk_ClassID primary key(ClassID)go-course表约束use StudentRecordDBgoalter table Courseadd constraint pk_Cno primary key(Cno)go-dept表约束use StudentRecordDBgoalter table Deptadd constraint pk_DeptID primary key(DeptID)go-huojiang表约束use StudentRecordDBgoalter table HuoJiangadd constraint pk_huojiang primary key(Sno,RewardID)go-punish表约束use StudentRecordDBgoalter table Punishadd constraint pk_PunishID primary key(PunishID)go-reward表约束use StudentRecordDBgoalter table Rewardadd constraint pk_RewardID primary key(RewardID)go-room表约束use StudentRecordDBgoalter table Roomadd constraint pk_RoomID primary key(RoomID)go-sc表约束use StudentRecordDBgoalter table SCadd constraint pk_sc primary key(Sno,Cno)go-student表约束use StudentRecordDBgoalter table Studentadd constraint pk_Sno primary key(Sno)go-Users表约束use StudentRecordDBgoalter table Usersadd constraint pk_Uid primary key(Uid)go-weiji表约束use StudentRecordDBgoalter table WeiJiadd constraint pk_weiji primary key(Sno)go-参照完整性use StudentRecordDBgoALTER TABLE SC WITH NOCHECKADD CONSTRAINT FK_student_sc FOREIGN KEY(Sno) REFERENCES Student(Sno)ON DELETE CASCADEALTER TABLE SC WITH NOCHECKADD CONSTRAINT FK_course_sc FOREIGN KEY(Cno) REFERENCES Course(Cno)ON DELETE CASCADEALTER TABLE HuoJiang WITH NOCHECKADD CONSTRAINT FK_student_huojiang FOREIGN KEY(Sno) REFERENCES Student(Sno)ON DELETE CASCADEALTER TABLE HuoJiang WITH NOCHECKADD CONSTRAINT FK_reward_huojiang FOREIGN KEY(RewardID) REFERENCES Reward(RewardID)ON DELETE CASCADEALTER TABLE WeiJi WITH NOCHECKADD CONSTRAINT FK_student_weiji FOREIGN KEY(Sno) REFERENCES Student(Sno)ON DELETE CASCADEALTER TABLE WeiJi WITH NOCHECKADD CONSTRAINT FK_punish_weiji FOREIGN KEY(PunishID) REFERENCES Punish(PunishID)ON DELETE CASCADEuse StudentRecordDBgocreate index ind_ClassID on Class(ClassID)create index ind_Cno on Course(Cno)create index ind_DeptID on Dept(DeptID)create index ind_huojiang on HuoJiang(Sno,RewardID)create index ind_PunishID on Punish(PunishID)create index ind_RewardID on Reward(RewardID)create index ind_RoomID on Room(RoomID)create index ind_sc on SC(Sno,Cno)create index ind_Sno on Student(Sno)create index ind_Uid on Users(Uid)create index ind_weiji on WeiJi(Sno,PunishID)go-class表约束(实体完整性)use StudentRecordDBgoalter table Classadd constraint pk_ClassID primary key(ClassID)go-course表约束(实体完整性)use StudentRecordDBgoalter table Courseadd constraint pk_Cno primary key(Cno)go-dept表约束(实体完整性)use StudentRecordDBgoalter table Deptadd constraint pk_DeptID primary key(DeptID)go-huojiang表约束(实体完整性)use StudentRecordDBgoalter table HuoJiangadd constraint pk_huojiang primary key(Sno,RewardID)go-punish表约束(实体完整性)use StudentRecordDBgoalter table Punishadd constraint pk_PunishID primary key(PunishID)go-reward表约束(实体完整性)use StudentRecordDBgoalter table Rewardadd constraint pk_RewardID primary key(RewardID)go-room表约束(实体完整性)use StudentRecordDBgoalter table Roomadd constraint pk_RoomID primary key(RoomID)go-sc表约束(实体完整性)use StudentRecordDBgoalter table SCadd constraint pk_sc primary key(Sno,Cno)go-student表约束(实体完整性)use StudentRecordDBgoalter table Studentadd constraint pk_Sno primary key(Sno)go-Users表约束(实体完整性)use StudentRecordDBgoalter table Usersadd constraint pk_Uid primary key(Uid)go-weiji表约束(实体完整性)use StudentRecordDBgoalter table WeiJiadd constraint pk_weiji primary key(Sno)go-参照完整性use StudentRecordDBgoALTER TABLE SC WITH NOCHECKADD CONSTRAINT FK_student_sc FOREIGN KEY(Sno) REFERENCES Student(Sno)ON DELETE CASCADEALTER TABLE SC WITH NOCHECKADD CONSTRAINT FK_course_sc FOREIGN KEY(Cno) REFERENCES Course(Cno)ON DELETE CASCADEALTER TABLE HuoJiang WITH NOCHECKADD CONSTRAINT FK_student_huojiang FOREIGN KEY(Sno) REFERENCES Student(Sno)ON DELETE CASCADEALTER TABLE HuoJiang WITH NOCHECKADD CONSTRAINT FK_reward_huojiang FOREIGN KEY(RewardID) REFERENCES Reward(RewardID)ON DELETE CASCADEALTER TABLE WeiJi WITH NOCHECKADD CONSTRAINT FK_student_weiji FOREIGN KEY(Sno) REFERENCES Student(Sno)ON DELETE CASCADEALTER TABLE WeiJi WITH NOCHECKADD CONSTRAINT FK_punish_weiji FOREIGN KEY(PunishID) REFERENCES Punish(PunishID)ON DELETE CASCADE3.5存储过程-通过姓名和课程名查询该生该课程成绩IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=Pro_S_name AND TYPE=p)DROP PROCEDURE Pro_S_nameGOcreate procedure Pro_QScoreS_name_in char(8),Cname_in char(10),Score_out int outputAs select Score_out=Score from Student,Course,SCwhere Student.Sno=SC.Sno and Course.Cno=SC.Cnoand S_name=S_name_in and Cname=Cname_in3.6触发器-建立一种触发器，如果修改Student中学号，则SC、HouJiang、WeiJi中学号自动修改。USE StudentRecordDBGOIF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=Sno_student_tri AND TYPE=tr)DROP TRIGGER Sno_student_triGOcreate trigger Sno_student_trion Student for update as if update(Sno)begindeclare Sno_new char(10),Sno_old char(10)select Sno_new=Sno from insertedselect Sno_old=Sno from deletedupdate SC set Sno=Sno_new where Sno=Sno_oldupdate HuoJiang set Sno=Sno_new where Sno=Sno_oldupdate WeiJi set Sno=Sno_new where Sno=Sno_oldendgo-建立一种触发器，修改SC中一种学号，如果Stdent中存在该学号，则不许修改，如不存在，则可以修改。USE StudentRecordDBGOIF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=Sno_sc_tri AND TYPE=tr)DROP TRIGGER Sno_sc_triGOcreate trigger Sno_sc_trion SC for update as if update(Sno)begindeclare Sno_new char(10),Sno_old char(10),Sno_cnt intselect Sno_old=Sno from deletedselect Sno_cnt=count(*) from Student where Sno=Sno_oldif Sno_cnt0rollback transactionendgo-建立一种触发器，保证成绩在之间。USE StudentRecordDBGOIF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=Score_sc_tri AND TYPE=tr)DROP TRIGGER Score_sc_triGOCREATE TRIGGER Score_sc_triON SC FOR INSERT,UPDATEASDECLARE Score_read intSELECT Score_read=Score FROM INSERTEDIF Score_read 100BEGINPRINT 成绩超过100！请重新输入。Rollback TransactionENDGO-建立一种触发器，保证选课表中记录和相应课程记录一致。USE StudentRecordDBGOIF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=Dc_course_tri AND TYPE=tr)DROP TRIGGER Dc_course_triGOcreate trigger Dc_course_trion Course for delete as declare Cno_del char(10)select Cno_del=Cno from deleteddelete from SC where Cno=Cno_delgo-建立一种触发器，保证系号保持一致。USE StudentRecordDBGOIF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=DeptID_dept_tri AND TYPE=tr)DROP TRIGGER DeptID_dept_triGOcreate trigger DeptID_dept_trion Dept for update as if update(DeptID)begindeclare DeptID_new char(8),DeptID_old char(8)select DeptID_new=DeptID from insertedselect DeptID_old=DeptID from deletedupdate Class set DeptID=DeptID_new where DeptID=DeptID_oldendgo-建立一种触发器，保证学院号保持一致。USE StudentRecordDBGOIF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=CollegeID_college_tri AND TYPE=tr)DROP TRIGGER CollegeID_college_triGOcreate trigger CollegeID_college_trion College for update as if update(CollegeID)begindeclare CollegeID_new char(8),CollegeID_old char(8)select CollegeID_new=CollegeID from insertedselect CollegeID_old=CollegeID from deletedupdate Dept set CollegeID=CollegeID_new where CollegeID=CollegeID_oldendgo-建立一种触发器，保证RewardID保持一致。USE StudentRecordDBGOIF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=RewardID_reward_tri AND TYPE=tr)DROP TRIGGER RewardID_reward_triGOcreate trigger RewardID_reward_trion Reward for update as if update(RewardID)begindeclare RewardID_new char(8),RewardID_old char(8)select RewardID_new=RewardID from insertedselect RewardID_old=RewardID from deletedupdate HuoJiang set RewardID=RewardID_new where RewardID=RewardID_oldendgo-建立一种触发器，保证