1、關(guān)系數(shù)據(jù)庫(kù)原理及應(yīng)用課程設(shè)計(jì)實(shí)驗(yàn)報(bào)告一、實(shí)驗(yàn)思路1. 系統(tǒng)需求分析通過(guò)對(duì)某公司的實(shí)地調(diào)查， 了解到該公司的人力資源管理仍停留在完全的人工管理階段，沒(méi)有標(biāo)準(zhǔn)的人事管理存檔信息。這種人工管理方式費(fèi)時(shí)、費(fèi)事、費(fèi)力，導(dǎo)致工作效率底下。 開(kāi)發(fā)出合適的人力資源管理系統(tǒng)， 可方便人力資源的管理， 提高該公司管理的效率。I. 需求分析階段的目標(biāo)(1) 了解目前企業(yè)人力資源管理現(xiàn)狀以及SQL2000的功能和特點(diǎn)。(2) 通過(guò)實(shí)地調(diào)查和問(wèn)答 - 記錄的方式了解企業(yè)人力資源管理的業(yè)務(wù)流程， 并記錄和處理相關(guān)數(shù)據(jù)。(3) 與指導(dǎo)教師交流個(gè)人想法，改正不合理的地方，為下面的概念設(shè)計(jì)和邏輯設(shè)計(jì)奠定基礎(chǔ)。II. 需求分析階

2、段的任務(wù)(1) 處理對(duì)象1. 員工基本信息 (employee): 包括員工號(hào)， 姓名， 性別，年齡，學(xué)歷，住址， 電話(huà)，出生年月，部門(mén)編號(hào)，職位編號(hào)，2. 員工工資信息 (salary,basicsalary,additional): 包括員工號(hào)，基本工資，補(bǔ)貼，應(yīng)發(fā)工資，以及所交稅。3. 公司部門(mén)職務(wù)信息 (position,department): 包括員工號(hào)，員工的職位和所屬部門(mén)。III. 數(shù)據(jù)字典(1) 數(shù)據(jù)項(xiàng)系統(tǒng)涉及的數(shù)據(jù)有項(xiàng)。見(jiàn)下表。數(shù)據(jù)項(xiàng)目編號(hào)數(shù)據(jù)項(xiàng)名數(shù)據(jù)項(xiàng)含義存儲(chǔ)結(jié)構(gòu)別名DI-1addid補(bǔ)貼編號(hào)Char(5)補(bǔ)貼號(hào)DI-2addmoney補(bǔ)貼金額Float補(bǔ)貼DI-3bas

3、icid基本工資編號(hào)Char(5)基本工資號(hào)DI-4basicsal員工基本工資Float基本工資DI-5dno部門(mén)編號(hào)Char(5)部門(mén)號(hào)DI-6dname部門(mén)名稱(chēng)Char(10)部門(mén)名DI-7eno員工編號(hào)Char(5)員工號(hào)DI-8ename員工姓名Char(10)姓名DI-9sex員工性別Char(2)性別DI-10age員工年齡Int年齡DI-11edu員工學(xué)歷Char(10)學(xué)歷DI-12address員工家庭住址Varchar(50)住址DI-13tel員工電話(huà)Char(20)電話(huà)DI-14birth員工出生年月Datetime生日DI-15pno部門(mén)編號(hào)Char(5)部門(mén)號(hào)DI

4、-16pname部門(mén)名稱(chēng)Char(10)部門(mén)名DI-17salary員工應(yīng)發(fā)工資Float應(yīng)發(fā)工資DI-18realsal員工實(shí)發(fā)工資Float實(shí)發(fā)工資DI-19tax員工上繳稅率Float稅率(2) 數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu)見(jiàn)下表：數(shù)據(jù)結(jié)構(gòu)編號(hào)數(shù)據(jù)結(jié)構(gòu)名數(shù)據(jù)結(jié)構(gòu)含組成義DS-1employee員工基本信eno,ename,sex,age,edu,息address,tel,birth,dno,pnoDS-2position職位信息pno,pnameDS-3department部門(mén)信息dno,dnameDS-4salary員工工資信eno,salary,realsal,addid,息basicid,ta

5、xDS-5additional補(bǔ)貼信息addid,addmoneyDS-6basicsalary基本工資信basicid,basicsal息二、實(shí)驗(yàn)過(guò)程1. 概念設(shè)計(jì)階段I. 概念模型設(shè)計(jì)(1)根據(jù)不同對(duì)象，分別畫(huà)出其E-R 圖。各實(shí)體間關(guān)系的E-R 圖，如下圖所示：?jiǎn)T工擁有工資各實(shí)體間關(guān)系的E-R 圖員工基本信息的E-R 圖，如下圖所示：姓名員 工性別年齡職位員工部門(mén)學(xué)歷生日住址電話(huà)員工基本信息的E-R 圖工資信息的E-R 圖，如下圖所示：基本工資號(hào)員工號(hào)應(yīng)發(fā)工資工資補(bǔ)貼號(hào)稅應(yīng)發(fā)工資工資信息的 E-R 圖職務(wù)信息和部門(mén)信息的E-R 圖：職務(wù)號(hào)部門(mén)編號(hào)職務(wù)信息部門(mén)信息職務(wù)名稱(chēng)部門(mén)名稱(chēng)職務(wù)信息、

6、部門(mén)信息的E-R 圖補(bǔ)貼信息和基本工資信息的E-R 圖：基本工資號(hào)補(bǔ)貼編號(hào)基本工資信息補(bǔ)貼信息基本工資補(bǔ)貼補(bǔ)貼信息、基本工資信息的E-R 圖(2) 各 E-R 圖中每個(gè)實(shí)體的屬性如下：?jiǎn)T工： employee(eno,ename,sex,age,edu,address,tel,birth,dno,pno)。職位： position(pno,pname)部門(mén)： department(dno,dname)員工工資： salary(eno,salary,realsal,addid,basicid,tax)補(bǔ)貼： additional(addid,addmoney)基本工資： basicsalary(

7、basicid,basicsal)(3)合并各 E-R 圖，消除屬性沖突，命名沖突，結(jié)構(gòu)沖突等3 類(lèi)沖突，得到初步E-R 圖，再消除不必要的冗余，得到基本E-R 圖，如下所示：部門(mén)補(bǔ)貼屬于屬于職位擁有員工擁有工資屬于基本工資人力資源管理基本E-R 圖II. 新系統(tǒng)流程新系統(tǒng)流程圖，如下圖所示：用戶(hù)發(fā)出查詢(xún)內(nèi)容發(fā)出修改信息判斷查詢(xún)內(nèi)容判斷修改內(nèi)容在員工基本信息中處理在員工工資信息中處理在員工基本信息中處理在員工工資信息中處理顯示查詢(xún)結(jié)果存儲(chǔ)修改數(shù)據(jù)2. 邏輯設(shè)計(jì)階段I. 將 E-R 圖轉(zhuǎn)換為關(guān)系模型員工： employee(eno,ename,sex,age,edu,address,tel,bi

8、rth,dno,pno)。職位： position(pno,pname)部門(mén)： department(dno,dname)員工工資： salary(eno,salary,realsal,addid,basicid,tax)補(bǔ)貼： additional(addid,addmoney)基本工資： basicsalary(basicid,basicsal)II. 數(shù)據(jù)庫(kù)模式定義(1) 員工 (employee) 基本信息表 , 用于記錄員工的基本信息。員工基本信息表屬性名數(shù)據(jù)類(lèi)型取值范圍是否是主屬性完整性或外鍵enoChar(5)0000099999是NOT NULLenameChar(10)否NO

9、T NULLsexChar(2)男、女否NOT NULLageInt0100否NOT NULLeduChar(10)否NOT NULLaddressVarchar(50)否NULLtelChar(20)否NULLbirthDatetime1900-1-19999-12-31否NULLdnoChar(5)0000099999是NOT NULLpnoChar(5)0000099999是NOT NULL(2) 補(bǔ)貼 (additional)信息表，記錄不同等級(jí)的補(bǔ)貼錢(qián)數(shù)。補(bǔ)貼信息表屬性名數(shù)據(jù)類(lèi)型取值范圍是否是主屬性或完整性外鍵addidChar(5)0000099999是NOT NULLaddmon

10、eyFloat0.099999否NOT NULL(3) 基本工資 (basicsalary) 信息表，記錄不同等級(jí)的基本工資錢(qián)數(shù)。基本工資信息表屬性名數(shù)據(jù)類(lèi)型取值范圍是否是主屬性或完整性外鍵basicidChar(5)0000099999是NOT NULLbasicsalFloat0.099999否NOT NULL(4) 部門(mén) (department) 信息表，記錄部門(mén)名稱(chēng)。部門(mén)信息表屬性名數(shù)據(jù)類(lèi)型取值范圍是否是主屬性或完整性外鍵dnoChar(5)0000099999是NOT NULLdnameChar(10)否NOT NULL(5) 職位 (position) 信息表，記錄職位名稱(chēng)。職位信

11、息表屬性名數(shù)據(jù)類(lèi)型取值范圍是否是主屬性或完整性外鍵pnoChar(5)0000099999是NOT NULLpnameChar(10)否NOT NULL(6) 員工工資 (salary) 信息表，記錄員工的工資情況。員工工資信息表屬性名數(shù)據(jù)類(lèi)型取值范圍是否是主屬性或完整性外鍵enoChar(5)0000099999是NOT NULLaddidChar(5)0000099999是NOT NULLbasicidChar(5)0000099999是NOT NULLsalaryFloat0.099999否NULLrealsalFloat0.099999否NULLtaxFloat0.01否NOT NUL

12、LIII. 系統(tǒng)功能模塊人力資源管理系統(tǒng)員工基本信息管理員工工資信息管理系統(tǒng)功能模塊圖3. 數(shù)據(jù)庫(kù)實(shí)施階段I. 建立數(shù)據(jù)庫(kù)和數(shù)據(jù)表Create database employee_management;(1) 員工基本信息表的建立create table employee(eno char(20) PRIMARY KEY,pname char(10) NOT NULL,sex char(2) NOT NULL,age int NOT NULL,edu char(10),address char(50),tel char(20),birth datetime NOT NULL,pno char(

13、20) NOT NULL,dno char(20) NOT NULL)(2) 工資基本信息表的建立create table salary(eno char(20) PRIMARY KEY,salary float,realsal float ,addid char(20) NOT NULL,basicid char(20) NOT NULL)(3) 部門(mén)信息表的建立create table department(dno char(20) PRIMARY KEY,dname char(20) NOT NULL,)(4) 職務(wù)信息表的建立create table position(pno char

14、(20) PRIMARY KEY,pname char(20) NOT NULL,)(5) 補(bǔ)貼表的建立create table additional(addid char(20) PRIMARY KEY,addmoney float NOT NULL)(6) 基本工資表的建立create table basicsalary(basicid char(20) PRIMARY KEY,basicsal float NOT NULL)II. 建立視圖(1) 員工信息視圖：create view view_employeeasselecteno as員工號(hào) ,employee.pname as 姓名

15、 ,sexas 性別 ,age as 年齡 ,edu as 學(xué)歷 ,addressas住址 ,tel as聯(lián)系電話(huà) ,birth as出生年月 ,position.pname as職位 ,department.dname as部門(mén)from employee,position,departmentwhere employee.pno=position.pno and employee.dno=department.dno(2) 工資信息視圖：create view view_salaryasselect employee.pnameas姓名 ,salaryas應(yīng)發(fā)工 資 ,realsal as實(shí)

16、發(fā)工資 ,addmoney as補(bǔ)貼 ,basicsal as基本工資, tax as稅from salary,additional,basicsalary,employeewheresalary.addid=additional.addidandbasicsalary.basicid=salary.basicidandemployee.eno=salary.eno(3) 全部信息視圖：create view view_allinfoasselect employee.eno as員工號(hào) ,employee.pname as姓名 ,sex as性別 ,age as年齡 ,edu as學(xué)歷 ,a

17、ddressas住 址 ,telas聯(lián) 系 電 話(huà) ,birthas 出 生 年 月 ,position.pnameas職位 ,department.dnameas 部 門(mén) ,salaryas應(yīng) 發(fā) 工 資 ,realsalas 實(shí) 發(fā) 工 資 ,addmoneyas補(bǔ)貼 ,basicsal as基本工資 , tax as稅from salary,additional,basicsalary,employee,position,departmentwhereemployee.pno=position.pnoandemployee.dno=department.dnoandsalary.addi

18、d=additional.addidandbasicsalary.basicid=salary.basicidandemployee.eno=salary.enoIII. 數(shù)據(jù)入庫(kù)員工信息采取用 Excel 導(dǎo)入的方式錄入數(shù)據(jù)。這種錄入方式方便快捷，適合大量數(shù)據(jù)的錄入。IV.創(chuàng)建各功能的存儲(chǔ)過(guò)程(1) 存儲(chǔ)過(guò)程：插入員工信息create procedure input_employee(員工號(hào) char(20),姓名 char(20),性別 char(2),年齡 int,學(xué)歷 char(10),住址 char(50),電話(huà) char(20),生日 datetime,職位號(hào) char(20),部

19、門(mén)號(hào) char(20)asinsert into employee values(員工號(hào) ,姓名 ,性別 ,年齡 ,學(xué)歷 ,住址 ,電話(huà) ,生日 ,職位號(hào) ,部門(mén)號(hào) )(2) 存儲(chǔ)過(guò)程：插入工資信息create procedure input_salary(員工號(hào) char(20),補(bǔ)貼號(hào) char(20),基本工資號(hào)char(20),稅 float)asinsert into salary values(員工號(hào) ,0,0, 補(bǔ)貼號(hào) ,基本工資號(hào) ,稅 )(3) 存儲(chǔ)過(guò)程：更新員工信息create procedure update_employee(員工號(hào) char(5),姓名 char(20

20、),性別 char(2),年齡 int,學(xué)歷 char(10),住址 char(50),電話(huà) char(20),生日 datetime,職位號(hào) char(20),部門(mén)號(hào) char(20)asif EXISTS(select * from employee where eno=BEGINupdate employee員工號(hào) )set pname= 姓名 ,sex=性別 ,age=年齡 ,edu=學(xué)歷 ,address= 住址 ,tel= 電話(huà) ,birth= 生日 ,pno=職位號(hào) ,dno=部門(mén)號(hào) where 員工號(hào) =employee.enoENDelseprint '不存在的員工

21、'(4) 存儲(chǔ)過(guò)程：更新工資信息create procedure update_salary(員工號(hào) char(20),補(bǔ)貼號(hào) char(20),基本工資號(hào)char(20),稅 float)asif EXISTS(select * from employee where eno=員工號(hào) )BEGINupdate salaryset addid= 補(bǔ)貼號(hào) ,basicid= 基本工資號(hào) ,tax=稅where 員工號(hào) =salary.enoENDelseprint '不存在的員工 '(5) 存儲(chǔ)過(guò)程：指定員工號(hào)刪除信息create procedure delete_inf

22、o(員工號(hào) char(5)asdeletefrom employeewhere eno=員工號(hào)deletefrom salarywhere eno=員工號(hào)V. 創(chuàng)建觸發(fā)器(1) 觸發(fā)器：計(jì)算應(yīng)發(fā)工資和實(shí)發(fā)工資create trigger salcalon salary for insert,updateasDECLARE bscsal floatdeclare additional floatselect additional=addmoney from additional where addid in (select addid from inseted) select bscsal=ba

23、sicsal from basicsalary where basicid in (select basicid from inserted) update salaryset realsal=additional+bscsalwhere addid in (select addid from inserted)VI.創(chuàng)建和綁定規(guī)則(1) 創(chuàng)建規(guī)則：性別限定create rule sex_ruleas sex=' 男' or sex='女'(2) 創(chuàng)建規(guī)則：年齡限定create rule age_ruleas age<=99 and age>=0(3) 創(chuàng)建規(guī)則：工資限定create rule salary_ruleas salary>0(4) 綁定規(guī)則sp_bindrule sex_rule,'employee.sex'sp_bindrule age_rule,'employee.age'sp_bindrule salary_rule,'additional.addmoney'sp_bindrule sal