数 据 库 原 理课设.docx

1、数 据 库 原 理课设数 据 库 原 理课 程 设 计 任 务 书班级： 计软112 学号： 1106120203 姓名： 杜素玉 成绩： 电子与信息工程学院计算机科学系7数据库原理课程设计报告7.1设计题目名称 学校人力资源管理系统7.2 系统概述 系统功能： 实现学校部门信息和教职工信息管理；􀀂 实现教师的工资管理；􀀂 实现教师的奖惩信息管理；􀀂 创建存储过程根据提交的部门编号，查看该部门的部长； 创建存储过程根据提交员工编号和工资发放时间，查看该员工的薪资信息 创建存储过程根据员工提交的员工编号和奖罚时间,查看该员工的奖罚信息

2、48578; 创建触发器当增加、删除教职工和修改教职工部门信息时自动修改相应部门的职工人 数；􀍅􀀂 建立数据库相关表之间的参照完整性约束。7.3系统功能模块设计说明：学校人力资源管理系统包含部门管理、员工管理、工资管理、奖惩管理。其中，部门管理包含部门名称、人数等；员工管理包含员工学历、家庭关系等；工资管理包含基本工资、实发工资等；奖惩管理包含编号、原因等。7.4数据库概念结构设计 在需求分析阶段所得到的应用需求应该首先抽象为信息世界的结构，才能更好地、 更准确地用某一DBMS实现这些需求。 概念结构的主要特点是：1. 能真实、充分地反应现实世界，包括事物和

3、事物之间的联系，能满足用户对数据的处理要求，是对现实世界的一个真实模型；2. 易于理解，从而可以用它和不熟悉计算机的用户交换意见，用户的积极参与是数据库设计成功的关键；3. 易于更改，当应用环境和应用要求改变时，容易对概念模型修改和扩充；4. 易于向关系、网状、层次等各种数据模型转换。概念结构是各种数据模型的共同基础，它比数据模型更独立于机器、更抽象，从而更加 稳定。描述概念模型的有力工具是E-R模型。E-R图：7.5数据库逻辑结构设计逻辑结构设计：下面将E-R图转换为关系模型：员工信息（员工编号，员工姓名，年龄，性别，出生年月，婚姻状态，政治面貌，学历，部门编号，职务，联系方式，备注） 主键

4、：员工编号； 外码：部门编号；部门信息（部门编号，部门名称，部门人数） 主键：部门编号；奖惩信息（奖惩编号，员工编号，奖罚时间，奖罚原因，奖罚地点，备注） 主键：奖惩编号， 外码：员工编号；薪资信息（薪资编号，员工编号，基本工资，福利，奖金，计算方法，实发工资，发放日期）， 主键：薪资编号； 外码：员工编号。员工工资（薪资编号，员工编号）主键：员工编号，薪资编号员工部门（员工编号，部门编号）主键：员工编号，薪资编号员工惩罚（员工编号，奖惩编号）主键：员工编号，奖惩编号7.6数据库物理设计员工信息表字段名字段类型小数位数是否主键是否为空是否外键备注列名数据类型是否员工编号int否员工姓名varc

5、har否年龄int否性别varchar否出生年月Datetime否婚姻状态varchar否政治面貌varchar否学历varchar否部门编号int否是职务varchar否联系方式varchar否备注varchar部门信息表字段名字段类型小数位数是否主键是否为空是否外键备注部门编号int是否部门名称varchar否部门人数int否薪资信息表字段名字段类型小数位数是否主键是否为空是否外键备注薪资编号int是否员工编号int否是基本工资int否福利int否奖金int否计算方法int否实发工资int否发放日期int否奖惩信息表字段名字段类型小数位数是否主键是否为空是否外键备注奖惩编号Int是否员工编

6、号Int否是奖罚原因Varchar否奖罚地点Varchar否奖罚时间Datetime否备注varchar1. 视图视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。分布式查询也可用于定义使用多个异类源数据的视图。2. 存储过程存储过程就是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫数据

7、库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。存储过程的优点：（1）.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。（2）.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 （3）.存储过程可以重复使用,可减少数据库开发人员的工作量（4）.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。3. 触发器触发器是一种特殊类型的存储过程，它

8、不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如Update、 Insert、 Delete 这些操作时，SQL Server 就会自动执行触发器所定义的SQL 语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有其它许多不同的功能：(1) 强化约束(Enforce restriction)触发器能够实现比CHECK 语句更为复杂的约束。(2) 跟踪变化Auditing changes触发器

9、可以侦测数据库内的操作，从而不允许数据库中未经许可的指定更新和变化。(3) 级联运行(Cascaded operation)。触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例如，某个表上的触发器中包含有对另外一个表的数据操作(如删除，更新，插入)而该操作又导致该表上触发器被触发。(4) 存储过程的调用(Stored procedure invocation)。在物理结构中，数据的基本存取单位是存储记录。有了逻辑结构以后，就可以设计存储记录结构，一个存储记录可以与多个逻辑记录相对应。存储记录就包括记录的组成，数据项的类型和长度等。7.7程序运行环境和开发环境运行环境：操作系

10、统：windows7运行软件：SQL开发环境：操作系统：windows7开发软件：SQL7.8主要模块程序流程图7.9主要功能实现的程序段1、创建高校教职工管理数据库：CREATE DATABASE 高校教职工管理ON( NAME=高校教职工管理DAT, FILENAME=D:SQL高校教职工管理DAT.MDF, SIZE=5, MAXSIZE=20, FILEGROWTH=2)LOG ON( NAME=高校教职工管理LOG, FILENAME=D:SQL高校教职工管理LOG.LDF, SIZE=5, MAXSIZE=20, FILEGROWTH=2)-2、创建表CREATE TABLE 部门

11、信息表(部门编号INT PRIMARY KEY,部门名称VARCHAR(20) UNIQUE NOT NULL,部门人数INT CHECK(部门人数=0) NOT NULL,)-CREATE TABLE 员工信息表(员工编号INT NOT NULL PRIMARY KEY,员工姓名VARCHAR(20) NOT NULL,年龄INT CHECK(年龄=1) NOT NULL,性别VARCHAR(2) CHECK(性别IN (男,女) NOT NULL,出生年月Datetime NOT NULL,婚姻状态VARCHAR(4) CHECK(婚姻状态IN (已婚,未婚) NOT NULL,政治面貌V

12、ARCHAR(20) CHECK(政治面貌IN (清白,团员,党员) NOT NULL,学历VARCHAR(20) NOT NULL,部门编号INT NOT NULL,职务VARCHAR(20) NOT NULL,联系方式VARCHAR(11) UNIQUE NOT NULL,备注VARCHAR(500),FOREIGN KEY (部门编号) REFERENCES 部门信息表(部门编号)-CREATE TABLE 薪资信息表(薪资编号INT PRIMARY KEY,员工编号INT NOT NULL,基本工资INT CHECK(基本工资=0) NOT NULL,福利INT CHECK(福利=0)

13、 NOT NULL,奖金INT CHECK(奖金=0) NOT NULL,计算方法VARCHAR(50) NOT NULL,实发工资INT CHECK(实发工资=0), 发放日期Datetime NOT NULL,FOREIGN KEY(员工编号) REFERENCES 员工信息表(员工编号)-CREATE TABLE 奖惩信息表(奖惩编号INT PRIMARY KEY,员工编号INT NOT NULL, 奖罚时间Datetime NOT NULL,奖罚原因VARCHAR(100) NOT NULL,奖罚地点VARCHAR(20) NOT NULL,备注VARCHAR(500),FOREIGN

14、 KEY(员工编号) REFERENCES 员工信息表(员工编号) )-3、创建视图：-视图员工基本信息：CREATE VIEW 员工基本信息(员工姓名,部门名称,职务,联系方式,薪资)AS SELECT 员工姓名,部门名称,职务,联系方式,实发工资FROM 员工信息表,部门信息表,薪资信息表WHERE 员工信息表.部门编号=部门信息表.部门编号 AND 员工信息表.员工编号=薪资信息表.员工编号-视图：各部门部长信息CREATE VIEW 部门部长信息(部门编号,部门名称,员工编号,员工名字)AS SELECT 部门信息表.部门编号,部门名称,员工编号,员工姓名FROM 员工信息表,部门信息

15、表WHERE 员工信息表.部门编号=部门信息表.部门编号 AND 员工信息表.职务=部长-4、创建存储过程-存储过程LOOKWAGE:根据员工提交的员工编号和工资发放时间，查看该员工的薪资信息，- 包括员工编号，姓名，实发薪资，和发放日期。CREATE PROCEDURE LOOKWAGEID INT,TIME DatetimeASSELECT 员工信息表.员工编号,员工姓名,实发工资,发放日期FROM 员工信息表,薪资信息表WHERE 员工信息表.员工编号=薪资信息表.员工编号 AND 员工信息表.员工编号=ID AND 发放日期=TIME-存储过程LOOKREWARDS:根据员工提交的员工

16、编号和奖罚时间,查看该员工的奖罚信息，- 包括员工编号，姓名，奖罚原因，奖罚时间。CREATE PROCEDURE LOOKREWARDSID INT,TIME DatetimeASSELECT 员工信息表.员工编号,员工姓名,奖罚原因,奖罚时间FROM 员工信息表,奖罚信息表WHERE 员工信息表.员工编号=奖罚信息表.员工编号 AND 员工信息表.员工编号=ID AND 发放日期=TIME-存储过程Find_Minister：根据提交的部门编号，查看该部门的部长CREATE PROCEDURE Find_MinisterID INT ASSELECT 部门信息表.部门编号,部门信息表.部门

17、名称,员工信息表.员工编号,员工姓名FROM 员工信息表,部门信息表WHERE 员工信息表.部门编号=部门信息表.部门编号 AND 员工信息表.职务=部长 -5、创建触发器-触发器workerid:要求员工信息表中的员工编号被修改时，薪资信息表- 奖惩信息表中的员工编号也被修改。CREATE TRIGGER WORKERID ON 员工信息表FOR UPDATEAS BEGIN IF(UPDATE(员工编号) BEGIN UPDATE 薪资信息表 SET 薪资信息表.员工编号=(SELECT 员工编号FROM inserted) WHERE 薪资信息表.员工编号=(SELECT 员工编号FRO

18、M deleted) UPDATE 奖惩信息表 SET 奖惩信息表.员工编号=(SELECT 员工编号FROM inserted) WHERE 奖惩信息表.员工编号=(SELECT 员工编号FROM deleted) ENDEND-触发器CHECK_EXIST：- 向员工信息表插入一条记录时，检查该记录的部门信息在部门信息表- 是否存在，若不存在则不允许插入。CREATE TRIGGER CHECK_EXISTON 员工信息表FOR INSERTAS IF EXISTS(SELECT * FROM inserted a WHERE a.部门编号NOT IN (SELECT 部门信息表.部门编号

19、FROM 部门信息表) BEGIN RAISERROR(该部门不存在！,16,1) ROLLBACK TRANSACTION -撤销刚才的操作，恢复到原来的状态END -触发器COUNT_STAFF- 向员工信息表插入一条记录时,该员工所在的部门人数加.CREATE TRIGGER COUNT_STAFFON 员工信息表AFTER INSERTAS BEGIN UPDATE 部门信息表 SET 部门人数=部门人数+1 WHERE 部门编号IN (SELECT 部门编号FROM inserted)END-触发器departid:要求部门信息中的部门编号被修改时，员工信息表中员工-所属部门编号也被

20、修改。CREATE TRIGGER departid ON 部门信息表FOR UPDATEAS BEGIN IF(UPDATE(部门编号) UPDATE 员工信息表 SET 员工信息表.部门编号=(SELECT 部门编号FROM inserted) WHERE 员工信息表.部门编号=(SELECT 部门编号FROM inserted)END-触发器CHECK_DELETE：删除员工记录时，该员工所在部门人数减.CREATE TRIGGER CHECK_DELETEON 员工信息表FOR DELETEAS BEGIN UPDATE 部门信息表 SET 部门人数=部门人数-1 WHERE 部门编号IN (SELECT 部门编号FROM inserted)END