Teaching feedback System-stage Project 1

if exists (select * from sysobjects where name= ' usertype ') drop table usertype go if exists (              SELECT * from sysobjects where name= ' methodtype ') drop table Methodtypego if exists (SELECT *   From sysobjects where name= ' item ') drop table item go if EXISTS (SELECT * FROM              sysobjects where name= ' template ') drop table templategoif exists (SELECT * from sysobjects where name= ' Templateanditem ') drop table Templateanditem Go--People type table CREATE table usertype (Usertypei d int primary key NOT NULL, Utypename varchar (a) unique NOT NULL)--Evaluation Mode type table CREATE table Methodtype (Methodtypeid int primary key NOT NULL, TypeName varchar is unique NOT NULL, description varchar (100 )--Evaluation Table CREATE table item (itemid int primary key NOT NULL, ItemName varchar) unique NOT NULL, Usert Ypeid int NOT NULL ForeiGN Key References Usertype (Usertypeid), Methodtypeid int not null foreign key references Methodtype (Methodtypeid)) Crea  Te table template (templateid int primary key not null,templatename varchar (a) unique not null,status int NOT null default 0 Check (status=0 or Status=1), Usertypeid int not null foreign key references usertype (Usertypeid))--Feedback Template and Evaluation association table create TA  BLE 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 Usertypedrop table Templatedrop Table Item  drop table Methodtypedrop table templateanditem*/if exists (select *from sysdatabases where name= ' feedback ') drop database FEEDBACKGO 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 Feedbackadd file (Name= ' feedbackf ', filename= ' D:\project\feedbackf.ndf ')--2. The type of personnel table to be deleted and changed--ainsert into usertype (usertypeid,utypename) VALUES (1, ' Faculty '); INSERT into usertype ( Usertypeid,utypename) VALUES (2, ' head teacher '), insert into usertype (Usertypeid,utypename) VALUES (3, ' engine room maintainer '); INSERT into Usertype (Usertypeid,utypename) VALUES (4, ' academic staff ')--b. Delete Academic staff delete from usertype where utypename= ' academic staff ' update usertype Set utypename= ' room manager ' where utypename= ' machine room maintainer '--3 assessment method Type table is added--A Insert 2 test statements into the Evaluation method table insert into Methodtype values (1, ' Answer ', ' by response ') insert into methodtype values (2, ' score ', ' by score/evaluation criteria: 5 points [Excellent] 4 points [good] 3 [general] 2 points [difference] 1 points [very poor])--4 evaluation of the increase--a Use T-SQL to insert 10 test data into the evaluation table (the data is simulated by the learner according to the reality, which applies to 5 of the teachers, of which 2 are evaluated according to the answer; 3 for the head teacher; 2 for room manager) insert into item values (1, ' Whether the lecture is clear ') , (+) insert into item values (2, ' Whether it drives classroom atmosphere ', ") insert into item values (3, ' Whether it helps you learn ',") insert into item values (4, ' whether the lecture INSERT into item values (5, ' patience to teach each question ', ') insert into item values (6, ' feel what the class teacher has improved ', 2,1) insert into item values ( 7, ' Whether the class will ', 2,2) insert into item values (8, ' Whether you care about classmates ', 2,2) insert into ITEM values (9, ' admin attitude score ', 3,2) insert into item values (10, ' admin on time ' score ', 3,2)--5 Review template additions/modifications/deletions--a) use T-SQL to insert 3 in the Evaluation Template table for instructors, 1 applies to the class teacher, 1 applies to the computer room Administrator's test data (data by the student according to the reality own simulation) insert into template values (1, ' Theory class evaluation ', 0,1) insert into template values (2, ' graduation design Class evaluation ', 0,1) insert into template values (3, ' class status assessment ', 0,1) insert into template values (4, ' Survey of class teacher placement work ', 0,2) insert INTO t Emplate VALUES (5, ' investigation of laboratory management work ', 0,3)--b) use T-SQL to modify any 1 test data that applies to instructors in the assessment Template table, requiring that the template name be modified and the evaluation item contained in the adjustment template Update template set Templatename= ' theory course assessment ' where templatename= ' theory class evaluation '--c ' use T-SQL to delete any 1 test data that applies to instructors in the Evaluation Template table--If the foreign key is used in two tables, the--delete from Template where usertypeid= (select Usertypeid from usertype where utypename = ' instructor ') SELECT Usertypeid from usertype where UT Ypename = ' teacher ' update template set Status=1where Templatename= ' theory class Assessment '--6 Assessment template Comprehensive query--a ' use T-SQL query to apply to teacher and class Teacher's assessment template, The required display format is as follows: Select templatename ' template name ', utypename ' user type ', (case status if 0 then ' normal ' when 1 then ' deleted ' End) ' status ' from Templat E,usertype where Template.usertypeid = Usertype.usertypeid anD usertype. utypename= ' class teacher ' or usertype. Utypename= ' teacher '--b) use T-SQL to query all the normal status of the assessment template and its included evaluation, no evaluation item shows "No evaluation", The required display format is as follows: Select templatename ' template name ', utypename ' use person type ', (case when itemname was null then ' no test item ' when ItemName was not null the n itemnameend) ' assessment ' from template, usertype, itemwhere Item.usertypeid = Usertype.usertypeid and template.usertypeid= Usertype.usertypeid and Status=0

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Teaching feedback System-stage Project 1