/*
a) 创建数据库
使用T-SQL创建数据库feedback，要求：①一个主要文件（存放在第一个硬盘分区C:\project文件夹下），初始大小为10M,最大为200M,文件自动增长率为15%
②一个次要数据文件(分别存放在第二个硬盘分区D上)
③一个日志文件(存放在第三个硬盘分区E：上) ④检查数据库是否已存在，如果存在则先删除
*/
use master
if exists(select * from sysdatabases where name = 'feedback')
drop database feedback
go
create database feedback
on primary
(
name = 'feedback_data', --文件名称
filename = 'C:\project\feedback_data.mdf', --文件存储位置
size = 10mb, --初始大小
maxsize = 200mb, --最大文件大小
filegrowth = 15% --可以通过 百分比 % 指定自动增长率 或 通过 数字+ mb指定
),
(
name = 'feedback_data1', --文件名称
filename = 'C:\project\feedback_data.ndf' --文件存储位置
)
log on
(
name = 'feedback_log', --文件名称
filename = 'C:\project\feedback_data.ldf' --文件存储位置
)
go
-------------------------------------------建表-----------------------------------------------------
USE feedback
GO
/*新建 usertype 表*/
if exists(select * from sysobjects where name = 'usertype')
drop table usertype
GO
create table usertype
(
usertypeid INT IDENTITY (1, 1) NOT NULL ,
utypename VARCHAR (20) NOT NULL
)
GO
/*新建 methodtype 表*/
if exists(select * from sysobjects where name = 'methodtype')
drop table methodtype
GO
create table methodtype
(
methodtypeid INT IDENTITY (1, 1) NOT NULL ,
typename VARCHAR (20) NOT NULL,
description VARCHAR (100) NULL
)
GO
/*新建 item 表*/
if exists(select * from sysobjects where name = 'item')
drop table item
GO
create table item
(
itemid INT IDENTITY (1, 1) NOT NULL ,
itemname VARCHAR (60) NOT NULL,
methodtypeid INT NOT NULL,
usertypeid INT NOT NULL
)
GO
/*新建 template 表*/
if exists(select * from sysobjects where name = 'template')
drop table template
GO
create table template
(
templateid INT IDENTITY (1, 1) NOT NULL ,
templatename VARCHAR (30) NOT NULL,
status INT NOT NULL,
usertypeid INT NOT NULL,
usecount INT
)
GO
/*新建 templateanditem 表*/
if exists(select * from sysobjects where name = 'templateanditem')
drop table templateanditem
GO
create table templateanditem
(
id INT IDENTITY (1, 1) NOT NULL,
templateid INT NOT NULL,
itemid INT NOT NULL
)
GO
/*新建 classtype 表*/
if exists(select * from sysobjects where name = 'classtype')
drop table classtype
GO
create table classtype
(
ctypeid INT IDENTITY (1, 1) NOT NULL,
ctypename VARCHAR (20) NOT NULL
)
GO
/*新建 classinfo 表*/
if exists(select * from sysobjects where name = 'classinfo')
drop table classinfo
GO
create table classinfo
(
classid INT IDENTITY (1, 1) NOT NULL,
classname VARCHAR (30) NOT NULL,
startdate DATETIME NOT NULL,
status INT NOT NULL,
ctypeid INT NOT NULL
)
GO
/*新建 userinfo 表*/
if exists(select * from sysobjects where name = 'userinfo')
drop table userinfo
GO
create table userinfo
(
userid INT IDENTITY (1, 1) NOT NULL,
username VARCHAR (20) NOT NULL,
usertypeid INT NOT NULL
)
GO
/*新建 courseinfo 表*/
if exists(select * from sysobjects where name = 'courseinfo')
drop table courseinfo
GO
create table courseinfo
(
courseid INT IDENTITY (1, 1) NOT NULL,
coursename VARCHAR (30) NOT NULL
)
GO
/*新建 activeinfo 表*/
if exists(select * from sysobjects where name = 'activeinfo')
drop table activeinfo
GO
create table activeinfo
(
activeid INT IDENTITY (1, 1) NOT NULL,
activename VARCHAR (50) NOT NULL,
activedate DATETIME NOT NULL,
usertypeid INT NOT NULL,
userid INT NOT NULL,
courseid INT NULL,
templateid INT NOT NULL,
status INT NOT NULL
)
GO
/*新建 activeandclass 表*/
if exists(select * from sysobjects where name = 'activeandclass')
drop table activeandclass
GO
create table activeandclass
(
acid INT IDENTITY (1, 1) NOT NULL,
activeid INT NOT NULL,
classid INT NOT NULL,
useramount INT NOT NULL,
status INT NOT NULL,
total INT NOT NULL,
avg decimal(18, 2) NOT NULL
)
GO
/*新建 result 表*/
if exists(select * from sysobjects where name = 'result')
drop table result
GO
create table result
(
resultid INT IDENTITY (1, 1) NOT NULL,
ip VARCHAR (15) NOT NULL,
acid INT NOT NULL,
itemid INT NOT NULL,
userresult INT NOT NULL,
answer VARCHAR (500) NULL
)
GO
/*新建 score 表*/
if exists(select * from sysobjects where name = 'score')
drop table score
GO
create table score
(
scoreid INT IDENTITY (1, 1) NOT NULL,
acid INT NOT NULL,
itemid INT NOT NULL,
total INT NOT NULL,
avg decimal(18, 2) NOT NULL,
numexcellent INT NOT NULL,
numpoorest INT NOT NULL
)
GO
----------------------------------------------------------------------------------------------------
/*usertype表约束*/
alter table usertype add constraint PK_usertypeid primary key (usertypeid) --主键约束
alter table usertype add constraint UQ_utypename unique (utypename) --唯一约束
/* methodtype 表约束*/
alter table methodtype add constraint PK_methodtypeid primary key (methodtypeid) --主键约束
alter table methodtype add constraint UQ_typename unique (typename) --唯一约束
/* item 表约束*/
alter table item add constraint PK_itemid primary key (itemid) --主键约束
alter table item add constraint UQ_itemname unique (itemname) --唯一约束
alter table item add constraint FK_item_methodtypeid foreign key (methodtypeid) references methodtype(methodtypeid) --外键约束
alter table item add constraint FK_item_usertypeid foreign key (usertypeid) references usertype(usertypeid) --外键约束
/* template 表约束*/
alter table template add constraint PK_templateid primary key (templateid) --主键约束
alter table template add constraint UQ_templatename unique (templatename) --唯一约束
alter table template add constraint FK_template_usertypeid foreign key (usertypeid) references usertype(usertypeid) --外键约束
alter table template add constraint DF_status default(0) for status --默认
alter table template add constraint DF_usecount default(0) for usecount --默认
/* templateanditem 表约束*/
alter table templateanditem add constraint PK_id primary key (id) --主键约束
alter table templateanditem add constraint FK_templateanditem_itemid foreign key (itemid) references item(itemid) --外键约束
alter table templateanditem add constraint FK_templateanditem_templateid foreign key (templateid) references template(templateid) --外键约束
alter table templateanditem add constraint UQ_itemid_templateid unique (itemid, templateid) --唯一约束
/* classtype 表约束*/
alter table classtype add constraint PK_ctypeid primary key (ctypeid) --主键约束
alter table classtype add constraint UQ_ctypename unique (ctypename) --唯一约束
/* classinfo 表约束*/
alter table classinfo add constraint PK_classid primary key (classid) --主键约束
alter table classinfo add constraint UQ_classname unique (classname) --唯一约束
alter table classinfo add constraint DF_startdate default(getDate()) for startdate --默认
alter table classinfo add constraint DF_classinfo_status default(0) for status --默认
alter table classinfo add constraint FK_classinfo_ctypeid foreign key (ctypeid) references classtype(ctypeid) --外键约束
/* userinfo 表约束*/
alter table userinfo add constraint PK_userid primary key (userid) --主键约束
alter table userinfo add constraint UQ_username unique (username) --唯一约束
alter table userinfo add constraint FK_userinfo_usertypeid foreign key (usertypeid) references usertype(usertypeid) --外键约束
/* courseinfo 表约束*/
alter table courseinfo add constraint PK_courseid primary key (courseid) --主键约束
alter table courseinfo add constraint UQ_coursename unique (coursename) --唯一约束
/* activeinfo 表约束*/
alter table activeinfo add constraint PK_activeid primary key (activeid) --主键约束
alter table activeinfo add constraint UQ_activename unique (activename) --唯一约束
alter table activeinfo add constraint DF_activedate default(getDate()) for activedate --默认
alter table activeinfo add constraint FK_activeinfo_userid foreign key (userid) references userinfo(userid) --外键约束
alter table activeinfo add constraint FK_activeinfo_courseid foreign key (courseid) references courseinfo(courseid) --外键约束
alter table activeinfo add constraint FK_activeinfo_templateid foreign key (templateid) references template(templateid) --外键约束
alter table activeinfo add constraint DF_activeinfo_status default(0) for status --默认
/* activeandclass 表约束*/
alter table activeandclass add constraint PK_acid primary key (acid) --主键约束
alter table activeandclass add constraint FK_activeandclass_activeid foreign key (activeid) references activeinfo(activeid) --外键约束
alter table activeandclass add constraint FK_activeandclass_classid foreign key (classid) references classinfo(classid) --外键约束
alter table activeandclass add constraint UQ_activeid_classid unique (activeid, classid) --唯一约束
alter table activeandclass add constraint DF_activeandclass_useramount default(0) for useramount --默认
alter table activeandclass add constraint DF_activeandclass_status default(0) for status --默认
alter table activeandclass add constraint DF_activeandclass_total default(0) for total --默认
alter table activeandclass add constraint DF_activeandclass_avg default(0.00) for avg --默认
/* result 表约束*/
alter table result add constraint PK_resultid primary key (resultid) --主键约束
alter table result add constraint FK_result_acid foreign key (acid) references activeandclass(acid) --外键约束
alter table result add constraint FK_result_itemid foreign key (itemid) references item(itemid) --外键约束
alter table result add constraint UQ_result_ip_acid_itemid unique (ip, acid, itemid) --唯一约束
alter table result add constraint DF_userresult default(0) for userresult --默认
/* score 表约束*/
alter table score add constraint PK_scoreid primary key (scoreid) --主键约束
alter table score add constraint FK_score_acid foreign key (acid) references activeandclass(acid) --外键约束
alter table score add constraint FK_score_itemid foreign key (itemid) references item(itemid) --外键约束
alter table score add constraint UQ_score_acid_itemid unique (acid, itemid) --唯一约束
alter table score add constraint DF_total default(0) for total --默认
alter table score add constraint DF_avg default(0.00) for avg --默认
alter table score add constraint DF_numexcellent default(0) for numexcellent --默认
alter table score add constraint DF_numpoorest default(0) for numpoorest --默认
/*
1添加反馈活动
a) 使用存储过程实现如下功能，根据实际传递的数据增加一项反馈活动，同时往activeandclass表中添加多条数据。
要求：注意事务的处理；检查存储过程是否已存在，如果存在则先删除
b) 测试存储过程，添加如下数据（其中参与班级应根据表中实际数据需添加对应的班级ID）：
09级实训班讲师反馈第一次 参与班级0901班/0902班/0903班/0904班
09级实训班讲师反馈第2次 参与班级0901班/0902班/01实训班/02实训班
*/
/*
select * from activeinfo
select * from classinfo
select * from activeandclass
*/
--a) 使用存储过程实现如下功能，根据实际传递的数据增加一项反馈活动，同时往activeandclass表中添加多条数据。
--要求：注意事务的处理；检查存储过程是否已存在，如果存在则先删除
-- 创建截取字符串存储过程
use feedback
if exists(select name from sysobjects where name = 'proc_splitStr')
drop procedure proc_splitStr
go
create procedure proc_splitStr
-- 存储过程参数，不写output默认为输入
@bigStr varchar(100) output,
@headStr varchar(20) output
as
-- 声明变量
declare @position int
-- 给变量赋值
set @position = charindex('/', @bigStr)
if @position = 0 -- 判断传入的字符串是否含有'/'
begin
set @headStr = @bigStr
set @bigStr = null
end
else
begin
-- substring（字符串， 字符串中的起点， 字符数）
-- ( @headStr 参与班级0901班)/0902班/0903班/0904班
set @headStr = substring(@bigStr, 0, @position)
-- @bigStr 0902班/0903班/0904班
set @bigStr = substring(@bigStr, @position + 1, len(@bigStr)-@position)
end
go
/*
-- 测试
declare @bigStr varchar(100)
declare @headStr varchar(20)
set @bigStr = '0901班/0902班/0903班/0904班'
set @headStr = null
exec proc_splitStr @bigStr output, @headStr output
print @bigStr
print @headStr
go
*/
-- 创建插入活动数据的存储过程
use feedback
if exists(select * from sysobjects where name ='proc_addactive')
drop proc proc_addactive
go
create proc proc_addactive
@activename varchar(100),
@usertypeid int,
@userid int,
@courseid int,
@templateid int,
@classname varchar(100),
@amount int
as
--声明变量
declare @identityNum int
declare @classunit varchar(50)
declare @classid int
declare @sumerror int
set @sumerror=0
--开启事务
begin transaction
insert into activeinfo(activename,usertypeid,userid,courseid,templateid)
values(@activename,@usertypeid,@userid,@courseid,@templateid)
set @sumerror = @sumerror + @@error
set @identityNum=@@identity
--截取班级的字符串
while @classname is not null
begin
exec proc_splitStr @classname output,@classunit output
select @classid=classid from classinfo where classname=@classunit
--添加班级与活动的对象关系
insert into activeandclass(activeid,classid,useramount) values(@identityNum,@classid,@amount)
set @sumerror = @sumerror + @@error
end
--判断语句执行的状态
if @sumerror=0
begin
--没错误
commit transaction
print '储存操作成功'
end
else
begin
--中间存在问题
rollback transaction
print '储存操作失败'
end
go
--b) 测试存储过程，添加如下数据（其中参与班级应根据表中实际数据需添加对应的班级ID）：
--09级实训班讲师反馈第一次 参与班级0901班/0902班/0903班/0904班
--09级实训班讲师反馈第2次 参与班级0901班/0902班/01实训班/02实训班
-- 添加班级
insert into classtype values('普通班')
insert into classtype values('冲刺班')
insert into classtype values('实训班')
-- 添加班级信息
insert into classinfo (classname,ctypeid) values ('0901班',1)
insert into classinfo (classname,ctypeid) values ('0902班',1)
insert into classinfo (classname,ctypeid) values ('0903班',1)
insert into classinfo (classname,ctypeid) values ('0904班',1)
insert into classinfo (classname,ctypeid) values ('01实训班',3)
insert into classinfo (classname,ctypeid) values ('02实训班',3)
-- 用户类型
INSERT INTO usertype (utypename) VALUES ('教员')
INSERT INTO usertype (utypename) VALUES ('班主任')
INSERT INTO usertype (utypename) VALUES ('机房维护员')
INSERT INTO usertype (utypename) VALUES ('教务人员')
-- 用户
insert into userinfo (username, usertypeid) values ('教员1', 1)
insert into userinfo (username, usertypeid) values ('教员2', 1)
insert into userinfo (username, usertypeid) values ('教员3', 1)
-- 课程
insert into courseinfo values ('语文')
insert into courseinfo values ('数学')
insert into courseinfo values ('英语')
-- 模板
insert into template (templatename, usertypeid, usecount) values ('理论课评定', 1, 20)
insert into template (templatename, usertypeid, usecount) values ('课外活动评定', 1, 20)
select * from classtype
select * from classinfo
select * from usertype
select * from userinfo
select * from courseinfo
select * from template
-- 测试存储过程
--09级实训班讲师反馈第一次 参与班级0901班/0902班/0903班/0904班
--09级实训班讲师反馈第2次 参与班级0901班/0902班/01实训班/02实训班
exec proc_addactive '09级实训班讲师反馈第一次',1,1,1,1,'0901班/0902班/0903班',50
exec proc_addactive '09级实训班讲师反馈第2次',1,1,1,1,'0901班/0902班/01实训班/02实训班',50
select * from activeinfo
select * from activeandclass
select * from classinfo
--------------------------
/*
2批量发布反馈活动
a) 使用存储过程实现如下功能，批量发布实际选中的多个反馈活动。
要求：注意事务的处理；检查存储过程是否已存在，如果存在则先删除
*/
-- 定义批量发布反馈活动存储过程
use feedback
if exists(select * from sysobjects where name ='proc_deployactive')
drop proc proc_deployactive
go
create proc proc_deployactive
@activeids varchar(100) -- 反馈活动id（id1/id2/id3）
as
--声明用到的变量
declare @activeid varchar(10) -- 待发布反馈活动的id
declare @sumerror int -- 错误号
set @sumerror=0
--开启事务
begin transaction
--截取班级的字符串
while @activeids is not null
begin
exec proc_splitStr @activeids output,@activeid output
--添加班级与活动的对象关系
update activeinfo set status = 2 where activeid = @activeid
set @sumerror = @sumerror + @@error
end
--判断语句执行的状态
if @sumerror=0
begin
--没错误
commit transaction
print '发布操作成功'
end
else
begin
--中间存在问题
rollback transaction
print '发布操作失败'
end
go
-- 测试
select * from activeinfo
exec proc_deployactive '1/2'
update activeinfo set status = 0 where activeid = 1
update activeinfo set status = 0 where activeid = 2
/*
3删除反馈活动结果
a) 使用触发器模拟简易的删除反馈活动结果的功能，要求删除反馈活动结果的同时修改activeandclass表中“参与人数”字段useramount -1
b) 测试触发器
*/
-- 添加数据
INSERT INTO methodtype (typename,description) VALUES ('answer','按回答评定')
INSERT INTO methodtype (typename,description) VALUES ('sorce','按分数评定/评价标准：5分[优秀] 4分[良好] 3分[一般] 2分[差] 1分[很差]')
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('对该教员有什么建议？',1,1)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('该教员哪方面对你有帮助？',1,1)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('讲课是否活跃？',2,1)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('内容是否详细？',2,1)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('是否热心帮助同学？',2,1)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('感觉班主任哪些方面需要改进',1,2)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('是否经常开班会？',2,2)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('是否关心班级的相关事情？',2,2)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('对该管理的服务态度有什么建议？',1,3)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('对该管理员的态度打多少分？',2,3)
select * from result
select * from activeandclass
insert into result (ip, acid, itemid, userresult, answer) values ('127.0.0.1', 1, 1, 1, '很好啊')
insert into result (ip, acid, itemid, userresult, answer) values ('127.0.0.1', 1, 2, 90, '不错')
insert into result (ip, acid, itemid, userresult, answer) values ('127.0.0.1', 1, 3, 85, '不错的事情')
--a) 使用触发器模拟简易的删除反馈活动结果的功能，要求删除反馈活动结果的同时
--修改activeandclass表中“参与人数”字段useramount -1
if exists(select * from sysobjects where name ='tgr_result_delete')
drop trigger tgr_result_delete
go
create trigger tgr_result_delete
on result
for delete --删除触发
as
-- 定义变量
declare @acid int
declare @sumerror int
set @sumerror = 0
select @acid=acid from Deleted
update activeandclass set useramount = useramount - 1 where acid = @acid
set @sumerror = @sumerror + @@error
if @sumerror = 0
begin
print 'activeandclass表中“参与人数”字段useramount -1'
end
else
begin
print 'activeandclass表更改失败'
end
go
--b) 测试触发器
select * from activeandclass
insert into result (ip, acid, itemid, userresult, answer) values ('127.0.0.1', 1, 1, 1, '很好啊')
delete result where answer = '很好啊'
/*
4查看所有反馈活动
a) 使用视图和函数实现，查看所有反馈活动的功能。如下图所示：
*/
select * from activeinfo
--select * from usertype
--select * from userinfo
--select * from courseinfo
select * from activeandclass
select * from classinfo
-- 创建根据 activeid 获取所有班级字符串的函数
if exists(select * from sysobjects where name = 'fun_getcnames')
drop function fun_getcnames
go
create function fun_getcnames(@activeid int)
returns varchar (100)
as
begin
-- 变量声明
declare @result_classinfo varchar (200)
declare @classnames varchar (200)
set @classnames = ''
-- 声明一个游标
declare cur_classinfo cursor for
-- 查询语句
select classname from activeandclass,classinfo
where activeandclass.classid = classinfo.classid and activeandclass.activeid = @activeid
-- 打开游标
Open cur_classinfo
-- 循环并提取记录
Fetch Next From cur_classinfo Into @result_classinfo-- 取第一条记录存入@result中
While ( @@Fetch_Status = 0 )
begin
set @classnames = @classnames + ' ' +@result_classinfo -- 处理结果
Fetch Next From cur_classinfo into @result_classinfo -- 下一条
end
-- 关闭游标
close cur_classinfo
-- 释放游标
deallocate cur_classinfo
return @classnames
end
go
-- 测试函数
select activeid from activeinfo
select dbo.fun_getcnames(1) as 'result'
select * from activeinfo
------------------- 创建视图 ---------------------------
if exists(select * from sysobjects where name = 'view_activeinfos')
drop view view_activeinfos
go
create view view_activeinfos
as
select activename as '活动名称', activedate as '活动时间', utypename as '被评价人类型',
username as '被评价人姓名', coursename as '技能课程', dbo.fun_getcnames(activeid) as '参与班级'
from activeinfo
left join usertype
on activeinfo.usertypeid = usertype.usertypeid
left join userinfo
on userinfo.userid = activeinfo.userid
left join courseinfo
on courseinfo.courseid = activeinfo.courseid
go
-- 测试结果
select * from view_activeinfos
/*
5根据条件查询反馈活动
a) 查询某位教员（如，刘小林），在某一时间段内（如，2009年度）被评价的反馈活动。如下图所示：
提示：使用索引
*/
-------------------------------------------------------------------------------
/*
select username as '被评价人姓名' ,
activename as '活动名称',
activedate as '活动时间',
dbo.fun_getcnames(activeinfo.activeid) as '参与班级',
--SUM() as '参与人数',
--AVG() as '总平均分'
userresult as '得分',
useramount as '本班人数'--,
--classname as '班级名称'
from result
left join activeandclass
on result.acid = activeandclass.acid
left join classinfo
on classinfo.classid = activeandclass.classid
left join activeinfo
on activeinfo.activeid = activeandclass.activeid
left join usertype
on activeinfo.usertypeid = usertype.usertypeid
left join userinfo
on userinfo.userid = activeinfo.userid
left join courseinfo
on courseinfo.courseid = activeinfo.courseid
--left join classinfo
-- on classinfo.classid = activeandclass.classid
where username = '教员1'
-- 添加时间限制
and convert(date, activedate) > convert(date, '2013')
and convert(date, activedate) < convert(date, '2014')
*/
-- 定义获取班级人数函数人数
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fun_getclassmount')
DROP function fun_getclassmount
go
create function fun_getclassmount(@activeid int)
returns int
as
begin
-- 定义游标
declare class_usermount cursor read_only for
select activeandclass.useramount
from activeandclass,classinfo
where activeandclass.activeid=@activeid and activeandclass.classid=classinfo.classid
--声明变量接收游标数据
declare @useramount int,@mount int
set @mount = 0
--打开游标
open class_usermount
--获取游标数据
fetch next from class_usermount into @useramount
--迭代遍历
while @@fetch_status = 0
begin
set @mount = @mount + @useramount
--获取游标数据
fetch next from class_usermount into @useramount
end
--关闭游标
close class_usermount
--释放游标
deallocate class_usermount
return @mount
end
GO
-- 定义获取平均分函数
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fun_getrseultavg')
DROP function fun_getrseultavg
go
create function fun_getrseultavg(@activeid int)
returns float
as
begin
--定义游标
declare class_usermount cursor read_only for
select activeandclass.total,activeandclass.useramount
from activeandclass,classinfo
where activeandclass.activeid=@activeid and activeandclass.classid=classinfo.classid
declare @useramount int,@mount int,@number int,@number_all int
set @mount=0
set @number_all=0
-- 打开游标
open class_usermount
-- 获取游标数据
fetch next from class_usermount into @useramount,@number
-- 迭代遍历
while @@fetch_status = 0
begin
--连接字符串
set @mount = @mount + @useramount
set @number_all = @number_all + @number
--获取游标数据
fetch next from class_usermount into @useramount,@number
end
--关闭游标
close class_usermount
--释放游标
deallocate class_usermount
declare @avg float
set @avg = @mount / @number_all
return @avg
end
GO
-----------------------------------------------------------------------
-- 测试函数
use feedback
select dbo.fun_getclassmount(1)
select * from activeinfo
select * from activeandclass
select * from classinfo
select username as '被评价人姓名',activename as '活动名称',activedate as '活动日期',
dbo.fun_getclassmount(activeinfo.activeid) as '参与班级',
dbo.fun_getclassmount(activeinfo.activeid) as '参与人数',
dbo.fun_getrseultavg(activeinfo.activeid) as '总平均分'
from (activeinfo left join userinfo on activeinfo.userid=userinfo.userid)
--left join activeandclass on activeinfo.activeid=activeandclass.activeid
教学反馈系统-阶段项目2
第一部分案例描述
案例目的
学习并巩固 SQL Server 数据库编程技术，包括存储过程、触发器、索引、视图、事务、游标、函数等，提高学生数据库设计和数据库编程的能力。
案例难度
★★★★
案例覆盖技能点
1、 存储过程
2、 触发器
3、 索引
4、 视图
5、 事务
6、 游标
7、 函数
推荐案例完成时间
0.5 天
适用课程和对象
SQL SERVER 数据库设计
第二部分需求和开发环境
使用技术和开发环境
SQL Server 2005
项目背景
中国经济数年来持续高增长带来了专业性职业人才的需求激增，职业教育作用日益显现，优秀企业也孕育而生。他们的作用不仅仅为社会培养了专业人才，在产业经营领域，他们也扮演了重要的角色。改革开放以来，随着中国经济社会的发展，职业教育越来越受到国家的高度重视和社会的广泛关注。随着经济社会的发展，中国的职业教育取得了长足的发展，在职业教育理念的实践群体中，若想更好地成为佼佼者，无疑是在 在规模化发展中保障 教学质量是其中一个比较重要的方面。
案例需求
教学质量是学校生存与发展的生命线，不断提高课堂教学水平是学校和每一位教师的共同心愿。及时了解课堂教学的主体—学生对教学情况的评价及建议，有利于教师发现自己教学中的优点以及不足，从而进一步改进教学方法，提高教学水平。为了更好的提高教学水平，建立学校与学员的更好勾通，院领导研究决定研发本系统，并提供考核内容管理、反馈项目管理、反馈表管理、数据统计分析等主要功能，本阶段案例主要以反馈活动管理为主要分析目标， 详细功能描述如下：
1、 反馈活动管理
对学院内部反馈活动进行管理和维护，包括对反馈活动的添加、修改、删除、查看、批量删除、发布、批量发布和关闭某个班的反馈活动等。反馈 活动 的详细信息包括：反馈活动编号、反馈活动名称、活动日期、被评价人类型、被评价人姓名、技能课程、本次反馈采用模板、参与班级。
反馈活动列表
添加反馈活动
查看反馈活动
系统基本模块包括：
|
功能点 |
难度 |
|
|
添加反馈活动 |
★★★★ |
|
|
批量发布反馈活动 |
★★★ |
|
|
删除反馈活动结果 |
★★★★ |
|
|
查看所有反馈活动 |
★★★★ |
|
|
根据条件查询反馈活动 |
★★ |
|
功能点介绍
数据库表结构关系图
表 1 人员类型表
|
表名 |
usertype (人员类型表) |
|||
|
列名 |
描述 |
数据类型 |
空/非空 |
约束条件 |
|
usertypeid |
类型编号 |
int |
非空 |
主键，标识列 |
|
utypename |
类型名称 |
Varchar(20) |
非空 |
唯一 |
表 2 考核方式类型表
|
表名 |
methodtype (考核方式表) |
|||
|
列名 |
描述 |
数据类型 |
空/非空 |
约束条件 |
|
methodtypeid |
考核方式编号 |
int |
非空 |
主键，标识列 |
|
typename |
考核方式名称 |
Varchar(20) |
非空 |
唯一 |
|
description |
描述 |
Varchar(100) |
|
|
表 3 考核项表
|
表名 |
item (考核项表) |
|||
|
列名 |
描述 |
数据类型 |
空/非空 |
约束条件 |
|
itemid |
考核项 编号 |
int |
非空 |
主键，标识列 |
|
itemname |
考核项 名称 |
Varchar(60) |
非空 |
唯一 |
|
methodtypeid |
考核方式编号 |
int |
非空 |
外键 |
|
usertypeid |
适用人员类型编号 |
int |
非空 |
外键 |
表 4 反馈模板表
|
表名 |
template (人员类型表) |
|||
|
列名 |
描述 |
数据类型 |
空/非空 |
约束条件 |
|
templateid |
模板 编号 |
int |
非空 |
主键，标识列 |
|
templatename |
模板 名称 |
Varchar(30) |
非空 |
唯一 |
|
status |
状态 |
int |
非空 |
0-正常（默认值） 1-删除 |
|
usertypeid |
适用人员类型编号 |
int |
非空 |
外键 |
|
usecount |
使用次数 |
int |
非空 |
外键 |
表 5 反馈模板与考核项关联表
|
表名 |
templateanditem (人员类型表) |
|||
|
列名 |
描述 |
数据类型 |
空/非空 |
约束条件 |
|
id |
编号 |
int |
非空 |
主键，标识列 |
|
templateid |
模板编号 |
int |
非空 |
外键 与 考核项 编号一起，唯一 |
|
itemid |
考核项 编号 |
int |
非空 |
外键 与模板编号一起，唯一 |
表 6 班级类型表
|
表名 |
classtype (班级类型表) |
|||
|
列名 |
描述 |
数据类型 |
空/非空 |
约束条件 |
|
ctypeid |
编号 |
int |
非空 |
主键，标识列 |
|
ctypename |
类型名称 |
Varchar(20) |
非空 |
唯一 |
表 7 班级信息表
|
表名 |
classinfo (班级信息表) |
|||
|
列名 |
描述 |
数据类型 |
空/非空 |
约束条件 |
|
classid |
编号 |
int |
非空 |
主键，标识列 |
|
classname |
班级名称 |
Varchar(30) |
非空 |
唯一 |
|
startdate |
开班时间 |
datetime |
非空 |
默认系统日期 |
|
status |
状态 |
int |
非空 |
0-正常(默认) 1-删除 2-结业 |
|
ctypeid |
班级类型编号 |
int |
非空 |
外键 |
表 8 用户信息表
|
表名 |
userinfo (用户信息表) |
|||
|
列名 |
描述 |
数据类型 |
空/非空 |
约束条件 |
|
userid |
编号 |
int |
非空 |
主键，标识列 |
|
username |
用户名称 |
Varchar(20) |
非空 |
唯一 |
|
usertypeid |
用户 类型编号 |
int |
非空 |
外键 |
表 9 课程信息表
|
表名 |
courseinfo (课程信息表) |
|||
|
列名 |
描述 |
数据类型 |
空/非空 |
约束条件 |
|
courseid |
编号 |
int |
非空 |
主键，标识列 |
|
coursename |
课程名称 |
Varchar(30) |
非空 |
唯一 |
表 10 反馈活动信息表
|
表名 |
activeinfo (反馈活动信息表) |
|||
|
列名 |
描述 |
数据类型 |
空/非空 |
约束条件 |
|
activeid |
编号 |
int |
非空 |
主键，标识列 |
|
activename |
活动名称 |
Varchar(50) |
非空 |
唯一 |
|
activedate |
活动时间 |
datetime |
非空 |
默认系统日期 |
|
usertypeid |
被评价人 类型编号 |
int |
非空 |
|
|
userid |
被评价人 编号 |
int |
非空 |
外键 |
|
courseid |
技能课程 编号 |
int |
|
外键 |
|
templateid |
反馈模板 编号 |
int |
非空 |
外键 |
|
status |
状态 |
int |
非空 |
0-正常（未发布，默认）；1-删除；2-已发布 |
表 11 反馈活动与班级关联表
|
表名 |
activeandclass (反馈活动与班级关联表) |
|||
|
列名 |
描述 |
数据类型 |
空/非空 |
约束条件 |
|
acid |
编号 |
int |
非空 |
主键，标识列 |
|
activeid |
活动 编号 |
int |
非空 |
外键 与 班级 编号一起，唯一 |
|
classid |
班级 编号 |
int |
非空 |
外键 与 活动 编号一起，唯一 |
|
useramount |
参与人数 |
int |
非空 |
默认0 |
|
status |
状态 |
int |
非空 |
0-正常（活动未开始，默认）；1-删除；2-进行中；3-关闭 |
|
total |
总成绩 |
int |
非空 |
默认0 |
|
avg |
平均成绩 |
decimal(18, 2) |
非空 |
默认0.00 |
表 12 反馈结果表
|
表名 |
result (反馈结果表) |
|||
|
列名 |
描述 |
数据类型 |
空/非空 |
约束条件 |
|
resultid |
编号 |
int |
非空 |
主键，标识列 |
|
ip |
IP地址 |
Varchar(15) |
非空 |
|
|
acid |
反馈活动与班级关联ID 号 |
int |
非空 |
外键 |
|
itemid |
考核项 编号 |
int |
非空 |
外键 |
|
userresult |
提交的成绩 |
int |
非空 |
默认0 |
|
answer |
按回答评定时的用户答案 |
Varchar(500) |
|
默认null |
表 13 成绩表
|
表名 |
score (成绩表) |
|||
|
列名 |
描述 |
数据类型 |
空/非空 |
约束条件 |
|
scoreid |
编号 |
int |
非空 |
主键，标识列 |
|
acid |
反馈活动与班级关联ID 号 |
int |
非空 |
外键 与 考核项 编号一起，唯一 |
|
itemid |
考核项 编号 |
int |
非空 |
外键 与 反馈活动与班级关联ID号 一起，唯一 |
|
total |
总成绩 |
int |
非空 |
默认0 |
|
avg |
平均成绩 |
decimal(18, 2) |
非空 |
默认0.00 |
|
numexcellent |
选”优”的人数 |
int |
非空 |
默认0 |
|
numpoorest |
选”很差”的人数 |
int |
非空 |
默认0 |
1 添加反馈活动
a) 使用存储过程实现如下功能，根据实际传递的数据增加一项反馈活动，同时往 activeandclass 表中添加多条数据。
要求：注意事务的处理；检查存储过程是否已存在，如果存在则先删除
b) 测试存储过程，添加如下数据（其中参与班级应根据表中实际数据需添加对应的班级 ID ）：
09 级实训班讲师反馈第一次 参与班级 0901 班 /0902 班 /0903 班 /0904 班
09 级实训班讲师反馈第 2 次 参与班级 0901 班 /0902 班 /01 实训班 /02 实训班
2 批量发布反馈活动
a) 使用存储过程实现如下功能，批量发布实际选中的多个反馈活动。
要求：注意事务的处理；检查存储过程是否已存在，如果存在则先删除
3 删除反馈活动结果
a) 使用触发器模拟简易的删除反馈活动结果的功能，要求删除反馈活动结果的同时修改 activeandclass 表中“参与人数”字段 useramount -1
b) 测试触发器
4 查看所有反馈活动
a) 使用视图和函数实现，查看所有反馈活动的功能。如下图所示：
5 根据条件查询反馈活动
a) 查询某位教员（如，刘小林），在某一时间段内（如， 2009 年度）被评价的反馈活动。如下图所示：
提示：使用索引
第三部分考核评价点
|
序号 |
功能列表 |
功能描述 |
分数 |
说明 |
|
1 |
批量删除反馈活动 |
|
|
|
|
2 |
|
|
|
|
|
3 |
删除反馈活动结果 |
|
|
|
|
4 |
查看所有反馈活动 |
|
|
|
|
5 |
根据条件查询反馈活动 |
|
|
|
|
6 |
数据库命名规范 |
|
|
|
