数据库程序设计.doc

数据库原理及应用课程设计课程设计题目：工资管理系统数据库2008/2009学年第一学期第 19 周学生专业：信息与计算科学专业姓名： 龚佳丽 学生学号： 08102106 学生班级：081021指导老师：游运 2009年12月31日 目 录1、时间和地址.22、课程设计目的.23、课程设计要求.24、课程设计题目.25、局部E-R图、整体E-R图.36、创建数据库.77、创建表.78、插入数据109、数据处理基本操作.1210、创建视图.1311、创建索引.1412、创建存储过程.1513、创建触发器.1614、课程设计心得与体会.17一、课程设计时间及地点时间：2009年12月27日至2010年12月31日地点：科技楼903机房二、课程设计目的：通过本次课程设计让学生能够综合运用所学的关系数据库原理知识解决并能设计一个实际问题，进一步掌握数据库原理的相关理论和数据库的设计实现过程，进一步提高我们学生自己的分析问题和解决问题的能力以及学生的都收能力。三、课程设计要求：1对各个系统进行系统功能需求分析2. 数据库设计分析阶段，进行详细的数据库需求分析，进行概念数据库的设计，画出数据库的E-R图（局部和整体E-R图）3. 设计出详细的逻辑数据库结构，将各个实体和联系转化为相应的二维表即关系模式，指定各个关系的主关键字和外部关键字，并对各个关系的约束加以限定4. 通过企业管理器或是查询分析器实现各个二维关系（建议最好用SQL代码实现），要求建立相关的索引5. 根据系统功能需求设计相应的查询视图6. 要求根据系统功能需求建立存储过程7. 根据功能需求建立相应的触发器以保证数据的一致性8. 通过建立用户和权限分配实现数据库一定的安全性，考虑数据库的备份与恢复（此内容选作）四、课程设计题目工资管理系统数数据库的设计与实现描述：设计一个工资管理系统，实现下列功能： 1工资信息的维护（包括录入，修改和删除）；2工资信息的核算和统计；3工资信息的报表输出；分析：根据题目要求及课程设计要求来设计工资管理系统数据库。先是系统需求分析；、概念结构设计；、逻辑结构设计；、物理结构设计；、数据库实施；、数据库运行与维护；为下面的E-R图做准备。五、局部E-R图和整体E-R图（一）、局部E-R图E-R方法是实体-联系方的简称。它是描述现实世界概念结构模型的有效方法。设计局部E-R模型的关键是正确划分实体和属性。局部E-R模式设计要遵循以下两条原则、实体具有描述信息，而属性没有。、属性不能与其他实体具有联系，联系只能发生在实体之间。部门描述部门名称部 门上级部门编号部门编号 局部E-R图部门表生日民族性别员工姓名员工编号政治面貌合同有效期文化水平备注信息婚姻状况所在部门ID家庭住址员 工上岗日期身份证号毕业院校工作证号手机号户口所在地办公室电话职务局部E-R图员工表性 别姓 名员工编号家 庭年 龄配偶工作单位关 系局部E-R图家庭表开始日期结束日期员工ID工作经历担任职务工作单位局部E-R图工作经历表病假天数出差天数全勤天数员工号考勤日期三类加班天数事假天数二类加班天数矿工天数法定休假天数考 勤一类加班天数年休假天数备注信息迟到时间倒休假天数早退时间局部E-R图考勤表总体评价员工编号考评月份奖励事由备注信息考 评奖励金额处罚金额处罚事由局部E-R图考评表（二）、整体E-R： 完成局部E-R图模型设计后，就是把局部E-R图集成起来就是全局E-R图，有两种：一种是多元集成法，一种是二元集成法。 部 门 部门所属员 工工作状况工作时间所属家 庭工作经历考 勤配偶同属单位工龄工作表现总 评整体E-R六创建数据库CREATE DATABASE GongziGO七、创建表(一)、根据E-R图创建表1、创建部门表CREATE TABLE Bumen(BumenID int PRIMARY KEY, -部门编号BumenName varchar(40) NOT NULL, -部门名称Describe varchar(400)， -部门描述UpperID int NOT NULL -上级部门编号)GO2、创建员工表CREATE TABLE Yuangong(yg_IDint PRIMARY KEY,-员工编号yg_Name varchar(50) NOT NULL, -员工姓名Sex char(2), -性别Nationalityvarchar(40),-民族Birthvarchar(20),-生日Political_Partyvarchar(40), -政治面貌Culture_Levelvarchar(40),-文化水平Marital_Conditionvarchar(20),-婚姻状况Family_Placevarchar(60),-家庭住址Id_Cardvarchar(20),-身份证号BadgeIDvarchar(40),-工作证号Office_phonevarchar(30),-办公电话Mobilevarchar(30),-手机号Files_Keep_Orgvarchar(100), -毕业院校Hukouvarchar(100),-户口所在地HireDate varchar(20),-上岗日期BumenIDint, -所在部门IDTitle varchar(20),-职务Contract_Durationvarchar(20),-合同有效期Memovarchar(200),-备注信息FOREIGN KEY （BumenID）REFERENCES Bumen (BumenID)GO3、创建家庭表CREATE TABLE Family( Id tinyint PRIMARY KEY, -自动增加的ID yg_ID int NOT NULL,-员工编号 Name varchar(50) NOT NULL,-姓名 Sex char(2), -性别 Age tinyint, -年龄 Relationshipvarchar(20), -关系 WorkingOrgvarchar(40), -配偶工作单位 FOREIGN KEY （yg ID）REFERENCES yuangong (yg_ID)GO4、创建工作经历表CREATE TABLE Jingli( Idtinyint PRIMARY KEY, -自动增加的ID yg_ID int NOT NULL,-员工IDStartDate char(10), -开始日期 EndDate char(10), -结束日期 Unitvarchar(50), -工作单位 Title varchar(20), -担任职务FOREIGN KEY （yg_ID）REFERENCES Yuangong (yg_ID)GO5、创键考勤表CREATE TABLE Kaoqin( yg_ID int PRIMARY KEY, -员工号CheckDatechar(10) NOT NULL, -考勤日期 qqDays decimal(4,1), -全勤天数 ccDaysdecimal(4,1), -出差天数bjDaysdecimal(4,0), -病假天数 sjDaysdecimal(4,0), -事假天数/ kgDaysdecimal(4,0), -旷工天数 fdxjDays decimal(4,0), -法定休假天数 nxjDaysdecimal(4,0), -年修假天数 dxjDaysdecimal(4,0), -倒修假天数/ cdMinutestinyint, -迟到时间（分钟）/ ztMinutestinyint, -早退时间（分钟） ot1Daysdecimal(4,0), -一类加班天数/ ot2Daysdecimal(4,0), -二类加班天数/ ot3Days decimal(4,0), -三类加班天数/ Memovarchar(200) -备注信息/ FOREIGN KEY （yg_ID）REFERENCES Yuangong (yg_ID) GO6、创建考评表CREATE TABLE Pingjia( EvaMonthchar(10) NOT NULL, -考评月份 yg_ID int NOT NULL, -员工编号 ztEva varchar(200), -总体评价 jlReason varchar(200), -奖励事由 jlAmountsmallint, -奖励金额 cfReasonvarchar(200), -处罚事由 cfAmountsmallint, -处罚金额 Memovarchar(200), -备注信息 Idtinyint , -自动增加的ID FOREIGN KEY （ld）REFERENCES Jingli (ld) FOREIGN KEY （ld）REFERENCES Family (ld) FOREIGN KEY （yg_ID）REFERENCES Kaoqin (yg_ID)GO八、输入数据1、（1）对部门表进行输入数据INSERT INTO Bumen (BumenID,BumenName,Describe,UpperID) SELECT 11,人事部,管理员工信息,1 UNION SELECT 12,研发部,研发新产品机构,1 UNION SELECT 13,销售部,对外销售产品,1 UNION SELECT 14,接待部,接待外来人员,1 UNION SELECT 1,高层部,接待外来人员,1GO（2）、对员工表进行输入数据INSERT INTO Yuangong (yg_ID,yg_Name,Sex,Nationality,Birth,Political_Party,Culture_Level,Marital_Condition,Family_Place,Id_Card,BadgeID,Office_phone,Mobile,Files_Keep_Org,Hukou,HireDate,BumenID,Title,Contract_Duration,Memo) SELECT 1111,张三,男,汉,19870621,团员,大学,未婚,南临路边100号07114225107975816116,东大,南昌,20080101,11,科员,3,无 UNION SELECT 1112,李四,男,汉,19820211,党员,大学,已婚,南临路边101号07114225207915816532,南大,南昌,20020101,12,科员,5,无 UNION SELECT 1113,王五,男,汉,19640322,党员,大学,已婚,南临路边102号071142253无,西大,南昌,20000101,1,科长,10,无 UNION SELECT 1114,小六,男,汉,19870321,无,大学,未婚,南临路边103号07114225407915876532,北大,南昌,20090101,13,科员,2,无 UNION SELECT 1115,张二,男,汉,19840321,团员,研究生,已婚,南临路边104号071142255无,东大,南昌,20060101,14,科员,2,无 UNION SELECT 1116,李三,女,汉,19820201,党员,研究生,已婚,南临路边105号07114225607915816732,南大,南昌,20050101,12,科员,4,无 UNION SELECT 1117,王四,男,汉,19870322,党员,大学,已婚,南临路边106号071142257无,西大,南昌,20060101,1,科长,2,无 UNION SELECT 1118,小五,女,汉,19890321,无,高中,未婚,南临路边107号07114225807918576532,北大,南昌,20090101,13,科员,2,无GO（3）、对家庭表输入数据INSERT INTO Family (Id,yg_ID,Name,Sex,Age,Relationship,WorkingOrg) SELECT 11,1111,张三,男,22,夫妻,江西经贸公司 UNION SELECT 12,1112,李四,男,27,夫妻,东华理工大学 UNION SELECT 13,1113,王五,男,45,夫妻,江西教育厅 UNION SELECT 14,1114,小六,男,22,夫妻,对外汉语学校 UNION SELECT 15,1115,张二,男,25,夫妻,产业厅 UNION SELECT 16,1116,李三,女,27,夫妻,东华理工大学 UNION SELECT 17,1117,王四,男,22,夫妻,东山小学 UNION SELECT 18,1118,小五,女,21,夫妻,无GO（4）对工作经历表输入数据INSERT INTO Jingli (Id,yg_ID,StartDate,EndDate,Unit,Title) SELECT 21,1111,20070101,至今,东华理工大学,科员 UNION SELECT 22,1112,20050101,至今,东华理工大学,科员 UNION SELECT 23,1113,20040101,至今,东华理工大学,科长 UNION SELECT 24,1114,20060101,至今,东华理工大学,科员 UNION SELECT 25,1115,20080101,至今,东华理工大学,科员 UNION SELECT 26,1116,20040101,至今,东华理工大学,科员 UNION SELECT 27,1117,20060101,至今,东华理工大学,科长 UNION SELECT 28,1118,20080101,至今,东华理工大学,科员GO（5）对考勤表输入数据INSERTINTOKaoqin(CheckDate,yg_ID,qqDays,ccDays,bjDays,sjDays,kgDays,fdxjDays,nxjDays,dxjDays,cdMinutes,ztMinutes,ot1Days,ot2Days,ot3Days,Memo)SELECT1月,1111,20,2,0,1,0,8,0,0,120,120,1,2,1,无UNIONSELECT1月,1112,19,2,1,1,0,8,0,0,150,130,3,2,3,无UNIONSELECT1月,1113,21,2,0,0,0,8,0,0,120,120,1,2,1,无UNIONSELECT1月,1114,18,3,0,0,1,8,0,0,120,120,2,3,1,无UNIONSELECT1月,1115,20,2,1,0,0,8,0,0,120,120,0,1,1,无UNIONSELECT1月,1116,15,5,1,1,0,8,0,0,120,120,0,0,4,无UNIONSELECT1月,1117,19,2,1,1,0,8,0,0,120,120,4,3,3,无UNIONSELECT1月,1118,21,2,0,0,0,8,0,0,120,120,1,2,2,无GO（6）对考评表输入数据INSERT INTO Pingjia (EvaMonth,yg_ID,ztEva,jlReason,jlAmount,cfReason,cfAmount,Memo) SELECT 一月,1111,好,加班次数多,500,旷工一次,100,无 UNION SELECT 一月,1112,好,加班次数多,1000,迟到时间长,50 ,无UNION SELECT 一月,1113,好,到勤率高,500,上进少,100 ,无UNION SELECT 一月,1114,一般,加班次数多,500,旷工次数多,400,无 UNION SELECT 一月,1115,好,到勤率高,500,上进少,100,无 UNION SELECT 一月,1116,好,公差多,1000,旷工次数多,200 ,无UNION SELECT 一月,1117,好,加班次数多,1000,旷工次数多,300 ,无UNION SELECT 一月,1118,好,加班次数多,1200,无,0,无GO（7）添加管理员触发事件EXECInsertAdminDefaUserName=chogchog,Pwd=123456,UserType=1GO九、数据处理基本操作DELETEFROMFamilyWHERE(yg_ID=(SELECTyg_IDFROMJingliWHEREStartDate=20070101)GOINSERTINTOFamilyValues(11,1111,张三,男,25,夫妻,江西经贸公司)GOUPDATEFamilySETAge=22WHERE(yg_ID=(SELECTyg_IDFROMJingliWHEREId=21)GO十、视图视图是一张虚表，其数据不进行存储，而是来自基本表，只是数据库中存储其定义。视图在概念上与基本表等同，用户可以在视图上在定义视图，可以对视图进行查询、删除、和更新等操作。使用视图有如下优点：利于数据保密，对不同的用户定义不同的视图，使用户只能看到与自己有关的数据。简化查询操作，为复杂的查询建立一个视图，用户不必键入复杂的查询语句，。保证数据的逻辑独立性，对于视图的操作，只依赖于视图的定义。1、创建视图用SQL命令创建视图，其语法格式为：CREATE VIEW () AS 其中，为可选项，省略时，视图的列名由子查询的结果决定。但是在下列情况不可省略：视图由多个表连接得到，在不同的表中存在同名列，则需指定列名。当视图的列名为表达式或库函数的计算结果，而不是单纯的属性名时，则可不视图定义后，对视图查询时再进行排序。2、用SQL命令修改视图，其语法格式为：ALTER VIEW () AS 3、用SQL命令删除视图，其语法格式为：DROP VIEW 创建一个考勤总表视图：CREATE VIEW dbo.v_KaoqinSumASSELECT LEFT(dbo.Kaoqin.CheckDate, 7) AS CheckMonth, dbo.Yuangong.yg_Name, SUM(dbo.Kaoqin.qqDays) AS Sumqq, SUM(dbo.Kaoqin.ccDays) AS SumCc, SUM(dbo.Kaoqin.bjDays) AS SumBj, SUM(dbo.Kaoqin.sjDays) AS SumSj, SUM(dbo.Kaoqin.kgDays) AS SumKg, SUM(dbo.Kaoqin.fdxjDays) AS SumFdxj, SUM(dbo.Kaoqin.nxjDays) AS SumNxj, SUM(dbo.Kaoqin.dxjDays) AS SumDxj, SUM(dbo.Kaoqin.cdMinutes) AS SumCd, SUM(dbo.Kaoqin.ztMinutes) AS SumZt, SUM(dbo.Kaoqin.ot1Days) AS SumOt1, SUM(dbo.Kaoqin.ot2Days) AS SumOt2, SUM(dbo.Kaoqin.ot3Days) AS SumOt3, dbo.Yuangong.BumenIDFROM dbo.Kaoqin INNER JOIN dbo.Yuangong ON dbo.Kaoqin.Yg_ID = dbo.Yuangong.Yg_IDGROUP BY LEFT(dbo.Kaoqin.CheckDate, 7), dbo.Yuangong.yg_Name, dbo.Kaoqin.Yg_ID, dbo.Yuangong.BumenIDGO十一、创建索引索引是数据库随机检索的常用手段，它实际上就是记录的关键字与其相应地址的对应表。使用索引的作用有：加快查询速度，保证行的唯一性。索引有如下几类：聚集索引与非聚集索引;聚集索引按照su9oyi的字段排列记录，并且按照排好的顺序将记录存储在表中。而非聚集索引的排列结果并不存储在表中，而是存储在另外的位置。唯一索引；唯一索引表示表中每一个索引值只对应唯一的数据记录。复合索引；复合索引时将两个字段或多个字段组合起来建立的索引，单独的字段允许有重复的值。1、创建索引（1）使用SQL命令创建索引，其格式为：CREATE UNIQUE CLUSTER INDEX ON (次序,次序)其中：UNIQUE表示建立唯一索引；CLUSTER表示建立聚集索引；“次序”表示用来指定索引值的排列顺序，可用ASC（升序）或DESC(降序)，缺省为升序。（2）对所有表建立起索引建立员工表的索引（其他表的索引也是一样的格式）CREATEUNIQUEINDEXworkerONYuangong(yg_ID,yg_Name,Culture_Level,Mobile,Title)GO2、删除索引其语法格式为：DROP INDEX 数据表名.索引名比如删除职称表的索引 DROP INDEX zhichengindex3、使用索引应该注意的是：（1）改变表中的数据时，索引将自动更新。 （2）索引建立后，在查询使用该列时，系统将自动使用索引进行查询。（3）索引数目无限制，但索引越多，更新数据的速度越慢；对于仅用于查询的表可多建立索引，对于数据更新频繁的表则应少建索引。十二、创建存储过程存储过程是存储在SQL server数据库中的一种编译对象。它是一组为了完成特定功能的SQL语句集，这些SQL语句集经编译后存储在数据库中，可以被客户机管理工具、应用程序、其他存储过程调用，同时可以传递参数。用户通过指定存储过程的名字并给参数来执行它。使用存储过程有一下优点：、模块化的程序设计；、高效率的执行；、减少网络流量；、可以作为安全机制使用。创建存储过程之前应该考虑的问题：、在一个批处理中，CREATE PROCEDURE语句不能与其他SQL语句合并在一起；、数据库所有者具有默认的创建存储过程的权限，它可把该权限传递给其他用户；、存储过程作为数据库对象，其命名必须符合标示符的命名规则；、只能在当前数据库中创建属于当前数据库的存储过程。创建存储过程的语法格式为：CREATE PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT,nWITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPRIONFOR REPLICATION AS sql_statement ,.n 其中procedure_name创建存储过程的名字，parameter 存储过程的参数，data_type 是参数的类型，VARYING指定有OUTPUT参数支持的结果集，仅应用于游标型参数，default是参数的缺省值，OUTPUT表明参数是一个返回参数，RECOMPILE指明SQL server并不保存该存储过程的执行计划，该存储过程每执行一次都要重新编译，ENCRYPTION 表明SQL server 加密了，FOR REPLICATION 选项指明为复制创建的存储过程不能再订阅服务器上执行，只有在创建过滤存储过程时，才使用该选项。AS 指明该存储过程将要执行的动作。（1）、创建员工存储过程CREATEPROCEDUREInsertRecordDefa(yg_IDint,yg_Namevarchar(50),Sexchar(2),Nationalityvarchar(40),Birthvarchar(20),Political_Partyvarchar(40),Culture_Levelvarchar(40),Marital_Conditionvarchar(20),Family_Placevarchar(60),Id_Cardvarchar(20),BadgeIDvarchar(40)=无,Office_phonevarchar(30),Mobilevarchar(30),Files_Keep_Orgvarchar(100),Hukouvarchar(100),HireDatevarchar(20),BumenIDint,Titlevarchar(20),Contract_Durationvarchar(20),Memovarchar(200)=无)A