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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

27、l4部門負責人編號Char(3)Not null員工：編號字段名稱數(shù)據(jù)結構約束條件1員工編號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身份證號varchar(20)null11辦公電話varchar(30)null12手機號碼varchar(30)null13部門編號intnull14職務

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

29、額smallintNull5處罰事由Varchar(200)Null6處罰金額smallintnull考勤：編號字段名稱數(shù)據(jù)結構約束條件1考勤編號Char(3)Not null primary key2考勤月份DatetimeNot null3員工編號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遲到時間（分）TinyintNull11早退時間(分)Tinyin

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

31、tinyintNot null家庭關系：編號說明數(shù)據(jù)結構約束條件1編號tinyintNot nullprimary key2員工編號intNot null3姓名Varchar(50)Null4性別Char(2)Null5年齡tinyintNull6與本人關系Varchar(20)Null7工作單位Varchar(40)null工作經(jīng)歷：編號說明數(shù)據(jù)結構約束條件1編號tinyintNot nullprimary key2員工編號intNot null3開始日期Char(10)null4終止日期Char(10)null5學校/單位名稱Varchar(50)null6職務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 部門(部門編號int primary key,部門名稱varchar(40) not null,部門職能描述varchar(400),部門負責人編號char(4)not null,)create table 員工(員工編號char(4) primary key,姓名varchar(50) not null,性別char(2),民族varchar(40),生日varchar(20),政治面貌varc

34、har(40),文化程度varchar(40),婚姻狀況varchar(20),籍貫varchar(60),身份證號varchar(20),辦公電話varchar(30),手機號碼varchar(40), 部門編號int not null,職務varchar(20),狀態(tài)tinyint,工資decimal(9,2)create table 考勤(考勤編號 int primary key,考勤月份datetime not null,員工編號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),遲到時間Tinyint,早退時間Tinyint,法定假日加班Decimal(4,1),周末加班Decimal(4,1),日常加班Decimal(4,1),)create table 員工調(diào)動(調(diào)動編號char(2) primary key,員工編號char(4) not null,調(diào)動前職務varchar(20),調(diào)動后職務varchar(20),調(diào)動日期char(10),批復部門編號int)create table 工資(工資編號char(3) primary key,員工編號char(4) not null,

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

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

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

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

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

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

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

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

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

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

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

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