1. 数据库概述

我们将设计一个简单的员工信息数据库，包含以下功能：

• 员工管理：添加、更新、删除员工信息。
• 部门管理：添加、更新、删除部门信息。
• 薪资管理：记录员工的薪资变动。
• 报表生成：生成部门和员工的统计报表。
• 权限管理：不同角色的用户有不同的操作权限。

2. 数据库设计

2.1 表结构设计

• 员工表（employees）：

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(100),
    salary NUMERIC,
    hire_date DATE,
    department_id INTEGER REFERENCES departments(id)
);

• 部门表（departments）：

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    budget NUMERIC
);

• 薪资变动表（salary_changes）：

CREATE TABLE salary_changes (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(id),
    old_salary NUMERIC,
    new_salary NUMERIC,
    change_date DATE
);

• 用户表（users）：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    role VARCHAR(50) NOT NULL
);

2.2 索引设计

• 员工表的索引：

CREATE INDEX idx_employee_name ON employees (name);
CREATE INDEX idx_employee_department ON employees (department_id);

• 薪资变动表的索引：

CREATE INDEX idx_salary_change_employee ON salary_changes (employee_id);

3. 功能实现

3.1 员工管理

• 添加员工：

INSERT INTO employees (name, position, salary, hire_date, department_id)
VALUES ('Alice', 'Software Engineer', 75000, '2023-01-01', 1);

• 更新员工信息：

UPDATE employees SET salary = 80000 WHERE id = 1;

• 删除员工：

DELETE FROM employees WHERE id = 1;

3.2 部门管理

• 添加部门：

INSERT INTO departments (name, budget) VALUES ('Engineering', 1000000);

• 更新部门信息：

UPDATE departments SET budget = 1200000 WHERE id = 1;

• 删除部门：

DELETE FROM departments WHERE id = 1;

3.3 薪资管理

• 记录薪资变动：

INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)
VALUES (1, 75000, 80000, '2023-10-01');

3.4 报表生成

• 部门统计报表：

SELECT d.name, COUNT(e.id) AS employee_count, SUM(e.salary) AS total_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.name;

• 员工薪资变动报表：

SELECT e.name, sc.old_salary, sc.new_salary, sc.change_date
FROM salary_changes sc
JOIN employees e ON sc.employee_id = e.id;

3.5 权限管理

• 创建角色：

CREATE ROLE manager;
CREATE ROLE employee;

• 授予权限：

GRANT SELECT, INSERT, UPDATE ON employees TO manager;
GRANT SELECT ON employees TO employee;

• 创建用户并分配角色：

CREATE USER alice WITH PASSWORD 'password';
GRANT manager TO alice;

4. 高级功能

4.1 触发器

• 自动记录薪资变动：

CREATE FUNCTION log_salary_change() RETURNS TRIGGER AS $
BEGIN
    INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)
    VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER salary_change_trigger
AFTER UPDATE OF salary ON employees
FOR EACH ROW EXECUTE FUNCTION log_salary_change();

4.2 存储过程

• 批量更新员工薪资：

CREATE PROCEDURE update_salaries(percentage NUMERIC)
LANGUAGE plpgsql
AS $
BEGIN
    UPDATE employees SET salary = salary * (1 + percentage / 100);
END;
$;

4.3 视图

• 员工详细信息视图：

CREATE VIEW employee_details AS
SELECT e.id, e.name, e.position, e.salary, d.name AS department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;