Scribd
Upload
2 views3 pages

Employee_Management_System_Database_Design

This document outlines the database design for an Employee Management System (EMS), including an ER diagram, schema definitions for various tables such as Departments, Roles, Employees, Salaries, Leaves, and Users. It also details indexing strategies, sample data seeding, and examples of stored procedures and triggers for managing employee data. The design ensures relationships between tables through foreign keys and provides a structure for efficient data management.

Uploaded by

Kekeletso Moremoholo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views3 pages

Employee_Management_System_Database_Design

This document outlines the database design for an Employee Management System (EMS), including an ER diagram, schema definitions for various tables such as Departments, Roles, Employees, Salaries, Leaves, and Users. It also details indexing strategies, sample data seeding, and examples of stored procedures and triggers for managing employee data. The design ensures relationships between tables through foreign keys and provides a structure for efficient data management.

Uploaded by

Kekeletso Moremoholo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

Employee Management System Database Design

1. Introduction

This document provides a complete database design for the Employee Management System (EMS), including an

Entity-Relationship (ER) diagram, schema definitions, indexes, data seeding examples, stored procedures, and triggers.

2. Entity-Relationship (ER) Diagram

3. Database Schema

CREATE TABLE Departments (


id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE Roles (


id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE Employees (


id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
department_id INT,
role_id INT,
salary_id INT,
FOREIGN KEY (department_id) REFERENCES Departments(id),
FOREIGN KEY (role_id) REFERENCES Roles(id),
FOREIGN KEY (salary_id) REFERENCES Salaries(id)
);

CREATE TABLE Salaries (


id INT PRIMARY KEY AUTO_INCREMENT,
amount DECIMAL(10,2) NOT NULL,
employee_id INT UNIQUE,
FOREIGN KEY (employee_id) REFERENCES Employees(id)
);

CREATE TABLE Leaves (


id INT PRIMARY KEY AUTO_INCREMENT,
employee_id INT,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
status ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
FOREIGN KEY (employee_id) REFERENCES Employees(id)
);

CREATE TABLE Users (


id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
employee_id INT UNIQUE,
is_admin BOOLEAN DEFAULT FALSE,
FOREIGN KEY (employee_id) REFERENCES Employees(id)
);

4. Indexing

CREATE INDEX idx_department ON Employees(department_id);


CREATE INDEX idx_role ON Employees(role_id);
CREATE INDEX idx_salary ON Employees(salary_id);

5. Sample Data Seeding


INSERT INTO Departments (name) VALUES ('HR'), ('IT'), ('Finance');
INSERT INTO Roles (title) VALUES ('Manager'), ('Developer'), ('Accountant');
INSERT INTO Salaries (amount) VALUES (60000), (80000), (50000);
INSERT INTO Employees (first_name, last_name, department_id, role_id, salary_id) VALUES
('John', 'Doe', 1, 1, 1),
('Jane', 'Smith', 2, 2, 2),
('Alice', 'Johnson', 3, 3, 3);

6. Stored Procedure Example

DELIMITER //
CREATE PROCEDURE GetEmployeeDetails (IN emp_id INT)
BEGIN
SELECT Employees.first_name, Employees.last_name, Departments.name AS department,
Roles.title AS role, Salaries.amount AS salary
FROM Employees
JOIN Departments ON Employees.department_id = Departments.id
JOIN Roles ON Employees.role_id = Roles.id
JOIN Salaries ON Employees.salary_id = Salaries.id
WHERE Employees.id = emp_id;
END //
DELIMITER ;

7. Trigger Example

DELIMITER //
CREATE TRIGGER after_employee_insert
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
INSERT INTO Users (username, password_hash, employee_id)
VALUES (CONCAT(NEW.first_name, '.', NEW.last_name), 'default_hash', NEW.id);
END //
DELIMITER ;

You might also like