某中学的排课管理系统_某中学的排课管理系统（SQL的简单应用）

一、系统分析

需求功能分析

需求分析的任务是调查应用领域，对应用领域中的信息要求和操作要求进行详细分析，形成需求分析说明书。重点是调查，收集与分析用户在数据管理中的信息要求，处理要求，数据的安全性与完整性要求。

为了完成需求分析的任务，要详细调查待开发的数据库应用部门的情况，了解原系统工作概况，分析用户的各种需求，在此基础上确定新系统的功能。新系统必须考虑今后的扩充和改变，不能仅仅按当前应用需求来设计数据库。

排课管理系统需要实现对学校教师、教室、班级、及课程的信息管理，对高校排课，都有智能的管理，主要实现如下的一些功能：

班级各种信息的输入，包括班级号，班级名称，班级人数。

教师各种信息的输入，包括教师号，教师姓名，职称，性别。

学校课程的增加，删除，修改。

学校课程计划的制定。

检测指定教师、指定节次是否有课；

生成指定班级的课程表；

生成指定老师的课程表；

生成指定学生的课程表；

二、系统数据库设计

1．概念设计

排课管理系统涉及的实体有4个，分别是需要学生、班级、教师、课程。

其中班级与学生是一对多的关系；

班级与课程是多对多的关系；

班级与教师是多对多的关系；

课程与教师是多对多的关系；

图1 学生实体E-R图

图2 班级实体E-R图

图3 教师实体E-R图

图4 课程实体E-R图

图5 E-R总图

• 学生具有4个属性：学生ID、姓名、性别、班级ID。学生ID是主键，班级ID是外键。
• 教师具有4个属性：教师ID、姓名、性别、年龄。教师ID是主键。
• 班级具有2个属性：班级ID、班级名称。班级ID是主键。
• 课程具有2个属性：课程ID、课程名称。课程ID是主键。
• 联系有1个，联系也可以转换为实体。故排课产生课程表。
• 课程表具有6个属性：班级ID、教师ID、课程ID、星期、节次、备注。星期+节次为主键，班级ID、教师ID、课程ID为外键。

2．逻辑设计

写出上述几个实体的关系模式，实现E-R图向关系模型的转变。

3．物理设计

将上述的实体转换为关系。

三、系统数据库实施

1．写出创建数据库、表、视图、存储过程、函数、触发器的SQL语句

--创建数据库
create database lesson
go
use lesson
go
--创建class表
create table class
(
	classID int primary key,
	classname char(20)
)
Go
--创建student表
create table student
(
	studentID int primary key,
	name	  char(10) not null,
	sex		  char(2)  not null check(sex='F' or sex='M'),
	classID   int references class(classID)
)
Go
--创建teacher表
create table teacher
(
	teacherID int primary key,
	name	  char(10) not null,
	sex		  char(2)  not null check(sex='F' or sex='M'),
	age		  int	   not null	
)
Go
--创建course表
create table course
(
	courseID   int primary key,
	coursename char(20)
)
--创建class_schdule表
use lesson
go
create table course_schdule
(
   classID    int,
   teacherID  int,
   courseID   int,
   weeks      int not null check(weeks>0 and weeks<8),
   lessons    int not null check(lessons>0 and lessons<9),
   remark     char(100),
   constraint pk_schdule primary key(classID,teacherID,courseID,weeks,lessons),
   constraint fk_classID foreign key (classID) references class(classID),
   constraint fk_teacherID foreign key (teacherID)references teacher(teacherID),
   constraint fk_courseID foreign key  (courseID)references course(courseID)
)

2．系统数据表结构关系

数据表结构的关系如图6所示。

图6 系统数据表结构关系图

四、系统数据查询与数据维护

1．插入测试数据（用Insert语句实现）

--向class表中添加数据
use lesson
go
insert into class values('01','物联网')
insert into class values('02','市场营销')
insert into class values('03','机电')
insert into class values('04','电气')
insert into class values('05','软件')

--向student表中添加数据
use lesson
go
insert into student values('1201','白某','M','01')
insert into student values('1202','贾某','M','01')
insert into student values('1203','王某','M','02')
insert into student values('1204','龚某','F','02')
insert into student values('1205','邢某','M','03')
insert into student values('1206','戴某','M','03')
insert into student values('1207','刘某','F','04')
insert into student values('1208','陈某','F','04')
insert into student values('1209','韩某','M','05')
insert into student values('1210','袁某','F','05')

--向teacher表中添加数据
use lesson
go
insert into teacher values('001','韩韩','M','35')
insert into teacher values('002','周周','F','40')
insert into teacher values('003','石石','M','45')
insert into teacher values('004','费费','M','43')
insert into teacher values('005','胡胡','M','37')
insert into teacher values('006','田田','F','43')
insert into teacher values('007','姚姚','M','45')
insert into teacher values('008','喻喻','F','36')
insert into teacher values('009','刘刘','F','30')
insert into teacher values('010','李李','M','31')

--向course表中添加数据
use lesson
go
insert into course values('0001','复变')
insert into course values('0002','模电')
insert into course values('0003','信号')
insert into course values('0004','电拖')
insert into course values('0005','概率')
insert into course values('0006','英语')
insert into course values('0007','口语')
insert into course values('0008','数据库')
insert into course values('0009','电路')
insert into course values('0010','大物')
insert into course values('0011','马原')

--向course_schdule表中添加数据
use lesson
go
insert into course_schdule values('1','001','0004',1,5,'第1周到第5周')
insert into course_schdule values('1','001','0004',1,6,'第1周到第5周')
insert into course_schdule values('1','002','0007',1,1,'第1周到第6周')
insert into course_schdule values('1','002','0007',1,2,'第1周到第6周')
insert into course_schdule values('1','003','0001',3,3,'第1周到第5周')
insert into course_schdule values('1','003','0001',3,4,'第1周到第5周')
insert into course_schdule values('1','004','0008',5,7,'第5周到第10周')
insert into course_schdule values('1','004','0008',5,8,'第5周到第10周')
insert into course_schdule values('2','001','0004',1,3,'第1周到第5周')
insert into course_schdule values('2','001','0004',1,4,'第1周到第5周')
insert into course_schdule values('2','005','0011',2,1,'第2周到第3周')
insert into course_schdule values('2','005','0011',2,2,'第2周到第3周')
insert into course_schdule values('2','005','0011',2,3,'第2周到第3周')
insert into course_schdule values('2','005','0011',2,4,'第2周到第3周')

2．简单数据查询举例

查询所有课程信息

select * from course
go

3．连接查询举例

查询学生信息，要求classID 换成班级名显示。

select student.studentID 学生ID,student.name 学生名,student.sex 性别,class.classname 班级名
from student,class
where student.classID=class.classID

4．子查询举例

查询老师给哪些班级上过课的信息，要求classID 换成班级名显示。

use lesson
go
select distinct teacher.teacherID 教师ID,teacher.name 教师名, class.classname 班级名
from teacher
	join course_schdule on teacher.teacherID=course_schdule.teacherID
	join class on course_schdule.classID=class.classID

5．数据修改举例

修改一条排课信息

use lesson
go
update course_schdule
set teacherID='006',courseID='0001'
where classID='1' and weeks=1 and lessons=6

6．数据删除举例

删除一条排课信息

use lesson
go
delete from  course_schdule
where classID='1' and weeks=1 and lessons=6

7．创建函数举例

创建函数计算制定班级所上的课程数

create function dbo.countcourses (@classid int)
returns int
as
begin
	declare @count int
	select @count=count(distinct courseID)
	from course_schdule
	where course_schdule.classID=@classid
	return @count
end

print convert(varchar(16),dbo.countcourses(1))

8．创建存储过程举例

创建存储过程生成指定班级的课程表

use lesson
go

create proc up_class(@classID int)
as
select class.classname 班级名,teacher.name 教师名,course.coursename 课程名,course_schdule.weeks 星期,course_schdule.lessons 节次,course_schdule.remark 备注
from course_schdule
	join class on course_schdule.classID=class.classID
	join teacher on course_schdule.teacherID=teacher.teacherID
	join course on course_schdule.courseID=course.courseID
where course_schdule.classID=@classID
order by course_schdule.weeks asc,course_schdule.lessons asc

execute up_class '01'

创建存储过程生成指定教师的课程表

use lesson
go

create proc up_teacher(@teacherID int)
as
select teacher.name 教师名,class.classname 班级名,course.coursename 课程名,course_schdule.weeks 星期,course_schdule.lessons 节次,course_schdule.remark 备注
from course_schdule
	join class on course_schdule.classID=class.classID
	join teacher on course_schdule.teacherID=teacher.teacherID
	join course on course_schdule.courseID=course.courseID
where course_schdule.teacherID=@teacherID 
order by course_schdule.weeks asc,course_schdule.lessons asc

execute up_teacher '0001'

创建存储过程生成指定学生的课程表

use lesson
go

create proc up_student(@studentID int)
as
begin
	declare @classid int
	select @classid=student.classID
	from student
	where student.studentID=@studentID

	select student.studentID 学生ID,student.name 学生名,teacher.name 教师名,class.classname 班级名,course.coursename 课程名,course_schdule.weeks 星期,course_schdule.lessons 节次,course_schdule.remark 备注
	from course_schdule
		join class on course_schdule.classID=class.classID
		join teacher on course_schdule.teacherID=teacher.teacherID
		join course on course_schdule.courseID=course.courseID
		join student on student.classID=@classid
	where course_schdule.classID=@classid and student.studentID=@studentID
	order by course_schdule.weeks asc,course_schdule.lessons asc
end

execute up_student '1203'

创建存储过程检测指定教师、指定节次是否有课；

use lesson
go

create proc up_search(@teacherID int,@week char(20),@lesson char(20))
as
begin
	declare @teachername char(10)
	select @teachername=teacher.name
	from teacher
	where teacher.teacherID=@teacherID 
	if exists(select *
				from course_schdule
					where course_schdule.teacherID=@teacherID and course_schdule.weeks=@week and course_schdule.lessons=@lesson )
	    print convert(varchar(4),@teachername)+'老师在星期'+convert(varchar(2),@week)+'的第'+convert(varchar(2),@lesson)+'节 有课！'
	else
		print convert(varchar(4),@teachername)+'老师在星期'+convert(varchar(2),@week)+'的第'+convert(varchar(2),@lesson)+'节 没课！'
end

execute up_search '003','3','4'

execute up_search '001','2','5'

9．创建触发器举例

创建触发器检测添加的排课信息是否与已有课表冲突；

use lesson
go
create trigger T_insert
on course_schdule
instead of insert
as
	if exists(select *
				from course_schdule,inserted
					where course_schdule.teacherID=inserted.teacherID and course_schdule.weeks=inserted.weeks and course_schdule.lessons=inserted.lessons and course_schdule.classID=inserted.classID and course_schdule.courseID=inserted.courseID)
	begin 
	 raiserror('该节次已经有课了，请重新添加排课信息！',16,1)
	 rollback
	end

insert into course_schdule values('2','005','0011',2,4,'第2周到第3周')

insert into course_schdule values('3','006','0009',2,4,'第2周到第3周')

10．创建视图举例

创建视图，通过该视图能显示学生的ID，姓名，所上的课程名，教师名，所在班级名，和课程的星期、节次、备注。

use lesson
go
create view v_stu
as
select student.studentID 学生ID,student.name 学生名,teacher.name 教师名,class.classname 班级名,course.coursename 课程名,course_schdule.weeks 星期,course_schdule.lessons 节次,course_schdule.remark 备注
from course_schdule
	join class on course_schdule.classID=class.classID
	join teacher on course_schdule.teacherID=teacher.teacherID
	join course on course_schdule.courseID=course.courseID
	join student on student.classID=class.classID

select *
from v_stu
where 学生ID='1201'

• C#程序应用举例

使用C#登陆到SQL数据库中

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace 登录
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("server=.;database=lesson;uid=sa;pwd=123");
            bool error = false;
            int num = 0;
            try
            {
                string sql = string.Format("select count(*) from stu where sname='{0}' and password='{1}'", textBox1.Text.Trim(), textBox2.Text.Trim());
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);
                num = Convert.ToInt32(command.ExecuteScalar());
            }
            catch (Exception ex)
            {
                error = true;
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            if (!error && (num == 1))
            {
                MessageBox.Show("登录成功！");
            }
            else
            {
                MessageBox.Show("输入的用户名或密码有误！", "登录提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}

五、系统数据库维护与管理

1．系统数据库备份和还原方案

（1)备份方式

针对不同数据库系统的实际情况，SQL server 2005提出了四种备份方式。

• 全库备份：备份整个数据库的副本
• 日志备份：可以在意外发生时将所有以及提交的事物全部恢复。
• 差异备份：备份自上次全库备份以来被修改的数据页。
• 文件和文件组备份：单独备份组成数据库的文件或文件组。

（2)备份和还原方案

• 备份方案

根据教材管理系统运行的实际情况有规律地进行完全备份，每晚进行或者每星期进行一次；其次以较小的时间间隔进行差异备份，每隔几小时就进行一次；最后，在相邻的两次差异备份之间进行事务日志备份，如每隔5min进行一次。

• 还原方案

首先利用最近一次全库备份进行全库备份的恢复；其次进行最佳一次差异备份的恢复；最后再按时间先后顺序进行事务日志备份的恢复。

2．系统数据库安全性管理

（1)SQL server的安全体系结构可划分为4个等级：

• 客户机操作系统的安全性
• SQL server的登录的安全性
• 数据库的安全性
• 数据库对象的安全性

（2)客户机安全认证

用windows操作系统登录用户实现客户机的安全性。

（3)服务器安全认证

SQL server安全认证模式分为windows身份验证和SQL server身份验证两种方式。创建登录账户时也分为两种模式的账户。

（4)数据库安全认证

数据库的访问权时通过映射数据库的用户和登录账户之间的关系来实现的。数据库用户是用来指出哪个人可以访问哪个数据库。当登录账户通过了认证后，必须设置数据库用户才可以对数据库及其对象进行操作。一个登录账户在不同的数据库中可以映射成不同的数据库用户，从而可以具有不同的权限。

（5)数据库对象安全认证

数据库对象的安全性是通过数据库用户的角色和权限来实现的。

• 角色

角色分为服务器角色和数据库角色。服务器角色是SQL server 2005赋予用户对服务器操作的权限，分为8种。服务器角色是固定的，不能添加和删除。数据库角色是SQL server 2005赋予用户对数据库操作的权限。在SQL server中有十种固定的数据库角色。除了10种固定数据库角色外，SQL server允许创建新的数据库角色。

• 权限

权限分为三种：对象权限（指用户对数据库中的表、视图、存储过程等对象的操作权限）、语句权限（指执行数据定义语句的权限）和隐含权限（指系统预定义的服务器角色、数据库拥有者、数据库对象所拥有的权限）。