Employee Management System

A comprehensive full-stack Employee Management System built as a course project for Database Management Systems (DBMS). This application demonstrates practical implementation of database concepts, RESTful APIs, and modern web technologies to manage employee data, departments, projects, and analytics.

Features

• Dashboard Overview: Real-time statistics including total employees, departments, average salary, and active projects
• Employee Management: Complete CRUD operations (Create, Read, Update, Delete) for employee records
• Department & Project Tracking: Manage departments and assign employees to projects
• Interactive Charts: Visual analytics for department distribution and salary analysis using Chart.js
• Search Functionality: Real-time search and filtering of employee data
• Responsive Design: Modern UI built with Tailwind CSS for desktop and mobile compatibility
• RESTful API: Backend API endpoints for seamless data interaction
• Database Analytics: Advanced SQL queries for reporting and insights

Technologies Used

Backend

• Node.js: JavaScript runtime for server-side development
• Express.js: Web framework for building RESTful APIs
• MySQL2: MySQL database driver for Node.js
• CORS: Cross-Origin Resource Sharing middleware

Frontend

• HTML5: Markup language for web pages
• Tailwind CSS: Utility-first CSS framework for styling
• Chart.js: JavaScript library for data visualization
• Vanilla JavaScript: Client-side scripting for interactivity

Database

• MySQL: Relational database management system
• SQL: Structured Query Language for database operations

Prerequisites

Before running this application, ensure you have the following installed:

• Node.js (version 14 or higher)
• MySQL Server (version 8.0 or higher)
• Git (for cloning the repository)

Installation and Setup

1. Clone the Repository

git clone <repository-url>
cd employee-management-system

2. Install Dependencies

npm install

3. Database Setup

1. Start your MySQL server
2. Create a new database and run the SQL script:

   -- Run the contents of DBMS_PROJECT.sql in your MySQL client
   -- This will create the database, tables, and insert sample data

3. Update database credentials in server.js if necessary:

   const db = mysql.createConnection({
     host: 'localhost',
     user: '****',  // Update with your MySQL username
     password: '*******',  // Update with your MySQL password
     database: 'employee_management'
   });

4. Start the Application

node server.js

The application will be available at:

• Frontend Dashboard: http://localhost:3000/index.html
• API Base URL: http://localhost:3000/api

Usage

1. Access the Dashboard: Open http://localhost:3000/index.html in your web browser
2. View Statistics: The dashboard displays key metrics and charts
3. Manage Employees:
   • Click "Add Employee" to create new records
   • Use the search box to filter employees
   • Click "Edit" or "Delete" buttons in the table for modifications
4. View Analytics: Charts show department distribution and salary analysis

Database Schema Overview

The system uses the following main tables:

• Department: Stores department information (Dept_ID, Dept_Name, Manager_ID)
• Employee: Contains employee details (Emp_ID, Name, Gender, DOB, Dept_ID, Designation, Salary, Hire_Date)
• Project: Manages project information (Project_ID, Project_Name, Dept_ID, Start_Date, End_Date)
• Assignment: Links employees to projects (Emp_ID, Project_ID, Role)
• Attendance: Tracks employee attendance (Emp_ID, Date, Status)
• Salary_Log: Audit trail for salary changes (created via triggers)

API Endpoints

Dashboard

• GET /api/dashboard - Retrieve dashboard statistics

Employees

• GET /api/employees - Get all employees
• GET /api/employees/:id - Get specific employee by ID
• POST /api/employees - Add new employee
• PUT /api/employees/:id - Update employee information
• DELETE /api/employees/:id - Delete employee

Departments

• GET /api/departments - Get all departments
• GET /api/departments/stats - Get department statistics

Projects

• GET /api/projects - Get all projects

Analytics

• GET /api/analytics/salary - Get salary analytics by department

Database Concepts Demonstrated

This project showcases various DBMS concepts covered in the course:

Basic Operations

• DDL (Data Definition Language): CREATE, ALTER, DROP statements
• DML (Data Manipulation Language): INSERT, UPDATE, DELETE, SELECT statements

Advanced Features

• Joins: INNER JOIN, LEFT JOIN for combining data from multiple tables
• Aggregate Functions: COUNT(), AVG(), SUM(), MIN(), MAX()
• GROUP BY & HAVING: Grouping data and filtering groups
• Nested Queries: Subqueries for complex data retrieval
• Views: Virtual tables for simplified data access (Emp_Details view)
• Stored Procedures: Precompiled SQL statements (GetDeptSalary procedure)
• Functions: User-defined functions (GetEmployeeAge function)
• Triggers: Automatic actions on data changes (Salary_Update trigger)
• Cursors: Row-by-row processing (Show_Employees procedure)

Constraints & Relationships

• Primary Keys: Unique identifiers for tables
• Foreign Keys: Referential integrity between tables
• AUTO_INCREMENT: Automatic ID generation
• ENUM: Restricted value sets for status fields

Contributing

This is a course project, but contributions for educational purposes are welcome. Please follow these steps:

1. Fork the repository
2. Create a feature branch (git checkout -b feature/AmazingFeature)
3. Commit your changes (git commit -m 'Add some AmazingFeature')
4. Push to the branch (git push origin feature/AmazingFeature)
5. Open a Pull Request

License

This project is licensed under the ISC License - see the package.json file for details.

---

Note: This project was developed as part of a Database Management Systems course to demonstrate practical application of SQL concepts and full-stack development principles.