课程设计说明书3

课程设计说明书

设计题目：专 业：设 计 人：

学生选课管理 _

信息与计算科学 班 级： 12级 _

胡上彪 李肖 _

山东科技大学

2015年 6 月 27 日

课 程 设 计 任 务 书

一、课程设计题目： 学生选课管理

二、课程设计应解决的主要问题：

（1） 对表的一些简单的增删改查操作和复杂的SQL语句的查询操作 （2） 使用了事务和创建了一个计算男女比例的自定义标量函数和一个查询一个特定学号 学生的平均分的自定义表值函数 （3） 分别创建了一个为选课学生的视图和课程信息的视图 （4） 分别创建了一个打印学生信息表的游标和一个根据分数计算等级列的游标 （5） 分别创建一个查看特定学号学生特定的成绩等级和一个计算特定学号学生总分的两个 存储过程 （6） 分别创建一个为选课的学生不能从学生实体中删除的DML触发器和防止数据库被修 改（进行的表的创建，修改和删除操作）的DDL触发器 （7） 分别对学生表和教师表创建了索引

三、任务发出日期： 2014-4-25 课程设计完成日期： 2014-6-27

小组分工说明

小组编号 2 题 目： 学生选课管理 小组分工情况：

胡上彪:

完成数据库及各表结构的创建和实现了复杂的查询语句；完成使用事务和创建了一个计算男女比例的自定义标量函数；创建了一个为选课学生的视图和教师讲授的视图；创建了一个打印学生信息表的游标；创建一个查看特定学号学生特定的成绩等级；创建一个为选课的学生不能从学生实体中删除的DML触发器；分别对学生表和教师表创建了索引。 李肖:

对表的一些简单的增删改查操作；创建了一个查询一个特定学号学生的平均分的自定义表值函数；创建了课程信息的视图；创建了一个根据分数计算等级列的游标；创建一个计算特定学号学生总分的两个存储过程；创建防止数据库被修改的DDL触发器。

组长签字：

年 月 日

指导教师对课程设计的评价

指导教师签字：

年 月 日

目 录

1 需求分析 …………………………………………………………1

1.11.21.31.4

需求背景…………………………………………………………1 选课分析……………………………………………………………1 数据事实……………………………………………………………1 主要用户视图………………………………………………………3

2 概念结构设计 …………………………………………………………4 2.1局部ER图……………………………………………………………4 2.2全局ER图…………………………………………………………5 3 逻辑结构设计…………………………………………………………5 3.1关系模型………………………………………………………5 3.2关系数据表………………………………………………………5 3.3数据字典…………………………………………………………7 4 物理结构设计 …………………………………………………………7 4.1创建数据库……………………………………………………7 4.2 创建数据库的表…………………………………………………………8 5 主要功能详细设计设计 ……………………………………………………9 5.15.2 5.3 5.4 5.5 5.6 5.7

实现数据查询…………………………………………………………9 创建自定义函数………………………………………………………9 创建视图…………………………………………………………10 创建游标…………………………………………………………11 创建存储过程…………………………………………………………12 创建触发器…………………………………………………………14 创建事务和索引……………………………………………………………14

1 需求分析

1.1：需求背景

随着信息时代科技不断提高,学校规模不断扩大,教学质量不断提高,大学期间有许多公共选修课,以往的选修课方法是课堂报名或者纸上填写方式报名,这种方法虽然直接,但是造成选课的盲目性,而且学生选过课程后不好在更改查看,一方面浪费大量的人力，物力资源,另一方面浪费时间以及在认为统计过程中不可避免的出现差错的情况。这给广大的老师和学生带来诸多的不便,管理起来也相当困难。使用网上选课系统可以提前进行网上选课,并且选课以后学生还可以在查看和更改,随意选择各个老师的课程,老师管理起来也比较方便。鉴于它的重要性,这就迫切需要研制开发一款功能强大,操作简单,具有人性化的网上选课系统。

1.2：选课分析

根据分析，本系统的实体有学生、教师、课程，而联系有选课信息和讲授。

描述学生的属性有：学号、院系、姓名、性别、出生日期、入学时间、专业、班级。 描述教师的属性有：教师编号、姓名、性别、职称、专业。

描述课程的属性有：课程编号、课程名称、课程类型、学时、学分。 描述选课信息的属性有：学号、课程编号、考试成绩。

描述讲授的属性有： 教师编号、课程编号、上课时间、上课地点。 1.3：数据事实

用户的需求具体体现在各种信息的提供、保存、更新和查询上，这就要求数据库结构能够满足各种信息的输入与输出。在对学生选课管理进行分析的基础上，本系统所涉及的主体共有5个数据表：学生表，教师表，课程表，选课信息表，讲授表，分别如下图所示。

表1-1 学生表

1

表1-2 教师表

表1-3 课程表

表1-4 选课信息表

2

表1-5 讲授表

1．4 主要用户视图

以下分别为选课学生的视图，教师讲课视图和选课信息视图。

图1-1 选课学生的视图

图1-2 教师讲课的视图

3

图1-3 选课信息的视图

2 概念结构设计

2．1 局部ER图 学号 课程名称 课程编 号 学生 姓名 性别 出生日期 教师 入学时间 院系 专业 班级 教师编号 姓名 性别 职称 所属专业 课程 课程类型 4 学时 学分 2．2 全局ER图 数据库的E-R图 学号成绩 学生 m 姓名 性别 出生日期上课 时间 选课 n n m 教师编号 入学时间 上课地点 姓名 院系 性别 专业 班级 课程 讲授 教师 职称 专业 课程 编号 课程名称 课程 类型 学时 学分 3 逻辑结构设计

3.1 关系模型：

学生（学号、姓名、性别、出生日期、入学时间、院系、专业、班级） 教师（教师编号、姓名、性别、职称、专业）

课程（课程编号、课程名称、课程类型、学时、学分） 选课信息（学号、课程编号、考试成绩）

讲授（教师编号、课程编号、上课时间、上课地点） 3.2关系数据表:

5

属性名 学号 姓名 数据类型 char(10) char(10) 学生表 可否为空 否 否 否 否 否 否 否 否 教师表 可否为空 否 否 否 否 否 课程表 可否为空 否 否 否 否 可以 可以 可以 可以 完整性约束 主码 性别 char(2) 出生日期 char(10) 入学时间 Char(10) 专业 char(10) 院系 char(10) 班级 char(10) 属性名 教师编号 姓名 性别 职称 专业

属性名 教师编号 课程名称 授课教师 课程类型 上课时间 上课地点 学时 学分

属性名 学号 课程编号 成绩

数据类型 char(10) char(10) Char(10) char(10) char(10) char(10) Char(10) Char(10) 数据类型 char(10) char(10) char(2) char(10) char(20) 完整性约束 主码 完整性约束 主码 选课信息表 数据类型 可否为空 char(10) 否 char(10) 否 Char(10) 否 完整性约束 主码 讲授表

属性名 课程号 教师号 上课时间

6

数据类型 char(10) char(10) Char(10) 可否为空 否 否 否 完整性约束 主码

3.3数据字典：

数据项编号 JS001 JS002 JS003 数据项名称 学生编号 学生学院 学生姓名 学生性别 学生出生日期 学生入学时间 学生专业 学生班级 教师编号 教师姓名 教师性别 教师职称 教师专业 课程编码 课程名称 课程类型 上课时间 上课地点 上课学时 上课学分 学生课程成绩 存储结构 Char(10) Char(20) Char(10) Char(2) Char(10) Char(10) Char(10) Char(10) Char(10) Char(10) Char(2) Char(10) Char(10) Char(10) Char(20) Char(20) Char(20) Char(20) Char(10) Char(10) Char(10) 别名 学号 学院 姓名 性别 出生日期 入学时间 专业 班级 编号 姓名 性别 职称 专业 课程号 课程名 类型 时间 地点 学时 学分 成绩

JS004 JS005 JS006 JS007

JS008 JS009

JS0010 JS0011

JS0012 JS0013 JS0014

JS0015 JS0016

JS0017 JS0018 JS0019 JS0020 JS0021 4 物理结构设计

--创建数据库和表

CREATE DATABASE 学生选课管理 GO

USE 学生选课管理

7

CREATE TABLE 学生表 (

学号 char(10) NOT NULL PRIMARY KEY, 姓名 char(10) NOT NULL,

性别 char(2) NOT NULL default '男' check(性别='男' or 性别='女'), 出身日期 char(10) NOT NULL, 入学时间 char(10) NOT NULL, 专业 char(10) NOT NULL, 班级 char(10) NOT NULL, 学院 char(10) NOT NULL )

CREATE TABLE 教师表 (

教师编号 char(10) NOT NULL PRIMARY KEY, 姓名 char(10) NOT NULL,

性别 char(2) NOT NULL default '男' check(性别='男' or 性别='女'), 职称 char(10) NOT NULL, 所属专业 char(20) NOT NULL )

CREATE TABLE 课程表 (

课程编号 char(10) NOT NULL primary key, 课程名称 char(20) NOT NULL, 课程类型 char(10) NOT NULL, 学时 char(10), 学分 char(10) )

Create table 选课信息表 (

学号 char(10), 课程编号 char(10), 考试成绩 Int,

Primary key(学号,课程编号),

Foreign key(学号) references 学生表(学号),

Foreign key(课程编号) references 课程表(课程编号) )

Create table 讲授表 (

课程号 char(10), 教师号 char(10),

上课时间 char(20) NOT NULL, 上课地点 char(10) NOT NULL,

8

Primary key(教师号,课程号),

Foreign key(教师号)references 教师表(教师编号), Foreign key(课程号) references 课程表(课程编号) )

5 主要功能详细设计

5．1 功能：通过sql语句实现对表中信息的查询 （1）.实现代码：简单 （主要完成人：李 肖 ）

USE 学生选课管理

delete from 学生表 where 学号='s0012'

update 学生表 set 学院 = '数理学院', 专业='数学', 班级= '1班'where 学号='s0001' insert into 学生表

values ('s0012','蔡文姬','女','19900807','20080901','化学','5班','化工学院')

select * from 学生表 where 学号='s0001' select * from 学生表 where 姓名='曹操' select * from 学生表 where 性别='女' select * from 学生表 where 专业='数学' select * from 选课信息表,课程表

where 选课信息表.学号='s0002' and 选课信息表.课程编号=课程表.课程编号

（2）实现代码：通过复杂sql语言实现复杂的查询功能 （主要完成人：胡上彪 USE 学生选课管理 select * from 学生表 where 学生表.学号=( select 选课信息表.学号 from 选课信息表

group by 选课信息表.学号 having count(选课信息表.学号)!<4)

select * from 学生表

where 学生表.学号 in(select 选课信息表.学号 from 选课信息表

where 选课信息表.考试成绩!<60 and 学生表.姓名 like'孙%' and 学生表.性别='女')

select 选课信息表.课程编号,avg(选课信息表.考试成绩) as 平均分 from 选课信息表 group by 选课信息表.课程编号 having count(选课信息表.学号)>=3

5．2 主要应用（函数）

（1）.功能：实现对选课学生的男女比例调查（主要完成人：胡上彪 ）

9

）

实现代码：

USE 学生选课管理 GO

IF EXISTS(SELECT * FROM sysobjects WHERE name='fun1' and type='FN') DROP FUNCTION fun1 GO

create function fun1() returns float as begin

declare @b float,@g float

select @b=count(*) from 学生表 where 性别='男' select @g=count(*) from 学生表 where 性别='女' return @b/@g end go

PRINT '该学校的男女比例为:'+CAST(dbo.fun1() AS char(10)) GO

（2）.功能：按学号查询某学生成绩的最低分 （主要完成人：李 肖 ）

USE 学生选课管理 GO

IF EXISTS(SELECT * FROM sysobjects WHERE name='fun2' and type='TF') DROP FUNCTION fun2 GO

create function fun2(@xh char(10)) returns @st TABLE (

min float ) as begin

INSERT @st

select MIN(选课信息表.考试成绩) FROM 选课信息表

where 选课信息表.学号=@xh AND 选课信息表.考试成绩 IS NOT NULL return end

5．3主要应用（视图）

（1）功能：创建了学生选课的视图和教师教授的视图 （主要完成人：胡上彪） 实现代码：

USE 学生选课管理 go

create view 选课学生的视图 as

select S.学号, S.姓名,

S.学院, C.课程编号, C.课程名称, SE.考试成绩 from 学生表 S join 选课信息表 SE on S.学号=SE.学号 join 课程表 C on C.课程编号=SE.课程编号

10

go

USE 学生选课管理

SELECT * FROM 选课学生的视图

USE 学生选课管理 go

create view 教师讲授的视图 as

select T.教师编号, T.姓名,

T.性别, T.职称, C.课程名称, C.学时, SE.上课时间, SE.上课地点

from 教师表 T join 讲授表 SE on T.教师编号=SE.教师号 join 课程表 C on C.课程编号=SE.课程号 go

USE 学生选课管理

SELECT * FROM 教师讲授的视图

（2）.功能：构建选课信息视图 （主要完成人：李 肖 ）

实现代码：

GO

create view 课程信息的视图 as

select C.课程编号, C.课程名称,C.学分,C.学时,T.姓名,J.上课地点,J.上课时间 from 课程表 C join 讲授表 J on C.课程编号=J.课程号 join 教师表 T on T.教师编号=J.教师号 go

USE 学生选课管理

Select * FROM 课程信息的视图 go

5．4主要应用（定义游标和使用游标）

（1）.功能：打印各个学生的平均分 （主要完成人：胡上彪 ）

实现代码：

USE 学生选课管理 go

set nocount on

declare @sno char(10),@sname char(10),@savg float declare st_cursor CURSOR

FOR SELECT 学生表.学号,学生表.姓名,AVG(选课信息表.考试成绩) from 学生表,选课信息表

where 学生表.学号=选课信息表.学号 and 选课信息表.考试成绩 IS NOT NULL group by 学生表.学号,学生表.姓名 order by 学生表.学号 open st_cursor

fetch next from st_cursor into @sno,@sname,@savg print '学号 姓名 平均分'

print '--------------------------------------'

11

while @@FETCH_STATUS=0 begin

print @sno+' '+@sname+' '+cast(@savg as char(6)) fetch next from st_cursor into @sno,@sname,@savg end

close st_cursor deallocate st_cursor GO

（2）.功能：根据成绩评定学生等级 （主要完成人：李 肖 ）

实现代码：

USE 学生选课管理

alter table 选课信息表 add 等级 char(2) go

declare s_cursor CURSOR FOR

SELECT 选课信息表.考试成绩 from 选课信息表 where 选课信息表.考试成绩 IS NOT NULL declare @fs int,@dj char(2) open s_cursor

fetch next from s_cursor into @fs while @@FETCH_STATUS=0 begin

set @dj=case

when @fs>=90 then 'A' when @fs>=80 then 'B' when @fs>=70 then 'C' when @fs>=60 then 'D' when @fs<60 then 'E' end

update 选课信息表 set 等级=@dj where current of s_cursor

fetch next from s_cursor into @fs end

close st_cursor deallocate st_cursor GO

select * from 选课信息表 order by 学号 go

alter table 选课信息表 drop column 等级 GO

5．5主要应用（存储过程）

（1）.功能：按学号查询并输出某学生所选课程的成绩总和（主要完成人：李 肖 ） 实现代码：

USE 学生选课管理 GO

12

IF EXISTS(SELECT * FROM sysobjects WHERE name='sum' and type='P') drop procedure sum GO

create proc sum ( @s_no char(10),

@s_name char(10) output, @s_sum float output )as

select @s_name=学生表.姓名,@s_sum=SUM(选课信息表.考试成绩) from 选课信息表,学生表

where 学生表.学号=选课信息表.学号 group by 学生表.学号,学生表.姓名 having 学生表.学号=@s_no go

USE 学生选课管理

IF EXISTS(SELECT * FROM sysobjects WHERE name='sum' and type='P') begin

DECLARE @st_name char(10) DECLARE @st_sum float

EXEC sum 's0003',@st_name output,@st_sum output select '姓名'=@st_name,'总分'=@st_sum end GO

（2）功能：通过存储过程实现对学生成绩的评级 （主要完成人：胡上彪 ）

实现代码：

USE 学生选课管理 GO

IF EXISTS(SELECT * FROM sysobjects WHERE name='stud_degree' and type='P') drop procedure stud_degree GO

create proc stud_degree(@学号 char(10)='s0003',@课程号 char(10)='c0001',@成绩等级 char(1) output)as begin

select @成绩等级= case

when 考试成绩>=90 then 'A' when 考试成绩>=80 then 'B' when 考试成绩>=70 then 'C' when 考试成绩>=60 then 'D' when 考试成绩<60 then 'E' end

from 选课信息表

where 学号=@学号 and 课程编号=@课程号 end

13

go

5．6主要应用（触发器） （1）功能：通过触发器实现未选课的学生不能从学生实体中删除（主要完成人：胡上彪 ）

实现代码：

USE 学生选课管理 GO

IF EXISTS(SELECT * FROM sysobjects WHERE name='SC_studentdel' and type='TR') drop trigger SC_studentdel GO

create trigger SC_studentdel on 学生表 instead of delete as begin

declare @temp char(10)

select @temp=学号 from deleted

if (select count(选课信息表.学号) from 选课信息表 where 选课信息表.学号=@temp)=0 print'未选课的学生不能从学生实体中删除' end go

USE 学生选课管理

IF EXISTS(SELECT * FROM sysobjects WHERE name='SC_studentdel' and type='TR') delete from 学生表 where 学号= 's0012' GO

（2）.功能：数据库中各表一经实现不可更改 （主要完成人：李 肖 ）

实现代码：

USE 学生选课管理 GO

create trigger safe

on DATABASE AFTER DROP_TABLE,ALTER_TABLE,CREATE_TABLE as begin

RAISERROR('不能修改表结构',16,2) ROLLBACK end go

USE 学生选课管理

ALTER TABLE 学生表 ADD 民族 char(5) GO

5．7主要应用（事务和索引） （主要完成人：胡上彪 ） 实现代码：

USE 学生选课管理 go

begin tran mytran

14

insert into 学生表

values ('s0013','张春华','女','19900227','20080901','化学','2班','化工学院') save tran mytran insert into 学生表

values ('s0014','祝融','女','19920807','20080901','化学','1班','化工学院') ROLLBACK TRANSACTION mytran COMMIT TRANSACTION GO

SELECT * FROM 学生表 Go

USE 学生选课管理

IF EXISTS(SELECT name FROM sysobjects WHERE name='IDX_tno') drop INDEX 教师表.IDX_tno GO

CREATE INDEX IDX_tno ON 教师表(教师编号) GO

USE 学生选课管理 go

EXEC sp_helpindex 教师表 GO

USE 学生选课管理

IF EXISTS(SELECT name FROM sysobjects WHERE name='IDX_bhname') drop INDEX xuesb.IDX_bhname GO

CREATE INDEX IDX_bhname ON 学生表(专业,姓名) GO

USE 学生选课管理 go

EXEC sp_helpindex 学生表 GO

15

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