    --修改列

--把 "Address" 欄位改名為 "Addr"。這可以用以下的指令達成： 

--ALTER table customer change Address Addr char(50)



------------------------------------------------------------------------

--1 通過給定的數據庫表結構關系圖和表格中定義的規范，創建數據庫數據表

--a) 創建數據庫

--	使用T-SQL創建數據庫feedback，要求：

	--①一個主要文件（存放在第一個硬盤分區C:\project文件夾下），初始大小為10M,最大為200M,文件自動增長率為15% 

	--②一個次要數據文件(分別存放在第二個硬盤分區D上) 

	--③一個日志文件(存放在第三個硬盤分區E：上)  

	--④檢查數據庫是否已存在，如果存在則先刪除

--提示：使用xp_cmdshell調用DOS命令創建文件夾；

--使用exists判斷數據庫是否存在



------------------------------------------------------------------------

--	創建文件夾

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE

GO

-- 調用DOS命令創建project文件夾

EXEC xp_cmdshell 'mkdir c:\project'

GO  



-- 判斷是否存在該數據庫，若存在則刪除

USE master

GO

IF EXISTS (SELECT * FROM sysdatabases WHERE NAME='feedback')

	DROP DATABASE feedback;

GO



--根據要求創建數據庫

USE master

GO

CREATE DATABASE feedback

ON PRIMARY 

( 

	NAME = feedback_data,

	FILENAME = 'C:\project\feedback.mdf',

	SIZE = 10MB,

	MAXSIZE = 200MB,

	FILEGROWTH = 15%

),

(	 

	NAME = feedback_data1,

	FILENAME = 'd:\feedback.ndf'

)

LOG ON 

( 

	NAME = feedback_log,

	FILENAME = 'e:\feedback.ldf'

)

GO



------------------創建表----------------------

--1創建 人員類型表

USE feedback

IF EXISTS(SELECT * FROM sysobjects WHERE NAME='usertype')

	DROP TABLE usertype

GO

CREATE TABLE usertype 

(

	usertypeid INT IDENTITY(1,1) PRIMARY KEY,

	utypename VARCHAR(20) NOT NULL UNIQUE

--CONSTRAINT FK_feedback FOREIGN KEY (username)

)



--2創建 考核方式類型表

IF EXISTS(SELECT * FROM sysobjects WHERE NAME='methodtype')

	DROP TABLE methodtype

GO

CREATE TABLE methodtype 

(

	methodtypeid INT IDENTITY(1,1) PRIMARY KEY,

	typename VARCHAR(20) NOT NULL UNIQUE,

	description VARCHAR(100) 

)



--3創建 考核項表

IF EXISTS(SELECT * FROM sysobjects WHERE NAME='item')

	DROP TABLE item

GO

CREATE TABLE item 

(

	itemid INT IDENTITY(1,1) PRIMARY KEY,

	itemname VARCHAR(60) NOT NULL UNIQUE,

	methodtypeid INT NOT NULL REFERENCES methodtype(methodtypeid),

	usertypeid INT NOT NULL REFERENCES usertype(usertypeid)

)



--4建立 反饋模板表

IF EXISTS(SELECT * FROM sysobjects WHERE NAME='template')

	DROP TABLE template

GO

CREATE TABLE template 

(

	templateid INT IDENTITY(1,1) PRIMARY KEY,

	templatename VARCHAR(30) NOT NULL UNIQUE,

	status INT NOT NULL DEFAULT 0,

	usertypeid INT NOT NULL REFERENCES usertype(usertypeid)

)

--修改表的列

/*

SELECT * FROM template

ALTER TABLE template DROP CONSTRAINT CK_status

ALTER TABLE template DROP COLUMN status

ALTER TABLE template ADD  status INT NOT NULL DEFAULT 0

*/

--添加約束 CHECK 0正常（默認） 1刪除

ALTER TABLE template

	ADD CONSTRAINT CK_status CHECK(status in (0,1))



--5建立 反饋模板與考核項關聯表

IF EXISTS(SELECT * FROM sysobjects WHERE NAME='templateanditem')

	DROP TABLE templateanditem

GO

CREATE TABLE templateanditem 

(

	id INT IDENTITY(1,1) PRIMARY KEY,

	templateid INT NOT NULL REFERENCES template(templateid),	--與考核項編號一起唯一

	itemid INT NOT NULL REFERENCES item(itemid)	--與考核項編號一起唯一

)

ALTER TABLE templateanditem

	ADD CONSTRAINT UK_templateid_itemid UNIQUE(templateid,itemid)



/*

2人員類型表的增/刪/改

a) 使用T-SQL向人員類型表中插入4條測試數據（①教員、②班主任、③機房維護員、④教務人員）

b) 使用T-SQL刪除人員類型表中的“教務人員”

c) 使用T-SQL修改人員類型表中的數據，將“機房維護員”改為“機房管理員”

*/



--a) 使用T-SQL向人員類型表中插入4條測試數據（①教員、②班主任、③機房維護員、④教務人員）

INSERT INTO usertype (utypename) VALUES ('教員')

INSERT INTO usertype (utypename) VALUES ('班主任')

INSERT INTO usertype (utypename) VALUES ('機房維護員')

INSERT INTO usertype (utypename) VALUES ('教務人員')

GO

--查詢 usertype 表內數據

SELECT * FROM usertype



--b) 使用T-SQL刪除人員類型表中的“教務人員”

DELETE usertype WHERE utypename='教務人員'



--c) 使用T-SQL修改人員類型表中的數據，將“機房維護員”改為“機房管理員”

UPDATE usertype SET utypename='機房管理員' WHERE utypename='機房維護員' 

GO

/*

3考核方式類型表的增加

a) 使用T-SQL向考核方式類型表中插入2條測試數據

（①按回答評定、②按分數評定/評價標準：5分[優秀] 4分[良好] 3分[一般] 2分[差] 1分[很差]）

*/

--查詢數據

SELECT * FROM methodtype



INSERT INTO methodtype (typename,description) VALUES ('answer','按回答評定')

INSERT INTO methodtype (typename,description) VALUES ('sorce','按分數評定/評價標準：5分[優秀] 4分[良好] 3分[一般] 2分[差] 1分[很差]')

GO

/*

4考核項的增加

a) 使用T-SQL向考核項表中插入10條測試數據

（數據由學員根據現實自己模擬，適用于教員的5條，其中2條按回答評定；

適用于班主任的3條；適用于機房管理員的2條）

*/

SELECT * FROM item



--查出考核方式的編號

SELECT methodtypeid FROM methodtype WHERE typename='answer' --> 1

SELECT methodtypeid FROM methodtype WHERE typename='sorce' --> 2



--(1).適用于教員的5條，其中2條按回答評定



--查出教員的類型編號即usertypeid

SELECT usertypeid FROM usertype WHERE utypename='教員' --> 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)



--刪除某項

/*

DELETE item WHERE itemname='上課是否準時？'

DELETE item WHERE itemname='課前是否備課?'

DELETE item WHERE itemname='講課是否活躍？'

DELETE item WHERE itemname='內容是否詳細？'

DELETE item WHERE itemname='是否熱心幫助同學？'

*/

GO

--(2).適用于班主任的3條

SELECT * FROM usertype



SELECT usertypeid FROM usertype WHERE utypename='班主任' --> 2



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)

GO

--(3).適用于機房管理員的2條

SELECT usertypeid FROM usertype WHERE utypename='機房管理員' --> 3



INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('對該管理的服務態度有什么建議？',1,3)



INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('對該管理員的態度打多少分？',2,3)

GO

/*

5考核模板的增加/修改/刪除

a) 使用T-SQL向考核模板表中插入3條適用于教員、

1條適用于班主任、1條適用于機房管理員的測試數據（數據由學員根據現實自己模擬）

b) 使用T-SQL修改考核模板表中適用于教員的任1條測試數據，

要求必須修改模板名稱和調整模板中包含的考核項

c) 使用T-SQL刪除考核模板表中適用于教員的任1條測試數據

*/



--a) 使用T-SQL向考核模板表中插入3條適用于教員、

--1條適用于班主任、1條適用于機房管理員的測試數據



SELECT * FROM template

SELECT * FROM item



SELECT usertypeid FROM usertype WHERE utypename='教員' --> 1

SELECT usertypeid FROM usertype WHERE utypename='班主任' --> 2

SELECT usertypeid FROM usertype WHERE utypename='機房管理員' --> 3



INSERT INTO template (templatename, usertypeid) VALUES ('理論課評定', 1)

INSERT INTO template (templatename, usertypeid) VALUES ('畢業設計課評定', 1)

INSERT INTO template (templatename, usertypeid) VALUES ('上課情況評定', 1)

INSERT INTO template (templatename, usertypeid) VALUES ('班主任代班工作的調查', 2)

INSERT INTO template (templatename, usertypeid) VALUES ('機房管理工作的調查', 3)

GO



--關聯數據

SELECT templateid FROM template WHERE templatename='理論課評定' --> 1

SELECT templateid FROM template WHERE templatename='畢業設計課評定' --> 2

SELECT templateid FROM template WHERE templatename='上課情況評定' --> 3

SELECT templateid FROM template WHERE templatename='班主任代班工作的調查' --> 4

SELECT templateid FROM template WHERE templatename='機房管理工作的調查' --> 5



INSERT INTO templateanditem (templateid, itemid) VALUES (1, 1)

INSERT INTO templateanditem (templateid, itemid) VALUES (1, 2)

INSERT INTO templateanditem (templateid, itemid) VALUES (1, 3)

INSERT INTO templateanditem (templateid, itemid) VALUES (1, 4)

INSERT INTO templateanditem (templateid, itemid) VALUES (1, 5)

INSERT INTO templateanditem (templateid, itemid) VALUES (2, 2)

INSERT INTO templateanditem (templateid, itemid) VALUES (3, 3)

INSERT INTO templateanditem (templateid, itemid) VALUES (4, 6)

INSERT INTO templateanditem (templateid, itemid) VALUES (4, 7)

INSERT INTO templateanditem (templateid, itemid) VALUES (4, 8)

INSERT INTO templateanditem (templateid, itemid) VALUES (5, 9)

INSERT INTO templateanditem (templateid, itemid) VALUES (5, 10)

GO

DELETE templateanditem WHERE id = 11 or id = 12



GO

--b) 使用T-SQL修改考核模板表中適用于教員的任1條測試數據，

--要求必須修改模板名稱

UPDATE template SET templatename = '理論課的評定' WHERE templatename = '理論課評定'

GO



SELECT * FROM template



--c) 使用T-SQL刪除考核模板表中適用于教員的任1條測試數據



--先刪除關聯數據

--SELECT templateid FROM template WHERE usertypeid = (SELECT usertypeid FROM usertype WHERE utypename = '教員')

SELECT * FROM template WHERE usertypeid = (SELECT usertypeid FROM usertype WHERE utypename = '教員')

--DELETE templateanditem WHERE templateid = (...)



UPDATE template SET status = 1 WHERE templateid = 1



/*

6考核模板的綜合查詢	

a) 使用T-SQL查詢適用于教員和班主任的考核模板，要求顯示格式如下圖所示：

提示：使用子查詢、IN…、CASE…WHEN…THEN…

b) 使用T-SQL查詢所有正常狀態的考核模板及其包含的考核項，沒有考核項則顯示”無考核項”，要求顯示格式如下圖所示：

提示：使用子查詢、CASE…WHEN…THEN…

*/



--a) 使用T-SQL查詢適用于教員和班主任的考核模板，要求顯示格式如下圖所示：

--提示：使用子查詢、IN…、CASE…WHEN…THEN…



SELECT * FROM template

SELECT * FROM usertype



SELECT templatename AS '模板名稱', utypename AS '適用人員類型',

(CASE status

	WHEN 0 THEN '正常'

	WHEN 1 THEN '已刪除'

END) AS '狀態'

FROM template, usertype WHERE template.usertypeid = usertype.usertypeid



--b) 使用T-SQL查詢所有正常狀態的考核模板及其包含的考核項，沒有考核項則顯示”無考核項”，要求顯示格式如下圖所示：

--提示：使用子查詢、CASE…WHEN…THEN…



SELECT * FROM template

SELECT * FROM usertype

SELECT * FROM item

SELECT * FROM templateanditem





SELECT templatename AS '模板名稱',uypename AS '適用人員類型',(

	CASE 

		WHEN itemname is null THEN '無考核項'

		when itemname is not null THEN itemname

	END

	) AS '考核項'

FROM template left join templateanditem

	ON template.templateid=templateanditem.templateid

left join item

	ON templateanditem.itemid=item.itemid

left join usertype

	ON template.usertypeid=usertype.usertypeid

WHERE status = 0

教學反饋系統-階段項目1

第一部分案例描述

?????? 案例目的

???????? 學習T-SQL基本概念、實體模型、ER圖技術，建庫、建表、基本增刪改查，復雜查詢（條件、關聯、復合），培養學生關于數據庫的設計能力與T-SQL編碼能力。

?????? 案例難度

???????? ★★★

?????? 案例覆蓋技能點

1、??T-SQL基本概念

2、??ER圖技術

1)???????能夠看懂數據庫ER圖、表結構關系圖

3、??建庫、建表、基本增刪改查等T-SQL技術

1)???????根據E-R圖使用T-SQL語句建庫、建表

2)???????對數據庫進行增刪改查

4、??高級查詢技術

1)???????會使用 IN,NOTIN,EXISTS,NOT EXISTS關鍵字

2)???????會使用CASE…WHEN…THEN…

3)???????會使用關聯查詢

4)???????會使用子查詢

?????? 推薦案例完成時間

???????? ?0.5天

適用課程和對象

???????? SQLSERVER數據庫基本技術

????????

第二部分需求和開發環境

?????? 使用技術和開發環境

???????? SQLServer 2005

項目背景

中國經濟數年來持續高增長帶來了專業性職業人才的需求激增，職業教育作用日益顯現，優秀企業也孕育而生。他們的作用不僅僅為社會培養了專業人才，在產業經營領域，他們也扮演了重要的角色。改革開放以來，隨著中國經濟社會的發展，職業教育越來越受到國家的高度重視和社會的廣泛關注。隨著經濟社會的發展，中國的職業教育取得了長足的發展，在職業教育理念的實踐群體中，若想更好地成為佼佼者，無疑是在在規模化發展中保障教學質量是其中一個比較重要的方面。

?????? 案例需求

教學質量是學校生存與發展的生命線，不斷提高課堂教學水平是學校和每一位教師的共同心愿。及時了解課堂教學的主體—學生對教學情況的評價及建議，有利于教師發現自己教學中的優點以及不足，從而進一步改進教學方法，提高教學水平。為了更好的提高教學水平，建立學校與學員的更好勾通，院領導研究決定研發本系統，并提供考核內容管理、反饋項目管理、反饋表管理、數據統計分析等主要功能，本階段案例主要以考核內容管理為主要分析目標，詳細功能描述如下：

1、? 考核內容管理

a)???? 考核項設置

根據學院內部人員類型（講師 / 班主任 / 機房維護員，等學院內部工作項）分別進行考核項的管理和維護，包括對考核項的添加、修改、刪除、查看、批量刪除。考核項的詳細信息包括：考核內容編號、考核項內容、考核方式（按分數評定 / 按回答評定）、人員類型。

考核內容列表

添加考核內容

b)???? 反饋模板設置

對學院內部反饋模板進行管理和維護，包括對反饋模板的添加、修改、刪除、查看、批量刪除。反饋模板的詳細信息包括：反饋模板編號、反饋模板名稱、被評價人類型、多項考核內容。

反饋模板列表

添加反饋模板

???????? ???????? 系統基本模塊包括：

功能點	難度	?
數據庫與表的建立	★★	?
人員類型表的增/刪/改	★★	?
考核方式類型表的增加	★	?
考核項的增加	★	?
考核模板的增加/修改/刪除	★★★	?
考核模板的綜合查詢	★★★★	?

功能點介紹

數據庫表結構關系圖

表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	非空	外鍵

表5 反饋模板與考核項關聯表

表名		templateanditem ( 反饋模板與考核項關聯表 )
列名	描述	數據類型	空/非空	約束條件
id	編號	int	非空	主鍵，標識列
templateid	模板編號	int	非空	外鍵與考核項編號一起，唯一
itemid	考核項編號	int	非空	外鍵與模板編號一起，唯一

1 通過給定的數據庫表結構關系圖和表格中定義的規范，創建數據庫數據表

a) 創建數據庫

使用T-SQL創建數據庫feedback，要求：①一個主要文件（存放在第一個硬盤分區C:\project文件夾下），初始大小為10M,最大為200M,文件自動增長率為15% ②一個次要數據文件(分別存放在第二個硬盤分區D上) ③一個日志文件(存放在第三個硬盤分區E：上) ?④檢查數據庫是否已存在，如果存在則先刪除

提示：使用 xp_cmdshell 調用 DOS 命令創建文件夾；

使用 exists 判斷數據庫是否存在

b) 創建數據表

使用T-SQL分別創建如下幾個表：①人員類型表 ②考核方式類型表 ③考核項表④反饋模板表 ⑤考核項和反饋模板關聯表。要求：檢查數據表是否已存在，如果存在則先刪除表結構

c) 添加約束

根據ER圖及表結構規范說明，使用T-SQL向各表添加約束，具體要求如下：

ü? 所有名稱不允許為空，并且唯一

ü? 反饋模板狀態， 0-正常（默認值）、1-刪除

ü? 根據ER圖，創建外鍵

提示：使用 Alter Table…… Add Constraint……

2人員類型表的增/刪/改

a) 使用T-SQL向人員類型表中插入4條測試數據（①教員、②班主任、③機房維護員、④教務人員）

b) 使用T-SQL刪除人員類型表中的“教務人員”

c) 使用T-SQL修改人員類型表中的數據，將“機房維護員”改為“機房管理員”

3考核方式類型表的增加

a) 使用T-SQL向考核方式類型表中插入2條測試數據（①按回答評定、②按分數評定/評價標準：5分[優秀] 4分[良好] 3分[一般] 2分[差] 1分[很差]）

4考核項的增加

a) 使用T-SQL向考核項表中插入10條測試數據（數據由學員根據現實自己模擬，適用于教員的5條，其中2條按回答評定；適用于班主任的3條；適用于機房管理員的2條）

5考核模板的增加/修改/刪除

a) 使用T-SQL向考核模板表中插入3條適用于教員、1條適用于班主任、1條適用于機房管理員的測試數據（數據由學員根據現實自己模擬）

b) 使用T-SQL修改考核模板表中適用于教員的任1條測試數據，要求必須修改模板名稱

c) 使用T-SQL刪除考核模板表中適用于教員的任1條測試數據

6考核模板的綜合查詢??????????????????????????????????????????????

a) 使用T-SQL查詢適用于教員和班主任的考核模板，要求顯示格式如下圖所示：

提示：使用子查詢、 IN… 、 CASE…WHEN…THEN…

b) 使用T-SQL查詢所有正常狀態的考核模板及其包含的考核項，沒有考核項則顯示”無考核項”，要求顯示格式如下圖所示：

提示：使用多表聯接查詢、 CASE…WHEN…THEN…

第三部分考核評價點

序號	功能列表	功能描述	分數	說明
1	正確設計數據庫/數據表，添加約束	根據題目要求正確創建數據庫、數據庫，并正確添加所有約束	20	必做
2	人員類型表的增/刪/改	正確對人員類型表中的數據進行添加、修改和刪除功能	10	必做
3	考核方式類型表的增加	正確對考核方式類型表中的數據進行添加功能	5	必做
4	考核項的增加	正確對考核項表中的數據進行添加功能	5	必做
5	考核模板的增加/修改/刪除	正確對考核模板表中的數據進行添加、修改和刪除功能	20	必做
6	考核模板的綜合查詢	根據題目要求及顯示格式要求，實現對考核模板等數據的綜合查詢功能	30	必做
7	數據庫命名規范	數據庫中的表名，列名，都要符合規范要求，不能任意命名	10	必做

《sql---教學反饋系統-階段項目1》

更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯系： 360901061

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

2元

5元

10元

20元

自定義

第一部分 案例描述