教学反馈系统-阶段项目1

if exists (select * 
            from  sysobjects  
           where  name='usertype')
   drop table usertype 
go  
if exists (select * 
            from  sysobjects  
           where  name='methodtype')
   drop table methodtype
go  
if exists (select * 
            from  sysobjects  
           where  name=' item ')
   drop table item 
go  

if exists (select * 
            from  sysobjects  
           where  name=' template')
   drop table  template
go
if exists (select * 
            from  sysobjects  
           where  name='templateanditem')
   drop table templateanditem 
go  


--人员类型表
create table usertype(

       usertypeid int   primary  key not null  ,
                       
       utypename varchar(20) unique not null
)
--考核方式类型表
create table methodtype
(

    methodtypeid int primary key not null,
    typename  varchar(60) unique not null,
    description varchar(100)
)
--考核项表
create  table item (
     itemid int primary key not null,
     itemname varchar(60) unique not null,
     
     usertypeid int not null foreign key references usertype(usertypeid),
     methodtypeid int not null foreign key references methodtype(methodtypeid)
)

create table template(
templateid int primary key not null,
templatename varchar(30) unique not null,
status int not null default 0 check(status=0 or status=1),
usertypeid int not null foreign key references usertype(usertypeid) 

)
--反馈模板与考核项关联表
create table templateanditem (
id int primary key not null,
templateid int unique not null foreign key references item(itemid),
itemid int  unique not null foreign key references template (templateid)
)
/*drop table usertype
drop table template
drop table item
drop table methodtype
drop table templateanditem*/

if exists (select *from sysdatabases where name='feedback')
drop database feedback
go 
create database feedback on primary
(
name='feedback',
filename='C:\project\feedback.mdf',
size=10mb,
maxsize=200mb,
filegrowth=15%
)
log on
(name='feedbackl',
filename='D:\project2\feedbackl.ldf'
)

alter database feedback
add file 
(
name='feedbackf',
filename='D:\project\feedbackf.ndf'
)
--2.人员类型表得增删改
--a
insert into usertype(usertypeid,utypename) values (1,'教员');
insert into usertype(usertypeid,utypename) values (2,'班主任');
insert into usertype(usertypeid,utypename) values (3,'机房维护员');
insert into usertype(usertypeid,utypename) values (4,'教务人员');
--b.删除教务人员
delete from usertype where utypename='教务人员'

update usertype set utypename='机房管理人员'
where utypename='机房维护员'
--3考核方式类型表的增加
--a向考核方式表中插入2条测试语句
insert into  methodtype values(1,'answer','按回答评定')
insert into  methodtype values(2,'score','按按分数评定/评价标准：5分[优秀] 4分[良好] 3分[一般] 2分[差] 1分[很差]）')
--4考核项的增加
--a) 使用T-SQL向考核项表中插入10条测试数据（数据由学员根据现实自己模拟，适用于教员的5条，其中2条按回答评定；适用于班主任的3条；适用于机房管理员的2条）
insert into  item values(1,'是否讲课清晰',1,1)
insert into  item values(2,'是否带动课堂气氛',1,1)
insert into  item values(3,'是否对你学习有帮助',1,2)
insert into  item values(4,'讲课是否仔细',1,2)
insert into  item values(5,'是否耐心教各个问题',1,2)
insert into  item values(6,'感觉班主任哪些方面有改进',2,1)
insert into  item values(7,'是否开班会',2,2)
insert into  item values(8,'是否关心同学',2,2)
insert into  item values(9,'管理员态度的分数',3,2)
insert into  item values(10,'管理员按时到位的分数',3,2)

--5考核模板的增加/修改/删除
--a) 使用T-SQL向考核模板表中插入3条适用于教员、1条适用于班主任、1条适用于机房管理员的测试数据（数据由学员根据现实自己模拟）
INSERT INTO template  VALUES (1,'理论课评定',0,1)  
INSERT INTO template  VALUES (2,'毕业设计课评定',0,1)  
INSERT INTO template  VALUES (3,'上课情况评定',0,1)  
INSERT INTO template  VALUES (4,'班主任代班工作的调查',0,2)  
INSERT INTO template  VALUES (5,'机房管理工作的调查', 0,3)  
--b) 使用T-SQL修改考核模板表中适用于教员的任1条测试数据，要求必须修改模板名称和调整模板中包含的考核项
update template set templatename='理论课评定'where templatename='理论课的评定'
--c）使用T-SQL删除考核模板表中适用于教员的任1条测试数据
--如果是外键,两个表中都用到,则都删除
--delete from template where usertypeid=(SELECT usertypeid FROM usertype WHERE utypename = '教员')
SELECT usertypeid FROM usertype WHERE utypename = '教员'
update template set status=1
where templatename='理论课评定'

--6考核模板的综合查询
--a) 使用T-SQL查询适用于教员和班主任的考核模板，要求显示格式如下图所示：
select templatename'模板名称',utypename'使用人员类型',(
case status 
when 0 then '正常'
when 1 then '已删除'
end 
)'状态'
from template,usertype 
where template.usertypeid = usertype.usertypeid and usertype .utypename='班主任'or usertype .utypename='教员'
--b) 使用T-SQL查询所有正常状态的考核模板及其包含的考核项，没有考核项则显示”无考核项”，要求显示格式如下图所示：
select templatename'模板名称',utypename'使用人员类型',(
case 
when itemname is null then '无考核项'
when itemname is not null then itemname
end)'考核项'
from  template ,usertype ,item
where item.usertypeid = usertype.usertypeid 
and 
template.usertypeid=usertype.usertypeid 
and status=0