数据库系统设计实验报告——教务管理系统
数据库系统设计实验报告
――教务管理系统
学院： 计算机科学与技术
专业：软件工程
班级：
学号：
姓名：
指导老师：
1
数据库系统设计实验报告——教务管理系统
一、概述
项目背景：数据库课程设计
系统名称：教务管理系统
开发环境：SQL Server 2008
二、需求分析
1、系统的概述
? 一个学校由很多个学院组成，每个学院有多个专业，每个专业有1个或
者多个班级。一个学生只属于1个班级，学生有姓名、学号、性别、身份证号、入学时间等属性。
? 每个专业的每个年级都有对应的教学计划。教学计划指定了这个专业这
个年级的某门课程在那个学期上，学分、学时是多少。每个学院都设置有1个教务秘书，这些信息都由教务秘书进行输入。每个院系的课程都有自己的唯一编码，比如计算机系上的C语言，和电子系上的C语言，虽然都是同一门课，但是编码是不同的。但是同一个院系中的相同的课程，课程代码是唯一的。比如计算机学院的07计算机软件专业和计算机学院的07计算机科学专业都有C语言课程，这两门课程的课程代码是相同的。
? 教务秘书在每个学期的中间时刻，为下一个学期进行排课，排课的内容
根据教学计划，比如08计算机软件专业的学生，在2009-20xx年第2学期上的课程，可以通过教学计划得知要上什么课程，然后对这些课程安排上课教师（比如陈越）、上课时间（比如星期2，第1-2节）和地点（理工楼234），有的课程是单周上，有的双周，也有是每周都上的。
? 在每个学期期末，学生登录到网站上选择下一个学期需要上的课，选择
的时刻，可以选择是否需要教材，如果某门课程的人数选满了，则不能给任何学生选择了。
? 学生根据选择的课程进行上课，参加考试。每个老师可以用自己的用户
名和密码登陆网站，选择这个学期自己所教的课程（一个老师可以上多个班级的课程），输入这个班级的成绩，成绩分为期末考试成绩、期中考试成绩、平时成绩、实验成绩以及总评成绩。这些成绩都需要进行存储。 ? 为了保证安全性，老师输入的成绩并不是直接放到最终的成绩表中，而
是放在一个临时的表中，只有当教务秘书审核过后，才导入到最终的成绩表中。
? 学生在老师输入完成绩、教务秘书审核后，可以登录到网上查询自己某
2
数据库系统设计实验报告——教务管理系统
个学期的成绩。成绩数据非常重要，为了防止教务秘书随意更改成绩，需要对成绩表的插入、删除、修改做出监控，凡是对成绩表做了上述操作，必须记录下来，放在另外一个表中，可以称为成绩变动记录表，这个表要求记录在什么时候，更新了那个同学那门课的成绩数据，原始值是什么，现在值是什么。如果是插入，则原始值为NULL，如果是删除，则现在值是NULL。
? 为了在数据库层次上保证数据库的安全性，在数据库中，建立两类数据
库用户，一类是教务秘书访问的，称为U_JWMS, 这个用户能对除了成绩变动记录表不能访问外，可以对所有的数据进行修改、选择、删除、插入。另外一类用户成为U_Web， 是为Web用户访问所设计的。这类用户只对学生选课的数据、老师输入成绩的临时的表据有删除、插入、修改权限，其它的表只有只读的权限。
3
数据库系统设计实验报告——教务管理系统
2、教务系统的E-R图
4
数据库系统设计实验报告——教务管理系统
3、数据库表
(1)学生信息表
(2)教师表
(3)班级信息表
5
数据库系统设计实验报告——教务管理系统
(4)学院表
(5)教务秘书信息表
(6)专业信息表
6
数据库系统设计实验报告——教务管理系统
(7)教学计划表
(8)课程表
(9)学生选课表
7
数据库系统设计实验报告——教务管理系统
(10)教师上课表
(11) 临时成绩表
(12) 最终成绩表
8
数据库系统设计实验报告——教务管理系统
(13) 成绩变动记录表
9
数据库系统设计实验报告——教务管理系统
三、程序清单
/*数据库的建立*/
create database [JWGL] on primary
(name = 'JWGL',
filename = 'd:\我的文件\SQL\JWGL.mdf',
size = 4096kb,
maxsize = unlimited,
filegrowth = 1024kb
)
log on
(name = 'JWGL_log',
filename = 'd:\我的文件\SQL\JWGL_log.ldf', size = 1024kb,
maxsize = 2048gb,
filegrowth = 10%
)
Go
/*基本表的建立*/
/*学生表*/
use JWGL
create table dbo.Student
(student_name nvarchar(8) null,
student_id nvarchar(10) primary key not null, student_tender nvarchar (8) null,
date_birth datetime null,
identity_no nvarchar(10) null,
date_entrance datetime null,
class_id nvarchar(10) not null,
major_id nvarchar(10) not null,
college_id nvarchar(10) not null,
)
10
数据库系统设计实验报告——教务管理系统
/*班级表*/
use JWGL
create table dbo.Class
(class_id nvarchar(10) primary key not null,
class_name nvarchar(20) null,
class_accom int null,
grade int null,
major_id nvarchar(10) not null
)
/*专业表*/
use JWGL
create table dbo.Major
(major_id nvarchar(10) primary key not null,
major_name nvarchar(20) null,
count_class int null,
college_id nvarchar(10) not null
)
/*教务秘书表*/
use JWGL
create table dbo.Secretary
(secretary_id nvarchar(10) primary key not null,
Secretary_name nvarchar(8) null default '教务秘书',
college_id nvarchar(10) not null,
secretary_usename nvarchar(10) null default 'secretary', secretary_password nvarchar(10) null default '0000' )
/*学院表*/
create table dbo.College
(college_id nvarchar(10) primary key not null,
college_name nvarchar(20) null,
count_major int null,
secretary_id nvarchar(10) not null
)
11
数据库系统设计实验报告——教务管理系统
/*教学计划表*/
use JWGL
create table dbo.TeachingPlan
(college_id nvarchar(10) not null,
major_id nvarchar(10) primary key(major_id,course_id) not null ,
grade int not null,
term int not null,
course_id nvarchar(10) not null,
course_name nvarchar(20) null,
course_credit real null,
course_length real null,
accommodate int null default '0'
)
/*课程表*/
use JWGL
create table dbo.Course
(course_id nvarchar(10) primary key(course_id,class_id) not null,
teacher_id nvarchar(10) null,
class_time datetime null,
class_addr nvarchar(20) null,
week nvarchar(10) null default '单双周均上',
term int null,
class_id nvarchar(10) not null,
major_id nvarchar(10) null,
)
/*学生选课表*/
use JWGL
create table dbo.CourseElect
(student_id nvarchar(10) primary key(student_id,course_id) not null,
term int null,
course_id nvarchar(10) not null,
textbook nvarchar(2) null default '是',
student_full nvarchar(2) null default '否')
12
数据库系统设计实验报告——教务管理系统
/* 教师表*/
use JWGL
create table dbo.Teacher
(teacher_name nvarchar(8) null,
teacher_id nvarchar(10) primary key not null,
teacher_usename nvarchar(10) null default 'teacher', teacher_password nvarchar(10) null default '0000'
)
/* 教师上课表*/
use JWGL
create table dbo.Teaching
(teacher_id nvarchar(10)
key(teacher_id,course_id,class_id)not null,
teacher_name nvarchar(8) null,
term int null,
major_id nvarchar(10) null,
class_id nvarchar(10) not null,
course_id nvarchar(10) not null
)
/*成绩临时表*/
use JWGL
create table dbo.TempScore
(class_id nvarchar(10) not null,
student_id nvarchar(10) primary key(student_id,course_id) not null,
course_id nvarchar(10) not null,
term int null,
score_terminal real null default '0',
score_mid real null default '0',
score_ordtime real null default '0',
score_expe real null default '0',
score_final real null default '0',
course_credit real null default '0'
)
13
primary
数据库系统设计实验报告——教务管理系统
/*最终成绩表*/
use JWGL
create table dbo.FinalScore
(class_id nvarchar(10) not null,
student_id nvarchar(10) primary key(student_id,course_id) not null,
course_id nvarchar(10) not null,
term int null,
score_terminal real null default '0',
score_mid real null default '0',
score_ordtime real null default '0',
score_expe real null default '0',
score_final real null default '0',
course_credit real null default '0'
)
/*成绩记录变动表*/
use JWGL
create table dbo.ModifyLog
(student_id nvarchar(10) primary key(student_id,course_id) not null,
course_id nvarchar(10) not null,
update_time datetime null,
score_pri_terminal real null,
score_pri_mid real null,
score_pri_ordtime real null,
score_pri_expe real null,
score_pri_final real null,
pri_course_credit real null,
score_cur_terminal real null,
score_cur_mid real null,
score_cur_ordtime real null,
score_cur_expe real null,
score_cur_final real null,
cur_course_credit real null
)
14
数据库系统设计实验报告——教务管理系统
/*检查约束*/
use JWGL
alter table Student
add constraint ck_student_tender
check (student_tender = '男' or student_tender = '女')
alter table Class
add constraint ck_class_accom
check (class_accom >= 0)
go
alter table Major
add constraint ck_count_class
check (count_class >= 0)
go
alter table College
add constraint ck_count_major
check (count_major >= 0)
go
alter table TeachingPlan
add constraint ck_course_credit
check (course_credit >= 0)
alter table TeachingPlan
add constraint ck_course_length
check (course_length >= 0)
alter table TeachingPlan
add constraint ck_accommodate
check (accommodate >= 0)
go
alter table Course
add constraint ck_week
check (week = '单周' or week = '双周' or week =
go
alter table CourseElect
add constraint ck_textbook
check (textbook = '是' or textbook = '否')
单双周') 15 '
数据库系统设计实验报告——教务管理系统
alter table CourseElect
add constraint ck_student_full
check (student_full = '是' or student_full ='否') go
alter table TempScore
add constraint ck_score_ordtime_temp
check (score_ordtime >= 0)
alter table TempScore
add constraint ck_score_mid_temp
check (score_mid >= 0)
alter table TempScore
add constraint ck_score_terminal_temp
check (score_terminal >= 0)
alter table TempScore
add constraint ck_score_expe_temp
check (score_expe >= 0)
alter table TempScore
add constraint ck_score_final_temp
check (score_final >= 0)
alter table TempScore
add constraint ck_course_credit_temp
check (course_credit >= 0)
go
alter table FinalScore
add constraint ck_score_ordtime_final
check (score_ordtime >= 0)
alter table FinalScore
add constraint ck_score_mid_final
check (score_mid >= 0)
alter table FinalScore
add constraint ck_score_expe_final
check (score_expe >= 0)
alter table FinalScore
add constraint ck_score_terminal_final
check (score_terminal >= 0)
alter table FinalScore
add constraint ck_score_final_final
check (score_final >= 0)
alter table FinalScore
add constraint ck_course_credit_final
check (course_credit >= 0)
16
数据库系统设计实验报告——教务管理系统
go
/*外键约束*/
alter table Student
add constraint fk_class_id_student_class
foreign key (class_id)
references Class(class_id)
alter table Student
add constraint fk_major_id_student_major
foreign key (major_id)
references Major(major_id)
alter table Student
add constraint fk_college_id_student_college foreign key (college_id)
references College(college_id)
go
alter table Class
add constraint fk_major_id_class_major
foreign key (major_id)
references Major(major_id)
go
alter table Major
add constraint fk_college_id_major_college
foreign key (college_id)
references College(college_id)
go
alter table College
add constraint fk_secretary_id_college_secrectary foreign key (secretary_id)
references Secretary(secretary_id)
go
alter table Secretary
add constraint fk_college_id_secrectary_college foreign key (college_id)
references College(college_id)
go
17
数据库系统设计实验报告——教务管理系统
alter table TeachingPlan
add constraint fk_college_id_plan_college foreign key (college_id)
references College(college_id)
go
alter table Course
add constraint fk_teacher_id_course_teacher foreign key (teacher_id)
references Teacher(teacher_id)
go
alter table Course
add constraint fk_major_id_course_major foreign key (major_id)
references Major(major_id)
go
alter table TempScore
add constraint fk_class_id_temp_class foreign key (class_id)
references Class(class_id)
go
alter table FinalScore
add constraint fk_class_id_score_class foreign key (class_id)
references Class(class_id)
go
18
数据库系统设计实验报告——教务管理系统
/*建立用户，分配权限*/
/*U_JWMS, 能对除了成绩变动记录表不能访问外，
可以对所有的数据进行修改、选择、删除、插入。*/
USE [JWGL]
GO
CREATE LOGIN [JINGQI-PC\Administrator] FROM WINDOWS WITH DEFAULT_DATABASE=[JWGL]
GO
USE [JWGL]
GO
CREATE USER [U_JWMS] FOR LOGIN [JINGQI-PC\Administrator] GO
/*把class等表的SELECT,INSERT,DELETE,UPDATE权限授予用户
U_JWMS*/
GRANT SELECT,INSERT,DELETE,UPDATE
ON Class TO U_JWMS WITH GRANT OPTION
GRANT SELECT,INSERT,DELETE,UPDATE
ON College TO U_JWMS WITH GRANT OPTION
GRANT SELECT,INSERT,DELETE,UPDATE
ON Course TO U_JWMS WITH GRANT OPTION
GRANT SELECT,INSERT,DELETE,UPDATE
ON TeachingPlan TO U_JWMS WITH GRANT OPTION
GRANT SELECT,INSERT,DELETE,UPDATE
ON Major TO U_JWMS WITH GRANT OPTION
GRANT SELECT,INSERT,DELETE,UPDATE
ON FinalScore TO U_JWMS WITH GRANT OPTION
GRANT SELECT,INSERT,DELETE,UPDATE
ON CourseElect TO U_JWMS WITH GRANT OPTION
GRANT SELECT,INSERT,DELETE,UPDATE
ON Secretary TO U_JWMS WITH GRANT OPTION
GRANT SELECT,INSERT,DELETE,UPDATE
ON Student TO U_JWMS WITH GRANT OPTION
GRANT SELECT,INSERT,DELETE,UPDATE
ON Teacher TO U_JWMS WITH GRANT OPTION
GRANT SELECT,INSERT,DELETE,UPDATE
ON Teaching TO U_JWMS WITH GRANT OPTION
GRANT SELECT,INSERT,DELETE,UPDATE
ON TempScore TO U_JWMS WITH GRANT OPTION
19
数据库系统设计实验报告——教务管理系统
DENY View Definition ON ModifyLog TO U_JWMS go
/*U_Web, 是为Web用户访问所设计的。
只对学生选课的数据、老师输入成绩的临时的表据有删除、插入、修改权限，其它的表只有只读的权限。*/
USE [JWGL]
GO
CREATE LOGIN [JINGQI-PC\guest] FROM WINDOWS WITH
DEFAULT_DATABASE=[JWGL]
GO
use JWGL
CREATE USER [U_Web] FOR LOGIN [JINGQI-PC\guest]
GRANT INSERT,DELETE,UPDATE ON TempScore TO U_Web GRANT INSERT,DELETE,UPDATE ON CourseElect TO U_Webgo
20
数据库系统设计实验报告——教务管理系统
--触发器的设计―――完成记录对成绩表进行进行修改时所更改的内容 use JWGL
create trigger tri_score_update
on dbo.FinalScore
after update
as
if (
update(score_terminal) or
update(score_mid) or
update(score_ordtime) or
update(score_expe) or
update(score_final) or
update(course_credit)
)
begin
insert into dbo.ModifyLog
(
student_id,
course_id,
update_time,
score_pri_terminal,
score_pri_mid,
score_pri_ordtime,
score_pri_expe,
score_pri_final,
pri_course_credit,
score_cur_terminal,
score_cur_mid,
score_cur_ordtime,
score_cur_expe,
score_cur_final,
cur_course_credit
)
Select
Deleted.student_id,
Deleted.course_id,
getdate(),
Deleted.score_terminal,
Deleted.score_mid,
Deleted.score_ordtime,
Deleted.score_expe,
Deleted.score_final,
Deleted.course_credit,
21
数据库系统设计实验报告——教务管理系统
Inserted.score_terminal,
Inserted.score_mid,
Inserted.score_ordtime,
Inserted.score_expe,
Inserted.score_final,
Inserted.course_credit,
from Deleted,Inserted
end
go
--计算平均成绩的函数―――完成计算学生平均成绩的功能
use JWGL
create function avg_score(@student_id nvarchar(10)) returns table
as
return (
select
avg(score_terminal) as avg_score_terminal,
avg(score_mid) as avg_score_mid,
avg(score_ordtime) as avg_score_ordtime,
avg(score_expe) as avg_score_expe,
avg(score_final) as avg_score_final
from score where student_id = @student_id)
go
四、心得体会
数据库在软件以及网络应用中被广泛采用，学好数据库非常重要；通过这次试验，我加深了对数据库的理解，熟悉了SQL Server的应用；但是要达到熟练应用的程度，还需继续努力！
22