数据库设计与实现实训报告.doc

一、实训目的1、 掌握数据库的建立。2、 掌握表的建立和使用。3、 掌握视图的建立和使用。4、 掌握存储过程的建立和使用。5、 掌握用户自定义函数的建立和使用。6、 掌握触发器的建立和使用。二、实训条件和环境1、设备条件：l Pentium II 450(推荐使用Pentium III 600)l PCI 2.1 总线l 256MB内存l 16GB硬盘l 512KB 二级缓存l CD-ROMl 网络适配器（非ISA接口）l 4MB显示适配器l Super VGA (SVGA)显示器l 鼠标l 声卡与扬声器2、软件环境：l Windows XP Professionall Microsoft SQL Server 2005 Developer Edition三、实训内容1、建立数据库代码：USEmasterGOCREATE DATABASE BlueHill ON PRIMARY( NAME = BlueHill,FILENAME = C:bluehillBlueHill.mdf, SIZE = 30720KB,MAXSIZE = UNLIMITED,FILEGROWTH=2048KB)LOG ON(NAME = BlueHill_log,FILENAME = C:bluehillBlueHill_log.ldf, SIZE = 1024KB, MAXSIZE = 20480KB,FILEGROWTH = 5%)GO2、建立表创建表：外键：CHECK约束：3、建立视图(1) 查看员工基本信息视图 viwEmpCommonInfoSELECT dbo.tblEmployee.EmployeeID, dbo.tblEmployee.Name, dbo.tblEmployee.Email, dbo.tblEmployee.Telephone, dbo.tblEmployee.LoginName, dbo.tblEmployee.Password, dbo.tblEmployee.BasicSalary,dbo.tblEmployee.OnboardDate,dbo.tblDepartment.DeptName,dbo.tblDepartment.ManagerID,dbo.tblEmployee.VacationRemain,dbo.tblEmployee.SelfIntroFROM dbo.tblEmployee INNERJO IN dbo.tblDepartment ON dbo.tblEmployee.DeptID = dbo.tblDepartment.DeptID(2) 查看员工信息和所属部门名称视图 viwEmployeeListSELECT dbo.tblEmployee.EmployeeID, dbo.tblEmployee.Name, dbo.tblEmployee.LoginName, dbo.tblEmployee.Email, dbo.tblDepartment.DeptName, dbo.tblEmployee.Title, dbo.tblEmployee.TelephoneFROM dbo.tblEmployee INNER JOIN dbo.tblDepartment ON dbo.tblEmployee.DeptID = dbo.tblDepartment.DeptID(3) 查看部门信息视图 viwDepartmentSELECT DeptID, DeptName, ManagerID, DescriptionFROM dbo.tblDepartment(4) 查看部门经理信息视图 viwManagerInfoSELECT dbo.tblEmployee.Name AS ManagerName, dbo.tblEmployee.Email AS ManagerEmail, dbo.tblDepartment.DeptName, dbo.tblDepartment.ManagerID, dbo.tblDepartment.DeptIDFROM dbo.tblDepartment INNER JOIN dbo.tblEmployee ON dbo.tblDepartment.ManagerID = dbo.tblEmployee.EmployeeID AND dbo.tblDepartment.DeptID = dbo.tblEmployee.DeptID(5) 查看员工加班申请信息视图 viwAllOTRegSELECT dbo.tblOvertime.OvertimeID, dbo.tblOvertime.EmployeeID, dbo.tblOvertime.SubmitTime, dbo.tblOvertime.StartTime, dbo.tblOvertime.EndTime, dbo.tblOvertime.Reason, dbo.tblOvertime.Status, dbo.tblOvertime.Type, dbo.tblOvertime.Denyreason, dbo.tblOvertime.Hours,dbo.tblEmployee.Name AS ApproverName, dbo.tblOvertimeType.Description AS TypeNameFROM dbo.tblOvertime INNER JOIN dbo.tblEmployee ON dbo.tblOvertime.ApproverID = dbo.tblEmployee.EmployeeID INNER JOINdbo.tblOvertimeType ON dbo.tblOvertime.Type = dbo.tblOvertimeType.Type(6) 查看已提交的加班申请信息视图 viwSubmittedOTRegSELECT dbo.tblOvertime.OvertimeID, dbo.tblOvertime.EmployeeID, dbo.tblOvertime.SubmitTime, dbo.tblOvertime.StartTime, dbo.tblOvertime.EndTime, dbo.tblOvertime.Type, dbo.tblOvertime.Reason, dbo.tblOvertime.Status, dbo.tblOvertime.Hours, dbo.tblEmployee.Name AS EmployeeName, dbo.tblOvertime.ApproverID, tblEmployee_1.Name AS ApproverNameFROM dbo.tblOvertime INNER JOIN dbo.tblEmployee ON dbo.tblOvertime.EmployeeID = dbo.tblEmployee.EmployeeID INNER JOINdbo.tblEmployee AS tblEmployee_1 ON dbo.tblOvertime.ApproverID = tblEmployee_1.EmployeeID(7) 查看员工加班信息视图 viwOTListSELECT dbo.tblOvertime.OvertimeID, dbo.tblEmployee.Name, dbo.tblOvertime.SubmitTime, dbo.tblOvertime.Reason, dbo.tblOvertime.Status, dbo.tblOvertimeType.DescriptionFROM dbo.tblOvertime INNER JOIN dbo.tblOvertimeType ON dbo.tblOvertime.Type = dbo.tblOvertimeType.Type INNER JOINdbo.tblEmployee ON dbo.tblOvertime.EmployeeID = dbo.tblEmployee.EmployeeID(8) 查看员工请假申请信息视图 viwAllLeaveRegSELECT dbo.tblLeave.LeaveID, dbo.tblLeave.EmployeeID, dbo.tblLeave.SubmitTime, dbo.tblLeave.StartTime, dbo.tblLeave.EndTime, dbo.tblLeave.Reason, dbo.tblLeave.Hours, dbo.tblLeave.Status, dbo.tblLeave.ApproverID, dbo.tblLeave.DenyReason, dbo.tblEmployee.Name AS ApproverNameFROM dbo.tblLeave INNER JOIN dbo.tblEmployee ON dbo.tblLeave.ApproverID = dbo.tblEmployee.EmployeeID(9) 查看已提交的请假申请信息视图 viwSubmittedLvRegSELECT dbo.tblLeave.LeaveID, dbo.tblLeave.EmployeeID, dbo.tblLeave.SubmitTime, dbo.tblLeave.StartTime, dbo.tblLeave.EndTime, dbo.tblLeave.Reason, dbo.tblLeave.Hours, dbo.tblLeave.Status, dbo.tblLeave.ApproverID, dbo.tblEmployee.Name AS EmployeeName, tblEmployee_1.Name AS ApproverNameFROM dbo.tblLeave INNER JOIN dbo.tblEmployee ON dbo.tblLeave.EmployeeID = dbo.tblEmployee.EmployeeID INNER JOIN dbo.tblEmployee AS tblEmployee_1 ON dbo.tblLeave.ApproverID = tblEmployee_1.EmployeeID(10) 查看员工请假信息视图 viwLeaveListSELECT dbo.tblLeave.LeaveID, dbo.tblEmployee.Name, dbo.tblLeave.SubmitTime, dbo.tblLeave.Reason, dbo.tblLeave.Status, dbo.tblLeave.DenyReasonFROM dbo.tblLeave INNER JOIN dbo.tblEmployee ON dbo.tblLeave.EmployeeID = dbo.tblEmployee.EmployeeID(11) 查看假期的具体日期视图 viwHolidaySELECT HolidayDateFROM dbo.tblHoliday(12) 查看员工考勤情况视图 viwAllAttendSELECT dbo.tblAttendance.AttendID, dbo.tblAttendance.EmployeeID, dbo.tblEmployee.DeptID, dbo.tblAttendance.Type, dbo.tblAttendance.DateFROM dbo.tblAttendance INNER JOIN dbo.tblEmployee ON dbo.tblAttendance.EmployeeID = dbo.tblEmployee.EmployeeID(13) 查看员工考勤信息视图 viwAttendanceSELECT dbo.tblAttendance.AttendID, dbo.tblEmployee.Name, dbo.tblAttendance.Date, dbo.tblAttendance.TypeFROM dbo.tblAttendance INNER JOIN dbo.tblEmployee ON dbo.tblAttendance.EmployeeID = dbo.tblEmployee.EmployeeID(14) 查看公司策略信息视图 viwCompanyPolicySELECT Name, DataFROM dbo.tblConfig(15) 查看员工业绩评定信息视图 viwPerformanceSELECT PerformID, EmployeeID, ReviewerID, SubmitTime, PerformYear, PerformSeason, Status, SelfScore, LastEditTime, ReviewScore, SelfComment, ReviewCommentFROM dbo.tblPerformance(16) 查看员工业绩评定中的子项目视图 viwPerformItemSELECT dbo.tblPerformance.PerformID, dbo.tblPerformItem.PerformItemID, dbo.tblPerformItem.ObjectContent, dbo.tblPerformItem.SelfScore, dbo.tblPerformItem.ReviewScore, dbo.tblPerformance.PerformYear, dbo.tblPerformance.PerformSeasonFROM dbo.tblPerformance INNER JOIN dbo.tblPerformItem ON dbo.tblPerformance.PerformID = dbo.tblPerformItem.PerformID(17) 查看员工基本薪资视图 viwEmpBasicSalarySELECT EmployeeID, Name, BasicSalaryFROM dbo.tblEmployee(18) 查看员工薪资历史信息视图 viwHistoeySalarySELECT dbo.tblSalary.EmployeeID, dbo.tblSalary.SalaryID, dbo.tblSalary.SalaryTime, dbo.tblSalary.OvertimeSalary, dbo.tblSalary.OtherSalary, dbo.tblSalary.AbsenseSalary, dbo.tblEmployee.BasicSalary, dbo.tblDepartment.DeptID, dbo.tblDepartment.DeptName, dbo.tblEmployee.Name AS EmployeeNameFROM dbo.tblSalary INNER JOIN dbo.tblEmployee ON dbo.tblSalary.EmployeeID = dbo.tblEmployee.EmployeeID INNER JOIN dbo.tblDepartment ON dbo.tblEmployee.DeptID = dbo.tblDepartment.DeptID AND dbo.tblEmployee.EmployeeID = dbo.tblDepartment.ManagerID4、 建立存储过程(1) 更新员工部门编号 spChangeEmployeeDeptCREATE PROCEDURE spChangeEmployeeDept EmpID int, DeptName nchar(10)ASBEGIN update dbo.tblEmployee set DeptID=(select DeptID from dbo.tblDepartment whereDeptName=DeptName) where EmployeeID=EmpIDEND(2) 根据员工编号更新员工自我介绍信息spUpdateSelfIntroCREATE PROCEDURE spUpdateSelfIntro EmpID int, SelfIntro char(200)ASBEGINupdate dbo.tblEmployee set SelfIntro=SelfIntro where EmployeeID=EmpIDEND(3) 删除一个员工 spDeleteEmployeeCREATE PROCEDURE spDeleteEmployee EmpID intASBEGIN delete from dbo.tblEmployeeID where EmployeeID=EmpIDEND(4) 获取部门员工详细信息 spDeptAllEmployeeCREATE PROCEDURE spDeptAllEmployee DeptName nchar(10)ASBEGIN select * from dbo.viwEmployeeList where DeptName=DeptNameEND(5) 根据员工登录名获取员工编号 spGetEmployeeIDCREATE PROCEDURE spGetEmployeeID LoginName char(20), EmpID int outputASBEGIN DECLARE result int SELECT EmployeeID from dbo.tblEmployee where LoginName = LoginName set result = rowcount SELECT EmpID=(SELECT EmployeeID from dbo.tblEmployee where LoginName = LoginName)return resultEND(6) 根据员工登录名获取员工登录密码 spGetEmpPasswordCREATE PROCEDURE spGetEmpPassword LoginName char(20), PWord char(20) outputASBEGIN DECLARE result int SELECT Password from dbo.tblEmployee where LoginName = LoginName set result = rowcount SELECT PWord=(SELECT Password from dbo.tblEmployee where LoginName = LoginName)return resultEND(7) 根据员工编号获取员工登录密码 spGetEmpPasswordByIDCREATE PROCEDURE spGetEmpPasswordByID EmpID int, PWord char(20) outputASBEGIN DECLARE result int SELECT Password from dbo.tblEmployee where EmployeeID = EmpID set result = rowcount SELECT PWord=(SELECT Password from dbo.tblEmployee where EmployeeID=EmpID)return resultEND(8) 根据员工编号更新员工登录密码 spUpdatePasswordCREATE PROCEDURE spUpdatePassword EmpID int, PWord char(20) ASBEGIN DECLARE result int update dbo.tblEmployee set Password = PWord where EmployeeID=EmpID set result = rowcount return resultEND(9) 实现员工在部门间的转移 spMoveEmpBetweenDeptCREATE PROCEDURE spMoveEmpBetweenDept FromDeptName nchar(10), ToDeptName nchar(10)ASBEGIN update dbo.tblEmployee set DeptID=(select DeptID from dbo.tblDepartment where DeptName = ToDeptName) where DeptID = (select DeptID from dbo.tblDepartment where DeptName = FromDeptName)END(10) 添加一个新部门 spCreateDepartmentCREATE PROCEDURE spCreateDepartment DeptName char(10), Description nvarchar(50), ManagerID intASBEGIN insert into dbo.tblDepartment(DeptName,Description,ManagerID) values(DeptName,Description,ManagerID)END(11) 删除一个指定部门 spDeleteDepartmentCREATE PROCEDURE spDeleteDepartment DeptName nchar(10) ASBEGIN delete from dbo.tblDepartment where DeptName=DeptName END(12) 获取所有部门的部门编号和部门名称spGetAllDepartmentCREATE PROCEDURE spGetAllDepartmentASBEGIN select DeptID,DeptName from dbo.tblDepartment END(13) 插入一条已提交的加班申请 spAddOTReqCREATE PROCEDURE spAddOTReq OvertimeID int, EmpID int, Type tinyint, SubmitTime datetime, StartTime datetime, EndTime dateTime, ApproverID int, Hours int, Reason nchar(100)ASBEGIN DECLARE result int insert into dbo.tblOvertime(OvertimeID,EmployeeID,Type,SubmitTime,StartTime,EndTime,ApproverID,Hours,Reason,Status) values(OvertimeID,EmpID,Type,SubmitTime,StartTime,EndTime,ApproverID,Hours,Reason,已提交)set result = rowcountreturn resultEND(14) 更新一条加班申请记录的状态 spChangeOTStatusCREATE PROCEDURE spChangeOTStatus OvertimeID int, Status nchar(10), DenyReason nchar(100)ASBEGIN DECLARE result int update dbo.tblOvertime set Status=Status,DenyReason=DenyReason where OvertimeID = OvertimeID set result=rowcount return resultEND(15) 取消一条加班申请 spwebCancelOTReqCREATE PROCEDURE spwebCancelOTReq OvertimeID intASBEGIN DECLARE result int delete from dbo.tblOvertime where OvertimeID=OvertimeID set result=rowcount return resultEND(16) 汇总部门员工已批准的加班信息 spGetDeptOTSummaryCREATE PROCEDURE spGetDeptOTSummary DeptID int, Type tinyint, StartTime datetime, EndTime datetimeASBEGINDECLARE result intselect o.* from dbo.tblOvertime as o inner join dbo.tblEmployee as e on o.EmployeeID=e.EmployeeID where e.DeptID=DeptID AND o.Type=Type AND o.StartTime=StartTime AND o.EndTime=EndTime and o.Status=已批准set result = rowcount return resultEND(17) 汇总部门员工加班信息 spOTbyGroupCREATE PROCEDURE spOTbyGroupDeptName nvarchar(20)ASBEGINselect dbo.tblOvertime.* from dbo.tblOvertime inner join dbo.tblEmployee on dbo.tblOvertime.EmployeeID=dbo.tblEmployee.EmployeeID inner join dbo.tblDepartment on dbo.tblEmployee.DeptID=dbo.tblDepartment.DeptID where dbo.tblDepartment.DeptName=DeptName END(18) 插入一条提交的请假申请 spAddLeaveReqCREATE PROCEDURE spAddLeaveReq LeaveID int, EmpID int, SubmitTime datetime, StartTime datetime, EndTime dateTime, ApproverID int, Hours int, Reason nchar(100)ASBEGINDECLARE result intinsert into dbo.tblLeave(LeaveID,EmployeeID,SubmitTime,StartTime,EndTime,ApproverID,Hours,Reason,Status) values(LeaveID,EmpID,SubmitTime,StartTime,EndTime,ApproverID,Hours,Reason,已提交)set result=rowcountEND(19) 更新一条请假申请记录的状态 spChangeLeaveStatusCREATE PROCEDURE spChangeLeaveStatus LeaveID int, Status nchar(10), DenyReason nchar(100)ASBEGINDECLARE result intupdate dbo.tblLeave set Status=Status,DenyReason=DenyReason where LeaveID = LeaveIDset result=rowcountEND(20) 取消一条请假申请 spCancelLeaveReqCREATE PROCEDURE spCancelLeaveReq LeaveID intASBEGINDECLARE result intdelete from dbo.tblLeave where LeaveID=LeaveIDset result=rowcountEND5、 建立用户自定义函数（1） 按部门名称得到本部门所有员工信息 udfDeptAllEmployeeCREATE FUNCTION udfDeptAllEmployee ( DeptName nchar(10)returns tableasreturn (select * from dbo.viwEmployeeList where DeptName=DeptName)（2） 按部门名称得到部门ID udfGetDeptIDbyDeptNameCREATE FUNCTION udfGetDeptIDbyDeptName( DeptName nchar(10) ) returns int as begin declare DeptID int select DeptID=(select DeptID from dbo.tblDepartment where DeptName=DeptName) return DeptID End（3） 按部门编号得到部门名称 udfGetDeptNamebyDe