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

学生档案管理系统数据库设计书1.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 StudentRecordDB