数据库设计报告——学籍管理系统.doc

数据库设计报告——学籍管理系统

———————————————————————————————— 作者：
———————————————————————————————— 日期：

2

个人收集整理 勿做商业用途

学籍管理系统设计报告

1 设计内容与要求
1．1 设计题目 学籍管理系统
1．2 设计目的与要求
该课程设计要求设计一个学籍管理的数据库系统，数据库中要求包含学生的基本信息，学校所有专业信息,学校教师信息以及课程设置的信息。同时还要设计它们之间的关系，如学生与课程关系和学生的成绩等，最后还要对一些数据进行查询，如查询学生成绩的各种集合等.我们通过该课程设计,应该达到的目的是把数据库理论知识更加的巩固加深，加强动手能力与实践能力，学以致用,与现实生活中的应用充分的结合起来。
1．3 设计环境
在Windows 环境下利用作为数据库管理系统对数据进行管理与维护。
2 需求分析
2．1 系统功能要求设计
² 使得学生的学籍管理工作更加清晰、条理化、自动化.
² 容易地完成学生信息的查询、更新、修改等操作。
² 对于学生成绩管理模块，能更加容易的求得学生的平均成绩、最高分、最低分等，　　可以按照学生的成绩进行排序处理等。
2．2 系统模块设计
学籍管理系统大体可以分成四大模块如下图所示， 首先是学生的基本信息模块，里面应该包含学生的各方面的基本信息;再者便是学生成绩管理模块， 在该模块中应该包含有对学生成绩信息的查询和处理,如平均成绩、最好成绩、最差成绩以及不及格学生的统计等功能模块；再其次还有院系、教师、课程等相关信息的模块;最后是系统模块.

图 2—1 系统模块图
2．3 数据字典
2.3.1 数据项
数据项是数据库的关系中不可再分的数据单位，下表分别列出了数据的名称(即码)、注释、数据类型、长度、取值范围和是否是主码。
表 2。1 数据项列表
名称
注释
数据类型
长度
最小值
最大值
主要的
班级

varchar（16)
16

FALSE
学号
唯一标识学生的信息
integer

TRUE
院系代号

varchar(5)
5

FALSE
专业号

varchar(5）
5

FALSE
姓名

varchar(6)
6

FALSE
性别

varchar（2）
2

FALSE
出生年月

datetime

FALSE
籍贯

varchar（8)
8

FALSE
民族

varchar(2)
2

FALSE
家庭住址

varchar(20)
20

FALSE
入学时间

datetime

FALSE
政治面貌

varchar（6）
6

FALSE
身份证号

char(18）
18

FALSE
联系电话

varchar(12）
12

FALSE
备注

varchar（14）
14

FALSE
课程号

varchar(5)
5

TRUE
先行课

smallint

FALSE
学分

integer

FALSE
备注

varchar(16）
16

FALSE
院系代号

varchar（5）
5

TRUE
院系名

varchar(10)
10

FALSE
系主任

varchar（8）
8

FALSE
备注

varchar（16)
16

FALSE
教师号

varchar（5)
5

TRUE
院系代号

varchar（5)
5

FALSE
姓名

varchar（6）
6

FALSE
性别

char(2)
2

FALSE
年龄

integer

FALSE
工龄

integer

FALSE
备注

varchar（16）
16

FALSE
专业号

varchar(5）
5

TRUE
院系代号

varchar（5)
5

FALSE
专业名称

varchar（16）
16

FALSE
备注

varchar（16)
16

FALSE
学号

integer

TRUE
课程号

varchar(5）
5

TRUE
分数

float

FALSE
课程号

varchar（5)
5

TRUE
教师号

varchar(5)
5

TRUE

2。3。2 数据结构
数据结构是为反映数据之间的组合关系，即 一个数据结构可以由若干个数据项组成，也可以由若干个数据结构组成,或由若干个数据项和数据结构组成。本学籍管理系统的数据组成可根据系统模块(图 1-1)的划分来设计，即分为四个数据结构，分别如下表所示。

表 2.2 数据结构清单
数据结构名
含义说明
组成
学生基本信息
学生基本信息的描述
班级+学号+院系代号+专业号+姓名+性别+出生年月
+籍贯+民族+家庭住址+入学时间+政治面貌+身份证号+联系电话+备注;
成绩管理
学生成绩的管理
与查询管理描述
学号+课程号+分数;
平均分数+最高分+最低分；
学生相关信息
学校的院系、教师信息以及课程信息的描述
课程号+课程名+先行课+学分+备注；
院系代号+院系名+系主任+备注；
教师号+院系代号+姓名+性别+年龄+工龄+备注；
系统管理
对系统中的所有表的更新、删除、修改描述
—————-
2.3.3 数据存储
数据存储是数据结构停留或保存的地方，也是数据流的来源和去向之一.其主要包括数据存储名、说明、编号、输入的数据流与输出的数据流、组成、数据量，存取频度、存取方式。下表表示了该学籍管理系统中的数据存储描述。

表 2。5 数据存储描述
数据存储名
组成
存取频度
存取方式
学生基本信息表
学生基本信息结构
大
顺序检索、更新
院系教师及专业表
学生相关信息数据结构
小
顺序检索、更新
学生成绩表
成绩管理结构
一般
顺序检索、更新
3 概念结构设计
3．1 数据抽象与局部视图设计
3.1。1 学生基本信息与课程信息视图

图3-1 学生基本信息视图 图3-2 课程信息视图

根据学生与课程的关系可设计如下关系图：

图3—3 学生与课程关系视图
3。1。2 专业、教师及院系信息视图

图3—4 专业、教师及院系信息视图

院系、教师以及专业的关系图如下:

图3—5 院系教师以及专业关系图
3．2 系统数据流图
系统的管理模块里面包含教务部门（系统管理员和教务处管理人员)、教师以及学生，下图为该学籍管理系统的系统流图，教务部门拥有对所有表的查询更新与删除权利（分类信息属于查询结果除外）；教师可对教师、专业以及院系信息,分类信息，学生情况进行查询,对成绩单进行查询、更新以及删除等所有操作；学生只可对所有模块信息进行查询，同时可对自己的部分学生情况进行一定的修改与更新。

图3-6系统数据流图
3．3 合并部分视图生成E-R 图
合并数据抽象与局部视图设计中的各个局部视图以及各个实体之间的关系生成E-R图如下:

图3—7 系统E-R图
3．4 概念模型设计
在生成了系统E-R图后，可根据E—R图中实体的各个属性组成以及实体与实体之间的关系用PowerDesigner设计概念模型。最后生成概念模型如下图所示：

图3—8 系统概念模型设计图
4 逻辑结构设计
4．1 概念模型向物理模型的转换
在概念模型设计好了之后,可直接在powerdesigner 中将设计好了的概念模型转换成物理模型。其具体操作过程为 点击主菜单的 ToolsàGenerate Physical Data Model 便可以将当前的概念模型转换为物理模型，转换后的物理模型如下图所示:

图4-1 物理模型图
因为一个实体模型转换为一个关系模型时遵循以下规则1.一个1：1联系可转换为一个独立的关系模式,也可以与任意一端对应的关系模式合并；2。一个1：n联系可以转换为独立的关系模式，也可以与n端对应的关系模式合并；3.一个m:n联系转换为一个关系模式.与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，各实体的码组成关系的码或关系码的一部分；所以在转换中院系与专业合并到学生基本信息关系中，院系与专业的主码成为学生信息的外码，同样院系的主码成为教师的外码。学生与课程、教师与课程均是多对多的关系所以,单独的生成了必修与教授两个关系。
4．2 数据库物理模型设计
在PowerDesigner中将概念模型转换成物理模型之后，我们可以对物理模型进行设计，双击物理模型的关系，便弹出对其设置的对话框，在General选项卡中可以对该关系的名称、注释以及码等信息进行设置。在Columns选项卡中可对该关系的属性列进行设计，可分别设置其名称、码、数据类型以及主码、是否为空等。

下图显示了课程与院系两个关系的物理设计其中课程号与院系代号分别为两个关系的住码：

图4—2 课程与院系的物理设计

下图是教师与专业两个关系的物理设计，起中教师号与专业号分别为主码，因为教师与专业均从属于院系，所以院系代号分别是两个关系的外码：

图4—3 教师与专业的物理设计

SC与TC关系分别是学生基本信息与课程,教师与课程两个多对多联系合并生成的关系。SC关系描述了学生选择什么课程以及该课程的分数,其中来自两个关系的主码组成该关系的主码同时也是外码；TC关系是教师与课程的联系，其描述了每个教师所教授的课程关系。

图 4-4 SC与TC物理设计

下面是学生基本信息的物理模型设计，学号作为主码,因为学生基本信息与专业、院系的对应关系均是n：1的联系，所以院系代号与专业号作为学生基本信息关系的外码。

图4—5 学生基本信息物理设计

5 物理结构设计与功能实现
5．1 系统的物理结构设计
5。1。1 确定关系模型的存取方法
在实际设计中最常用的存取方法是索引发，使用索引可以大大减少数据的查询时间,在建立索引时应遵循：在经常需要搜索的列上建立索引；  在主关键字上建立索引；在经常用于连接的列上建立索引，即在外键上建立索引；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序,其指定的范围是连续的等规则。才能充分利用索引的作用避免因索引引起的负面作用。
5.1。2 确定数据库的存储结构
确定数据库的存储结构主要指确定数据的存放位置和存储结构，包括确定关系、索引、日志、备份等的存储安排及存储结构,以及确定系统存储参数的配置。因为该学籍管理系统的数据量小，而且只作实验之用，所以我们只用把数据存储在使用的电脑硬盘上，不用作更多的安排。
5．2 数据库的实施与数据载入
5。2。1 数据库的实施
首先在数据库中建立一个学生数据库，然后新建一个数据源,在porwerDesigner中先建立连接 Database——>Connect ，然后在弹出的对话框中选择数据源，填好数据库的用户名和密码(在本连接中用户名为sa密码为空)，然后连接，最后检测数据是否连接正确。在数据库连接好了之后点击 Database—〉 Generate Database 将弹出的对话框的Generation 单选按扭选择为 ODBC Generation然后选择确定便可执行数据库的实施,生成的主要代码如下：
/＊==============================================================＊/
/* DBMS name： Microsoft SQL Server 2000 */
/* Created on: 2006—1-3 10：56:43 ＊/
/*==============================================================*/
----——----——---——---—-—创建课程表————---——---—-—--——
create table Course (
课程号 varchar(5) not null,
课程名 varchar(10） null,
先行课 smallint null，
学分 int null，
备注 varchar（16) null,
constraint PK_COURSE primary key (课程号）
)
go
execute sp_addextendedproperty 'MS_Description'，
'学校所有课程描述’，
'user’， ''， ’table'， 'Course'
go

——------——--—--———---—-创建院系表-—-—-—-—--——-—-—---
create table Department （
院系代号 varchar（5) not null,
院系名 varchar（10) null,
系主任 varchar(8） null，
备注 varchar（16） null，
constraint PK_DEPARTMENT primary key (院系代号)
）
go
execute sp_addextendedproperty ’MS_Description’,
'学校所有的院系信息描述',
'user', ’’， 'table’, 'Department'
go

---—--————————-—-———--—创建专业表----———-——-—----—--
create table Major （
专业号 varchar（5） not null,
院系代号 varchar（5） null，
专业名称 varchar(16) null，
备注 varchar(16） null,
constraint PK_MAJOR primary key (专业号)
)
go
execute sp_addextendedproperty 'MS_Description’，
'学校所有专业信息描述',
'user'， ’’, ’table’, ’Major’
go

----——---———-————---———在专业表上创建索引—-——-—--——--—---—-—
create index DM_FK on Major (
院系代号 ASC
)
go

—---———---————--—--—-——创建学生与课程的成绩表——-—--—-——-—-—-—---—-—
create table SC (
学号 bigint not null,
课程号 varchar(5） not null,

constraint PK_SC primary key (学号， 课程号）
)
go

—-—-————-—--—-—-----—--在SC表上创建索引SC_FK与SC2_FK-----——-——---—--—-—
create index SC_FK on SC (
学号 ASC
）
Go
create index SC2_FK on SC （
课程号 ASC
)
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
execute sp_addextendedproperty 'MS_Description',
'学生基本信息描述’,
’user’， '', ’table’， 'StudentsInfo’
go
--———-——--———--———----—--——创建院系代号索引-——-———--——-———-———--————-—---
create index SD_FK on StudentsInfo (
院系代号 ASC
)
go

————---—--—-—-—---—-——-———创建专业号索引—----—-------——-—-—--——--—-—--
create index SM_FK on StudentsInfo (
专业号 ASC
）
go

--—-—---—---—-—-—-—--——---—创建教师与课程关系表--——-———-—--—--—--——---————-——
create table TC (
课程号 varchar(5) not null，
教师号 varchar(5） not null,
constraint PK_TC primary key （课程号, 教师号)
)
go

—-————-—--——-—-———-————————建立课程号索引-———-—--———-———-—-----——-—-—-—
create index TC_FK on TC （
课程号 ASC
)
go
——-——-———-—-——-———-—----—-建立教师号索引—-———-——--—---——---——--—-—----
create index TC2_FK on TC (
教师号 ASC
）
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

execute sp_addextendedproperty ’MS_Description',
’学校教师信息描述'，
’user’, ’’， ’table’， ’Teacher'
go

—————-——----—————-————-—--—建立TD_FK 索引-——————-—----—-—-——-————--——-—
create index TD_FK on Teacher （
院系代号 ASC
)
go
alter table Major
add constraint FK_MAJOR_DM_DEPARTME foreign key (院系代号）
references Department （院系代号）
go
alter table SC
add constraint FK_SC_SC_STUDENTS foreign key （学号）
references StudentsInfo (学号）
go
alter table SC
add constraint FK_SC_SC2_COURSE foreign key （课程号)
references Course （课程号）
go
alter table StudentsInfo
add constraint FK_STUDENTS_SD_DEPARTME foreign key （院系代号）
references Department (院系代号)
go
alter table StudentsInfo
add constraint FK_STUDENTS_SM_MAJOR foreign key (专业号)
references Major （专业号）
go
alter table TC
add constraint FK_TC_TC_COURSE foreign key (课程号)
references Course (课程号)
go
alter table TC
add constraint FK_TC_TC2_TEACHER foreign key (教师号)
references Teacher (教师号）
go
alter table Teacher
add constraint FK_TEACHER_TD_DEPARTME foreign key (院系代号）
references Department (院系代号）
go
5。2。2 数据库中表的设计
当上面的代码运行成功后后，在数据库的Student中便会生成相应的表,包括学生基本信息表、课程表、教师,专业和学院表、以及SC和TC表.数据库中生成表后，可以对其进行设计，如下图便是数据库中的学生基本信息表，我们可以对其每一列以及其数据类型、取值范围和是否是主码是否可以为空等信息进行设置后或修改。例如下图，该表的初始状态只有学号不允许为空，但是这是很不符合实际情况的,在实际中我们往往还需要确定学生的班级、姓名、入学时间所在院系与所属专业等情况,要求把这些信息也设置为不为空。

图5—1 对数据库中学生基本信息表的设计
5。2。3 数据的载入
数据的载入是一个非常繁杂和重要的过程需要很大的工作量，可分为人工输入与其它格式数据文件导入。该系统只是一个简单的实验系统，所以采用手工输入少量的数据可满足实验要求即可。下面两个表分别显示了学生基本信息的录入与课程信息的录入。
表5.1 学生基本信息的录入

表5。2 课程信息的录入

5．3 触发器与存储过程设计
5.3。1 触发器设计
1．StudentInfo 表上的出发器触发器：对于学生基本信息表，当学生信息修改时，其它表中若有对应的该信息时,应该对其进行修改；当学生信息删除时，对应的如SC表中的对应学生成绩信息也应该自动修改:

27
① Supdata：
CREATE TRIGGER Supdate ON [dbo]。[StudentsInfo］
FOR UPDATE
AS
update SC
set 学号 = （ select 学号 from inserted)
where SC。学号=（select 学号 from deleted） ；
② Sdelete:
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） ;
；
3．Department表上的触发器:当院系的代号改变时，对应的学生所在院系、专业所属院系以及教师所在院系都应当做相应的更改。
① Dupdata:
CREATE TRIGGER Dupdate ON ［dbo].[Department］
FOR UPDATE
AS
update S
set S.专业号 = （ select 专业号 from inserted)
where S.专业号=（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)；
4．Teacher 表上的触发器:当教师号改变或教师离任时，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) ；
5．Major表上的触发器：当专业编号发生改变或专业取消，对应的学生表的专业号应该发生改变或置为空值。
① Mupdata：
CREATE TRIGGER Mupdate ON ［dbo].［Major]
FOR UPDATE
AS
update StudentsInfo
set 专业号= ( select 专业号 from inserted）
where 专业号=(select 专业号 from deleted) ；
② Mdelete:
CREATE TRIGGER Mupdate1 ON ［dbo].［Major]
FOR DELETE
AS
update StudentsInfo
set 专业号 = NULL
where 专业号=(select 专业号 from deleted) ;
5.3。2 存储过程设计
下面是部分存储过程的描述,包括插入数据、更新数据与查询数据。
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

2．Update 学生成绩信息的存储过程：
CREATE PROCEDURE UpdateSC
@学号 int(4)，
@课程号 varchar(5），
＠分数 float，
AS
update SC
set 分数=@分数
where 学号=@学号 and 课程号=@课程号
GO

3。 对不有不及格科目的所有学生的信息查询的存储过程
CREATE PROCEDURE selectstuendt_1
AS
SELECT StudentsInfo。学号， 姓名，班级，课程名,分数
FROM StudentsInfo, SC，Course
Where 分数 in（0, 60） And StudentsInfo.学号=SC.学号 And SC.课程号=Course.课程号
GO

5．4 数据库的试运行
5.4.1 触发器功能的试运行
在对StudentsInfo上的SUpdate触发器的验证时,先运行sql语句：
select ＊
from dbo。SC；
得到在update操作前的SC表的所有内容，如下图6-2所示，接着执行对StudentsInfo表的Update操作，即如下语句:
update dbo。StudentsInfo
set 学号=’2005541007'
where 学号=’2005541023'；
select ＊
from dbo。SC；
得到新的SC表，如图6—3所示, 学号为2005541023的自动修改为20055410