Employee Management System
1. Project Title: Employee Management System
The objective is to build a simple system that manages employee details and assigns them to
departments.
The system should allow the user to:
- Add and update employee information.
- Manage department assignments.
2. Database Design
Entities:
1. Employees: Contains information about employees.
2. Departments: Contains details about departments.
Tables:
1. Employees
- employee_id (Primary Key)
- first_name
- last_name
- email
- phone
- hire_date
- department_id (Foreign Key)
2. Departments
- department_id (Primary Key)
- department_name
3. SQL Queries
Table Creation Queries:
-- Table for Departments
CREATE TABLE Departments (
department_id INT PRIMARY KEY AUTO_INCREMENT,
department_name VARCHAR(50)
);
-- Table for Employees
CREATE TABLE Employees (
employee_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(15),
hire_date DATE,
department_id INT,
FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);
Sample Data Insertion
-- Insert into Departments
INSERT INTO Departments (department_name) VALUES
('HR'),
('IT'),
('Finance');
-- Insert into Employees
INSERT INTO Employees (first_name, last_name, email, phone, hire_date, department_id)
VALUES
('John', 'Doe', 'johndoe@[Link]', '123-456-7890', '2022-05-10', 1),
('Jane', 'Smith', 'janesmith@[Link]', '987-654-3210', '2023-06-15', 2),
('Emily', 'Johnson', 'emilyj@[Link]', '456-789-1234', '2023-07-01', 3);
Sample Queries
1. List all employees:
SELECT first_name, last_name, email, phone, hire_date FROM Employees;
2. List all departments:
SELECT department_name FROM Departments;
3. List employees in a specific department:
SELECT E.first_name, E.last_name, D.department_name
FROM Employees E
JOIN Departments D ON E.department_id = D.department_id
WHERE D.department_name = 'IT';
4. Count the number of employees in each department:
SELECT D.department_name, COUNT(E.employee_id) AS num_employees
FROM Departments D
LEFT JOIN Employees E ON D.department_id = E.department_id
GROUP BY D.department_name;
