数据库设计：员工信息管理系统

1 设计要求

        1、包括五个或五个以上基本表，其中至少有一个是基于实体之间联系的基本表；

         2、实现员工基本信息、部门单位、学历、婚姻状况、职称、工作岗位等信息的增加、删除、修改、查询（包括五种或五种以上单条件查询和多条件复合查询）、统计功能；

        3、设置用户权限管理，不同权限的用户对不同信息操作的权限要区分;

2  实现步骤

2.1 需求分析

        通过设计数据库实现对企业员工的基本信息、职位、所属项目、工资等数据进行存储、查询和管理。数据库需要存储员工的基本信息如员工编号、身份证号、联系方式、所属部门信息、工资信息等，还要对员工参与的项目信息进行存储，如项目编号，项目名称，项目起止时间等。

        系统开发先通过ER图设计模型，然后根据设计好的概念模型进行建库建表。

        并且加入相关数据，在数据库中完成查询管理工作。

 2.2 概念结构设计：ER图

 

 2.3 逻辑结构设计

①关系模式设计

一个公司部门中可以包含多个员工，每个员工只能属于一个公司部门，因此员工与公司部门的关系为N：1.

一个公司部门内可以多个职位，每个特定职位只能属于一个部门，因此公司部门与部门职位的关系为1：N.

每个员工可以属于不同部门的相同职位，及同一个职位可以对应多个员工，而每个员工只能由一个职位，因此部门职位与员工的关系为1：N.

每个员工都对应一个工资单，每个工资单只能属于一个员工，因此员工表与工资表的关系是1：1.

每个员工可以参与多个项目，每个项目可以由多个员工参与，因此员工表与项目表的关系是N：M.

②按照三范式标准检验

没有传递依赖，是3NF

附表 1  员工在位部门的相关属性列表

实体或联系	属性
员工基本信息 单位部门 项目 部门职位 工资	姓名，身份证号，员工编号，性别，手机号码，婚姻状况 部门编号，负责人工号，部门名称 项目编号，项目名称，开启时间，结束时间 职位编号，职位名称 基本工资，绩效奖金，补贴，加班费，出纳编号

附表2 员工基本信息表（employee）

字段名	描述	类型	宽度	可空	主键	外键	默认值	依赖关系	备注
employee_id	员工编号	int	8		Y
phone	电话号码	varchar	20				NULL
cart_id	身份证号	varchar	20				NULL
sex	性别	varchar	10				NULL
marriage	婚姻状况	varchar	20	Y
employee_name	姓名	varchar	50

附表3 单位部门表(department)

字段名	描述	类型	宽度	可空	主键	外键	默认值	依赖关系	备注
Department_id	部门编号	int	8		Y
Department_name	部门名称	varchar	50				NULL
Chairman_number	负责人工号	varchar	20				NULL

附表4 部门职位表(POSITION)

字段名	描述	类型	宽度	可空	主键	外键	默认值	依赖关系	备注
position_id	职位编号	Int	8		Y
position_name	职位名称	varchar	50				NULL
department_id	部门编号	Int	20			Y	NULL

附表5 项目表(project)

字段名	描述	类型	宽度	可空	主键	外键	默认值	依赖关系	备注
project_id	项目编号	Int	8		Y
project_name	项目名称	varchar	20				NULL
start_time	开始时间	varchar	20				NULL
end_time	结束时间	varchar	20	Y

附表6 工资表(salary_info)

字段名	描述	类型	宽度	可空	主键	外键	默认值	依赖关系	备注
bill_id	出纳编号	Int	8		Y
employee_id	员工编号	Int	8			Y
base_pay	基本工资	Int					0
merit_pay	绩效奖金	Int		Y			0
subsidy_pay	补贴	Int		Y			0
overtime_pay	加班费	Int		Y			0

附表7 员工和项目关系表

字段名	描述	类型	宽度	可空	主键	外键	默认值	依赖关系	备注
emp_project_r_id	员工参与项目编号	Int			Y
employee_id	员工编号	int	8			Y
project_id	项目编号	Int	8			Y

2.4 物理结构设计 ：表的结构设计和索引设计部分

4.1、表结构

1：1的关系如员工信息表和工资表，可将员工信息表的主键设置为工资表的外键，也可将工资表的主键设置为员工信息表的外键

1：N的关系如公司部门表和员工信息表，公司部门表为1的一方，员工信息表为N的一方，需要将公司部门表的主键设为员工信息表的外键。

M：N的关系如员工信息表和项目表，需要建立一个联系表（员工和项目关系表）将员工信息表的主键和项目表的主键分别设为员工和项目关系表的外键。

4.2、表索引

根据员工姓名建立索引

根据部门名称建立索引

根据项目名称建立索引

根据职位名称建立索引

2.5 SQL语句实施: 建表、输入数据

CREATE DATABASE `employee_management` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `employee_management`;

CREATE TABLE `employee` (
  `employee_id` int(8) COMMENT '员工id',
  `department_id` int(8) NULL COMMENT '部门id',
  `position_id` int(8) NULL COMMENT '职位id',
  `employee_name` varchar(50) NULL COMMENT '员工名称',
  `cart_id` int NULL COMMENT '身份证号',
  `sex` varchar(10) NULL COMMENT '性别',
  `phone` varchar(20) NULL COMMENT '手机号',
  `email` varchar(30) NULL COMMENT '邮箱',
  PRIMARY KEY (`employee_id`)
);


CREATE TABLE `department`(
  `department_id` int(8) PRIMARY KEY,
  `department_name` varchar(50),
  `chairman_number` varchar(20)
);


CREATE TABLE `POSITION` (
  `position_id` int(8) PRIMARY key,
  `department_id` int(8),
  `position_name` varchar(20)
);

CREATE TABLE `project` (
  `project_id` int(8) PRIMARY key,
  `project_name` VARCHAR(20),
  `start_time` varchar(20),
  `end_time` varchar(20)
);


#员工和项目关系表

CREATE TABLE `emp_project_info`(
  `emp_project_r_id` int PRIMARY KEY,
  `employee_id` int(8),
  `project_id` int(8)
);

CREATE TABLE `salary_info` (
  `bill_id` int(8) PRIMARY key,
  `employee_id` int(8),
  `base_pay` int,
  `insurance_pay` int,
  `merit_pay` int,
  `subsidy_pay` int,
  `overtime_pay` int
);

#添加表之间的约束

ALTER TABLE `salary_info` 
ADD FOREIGN KEY (`employee_id`) REFERENCES `employee` (`employee_id`);

ALTER TABLE `salary_info` 
ADD FOREIGN KEY (`employee_id`) REFERENCES `employee` (`employee_id`);

ALTER TABLE `position` 
ADD FOREIGN KEY (`department_id`) REFERENCES `department` (`department_id`);


ALTER TABLE `emp_project_info` 
ADD FOREIGN KEY (`employee_id`) REFERENCES `employee` (`employee_id`),
ADD FOREIGN KEY (`project_id`) REFERENCES `project` (`project_id`);

ALTER TABLE `employee` 
ADD FOREIGN KEY (`position_id`) REFERENCES `position` (`position_id`);

#3 创建索引
CREATE INDEX idx_emp_name ON employee(employee_name);

CREATE INDEX idx_dept_name ON department(department_name);

CREATE INDEX idx_project_name ON project(project_name);

ALTER TABLE `position` 
ADD INDEX `idx_position_name`(`position_name`) USING BTREE;

#插入数据
INSERT INTO `department` (`department_id`, `department_name`, `chairman_number`) VALUES (1001, '开发部', '202101');
INSERT INTO `department` (`department_id`, `department_name`, `chairman_number`) VALUES (1002, '设计部', '202102');

INSERT INTO `position` (`position_id`, `department_id`, `position_name`) VALUES (2001, 1001, '技术总监');
INSERT INTO `position` (`position_id`, `department_id`, `position_name`) VALUES (2002, 1002, '设计总监');

INSERT INTO `employee` VALUES (202101, 1001, 2001, '张三', 2204554, '男', '21343545', '23478957@qq.com');
INSERT INTO `employee` VALUES (202102, 1001, 2001, '李四', 2203425, '女', '23475453', '23184672@qq.com');
INSERT INTO `employee` VALUES (202103, 1001, 2001, '王五', 2204545, '男', '32313435', '12323233@qq.com');
INSERT INTO `employee` VALUES (202104, 1002, 2002, '刘六', 2206756, '女', '12313445', '32847239@qq.com');
INSERT INTO `employee` VALUES (202105, 1002, 2002, '赵七', 2204735, '男', '32490294', '31287463@qq.com');

INSERT INTO `project` VALUES (3001, 'xx电子平台', '2021.01', '2021.03');
INSERT INTO `project` VALUES (3002, 'xx电商', '2021.04', '2021.07');

INSERT INTO `emp_project_info` (`emp_project_r_id`, `employee_id`, `project_id`) VALUES (4001, 202101, 3001);
INSERT INTO `emp_project_info` (`emp_project_r_id`, `employee_id`, `project_id`) VALUES (4002, 202102, 3001);
INSERT INTO `emp_project_info` (`emp_project_r_id`, `employee_id`, `project_id`) VALUES (4003, 202103, 3001);
INSERT INTO `emp_project_info` (`emp_project_r_id`, `employee_id`, `project_id`) VALUES (4004, 202104, 3002);
INSERT INTO `emp_project_info` (`emp_project_r_id`, `employee_id`, `project_id`) VALUES (4005, 202105, 3002);

INSERT INTO `salary_info` VALUES (5001, 202101, 8000, 1000, 2000, 4000, 3000);
INSERT INTO `salary_info` VALUES (5002, 202102, 7500, 1000, 2000, 3000, 3000);
INSERT INTO `salary_info` VALUES (5003, 202103, 8000, 1000, 2000, 4000, 3000);
INSERT INTO `salary_info` VALUES (5004, 202104, 7000, 1000, 2000, 2000, 3000);
INSERT INTO `salary_info` VALUES (5005, 202105, 9000, 1000, 2000, 1000, 3000);

2.6 查询，创建视图操作(SQL)

#1.查询全部员工的平均工资
SELECT AVG(base_pay+insurance_pay+merit_pay+subsidy_pay+overtime_pay) avg_salary FROM salary_info;

#2.查询开发部员工的平均工资

SELECT AVG(base_pay+insurance_pay+merit_pay+subsidy_pay+overtime_pay) avg_salary_dep 
FROM salary_info WHERE employee_id IN(SELECT employee_id FROM employee,department 
WHERE employee.department_id=department.department_id AND department.department_id=1001);

#3.查询各个项目平均补贴
SELECT AVG(subsidy_pay) FROM salary_info WHERE employee_id 
IN(SELECT employee_id FROM emp_project_info WHERE project_id=3001);
SELECT AVG(subsidy_pay) FROM salary_info WHERE employee_id 
IN(SELECT employee_id FROM emp_project_info WHERE project_id=3002);

#4.查询工资最高的员工的姓名
SELECT employee_name FROM employee e 
JOIN salary_info s
ON e.employee_id=s.employee_id
HAVING MAX(base_pay+insurance_pay+merit_pay+subsidy_pay+overtime_pay);

#5.查询技术总监的平均工资
SELECT AVG(base_pay+insurance_pay+merit_pay+subsidy_pay+overtime_pay) avg_pos FROM salary_info 
WHERE employee_id IN(SELECT employee_id FROM employee 
WHERE position_id IN(SELECT position_id FROM `position` WHERE `position_name`='技术总监'));

#创建视图：
CREATE VIEW s1 AS SELECT AVG(base_pay+insurance_pay+merit_pay+subsidy_pay+overtime_pay) avg_salary FROM salary_info;

3 实验总结

        经过这次实验，我学习如何正确运用数据库，加深了对数据库思想与方法的理解，通过这次实验复习、巩固数据库知识，提高数据库的实践能力。深一步了解了数据库的设计，学会创建ER图，能够更加熟练的完成对具体某一个管理系统的数据库分析和概念结构和逻辑结构设计能力，并通过数据库管理软件完成实施操作。