数据库原理与应用课程设计-学籍管理系统.doc

数据库原理与应用课程设计题目：数据库系统学院：信息工程学院班级：09信息管理与信息系统姓名：谭艳平学号：200910530227指导老师：成绩： 二一二年一月一、 需求分析1.1设计目的与要求 随着社会的发展，基本上所有的具有一定数量数据的企事业单位或机关部门都开始使用计算机数据库来做管理。当然几乎所有学校也都已经在使用计算机管理数据的机制，大大减少了学校学籍管理的工作量。该课程设计要求设计一个学籍管理的数据库系统，数据库中要求包含学生的基本信息，学校所有专业信息，学校教师信息以及课程设置的信息。同时还要设计它们之间的关系，如学生与课程关系和学生的成绩等，最后还要对一些数据进行查询，如查询学生成绩的各种集合等。我们通过该课程设计，应该达到的目的是把数据库理论知识更加的巩固加深，加强动手能力与实践能力，学以致用，与现实生活中的应用充分的结合起来。1.2设计背景随着我国教育体制改革的深入进行，教育系统得到了前所未有的发展。学生管理正在逐步迈向管理信息现代化。但是我国的学生管理信息化水平还处在初级阶段，主要表现在对学生的交费管理、学生的档案管理还是采用原有的人工管理方式。学校的规模不断扩大,学生数量急剧增加,有关学生的各种信息量也成倍增长。由于大学生类别和专业的设置的多样化，大学生的学籍管理历来是非常繁琐和复杂的的工作。面对庞大的信息量, 这时的人工管理几乎无法实现，在这种情况下用数据库进行管理变的尤为必要，这可以发挥计算机的优势，就需要有学生信息管理系统来提高学生管理工作的效率.通过这样的系统,可以做到信息的规范管理,科学统计和快速的查询,从而减少管理方面的工作量和减少人为的错误。1.3 定义系统数据流程图的一些定义1.4 目标 使得学生的学籍管理工作更加清晰、条理化、自动化。 容易地完成学生信息的查询、更新、修改等操作。 对于学生成绩管理模块，能更加容易的求得学生的平均成绩、最高分、最低分等，可以按照学生的成绩进行排序处理等。1.5 系统模块设计 学籍管理系统大体可以分成四大模块如下图所示， 首先是学生的基本信息模块，里面应该包含学生的各方面的基本信息；再者便是学生成绩管理模块， 在该模块中应该包含有对学生成绩信息的查询和处理，如平均成绩、最好成绩、最差成绩以及不及格学生的统计等功能模块；再其次还有院系、教师、课程等相关信息的模块；最后是系统模块。图 2-1 系统模块图1.6 需求分析本系统是针对高等院校的学生学籍管理，因此学籍管理系统的用户包括系统管理员、教师和学生。主要涉及系部信息、班级信息、任课教师信息、学生信息、课程信息以及选课记录和成绩等多种数据信息。1.系统功能分析l 学生基本信息管理系统管理员负责对各种基本信息的录入、修改、删除等操作。l 成绩管理系统管理员负责对学生成绩的录入、修改、删除等操作，学生可以查询各科成绩。l 院系、教师、课程信息管理学生可以查询课程基本信息和各科的任课老师，老师可以查询基本信息和授课信息，系统管理员负责对学校的院系、教师信息以及课程信息的修改更新，最后再输出所有的信息。l 系统用户管理系统管理员负责用不同的权限来限制不同用户对系统的使用。以上是用户对系统的基本的功能要求，此外用户还要求系统的效率要高，查询速度要比较小的冗余，易维护，具有较高的数据安全性。1.7 数据需求及业务规则分析（1）一所大学有多个学院，每个学院包括学院名称（唯一标识）和院系代号（2）学生由学号唯一标识，需存储学号、姓名、性别、出生年月、家庭住址、班级号、籍贯、名族等信息。其中电话号码课有多个，如宿舍电话和移动电话等；年龄有出生日期和当前日期推算。一个班级有多名学生，但一个学生之属于一个班级。（3）教师由教师号唯一标识，要求记录教师编号、姓名、性别、年龄及工龄的呢过信息。一个学院可以有多个教师，但一名教师只能在一个学院任职。（4）课程由课程号唯一标识，需记录课程号、名称、先行课、学分、备注等信息。一个学院可开设多个课程，但一门课程只能由一个学院开设。（5）学生选修某课程后续保存学生的选修课程信息.（6）某课程考试结束后教师需在规定的时间内讲、将全部学生改课程的考试成绩登入系统，并要求记录登分人和时间。（7）不允许同一学生在同一时间段选修两个及以上开课班级。（8）不允许同一教师在同一时间段讲授两个及两个以上开课班。（9）对选修人数少于15人的开课班需取消或进行开课班合并调整。 1.8 数据项 数据项是数据库的关系中不可再分的数据单位，下表分别列出了数据的名称(即码)、注释、数据类型、长度、取值范围和是否是主码。表 2.1 数据项列表名称注释数据类型长度最小值最大值主要的班级varchar(16)16FALSE学号唯一标识学生的信息integerTRUE院系代号varchar(5)5FALSE专业号varchar(5)5FALSE姓名varchar(6)6FALSE性别varchar(2)2FALSE出生年月datetimeFALSE籍贯varchar(8)8FALSE民族varchar(2)2FALSE家庭住址varchar(20)20FALSE入学时间datetimeFALSE政治面貌varchar(6)6FALSE身份证号char(18)18FALSE联系电话varchar(12)12FALSE备注varchar(14)14FALSE课程号varchar(5)5TRUE先行课smallintFALSE学分integerFALSE备注varchar(16)16FALSE院系代号varchar(5)5TRUE院系名varchar(10)10FALSE系主任varchar(8)8FALSE备注varchar(16)16FALSE教师号varchar(5)5TRUE院系代号varchar(5)5FALSE姓名varchar(6)6FALSE性别char(2)2FALSE年龄integerFALSE工龄integerFALSE备注varchar(16)16FALSE专业号varchar(5)5TRUE院系代号varchar(5)5FALSE专业名称varchar(16)16FALSE备注varchar(16)16FALSE学号integerTRUE课程号varchar(5)5TRUE分数floatFALSE课程号varchar(5)5TRUE教师号varchar(5)5TRUE1.9数据结构数据结构是为反映数据之间的组合关系，即 一个数据结构可以由若干个数据项组成，也可以由若干个数据结构组成，或由若干个数据项和数据结构组成。本学籍管理系统的数据组成可根据系统模块(图 1-1)的划分来设计，即分为四个数据结构，分别如下表所示。表 2.2 数据结构清单数据结构名含义说明组成学生基本信息学生基本信息的描述班级+学号+院系代号+专业号+姓名+性别+出生年月+籍贯+民族+家庭住址+入学时间+政治面貌+身份证号+联系电话+备注;成绩管理学生成绩的管理与查询管理描述学号+课程号+分数;平均分数+最高分+最低分;学生相关信息学校的院系、教师信息以及课程信息的描述课程号+课程名+先行课+学分+备注;院系代号+院系名+系主任+备注;教师号+院系代号+姓名+性别+年龄+工龄+备注;系统管理对系统中的所有表的更新、删除、修改描述1.10数据流数据流是数据结构在系统内传输的路径。表1-3是描述数据流的数据流定义表，其中包含了数据流名、说明、数据流组成、流量与备注等，表1-4是系统中的数据流描述，其中包括输入数据流、输出数据流等的描述。表 2.3 数据流定义表编号数据流名说明数据流组成流量备注L01学生基本信息学生的所有基本信息学生基本信息结构L02学生成绩所有学生的成绩成绩管理结构 L03分类信息同班级学生名单部分学生基本信息结构L04各门成绩学生的平均成绩（包括所有学生和同班级学生的成绩情况）成绩管理结构L05科目成绩学生的单科成绩成绩管理结构L06教师、专业、院系信息学生相关信息L07查询结果L04 | L05L08统计分析L04+L05表 2.4 数据流表使用者名称输入数据流数输出数据流数备注管理员L01+L02+L06L07+L08系统管理员拥有所有权限教务处人员L01+L02+L06L07+L08拥有对学生和教师的管理权限任课教师L02L07可对成绩进行查询与更新学生L01L07查询成绩与学生基本信息1.11. 软硬件平台设计(1) 软件平台： windowsXP操作系统、Sql-Server2000数据库(2) 硬件平台：普通微机二、概念设计21 数据抽象与局部视图设计2.1.1 学生基本信息与课程信息视图 图3-1 学生基本信息视图 图3-2 课程信息视图根据学生与课程的关系可设计如下关系图：图3-3 学生与课程关系视图2.1.2 专业、教师及院系信息视图 图3-4 专业、教师及院系信息视图院系、教师以及专业的关系图如下：图3-5 院系教师以及专业关系图22 系统数据流图 系统的管理模块里面包含教务部门(系统管理员和教务处管理人员)、教师以及学生，下图为该学籍管理系统的系统流图，教务部门拥有对所有表的查询更新与删除权利(分类信息属于查询结果除外)；教师可对教师、专业以及院系信息，分类信息，学生情况进行查询，对成绩单进行查询、更新以及删除等所有操作；学生只可对所有模块信息进行查询，同时可对自己的部分学生情况进行一定的修改与更新。图3-6系统数据流图23 合并生成总E-R 图 合并数据抽象与局部视图设计中的各个局部视图以及各个实体之间的关系生成E-R图如下：图3-7 系统E-R图三、逻辑设计本次设计的数据库在SQL Server 2000上实现，将概念结构设计中的E-R图转换成SQL Sever 2000支持的关系数据模型后，结合上述分析得到如下数据表：（1）学生基本信息表：主键为“学号”名称注释数据类型长度最小值最大值是否是主键班级varchar(16)16FALSE学号唯一标识学生的信息integerTRUE院系代号varchar(5)5FALSE专业号varchar(5)5FALSE姓名varchar(6)6FALSE性别varchar(2)2FALSE出生年月datetimeFALSE籍贯varchar(8)8FALSE民族varchar(2)2FALSE家庭住址varchar(20)20FALSE入学时间datetimeFALSE政治面貌varchar(6)6FALSE身份证号char(18)18FALSE联系电话varchar(12)12FALSE备注varchar(14)14FALSE表1 StudentsInfo表（2）课程表course:主键为“课程号”名称注释数据类型长度最小值最大值是否是主键课程号varchar(5)5TRUE先行课smallintFALSE学分integerFALSE备注varchar(16)16FALSE 表2 StudentsInfo表（3）学院表apartment：主键为“院系代号”名称注释数据类型长度最小值最大值是否是主键院系代号varchar(5)5TRUE院系名varchar(10)10FALSE系主任varchar(8)8FALSE备注varchar(16)16FALSE 表3 apartment表（4）教师信息表Teacher：主键为“教师号”名称注释数据类型长度最小值最大值是否是主键教师号varchar(5)5TRUE院系代号varchar(5)5FALSE姓名varchar(6)6FALSE性别char(2)2FALSE年龄integerFALSE工龄integerFALSE备注varchar(16)16FALSE 表4 teacher表（5）专业表major主键“专业号”名称注释数据类型长度最小值最大值是否是主键专业号varchar(5)5TRUE院系代号varchar(5)5FALSE专业名称varchar(16)16FALSE备注varchar(16)16FALSE 表5major表(6)学生选课表SC：主键（学号，课程号）名称注释数据类型长度最小值最大值是否是主键学号integerTRUE课程号varchar(5)5TRUE分数floatFALSE 表6 sc表(7)教师授课表主键（课程号，教师号）名称注释数据类型长度最小值最大值是否是主键课程号varchar(5)5TRUE教师号varchar(5)5TRUE 表7 course表 因为一个实体模型转换为一个关系模型时遵循以下规则1.一个1：1联系可转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并；2.一个1：n联系可以转换为独立的关系模式，也可以与n端对应的关系模式合并；3.一个m:n联系转换为一个关系模式。与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，各实体的码组成关系的码或关系码的一部分；所以在转换中院系与专业合并到学生基本信息关系中，院系与专业的主码成为学生信息的外码，同样院系的主码成为教师的外码。学生与课程、教师与课程均是多对多的关系所以，单独的生成了必修与教授两个关系。 四、应用安全与设计1 权限分析在学籍管理系统中有三类用户：学生、教师和系统管理员，针对这四类用户的权限做如下规定：l 学生可以查询、浏览自己的学籍信息（包括成绩、选修课程、任课老师信息等），并可以修改自己的个人信息例如家庭住址、联系电话等属性l 教师：登陆后可浏览所属学生的具体个人信息，可对学生的成绩进行输入、修改、更新等操作，但不能修改学生的基本个人信息l 系统管理员即DBA，负责所有数据的全局访问并保证数据库系统的正常运行2 创建用户create user uwith dbacreate user u1with connectcreate user u2with resource3角色创建、授予DBA可以为一组具有相同权限的的用户创建一个角色，然后通过给该角色授权实现对该角色的用户授权。除DBA外，可创建两类类角色：student和teacher角色创建课通过下列语句创建：sp_addrole studentsp_addrole teacher将角色授予特定用户（student角色为学生，teacher角色为相对应的教师）例：Grant student to tan,wang,linGrant teacher to li,zhangGrant select on sc,tc to studentGrant select,update,delete on sc to teacher五、物理结构设计与功能实现51 系统的物理结构设计5.1.1 确定关系模型的存取方法在实际设计中最常用的存取方法是索引发，使用索引可以大大减少数据的查询时间，在建立索引时应遵循：在经常需要搜索的列上建立索引； 在主关键字上建立索引；在经常用于连接的列上建立索引，即在外键上建立索引；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的等规则。才能充分利用索引的作用避免因索引引起的负面作用。5.1.2 确定数据库的存储结构确定数据库的存储结构主要指确定数据的存放位置和存储结构，包括确定关系、索引、日志、备份等的存储安排及存储结构，以及确定系统存储参数的配置。因为该学籍管理系统的数据量小，而且只作实验之用，所以我们只用把数据存储在使用的电脑硬盘上，不用作更多的安排。52 数据库的实施 首先在数据库中建立一个数据库xueji，然后新建一个数据源，生成的表及索引的主要代码如下：-创建课程表-create table Course ( 课程号 varchar(5) not null, 课程名 varchar(10) null, 先行课 smallint null, 学分 int null, 备注 varchar(16) null, constraint PK_COURSE primary key (课程号)go -创建院系表-create table Department ( 院系代号 varchar(5) not null, 院系名 varchar(10) null, 系主任 varchar(8) null, 备注 varchar(16) null, constraint PK_DEPARTMENT primary key (院系代号)go-创建专业表-create table Major ( 专业号 varchar(5) not null, 院系代号 varchar(5) null, 专业名称 varchar(16) null, 备注 varchar(16) null, constraint PK_MAJOR primary key (专业号)go-创建学生与课程的成绩表-create table SC ( 学号 bigint not null, 课程号 varchar(5) not null, constraint PK_SC primary key (学号, 课程号)go-创建学生基本信息表-create table StudentsInfo ( 班级 varchar(16) null, 学号 bigint not null, 院系代号 varchar(5) null, 专业号 varchar(5) null, 姓名 varchar(6) null, 性别 varchar(2) null, 出生年月 datetime null, 籍贯 varchar(8) null, 民族 varchar(2) null, 家庭住址 varchar(20) null, 入学时间 datetime null, 政治面貌 varchar(6) null, 身份证号 char(18) null, 联系电话 varchar(12) null, 备注 varchar(14) null, constraint PK_STUDENTSINFO primary key (学号)go-创建教师与课程关系表-create table TC ( 课程号 varchar(5) not null, 教师号 varchar(5) not null, constraint PK_TC primary key (课程号, 教师号)go-创建 教师表-create table Teacher ( 教师号 varchar(5) not null, 院系代号 varchar(5) null, 姓名 varchar(6) null, 性别 char(2) null, 年龄 int null, 工龄 int null, 备注 varchar(16) null, constraint PK_TEACHER primary key (教师号)Go-建立课程号索引-create index TC_FK on TC (课程号 ASC)go-建立教师号索引-create index TC2_FK on TC (教师号 ASC)go-建立TD_FK 索引-create index TD_FK on Teacher (院系代号 ASC)Go-创建专业号索引-create index SM_FK on StudentsInfo (专业号 ASC)go-创建院系代号索引-create index SD_FK on StudentsInfo (院系代号 ASC)go-在SC表上创建索引SC_FK与SC2_FK-create index SC_FK on SC (学号 ASC)Gocreate index SC2_FK on SC (课程号 ASC)go-在专业表上创建索引-create index DM_FK on Major (院系代号 ASC)goalter table Major add constraint FK_MAJOR_DM_DEPARTME foreign key (院系代号) references Department (院系代号)goalter table SC add constraint FK_SC_SC_STUDENTS foreign key (学号) references StudentsInfo (学号)goalter table SC add constraint FK_SC_SC2_COURSE foreign key (课程号) references Course (课程号)goalter table StudentsInfo add constraint FK_STUDENTS_SD_DEPARTME foreign key (院系代号) references Department (院系代号)goalter table StudentsInfo add constraint FK_STUDENTS_SM_MAJOR foreign key (专业号) references Major (专业号)goalter table TC add constraint FK_TC_TC_COURSE foreign key (课程号) references Course (课程号)goalter table TC add constraint FK_TC_TC2_TEACHER foreign key (教师号) references Teacher (教师号)goalter table Teacher add constraint FK_TEACHER_TD_DEPARTME foreign key (院系代号) references Department (院系代号)go53 触发器与存储过程设计5.3.1 触发器设计1StudentInfo 表上的出发器触发器： 38 建立触发器Supdata:（ 对于学生基本信息表，当学生信息修改时，其它表中若有对应的该信息时，应该对其进行修改）CREATE TRIGGER Supdate ON dbo.StudentsInfo FOR UPDATE AS update SC set 学号 = ( select 学号 from inserted) where SC.学号=(select 学号 from deleted) ; Sdelete:（当学生信息删除时，对应的如SC表中的对应学生成绩信息也应该自动修改）CREATE TRIGGER Sdelete ON dbo.StudentsInfo FOR DELETE AS DELETE FROM SC WHERE SC.学号=(select 学号 from deleted) ;2. Course表上的触发器：其功能是当一个课程的信息改变时，或当一个课程的信息被删除时，相应的在SC和TC表中的相应信息修改或删除。 Cupdata:CREATE TRIGGER Cupdate ON dbo.Course FOR UPDATE AS update SC set 课程号 = ( select 课程号 from inserted) where SC.课程号=(select 课程号 from deleted) update TC set TC.课程号 = ( select 课程号 from inserted) where TC.课程号=(select 课程号 from deleted) ; Cdelete:CREATE TRIGGER Cdelete ON dbo.Course FOR DELETE AS DELETE FROM SC WHERE SC.课程号=(select 课程号 from deleted)DELETE FROM TC WHERE TC.课程号=(select 课程号 from deleted) ; ;3Department表上的触发器：当院系的代号改变时，对应的学生所在院系、专业所属院系以及教师所在院系都应当做相应的更改。 Dupdata:CREATE TRIGGER Dupdate ON dbo.Department FOR UPDATE AS update StudentsInfo set StudentsInfo.专业号 = ( select专业号from inserted) where StudentsInfo.专业号=(select 专业号 from deleted) update Major set Major.专业号 = ( select专业号from inserted) where Major.专业号=(select 专业号 from deleted)update Teacher set Teacher.专业号 = ( select专业号from inserted) where Teacher.专业号=(select 专业号 from deleted);4Teacher 表上的触发器：当教师号改变或教师离任时，TC表上的信息应相应的得到改变。 Tupdata:CREATE TRIGGER Tupdate ON dbo.Teacher FOR UPDATE AS update TC set TC.教师号 = ( select 教师号 from inserted) where TC.教师号=(select 教师号 from deleted) ; Sdelete:CREATE TRIGGER Tdelete ON dbo.Teacher FOR DELETE AS DELETE FROM TC WHERE TC.教师号=(select 教师号 from deleted) ; 5Major表上的触发器：当专业编号发生改变或专业取消，对应的学生表的专业号应该发生改变或置为空值。 Mupdata:CREATE TRIGGER Mupdate ON dbo.Major FOR UPDATE AS update StudentsInfo set 专业号= ( select 专业号 from inserted) where 专业号=(select 专业号 from deleted) ; Mdelete:CREATE TRIGGER Mupdate ON dbo.Major FOR DELETE AS update StudentsInfo set 专业号 = NULL where 专业号=(select 专业号 from deleted) ;5 . 4数据库的试运行 触发器功能的试运行在对StudentsInfo上的SUpdate触发器的验证时，先运行sql语句:select *from dbo.SC;55 数据库的运行和维护 当试运行数据库合格后，数据库开发设计的工作就基本完成了，接下来就是正式运行中的调试，应为该系统比较简单，数据量小，数据库中几乎不会发生什么大的变化，但是还是需要做好数据的备份，在sqlserver中我们可以利用备份数据库的功能对已经设计好的数据做备份，如果数据库受到破坏或系统故障，我们便可轻松的利用备份文件恢复数据库的数据。当然也可以利用其他各种方法进行数据维护。六、存储过程设计下面是部分存储过程的描述，包括插入数据、更新数据与查询数据。 1 . 插入学生基本信息的存储过程： CREATE PROCEDURE TnsertStudentsInfo 班级 varchar(16), 学号 int(4), 姓名 varchar(6), 性别varchar(2), 出生年月 datatime(8), 籍贯varchar(8), 民族varchar(2), 家庭住址 varchar(20), 入学时间 datatime(8),政治面貌varchar(6), 院系代号varchar(5), 专业号varchar(5),身份证号char(18), 电话号码varchar(12), 备注varchar(14), As insert into StudentsInfo(班级,学号, 院系代号,专业号,姓名,性别,出生年月,籍 贯,民族,家庭住址,入学时间,政治面貌,身份证号,电话号码,备注)values (班级,学号, 姓名,性别,出生年月,籍贯,民族,家庭住址,入学时间,政治面貌,院系代号,专业号,身份证号,电话号码,备注)GO例如对于studentinfo表的数据存储，其具体代码为：CREATE PROCEDURE TnsertStudentsInfo 班级varchar(16), 学号char(16), 院系代号varchar(5), 专业号char(10),姓名varchar(8), 性别char(10), 出生年月datatime, 籍贯varchar(30), 民族varchar(10), 家庭住址varchar(20), 入学时间datatime,政治面貌varchar(8), 身份证号char(50), 电话号码varchar(12), 备注varchar(16),asinsert into StudentsInfovalues (信管,10530201,530,001,曾小贤,男,1990-11-22,上海,汉族,东风路号,2009-09-01,群众,199011221111,5889698,无) GOinsert into StudentsInfovalues (信管,10530202,530,001,吕子乔,男,1991-11-22,南京,满族,红旗路号,2009-09-01,党员,199111220000,5889699,无) GOinsert into StudentsInfovalues (信管,10530203,530,001,关谷,男,1990-03-22,湖南,汉族,顺风路号,2009-09-01,团员,199003221132,5889690,无) GOinsert into StudentsInfovalues (信管,10530204,530,001,胡一菲,女,1992-07-18,海南,汉族,浏万路号,2009-09-01,预备党员,199207181234,5889692,无)GOinsert into StudentsInfovalues (信管,10530205,530,001,秦羽墨,女,1991-04-20,广州,汉族,阳光路号,2009-09-01,党员,199204201526,5889694,无)GO2Update 学生成绩信息的存储过程：CREATE PROCEDURE UpdateSC 学号 int(4), 课程号 varchar(5), 分数 float,ASupdate SCset 分数=分数where 学号=学号 and 课程号=课程号GO例如Update 学生成绩信息的存储过程：CREATE PROCEDURE UpdateSCASupdate SCset 分数=85where 学号=530201 and 课程号=0001GO3. 对不有不及格科目的所有学生的信息查询的存储过程CREATE PROCEDURE selectstuendt_1ASSELECT StudentsInfo.学号， 姓名，班级，课程名，分数 FROM StudentsInfo, SC，Course Where 分数 in(0, 60) And StudentsInfo.学号=SC.学号 And SC.课程号=Course.课程号GO4.对数据进行修改、更新表等操作alter table Major add constraint FK_MAJOR_DM_DEPARTME foreign key (院系代号) references Department (院系代号)goalter table SC add constraint FK_SC_SC_STUDENTS f