1、Database programming with RDBMS課程設計職工工資管理統指導老師：郭小芳學號：1441904103姓名： 高嘉威 Database programming with RDBMS考查命題1、根據周圍的實際情況，自選一個小型的數據庫應用項目，并深入到應用項目中調研，進行分析和設計。例如可選擇職工工資管理系統、人事管理系統、教材管理系統和小型超市商品管理系統和圖書管理系統等。要求寫出數據庫設計報告。一、功能需求系統的基本流程是：用戶登入主界面選擇各項子系統。模塊設計和功能說明（一）權限說明：對于不同的用戶可以對其賦予不同的權限級別。部門主管擁有最高的權限，能夠對員工的資料

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

3、和工資信息。職工工資管理系統 整個系統模塊劃分如下圖：信息查詢信息管理系統管理用戶登入退出系統修改密碼員工信息管理部門信息管理基本信息查詢薪資信息查詢薪資信息管理基本信息管理系統管理：1）用戶登錄: 普通用戶登錄：只可查詢信息，不可插入、修改、刪除； 主管登錄：可插入、修改、查詢、刪除信息；2）修改密碼：凡是該系統的用戶都可以修改自己的密碼；3）退出系統信息管理：1）部門信息管理：主管可以插入，刪除，更改部門信息；2）員工信息管理：主管可以插入，刪除，更改員工的基本信息和薪資信息；信息查詢:普通員工可以查詢自己的基本信息和薪資信息；2、 E-R圖3、 關系模型：1.部門信息（部門編號，部門名稱

4、）2.員工信息（員工編號，姓名，性別，學歷，出生日期，職務，部門編號,密碼）3.薪資信息（賬號，員工編號，基本工資，福利，缺勤時間及原因,懲罰金額,應繳稅 費，總薪資）2、如創建職工工資管理系統中，一個名稱為factory的數據庫，要求：（1）將主數據庫文件factory.mdf放置在H:DBF文件夾中，其文件大小自動增長為按5 MB增長。（2）將事務日志文件factory_log.ldf放置在H:DBF文件夾中，其文件大小自動增長為按1 MB增長。create database factoryon(name=factory,FILENAME=H:DBFfactory.mdf,size=3mb

5、,filegrowth=5mb)log on(name=factory_log,FILENAME=H:DBFfactory_log.ldf,size=1mb,filegrowth=1mb)3、在上機面所創建的數據庫factory中，完成如下各題操作。（1）用create語句建立職工表worker，其結構為：職工號，int；姓名，char(8)；性別，char(2)；出生日期，datetime；黨員否char(2）；參加工作，datetime；部門號，int。其中“職工號”為主鍵。并用insert語句在worker表中輸入如下記錄：職工號姓名性別出生日期黨員否參加工作部門號1孫華男01/03/5

6、2是10/10/701012陳明男05/08/45否01/01/651023程西女06/10/80否07/10/021014孫天奇女03/10/65是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/02102use factorygocreate table worker(職工號 int primary key, 姓名 char(8), 性別 char(2), 出生日期 datetime, 黨員否 char(2), 參加工作 dateti

7、me, 部門號 int,);insert into workervalues(1,孫華,男,01/03/52,是,10/10/70,101),(2,陳明,男,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,

8、102)（2）用create語句建立部門表depart，其結構為：部門號，int；部門名，char(10)。其中，“部門號”為主鍵。并用insert語句在depart表中輸入如下記錄：部門號部門名101財務處102人事處103市場部use factorygocreate table depart(部門號 int primary key,部門名 char(10);insert into departvalues(101,財務處),(102,人事處), (103,市場部)（3）用create建立職工工資表salary，其結構為：職工號，int；姓名，char(8)；日期，datetime；工資，d

9、ecimal(6,1)。其中，“職工號”和“日期”為主鍵。并用insert語句在salary表中輸入如下記錄：職工號姓名日期工資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.0use factory gocreate table salary(職工號 int primary key,姓名 char(8),日期 datetime,工資 decimal(6,1);insert in

10、to salaryvalues(1,孫華,01/04/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個表之間的關系。4、在建立的factory數據庫上，完成如下各題的程序，要求以文本格式顯示結果。（1）顯示所有職工的年齡，并按職工號遞增排序。USE f

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

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

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

14、G AVG(工資)1200GO（8） 顯示所有職工的職工號、姓名和部門類型，其中財務部和人事部屬于管理部門，市場部屬于市場部門。USE factoryGOSELECT 職工號,姓名,部門類型=CASE WHEN 部門名=財務處 THEN 管理部門WHEN 部門名=人事處 THEN 管理部門WHEN 部門名=市場部 THEN 市場部門END FROM worker,departWHERE worker.部門號=depart.部門號GO（9） 若存在職工號為10的職工，則顯示其工作部門名稱，否則顯示相應提示信息。USE factoryGOIF EXISTS(SELECT * FROM worker

15、WHERE 職工號=10)BEGIN SELECT 部門名 FROM worker,depart WHERE worker.部門號=depart.部門號 AND 職工號=10ENDELSE PRINT不存在職工號為的員工。 GO（10） 求出男女職工的平均工資，若男職工平均工資高出女職工平均工資50%，則顯示“男職工比女職工的工資高多了”的信息；若男職工平均工資與女職工平均工資比率在1.50.8之間，則顯示“男職工跟女職工的工資差不多”的信息；否則顯示“女職工比男職工的工資高多了”的信息。USE factoryGOSELECT 性別,AVG(工資) AS 平均工資 FROM worker,sa

16、laryWHERE worker.職工號=salary.職工號GROUP BY 性別GODECLARE m decimalDECLARE f decimalSELECT m=AVG(工資) FROM worker,salaryWHERE worker.職工號=salary.職工號 AND 性別=男SELECT f=AVG(工資) FROM worker,salaryWHERE worker.職工號=salary.職工號 AND 性別=女IF(m-f)/f=0.5) PRINT 男職工比女職工的工資高多了。ELSE IF(m/f=0.8 AND m/f=1.5) PRINT 男職工跟女職工的工資

17、差不多。ELSE PRINT 女職工比男職工的工資高多了。GO5、在上機實驗題4建立的factory數據庫上，完成如下各題（所有SELECT語句的查詢結果以文本格式顯示）。（1）刪除factory數據庫上各個表之間建立的關系。ALTER TABLE salaryDROP CONSTRAINT FK_salary_workerGOALTER TABLE workerDROP CONSTRAINT FK_worker_departGO（2） 顯示各職工的工資記錄和相應的工資小計。use factorygoselect 職工號,姓名,工資 from salaryorder by 職工號compute

18、 sum(工資) by 職工號go（3） 按性別和部門名的所有組合方式列出相應的平均工資。USE factoryGOSELECT 性別,部門名,AVG(工資) AS 平均工資 FROM worker,depart,salaryWHERE worker.部門號=depart.部門號 AND worker.職工號=salary.職工號GROUP BY 性別,部門名GO（4） 在worker表中使用以下語句插入一個職工記錄：INSERT INTO worker VALUES(20,陳立,女,55/03/08,1,75/10/10,4)在depart表中使用以下語句插入一個部門記錄：INSERT IN

19、TO depart VALUES(5,設備處)對worker和depart表進行全外連接顯示職工的職工號、姓名和部門名，然后刪除這兩個插入的記錄。USE factoryGOINSERT INTO worker VALUES(20,陳立,55/03/08,1,75/10/10,4)INSERT INTO depart VALUES(5,設備處)GOSELECT 職工號,姓名,部門名 FROM workerFULL OUTER JOIN depart ON worker.部門號=depart.部門號GODELETE FROM workerWHERE 職工號=20GODELETE FROM depa

20、rtWHERE 部門號=5GO（5） 顯示最高工資的職工的職工號、姓名、部門名、工資發放日期和工資。USE factoryGOSELECT worker.職工號,worker.姓名,部門名,salary.日期,工資 FROM worker,depart,salaryWHERE worker.部門號=depart.部門號 AND worker.職工號=salary.職工號 AND 工資=(SELECT MAX(工資) FROM salary)GO（6） 顯示最高工資的職工所在的部門名。USE factoryGOSELECT 部門名 FROM worker,depart,salaryWHERE w

21、orker.部門號=depart.部門號 AND worker.職工號=salary.職工號 AND 工資=(SELECT MAX(工資) FROM salary)GO（7） 顯示所有平均工資低于全部職工平均工資的職工的職工號和姓名。USE factoryGOSELECT worker.職工號,worker.姓名 FROM worker,salaryWHERE worker.職工號=salary.職工號 AND 工資(SELECT AVG(工資) FROM salary)GO（8） 采用游標方式實現（6）小題的功能。USE factoryGODECLARE s_max CHAR(10)DECL

22、ARE st_cursor CURSOR FOR SELECT 部門名 FROM worker,depart,salaryWHERE worker.部門號=depart.部門號 AND worker.職工號=salary.職工號 AND 工資=(SELECT MAX(工資) FROM salary)OPEN st_cursorFETCH NEXT FROM st_cursor INTO s_maxPRINT 部門名WHILE FETCH_STATUS = 0 BEGIN PRINT CAST(s_max AS char(10) FETCH NEXT FROM st_cursor INTO s_

23、maxENDCLOSE st_cursorDEALLOCATE st_cursorGO（9） 采用游標方式實現（7）小題的功能。USE factoryGODECLARE c_no int,s_name varchar(10)DECLARE st_cursor CURSOR FOR SELECT worker.職工號,worker.姓名 FROM worker,salaryWHERE worker.職工號=salary.職工號 AND 工資=0 AND 工資=9999)GO（8） 實施depart表的“部門號”列值唯一的非聚集索引的約束。USE factoryGOALTER TABLE depa

24、rtADD CONSTRAINT unique_bmhUNIQUE NONCLUSTERED (部門號)GO（9） 為worker表建立外鍵“部門號”，參考表depart的“部門號”列。USE factoryGOALTER TABLE workerADD CONSTRAINT wj_worker_departFOREIGN KEY (部門號) REFERENCES depart(部門號)GO（10） 建立一個規則sex：性別=男 OR 性別=女，將其綁定到worker表的“性別”列上。USE factoryGOCREATE RULE sexAS 性別=男 OR 性別=女GOEXEC sp_bi

25、ndrule sex,worker.性別GO（11） 刪除（1）小題所建立的約束。USE factoryGOALTER TABLE workerDROP CONSTRAINT default_xbGO（12） 刪除（2）小題所建立的約束。USE factoryGOALTER TABLE salaryDROP CONSTRAINT check_gzGO（12） 刪除（3）小題所建立的約束。USE factoryGOALTER TABLE departDROP CONSTRAINT unique_bmhGO（14） 刪除（4）小題所建立的約束。USE factoryGOALTER TABLE wo

26、rkerDROP CONSTRAINT wj_worker_departGO（15） 解除（5）小題所建立的綁定并刪除規則sex。EXEC sp_unbindrule worker.性別GODROP RULE sexGO9、在的factory數據庫上，使用T-SQL語句完成如下各題：（1）創建一個為worker表添加職工記錄的存儲過程Addworker。CREATE PROCEDURE Addworkernumber int,name char(8),gender char(2),birthday datetime,party char(2),starttime datetime,depart

27、number intASBEGIN INSERT INTO worker VALUES(number,name,gender,birthday,party,starttime,departnumber)ENDGO（2） 創建一個存儲過程Delworker刪除worker表中指定職工號的記錄。USE factoryGOCREATE PROCEDURE Delworkernumber intASBEGIN DELETE FROM worker WHERE 職工號=numberENDGO（3） 顯示存儲過程Delworker。USE factoryGOEXEC sp_helptext Delwork

28、erGO（4）刪除存儲過程Addworker和Delworker。USE factoryGODROP PROCEDURE Addworker,DelworkerGO10、在factory數據庫上，使用T-SQL語句完成如下各題：（1）在表depart上創建一個觸發器depart_update，當更改部門號時同步更改worker表中對應的部門號。USE factoryGOCREATE TRIGGER depart_update ON departFOR UPDATEASIF UPDATE(部門號)BEGIN DECLARE number1 int,number2 int SELECT numbe

29、r1=deleted.部門號,number2=inserted.部門號 FROM deleted,inserted UPDATE worker SET 部門號=number1 WHERE 部門號=number2ENDGO（2） 在表worker上創建一個觸發器worker_delete，當刪除職工記錄時同步刪除salary表中對應職工的工資記錄。USE factoryGOCREATE TRIGGER worker_delete ON workerFOR DELETEASBEGIN DELETE FROM salary WHERE 職工號= (SELECT 職工號 FROM deleted)EN

30、DGO（3） 刪除觸發器depart_update。DROP TRIGGER depart_updateGO（4）刪除觸發器worker_delete。DROP TRIGGER worker_deleteGO11、在SQL Server管理控制器中完成如下操作：（1）創建一個登錄賬號XYZ/123（其默認的工作數據庫為factory；其“服務器角色”設置為sysadmin；將“映射到此登錄名的用戶”設置為Factory，使其具有public權限；設置安全對象LCB-PC服務器具有Connect SQL權限）。CREATE LOGIN XYZWITH PASSWORD=123,DEFAULT_D

31、ATABASE=factoryGOEXEC sp_addsrvrolemember XYZ,sysadminGOCREATE USER FactoryFOR LOGIN XYZGOGRANT public TO FactoryGOGRANT Connect SQL TO LCB_PCGO（2） 修改（1）中為factory數據庫創建的用戶賬號XYZ的屬性，使XYZ登錄賬號對factory數據庫具有db_owner權限。12、 使用SQL Server管理控制器對factory數據庫執行完全備份（備份到H:DBFbackup1文件中）和恢復操作。13、使用SQL Server管理控制器生成數據庫

32、factory的腳本文件H:DBFfactory.sql，并顯示該文件的內容。USE masterGO/* Object: Database factory Script 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 = N

33、G:DBFfactory_log.ldf , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB )GOALTER DATABASE factory SET COMPATIBILITY_LEVEL = 100GOIF (1 = FULLTEXTSERVICEPROPERTY(IsFullTextInstalled)beginEXEC factory.dbo.sp_fulltext_database action = enableendGOALTER DATABASE factory SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE factory SET ANSI_NULLS OFF GOALTER DATABASE factory SET ANSI_PADDING OFF GOALTER DATABASE factory SET ANSI_WARNINGS OFF GOALTER DATABASE factory SET ARITHABORT OFF GOALTER DATABASE factory SET AUTO_CLOSE OFF GOALTE