factory數(shù)據(jù)庫考核資料1

《DatabaseprogrammingwithRDBMS》課程設(shè)計職工工資管理統(tǒng)指導(dǎo)老師：學(xué)號：姓名：《DatabaseprogrammingwithRDBMS》考查命題1、根據(jù)周圍的實際情況，自選一個小型的數(shù)據(jù)庫應(yīng)用項目，并深入到應(yīng)用項目中調(diào)研，進行分析和設(shè)計。例如可選擇職工工資管理系統(tǒng)、人事管理系統(tǒng)、教材管理系統(tǒng)和小型超市商品管理系統(tǒng)和圖書管理系統(tǒng)等。要求寫出數(shù)據(jù)庫設(shè)計報告。一、功能需求系統(tǒng)的基本流程是：用戶登入一＞主界面一＞選擇各項子系統(tǒng)。模塊設(shè)計和功能說明（一）權(quán)限說明：對于不同的用戶可以對其賦予不同的權(quán)限級別。部門主管擁有最高的權(quán)限，能夠?qū)T工的資料進行增刪，對部門的信息進行增刪。1）用戶登入：填入用戶名稱，填寫正確的密碼，選擇用戶類型。2）用戶登入后，可以對自己的密碼進行修改。3）用戶可以查看個人資料。以下是各部門主管所特有的權(quán)限，其他員工均沒有該權(quán)限，不能實現(xiàn)一下的操作。4）部門主管可以對員工信息進行瀏覽、查詢、編輯、插入、刪除。5）部門主管可以對該公司的部門信息進行瀏覽、查詢、編輯、插入、刪除。（二）系統(tǒng)的要求：職工工資管理系統(tǒng)開發(fā)時，應(yīng)該滿足以下幾點1）方便，容易操作，快捷的原則。2）對于不同的用戶，應(yīng)該有不同的使用權(quán)限。3）各主管的任務(wù)是管理部門、職工、工資信息，即時更新、登記、發(fā)布信息，普通職工可以查詢、核對自己的基本信息和工資信息。整個系統(tǒng)模塊劃分如下圖：系統(tǒng)管理：1）用戶登錄：普通用戶登錄：只可查詢信息，不可插入、修改、刪除；主管登錄：可插入、修改、查詢、刪除信息；2）修改密碼：凡是該系統(tǒng)的用戶都可以修改自己的密碼；3）退出系統(tǒng)信息管理：1）部門信息管理：主管可以插入，刪除，更改部門信息；2）員工信息管理：主管可以插入，刪除，更改員工的基本信息和薪資信息;信息查詢：普通員工可以查詢自己的基本信息和薪資信息；二、E-R圖.部門信息（部門編號，部門名稱）.員工信息（員工編號，姓名，性別，學(xué)歷，出生日期，職務(wù)，部門編號,密碼）.薪資信息（賬號，員工編號，基本工資，福利，缺勤時間及原因，懲罰金額,應(yīng)繳稅費，總薪資）2、如創(chuàng)建職工工資管理系統(tǒng)中，一個名稱為factory的數(shù)據(jù)庫，要求：（1）將主數(shù)據(jù)庫文件factory.mdf放置在H:\DBF文件夾中，其文件大小自動增長為按5MB增長。(2)將事務(wù)日志文件factory_log.ldf放置在H:\DBF文件夾中，其文件大小自動增長為按1MB增長。createdatabasefactoryon(name=factory,FILENAME='H:\DBF\factory.mdf',size=3mb,filegrowth=5mb)logon(name=factory_log,FILENAME='H:\DBF\factory_log.ldf',size=1mb,filegrowth=1mb)3、在上機面所創(chuàng)建的數(shù)據(jù)庫factory中，完成如下各題操作。(1)用create語句建立職工表worker，其結(jié)構(gòu)為：職工號，int；姓名，char(8)；性別，char(2)；出生日期，datetime；黨員否char(2)；參加工作，datetime；部門號，int。其中“職工號”為主鍵。并用insert語句在worker表中輸入如下記錄：職工號姓名性別出生日期黨員否參加工作部門號1孫華男01/03/52是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/02102usefactorygocreatetableworker(職工號intprimarykey,姓名char(8),性別char(2),出生日期datetime,黨員否char(2),參加工作datetime,部門號int,);insertintoworkervalues(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,102)(2)用create語句建立部門表depart,其結(jié)構(gòu)為：部門號，int；部門名，char(10)。其中，“部門號”為主鍵。并用insert語句在depart表中輸入如下記錄：部門號部門名101財務(wù)處102人事處103市場部usefactorygocreatetabledepart（部門號intprimarykey,部門名char（10））;insertintodepartvalues（101,‘財務(wù)處'），（102,'人事處'）,(103,'市場部')(3)用create建立職工工資表salary，其結(jié)構(gòu)為：職工號，int；姓名，由21(8)；日期，datetime；工資，decimal(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.0usefactorygocreatetablesalary（職工號intprimarykey,姓名char（8）,日期datetime,工資decimal（6,1）

);insertintosalaryvalues(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和salary3個表之間的關(guān)系。㈣中曾。■.JL1°focal][SQLGerwI&OilfiOa-禾黑明5網(wǎng)d+_j雅就^卜l口音即美某總?cè)铡籺i、3rMst4、在建立的factory數(shù)據(jù)庫上，完成如下各題的程序，要求以文本格式顯示結(jié)果。（1）顯示所有職工的年齡，并按職工號遞增排序。USEfactoryGOSELECT職工號,YEAR（參加工作）-YEAR（出生日期）AS年齡FROMworkerORDERBY職工號ASCGO（2）求出各部門的黨員人數(shù)。USEfactoryGOSELECT部門號,COUNT（黨員否）AS黨員人數(shù)FROMworkerGROUPBY部門號GO（3）顯示所有職工的姓名和2004年1月份的工資。USEfactoryGOSELECTworker.姓名，工資FROMworker,salaryWHEREworker.職工號=salary.職工號GO（4）顯示所有職工的職工號、姓名和平均工資。USEfactoryGOSELECTworker.職工號,worker.姓名,AVG（工資）AS平均工資FROMworker,salaryGROUPBYworker.職工號,worker.姓名GO（5）顯示所有職工的職工號、姓名、部門名和2004年2月份的工資，并按部門名的順序排列。USEfactoryGOSELECTworker.職工號,worker.姓名,部門名，工資FROMworker,depart,salaryWHEREworker.部門號=depart.部門號ANDworker.職工號=salary.職工號ORDERBY部門名GO（6）顯示各部門名和該部門的所有職工平均工資。USEfactoryGOSELECT部門名,AVG（工資）AS平均工資FROMworker,depart,salaryWHEREworker.部門號=depart.部門號ANDworker.職工號=salary.職工號GROUPBY部門名GO（7）顯示所有平均工資高于1200的部門名和對應(yīng)的平均工資。USEfactoryGOSELECT部門名,AVG（工資）AS平均工資FROMworker,depart,salaryWHEREworker.部門號=depart.部門號ANDworker.職工號=salary.職工號GROUPBY部門名HAVINGAVG（工資）＞1200GO（8）顯示所有職工的職工號、姓名和部門類型，其中財務(wù)部和人事部屬于管理部門，市場部屬于市場部門。USEfactoryGOSELECT職工號,姓名,部門類型=CASEWHEN部門名='財務(wù)處'THEN'管理部門'WHEN部門名='人事處'THEN'管理部門'WHEN部門名='市場部'THEN'市場部門'ENDFROMworker,departWHEREworker.部門號=depart.部門號GO（9）若存在職工號為10的職工，則顯示其工作部門名稱，否則顯示相應(yīng)提示信息。USEfactoryGOIFEXISTS（SELECT*FROMworkerWHERE職工號=10）BEGINSELECT部門名FROMworker,departWHEREworker.部門號=depart.部門號AND職工號=10ENDELSEPRINT'不存在職工號為'‘的員工?！疓O(10)求出男女職工的平均工資，若男職工平均工資高出女職工平均工資50%，則顯示“男職工比女職工的工資高多了”的信息；若男職工平均工資與女職工平均工資比率在1.5?0.8之間，則顯示“男職工跟女職工的工資差不多”的信息；否則顯示“女職工比男職工的工資高多了”的信息。USEfactoryGOSELECT性別,AVG(工資)AS平均工資FROMworker,salaryWHEREworker.職工號=salary.職工號GROUPBY性別GODECLARE@mdecimalDECLARE@fdecimalSELECT@m=AVG(工資)FROMworker,salaryWHEREworker.職工號=salary.職工號AND性別='男’SELECT@f=AVG(工資)FROMworker,salaryWHEREworker.職工號=salary.職工號AND性別='女’IF((@m-@f)/@f>=0.5)PRINT'男職工比女職工的工資高多了。'ELSEIF(@m/@f>=0.8AND@m/@f<=1.5)PRINT'男職工跟女職工的工資差不多。'ELSEPRINT'女職工比男職工的工資高多了。'GO5、在上機實驗題4建立的factory數(shù)據(jù)庫上，完成如下各題(所有SELECT語句的查詢結(jié)果以文本格式顯示)。(1)刪除factory數(shù)據(jù)庫上各個表之間建立的關(guān)系。ALTERTABLEsalaryDROPCONSTRAINTFK_salary_workerGOALTERTABLEworkerDROPCONSTRAINTFK_worker_departGO(2)顯示各職工的工資記錄和相應(yīng)的工資小計。usefactorygoselect職工號,姓名,工資fromsalaryorderby職工號computesum(工資)by職工號go(3)按性別和部門名的所有組合方式列出相應(yīng)的平均工資。USEfactoryGOSELECT性別，部門名,AVG(工資)AS平均工資FROMworker,depart,salaryWHEREworker.部門號=depart.部門號ANDworker.職工號=salary.職工號GROUPBY性別,部門名GO(4)在worker表中使用以下語句插入一個職工記錄：INSERTINTOworkerVALUES(20,'陳立'，'女','55/03/08',1,'75/10/10',4)在depart表中使用以下語句插入一個部門記錄：INSERTINTOdepartVALUES(5,'設(shè)備處')對worker和depart表進行全外連接顯示職工的職工號、姓名和部門名，然后刪除這兩個插入的記錄。USEfactoryGOINSERTINTOworkerVALUES('20','陳立','55/03/08',1,'75/10/1。,4)INSERTINTOdepartVALUES('5','設(shè)備處')GOSELECT職工號,姓名,部門名FROMworkerFULLOUTERJOINdepartONworker.部門號=depart.部門號GODELETEFROMworkerWHERE職工號=20GODELETEFROMdepartWHERE部門號=5GO(5)顯示最高工資的職工的職工號、姓名、部門名、工資發(fā)放日期和工資。USEfactoryGOSELECTworker.職工號,worker.姓名，部門名,salary.日期，工資FROMworker,depart,salaryWHEREworker.部門號=depart.部門號ANDworker.職工號=salary.職工號AND工資=(SELECTMAX(工資)FROMsalary)GO(6)顯示最高工資的職工所在的部門名。USEfactoryGOSELECT部門名FROMworker,depart,salaryWHEREworker.部門號=depart.部門號ANDworker.職工號=salary.職工號AND工資=(SELECTMAX(工資)FROMsalary)GO(7)顯示所有平均工資低于全部職工平均工資的職工的職工號和姓名。USEfactoryGOSELECTworker.職工號,worker.姓名FROMworker,salaryWHEREworker.職工號=salary.職工號AND工資＜(SELECTAVG(工資)FROMsalary)GO(8)采用游標(biāo)方式實現(xiàn)(6)小題的功能。USEfactoryGODECLARE@s_maxCHAR(10)DECLAREst_cursorCURSORFORSELECT部門名FROMworker,depart,salaryWHEREworker.部門號=depart.部門號ANDworker.職工號=salary.職工號AND工資=(SELECTMAX(工資)FROMsalary)OPENst_cursorFETCHNEXTFROMst_cursorINTO@s_maxPRINT'部門名'WHILE@@FETCH_STATUS=0BEGINPRINTCAST(@s_maxASchar(10))FETCHNEXTFROMst_cursorINTO@s_maxENDCLOSEst_cursorDEALLOCATEst_cursorGO(9)采用游標(biāo)方式實現(xiàn)(7)小題的功能。USEfactoryGODECLARE@c_noint,@s_namevarchar(10)DECLAREst_cursorCURSORFORSELECTworker.職工號,worker.姓名FROMworker,salaryWHEREworker.職工號=salary.職工號AND工資＜(SELECTAVG(工資)FROMsalary)OPENst_cursorFETCHNEXTFROMst_cursorINTO@c_no,@s_namePRINT'職工號'+''+'姓名'WHILE@@FETCH_STATUS=0BEGINPRINTCAST(@c_noASvarchar(10))+''+@s_nameFETCHNEXTFROMst_cursorINTO@c_no,@s_nameENDCLOSEst_cursorDEALLOCATEst_cursorGO(10)先顯示worker表中的職工人數(shù)，開始一個事務(wù)，插入一個職工記錄，再顯示worker表中的職工人數(shù)，回滾該事務(wù)，最后顯示worker表中的職工人數(shù)。USEfactoryGOSELECTCOUNT（*）AS職工人數(shù)FROMworkerGOBEGINTRANSACTIONINSERTINTOworkerVALUES（11,'小明','女','1955/03/08','1','1975/10/1。,'4'）SAVETRANSACTIONTRAN1SELECTCOUNT（*）AS職工人數(shù)FROMworkerGOROLLBACKTRANSACTIONSELECTCOUNT（*）AS職工人數(shù)FROMworkerGO6、在factory數(shù)據(jù)庫上，使用T-SQL語句完成如下各題：（1）在worker表中的“部門號”列上創(chuàng)建一個非聚集索引；若該索引已存在，則刪除后重建。CREATENONCLUSTEREDINDEXbmh_indexONworker（部門號）GO（2）在salary表的“職工號”和“日期”列創(chuàng)建聚集索引，并且強制唯一性。CREATEUNIQUECLUSTEREDINDEXzgh_rq_indexONsalary（職工號,日期）GO7、在factory數(shù)據(jù)庫上，使用T-SQL語句完成如下各題：（1）建立視圖view1,查詢所有職工的職工號、姓名、部門名和2004年2月份工資，并按部門名順序排列。USEfactoryGOCREATEVIEWview1ASSELECTworker.職工號,worker.姓名,部門名，工資FROMworker,depart,salaryWHEREworker.部門號=depart.部門號ANDworker.職工號=salary.職工號GO（2）建立視圖view2,查詢所有職工的職工號、姓名和平均工資。USEfactoryGOCREATEVIEWview2ASSELECTworker.職工號,worker.姓名,AVG（工資）AS平均工資FROMworker,salaryGROUPBYworker.職工號,worker.姓名GO（3）建立視圖view3,查詢各部門名和該部門的所有職工平均工資。USEfactoryGOCREATEVIEWview3ASSELECT部門名,AVG(工資)AS平均工資FROMworker,depart,salaryWHEREworker.部門號=depart.部門號ANDworker.職工號=salary.職工號GROUPBY部門名GO(4)顯示視圖view3的定義。USEfactoryGOEXECsp_helpview3GO8、在factory數(shù)據(jù)庫上，使用T-SQL語句完成如下各題：(1)修改worker表的結(jié)構(gòu)，添加“E_mailvarchar(20)”字段。USEfactoryGOALTERTABLEworkerADDE_mailvarchar(20)GO(2)將上例中worker表中“E_mail”字段的數(shù)據(jù)類型改為varchar(30)USEfactoryGOALTERTABLEworkerALTERCOLUMNE_mailvarchar(30)GO(3)刪除worker表中的“E_mail”字段USEfactoryGOALTERTABLEworkerDROPCOLUMNE_mailGO(4)將worker表中姓名為“陳明”的部門號改為“101”。USEfactoryGOUPDATEworkerSET部門號='101'WHERE姓名='陳明'GO(5)刪除工資表salary中姓名為“陳明”的記錄。USEfactoryGODELETEFROMworkerWHERE姓名='陳明'GO(6)實施worker表的“性別”列默認值為“男”的約束。USEfactoryGOALTERTABLEworkerADDCONSTRAINTdefault_xbDEFAULT'男'FOR性別GO(7)實施salary表的“工資”列值限定在0?9999的約束。USEfactoryGOALTERTABLEsalaryADDCONSTRAINTcheck_gzCHECK(工資＞=0AND工資＜=9999)GO(8)實施depart表的“部門號”列值唯一的非聚集索引的約束。USEfactoryGOALTERTABLEdepartADDCONSTRAINTunique_bmhUNIQUENONCLUSTERED(部門號)GO(9)為worker表建立外鍵“部門號”，參考表depart的“部門號”列。USEfactoryGOALTERTABLEworkerADDCONSTRAINTwj_worker_departFOREIGNKEY(部門號)REFERENCESdepart(部門號)GO(10)建立一個規(guī)則sex：@性別='男'OR@性別='女'，將其綁定到worker表的“性別”列上。USEfactoryGOCREATERULEsexAS@性別='男'OR@性別='女'GOEXECsp_bindrule'sex','worker.性別’GO(11)刪除(1)小題所建立的約束。USEfactoryGOALTERTABLEworkerDROPCONSTRAINTdefault_xbGO（12）刪除（2）小題所建立的約束。USEfactoryGOALTERTABLEsalaryDROPCONSTRAINTcheck_gzGO（12）刪除（3）小題所建立的約束。USEfactoryGOALTERTABLEdepartDROPCONSTRAINTunique_bmhG