數(shù)據(jù)庫人事管理系統(tǒng)課程設(shè)計(jì)報(bào)告

1、課程設(shè)計(jì)任務(wù)書設(shè)計(jì)題目：人事管理系統(tǒng)設(shè)計(jì)學(xué)生姓名課程名稱數(shù)據(jù)庫原理課程設(shè)計(jì)設(shè) 計(jì) 內(nèi) 容 及 要 求起止時(shí)間內(nèi)容：學(xué)生根據(jù)本課程設(shè)計(jì)指導(dǎo)書中的題目，進(jìn)行設(shè)計(jì)。(1)設(shè)計(jì)友好的登錄界面，完成用戶身份檢驗(yàn)?？梢蕴峁?duì)各種窗日 的外觀進(jìn)行設(shè)計(jì)，可以提供用戶進(jìn)行外觀選擇。(2)企業(yè)新員工各種信息的輸入、修改、刪除等，包括員工基本信息、 學(xué)歷信息、婚姻狀況、職稱等。(3)數(shù)據(jù)庫信息維護(hù)，即員工各種數(shù)據(jù)信息的備份。(4)對(duì)于轉(zhuǎn)出、辭職、退休員工信息的刪除。(5)按照一定的條件，查詢、統(tǒng)計(jì)符合條件的員工信息；至少應(yīng)該包括每個(gè)員工詳細(xì)信息的查詢、按婚姻狀況查詢、按學(xué)歷查詢、按工作崗位查詢等，至少應(yīng)該包括按學(xué)歷

2、、婚姻狀況、崗位、參加工作時(shí)間等統(tǒng)計(jì)各 自的員工信息；(6)對(duì)查詢、統(tǒng)計(jì)的結(jié)果打印輸出。(7)按照部門將現(xiàn)有員工進(jìn)行分類，查看各個(gè)部門現(xiàn)有員工的平均年齡，根據(jù)各部門工作量的大小，較好地進(jìn)行人事調(diào)度。另外可以幫助領(lǐng)導(dǎo)做出招聘新員工人數(shù)的決定。(8)可供其他人使用幫助。要求：按照數(shù)據(jù)庫系統(tǒng)開發(fā)步驟進(jìn)行數(shù)據(jù)庫概念結(jié)構(gòu)設(shè)計(jì)、邏輯結(jié)構(gòu)設(shè)計(jì)、 物理結(jié)構(gòu)設(shè)計(jì)，使學(xué)生掌握數(shù)據(jù)庫應(yīng)用軟件的開發(fā)流程，SQL語句的使用和 存儲(chǔ)過程的使用。至少5個(gè)表結(jié)構(gòu)；5個(gè)存儲(chǔ)過程；5個(gè)觸發(fā)器；5個(gè)視圖、2處使用事務(wù)處理、3處 使用游標(biāo)過程報(bào)告格式規(guī)范進(jìn) 度要 求19.1 講課閱讀分析任務(wù)書制訂設(shè)計(jì)計(jì)劃19.2 - 3 需求分析概

3、念結(jié)構(gòu)設(shè)計(jì)19.4 - 5概念結(jié)構(gòu)設(shè)計(jì)準(zhǔn)備文擋20.1-3邏輯結(jié)構(gòu)設(shè)計(jì)物理結(jié)構(gòu)設(shè)計(jì)20.4-5寫文擋答辯交設(shè)計(jì)報(bào)告書參 考 資 料1 .雷亮等數(shù)據(jù)庫原理課程設(shè)計(jì)指導(dǎo)書2 .王珊、薩師熠.數(shù)據(jù)庫系統(tǒng)概述（第四版）.北京：高等教育出版社. 2006。3 . C.J.Date著.孟小峰、王珊等譯.數(shù)據(jù)庫系統(tǒng)導(dǎo)論（第8版）.北京：機(jī)械工 業(yè)出版社.2007。4 .陳根才等.數(shù)據(jù)庫課程設(shè)計(jì).浙江.浙江大學(xué)出版社.2007其 它說明1 .本表應(yīng)在每次實(shí)施前一周由負(fù)責(zé)教師填寫二份， 院系審批后交院系辦備案， 一份由 負(fù)責(zé)教師留用。2 .若填寫內(nèi)容較多可另紙附后。3.一題多名學(xué)生共用的，在設(shè)計(jì)內(nèi)容、 參數(shù)、要

4、求等方面應(yīng)有所區(qū)別。教研室主任：指導(dǎo)教師:摘要在現(xiàn)代化的社會(huì)中，無論是大型企業(yè)還是中小型企業(yè)，都必不可少地會(huì)涉及 到人事管理的問題。一個(gè)完善的人事管理系統(tǒng)對(duì)企業(yè)的發(fā)展有著至關(guān)重要的作 用。人事管理系統(tǒng)分為6個(gè)模塊，基本信息管理模塊、退休員工信息管理模塊、部 門信息管理模塊、學(xué)歷信息管理模塊、家庭信息管理模塊和職稱信息管理模。本 系統(tǒng)采用SQL Server 2008作為數(shù)據(jù)庫。在實(shí)現(xiàn)數(shù)據(jù)庫部分功能時(shí)要求寫出存儲(chǔ) 過程、觸發(fā)器和視圖。關(guān)鍵詞：人事管理SQL Server 2008觸發(fā)器存儲(chǔ)過程視圖事務(wù)處理游標(biāo)II目錄摘要 II1需求分析 11.1 系統(tǒng)目標(biāo)設(shè)計(jì) 11.2 系統(tǒng)功能分析 11.3

5、功能模塊分析 21.4 系統(tǒng)全局?jǐn)?shù)據(jù)流圖 21.5 數(shù)據(jù)字典 32概念結(jié)構(gòu)設(shè)計(jì) 62.1 構(gòu)成系統(tǒng)的實(shí)體型 62.2 人事管理系統(tǒng)E-R圖 73邏輯結(jié)構(gòu)設(shè)計(jì) 83.1 邏輯結(jié)構(gòu)簡介 83.2 E-R圖向關(guān)系模型的轉(zhuǎn)換 84物理模型設(shè)計(jì) 94.1 定義數(shù)據(jù)庫 94.2 創(chuàng)建存儲(chǔ)過程 94.3 創(chuàng)建觸發(fā)器 144.4 創(chuàng)建視圖 174.5 創(chuàng)建游標(biāo) 194.6 創(chuàng)建事物處理 21總結(jié) 22致謝 23參考文獻(xiàn) 24iii1需求分析1.1 系統(tǒng)目標(biāo)設(shè)計(jì)使企業(yè)可以實(shí)現(xiàn)規(guī)范化的管理。推動(dòng)企業(yè)勞動(dòng)人事管理走向科學(xué)化、現(xiàn)代化，適應(yīng)現(xiàn)代企業(yè)制度的要求。使管理員可以很簡單地完成日常的管理工作，例如新員工的加入、老

6、員工的退 休等。支持企業(yè)進(jìn)行勞動(dòng)人事管理及其相關(guān)方面的科學(xué)決策，如企業(yè)領(lǐng)導(dǎo)根據(jù)現(xiàn)有職 工的數(shù)目決定招聘的人數(shù)等。1.2 系統(tǒng)功能分析根據(jù)現(xiàn)代企業(yè)的情況，本系統(tǒng)要求應(yīng)能完成以下主要功能：設(shè)計(jì)友好的登錄界面，完成用戶身份檢驗(yàn)。可以提供對(duì)各種窗口的外觀進(jìn)行設(shè) 計(jì)，可以提供用戶進(jìn)行外觀選擇。企業(yè)新員工各種信息的輸入、修改、刪除等，包括員工基本信息、學(xué)歷信息、 蠟姻狀況、職稱等。數(shù)據(jù)庫信息維護(hù)，即員工各種數(shù)據(jù)信息的備份。對(duì)于轉(zhuǎn)出、辭職、退休員工信息的刪除。按照一定的條件，查詢、統(tǒng)計(jì)符合條件的員工信息；至少應(yīng)該包括每個(gè)員工詳 細(xì)信息的查詢、按蠟姻狀況查詢、按學(xué)歷查詢、按工作崗位查詢等，至少應(yīng)該包括按學(xué) 歷

7、、焙姻狀況、崗位、參加工作時(shí)間等統(tǒng)計(jì)各自的員工信息；對(duì)查詢、統(tǒng)計(jì)的結(jié)果打印輸出。按照部門將現(xiàn)有員工進(jìn)行分類，查看各個(gè)部門現(xiàn)有員工的平均年齡，根據(jù)各部 門工作量的大小，較好地進(jìn)行人事調(diào)度。另外可以幫助領(lǐng)導(dǎo)作出招聘新員工人數(shù)的決定可供其他人使用幫助。1.3 功能模塊分析根據(jù)系統(tǒng)功能的基本要求，可對(duì)整個(gè)系統(tǒng)劃分為幾個(gè)模塊人事管理系統(tǒng)圖1.1功能模塊分析1.4 系統(tǒng)全局?jǐn)?shù)據(jù)流圖系統(tǒng)的全局?jǐn)?shù)據(jù)流圖，也稱第一層數(shù)據(jù)流圖，是從整體上描述系統(tǒng)的數(shù)據(jù)流，反映 系統(tǒng)數(shù)據(jù)的整體流向，給設(shè)計(jì)者、開發(fā)者和用戶一個(gè)總體描述。更新人事病理數(shù)據(jù)庫查詢1.5 數(shù)據(jù)字典表1.1員工基本信息表(worker_info)名稱數(shù)據(jù)類型

8、說明Worker_novarchar(15)r員工編號(hào)關(guān)鍵字Worker_namevarchar(8)員工姓名Sexvarchar(2)性別Birthdaydate廠出生日期Agesmallint年齡IDvarchar(18)身份證號(hào)Department_namevarchar(15)部門Postvarchar(8)職位Phonevarchar(11)電話Work_datedate工作時(shí)間Worker_ typevarchar(8)員工類型表1.2離退休員工(tui_info)名稱數(shù)據(jù)類型說明Worker_novarchar(15)員工編號(hào)關(guān)鍵字Worker_namevarchar(8)員工姓

9、名Sexvarchar(2)性別Birthdaydate出生日期Agesmallint年齡IDvarchar(18)身份證號(hào)Department_namevarchar(15)部門Postvarchar(8)職位Phonevarchar(11)電話Work_datedate工作時(shí)間表1.3部門基本信息表(Department_info )名稱數(shù)據(jù)類型說明Department_noVarchar(15)部門編號(hào)關(guān)鍵字Department_namevarchar(15)部門名稱manager_novarchar(15)經(jīng)理編號(hào)WorkeloadFloat(2)工作量（%Real_numberInt

10、現(xiàn)人數(shù)Need_numberInt所需人數(shù)表 1.4 學(xué)歷信息(degree_info)名稱數(shù)據(jù)類型說明Degree_noVarchar(15)學(xué)歷編號(hào)關(guān)鍵字Worker_novarchar(15)員工編號(hào)Degreevarchar(4)學(xué)位MajorVarchar(8)專業(yè)Schoolvarchar(25)學(xué)校Graduate_datedate畢業(yè)時(shí)間表1.5 家庭信息（home_info）名稱數(shù)據(jù)類型說明Home_noVarchar(15)家庭編號(hào)關(guān)鍵字Worker_novarchar(15)員工編號(hào)Marriage_infovarchar(4)婚姻狀況Home_numbersmallin

11、t家庭人數(shù)Home_phonevarchar(8)家庭電話Home_addressvarchar(50)家庭住址表1.6職稱信息（Pro_info）名稱數(shù)據(jù)類型說明Pro_noVarchar(15)職稱編號(hào)關(guān)鍵字Worker_novarchar(15)員工編號(hào)Assess_datedatetime評(píng)定日期Assess_unitvarchar(20)評(píng)定單位Assess_titlevarchar(8)評(píng)定職稱Remarkvarchar(200)備注222概念結(jié)構(gòu)設(shè)計(jì)2.1構(gòu)成系統(tǒng)的實(shí)體型由人事管理系統(tǒng)的數(shù)據(jù)流圖和數(shù)據(jù)字典，抽取出系統(tǒng)的6個(gè)主要實(shí)體，包括：員工、部門、學(xué)歷、家庭、職稱評(píng)定信息、賬號(hào)

12、。員工 實(shí)體型屬性： Worker_no, Worker_name, Sex, Birthday, Age, ID, Department_name, Post, Phone, Work_date, Worker_ type退休員工實(shí)體型屬性：Worker_no, Worker_name, Sex, Birthday, Age, ID, Department_name, Post, Phone, Work_date, Worker_ type , tui_date部門實(shí)體型屬性：Department。，Department_name, manager_no, Workeload,Real_nu

13、mber, Need_number學(xué)歷實(shí)體型屬性：Degree_no, Worker_no, Degree, Major, School, Graduate_date 家庭實(shí)體型屬性：Home_noWorker_no, Marriage_info , Home_numberHome_phone Home_address職稱評(píng) 定信息 實(shí)體型 屬性：Pro_no, Worker_no, Assess_date, Assess_unit, Assess_title, Remark2.2人事管理系統(tǒng)E-R圖SckiRcla 距MajorSchoddegree_inf0弋 Degree-WoiuEB1

14、 r»worker_mfodepartmen Jirif uq Dapi«rlrn«fTt_r>9Wcrki|r_nflni4DepartEgE：AmeMAhigyioRj&a numberNeed nuriTiberCOErtl也IDOflpMtTTW<_nMTM-PostPtoraWcrk_iiirtsWorkjef_jype圖2.1人事管理系統(tǒng)E-R圖tuijnf。牙 Workef_w Warkjar_nffn« Sex B<ihdj>r * IDDMAWKjiWH*PgsPhaneWgrkjhte Ty i ckr

15、te3邏輯結(jié)構(gòu)設(shè)計(jì)3.1邏輯結(jié)構(gòu)簡介邏輯結(jié)構(gòu)設(shè)計(jì)就是把概念結(jié)構(gòu)設(shè)計(jì)階段設(shè)計(jì)好的基本E-R圖轉(zhuǎn)換為與選用DBMS"品所支持的數(shù)據(jù)模型相符合的邏輯結(jié)構(gòu)。設(shè)計(jì)邏輯結(jié)構(gòu)一般分為3步進(jìn)行：(1)將概念結(jié)構(gòu)轉(zhuǎn)換為一般的關(guān)系、網(wǎng)狀、層次模型；(2)將轉(zhuǎn)換來的關(guān)系、網(wǎng)狀、層次模型向特定 DBM豉持下的數(shù)據(jù)模型轉(zhuǎn)換；(3)對(duì)數(shù)據(jù)模型進(jìn)行優(yōu)化。3.2 E-R圖向關(guān)系模型的轉(zhuǎn)換將E-R圖轉(zhuǎn)換為關(guān)系模型實(shí)際上就是要將實(shí)體型、實(shí)體的屬性和實(shí)體型間的聯(lián)系轉(zhuǎn) 換為關(guān)系模式，這種轉(zhuǎn)換一般遵循如下原則：一個(gè)實(shí)體型轉(zhuǎn)換為一個(gè)關(guān)系模式。實(shí)體的 屬性就是關(guān)系的屬性，實(shí)體的碼就是關(guān)系的碼。對(duì)于實(shí)體間的聯(lián)系則有以下不同的情況：

16、(1) 一個(gè)1: 1聯(lián)系可以轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系模式，也可以與任意一斷對(duì)應(yīng)的關(guān) 系模式合并。如果轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系模式，則與該聯(lián)系相連的試題的碼以及聯(lián)系本 身的屬性均轉(zhuǎn)換為關(guān)系的屬性，每個(gè)實(shí)體的碼均為該關(guān)系的候選碼。如果與某一端實(shí)體 對(duì)應(yīng)的關(guān)系模式合并，則需要在該關(guān)系模式的屬性中加入另一個(gè)關(guān)系模式的碼和聯(lián)系本 身的屬性。(2) 一個(gè)1: n聯(lián)系可以轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系模式，也可以與 n端對(duì)應(yīng)的關(guān)系模 式合并。如果轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系模式，則與該聯(lián)系相連的各實(shí)體的碼以及聯(lián)系本身 的屬性均轉(zhuǎn)換為關(guān)系的屬性，而關(guān)系的碼為 n端實(shí)體的碼。(3) 一個(gè)m n聯(lián)系轉(zhuǎn)換為一個(gè)關(guān)系模式。與該聯(lián)系相連的餓個(gè)實(shí)

17、體的碼以及聯(lián)系 本身的屬性均轉(zhuǎn)換為關(guān)系的屬性，個(gè)實(shí)體的碼組成關(guān)系的碼或關(guān)系碼的一部分。(4) 3個(gè)或3個(gè)以上實(shí)體間的一個(gè)多元聯(lián)系可以轉(zhuǎn)換為一個(gè)關(guān)系模式。 與該多元聯(lián) 系相連的各實(shí)體的碼以及聯(lián)系本身的屬性均轉(zhuǎn)換為關(guān)系的屬性， 各實(shí)體的碼組成關(guān)系的 碼或關(guān)系碼的一部分。(5)具有相同碼的關(guān)系模式可合并4物理模型設(shè)計(jì)數(shù)據(jù)庫物理設(shè)計(jì)是將邏輯設(shè)計(jì)影射到存儲(chǔ)介質(zhì)上，利用可用的硬件和軟件功能盡可 能快地對(duì)數(shù)據(jù)進(jìn)行物理訪問和維護(hù)。4.1 定義數(shù)據(jù)庫SQL Server 2008數(shù)據(jù)庫文件分為3中類型：主數(shù)據(jù)文件、次數(shù)據(jù)文件和日志文件 通過SQL語言進(jìn)行數(shù)據(jù)庫創(chuàng)建，創(chuàng)建數(shù)據(jù)庫的語句如下：Create datab

18、ase 人事管理系統(tǒng)；4.2 創(chuàng)建存儲(chǔ)過程4.3 . 1在員工基本信息表，家庭信息表，學(xué)歷信息表，職稱評(píng)定表，創(chuàng)建一個(gè)插入員工信息的存儲(chǔ)過程：create procedure insertworkerWorker_no varchar(15),- 工號(hào)Worker_name varchar(8),- 姓名Sex varchar(2),-性另1JBirthday date ,-出生年月IDvarchar(18),-身份證號(hào)Department_name varchar(15),- 部門postvarchar(8),-職位phone varchar(11),-電話work_date date ,-

19、工作時(shí)間Worker_type varchar(8)，-員工類型Degree_no varchar(15) ,-學(xué)位號(hào)Degreevarchar(4),-學(xué)位Majorvarchar(25),-專業(yè)Schoolvarchar(25),-畢業(yè)學(xué)校Gradute_date date，一畢業(yè)時(shí)間Home_no varchar(15),-家庭編號(hào)Marriage_info varchar(4),-蠟姻狀況Home_number smallint ,-家庭人口Home_phone varchar(11),-家庭電話Home_address varchar(50),-家庭地址Pro_no varchar(

20、15) ,-職稱編號(hào)Assess_date date ,-評(píng)定日期Assess_unit varchar(20),-評(píng)定單位Assess_title varchar(8),- 評(píng)定職稱Remark varchar(200)-備注as-插入員工信息 insert intoworker_info(Worker_no,Worker_name,Sex,Birthday,age,ID,Department_name,post, phone,work_date,Worker_type) values (Worker_no,Worker_name,Sex,Birthday,FLOOR(datediff(DY

21、,Birthday,getdate ()/365.25),ID,Department_name,post,phone,work_date,Worker_type) -插入職稱信息一一一insert into Pro_info(Pro_no,Worker_no,Assess_date,Assess_unit,Assess_title,Remark) values (Pro_no,Worker_no,Assess_date,Assess_unit,Assess_title,Remark) -插入家庭信息 insert into home_info(Home_no,worker_no,Marriag

22、e_info,Home_number,Home_phone,Home_addre ss) values (Home_no,worker_no,Marriage_info,Home_number,Home_phone,Home_address)-插入學(xué)歷信息 insert intodegree_info(Degree_no,Worker_no,Degree,Major,School,Gradute_date) values (Degree_no,Worker_no,Degree,Major,School,Gradute_date) 4.2 . 2在部門基本信息表中創(chuàng)建插入部門信息的存儲(chǔ)過程：cr

23、eate procedure add_department Department_no varchar(15), Department_name varchar(15), Manager_novarchar(15),Workeload float(2), Real_numbersmallint ,Need_number smallint as insert into department_info(Department_no,Department_name,Manager_no,Workeload,Real_num ber,Need_number) values(Department_no,D

24、epartment_name,Manager_no,Workeload,Real_number,Need_ number) 4.2 . 3在部門基本信息表中創(chuàng)建更新部門信息的存儲(chǔ)過程：create procedure update_department Department_name varchar(15),Manager_no varchar(15), Workeload float(2), Need_number smallint as update department_info set Manager_no=Manager_no, Workeload=Workeload, Need_n

25、umber=Need_number where Department_name=Department_name;4.2 . 4創(chuàng)建當(dāng)一個(gè)員工辭職時(shí)刪除該信息，當(dāng)一個(gè)員工退休時(shí)，將基本信息復(fù)制到退休表 中，其他信息刪除的存儲(chǔ)過程CREATE PROCEDURE worker_type worker_no varchar(15), worker_type varchar(4) AS BEGIN SET NOCOUNT ON; IF worker_type='辭職' BEGIN delete from worker_info where worker_no=worker_no END

26、 IF worker_type='退休' BEGIN INSERT INTOtui_info(Worker_no,Worker_name,Sex,Birthday,Age,ID,Department_name,Post,Pho ne,Work_date,Tui_date) SELECTWorker_no,Worker_name,Sex,Birthday,FLOOR(datediff(DY,Birthday,getdate()/36 5.25),ID,Department_name,Post,Phone,Work_date,GETDATE()FROM worker_info WH

27、ERE Worker_no=Worker_no;DELETE FROM worker_info WHERE Worker_no=Worker_no; END END 4.2 . 5創(chuàng)建更新家庭信息表、學(xué)歷信息、職稱信息的存儲(chǔ)過程create procedure update_homeWorker_no varchar(15),-工號(hào)Marriage_info varchar(4),-蠟姻狀況Home_number smallint ,-家庭人口Home_phone varchar(11),-家庭電話Home_address varchar(50)-家庭地址as update home_info

28、 set Marriage_info=Marriage_info, Home_number=Home_number, Home_phone=Home_phone, Home_address=Home_address where Worker_no=Worker_no;-更新學(xué)歷信息create procedure update_degree Worker_no varchar(15),- 工號(hào) Degreevarchar(4),-學(xué)位Majorvarchar(25),-專業(yè)Schoolvarchar(25),-畢業(yè)學(xué)校Gradute_date date -畢業(yè)時(shí)間asupdate degree

29、_info set Degree=Degree, Major=Major, School=School, Gradute_date=Gradute_date where Worker_no=Worker_no;-更新職稱信息create procedure update_proWorker_no varchar(15),- 工號(hào) Assess_date date ,-評(píng)定日期Assess_unit varchar(20),-評(píng)定單位Assess_title varchar(8)-評(píng)定職稱asupdate pro_info set Assess_date=Assess_date, Assess_

30、unit=Assess_unit, Assess_title=Assess_title where Worker_no=Worker_no;4.2 . 6創(chuàng)建某部門平均年齡的存儲(chǔ)過程create procedure avg_age Department_name varchar(15)asselect AVG(Age)from worker_info where Department_name=Department_name4.2 . 7按學(xué)歷查詢-學(xué)歷查詢create procedure search_degreename varchar(4) asbegindeclare search_d

31、egree cursor for select worker_no from degree_infowhere worker_no in(select Worker_no from degree_info where Degree=name) open search_degreedeclare no1 varchar(15)fetch next from search_degree into no1while FETCH_STATUS = 0beginselectworker_info.Worker_name,worker_info.Department_name,home_info.Marr

32、iage_info,degree_info.Major,degree_info.degree,worker_info.Work_datefrom worker_info,degree_info,pro_info,home_info whereworker_info.Worker_no=no1 and home_info.Worker_no=no1and pro_info.Worker_no=no1 and degree_info.Worker_no=no1fetch next from search_degree into no1endclose search_degreedeallocate

33、 search_degree end4.2 . 7按部門查詢create procedure search_departmentname varchar(15) asbegindeclare search_department cursor for select worker_no from worker_infowhere Department_name=nameopen search_departmentdeclare no1 varchar(15)fetch next from search_department into no1while FETCH_STATUS = 0beginse

34、lectworker_info.Worker_name,worker_info.Department_name,home_info.Marriage_info ,degree_info.Major,worker_info.Work_datefrom worker_info,degree_info,pro_info,home_info whereworker_info.Worker_no=no1 and home_info.Worker_no=no1and pro_info.Worker_no=no1 and degree_info.Worker_no=no1fetch next from se

35、arch_department into no1 endclose search_departmentdeallocate search_department end4.2 . 9按蠟姻狀況查詢create procedure search_marriage name varchar(15)as begindeclare search_marriage cursor for select worker_no from home_infowhere worker_no in(select Worker_no from home_info where marriage_info=name) ope

36、n search_marriage declare no varchar(15)fetch next from search_marriage into nowhile FETCH_STATUS = 0beginselect * from worker_info,degree_info,pro_info,home_info where worker_info.Worker_no=no and home_info.Worker_no=no and pro_info.Worker_no=no and degree_info.Worker_no=no fetch next from search_m

37、arriage into no endclose search_marriagedeallocate search_marriage end4.3創(chuàng)建觸發(fā)器4.3 . 1定義一個(gè)觸發(fā)器，當(dāng)在數(shù)據(jù)庫中插入一條員工信息后，觸發(fā)相應(yīng)的部門人數(shù)加1create trigger insert_worker on worker_info after insert as beginupdate department_info setdepartment_info.Real_number = department_info.Real_number + 1 fromdepartment_info as depa

38、rtment_info,inserted as worker_infowhere department_info.Department_name=worker_info.Department_name end;4.3 . 2定義一個(gè)觸發(fā)器，當(dāng)在數(shù)據(jù)庫中刪除一條員工信息后，觸發(fā)相應(yīng)的部門人數(shù)減1create trigger delete_workeron worker_infoafter deleteas beginupdate department_infosetdepartment_info.Real_number = department_info.Real_number - 1fromd

39、epartment_info as department_info,deleted as worker_infowhere department_info.Department_name=worker_info.Department_name end;delete from worker_info where worker_no='j1'4.3 . 3定義一個(gè)觸發(fā)器，當(dāng)在數(shù)據(jù)庫中員工所屬的部門更改后，觸發(fā)轉(zhuǎn)出的部門人數(shù)減1,轉(zhuǎn)入的部門人數(shù)加1create trigger update_workeron worker_infoafter updateas IF(UPDATE(De

40、partment_name)begin-轉(zhuǎn)出的部門人數(shù)減update department_infosetdepartment_info.Real_number = department_info.Real_number - 1fromdepartment_info as department_info,deleted as worker_infowhere department_info.Department_name=worker_info.Department_name-轉(zhuǎn)入的部門人藪加update department_infosetdepartment_info.Real_numbe

41、r = department_info.Real_number + 1fromdepartment_info as department_info,inserted as worker_infowhere department_info.Department_name=worker_info.Department_name end;4.3 . 4定義一個(gè)觸發(fā)器，當(dāng)在數(shù)據(jù)庫中刪除一個(gè)員工的基本信息時(shí)連帶刪除該員工的職稱學(xué)歷，家庭信息create TRIGGER del_workerON worker_infofor DELETEASBEGINdelete home_infoFromhome_in

42、fo as home_info , deleted as worker_infoWhere home_info.worker_no= worker_info.worker_noENDBEGINdelete degree_infoFrom degree_info as degree_info , deleted as worker_infoWhere degreeinfo.workerno=workerinfo.workernoENDBEGINdelete pro_infoFrom pro_info as pro_info , deleted as worker_infoWhere pro_in

43、fo.worker_no=worker_info.worker_noEND4.3 . 5創(chuàng)建一個(gè)觸發(fā)器，檢查部門員工是否已滿，滿人則不進(jìn)行操作create Trigger check_departmenton department_infofor update asIF(UPDATE(Real_number)begindeclaresize smallint,MaxSize smallint,Department_name varchar(15)select department_name =department_name from insertedselect MaxSize =Need_n

44、umber ,size = Real_number from department_info where department_name = department_namebeginif( size > MaxSize )beginprint '該部門員工已滿rollback Transactionreturn ;endendend4.3 . 6創(chuàng)建一個(gè)觸發(fā)器，插入或更新時(shí)檢查部門是否存在，不存在則不進(jìn)行操作create trigger check_dapartment_nameon worker_infofor update,insertasbegindeclaredepart

45、ment_name varchar(15)select department_name = department_name from insertedif( not exists(select * from department_info where department_name =department_name )beginprint '該部門不存在rollback Transactionreturn;endend4.4創(chuàng)建視圖4.4 . 1建立一個(gè)查看所有員工的所有信息的視圖create view all_worker(員工編號(hào)，姓名，性別，出生日期，年齡，身份證號(hào)，部門編號(hào)，部

46、門，經(jīng)理編號(hào),訕位，電話,工作時(shí)間，員工類型，學(xué)歷號(hào)，學(xué)位，專業(yè),畢業(yè)學(xué)校，畢業(yè)時(shí)間，職稱號(hào)，評(píng)定時(shí)間，評(píng)定單位，評(píng)定職稱，備注，家庭編號(hào)，焙姻 狀況,家庭人口，家庭電話，家庭地址)asselectworkerJnfo.Worker_no,worker_info.Worker_name,worker_info.Sex,workerJnfo.B irthday,worker_info.Age,worker_info.ID, department_info.Department_no,worker_info.Department_name,department_info.M anager_no,w

47、orker_info.Post,worker_info.Phone,workerJnfo.Work_date,workerJ nfo.Worker_type,degree_info.Degree_no,degree_info.Degree,degree_info.Major,degreeJnfo.School,degreeJnfo.Gradute _date,pro_info.Pro_no,pro_info.Assess_date,pro_info.Assess_unit, pro_info.Assess_title,pro_info.Remark,homeJnfo.Home_no,homeJ

48、nfo.Marriage_info,home_info.Home_number,home_info.Home_phone,homeJnfo.Home_address from worker_info,degree_info,pro_info,home_info,department_info where workerinfo.Workerno=degreeinfo.Workerno and worker_info.Worker_no=pro_info.Worker_noand worker_info.Worker_no=home_info.Worker_no anddepartment_inf

49、o.Department_name=worker_info.Department_name with check option4.4 . 2建立一個(gè)用于查看員工常用信息的視圖create view search asselect worker_info.Worker_no ' 員工編號(hào)',worker_info.Worker_name '員工姓名',worker_info.Sex '性另 U',workerJnfo.Age'年齡',worker_info.Department_name '部門,，_worker_info.

50、Post ' 職位',degree_info.Degree' 學(xué)位',home_info.Marriage_info ' 女昏 姻狀況',worker_info.Work_date ' 工作時(shí)間 from worker_info,degree_info,home_info,department_info where workerinfo.Workerno=degreeinfo.Workerno and worker_info.Worker_no=home_info.Worker_no with check option 4.4 . 3查詢

51、員工學(xué)歷視圖create view show_degree（員工編號(hào)，姓名，性別，學(xué)歷號(hào)，學(xué)位，專業(yè)，畢業(yè)學(xué)校，畢業(yè)時(shí) 問）asselect worker_info.Worker_no,workerJnfo.Worker_name,worker_info.Sex, degree_info.Degree_no,degree_info.Degree, degree_info.Major,degree_info.School,degree_info.Gradute_date from worker_info,dergee_infowhere workerinfo.Workerno=degreeinf

52、o.Workerno with check option4.4 . 4查詢部門工作量create view show_load（ 員工編號(hào)，員工姓名，部門，工作量） asselectworker_info.worker_no,worker_info.worker_name,worker_info.Department_name,d epartment_info.Workeloadfrom department_info,worker_info wheredepartment_info.department_name=worker_info.department_nameselect * fro

53、m show_loadwith check option4.4 . 5查詢員工職稱create view show_degree(員工編號(hào)，姓名，性別，職稱號(hào)，評(píng)定時(shí)間，評(píng)定單位，評(píng)定職稱， 備注)asselectworkerJnfo.Worker_no,worker_info.Worker_name,workerJnfo.Sex,pro_info.Pro_no,pro_info.Assess_date,pro_info.Assess_unit,pro_info.Assess_title,pro_info.Remark from worker_info,pro_info,home_infowh

54、ere worker_info.Worker_no=pro_info.Worker_no4.4 . 6查詢員工學(xué)歷create view show_degree(員工編號(hào)，姓名，性別，學(xué)歷號(hào)，學(xué)位，專業(yè)，畢業(yè)學(xué)校，畢業(yè)時(shí) 問)asselectworker_info.Worker_no,worker_info.Worker_name,worker_info.Sex,degree_info.Degree_no,degree_info.Degree,degree_info.Major,degree_info.School,degree_info.Gradute_datefrom worker_info,degree_info whereworkerinfo.Workerno=degreeinfo.Workernowith check option4.5創(chuàng)建游標(biāo)4.5 . 1創(chuàng)建查詢未焙員工的游標(biāo)declare search_marriage1 cursor for select worker_no from home_infowhere worker_no in(select Worker_no from home_info where marriage_info='未女昏')一一一一open search_marriagedeclare no varchar