学生选课数据库设计.doc

北京理工大学珠海学院课程设计说明书2011 2012 学年第 1 学期题目: 学生选课数据库设计 学 院： 商学院 专业班级： 学号姓名： 指导教师： 成 绩： 时 间： 2011年 12 月 5 日北京理工大学珠海学院课程设计任务书 2011 2012 学年第 1 学期学生姓名： 专业班级： 指导教师： 工作部门： 商学院 一、课程设计题目学生选课数据库设计二、课程设计内容、选课系统的设计(1) 在选课数据库中，统计出选修课程超过4门以上的学生信息。(2) 检索出成绩及格的、姓王的女同学信息。(3) 统计出平均分在80分以上的学生的信息，并将结果按平均分升序排列。(4)统计出至少有三名学生选修的课程的平均分，统计项包含课程编号和平均分。(5)为每个实体表和关系表创建合适的索引（聚簇索引、复合索引）、主码、外码；(6)创建课程类别取值范围为管理类、电子类、计算机类、机械类的规则，并与相应的列绑定；(7)在选课联系中创建成绩默认值为60的默认，并与相应的列绑定；(8)创建一个可以通过课程类别和任课教师进行查询课程的存储过程；(9)通过触发器定义未选课的学生不能从学生实体中删除；(10)建立包括选课学生的学号，姓名，系别，所选课程号，课程名，和成绩的视图；三、进度安排1需求分析（4学时）2概念模型（4学时）、逻辑模型及关系数据表（4学时）2建库及建表（4学时）、索引（4学时）、视图（4学时）、触发器（4学时）、存储过程（4学时）、事务（4学时）、输入初始数据（4学时）， 共40%3统计报表（两个及以上综合数据统计报表）（4学时）4答辩（4学时）5文档整理（4学时）四、基本要求（1）概念模型设计合理、可扩展性好；（2）数据合理、符合业务场景；（3）数据库对象设计合理、使用得当；（4）数据合理、有效、完整；（5）提交纸制版课程设计报告，应不少于3000字，每人提交1份课程设计报告（Word文档和Sql文档）。 课程负责人签名： 年 月 日学生选课数据库课程设计摘 要1. 目的和意义在当今信息化越来越快的发展的环境下，各学校无论中学大学都争相采用网上选课方式。相比而言，传统选课方式由于不方便、浪费资源等原因而落后了。学生选课系统作为现时代的教学技术，广受学生欢迎。学生选课系统由于其科学性、简便性、效率高、公平性和准确性从而提高学生选课效率。本课程设计目的是探索学生选课的需求分析、概要设计、逻辑设计及规范化、数据库对象设计、数据统计这几个方面的内容。为了更好地在以后的工作中能完成数据库建立和使用等操作。使用SQL Server数据库来设计数据库物理结构设计，包括数据库，数据表，索引、视图、触发器、存储过程、事务等，以达到数据库的操作和使用者的要求。关键词：学生信息查询、选课信息、学生选课信息查询、数据库、数据表 - 22 -目 录第一章设计内容与目标2第二章需求分析32.1 需求背景32.2 选课分析3第三章概要设计43.1 各属性E-R图43.2 总 E-R图5第四章逻辑设计及规范化64.1 关系模型64.2 关系数据表6第五章数据库对象设计95.1 SQL物理结构设计95.2 主要查询功能sql语句135.3 选课系统设计要求内容14第六章数据统计20参考文献21心得体会22第一章 设计内容与目标设计内容：我们组设计的是学生选课管理系统，选课管理系统是学校教务系统中很庞大、很复杂的一个数据库系统，在这里我们将其简化，只选取了部分比较直观的关系模式。我们设计的系统涉及院系信息、学生信息、教师信息、课程信息、选课信息、还有成绩管理及查询。 设计目标： 所实现的功能有：能记录院系、学生、教师以及课程的基本情况，能记录学生所选的课程以及相应的成绩和授课教师。每个学生和老师都只能被一个院系管理，为了简化每个教师最多教授一门课程，每个学生可选多门课程或者不选，每个学生选修的每门课程有一个成绩记载，每个教师教授最多一门课程，学生在系统中可以有查询成绩的权限，管理员有修改成绩的权限。 第二章 需求分析2.1：需求背景随着信息时代科技不断提高,学校规模不断扩大,教学质量不断提高,大学期间有许多公共选修课,以往的选修课方法是课堂报名或者纸上填写方式报名,这种方法虽然直接,但是造成选课的盲目性,而且学生选过课程后不好在更改查看,一方面浪费大量的人力，物力资源,另一方面浪费时间以及在认为统计过程中不可避免的出现差错的情况。这给广大的老师和学生带来诸多的不便,管理起来也相当困难。使用网上选课系统可以提前进行网上选课,并且选课以后学生还可以在查看和更改,随意选择各个老师的课程,老师管理起来也比较方便。鉴于它的重要性,这就迫切需要研制开发一款功能强大,操作简单,具有人性化的网上选课系统。2.2：选课分析根据分析，本系统的实体有学生、教师、课程和选课信息。描述学生的属性有：学号、院系、姓名、性别、出生日期、入学时间、专业、班级。描述教师的属性有：教师编号、姓名、性别、职称、专业。描述课程的属性有：课程编豪、课程名称、授课老师、课程类型、上课时间、上课地点、学时、学分。描述选课信息的属性有：学号、课程编号、考试成绩。每个学生、教师只能属于一个院系，每个学生可选多门课程或者不选，每个教师教授最多一门课程。学生进入查询系统后只能有查询功能，管理员进入则可以有修改成绩的权限。用户登录时需要有一个用户名和密码。第三章 概要设计3.1各属性E-R图学生 出生日期入学时间姓名性别班级专业学号院系教师教师编号职称专业性别姓名课程学分学时上课时间上课地点课程类型授课老师课程名称课程编号 登录 密码用户名选课成绩课程号学号3.2总 E-R图数据库的E-R图：学号学生姓名m性别选课教师编号2姓名性别职称专业出生日期n入学时间mn院系课程教师讲授专业班级学分学时上课地点上课时间课程类型授课教师课程名称课程编号第四章 逻辑设计及规范化 4.1 关系模型： 登陆（用户名、密码）学生（学号、姓名、性别、出生日期、入学时间、院系、专业、班级）教师（教师编号、姓名、性别、职称、专业）课程（课程编号、课程名称、授课教师、课程类型、上课时间、上课地点、学时、学分）选课信息（学号、课程编号、考试成绩）4.2关系数据表: Register属性名数据类型可否为空含义完整性约束条件UsernameChar(10)否用户名passwordChar(10)否密码Student属性名数据类型可否为空含义完整性约束Stunumchar(10)否学号主码Stunamechar(10)否姓名Stusexchar(2)否性别Stubirthchar(10)否出生日期StuadimChar(10)否入学时间Stumajorchar(10)否专业Stucollegechar(10)否院系Stuclasschar(10)否班级Teacher属性名数据类型可否为空含义完整性约束Tenumchar(10)否教师编号主码Tenamechar(10)否姓名Tesexchar(2)否性别Tetitlechar(10)否职称Temajorchar(20)否专业Course属性名数据类型可否为空含义完整性约束Cnochar(10)否教师编号主码Cnamechar(10)否课程名称CteacherChar(10)否授课教师外码Ctypechar(10)否课程类型Ctimechar(10)可以上课时间Cplacechar(10)可以上课地点ChoursChar(10)可以学时CcreditChar(10)可以学分Select_Course属性名数据类型可否为空含义完整性约束Stunumchar(10)否学号主码Cnochar(10)否课程编号resultChar(10)否成绩数据字典：数据项编号数据项名数据项含义存储结构别名JS001Stunum学生编号Char(10)学号JS002Stucollege学生学院Char(20)学院JS003Stuname学生姓名Char(10)姓名JS004Stusex学生性别Char(2)性别JS005Stubirth学生出生日期Char(10)出生日期JS006Stuadim学生入学时间Char(10)入学时间JS007Stumajor学生专业Char(10)专业JS008Stuclass学生班级Char(10)班级JS009Tenum教师编号Char(10)编号JS0010Tename教师姓名Char(10)姓名JS0011Tesex教师性别Char(2)性别JS0012Tetitle教师职称Char(10)职称JS0013Temajor教师专业Char(10)专业JS0014Cno课程编码Char(10)课程号JS0015Cname课程名称Char(20)课程名JS0016Cteacher授课老师Char(10)教师JS0017Ctype课程类型Char(20)类型JS0018Ctime上课时间Char(20)时间JS0019Cplace上课地点Char(20)地点JS0020Chours上课学时Char(10)学时JS0021Ccredit上课学分Char(10)学分JS0022result学生课程成绩Char(10)成绩JS0023Username用户名Char(10)JS0024password密码Char(10)第五章 数据库对象设计5.1 SQL物理结构设计:1.新建数据库：Create database SCdatabase2.新建用户名和密码表：Create table register( username char(10),password2 char(10);3.新建学生表:Create table student(Stunum char(10) NOT NULL primary key ,Stuname char(10) NOT NULL,Stusex char(2) NOT NULL default 男 check(Stusex=男 or Stusex=女),Stubirth char(10) NOT NULL,Stuadim char(10) NOT NULL,Stumajor char(10) NOT NULL,Stuclass char(10) NOT NULL,Stucollege char(10)NOT NULL)insert into student values (s0001,景天,男,19890101,20080901,信管,1班,商学院)insert into student values (s0002,云天河,男,19900102,20080901,信管,2班,商学院)insert into student values (s0003,唐雪见,女,19900901,20090901,信息工程,1班,信息学院)insert into student values (s0004,张琳芃,男,19890509,20080901,信息工程,2班,信息学院)insert into student values (s0005,陈琳,女,19910201,20090901,英语,1班,外语学院)insert into student values (s0006,王小雪,女,19900520,20080901,英语,3班,外语学院)insert into student values (s0007,李大龙,男,19891202,20080901,英语,4班,外语学院)insert into student values (s0008,何天,男,19890915,20070901,数学,1班,数理学院)insert into student values (s0009,卢巧音,女,19910712,20090901,数学,2班,数理学院)insert into student values (s0010,卓不凡,男,19891023,20090901,化学,1班,化工学院)insert into studentvalues (s0011,林达,女,19900805,20080901,化学,5班,化工学院)insert into studentvalues (s0012,林易,女,19900807,20080901,化学,5班,化工学院)select *from student4.新建教师表：Create table teacher(Tenum char(10) NOT NULL primary key,Tename char(10) NOT NULL,Tesex char(2) NOT NULL default 男 check(Tesex=男 or Tesex=女),Tetitle char(10) NOT NULL,Temajor char(20) NOT NULL)insert into teachervalues (t0001,李老师,女,教师,信管)insert into teachervalues (t0002,张老师,女,教师,信息工程)insert into teachervalues (t0003,何老师,男,教授,物理)insert into teachervalues (t0004,陈老师,女,教师,管理经济学)insert into teachervalues (t0005,刘老师,男,教授,物理)insert into teachervalues (t0006,郑老师,男,教师,信管)insert into teachervalues (t0007,王老师,女,教师,信息工程)insert into teachervalues (t0008,黄老师,男,教授,物理)insert into teachervalues (t0009,祝老师,女,教授,管理经济学)insert into teachervalues (t0010,古老师,男,教师,物理)select *from teacher5.新建课程表：Create table course(Cno char(10) NOT NULL primary key,Cname char(20) NOT NULL,Cteacher char(10) NOT NULL,Ctype char(10) NOT NULL,Ctime char(20) NOT NULL,Cplace char(10),Chours char(10),Ccredit char(10),foreign key(Cteacher) references teacher(Tenum);insert into course values (c0001,数据库设计,t0001,计算机类,周一下午、节,JB105,48,4)insert into course values (c0002,java基础,t0002,计算机类,周二上午、节,JB305,64,3)insert into course values (c0003,电子商务,t0004,管理类,周三下午、节,JD201,48,2)insert into course values (c0004,数字控制技术,t0005,电子类,周三下午、节,HH303,48,2)insert into course values (c0005,机械应用,t0003,机械类,周四下午、节,HF406,64,3)insert into course values (c0006,数据结构,t0006,计算机类,周二下午、节,JB103,48,4)insert into course values (c0007,c语言基础,t0007,计算机类,周四上午、节,JB405,48,3)insert into course values (c0008,电子证务,t0009,管理类,周一下午、节,JD301,48,3)insert into course values (c0009,电路板基础,t0008,电子类,周三下午、节,HF302,64,4)insert into course values (c0010,工业设备应用,t0010,机械类,周四下午、节,HF406,64,3)select *from course6.新建选课表：Create table Select_Course(Stunum char(10),Cno char(10),Result Int,Primary key(Stunum,Cno),Foreign key(Stunum)references student (Stunum),Foreign key(Cno)references course(Cno)insert into Select_Coursevalues (s0001,c0001,90)insert into Select_Coursevalues (s0002,c0002,91)insert into Select_Coursevalues (s0003,c0001,92)insert into Select_Coursevalues (s0003,c0002,95)insert into Select_Coursevalues (s0003,c0003,87)insert into Select_Coursevalues (s0003,c0005,85)insert into Select_Coursevalues (s0004,c0003,88)insert into Select_Coursevalues (s0005,c0004,90)insert into Select_Coursevalues (s0006,c0005,89)insert into Select_Coursevalues (s0007,c0001,76)insert into Select_Coursevalues (s0008,c0003,83)insert into Select_Coursevalues (s0009,c0004,79)insert into Select_Coursevalues (s0010,c0005,86)insert into Select_Coursevalues (s0010,c0003,93)select *from Select_Course5.2主要查询功能sql语句：1、登陆管理利用用户和密码登陆检验用户名和密码是否存在和正确select *from register where username=abc and password2=1234562、学生信息管理1)删除学生信息delete from student where stunum =s00122)修改学生信息update student set stunum = , stucollege = , stuname = ,stusex = , stubirth =, stuadim = , stumajor =, stuclass = where snum = ;3)增加学生信息insert into student values (,)3、课程管理1)增加课程insert into course values (, , , ,)2)课程修改update course set Cno = ,Cname=,Cteacher=,Ctype=,Ctime=,Cplace=,Chours=,Ccredit= where cno = 4、查询管理1)学生查询(1)按学号查询select * from student where stunum =(2)按姓名查询select * from student where stuname=(3)按性别查询select * from student where stusex=(4)按专业查询select * from student where Stumajor=2)课程查询(1)按课程号查询select * from Select_Course,course where Select_Course.Stunum = and Select_Course.Cno = course.Cno(2)按授课教师查询select * from Select_Course, teacherwhere Select_Course. Result = and teacher.Tenum=3)成绩查询select * from Select_Course,course,student where Select_Course.stunum = and Select_Course.Cno = course.Cno and Select_Course.Stunum = student.Stunum5.3选课系统设计要求内容：(1) 在选课数据库中，统计出选修课程超过门以上的学生信息。select *from studentwhere student.Stunum=(select Stunumfrom Select_Coursegroup by Stunum having count(Stunum)!4)(2) 检索出成绩及格的、姓王的女同学信息。select * from studentwhere student.Stunum in(select Select_Course.Stunumfrom Select_Coursewhere Select_Course.Result!=80select student.*,平均分 from student join #avgt on student.Stunum=#avgt.Stunumorder by 平均分 asc(4) 统计出至少有三名学生选修的课程的平均分，统计项包含课程编号和平均分。select Cno,avg(Result) as 平均分from Select_Course group by Cno having count(stunum)=3(5)为每个实体表和关系表创建合适的索引（聚簇索引、复合索引）、主码、外码Create unique index stunum On student(Stunum);Create unique index cno ON course(Cno);Create unique index tenum ON teacher(Tenum);(6)创建课程类别取值范围为管理类、电子类、计算机类、机械类的规则，并与相应的列绑定；create rule Ctype as Ctype in (管理类,电子类,计算机类,机械类)goexec sp_bindrule Ctype,Course.Ctypego(7)在选课联系中创建成绩默认值为的默认，并与相应的列绑定；create default df_Result as 60alter table Select_CourseADD CONSTRAINT pk_Results DEFAULT(60) FOR Resultgoexec sp_bindefault df_Result,Select_Course.Result(8)创建一个可以通过课程类别和任课教师进行查询课程的存储过程:create proc proCourse课程类别char(20),任课教师char(10)asselect Cno as 课程号,Cname as 课程名称,Ctype as 课程类别号,Cteacher as 教师编号from course where Ctype=课程类别andCteacher=任课教师测试：EXECUTE proCourse 课程类别=电子类,任课教师=t0005EXECUTE proCourse 课程类别=计算机类,任课教师=t0001(9)通过触发器定义未选课的学生不能从学生实体中删除:create trigger SC_studentdelon student instead of deleteasbegindeclare temp char(10)select temp=Stunum from deletedif (select count(Select_Course.Stunum) from Select_Course where Select_Course.Stunum=temp)=0print未选课的学生不能从学生实体中删除end测试：delete from student where Stunum= s0012delete from student where Stunum= s0010(10)建立包括选课学生的学号，姓名，系别，所选课程号，课程名，和成绩的视图:create view 选课学生的视图asselect S.Stunum, S.Stuname,S.Stucollege, C.Cno, C.Cname, SE.Result from student S join Select_Course SE on S.Stunum=SE.Stunum join course C on C.Cno=SE.CnoSELECT