factory數(shù)據(jù)庫(kù)考核

1、Database programming with RDBMS課程設(shè)計(jì)職工工資管理統(tǒng)指導(dǎo)老師：郭小芳學(xué)號(hào)：姓名： 高嘉威Database programming with RDBMS 考查命題1、根據(jù)周圍的實(shí)際情況， 自選一個(gè)小型的數(shù)據(jù)庫(kù)應(yīng)用項(xiàng)目， 并深入到應(yīng)用項(xiàng)目中調(diào)研， 進(jìn)行分析和設(shè)計(jì)。 例如可選擇職工工資管理系統(tǒng)、 人事管理系統(tǒng)、 教材管理系統(tǒng)和小型超市 商品管理系統(tǒng)和圖書管理系統(tǒng)等。要求寫出數(shù)據(jù)庫(kù)設(shè)計(jì)報(bào)告。一、功能需求系統(tǒng)的基本流程是：用戶登入主界面 選擇各項(xiàng)子系統(tǒng)。模塊設(shè)計(jì)和功能說明一）權(quán)限說明：對(duì)于不同的用戶可以對(duì)其賦予不同的權(quán)限級(jí)別。部門主管擁有最高的權(quán)限，能夠?qū)T工的資料進(jìn)行增

2、刪，對(duì)部門的信息進(jìn)行增刪。 ?1）用戶登入：填入用戶名稱，填寫正確的密碼，選擇用戶類型。2）用戶登入后，可以對(duì)自己的密碼進(jìn)行修改。3）用戶可以查看個(gè)人資料。4）5）1）方便，容易操作，快捷的原則。以下是各部門主管所特有的權(quán)限， 其他員工均沒有該權(quán)限， 不能實(shí)現(xiàn)一下的操作。部門主管可以對(duì)員工信息進(jìn)行瀏覽、查詢、編輯、插入、刪除。部門主管可以對(duì)該公司的部門信息進(jìn)行瀏覽、查詢、編輯、插入、刪除。二）系統(tǒng)的要求：職工工資管理系統(tǒng)開發(fā)時(shí)，應(yīng)該滿足以下幾點(diǎn)2）對(duì)于不同的用戶，應(yīng)該有不同的使用權(quán)限。3）各主管的任務(wù)是管理部門、職工、工資信息，即時(shí)更新、登記、發(fā)布信息，普通職工可以查詢、核對(duì)自己的基本信息和工

3、資信息。整個(gè)系統(tǒng)模塊劃分如下圖：1）2）修改密碼：凡是該系統(tǒng)的用戶都可以修改自己的密碼；3）退出系統(tǒng)信息管理：1）部門信息管理：主管可以插入，刪除，更改部門信息；2）員工信息管理：主管可以插入，刪除，更改員工的基本信息和薪資信息；?信息查詢 :?普通員工可以查詢自己的基本信息和薪資信息；二、E-R 圖三、關(guān)系模型： ?1. 部門信息（部門編號(hào)，部門名稱）2. 員工信息（員工編號(hào)，姓名，性別，學(xué)歷，出生日期，職務(wù)，部門編號(hào), 密碼） ?3. 薪資信息（賬號(hào)，員工編號(hào)，基本工資，福利，缺勤時(shí)間及原因 , 懲罰金額 , 應(yīng)繳稅 費(fèi)，總薪資）2、如創(chuàng)建職工工資管理系統(tǒng)中，一個(gè)名稱為factory 的數(shù)

4、據(jù)庫(kù)，要求：（ 1）將主數(shù)據(jù)庫(kù)文件 factory.mdf 放置在 H:DBF 文件夾中，其文件大小自動(dòng)增長(zhǎng)為按 5 MB 增長(zhǎng)。（ 2）將事務(wù)日志文件 factory_log.ldf 放置在 H:DBF 文件夾中，其文件大小自動(dòng)增長(zhǎng)為按 1 MB 增長(zhǎng)。createdatabasefactoryon（name=factory,FILENAME =H:DBFfactory.mdf ,size=3mb,filegrowth =5mb）log on（name=factory_log ,FILENAME =H:DBFfactory_log.ldf , size=1mb,filegrowth =1mb

5、）3、在上機(jī)面所創(chuàng)建的數(shù)據(jù)庫(kù) factory 中，完成如下各題操作。（1）用 create語句建立職工表 worker ，其結(jié)構(gòu)為：職工號(hào)， int ；姓名， char（8） ；性別， char（2） ；出生日期， datetime；黨員否 char（2）；參加工作， datetime；部門號(hào)， int。其中“職 工號(hào)”為主鍵。并用 insert 語句在 worker 表中輸入如下記錄：職工號(hào)姓名性別出生日期黨員否參加工作部門號(hào)1孫華男01/03/52是10/10/701012陳明男05/08/45否01/01/651023程西女06/10/80否07/10/021014孫天奇女03/10/6

6、5是07/10/871025劉夫文男01/11/42否08/10/601026劉欣男10/08/52否01/07/701017余慧男12/04/80否07/10/021038張旗男11/10/80否07/10/02102usefactorygocreate table worker（職工號(hào) int primary key,姓名 char（8）,性別 char（2）,出生日期 datetime ,黨員否 char（2）,參加工作 datetime ,部門號(hào) int,）;insert into workervalues（1,孫華 ,男,01/03/52,是 ,10/10/70,101）,（2,陳明

7、 ,男 ,05/08/45,否,01/01/65,102）,（3,程西 ,女 ,06/10/80,否,07/10/02,101）,（4,孫天奇 ,女 ,03/10/65,是,07/10/87,102）,（5,劉夫文 ,男 ,01/11/42,否,08/10/60,102）,（6,劉欣 ,男 ,10/08/52,否,01/07/70,101）,（7,余慧 ,男 ,12/04/80,否,07/10/02,103）,（8,張旗 ,男 ,11/10/80,否,07/10/02,102）（ 2）用 create 語句建立部門表 depart，其結(jié)構(gòu)為：部門號(hào)， int；部門名， char（10）。其 中

8、，“部門號(hào)”為主鍵。并用 insert 語句在 depart 表中輸入如下記錄：部門號(hào)部門名101財(cái)務(wù)處102人事處103市場(chǎng)部usefactorygocreate table depart（部門號(hào) int primary key, 部門名 char（10）;insert into depart values（101,財(cái)務(wù)處 ）,（102,人事處 ）,（103,市場(chǎng)部 ）（3）用 create 建立職工工資表 salary，其結(jié)構(gòu)為：職工號(hào)， int；姓名， char（8）；日期， datetime；工資， decimal（6,1） 。其中，“職工號(hào)” 和“日期” 為主鍵。 并用 insert

9、 語句在 salary 表中輸入如下記錄：職工號(hào)姓名日期工資1孫華01/04/041201.52陳明01/04/041350.63程西01/04/04750.84孫天奇01/04/04900.05劉夫文01/04/042006.86劉欣01/04/041250.07余慧01/04/04725.08張旗01/04/04728.0usefactorygo create table salary （職工號(hào) int primary key, 姓名 char（8）, 日期 datetime , 工資 decimal （6,1）;insert into salaryvalues（1, 孫華 ,01/04/

10、04,1201.5）,（2,陳明 ,01/04/04,1350.6）,（3,程西 ,01/04/04,750.8）, （4,孫天奇 ,01/04/04,900.0）,（5,劉夫文 ,01/04/04,2006.8）,（6,劉欣 ,01/04/04,1250.0）,（7,余慧 ,01/04/04,725.0）,（8,張旗 ,01/04/04,728.0）（ 4）建立 worker 、 depart 和 salary 3 個(gè)表之間的關(guān)系。4、在建立的 factory數(shù)據(jù)庫(kù)上，完成如下各題的程序，要求以文本格式顯示結(jié)果。1）顯示所有職工的年齡，并按職工號(hào)遞增排序。USE factoryGOSELEC

11、T 職工號(hào) ,YEAR （參加工作 ）-YEAR （出生日期 ）AS 年齡 FROM worker ORDER BY 職工號(hào) ASCGO（2）求出各部門的黨員人數(shù)。USE factoryGOSELECT 部門號(hào) ,COUNT （ 黨員否 ） AS 黨員人數(shù) FROM worker GROUP BY 部門號(hào)GO（3）顯示所有職工的姓名和 2004年 1月份的工資。USE factoryGOSELECT worker.姓名 ,工資 FROM worker ,salaryWHERE worker.職工號(hào) = salary.職工號(hào)GO（4）顯示所有職工的職工號(hào)、姓名和平均工資。USE factoryG

12、OSELECT worker.職工號(hào) ,worker .姓名,AVG （工資） AS 平均工資 FROM worker ,salary GROUP BY worker .職工號(hào) ,worker .姓名GO（5）顯示所有職工的職工號(hào)、姓名、部門名和 2004年2月份的工資，并按部門名的順序排列。 USE factoryGOSELECT worker.職工號(hào) ,worker .姓名 ,部門名 ,工資 FROM worker ,depart,salaryWHERE worker.部門號(hào) =depart.部門號(hào) AND worker .職工號(hào) = salary.職工號(hào)ORDER BY 部門名GO（6）

13、顯示各部門名和該部門的所有職工平均工資。USE factoryGOSELECT 部門名 ,AVG （工資 ） AS 平均工資 FROM worker ,depart,salaryWHERE worker.部門號(hào) =depart.部門號(hào) AND worker .職工號(hào) = salary.職工號(hào)GROUP BY 部門名GO（7）顯示所有平均工資高于 1200 的部門名和對(duì)應(yīng)的平均工資。USE factoryGOSELECT 部門名 ,AVG （工資 ）AS 平均工資 FROM worker ,depart,salaryWHERE worker.部門號(hào) =depart.部門號(hào) AND worker

14、.職工號(hào) = salary.職工號(hào)GROUP BY 部門名HAVING AVG（工資 ） 1200GO（8）顯示所有職工的職工號(hào)、姓名和部門類型，其中財(cái)務(wù)部和人事部屬于管理部門， 市場(chǎng)部屬于市場(chǎng)部門。USE factoryGOSELECT 職工號(hào) ,姓名 ,部門類型 =CASEWHEN 部門名 =財(cái)務(wù)處 THEN 管理部門 WHEN 部門名 =人事處 THEN 管理部門 WHEN 部門名 =市場(chǎng)部 THEN 市場(chǎng)部門 ENDFROM worker ,departWHERE worker.部門號(hào) =depart.部門號(hào)GO（9）若存在職工號(hào)為 10 的職工，則顯示其工作部門名稱，否則顯示相應(yīng)提示

15、信息。USE factoryGOIF EXISTS（SELECT * FROM workerWHERE 職工號(hào) =10）BEGINSELECT 部門名 FROM worker,departWHERE worker .部門號(hào) =depart.部門號(hào) AND 職工號(hào) =10ENDELSEPRINT 不存在職工號(hào)為 的員工。 GO（ 10）求出男女職工的平均工資，若男職工平均工資高出女職工平均工資50%，則顯示“男職工比女職工的工資高多了”的信息；若男職工平均工資與女職工平均工資比率在 1.5 0.8 之間，則顯示“男職工跟女職工的工資差不多”的信息；否則顯示“女職工比男職 工的工資高多了”的信息。

16、USE factoryGOSELECT 性別 ,AVG （工資 ） AS 平均工資 FROM worker,salaryWHERE worker.職工號(hào) = salary.職工號(hào)GROUP BY 性別GODECLARE m decimalDECLARE f decimalSELECT m=AVG（工資 ） FROM worker ,salaryWHERE worker.職工號(hào) =salary.職工號(hào) AND 性別 =男SELECT f=AVG （工資 ）FROM worker ,salaryWHERE worker.職工號(hào) =salary.職工號(hào) AND 性別 =女IF（ m-f ）/f =

17、0.5）PRINT 男職工比女職工的工資高多了。ELSE IF （ m /f = 0.8 AND m /f = 1.5 ）PRINT 男職工跟女職工的工資差不多。ELSEPRINT 女職工比男職工的工資高多了。GO5、在上機(jī)實(shí)驗(yàn)題 4 建立的 factory 數(shù)據(jù)庫(kù)上， 完成如下各題 （所有 SELECT 語句的查詢 結(jié)果以文本格式顯示）。（ 1）刪除 factory 數(shù)據(jù)庫(kù)上各個(gè)表之間建立的關(guān)系。ALTER TABLE salaryDROP CONSTRAINT FK_salary_workerGOALTER TABLE workerDROP CONSTRAINT FK_worker_dep

18、artGO（2）顯示各職工的工資記錄和相應(yīng)的工資小計(jì)。usefactorygoselect 職工號(hào) , 姓名 ,工資 from salaryorder by 職工號(hào)compute sum（工資 ） by 職工號(hào)go （3）按性別和部門名的所有組合方式列出相應(yīng)的平均工資。USE factoryGOSELECT 性別 ,部門名 ,AVG （工資 ）AS 平均工資 FROM worker ,depart,salaryWHERE worker.部門號(hào) =depart.部門號(hào) AND worker .職工號(hào) = salary.職工號(hào)GROUP BY 性別 ,部門名GO（ 4）在 worker 表中使用以

19、下語句插入一個(gè)職工記錄：INSERT INTO worker VALUES（20,陳立 , 女,55/03/08,1,75/10/10,4）在 depart 表中使用以下語句插入一個(gè)部門記錄：INSERT INTO depart VALUES（5, 設(shè)備處 ）對(duì) worker 和 depart 表進(jìn)行全外連接顯示職工的職工號(hào)、姓名和部門名，然后刪除這兩 個(gè)插入的記錄。USE factoryGOINSERT INTO worker VALUES （20,陳立 ,55/03/08 ,1,75/10/10 ,4）INSERT INTO depart VALUES （5,設(shè)備處 ）GOSELECT 職

20、工號(hào) ,姓名 ,部門名 FROM workerFULL OUTER JOIN depart ON worker.部門號(hào) =depart.部門號(hào)GODELETE FROM workerWHERE 職工號(hào) =20GODELETE FROM departWHERE 部門號(hào) =5GO（5）顯示最高工資的職工的職工號(hào)、姓名、部門名、工資發(fā)放日期和工資。USE factoryGOSELECT worker.職工號(hào) ,worker .姓名,部門名 ,salary.日期 ,工資 FROM worker ,depart,salary WHERE worker.部門號(hào) =depart.部門號(hào) AND worker

21、 .職工號(hào) = salary.職工號(hào) AND 工資 = （SELECT MAX （工資 ） FROM salary）GO（6）顯示最高工資的職工所在的部門名。USE factoryGOSELECT 部門名 FROM worker ,depart,salaryWHERE worker.部門號(hào) =depart.部門號(hào) AND worker .職工號(hào) = salary.職工號(hào) AND 工資 = （SELECT MAX （工資 ） FROM salary）GO（7）顯示所有平均工資低于全部職工平均工資的職工的職工號(hào)和姓名。USE factoryGOSELECT worker.職工號(hào) ,worker .

22、姓名 FROM worker,salaryWHERE worker.職工號(hào) =salary.職工號(hào) AND 工資 （SELECT AVG（工資 ）FROM salary）GO（ 8）采用游標(biāo)方式實(shí)現(xiàn)（ 6）小題的功能。USE factoryGODECLARE s_max CHAR （10）DECLARE st_cursor CURSORFOR SELECT 部門名 FROM worker ,depart,salaryWHERE worker.部門號(hào) =depart.部門號(hào) AND worker .職工號(hào) = salary.職工號(hào) AND 工資 = （SELECT MAX （工資 ） FROM

23、salary）OPEN st_cursorFETCH NEXT FROM st_cursor INTO s_maxPRINT 部門名 WHILE FETCH_STATUS = 0BEGINPRINT CAST （s_max AS char（10）FETCH NEXT FROM st_cursor INTO s_maxENDCLOSE st_cursorDEALLOCATE st_cursorGO（ 9）采用游標(biāo)方式實(shí)現(xiàn)（ 7）小題的功能。USE factoryGODECLARE c_no int,s_namevarchar（10）DECLARE st_cursor CURSORFOR SELE

24、CT worker.職工號(hào) ,worker.姓名 FROM worker ,salaryWHERE worker.職工號(hào) =salary.職工號(hào) AND 工資 =0 AND 工資 = 9999 ）GO（8）實(shí)施 depart 表的“部門號(hào)”列值唯一的非聚集索引的約束。USE factoryGOALTER TABLE departADD CONSTRAINT unique_bmhUNIQUE NONCLUSTERED （部門號(hào) ）GO（ 9）為 worker 表建立外鍵“部門號(hào)”，參考表depart 的“部門號(hào)”列。USE factoryGOALTER TABLE workerADD CONST

25、RAINT wj_worker_departFOREIGN KEY （部門號(hào) ） REFERENCES depart（部門號(hào) ）GO（ 10）建立一個(gè)規(guī)則 sex：性別=男 OR 性別 =女，將其綁定到 worker 表的“性別” 列上。USE factoryGOCREATE RULE sexAS性別=男 OR性別 =女GOEXEC sp_bindrule sex,worker.性別 GO（ 11）刪除（ 1）小題所建立的約束。USE factoryGOALTER TABLE workerDROP CONSTRAINT default_xbGO（ 12）刪除（ 2）小題所建立的約束。USE f

26、actoryGOALTER TABLE salaryDROP CONSTRAINT check_gzGO（ 12）刪除（ 3）小題所建立的約束。USE factoryGOALTER TABLE departDROP CONSTRAINT unique_bmhGO（ 14）刪除（ 4）小題所建立的約束。USE factoryGOALTER TABLE workerDROP CONSTRAINT wj_worker_departGO（ 15）解除（ 5）小題所建立的綁定并刪除規(guī)則 sex。EXEC sp_unbindrule worker. 性別 GODROP RULE sexGO9、在的 fac

27、tory 數(shù)據(jù)庫(kù)上，使用 T-SQL 語句完成如下各題：1）創(chuàng)建一個(gè)為 worker 表添加職工記錄的存儲(chǔ)過程 Addworker 。 CREATE PROCEDURE Addworkernumber int,name char（8）,gender char（2）,birthday datetime ,party char（2）,starttime datetime, departnumber intASBEGININSERT INTO workerVALUES （number,name,gender,birthday ,party ,starttime ,departnumber ）ENDG

28、O（ 2）創(chuàng)建一個(gè)存儲(chǔ)過程 Delworker 刪除 worker 表中指定職工號(hào)的記錄。USE factoryGOCREATE PROCEDURE Delworkernumber intASBEGINDELETE FROM workerWHERE 職工號(hào) =numberENDGO（ 3）顯示存儲(chǔ)過程 Delworker 。USE factoryGOEXEC sp_helptext DelworkerGO（ 4）刪除存儲(chǔ)過程 Addworker 和 Delworker 。USE factoryGODROP PROCEDURE Addworker ,DelworkerGO10、在 factory

29、 數(shù)據(jù)庫(kù)上，使用 T-SQL 語句完成如下各題：（ 1）在表 depart 上創(chuàng)建一個(gè)觸發(fā)器 depart_update，當(dāng)更改部門號(hào)時(shí)同步更改worker 表中對(duì)應(yīng)的部門號(hào)。USE factoryGOCREATE TRIGGER depart_update ON departFOR UPDATEASIF UPDATE （部門號(hào) ）BEGINDECLARE number1 int ,number2 intSELECT number1 =deleted.部門號(hào) ,number2 =inserted.部門號(hào)FROM deleted,insertedUPDATE workerSET 部門號(hào) =num

30、ber1WHERE 部門號(hào) =number2ENDGO（2）在表 worker 上創(chuàng)建一個(gè)觸發(fā)器 worker_delete ，當(dāng)刪除職工記錄時(shí)同步刪除 salary 表中對(duì)應(yīng)職工的工資記錄。USE factoryGOCREATE TRIGGER worker_delete ON workerFOR DELETEASBEGINDELETE FROM salaryWHERE 職工號(hào) =（SELECT 職工號(hào) FROM deleted）ENDGO（ 3）刪除觸發(fā)器 depart_update。DROP TRIGGER depart_updateGO（ 4）刪除觸發(fā)器 worker_delete 。

31、DROP TRIGGER worker_deleteGO11、在 SQL Server 管理控制器中完成如下操作：（1）創(chuàng)建一個(gè)登錄賬號(hào) XYZ/123 （其默認(rèn)的工作數(shù)據(jù)庫(kù)為 factory ；其“服務(wù)器角色” 設(shè)置為 sysadmin；將“映射到此登錄名的用戶”設(shè)置為Factory ，使其具有 public 權(quán)限；設(shè)置安全對(duì)象 LCB-PC 服務(wù)器具有 Connect SQL 權(quán)限）。CREATE LOGIN XYZWITH PASSWORD =123,DEFAULT_DATABASE =factoryGOEXEC sp_addsrvrolemember XYZ ,sysadminGOCR

32、EATE USER FactoryFOR LOGIN XYZGOGRANT public TO FactoryGOGRANT Connect SQL TO LCB_PCGO（ 2）修改（ 1）中為 factory 數(shù)據(jù)庫(kù)創(chuàng)建的用戶賬號(hào) XYZ 的屬性，使 XYZ 登錄賬號(hào)對(duì) factory 數(shù)據(jù)庫(kù)具有 db_owner 權(quán)限。12、使 用 SQL Server 管 理 控 制 器 對(duì) factory 數(shù) 據(jù) 庫(kù) 執(zhí) 行 完 全 備 份 （ 備 份 到 H:DBFbackup1 文件中）和恢復(fù)操作。13、使用 SQL Server 管理控制器生成數(shù)據(jù)庫(kù) factory 的腳本文件 H:DBFfa

33、ctory.sql ，并 顯示該文件的內(nèi)容。USE masterGO/* Object:Database factoryScript Date: 12/23/2015 12:51:34 */CREATE DATABASE factory ON PRIMARY（ NAME = Nfactory , FILENAME = NG:DBFfactory.mdf ,SIZE = 3072KB , MAXSIZE= UNLIMITED , FILEGROWTH = 5120KB )LOG ON( NAME = Nfactory_log , FILENAME = NG:DBFfactory_log.ldf

34、, SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB )GOALTER DATABASE factory SET COMPATIBILITY_LEVEL = 100GOIF (1 = FULLTEXTSERVICEPROPERTY (IsFullTextInstalled ) beginEXEC factory .dbo .sp_fulltext_database action = enable endALTER DATABASEGOALTER DATABASEGOALTER DATABASEGOALTER DATABASEGOALTER DATABASEGOALTER DATABASEGOALTER DATABASEGOALTER DATABASEGOALTER DATABASEGOALTER DATABASEGOALTER DATABASEGOALTER DATABASEGOALTER DATABASE