数据库课程设计-教务管理系统

目录

一、 二、

需求分析（11） ··················································································1 概要设计（全体组员） ·········································································4

（一）学生管理（11） ··············································································5 （二）教师管理（11） ··············································································5 (三) 教材管理（11） ················································································6 （四）班级管理（11） ··············································································6 （五）教学管理（11） ··············································································7 （六）成绩管理（11） ··············································································7 （七）课程管理（11） ··············································································8 （八）系部管理（11） ························································································· 8 三、详细设计（全体组员） ·············································································9 四、调试分析： ··························································································· 10

（一）学生管理（11） ············································································ 18 （二）教师管理（11） ············································································ 20 (三) 教材管理（11） ·············································································· 21 （四）班级管理（11） ············································································ 22 （五）教学管理（11） ············································································ 23 （六）成绩管理（11） ············································································ 24 （七）课程管理（11） ············································································ 26 （八）系部管理（11） ······················································································· 27 五、用户使用说明（11） ·············································································· 28 六、测试结果示例（11） ·············································································· 29 七、心得体会（11）····················································································· 29 参考文献 ： ······························································································· 34

摘 要：数据库技术是计算机科学技术中发展最快的领域之一，也是应用范围最广、实用性很强

的技术之一，它已成为信息社会的核心技术和重要基础。在Internet告诉发展的信息化时代，信息资源的经济价值和社会价值越来越明显，建设以数据库为核心的各类信息系统，对提高企业的竞争力与效益、改善部门的管理能力与管理水平，都具有实实在在的重要意义。

教务管理系统就是在数据库的基础之上对学校教务信息进行操作，实现数据的修改、存储、分析等操作。教务人员通过学生信息管理模块来管理学生信息，如进行学生信息的添加、修改、删除等；教务工作人员通过此模块来管理教师信息，包括教师信息的添加、修改、删除等； 教务工作人员通过该模块来管理课程的基本信息，包括课程信息的添加，修改，删除和班级课程的设置等；教务工作人员通过该模块来进行学生成绩的管理，在进行学生成绩的添加时，可用它来为学生设置所在班级的课程表来直接输入成绩；通过该模块可以查询教务所需要的各种信息；教务工作人员可以通过打印模块来打印学生成绩信息，选课记表和班级表。例如：学生信息查询提供了包括学生学号、姓名、所在班级、班主任名字和宿舍等信息，学生信息进行查询的各种查询条件，用户可以根据单个的查询条件或者它们的组合来进行查询。整个系统实现了教务信息实时性更新，给学校提供了一个高效性、准确性的信息存储系统；方便了广大用户，为学校现代化发展提供了有利保障

关键词：数据库、教务管理、信息系统、存储系统

一、需求分析（11）

随着学校的规模不断扩大，学生数量急剧增加。有关学生的各种信息量也成倍的增长。面对庞大的信息量，就需要由教务信息管理系统来提高学生管理工作的效率。通过教务管理系统，可以做到信息的规范化管理、科学统计和快速查询，从而减少管理方面的工作量。 学校为方便学生、老师、教务管理人员信息查询，注册以及信息修改，学校把学生信息，包括姓名、性别、年龄、成绩等信息输入教务管理系统的数据库，然后在终端可以对数据进行查询和修改造作。要求生成数据库系统所有需要的表以及表之间的关系，所有查询通过视图来实现，所有维护和管理工作通过存储过程来实现，对数据库

1

查询进行优化，建立需要的索引。 以下为教务管理系统需求分析图

教务管理系统需求分析图

数据字典：（11）

学生管理实体

2

系信息表实体

教室管理实体

成绩管理实体

班级管理实体

教材管理实体

课程信息表实体：

3

教学管理实体

二、概要设计（全体组员）

教务管理系统设计实现对有关学生的信息、有关教师的信息、有关每个系部的信息、有关每个班级的信息、有关课程及使用教材的信息、有关课程安排的信息的存储、查询、维护。（概要设计CDM图如下）

4

概要设计CDM图

（一）学生管理（11）

学生管理实现对有关学生姓名、学号、系编号、班级编号、性别、住址、电话、出生日期的信息的存储、查询、维护。（其E-R图如下）

出生日期 电话 学生管理 学号 姓名 住址 性别 系编号 班级编号 （二）教师管理（11） 教师管理实现对有关老师工号、课程编号、姓名、性别、住址、职称的信息的存储、查询、维护。（其E-R图如下）

5

教师管理 职称 工号 住址 课程编号 姓名 性别

(三) 教材管理（11）

教材管理实现对有关教材编号、出版社、所属专业、作者、库存量、价格的信息的存储、查询、维护。（其E-R图如下）

教材管理 价格 教材编号 库存量 出版社 所属专业 作者

（四）班级管理（11）

班级管理实现对有关班级编号、系编号、班级名称、班级人数的信息的存储、查询、维

6

护。（其E-R图如下）

班级管理 班级编号 班级名称 班级人数 系编号

（五）教学管理（11）

教学管理实现对有关教学工号、课程编号、教学时间、教学地点的信息的存储、查询、维护。（其E-R图如下）

教学管理 工号 课程编号 教学时间 教学地点

（六）成绩管理（11）

7

成绩管理实现对有关学号、课程编号、成绩的信息的存储、查询、维护。（其E-R图如下）

成绩管理 学号 成绩 课程编号

（七）课程管理（11）

课程管理实现对有关课程编号、课程名称、任课教师、班级名称的信息的存储、查询、维护。（其E-R图如下）

课程管理 课程编号 课程名称 任课教师 班级名称

（八）系部管理（11）

系部管理实现对有关系编号、系名称、系主任、系部电话的信息的存储、查询、维护。（其

8

E-R图如下）

系部管理 系编号 系名称 系主任 系部电话

三、详细设计（全体组员）

教务管理系统由学生管理、教师管理、教材管理、班级管理、教学管理、成绩管理

9

留个模块构成，建立它们各自内部的列属性，并相互关联它们彼此之间的关系，最后到完善的教务管理系统。（模型结构图如下）

教务管理系统模型图

四、调试分析：

10

教务管理系统详细设计代码如下：

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('Classmangement') and o.name 'FK_CLASSMAN_RELATIONS_DEPARTME') alter table Classmangement

drop constraint FK_CLASSMAN_RELATIONS_DEPARTME go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('GradeManagement') and o.name 'FK_GRADEMAN_RELATIONS_STUDENTM') alter table GradeManagement

drop constraint FK_GRADEMAN_RELATIONS_STUDENTM go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('GradeManagement') and o.name 'FK_GRADEMAN_RELATIONS_COURSEIN') alter table GradeManagement

drop constraint FK_GRADEMAN_RELATIONS_COURSEIN go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('TeachManage') and o.name 'FK_TEACHMAN_RELATIONS_COURSEIN') alter table TeachManage

drop constraint FK_TEACHMAN_RELATIONS_COURSEIN go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('TeachManage') and o.name 'FK_TEACHMAN_RELATIONS_TEACHERM') alter table TeachManage

drop constraint FK_TEACHMAN_RELATIONS_TEACHERM go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('TeacherManage') and o.name

11

=

=

=

=

=

=

'FK_TEACHERM_RELATIONS_COURSEIN') alter table TeacherManage

drop constraint FK_TEACHERM_RELATIONS_COURSEIN go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('studentmanage') and o.name = 'FK_STUDENTM_RELATIONS_DEPARTME') alter table studentmanage

drop constraint FK_STUDENTM_RELATIONS_DEPARTME go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('studentmanage') and o.name = 'FK_STUDENTM_RELATIONS_CLASSMAN') alter table studentmanage

drop constraint FK_STUDENTM_RELATIONS_CLASSMAN go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('教材使用') and o.name = 'FK_教材使用_教材使用_COURSEIN') alter table 教材使用

drop constraint FK_教材使用_教材使用_COURSEIN go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('教材使用') and o.name = 'FK_教材使用_教材使用2_SBMANAGE') alter table 教材使用

drop constraint FK_教材使用_教材使用2_SBMANAGE go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('教材发放') and o.name = 'FK_教材发放_教材发放_STUDENTM') alter table 教材发放

drop constraint FK_教材发放_教材发放_STUDENTM go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

12

where r.fkeyid = object_id('教材发放') and o.name = 'FK_教材发放_教材发放2_SBMANAGE') alter table 教材发放

drop constraint FK_教材发放_教材发放2_SBMANAGE go

if exists (select 1

from sysindexes

where id = object_id('Classmangement') and name = 'Relationship_7_FK' and indid > 0 and indid < 255)

drop index Classmangement.Relationship_7_FK go

if exists (select 1

from sysobjects

where id = object_id('Classmangement') and type = 'U') drop table Classmangement go

if exists (select 1

from sysobjects

where id = object_id('Courseinformationform') and type = 'U')

drop table Courseinformationform go

if exists (select 1

from sysobjects

where id = object_id('Departmentform') and type = 'U') drop table Departmentform go

if exists (select 1

from sysindexes

where id = object_id('GradeManagement') and name = 'Relationship_5_FK' and indid > 0 and indid < 255)

drop index GradeManagement.Relationship_5_FK go

13

if exists (select 1

from sysindexes

where id = object_id('GradeManagement') and name = 'Relationship_3_FK' and indid > 0 and indid < 255)

drop index GradeManagement.Relationship_3_FK go

if exists (select 1

from sysobjects

where id = object_id('GradeManagement') and type = 'U') drop table GradeManagement go

if exists (select 1

from sysobjects

where id = object_id('SBMANAGE') and type = 'U') drop table SBMANAGE go

if exists (select 1

from sysindexes

where id = object_id('TeachManage') and name = 'Relationship_9_FK' and indid > 0 and indid < 255)

drop index TeachManage.Relationship_9_FK go

if exists (select 1

from sysindexes

where id = object_id('TeachManage') and name = 'Relationship_8_FK' and indid > 0 and indid < 255)

drop index TeachManage.Relationship_8_FK go

if exists (select 1

from sysobjects

where id = object_id('TeachManage')

14

and type = 'U') drop table TeachManage go

if exists (select 1

from sysindexes

where id = object_id('TeacherManage') and name = 'Relationship_6_FK' and indid > 0 and indid < 255)

drop index TeacherManage.Relationship_6_FK go

if exists (select 1

from sysobjects

where id = object_id('TeacherManage') and type = 'U') drop table TeacherManage go

if exists (select 1

from sysindexes

where id = object_id('studentmanage') and name = 'Relationship_2_FK' and indid > 0 and indid < 255)

drop index studentmanage.Relationship_2_FK go

if exists (select 1

from sysindexes

where id = object_id('studentmanage') and name = 'Relationship_1_FK' and indid > 0 and indid < 255)

drop index studentmanage.Relationship_1_FK go

if exists (select 1

from sysobjects

where id = object_id('studentmanage') and type = 'U') drop table studentmanage go

15

if exists (select 1

from sysindexes

where id = object_id('教材使用') and name = '教材使用2_FK' and indid > 0 and indid < 255)

drop index 教材使用.教材使用2_FK go

if exists (select 1

from sysindexes

where id = object_id('教材使用') and name = '教材使用_FK' and indid > 0 and indid < 255)

drop index 教材使用.教材使用_FK go

if exists (select 1

from sysobjects

where id = object_id('教材使用') and type = 'U') drop table 教材使用 go

if exists (select 1

from sysindexes

where id = object_id('教材发放') and name = '教材发放2_FK' and indid > 0 and indid < 255)

drop index 教材发放.教材发放2_FK go

if exists (select 1

from sysindexes

where id = object_id('教材发放') and name = '教材发放_FK' and indid > 0 and indid < 255)

drop index 教材发放.教材发放_FK go

16

if exists (select 1

from sysobjects

where id = object_id('教材发放') and type = 'U') drop table 教材发放 go

alter table Classmangement

add constraint FK_CLASSMAN_RELATIONS_DEPARTME foreign key (Depar_Id) references Departmentform (Depar_Id) go

alter table GradeManagement

add constraint FK_GRADEMAN_RELATIONS_STUDENTM foreign key (St_Id) references studentmanage (St_Id) go

alter table GradeManagement

add constraint FK_GRADEMAN_RELATIONS_COURSEIN foreign key (Course_Id) references Courseinformationform (Course_Id) go

alter table TeachManage

add constraint FK_TEACHMAN_RELATIONS_COURSEIN foreign key (Course_Id) references Courseinformationform (Course_Id) go

alter table TeachManage

add constraint FK_TEACHMAN_RELATIONS_TEACHERM foreign key (T_Id) references TeacherManage (T_Id) go

alter table TeacherManage

add constraint FK_TEACHERM_RELATIONS_COURSEIN foreign key (Course_Id) references Courseinformationform (Course_Id) go

alter table studentmanage

add constraint FK_STUDENTM_RELATIONS_DEPARTME foreign key (Depar_Id) references Departmentform (Depar_Id) go

alter table studentmanage

add constraint FK_STUDENTM_RELATIONS_CLASSMAN foreign key (class_id)

17

references Classmangement (class_id) go

alter table 教材使用

add constraint FK_教材使用_教材使用_COURSEIN foreign key (Course_Id) references Courseinformationform (Course_Id) go

alter table 教材使用

add constraint FK_教材使用_教材使用2_SBMANAGE foreign key (SUBID) references SBMANAGE (SUBID) go

alter table 教材发放

add constraint FK_教材发放_教材发放_STUDENTM foreign key (St_Id) references studentmanage (St_Id) go

alter table 教材发放

add constraint FK_教材发放_教材发放2_SBMANAGE foreign key (SUBID) references SBMANAGE (SUBID) go

（一）学生管理（11）

/*==============================================================*/ /* Table: studentmanage 建立学生信息表 */ /*==============================================================*/ create table studentmanage (

St_Name varchar(10) not null, 学生姓名 St_Id numeric(20) not null, 学生编号 Depar_Id numeric(10) null, 系部编号 class_id numeric(10) null, 班级编号 St_Sex varchar(10) not null, 学生性别 St_Address varchar(50) not null, 学生住址 St_Tel numeric(20) not null, 学生电话 St_Bir datetime not null, 学生生日

constraint PK_STUDENTMANAGE primary key nonclustered (St_Id) 建立主键：学生编号

18

) Go

1、数据插入

学生管理插入的相关信息：

insert into dbo.studentmanage values('王一',100,1,1,'男','芙蓉路一号',135641,1989-06-22) insert into dbo.studentmanage values('王二',101,1,1,'男','芙蓉路二号',165941,1989-05-12) insert into dbo.studentmanage values('王三',102,4,2,'男','芙蓉路三号',169941,1988-12-12) insert into dbo.studentmanage values('王四',103,4,2,'男','芙蓉路三号',149941,1988-12-12) insert into dbo.studentmanage values('王五',104,3,5,'男','芙蓉路四号',129941,1990-01-12) insert into dbo.studentmanage values('王六',105,4,6,'男','芙蓉路五号',119941,1990-02-12) insert into dbo.studentmanage values('王七',106,5,7,'男','芙蓉路六号',165541,1990-03-12) insert into dbo.studentmanage values('王八',107,6,8,'男','芙蓉路七号',163341,1990-04-12) insert into dbo.studentmanage values('王九',108,7,9,'男','芙蓉路八号',160041,1990-05-12) insert into dbo.studentmanage values('王十',109,8,10,'男','芙蓉路九号',167941,1990-06-12) insert into dbo.studentmanage values('王十一',110,9,11,'男','芙蓉路十号',163941,1990-07-12)

2、查询结果

select * from dbo.studentmanage

19

（二）、查询每个学生的所在系

SELECT studentmanage.St_Id, studentmanage.St_Name, studentmanage.class_id, Classmangement.Depar_Id, Departmentform.Depar_Name FROM studentmanage INNER JOIN Classmangement ON

studentmanage.class_id

Classmangement.class_id INNER JOIN Departmentform

ON

studentmanage.Depar_Id

Departmentform.Depar_Id AND Classmangement.Depar_Id = Departmentform.Depar_Id

通过studentmanage,Classmanagement,Departmentform 三个表实现如下查询

30

=

=

（三）、查询每个学生每门课程的成绩

SELECT studentmanage.St_Name, studentmanage.St_Id, GradeManagement.Mark, Courseinformationform.Course_Name FROM studentmanage INNER JOIN GradeManagement GradeManagement.St_Id INNER JOIN

Courseinformationform ON GradeManagement.Course_Id = Courseinformationform.Course_Id

通过studentmanage,Grademanagement,Courseinformationform 三个表实现如下查询

ON

studentmanage.St_Id

=

查询结果

相关联系

31

（四）、查询每个学生的课程表

SELECT

studentmanage.St_Name,

studentmanage.St_Id,

Courseinformationform.Course_Id, Courseinformationform.Course_Name, Courseinformationform.Teacher FROM studentmanage INNER JOIN GradeManagement GradeManagement.St_Id INNER JOIN

Courseinformationform ON GradeManagement.Course_Id = Courseinformationform.Course_Id

通过studentmanage,Grademanagement,Courseinformationform 三个表实现如下查询

ON studentmanage.St_Id =

查询结果

相关联系

32

（五）、查询每个老师所要教的课程信息

SELECT

TeacherManage.T_Id,

TeacherManage.T_Name,

Courseinformationform.Course_Name, Courseinformationform.class_name, TeachManage.Teach_Add FROM TeacherManage INNER JOIN Courseinformationform Courseinformationform.Course_Id INNER JOIN

TeachManage ON TeacherManage.T_Id = TeachManage.T_Id AND Courseinformationform.Course_Id = TeachManage.Course_Id

通过TeacherManage,TeachManage, Courseinformationform 三个表实现如下查询

ON TeacherManage.Course_Id =

查询结果

相关联系

33

七、心得体会：（11）

通过一个星期的数据库课程实践操作，我们收获颇多；首先我们懂得了团队团结合作精神，通过大家共同奋斗，最终整个课程设计得到了完美的结束。虽然只有五天的时间学习，我们初步掌握了对PowerDesign 软件的基本造作，对模型的创建，各模型的关系连接也有一定了解。这次课程设计让我们对数据库得到了更深的了解，更深刻的体会数据库的重要性，它的分布之广，效率之高，是其他技术无可匹敌的。

其实有时我们会发现有些东西表面上看起来很乏味，或者很难，但是当我们静下心去研究摸索，我们也会找寻到事物本身的规律与其独特的魅力，当我们发现她的神奇之处和其规律时我们就会渐渐的喜欢上她。这就是学习的乐趣！

最后感谢本次课程设计指导老师相克军老师不厌其烦的指导，同时也感谢数据库基础课课程老师刁老师理论课的教授，和对我课程设计报告的耐心审批，谢谢！

参考文献 ：

[1萨师煊，王珊.数据库系统概论[M].北京：高等教育出版社，2002. [2]卢开澄. 计算机算法引导—设计与分[M].北京：清华大学出版社，1996. [3 Abraham Silberschatz.Database System Concepts[J],Mcgraw-Hillm,1990. [4]Raghu Ramakrishnan.Database Management System[M],清华大学出版社，2000.

[5]Efrem G.Mallach.Decision Support and Data Warehouse Ststem[M],清华大学出版社，2001.

34

本文来源：https://www.bwwdw.com/article/zkc.html