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

一、系统分析

需求功能分析

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

为了完成需求分析的任务，应详细调查待开发的数据库应用程序部门，了解原始系统的工作概况，分析用户的各种需求，并在此基础上确定新系统的功能。新系统必须考虑未来的扩展和变化，而不仅仅是根据当前的应用程序需求来设计数据库。

课程安排管理系统需要对学校教师、教室、班级和课程进行信息管理，对高校课程安排进行智能管理，主要实现以下功能：

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

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

增加、删除和修改学校课程。

制定学校课程计划。

测试指定教师和指定节目是否有课；

生成指定班级的课程表；

生成指定教师的课程表；

生成指定学生的课程表；

 

 

二、系统数据库设计

1．概念设计

排课管理系统涉及学生、班级、教师、课程四个实体。

班级与学生有一对多的关系；

班级与课程多对多；

班级与教师多对多；

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

图1 学生实体E-R图图2 班级实体E-R图图3 教师实体E-R图图4 课程实体E-R图图5 E-R学生有四个属性：学生ID、姓名、性别、班级ID。学生ID是主键，班级ID是外键。教师有四个属性：ID、姓名、性别、年龄。教师ID是主键。班级有两个属性：ID、班级名称。班级ID是主键。课程有两个属性：课程ID、课程名称。课程ID是主键。联系有1个，联系也可以转换为实体。故排课产生课程表。课程有六个属性:班级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 pri ** ry key,

classname char(20)

)

Go

--创建student表

create table student

(

studentID int pri ** ry key,

name   char(10) not null,

**     char(2)  not null check( ** ='F' or  ** ='M'),

classID   int references class(classID)

)

Go

--创建teacher表

create table teacher

(

teacherID int pri ** ry key,

name   char(10) not null,

**     char(2)  not null check( ** ='F' or  ** ='M'),

age    int    not null

)

Go

--创建course表

create table course

(

courseID   int pri ** ry 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),

re ** rk     char(100),

constraint pk_schdule pri ** ry 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',&#39；营销')

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,&#39；第一周到第五周；')

insert into course_schdule values('1','001','0004',1,6,&#39；第一周到第五周；')

insert into course_schdule values('1','002','0007',1,1,&#39；第一周到第六周；')

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. **  性别,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

endprint 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.re ** rk 备注

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 ascexecute 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.re ** rk 备注

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 ascexecute 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.re ** rk 备注

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

endexecute 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)+'节 没课！'

endexecute 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

endinsert 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.re ** rk 备注

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.classIDselect *

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.For ** t("select count(*) from stu where sname='{0}' and password='{1}'", textBox1.Text.Trim(), textBox2.Text.Trim());

conn.Open();

SqlCom ** nd com ** nd = new SqlCom ** nd(sql, conn);

num = Convert.ToInt32(com ** nd.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允许创建新的数据库角色。

权限

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