一、 概要设计

1.1目的和意义
学生选课系统作为一种现代化的教学技术，被广泛应用于学校的学生选课，是一个学校不可或缺的部分, 学生选课系统使管理选课信息更加简单，学生选课更加方便。学生选课系统的将使选课管理工作规范化、系统化、程序化，避免选课管理的无条理性，提高信息处理的速度和准确性，能够准确、及时、有效的查询和修改学生选课情况。
与传统的选课方式相比，网上选课系统利用局域网为学生选课带来了极大的便捷。学生在公共机房，或者宿舍的个人电脑上便可以通过校园网络来选课。在选课时间内，学生能够使用选课系统灵活的修改自己的选课情况，大大提高了学校选课工作的效率。老师则可以通过选课系统的管理员子系统来管理学生的选课情况，使得学生选课工作达到系统化和自动化，大大提高了学校的工作效率，为广大师生及相关人员节省了极多的时间、提供了极大的便利。

1.2内容和要求

• 数据库需要有以下信息：

  学生：学号、单位名称、姓名、性别、年龄、选修课程名
  课程：编号、课程名、开课单位、任课教师号
  教师：教师号、姓名、性别、职称、讲授课程编号
  单位：单位名称、电话、教师号、教师姓名

• 上述实体中存在如下联系：
  （1）一个学生可选修多门课程，一门课程可被多个学生选修。
  （2）一个教师可讲授多门课程，一门课程可由多个教师讲授。
  （3）一个单位可有多个教师，一个教师只能属于一个单位。

二、 需求分析

2.1背景
全校性选修课开设的目的在于扩大学生的知识面、加强学生素质教育、培养复合型高级人才，具有不可替代的重要性。随着教育改革的不断深入和素质教育的加强，学分制的实施，选修课在一个学生的培养计划中占的比重将越来越大。
网上选课系统的出现使同学们能够更加自主、便捷、准确的进行选课。但是，由于一般高校中的学生都比较多，因此带来了诸多如信息管理等问题，鉴于需要将学生信息、选课信息等信息数字化以便于管理维护，我们便想到了利用数据库能够比较良好地解决此类问题，由此下面我将设计出一个高校选课系统以供参考。
2.2概要分析
我们的高校选课系统将包含学生、教师等实体，学生可以在规定的时间内选课、退选和成绩查询等操作；教师可以查看学生的相关信息，录入学生成绩等操作；管理员可以添加管理员，管理教师、学生等信息。

三、E-R图

3.1概念设计
在我们的数据库系统中共有4个实体：学生、课程、教师、单位

• 学生：学号、所在系、姓名、性别、年龄

• 课程：编号、课程名、开课单位、任课教师号

• 教师：教师号、姓名、性别、职称、所在系

• 单位：单位名称、电话、教师号、教师姓名

3.2 E-R图

四、逻辑结构

4.1逻辑转换
根据E-R图，可以将系统中的概念模型转换为关系模型，详细信息如下图所示：(下划线的为主码， 斜体为外码)

4.2细化表结构
根据上述文字描述，用英文简写为表和列取名，确定列的数据类型及必要的约束规则，给出如下所示数据库表的基本结构及说明：
(1)单位信息表

（2）学生信息表

（3）课程表

（4）教师表

（5）学生选课信息表

（6）教师选课信息表

五、数据库实施

5.1创建表
（1）创建数据库

create database SCSMS
go
use SCSMS
go

（2）dept院系信息表

create table dept
(
	dname char(20) primary key,  --系名
	dphone char(15) ,			 --电话
	teachernum char(15) ,		 --教师号
	teachername char(20) 		 --教师姓名
)

（3）student学生表

create table student
(
	sno char(10) primary key,  --学号
	sname char(20),           --姓名
	ssex char(2),             --性别
	sage tinyint,             --年龄
	sdept char(20),           --所在系
	foreign key(sdept) references dept(dname), 
	constraint s_1 check(sage>=5 and sage<=30),  --检查年龄是否在~30岁之间
	constraint s_2 check(ssex='男' or ssex='女') --检查性别是否为男或女   
)
go

(5)course课程表

create table course
(
	cno char(10) primary key,  --课程号
	cname char(24),            --课程名
	cdept char(20),            --课程所属系
	ccredit tinyint,           --学分
	foreign key(cdept) references dept(dname), 
	constraint c_1 check(ccredit>=0 and ccredit<=8), --检查学分范围是否为~8
)
go

（6）teacher教师表

create table teacher
(
	tno char(10) primary key,   --教师号
	tname char(20),             --教师姓名
	tsex  char(2),              --性别
	ttitle char(30),            --职称
	tdept char(20),             --所在系
	foreign key(tdept) references dept(dname),
	constraint t_1 check(tsex='男' or tsex='女') --检查性别是否为男或女	
)
go

（7）s_course学生选课表

create table s_course
(
	sno char(10),               --学生学号
	sname char(20),             --学生姓名
	cno char(10),               --所选课程号
	cname char(24),             --所选课程名
	cgrade tinyint,             --所选课程成绩
	primary key(sno,cno),       --学号和课程号为选课表的主码
	foreign key(cno) references course(cno), 
	foreign key(sno) references student(sno)
	 on delete cascade   on update cascade,  --当删除、更新student表中的元组时，级联删除、更新相应的元组
	foreign key(cno) references course(cno)
	 on delete no action   on update cascade --当删除course表中元组造成与s_course表中不一致时，拒绝删除
)
go

（8）t_course教师授课表

create table t_course
(
	tno char(10),               --教师号
	tname char(20),             --教师姓名
	cno char(10),               --所授课程号
	cname char(24),             --所选课程名
	ccredit tinyint,            --所授课学分
	primary key(tno,cno),       --教师号和课程号为主码 
	foreign key(tno) references teacher(tno)
	on delete cascade   on update cascade,   --当删除、更新teacher表中的元组时，级联删除、更新相应的元组
)
go

5.2创建必要的视图

（1）–建立学生选课信息视图vi_student

```sql
create view vi_student
as
select course.cname, s_course.sno, s_course.sname, s_course.cgrade
from course, s_course
where course.cno = s_course.cno
go

select *
from vi_student
go

（2）–建立学生成绩视图vi_grade

create view vi_grade
as
select s_course.sname, s_course.cname, s_course.cgrade
from s_course
go

（3）–建立数学系学生的视图MA_student,保证在进行修改和插入时只有数学系的学生

create view MA_student
as
select *
from student 
where sdept='math'
with check option;
go

(4) --建立反映学生出生年份视图

create view BT_S(sno,sname,sbirth)
as
select sno,sname,2020-sage
from student 
go
 

5.3创建必要的触发器

（1）–建立学生触发器tri_addstudent,当学生选课信息中专业名不对
–或者系统中已存在对应学号的学生时，系统给出错误提示并回滚

create trigger tri_addstudent on student
for insert, update
as
if(select count(*)from dept, inserted
where dept.dname = inserted.dname) = 0
begin
print'未找到该学生的专业信息，请添加相关专业后重试'
rollback
end
else if
(select count(*)from student, inserted
where student.sno = inserted.sno) > 0
begin
print'学号产生冲突！'
rollback
end
go

（2）–建立学生选课存储过程，查询所有学生的选课记录

create proc proc_course
@sno char(10) out,
@sname char(20) out,
@cname char(24) out,
@ccredit tinyint out
as
select @sno = student.sno,
	   @sname = student.sname,
	   @cname = course.cname,
	   @ccredit = course.ccredit
from s_course, student, course
where s_course.sno = student.sno 
	  and s_course.cno = course.cno
go

（3）–触发器当输入、修改的cgrade大于100时，将值改为100

create trigger t1 on s_course  
for insert,update
as
select * from inserted
declare @a smallint
select @a=cgrade from inserted
if @a>100
update s_course
set cgrade=100 where sno=(select sno from inserted)
go

5.4创建必要的存储过程

（1）–建立学生选课存储过程，查询所有学生的选课记录

create proc proc_course
@sno char(10) out,
@sname char(20) out,
@cname char(24) out,
@ccredit tinyint out
as
select @sno = student.sno,
	   @sname = student.sname,
	   @cname = course.cname,
	   @ccredit = course.ccredit
from s_course, student, course
where s_course.sno = student.sno 
	  and s_course.cno = course.cno
go

5.5插入数据
（1）–插入数据到dept表中

insert into dept
values
('cs','52539707','01','刘友'),
('fl','52517090','02','刘林'),
('math','52517099','03','刘永亿')
go

（2）–插入数据到student表中

insert into student(sno,sname,ssex,sage,sdept)
values
( '19436125','李四','女',18 , 'cs'),
( '19436126','王铁锤','女',18, 'cs') ,
( '19436136','徐五','女',18, 'cs' ),
('19436143','张四','女',18 , 'cs') ,
( '19420242','张三','男',19, 'cs') ,
( '19420246','朱波','男',19 , 'cs'),
('19405129','段菲','男',19,'fl'),
('19405128','程紫','女',19,'fl'),
('19410311','赵强','男',19,'math'),
('19410312','李宇','男',19,'math')

（3）–插入数据到teacher表中

insert into teacher(tno,tname,tsex,ttitle,tdept)
values
('690508','李成','男','assistant professor','cs'),
('700508','王英','女','lecturer','cs'),
('700509','陈丽','女','lecturer','fl'),
('700510','陈庆','男','assistant professor','math'),
('700511','刘永','男','assistant professor','math'),
('700512','王立','女','lecturer','math')

（4）–插入数据到course表中

insert into course(cno,cname,cdept,ccredit)
values 
('1','数据库','cs',null),
('2','数据结构','cs',null),
('3','高等数学','math',null),
('4','概率论','math',null),
('5','离散数学','math',null),
('6','英语','fl',null)
go

update course 
set ccredit = '2'
where cno = '1'
go
update course
set ccredit = '2'
where cno = '2'
go
update course
set ccredit = '6'
where cno = '3'
go
update course
set ccredit = '2'
where cno = '4'
go
update course
set ccredit = '4'
where cno = '5'
go
update course
set ccredit = '3'
where cno = '6'
go

（5）–插入数据s_course表中，并输入分数

insert into s_course(sno,sname,cno,cname)
values
('19436125','李四','1','数据库'),
('19436126','王铁锤','1','数据库'),
('19436136','徐五','2','数据结构'),
('19436143','张四','2','数据结构'),
('19420242','张三','3','java'),
('19420246','朱波','3','java'),
('19410312','李宇','4','概率论'),
('19410311','赵强','5','离散数学'),
('19405128','程紫','6','英语'),
('19405129','段菲','6','英语')
go
update s_course
set cgrade='99'
where sno = '19405128'
go
update s_course
set cgrade='89'
where sno = '19405129'
go
update s_course
set cgrade='95'
where sno = '19410311'
go
update s_course
set cgrade='65'
where sno = '19410312'
go
update s_course
set cgrade='88'
where sno = '19420242'
go
update s_course
set cgrade='85'
where sno = '19420246'
go
update s_course
set cgrade='89'
where sno = '19436125'
go
update s_course
set cgrade='90'
where sno = '19436126'
go
update s_course
set cgrade='92'
where sno = '19436136'
go
update s_course
set cgrade='94'
where sno = '19436143'
go

（6）–插入数据到t_course表中

insert into t_course (tno,tname,cno,cname)
values 
('690508','李芝','1','数据库'),
('700508','王英','2','数据结构'),
('700509','陈丽','6','英语'),
('700510','陈庆','3','高等数学'),
('700511','刘永','5','离散数学'),
('700512','王立','4','概率论')
update t_course 
set ccredit = '2'
where cno = '1'
go
update t_course
set ccredit = '2'
where cno = '2'
go
update t_course
set ccredit = '6'
where cno = '3'
go
update t_course
set ccredit = '2'
where cno = '4'
go
update t_course
set ccredit = '4'
where cno = '5'
go
update t_course
set ccredit = '3'
where cno = '6'
go

5.6查找表
（1）查找学生信息表

select * 
from student
go

（2）查找教师信息表

select * 
from teacher
go

（3）查找系别

select * 
from dept 
go
 

（4）查找课程信息

select * 
from course
go

（5）查找选课信息

select * 
from s_course
go
 

（6）查找教师授课信息

select * 
from t_course
go