/*
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
項目背景
中國經濟數年來持續高增長帶來了專業性職業人才的需求激增，職業教育作用日益顯現，優秀企業也孕育而生。他們的作用不僅僅為社會培養了專業人才，在產業經營領域，他們也扮演了重要的角色。改革開放以來，隨著中國經濟社會的發展，職業教育越來越受到國家的高度重視和社會的廣泛關注。隨著經濟社會的發展，中國的職業教育取得了長足的發展，在職業教育理念的實踐群體中，若想更好地成為佼佼者，無疑是在 在規?；l展中保障 教學質量是其中一個比較重要的方面。
案例需求
教學質量是學校生存與發展的生命線，不斷提高課堂教學水平是學校和每一位教師的共同心愿。及時了解課堂教學的主體—學生對教學情況的評價及建議，有利于教師發現自己教學中的優點以及不足，從而進一步改進教學方法，提高教學水平。為了更好的提高教學水平，建立學校與學員的更好勾通，院領導研究決定研發本系統，并提供考核內容管理、反饋項目管理、反饋表管理、數據統計分析等主要功能，本階段案例主要以反饋活動管理為主要分析目標， 詳細功能描述如下：
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 |
數據庫命名規范 |
? |
? |
? |
?
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
