学生成绩管理系统需求分析说明书

学生成绩管理系统需求分析说明书课程名称： 数据库应用技术 专 业： 计算机科学与技术 班 级： 小成员组：学 号： 姓 名： 学 号： 姓 名： 学 号： 姓 名： 学 号： 姓 名： 学 号： 姓 名： 指导教师： 成 绩： 1. 开发本系统的目标及意义随着科技的发展，用计算机管理学校的信息已经越来越普遍了。由传统的人工操作继而转变为机器操作，但毕竟机器不像人类的思维，它的运行是在人类开发的程序或软件的基础之上运行的，因此它具有一定的局限性。科技在进步，人类的创造力使机器有了很大的提升，更能很好地满足人们的需求。本系统的建立在为了将现代化的计算机技术和传统的教学、教务工作相结合，按照学院的工作流程设计完成的。为了使系统在学院的管理中发挥更大的作用，实现工作工程的计算机化，提高工作效率和工作质量，现提出如下的系统开发目标：1.系统用具有实用性、可靠性和通用性，同时注意到先进性。2.对各个数据库进行动态管理，防止混乱。3.能过按照用户选择的不同的条件进行简单查询和复合查询。4.能够对查询结果行进分类汇总，实现包边打印，5.注意数据的安全性及完整性，具有数据备份和恢复的功能。6.方面用户的操作，尽量减少用户的操作，便于用户快速查到自己所需的信息。开发此系统的不仅能提高学校的管理能力和工作效率，而且还节省了许多人力物力，增强了学校资料的安全性。2. 开发本系统的可行性首先在经济上，本系统的开发能给学校的工作效率带来一个质的飞跃，且计算机的价格也已经十分低廉。其次在技术上，本系统的开发是利用Microsoft SQL Server2000 作为本系统的数据库，它是一个支持多用户的新型数据库，适用于大中规模的数据量需求。使用java语言作为系统开发的环境，它能提供面向对象的图化的设计、类与对象的支持及丰富的数据类型，同时它还具有分布式、解释型、跨平台移植性、高性能、多性能、多线程、动态性、可靠性和安全性的特点，同时也便于对数据库的链接及操作。最后在运行上，由于本系统为一个小型的学生信息管理系统，所耗费的资源非常的小，学校的电脑无论在硬件还是在软件上都能满足条件，因此，本系统在运行上是可行的。3. 总体设计登录成功与否学生教师学生操作与否学生操作与否学生信息查询教师信息查询登录密码修改登录密码修改学生信息变动成绩表寝室表学生表课程表教师表是否成功学生信息修改学生信息增加学生信息删除成功与否是否退出成功与否成功与否是否成功退出4. 各个模块实体间的联系5. 各实体的属性 6. 各实体即联系的二维表-学生（学号，姓名，性别，年龄，出生日期，地址，民族，联系电话）；-选课（学号，课程号，学年，成绩）-课程（课程号，课程名，课时，学分）-教师（教师编号，姓名，性别，年龄，学历，教研室，职称，电话,工资）；-授课（教师编号，课程号，教学质量）-班级（班级号，年级，年制，专业，指导员）；-专业（专业号，专业名）-学院（学院编号，学院名称）；-用户（用户名，密码，权限）-学费（专业号，年制，学年，学费）；-学生寝室（学号，寝室号，区号，床号，学年，值日表现，备注）；-交费（学号，专业号，交费日期，操作员）-考试信息（学号，课程号，考试教室，监考老师，考试人数，开考时间，结束时间）-贷款（学号，专业号，学年，是否欠交学费，是否有贫困证明，备注（贫困生）7. 建立二维表并插入数据-学生（学号，姓名，性别，年龄，出生年月，身份证号，家庭住址，民族，联系电话）；create table student(sno varchar(10) primary key,sname varchar(8),ssex char(2), sage int,sbirthday datetime,sid varchar(18),sadress varchar(50),snational char(10),stel varchar (15)drop table studentinsert into student values(11321136,王帅,男,22,1989-10-29 浙江省金华市,汉insert into student values(11321128,索全明,男,26,1986-06-23 河北省邯郸市,汉insert into student values(11321141,陈东,男,23,1989-09-15 辽宁省锦州市,满insert into student values(11321132,丁明明,男,23,1988-06-25 辽宁省沈阳市,汉insert into student values(11321133,郁苛鑫,男,24,1988-04-23 四川省成都市,汉,1824233)insert into student values(11334208,王林,女,22,1990-02-23辽宁省盘锦市,汉insert into student values(11334215,杨丽,女,23,1989-06-18吉林省通化市,汉insert into student values(11334153,李治,男,21,1991-01-08浙江省杭州市,汉insert into student values(11331136,张成龙,男,23,1989-05-25辽宁省沈阳市,汉insert into student values(11331145,王阳阳,男,21,1991-07-21辽宁省沈阳市,汉insert into student values(11307108,吴林,女,20,1992-03-16江苏省徐州市,畲族insert into student values(10307150,林海,男,18,1994-01-25辽宁省鞍山市,满insert into student values(10307151,尹星,男,18,1994-06-12山东省泰安市,蒙族insert into student values(10306150,李罗娜,女,19,1993-06-30辽宁省朝阳市,汉insert into student values(10306151,雷宁,男,20,1992-07-27河南省开封市,汉-选课（学号，课程号，成绩）create table sc(sno varchar(10),cno varchar(10),grade tinyint constraint pk3 primary key(sno,cno)drop table scinsert into sc values(11321128,c01,68)insert into sc values(11321128,c05,83)insert into sc values(11321128,c11,69)insert into sc values(11321136,c02,88)insert into sc values(11321136,c05,76)insert into sc values(11321141,c09,58)insert into sc values(11321132,c05,78)insert into sc values(11321132,c10,49)insert into sc values(11321133,c04,91)insert into sc values(11321133,c07,84)insert into sc values(10306150,c05,86)insert into sc values(10306150,c08,73)insert into sc values(10306151,c03,90)insert into sc values(10306151,c08,86)insert into sc values(10307150,c06,91)insert into sc values(10307150,c09,82)insert into sc values(10307151,c06,81)insert into sc values(10307151,c11,73)insert into sc values(11334208,c01,59)insert into sc values(11334208,c07,72)insert into sc values(11334215,c02,64)insert into sc values(11334215,c10,75)insert into sc values(11331136,c11,null)insert into sc values(11331136,c09,63)insert into sc values(11331145,c04,68)insert into sc values(11331145,c13,78)-课程（课程号，课程名，课程类型，学时，学分）create table course(cno varchar(10)primary key,cname varchar(50),ctype varchar(10),ctime tinyint,credites decimal(3,1)drop table courseinsert into course values(c01,高等数学,必修课,120,15)insert into course values(c02,c语言程序设计,必修课,80,10)insert into course values(c03,c#程序设计,专业课,60,7.5)insert into course values(c04,vb程序设计,选修课,32,4)insert into course values(c05,数据库原理及应用,专业课,60,7.5)insert into course values(c06,计算机网络技术,专业课,60,7.5)insert into course values(c07,操作系统,专业课,48,6)insert into course values(c08,数据库访问技术,专业课,32,4)insert into course values(c09,网络互联设备,必修课,48,6)insert into course values(c10,中小企业网络架构,选修课,32,4)insert into course values(c11,数据结构,选修课,48,6)insert into course values(c12,舞蹈欣赏,选修课,24,3)insert into course values(c13,数学建模,选修课,24,3)-教师（教师编号，姓名，性别，年龄，学历，教研室，专业名，电话,工资）；create table teacher(tno varchar(10) primary key,tname varchar(10),tsex char(2),tage int ,tdegree text,t_r_no varchar(15),sprof varchar(50),ttel char(13),salary decimal(7,2)drop table teacherinsert into teacher values(t01,李星华,男,41,研究生,信息教研室,计算机科学与技术5000)insert into teacher values(t02,马超,男,39,本科,数学教研室,计算机科学与技术3500)insert into teacher values(t03,李梅,女,32,硕士,机械教研室,机械自动化6500)insert into teacher values(t04,马汉,男,38,研究生,英语教研室,通信工程5000)insert into teacher values(t05,董华,女,35,副教授,信息教研室,电子自动化7500)insert into teacher values(t06,王艳,女,40,教授,数学教研室,测控技术与仪器8000)-授课（教师编号，课程号，教学质量）create table teach(tno varchar(10),cno varchar(10),quality varchar(6)default优 constraint pk8 primary key(tno,cno)drop table teachinsert into teach values(t01,c11,良好)insert into teach values(t02,c08,)insert into teach values(t03,c06,良好)insert into teach values(t04,c13,)insert into teach values(t05,c05,及格)insert into teach values(t06,c01,)insert into teach values(t03,c07,良好)insert into teach values(t04,c10,)insert into teach values(t05,c02,及格)insert into teach values(t06,c04,良好)-班级（班级号，年级，年制，专业号，辅导员）；create table class(classno varchar(8) primary key,classgrade char(2) ,classyears char(4),spino varchar(10) ,classtutor varchar(8)drop table classinsert into class values(113342,11,三年,34,刘新宇)insert into class values(113211,11,二年,21,赵义)insert into class values(113071,11,四年,07,张程潜)insert into class values(103061,10,四年,06,李成器)insert into class values(113311,11,四年,31,赵义)insert into class values(083342,08,三年,34,刘新宇)insert into class values(112012,11,三年,01,张程潜)-专业（专业号，专业名）create table spi_info(spino varchar(10)primary key,sprof varchar(50)insert into spi_info values(21,计算机科学与技术)insert into spi_info values(07,机械自动化)insert into spi_info values(34,计算机网络技术)insert into spi_info values(06,通信工程)insert into spi_info values(31,电子自动化)insert into spi_info values(05,测控技术与仪器)insert into spi_info values(08,机电一体化)-学院（学院编号，学院名称）；create table school_info(schno varchar(10)primary key,schname varchar(30)insert into school_info values(2,经济与管理学院)insert into school_info values(3,信息与控制学院)insert into school_info values(4,机械与运载学院)insert into school_info values(5,艺术与传媒学院)-用户（用户名，密码，权限）create table use_info(uadmin varchar(8) primary key,upass varchar(8) not null,ulimited varchar(10)insert into use_info values(索全明,11321128,学生登录)insert into use_info values(陈东,11321141,学生登录)insert into use_info values(李成器,12345678,教师登录)insert into use_info values(刘新宇,10987654,教师登录)insert into use_info values(王帅,10295615,管理员登录)-学费（专业号，年级，年制，学年，学费）；create table money_info(spino varchar(10), classgrade char(2), classyears char(4), schoolyear char(8),tuition money constraint pk6 primary key(spino,schoolyear,schoolyear)drop table money_infoinsert into money_info values(06,10,四年,第一学年,14500)insert into money_info values(06,10,四年,第二学年,14500)insert into money_info values(06,10,四年,第三学年,14800)insert into money_info values(06,10,四年,第四学年,14800)insert into money_info values(31,11,四年,第一学年,14500)insert into money_info values(31,11,四年,第二学年,14800)insert into money_info values(31,11,四年,第三学年,15000)insert into money_info values(31,11,四年,第四学年,15200)insert into money_info values(21,11,二年,第一学年,4800)insert into money_info values(21,11,二年,第二学年,4800)insert into money_info values(34,11,三年,第一学年,5000)insert into money_info values(34,11,三年,第二学年,5000)insert into money_info values(34,11,三年,第三学年,5000)create table payfor(sno varchar(10)not null,spino varchar(10) not null,paydate smalldatetime,operator varchar(8)alter table payfor add constraint pk1 primary key(sno,spino) alter table payfor add constraint fk1 foreign key payfor.sno references student(sno) drop table payforinsert into payfor values(11321128,21,2011-8-27,12345)insert into payfor values(11321141,21,2011-8-28,12365)insert into payfor values(10306150,06,2011-8-27,12345)insert into payfor values(11321132,21,2011-8-26,12365)insert into payfor values(10307150,07,2011-8-27,12365)insert into payfor values(10307151,07,2011-8-28,12345)insert into payfor values(11331145,31,2011-8-26,12355)insert into payfor values(11331136,31,2011-8-27,12345)insert into payfor values(11334215,42,2011-8-28,12355)insert into payfor values(11334208,42,2011-8-27,12345)create table studorm(sno varchar(10), sdorm char(6) ,studormarea varchar(6),studormbed char(2) ,studormduty varchar(6),remarks textconstraint pk9 primary key(sno,sdorm,studormbed)drop table studorminsert into studorm values(10306150,2#205,西区,2,优秀,)insert into studorm values(10306151,4#521,西区,4,及格,)insert into studorm values(10307150,7#309,东区,6,良好,)insert into studorm values(10307151,5#315,西区,3,良好,)insert into studorm values(11334208,13#205,东区,5,优秀,)insert into studorm values(11321136,4#106,西区,2,不及格,)insert into studorm values(11334208,1#403,西区,1,良好,)insert into studorm values(11334215,11#206,东区,4,及格,)-考试信息（学号，课程号，考试教室，监考老师，考试人数，开考时间，结束时间）create table grade_info( sno varchar(10), cno varchar(10), eroom char(6), tname varchar(10), enum char(3), estartime smalldatetime, endtime smalldatetime constraint pk5 primary key(sno,cno)insert into grade_info values(11321136,c05,6#507,赵宏斌,60,2011-12-06 9:00,2011-12-06 10:00)insert into grade_info values(11321128,c05,6#507,赵宏斌,60,2011-12-06 9:00,2011-12-06 10:00)insert into grade_info values(11334208,c1,6#307,马超,40,2011-11-09 14:00,2011-11-09 15:30)insert into grade_info values(11324215,c10,5#120,王艳,70,2012-01-05 14:00,2012-01-05 16:00)insert into grade_info values(11331136,c11,6#307,马汉,60,2011-12-25 9:00,2011-12-25 11:00)insert into grade_info values(11331145,c04,6#512,李梅,45,2011-12-28 9:00,2011-12-28 11:00)insert into grade_info values(10306150,c09,5#126,王艳,80,2011-12-06 9:00,2011-12-06 11:00)insert into grade_info values(10306151,c11,4#212,李星华,55,2011-11-12 18:00,2011-11-12 19:30)insert into grade_info values(10307150,c08,6#507,赵宏斌,48,2012-01-09 14:00,2012-01-09 16:00)insert into grade_infovalues(10307151,c03,6#507,李梅,63,2011-12-30 14:00,2011-12-30 16:00)7. 查询二维表的一些SQL语句一简单Select语句的查询1 -查询男生人数select count(sno) 男生人数from studentwhere ssex=男2 -查询籍贯不为河南,男生的学生信息select *from studentwhere snational河北 and ssex=男3 -查询成绩分以上的男生和女生分别多少人select ssex,count(student.sno) 人数from student,choose_infowhere choose_info.sno=student.sno and grade8group by ssex4 -查询-8-27时交学费学生的信息select * from student join payfor_info on (student.sno=payfor_info.sno)where paydate=2011-8-275 -查询除了高等数学的必修课的课程名和学分select cname,creditesfrom course where ctype=必修课 and cname in(select cnamefrom coursewhere cname!=高等数学 )6 -查询课程名字中带“设计”的课程名select cnamefrom coursewhere cname like %设计%7 -视图查询各专业在-8-28号交费的学生学号和姓名create view v_stu1asselect spino,student.sno,sname,paydatefrom student join payfor on(student.sno=payfor.sno)group by spino,student.sno,sname,paydatehaving paydate=2011-8-28select * from v_stu18 -查询学生学习课程名为数据库原理及应用的学生学号、姓名、课程号、课程名及教师编号、姓名select student.s