人事管理系統(tǒng)數(shù)據(jù)庫課程設(shè)計(jì)SQL Server

1、數(shù)據(jù)庫管理實(shí)務(wù)實(shí)驗(yàn)報(bào)告課程號(hào)：B0900980實(shí)驗(yàn)項(xiàng)目：數(shù)據(jù)庫設(shè)計(jì)、創(chuàng)建、管理，數(shù)據(jù)庫操作學(xué)號(hào)姓名潘歡同組人學(xué)號(hào)姓名自己實(shí)驗(yàn)地點(diǎn)工程學(xué)院機(jī)房（文波）指導(dǎo)教師肖慎勇時(shí)間2012年6月評(píng)語（要求）： 按時(shí)完成實(shí)驗(yàn)；實(shí)驗(yàn)內(nèi)容和過程記錄完整；回答問題完整、正確；實(shí)驗(yàn)報(bào)告的撰寫認(rèn)真、格式符合要求。成績教師簽字肖慎勇一、實(shí)驗(yàn)?zāi)康?理解并掌握數(shù)據(jù)庫設(shè)計(jì)的概念、方法和步驟。2初步應(yīng)用數(shù)據(jù)庫設(shè)計(jì)方法。了解需求分析的內(nèi)容。3重點(diǎn)運(yùn)用ER模型進(jìn)行概念設(shè)計(jì)，然后將ER模型轉(zhuǎn)換為關(guān)系模型。4深入理解SQL Server 2005數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)。5深入理解SQL Server 2005的數(shù)據(jù)類型、表對(duì)象設(shè)計(jì)與定義。6掌

2、握SQL Server 2005Management studio的應(yīng)用。運(yùn)用交互方式和命令方式建立數(shù)據(jù)庫和表。7認(rèn)識(shí)和掌握Transact-SQL 的數(shù)據(jù)庫操作。8深入理解視圖意義。掌握SQL Server 中創(chuàng)建、管理與應(yīng)用視圖的方法。9深入理解數(shù)據(jù)庫安全的概念。10深入理解SQL Server 2005的安全體系框架。11掌握登錄與服務(wù)器角色的概念、操作與應(yīng)用。掌握數(shù)據(jù)庫用戶、固定角色、自定義角色、架構(gòu)的概念、操作與應(yīng)用。掌握數(shù)據(jù)庫權(quán)限的概念、操作與應(yīng)用。12理解事務(wù)的概念。13深入理解并掌握服務(wù)器編程的意義及方法。14能夠編寫各種自定義函數(shù)和存儲(chǔ)過程。15理解觸發(fā)器，并能夠編寫、應(yīng)用觸

3、發(fā)器。二、實(shí)驗(yàn)設(shè)備（環(huán)境）及要求PC、 Windows XP、SQL SERVER 2005三、實(shí)驗(yàn)內(nèi)容及要求 實(shí)驗(yàn)項(xiàng)目：人事綜合管理系統(tǒng) 通過調(diào)查、收集信息、分析，寫出分析與設(shè)計(jì)報(bào)告。1. 簡要的系統(tǒng)需求分析包括業(yè)務(wù)分析、功能需求分析、信息需求分析。試分析該系統(tǒng)的基本業(yè)務(wù)，計(jì)算機(jī)信息系統(tǒng)需要完成的基本功能、信息系統(tǒng)需要處理的信息。2. 概念設(shè)計(jì)設(shè)計(jì)系統(tǒng)的概念模型，采用ER模型。3. 邏輯設(shè)計(jì)將ER模型為關(guān)系模型，指出每個(gè)關(guān)系的主鍵、外鍵和必要的約束。4. 寫出數(shù)據(jù)庫的物理設(shè)計(jì)包括存儲(chǔ)組織結(jié)構(gòu)、表的結(jié)構(gòu)設(shè)計(jì)等。5. T-SQL命令方式創(chuàng)建數(shù)據(jù)庫的操作利用SQL命令創(chuàng)建數(shù)據(jù)庫文件、表、索引、聯(lián)系

4、和主鍵、外鍵等約束。（實(shí)驗(yàn)報(bào)告應(yīng)寫出實(shí)驗(yàn)的過程，包括必要的截圖。）6. 利用T-SQL命令增加、刪除、修改數(shù)據(jù)。7. 利用T-SQL命令進(jìn)行數(shù)據(jù)的檢索和統(tǒng)計(jì)根據(jù)自己設(shè)計(jì)的數(shù)據(jù)庫和輸入的數(shù)據(jù)，寫出至少10個(gè)查詢要求及對(duì)應(yīng)的SQL查詢命令，應(yīng)該包含如下功能：投影和選擇；多表連接；分組統(tǒng)計(jì)與HAVING；子查詢；查詢結(jié)果保存。8. 利用SQL命令創(chuàng)建視圖對(duì)象根據(jù)開發(fā)的系統(tǒng)的需要，至少設(shè)計(jì)三個(gè)視圖：基于單表的、包含多表連接的、包含統(tǒng)計(jì)運(yùn)算的。利用SQL對(duì)視圖進(jìn)行查詢。9. 對(duì)視圖進(jìn)行插入、刪除、修改數(shù)據(jù)操作。體會(huì)視圖與表的異同。10. 編寫自定義函數(shù)。（1）編寫一個(gè)自定義函數(shù)，將一個(gè)字符串作為自變量，

5、返回顛倒順序的字符串。寫出源代碼。（2） 編寫一個(gè)自定義函數(shù)，能夠?qū)崿F(xiàn)參數(shù)化查詢的功能。自己設(shè)定函數(shù)的具體要求，然后編寫出來。11. 編寫利用游標(biāo)進(jìn)行數(shù)據(jù)處理的存儲(chǔ)過程。并在存儲(chǔ)過程中應(yīng)用事務(wù)的概念。自己確定過程的具體要求，然后編寫出來。12. 編寫一個(gè)實(shí)現(xiàn)修改表的觸發(fā)器，實(shí)現(xiàn)完整性控制。13. 設(shè)置服務(wù)器身份驗(yàn)證模式。用命令創(chuàng)建若干不同驗(yàn)證模式的登錄賬戶。14. 將部分登錄賦予服務(wù)器角色。然后撤消。15. 將部分登錄映射到你的數(shù)據(jù)庫中成為用戶。16. 創(chuàng)建自定義角色、架構(gòu)。17. 通過角色給用戶授權(quán)。18. 直接給用戶授權(quán)，驗(yàn)證其獲得權(quán)限前后的操作差別。19. 刪除用戶和自定義的角色。四、回

6、答問題1你如何認(rèn)識(shí)需求分析在系統(tǒng)開發(fā)中的地位和重要性？答：我覺得需求分析就是把客戶的功能描述轉(zhuǎn)化為開發(fā)員所能理解的功能描述，并在客戶描述的基礎(chǔ)上去除不合理的地方，補(bǔ)充系統(tǒng)缺失的地方，最后為系統(tǒng)的概要設(shè)計(jì)，詳細(xì)設(shè)計(jì)提供準(zhǔn)確，有效的數(shù)據(jù)基礎(chǔ)。在需求分析中要把系統(tǒng)所要處理的業(yè)務(wù)理解透徹，并把其中的信息整理歸類。然后還需要把整個(gè)系統(tǒng)所要實(shí)現(xiàn)的功能的邏輯思路理清楚，這些工作都是系統(tǒng)開發(fā)能夠繼續(xù)做下去的基礎(chǔ)。如果需求分析沒有做好，或者說對(duì)整個(gè)系統(tǒng)的總體框架不明晰，則在后面的設(shè)計(jì)中會(huì)很混亂，出現(xiàn)很多的問題。所以我覺得需求分析可以說是系統(tǒng)開發(fā)的核心部分，做好這步，后面的就能清晰的進(jìn)行了。2設(shè)計(jì)ER圖、關(guān)系模型

7、分別屬于數(shù)據(jù)庫設(shè)計(jì)的哪一階段？答：ER圖屬于概念設(shè)計(jì)階段；關(guān)系模型處于邏輯設(shè)計(jì)階段3主鍵、外鍵對(duì)于關(guān)系數(shù)據(jù)庫的意義何在？答：主鍵是對(duì)表的約束，保證數(shù)據(jù)的唯一性！ 外鍵是建立表于表之間的聯(lián)系，方便程序的編寫！主鍵和外鍵是把多個(gè)表組織為一個(gè)有效的關(guān)系數(shù)據(jù)庫的粘合劑。主鍵和外鍵的設(shè)計(jì)對(duì)物理數(shù)據(jù)庫的性能和可用性都有著決定性的影響。4在數(shù)據(jù)庫存儲(chǔ)結(jié)構(gòu)設(shè)計(jì)時(shí)要考慮哪些因素？答：首先要確定數(shù)據(jù)的存放位置和存儲(chǔ)結(jié)構(gòu)，包括：確定關(guān)系、索引、聚簇、日志、備份等的存儲(chǔ)安排，確定系統(tǒng)配置。同時(shí)要綜合考慮存取時(shí)間、存儲(chǔ)空間利用率和維護(hù)代價(jià)3個(gè)方面的因素。把它們進(jìn)行權(quán)衡，選擇一個(gè)折中的方案。5在表設(shè)計(jì)時(shí)對(duì)于數(shù)據(jù)類型的選擇

8、和使用如何考慮？答：（1）首先要清楚理解數(shù)據(jù)庫中各種數(shù)據(jù)類型的用法和范圍；（2）應(yīng)該根據(jù)字段的實(shí)際類型來考慮數(shù)據(jù)類型；（3）要估計(jì)表中數(shù)據(jù)的大小范圍，來確定數(shù)據(jù)類型的大小，避免空間不足或者浪費(fèi)。6. 交互式如何實(shí)現(xiàn)表之間的聯(lián)系？SQL命令如何實(shí)現(xiàn)表之間的聯(lián)系？答：（1）交互式：在管理平臺(tái)中，展開要操作的數(shù)據(jù)庫，選擇“數(shù)據(jù)庫關(guān)系圖”選項(xiàng)，然后單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“新建數(shù)據(jù)庫關(guān)系圖”命令，在彈出的窗口中選擇要建立關(guān)系的表后，則會(huì)彈出數(shù)據(jù)庫關(guān)系圖設(shè)計(jì)窗口。然后右鍵單擊關(guān)系圖的空白頁面，在彈出的快捷菜單中，可以新建或添加數(shù)據(jù)庫中已定義的表，在該表的關(guān)聯(lián)菜單中選擇“屬性”選項(xiàng)，可以創(chuàng)建或

9、定義該表的關(guān)系、鍵、索引和約束或修改當(dāng)前的附加特性。（2）SQL命令：ALTER TABLEADD CONSTRAINT constraint_namePRIMARYKEYCLUSTERED|NONCLUSTEREDFOREIGN KEYREFERENCES ref_table7如果在定義表時(shí)進(jìn)行了主鍵、外鍵以及約束等完整性設(shè)置，對(duì)于數(shù)據(jù)的輸入有何影響？試舉例說明。答：定義主鍵 ：定義主鍵來強(qiáng)制不允許空值的指定列中輸入值的唯一性。如果在數(shù)據(jù)庫中為表定義了主鍵，則可將該表與其它表相關(guān)，從而減少冗余數(shù)據(jù)。表只能有一個(gè)主鍵。如果主鍵包含不止一列，則在一列中允許有重復(fù)值，但主鍵中所有列值的每個(gè)組合必須

10、唯一。定義外鍵：外鍵引用完整性表示得到正常維護(hù)的表之間的關(guān)系。表中的數(shù)據(jù)只應(yīng)指向另一個(gè)表中的現(xiàn)有行，不應(yīng)指向不存在的行。主鍵約束：使用單列或者多列組合，表示每一條記錄的唯一性，值不能為空；外鍵約束：       是為了加強(qiáng)數(shù)據(jù)表之間的關(guān)鍵的表中的單列或者多列值，必須依賴另外的表存在；唯一約束：不允許數(shù)據(jù)庫中的某一列數(shù)據(jù)有重復(fù)，但是允許有空值存在。主鍵和唯一性約束定義了表中記錄的唯一性，但是主鍵列的值不鞥為空，而唯一鍵約束的列的值可以為空值；8用INSERT INTO 命令輸入，如果數(shù)據(jù)與字段數(shù)量和要求不一致有什么問題？當(dāng)表定義中有默認(rèn)值的時(shí)候，

11、插入記錄是否可以省略該字段的數(shù)據(jù)？答：如果數(shù)據(jù)與字段數(shù)量和要求不一致，那么會(huì)插入失敗，并且SQL Server將顯示錯(cuò)誤信息。有默認(rèn)值時(shí)可以省略該字段數(shù)據(jù)，系統(tǒng)會(huì)為該列提供默認(rèn)值。9HAVING子句有什么作用？答：HAVING字句與WHERE子句一樣，也可以起到按條件選擇記錄的功能，但HAVING子句是作用于組，必須與GROUP BY子句連用，用來指定每一分租內(nèi)應(yīng)滿足的條件。10當(dāng)通配字符作為普通字符參與匹配查詢時(shí)，如何解決這樣的問題？答：轉(zhuǎn)義，對(duì)通配符加。11可否利用視圖進(jìn)行數(shù)據(jù)的插入、刪除、修改？ 答：可以通過視圖對(duì)基礎(chǔ)表中的數(shù)據(jù)進(jìn)行檢索、添加、修改和查詢。但是插入、修改、刪除數(shù)據(jù)是只能做

12、單表的，基于多表的視圖不可以刪除和修改。12根據(jù)自己的體會(huì)，談?wù)勔晥D在數(shù)據(jù)庫體系中的作用。 模式答：利用視圖可以簡化用戶操作數(shù)據(jù)的方式?？蓪⒔?jīng)常使用的連接、投影、聯(lián)合查詢和選擇查詢定義為視圖，這樣每次對(duì)特定的數(shù)據(jù)執(zhí)行進(jìn)一步操作時(shí)，不必指定所有條件和限定。除此之外還可以定制數(shù)據(jù)，導(dǎo)出數(shù)據(jù)。還可以用GRANT和REVOKE命令為各種用戶授予在視圖上的操作權(quán)限，這樣通過視圖，用戶只能查詢或修改他們各自所能見到的數(shù)據(jù)，數(shù)據(jù)庫中的其他數(shù)據(jù)對(duì)他們來說是不可見的或不可修改的。保證數(shù)據(jù)庫的安全性。13視圖是如何保存的？視圖中的數(shù)據(jù)是否真的存儲(chǔ)在文件中？答：保存視圖將更改服務(wù)器上的視圖定義。 當(dāng)您保存視圖時(shí)，基

13、礎(chǔ)數(shù)據(jù)庫引擎將使用新的視圖定義。在查詢和視圖設(shè)計(jì)器中打開視圖定義并對(duì)其進(jìn)行修改。（1）在查詢和視圖設(shè)計(jì)器中打開視圖定義并對(duì)其進(jìn)行修改。（2）從“文件”菜單中單擊“保存 view_name”，其中 view_name 是打開的視圖的名稱。14SQL SERVER有幾種身份驗(yàn)證模式？區(qū)別是什么？不同驗(yàn)證模式的登錄賬戶有何區(qū)別？答：有兩種（1）windows身份驗(yàn)證模式，是默認(rèn)的驗(yàn)證模式，使用windows操作系統(tǒng)的安全機(jī)制驗(yàn)證用戶身份，只要用戶能夠通過windows用戶賬號(hào)驗(yàn)證，并且是SQL Server的登錄賬戶，即可連接到SQL Server而不用進(jìn)行身份驗(yàn)證，只適用于能夠提供有效身份驗(yàn)證的w

14、indows操作系統(tǒng)。（2）混合的驗(yàn)證模式：在該模式下，Windows和SQL Server兩種驗(yàn)證模式都可用，對(duì)可信任連接用戶，直接采用windows 的身份驗(yàn)證，否則采用SQL Server模式，用戶在連接SQL Server時(shí)必須提供登錄名和密碼，SQL Server自己執(zhí)行認(rèn)證處理，如果輸入的登錄信息與系統(tǒng)表syslogins中的某幾條記錄相匹配時(shí)表明登錄成功。15Sa賬戶有何特點(diǎn)？ 答：Sa賬戶是SQL Server的特殊賬戶，sa賬戶擁有服務(wù)器和所有的數(shù)據(jù)庫。即Sa賬戶擁有最高的管理權(quán)限，可以執(zhí)行服務(wù)器范圍內(nèi)所有操作。它是以SQL Server身份驗(yàn)證的，所以必須設(shè)置一個(gè)密碼。同時(shí)

15、，sa賬戶的屬性不可以更改，密碼可以修改。Sa登錄時(shí)無法刪除的。16共有幾種服務(wù)器角色？最高權(quán)限的角色是什么？能夠創(chuàng)建數(shù)據(jù)庫的角色是哪個(gè)？答：共有8種固定服務(wù)器角色，分別是：sysadmin，serveradmin，setupadmin，securityadmin，processadmin，dbcreator，diskadmin，bulkadmin；同時(shí)每一個(gè)登錄用戶都屬于public服務(wù)器角色。最高權(quán)限的角色是sysadmin；能夠創(chuàng)建數(shù)據(jù)庫的角色是：sysadmin，dbcreator；17數(shù)據(jù)庫用戶和登錄賬戶有什么關(guān)系？guest是什么用戶？有何作用？答：這是兩種不同級(jí)別的安全機(jī)制，登錄

16、賬戶是SQL Server級(jí)的安全性設(shè)置，而數(shù)據(jù)庫用戶則是數(shù)據(jù)庫級(jí)的安全性設(shè)置。登錄賬戶用于登錄服務(wù)器，但是當(dāng)某一用戶要訪問某個(gè)數(shù)據(jù)庫時(shí)，除了可以成為服務(wù)器的一個(gè)登錄以外，還必須成為這個(gè)數(shù)據(jù)庫的用戶賬戶，即獲得一定的授權(quán)。數(shù)據(jù)庫用戶賬號(hào)在一般情況下是從某個(gè)登錄賬戶中映射過來的。Guest是來賓用戶，是用來共享文件的。任何一個(gè)登錄都可以以此身份訪問數(shù)據(jù)庫。Guest自動(dòng)本身只具有public權(quán)限。也可以由其他用戶授權(quán)。18數(shù)據(jù)庫角色有哪兩類？PUBLIC角色有何特點(diǎn)？答：數(shù)據(jù)庫角色分為固定的數(shù)據(jù)庫角色和用戶定義數(shù)據(jù)庫角色。每個(gè)數(shù)據(jù)庫都有一系列固定數(shù)據(jù)庫角色。雖然每個(gè)數(shù)據(jù)庫中都存在名稱相同的角色，

17、但各個(gè)角色的作用域只是在特定的數(shù)據(jù)庫內(nèi)。用戶定義數(shù)據(jù)庫角色是用戶根據(jù)工作的職能定義一系列角色，并給每個(gè)角色指派了適合這項(xiàng)工作的權(quán)限。從而在數(shù)據(jù)庫中管理這些權(quán)限。PUBLIC角色的特點(diǎn)：默認(rèn)不具有任何權(quán)限，但用戶可對(duì)此角色進(jìn)行授權(quán)，他不能被刪除，是所有user自動(dòng)擁有的角色。數(shù)據(jù)庫中的每個(gè)用戶都屬于 public 數(shù)據(jù)庫角色。如果想讓數(shù)據(jù)庫中的每個(gè)用戶都能有某個(gè)特定的權(quán)限，則將該權(quán)限指派給 public 角色。如果沒有給用戶專門授予對(duì)某個(gè)對(duì)象的權(quán)限，他們就使用指派給 public 角色的權(quán)限。 19用戶與架構(gòu)有什么關(guān)系？系統(tǒng)默認(rèn)架構(gòu)是什么？怎樣為用戶指定架構(gòu)？答：SQL Server 2000中

18、數(shù)據(jù)庫用戶和構(gòu)架是隱式連在一起的，架構(gòu)將用戶和權(quán)限之間建立了一個(gè)間接地方式。數(shù)據(jù)庫架構(gòu)是一個(gè)獨(dú)立于數(shù)據(jù)庫用戶的非重復(fù)命名空間，可以將架構(gòu)視為對(duì)象的容器。每個(gè)數(shù)據(jù)庫用戶都是與該用戶同名架構(gòu)的所有者；而在SQL Server 2005中構(gòu)架和用戶是不同的實(shí)體。一個(gè)對(duì)象只能屬于一個(gè)架構(gòu)，就像一個(gè)文件只能存放于一個(gè)文件夾中一樣。與文件夾不同的是，架構(gòu)是不能嵌套的。系統(tǒng)默認(rèn)架構(gòu)是dbo架構(gòu)；通過使用CREATE USER 和ALTER USER 語句，可以分配一個(gè)默認(rèn)架構(gòu)；也可以通過SSMS在該用戶的屬性下面分配一個(gè)默認(rèn)架構(gòu)。20. 什么是安全對(duì)象？怎樣為數(shù)據(jù)庫用戶指定查詢表的權(quán)限？答：安全對(duì)象是SQL

19、 Server 數(shù)據(jù)庫引擎授權(quán)系統(tǒng)控制對(duì)其進(jìn)行訪問的資源。安全對(duì)象范圍有服務(wù)器、端點(diǎn)和架構(gòu)。21簡述自定義函數(shù)、存儲(chǔ)過程和觸發(fā)器的異同。答：（1）自定義函數(shù)、存儲(chǔ)過程、和觸發(fā)器都是模塊化設(shè)計(jì)的類型。（2）函數(shù)最后是返回一個(gè)值，在表達(dá)式中調(diào)用，定義函數(shù)時(shí)要定義函數(shù)的函數(shù)名、參數(shù)、函數(shù)值類型和函數(shù)體。（3）存儲(chǔ)過程經(jīng)編譯后放在數(shù)據(jù)庫服務(wù)器端，供客戶端調(diào)用，處理一件事或完成一個(gè)功能，有單獨(dú)的調(diào)用命令。調(diào)用過程時(shí)，是實(shí)際參數(shù)與形參的傳遞（值傳遞和名傳遞兩種）。定義過程時(shí)需定義過程名、參數(shù)、過程體。它可以實(shí)現(xiàn)完整性控制機(jī)。（4）觸發(fā)器實(shí)質(zhì)是一種特殊類型的存儲(chǔ)過程，它在插入、修改或刪除時(shí)觸發(fā)執(zhí)行。能夠?qū)崿F(xiàn)

20、由主鍵和外鍵所不能保證的參照完整性和數(shù)據(jù)的一致性。22什么是游標(biāo)？應(yīng)用游標(biāo)的步驟是什么？答：游標(biāo)是對(duì)表的運(yùn)用的一種機(jī)制，相當(dāng)于在表上定義一條指針，把表中的數(shù)據(jù)按一行一行的來讀取。把表中的數(shù)據(jù)取出來放在高級(jí)語言的變量中，是SQL語言與高級(jí)語言之間的橋梁。應(yīng)用游標(biāo)的步驟：（1）聲明游標(biāo)（2）打開游標(biāo)，即把外存上的數(shù)據(jù)調(diào)入內(nèi)存，運(yùn)行查詢（3）提取數(shù)據(jù)(4)關(guān)閉游標(biāo)（5）釋放游標(biāo)五、分析討論通過實(shí)驗(yàn)我覺得最重要的是系統(tǒng)分析，邏輯設(shè)計(jì)和物理設(shè)計(jì)時(shí)一定要認(rèn)真想清楚每張表的屬性和它們之間的相互聯(lián)系，這樣在后面使用查詢的時(shí)候才不會(huì)出錯(cuò)，如果前面沒做好，后面做查詢的時(shí)候就會(huì)出現(xiàn)各種錯(cuò)誤，如數(shù)據(jù)結(jié)構(gòu)定義不統(tǒng)一，外

21、鍵沖突等問題。除此之外要做好一個(gè)數(shù)據(jù)庫系統(tǒng)，要掌握好學(xué)習(xí)中的每一步，平時(shí)上課學(xué)習(xí)很有用，好好學(xué)了，做的時(shí)候就會(huì)比較輕松。除此之外，我覺得函數(shù)和觸發(fā)器那部分比較難，只會(huì)實(shí)現(xiàn)簡單的查詢、刪除、修改控制。對(duì)基于多表的帶參函數(shù)運(yùn)用還比較弱。 以下為本次實(shí)驗(yàn)的過程記錄報(bào)告撰寫。（可包括必要的截圖）人事綜合管理系統(tǒng)信管1003 1009030340 潘歡 1.簡要的系統(tǒng)需求分析 人事管理系統(tǒng)是非常通用的管理信息系統(tǒng)，是企事業(yè)單位實(shí)現(xiàn)辦公自動(dòng)化的前提和基礎(chǔ)。一個(gè)公司要正常運(yùn)作必定有一個(gè)健全的人事管理系統(tǒng)，來管理員工的信息，包括員工編號(hào)、姓名、性別、民族工資及獎(jiǎng)懲考勤情況。除此之外，系統(tǒng)還供員工查詢個(gè)人和公共

22、信息，管理員對(duì)員工信息進(jìn)行更改或刪除，管理公司的人事變更和管理，大大提高公司內(nèi)部員工管理的工作效率。1.1業(yè)務(wù)分析（1）部門管理：對(duì)公司的部門進(jìn)行編號(hào)，將每一個(gè)部門的部門號(hào)、部門名稱、部門描述和部門負(fù)責(zé)人信息統(tǒng)一保存在部門信息檔案中。當(dāng)新增或去除某個(gè)部門時(shí)，將其信息添加到部門信息檔案或或從中刪除。當(dāng)部門信息發(fā)生變化時(shí)，修改對(duì)應(yīng)和相關(guān)的屬性值。（2）員工信息管理：對(duì)公司里的所有員工進(jìn)行統(tǒng)一編號(hào)；將每一位員工的信息錄入到員工信息檔案中。對(duì)新聘用的員工，將其信息加入到員工信息檔案中；對(duì)于解雇的員工，將其信息從信息檔案中刪除。當(dāng)員工的信息發(fā)生變動(dòng)時(shí)，修改員工信息檔案中相應(yīng)的屬性。同時(shí)供員工自己查詢使用

23、。其下還包含有個(gè)人家庭關(guān)系和個(gè)人呢工作學(xué)習(xí)的經(jīng)歷，各放在一張表格中。（3）員工職務(wù)調(diào)動(dòng)管理：同樣對(duì)公司員工的職務(wù)進(jìn)行統(tǒng)一編號(hào)；登記員工調(diào)動(dòng)前后的具體職務(wù)，以及調(diào)動(dòng)時(shí)間。（4）獎(jiǎng)懲管理：根據(jù)每個(gè)員工的編號(hào)登記獎(jiǎng)懲原因和獎(jiǎng)懲金額及獎(jiǎng)懲月份。（5）考勤管理：根據(jù)員工編號(hào)，登記員工每個(gè)月的全勤，出差，請(qǐng)假時(shí)日和加班遲到早退信息。（6）工資管理：對(duì)所有員工的每筆工資進(jìn)行編號(hào)，記錄工資記錄表。1.2功能需求分析（1）一般用戶可以查詢個(gè)人信息和修改自己的登錄密碼；（2）管理員可以查詢所有員工的信息并可以做添加、修改、刪除的操作。2.概念設(shè)計(jì)ER模型設(shè)計(jì)部門部門編號(hào)部門名稱部門職能描述部門負(fù)責(zé)人編號(hào)用戶管理用

24、戶名密碼用戶類型員工員工編號(hào)姓名籍貫婚姻狀況文化程度政治面貌······性別民族身份證號(hào)生日考勤考勤編號(hào)考勤月份法定休假天數(shù)曠工天數(shù)員工編號(hào)全勤天數(shù)出差天數(shù)病假天數(shù)事假天數(shù)······員工調(diào)動(dòng)調(diào)動(dòng)編號(hào)調(diào)動(dòng)日期員工編號(hào)調(diào)動(dòng)前職務(wù)調(diào)動(dòng)后職務(wù)批復(fù)部門編號(hào)獎(jiǎng)懲員工編號(hào)獎(jiǎng)懲編號(hào)處罰金額獎(jiǎng)勵(lì)事由獎(jiǎng)勵(lì)金額處罰事由家庭關(guān)系家庭編號(hào)工作單位與本人關(guān)系員工編號(hào)年齡性別姓名工作經(jīng)歷職務(wù)學(xué)校/單位名稱終止日期開始日期員工編號(hào)編號(hào)工資工資編號(hào)處罰金額獎(jiǎng)勵(lì)金額員工編號(hào)基本工資考勤月份管理員員工屬于調(diào)動(dòng)查

25、詢部門維護(hù)工作經(jīng)歷家庭關(guān)系附有附有員工調(diào)動(dòng)工資考勤查詢查詢修改查詢修改管理員查詢修改查詢修改查詢修改維護(hù)3.邏輯結(jié)構(gòu)設(shè)計(jì)將ER模型轉(zhuǎn)化為關(guān)系模型（1）部門（部門編號(hào)+部門名稱+部門簡介+部門負(fù)責(zé)人）；PS：部門編號(hào)為主碼（2）員工信息（員工編號(hào)+姓名+性別+民族+生日+政治面貌+文化程度+婚姻狀況+籍貫+身份證號(hào)+工作證號(hào)+辦公電話+手機(jī)號(hào)碼+部門編號(hào)+職務(wù)+狀態(tài)+工資+備注）Ps：員工編號(hào)為主碼，部門編號(hào)為外碼（3）職務(wù)調(diào)動(dòng)信息（調(diào)動(dòng)編號(hào)+員工編號(hào)+調(diào)動(dòng)前職務(wù)+調(diào)動(dòng)后職務(wù)+調(diào)動(dòng)日期+批復(fù)部門號(hào)）PS：調(diào)動(dòng)編號(hào)為主碼，員工編號(hào)為外碼（4）獎(jiǎng)懲信息（獎(jiǎng)懲編號(hào)+員工編號(hào)+獎(jiǎng)勵(lì)事由+獎(jiǎng)勵(lì)金額+處罰事

26、由+處罰金額）PS：獎(jiǎng)懲編號(hào)為主碼+員工編號(hào)為外碼（5）考勤信息（考勤月份+員工編號(hào)+全勤天數(shù)+出差天數(shù)+病假天數(shù)+事假天數(shù)+曠工天數(shù)+遲到時(shí)間+早退時(shí)間+）PS:考勤月份為主碼，員工編號(hào)為外碼（6）工資信息（工資編號(hào)+員工編號(hào)+基本工資+獎(jiǎng)勵(lì)金額+處罰金額+考勤月份）PS：工資編號(hào)為主碼，員工編號(hào)和考勤月份為外碼（7）管理員信息（用戶名+密碼+用戶類型）PS：用戶名為主碼；4.數(shù)據(jù)庫的物理設(shè)計(jì)（1）具體表的結(jié)構(gòu)設(shè)計(jì)部門：編號(hào)字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1部門編號(hào)intNot nullprimary key2部門名稱varchar(40)Not null3部門職能描述Varchar(400)nul

27、l4部門負(fù)責(zé)人編號(hào)Char(3)Not null員工：編號(hào)字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1員工編號(hào)Char(4)Not null primary key2姓名Varchar(50)Not null3性別Char(2)null4民族varchar(40)null5生日datetimenull6政治面貌varchar(40)null7文化程度varchar(40)null8婚姻狀況varchar(20)null9籍貫varchar(60)null10身份證號(hào)varchar(20)null11辦公電話varchar(30)null12手機(jī)號(hào)碼varchar(30)null13部門編號(hào)intnull14職務(wù)

28、varchar(20)null15員工狀態(tài)（1-在職，2-兼職，3，試用，4-離職，5-退休）tinyintnull員工調(diào)動(dòng)：編號(hào)字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1調(diào)動(dòng)編號(hào)Char(2)Not null primary key2員工編號(hào)Char(4)Not null3調(diào)動(dòng)前職務(wù)Varchar(20)Null4調(diào)動(dòng)后職務(wù)Varchar(20)null5調(diào)動(dòng)日期DatetimeNull6批復(fù)部門編號(hào)intNot null獎(jiǎng)懲：編號(hào)字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1獎(jiǎng)懲編號(hào)Char(2)Not null primary key2員工編號(hào)Char(4)Not null3獎(jiǎng)勵(lì)事由Varchar(200)Null4獎(jiǎng)勵(lì)金

29、額smallintNull5處罰事由Varchar(200)Null6處罰金額smallintnull考勤：編號(hào)字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1考勤編號(hào)Char(3)Not null primary key2考勤月份DatetimeNot null3員工編號(hào)intNot null4全勤天數(shù)Decimal(4,1)Null5出差天數(shù)Decimal(4,1)Null6病假天數(shù)Decimal(4,1)Null7事假天數(shù)Decimal(4,1)Null8曠工天數(shù)Decimal(4,1)Null9法定休假天數(shù)Decimal(4,1)Null10遲到時(shí)間（分）TinyintNull11早退時(shí)間(分)Tinyin

30、tNull12一類加班天數(shù)（法定節(jié)假日加班）IntNull13二類加班天數(shù)（周六或周日加班）IntNull14三類加班天數(shù)（日常加班）IntNull工資：編號(hào)字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1工資編號(hào)Char(3)Not nullprimary key2員工編號(hào)Char(4)Not null3基本工資moneyNull4考勤月份DatetimeNot null5獎(jiǎng)勵(lì)金額moneyNull6處罰金額moneynull用戶管理：編號(hào)字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1用戶名Varchar(40)Not nullprimary key2密碼Varchar(40)Not null3用戶類型（1-系統(tǒng)管理員，2-普通用戶

31、tinyintNot null家庭關(guān)系：編號(hào)說明數(shù)據(jù)結(jié)構(gòu)約束條件1編號(hào)tinyintNot nullprimary key2員工編號(hào)intNot null3姓名Varchar(50)Null4性別Char(2)Null5年齡tinyintNull6與本人關(guān)系Varchar(20)Null7工作單位Varchar(40)null工作經(jīng)歷：編號(hào)說明數(shù)據(jù)結(jié)構(gòu)約束條件1編號(hào)tinyintNot nullprimary key2員工編號(hào)intNot null3開始日期Char(10)null4終止日期Char(10)null5學(xué)校/單位名稱Varchar(50)null6職務(wù)Varchar(20)nul

32、l5.T-SQL命令方式創(chuàng)建數(shù)據(jù)庫文件、表、索引、聯(lián)系和主鍵、外鍵等約束（1）創(chuàng)建數(shù)據(jù)庫create database HrSysALTER DATABASE HrSySADD FILE(NAME=HrSys_dat2,FILENAME='C:Program FilesMicrosoft SQL ServerMSSQLDataHrSys_dat2.ndf'SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)LOG ON ( NAME='HrSys_LOG', FILENAME='C:Program FilesMicrosoft S

33、QL ServerMSSQLDataHrSys_log.ldf', SIZE=3, MAXSIZE=20, FILEGROWTH=1 )（2）創(chuàng)建表use HrSyscreate table 部門(部門編號(hào)int primary key,部門名稱varchar(40) not null,部門職能描述varchar(400),部門負(fù)責(zé)人編號(hào)char(4)not null,)create table 員工(員工編號(hào)char(4) primary key,姓名varchar(50) not null,性別char(2),民族varchar(40),生日varchar(20),政治面貌varc

34、har(40),文化程度varchar(40),婚姻狀況varchar(20),籍貫varchar(60),身份證號(hào)varchar(20),辦公電話varchar(30),手機(jī)號(hào)碼varchar(40), 部門編號(hào)int not null,職務(wù)varchar(20),狀態(tài)tinyint,工資decimal(9,2)create table 考勤(考勤編號(hào) int primary key,考勤月份datetime not null,員工編號(hào)char(4) not null,全勤天數(shù)Decimal(4,1),出差天數(shù)Decimal(4,1),病假天數(shù)Decimal(4,1),事假天數(shù)Decimal

35、(4,1),曠工天數(shù)Decimal(4,1),法定休假日Decimal(4,1),遲到時(shí)間Tinyint,早退時(shí)間Tinyint,法定假日加班Decimal(4,1),周末加班Decimal(4,1),日常加班Decimal(4,1),)create table 員工調(diào)動(dòng)(調(diào)動(dòng)編號(hào)char(2) primary key,員工編號(hào)char(4) not null,調(diào)動(dòng)前職務(wù)varchar(20),調(diào)動(dòng)后職務(wù)varchar(20),調(diào)動(dòng)日期char(10),批復(fù)部門編號(hào)int)create table 工資(工資編號(hào)char(3) primary key,員工編號(hào)char(4) not null,

36、基本工資money,考勤月份datetime not null,獎(jiǎng)勵(lì)金額money,處罰金額money,)alter table 工資add 總共資as 基本工資+獎(jiǎng)勵(lì)金額-處罰金額create table 用戶管理(用戶名varchar(40)PRIMARY KEY,密碼 varchar(40) NOT NULL,用戶類型tinyint NOT NULL)insert into Users values('Admin','8888',1)create table 獎(jiǎng)懲(獎(jiǎng)懲編號(hào) char(10)primary key,員工編號(hào) char(4) not null

37、,獎(jiǎng)勵(lì)事由 varchar(200),獎(jiǎng)勵(lì)金額 smallint,處罰事由 varchar(200),處罰金額 smallint,)reate table 家庭關(guān)系(家庭編號(hào) char(2) primary key, 員工編號(hào) char(4) not null,姓名 varchar(50) not null,性別 char(2),年齡 tinyint,與本人關(guān)系 varchar(20),工作單位 varchar(40)create table 工作經(jīng)歷(編號(hào)tinyint primary key,員工編號(hào) char(4) not null,開始日期Char(10),終止日期Char(10),學(xué)

38、校/單位名稱Varchar(50),職務(wù)Varchar(20)（3）創(chuàng)建索引（4）修改表來創(chuàng)建外鍵關(guān)系和約束6.利用T-SQL命令增加、刪除、修改數(shù)據(jù)（1）向表中增加屬性列（2）修改、添加和刪除數(shù)據(jù)例如：更新工資表的結(jié)果為7.利用T-SQL命令進(jìn)行數(shù)據(jù)的檢索和統(tǒng)計(jì)（1）投影和選擇（2）多表連接選擇在技術(shù)部或企劃部的員工姓名和所在部門名稱選擇總共資在4000元以上的員工編號(hào)，姓名，職務(wù)，工資和考勤月份信息選擇有信息存在員工表，家庭關(guān)系表，工資表的員工的編號(hào)、姓名、籍貫、家庭關(guān)系、與本人關(guān)系、基本工資信息（3）分組統(tǒng)計(jì)與HAVING統(tǒng)計(jì)每個(gè)部門的男女人數(shù)select 部門編號(hào),性別,count(*

39、) as 人數(shù)from 員工group by 部門編號(hào),性別選擇基本工資在2500以上的員工SELECT 員工編號(hào),avg(基本工資)FROM 工資group by 員工編號(hào)having avg(基本工資)>2500（4）子查詢選擇沒有信息在家庭關(guān)系表中的員工信息SELECT * FROM 員工 WHERE not EXISTS (SELECT * FROM 家庭關(guān)系 WHERE 家庭關(guān)系.員工編號(hào)=員工.員工編號(hào))select 員工編號(hào),姓名,生日,籍貫,身份證號(hào),職務(wù),辦公電話from 員工where EXISTS(SELECT * FROM 工作經(jīng)歷 WHERE 員工.員工編號(hào)=工

40、作經(jīng)歷.員工編號(hào))（5）查詢結(jié)果保存select 姓名,職務(wù),辦公電話,手機(jī)號(hào)碼,調(diào)動(dòng)前職務(wù)into B_INfofrom 員工,員工調(diào)動(dòng)where 員工.員工編號(hào)=員工調(diào)動(dòng).員工編號(hào)select *from B_INfo8.利用SQL命令創(chuàng)建數(shù)據(jù)庫視圖（1）基于單表的建立視圖員工常用信息CREATE VIEW 員工常用信息(員工編號(hào),姓名,職位,辦公電話,手機(jī)號(hào)碼,部門編號(hào))AS SELECT 員工編號(hào),姓名,職務(wù),辦公電話,手機(jī)號(hào)碼,部門編號(hào)FROM dbo.員工sp_helptext 員工常用信息（2）包含多表連接的建立視圖員工考勤信息，員工基本信息和2012年考勤情況CREATE VI

41、EW 員工考勤信息(員工姓名,職位,辦公電話,曠工天數(shù),事假天數(shù),病假天數(shù))AS SELECT 姓名,職務(wù),辦公電話,曠工天數(shù),事假天數(shù),病假天數(shù)FROM dbo.員工, dbo.考勤WHERE (datename(yy,考勤月份)='2012')sp_helptext 員工考勤信息（3）包含統(tǒng)計(jì)運(yùn)算的建立視圖view1，查詢所有職工的職工號(hào)、姓名、部門名和2011年份工資，并按部門名順序排列；create view view1asselect top 50 員工.員工編號(hào),員工.姓名,部門.部門名稱,工資.總工資 from 員工,部門,工資 where year(考勤月份)=

42、'2011' order by 部門名稱 sp_helptext view1建立視圖view2,查詢所有員工的員工編號(hào)、姓名和平均工資；create view view2 as select 員工.員工編號(hào),員工.姓名,avg(基本工資)as 平均工資from 員工,工資where 員工.員工編號(hào)=工資.員工編號(hào)GROUP BY 員工.員工編號(hào),員工.姓名sp_helptext view2（4）利用SQL對(duì)視圖進(jìn)行查詢9.對(duì)視圖進(jìn)行插入、刪除、修改數(shù)據(jù)操作插入：刪除：修改：10.編寫一個(gè)自定義函數(shù)，將一個(gè)字符串作為自變量，返回顛倒順序的字符串（2）帶參數(shù)的函數(shù)，通過員工編號(hào)返回

43、員工信息create function 員工情況(個(gè)人編號(hào)varchar(4)returns tableasreturn(select 姓名,職務(wù),辦公電話from 員工where 員工編號(hào)=個(gè)人編號(hào))select * from 員工情況('4002')11. 編寫利用游標(biāo)進(jìn)行數(shù)據(jù)處理的存儲(chǔ)過程。并在存儲(chǔ)過程中應(yīng)用事務(wù)的概念。自己確定過程的具體要求，然后編寫出來。（1）利用游標(biāo)讀取員工表中的每一行數(shù)據(jù)：declare cur_員工cursorglobal scroll dynamicforselect 員工編號(hào),姓名,職務(wù),文化程度,籍貫,身份證號(hào),手機(jī)號(hào)碼from 員工ope

44、n cur_員工fetch next from cur_員工while fetch_status=0beginfetch next from cur_員工end（2）創(chuàng)建帶參數(shù)的存儲(chǔ)過程，從表員工，家庭關(guān)系，工資的連接中返回輸入的員工姓名的員工編號(hào)、姓名、籍貫、婚姻狀況、基本工資、家人姓名、與自己的關(guān)系、工作單位信息。create proc yg_info 姓名varchar(20) asselect 員工.員工編號(hào),員工.姓名,籍貫,婚姻狀況,基本工資,家庭關(guān)系.姓名,與本人關(guān)系,工作單位from 員工inner join 家庭關(guān)系on 員工.員工編號(hào)=家庭關(guān)系.員工編號(hào)join 工資on

45、家庭關(guān)系.員工編號(hào)=工資.員工編號(hào)where 員工.姓名like 姓名exec yg_info '陳禮貌'12. 編寫一個(gè)實(shí)現(xiàn)修改表的觸發(fā)器，實(shí)現(xiàn)完整性控制。(1)更新員工表信息use HrSysCREATE TRIGGER workerid ON 員工FOR UPDATEASIF (update(員工編號(hào)) BEGINDECLARE BHO CHAR(4),BHN CHAR(4) SELECT BHO=員工編號(hào)FROM DELETED SELECT BHN=員工編號(hào)FROM INSERTEDUPDATE 工資 SET 工資.員工編號(hào)=BHN WHERE 工資.員工編號(hào)=BHO

46、UPDATE 考勤 SET 考勤.員工編號(hào)=BHN WHERE 考勤.員工編號(hào)=BHOUPDATE 獎(jiǎng)懲 SET 獎(jiǎng)懲.員工編號(hào)=BHN WHERE 獎(jiǎng)懲.員工編號(hào)=BHO UPDATE 工作經(jīng)歷 SET 工作經(jīng)歷.員工編號(hào)=BHN wHERE 工作經(jīng)歷.員工編號(hào)=BHOUPDATE 員工調(diào)動(dòng) SET 員工調(diào)動(dòng).員工編號(hào)=BHN WHERE 員工調(diào)動(dòng).員工編號(hào)=BHOUPDATE 家庭關(guān)系 set 家庭關(guān)系.員工編號(hào)=BHN WHERE 家庭關(guān)系.員工編號(hào)=BHO ENDUPDATE dbo.員工SET 員工編號(hào)='1012' WHERE 員工編號(hào)='1002'

47、（2）刪除員工編號(hào)CREATE TRIGGER YGDELON dbo.員工FOR DELETEAS BEGIN DECLARE BH CHAR(4) SELECT BH=員工編號(hào)FROM DELETED IF EXISTS (SELECT * FROM 部門WHERE 部門負(fù)責(zé)人編號(hào)=BH) UPDATE dbo.部門 SET 部門負(fù)責(zé)人編號(hào)=NULL WHERE 部門負(fù)責(zé)人編號(hào)=BHEnddelete from 員工where 員工編號(hào)='2001'13. 設(shè)置服務(wù)器身份驗(yàn)證模式。用命令創(chuàng)建若干不同驗(yàn)證模式的登錄賬戶。（1）在控制面板了面創(chuàng)建用戶（2）創(chuàng)建Windows身份驗(yàn)證和SQL Server的登錄賬戶14. 將部分登錄賦予服務(wù)器角色。然后撤消。