EMPLOYEE PAYROLL MANAGEMENT - v0 by VercelEMPLOYEE PAYROLL MANAGEMENT - v0 by Vercel

Give me a complete beginner-friendly SQL project including clear steps, a database schema, and full source code written in pure SQL. If needed, you may also include Python code to interact with the database. The project should be simple, educational, and practical for someone new to SQL

Employee Payroll Management

403d ago

Employee Payroll Management System for Beginners

This beginner-friendly SQL project will guide you through creating a simple employee payroll management system. You'll learn how to design a database schema, create tables, insert data, and run various queries to extract useful information.

Project Overview

In this project, you'll build a database to:

Store employee information
Track departments and positions
Manage salary information
Record attendance and calculate payroll

Step 1: Database Schema

Here's the schema we'll implement:

Step 2: Create the Database and Tables

Let's start by creating our database and tables:

Employee Payroll System

create_database.sql

Step 3: Insert Sample Data

Now let's populate our tables with sample data:

Employee Payroll System

insert_data.sql

Step 4: Basic SQL Queries

Let's explore some basic SQL queries to retrieve information from our database:

Employee Payroll System

basic_queries.sql

Step 5: Advanced SQL Queries

Now let's look at some more advanced queries:

Employee Payroll System

advanced_queries.sql

Step 6: Creating Views for Common Queries

Views can simplify complex queries and provide a layer of abstraction:

Employee Payroll System

create_views.sql

Step 7: Creating Stored Procedures

Stored procedures can automate common tasks:

Employee Payroll System

stored_procedures.sql

Step 8: Python Integration

Now let's create a Python script to interact with our database:

Employee Payroll System

payroll_system.py

Step 9: Running the Project

To run this project:

Install MySQL or MariaDB on your system
Create a database and run the SQL scripts in order:
- create_database.sql
- insert_data.sql
- create_views.sql
- stored_procedures.sql
Install Python dependencies:

pip install mysql-connector-python pandas matplotlib

Update the database credentials in payroll_system.py
Run the Python script:

python payroll_system.py

Project Extensions

Once you're comfortable with the basics, here are some ways to extend the project:

Add a web interface using Flask or Django
Implement user authentication for different roles (HR, managers, employees)
Add more complex payroll calculations (overtime, bonuses, benefits)
Create a reporting dashboard with more visualizations
Implement data validation and error handling
Add email notifications for payroll processing

Conclusion

This project has introduced you to:

Database schema design
Creating tables with relationships
Writing basic and advanced SQL queries
Creating views and stored procedures
Integrating SQL with Python
Data visualization

These skills form the foundation of database development and will be valuable in many real-world applications.

403d ago

import mysql.connector
from mysql.connector import Error
import pandas as pd
from datetime import datetime, timedelta
import matplotlib.pyplot as plt

class PayrollSystem:
    def __init__(self, host, user, password, database):
        """Initialize connection to the database"""
        try:
            self.connection = mysql.connector.connect(
                host=host,
                user=user,
                password=password,
                database=database
            )
            if self.connection.is_connected():
                print(f"Connected to MySQL database: {database}")
                self.cursor = self.connection.cursor(dictionary=True)
        except Error as e:
            print(f"Error connecting to MySQL: {e}")
            self.connection = None
    
    def close_connection(self):
        """Close the database connection"""
        if self.connection and self.connection.is_connected():
            self.cursor.close()
            self.connection.close()
            print("MySQL connection closed")
    
    def execute_query(self, query, params=None):
        """Execute a query and return results"""
        try:
            self.cursor.execute(query, params or ())
            if query.strip().upper().startswith(('SELECT', 'SHOW')):
                return self.cursor.fetchall()
            else:
                self.connection.commit()
                return {"affected_rows": self.cursor.rowcount}
        except Error as e:
            print(f"Error executing query: {e}")
            return None
    
    def get_all_employees(self):
        """Get all employees with their details"""
        query = """
        SELECT * FROM employee_details
        ORDER BY employee_id
        """
        return self.execute_query(query)
    
    def get_department_salary_stats(self):
        """Get salary statistics by department"""
        query = """
        SELECT 
            d.name AS department,
            COUNT(e.employee_id) AS employee_count,
            MIN(p.base_salary) AS min_salary,
            MAX(p.base_salary) AS max_salary,
            ROUND(AVG(p.base_salary), 2) AS avg_salary
        FROM 
            departments d
        JOIN 
            positions p ON d.department_id = p.department_id
        JOIN 
            employees e ON p.position_id = e.position_id
        GROUP BY 
            d.name
        ORDER BY 
            avg_salary DESC
        """
        return self.execute_query(query)
    
    def add_employee(self, first_name, last_name, hire_date, email, phone, position_id, manager_id=None):
        """Add a new employee using the stored procedure"""
        query = "CALL add_employee(%s, %s, %s, %s, %s, %s, %s)"
        params = (first_name, last_name, hire_date, email, phone, position_id, manager_id)
        return self.execute_query(query, params)
    
    def record_attendance(self, employee_id, work_date, time_in, time_out, status='Present'):
        """Record attendance for an employee"""
        query = """
        INSERT INTO attendance (employee_id, work_date, time_in, time_out, status)
        VALUES (%s, %s, %s, %s, %s)
        """
        params = (employee_id, work_date, time_in, time_out, status)
        return self.execute_query(query, params)
    
    def generate_payroll(self, start_date, end_date, payment_date):
        """Generate payroll for all employees for a specific period"""
        query = "CALL calculate_payroll(%s, %s, %s)"
        params = (start_date, end_date, payment_date)
        return self.execute_query(query, params)
    
    def get_attendance_report(self, employee_id, start_date, end_date):
        """Get attendance report for an employee"""
        query = "CALL get_attendance_report(%s, %s, %s)"
        params = (employee_id, start_date, end_date)
        return self.execute_query(query, params)
    
    def visualize_department_salaries(self):
        """Visualize average salaries by department"""
        data = self.get_department_salary_stats()
        if not data:
            print("No data available for visualization")
            return
        
        df = pd.DataFrame(data)
        
        plt.figure(figsize=(10, 6))
        bars = plt.bar(df['department'], df['avg_salary'], color='skyblue')
        
        # Add employee count as text on top of bars
        for bar, count in zip(bars, df['employee_count']):
            plt.text(
                bar.get_x() + bar.get_width()/2,
                bar.get_height() + 1000,
                f"{count} emp",
                ha='center',
                va='bottom'
            )
        
        plt.title('Average Salary by Department')
        plt.xlabel('Department')
        plt.ylabel('Average Salary ($)')
        plt.xticks(rotation=45, ha='right')
        plt.tight_layout()
        
        # Add min and max as annotations
        for i, (dept, min_sal, max_sal) in enumerate(zip(df['department'], df['min_salary'], df['max_salary'])):
            plt.annotate(
                f"Min: ${min_sal:,.2f}",
                (i, df['avg_salary'][i] - 10000),
                ha='center',
                va='top',
                color='red'
            )
            plt.annotate(
                f"Max: ${max_sal:,.2f}",
                (i, df['avg_salary'][i] - 5000),
                ha='center',
                va='top',
                color='green'
            )
        
        plt.grid(axis='y', linestyle='--', alpha=0.7)
        plt.show()
    
    def visualize_attendance_trends(self, start_date, end_date):
        """Visualize attendance trends for all employees"""
        query = """
        SELECT 
            a.work_date,
            COUNT(CASE WHEN a.status = 'Present' THEN 1 END) AS present_count,
            COUNT(CASE WHEN a.status = 'Absent' THEN 1 END) AS absent_count,
            COUNT(CASE WHEN a.status = 'Late' THEN 1 END) AS late_count
        FROM 
            attendance a
        WHERE 
            a.work_date BETWEEN %s AND %s
        GROUP BY 
            a.work_date
        ORDER BY 
            a.work_date
        """
        data = self.execute_query(query, (start_date, end_date))
        if not data:
            print("No attendance data available for visualization")
            return
        
        df = pd.DataFrame(data)
        
        plt.figure(figsize=(12, 6))
        plt.plot(df['work_date'], df['present_count'], marker='o', label='Present', color='green')
        plt.plot(df['work_date'], df['absent_count'], marker='s', label='Absent', color='red')
        plt.plot(df['work_date'], df['late_count'], marker='^', label='Late', color='orange')
        
        plt.title('Daily Attendance Trends')
        plt.xlabel('Date')
        plt.ylabel('Employee Count')
        plt.legend()
        plt.grid(True, linestyle='--', alpha=0.7)
        plt.xticks(rotation=45)
        plt.tight_layout()
        plt.show()


# Example usage
if __name__ == "__main__":
    # Replace with your actual database credentials
    payroll_system = PayrollSystem(
        host="localhost",
        user="root",
        password="your_password",
        database="employee_payroll"
    )
    
    # Example operations
    try:
        # Get all employees
        employees = payroll_system.get_all_employees()
        print(f"Total employees: {len(employees)}")
        for emp in employees[:3]:  # Print first 3 employees
            print(f"ID: {emp['employee_id']}, Name: {emp['first_name']} {emp['last_name']}, Position: {emp['position']}")
        
        # Add a new employee
        # new_emp = payroll_system.add_employee(
        #     'Jane', 'Doe', '2023-05-15', 'jane.doe@company.com', 
        #     '555-9876', 4, 3
        # )
        # print(f"New employee added: {new_emp}")
        
        # Record attendance
        today = datetime.now().date()
        # payroll_system.record_attendance(1, today, '09:00:00', '17:00:00', 'Present')
        # print("Attendance recorded")
        
        # Generate payroll for May 2023
        # may_start = datetime(2023, 5, 1).date()
        # may_end = datetime(2023, 5, 31).date()
        # payment_date = datetime(2023, 6, 5).date()
        # payroll_system.generate_payroll(may_start, may_end, payment_date)
        # print("Payroll generated")
        
        # Get attendance report for employee 1
        # report = payroll_system.get_attendance_report(1, '2023-05-01', '2023-05-05')
        # print("Attendance report:")
        # for record in report:
        #     print(f"Date: {record['work_date']}, Status: {record['status']}, Hours: {record['hours_worked']}")
        
        # Visualize department salaries
        payroll_system.visualize_department_salaries()
        
        # Visualize attendance trends
        payroll_system.visualize_attendance_trends('2023-05-01', '2023-05-05')
        
    finally:
        # Close the connection
        payroll_system.close_connection()

No Output

Run the code to generate an output.

Employee Payroll Management

403d ago

Employee Payroll Management System for Beginners

Project Overview

In this project, you'll build a database to:

Store employee information
Track departments and positions
Manage salary information
Record attendance and calculate payroll

Step 1: Database Schema

Here's the schema we'll implement:

Generating diagram...

Step 2: Create the Database and Tables

Let's start by creating our database and tables:

Employee Payroll System

create_database.sql

Step 3: Insert Sample Data

Now let's populate our tables with sample data:

Employee Payroll System

insert_data.sql

Step 4: Basic SQL Queries

Let's explore some basic SQL queries to retrieve information from our database:

Employee Payroll System

basic_queries.sql

Step 5: Advanced SQL Queries

Now let's look at some more advanced queries:

Employee Payroll System

advanced_queries.sql

Step 6: Creating Views for Common Queries

Views can simplify complex queries and provide a layer of abstraction:

Employee Payroll System

create_views.sql

Step 7: Creating Stored Procedures

Stored procedures can automate common tasks:

Employee Payroll System

stored_procedures.sql

Step 8: Python Integration

Now let's create a Python script to interact with our database:

Employee Payroll System

payroll_system.py

Step 9: Running the Project

To run this project:

Install MySQL or MariaDB on your system
Create a database and run the SQL scripts in order:
- create_database.sql
- insert_data.sql
- create_views.sql
- stored_procedures.sql
Install Python dependencies:

pip install mysql-connector-python pandas matplotlib

Update the database credentials in payroll_system.py
Run the Python script:

python payroll_system.py

Project Extensions

Once you're comfortable with the basics, here are some ways to extend the project:

Add a web interface using Flask or Django
Implement user authentication for different roles (HR, managers, employees)
Add more complex payroll calculations (overtime, bonuses, benefits)
Create a reporting dashboard with more visualizations
Implement data validation and error handling
Add email notifications for payroll processing

Conclusion

This project has introduced you to:

Database schema design
Creating tables with relationships
Writing basic and advanced SQL queries
Creating views and stored procedures
Integrating SQL with Python
Data visualization

These skills form the foundation of database development and will be valuable in many real-world applications.

403d ago

import mysql.connector
from mysql.connector import Error
import pandas as pd
from datetime import datetime, timedelta
import matplotlib.pyplot as plt

class PayrollSystem:
    def __init__(self, host, user, password, database):
        """Initialize connection to the database"""
        try:
            self.connection = mysql.connector.connect(
                host=host,
                user=user,
                password=password,
                database=database
            )
            if self.connection.is_connected():
                print(f"Connected to MySQL database: {database}")
                self.cursor = self.connection.cursor(dictionary=True)
        except Error as e:
            print(f"Error connecting to MySQL: {e}")
            self.connection = None
    
    def close_connection(self):
        """Close the database connection"""
        if self.connection and self.connection.is_connected():
            self.cursor.close()
            self.connection.close()
            print("MySQL connection closed")
    
    def execute_query(self, query, params=None):
        """Execute a query and return results"""
        try:
            self.cursor.execute(query, params or ())
            if query.strip().upper().startswith(('SELECT', 'SHOW')):
                return self.cursor.fetchall()
            else:
                self.connection.commit()
                return {"affected_rows": self.cursor.rowcount}
        except Error as e:
            print(f"Error executing query: {e}")
            return None
    
    def get_all_employees(self):
        """Get all employees with their details"""
        query = """
        SELECT * FROM employee_details
        ORDER BY employee_id
        """
        return self.execute_query(query)
    
    def get_department_salary_stats(self):
        """Get salary statistics by department"""
        query = """
        SELECT 
            d.name AS department,
            COUNT(e.employee_id) AS employee_count,
            MIN(p.base_salary) AS min_salary,
            MAX(p.base_salary) AS max_salary,
            ROUND(AVG(p.base_salary), 2) AS avg_salary
        FROM 
            departments d
        JOIN 
            positions p ON d.department_id = p.department_id
        JOIN 
            employees e ON p.position_id = e.position_id
        GROUP BY 
            d.name
        ORDER BY 
            avg_salary DESC
        """
        return self.execute_query(query)
    
    def add_employee(self, first_name, last_name, hire_date, email, phone, position_id, manager_id=None):
        """Add a new employee using the stored procedure"""
        query = "CALL add_employee(%s, %s, %s, %s, %s, %s, %s)"
        params = (first_name, last_name, hire_date, email, phone, position_id, manager_id)
        return self.execute_query(query, params)
    
    def record_attendance(self, employee_id, work_date, time_in, time_out, status='Present'):
        """Record attendance for an employee"""
        query = """
        INSERT INTO attendance (employee_id, work_date, time_in, time_out, status)
        VALUES (%s, %s, %s, %s, %s)
        """
        params = (employee_id, work_date, time_in, time_out, status)
        return self.execute_query(query, params)
    
    def generate_payroll(self, start_date, end_date, payment_date):
        """Generate payroll for all employees for a specific period"""
        query = "CALL calculate_payroll(%s, %s, %s)"
        params = (start_date, end_date, payment_date)
        return self.execute_query(query, params)
    
    def get_attendance_report(self, employee_id, start_date, end_date):
        """Get attendance report for an employee"""
        query = "CALL get_attendance_report(%s, %s, %s)"
        params = (employee_id, start_date, end_date)
        return self.execute_query(query, params)
    
    def visualize_department_salaries(self):
        """Visualize average salaries by department"""
        data = self.get_department_salary_stats()
        if not data:
            print("No data available for visualization")
            return
        
        df = pd.DataFrame(data)
        
        plt.figure(figsize=(10, 6))
        bars = plt.bar(df['department'], df['avg_salary'], color='skyblue')
        
        # Add employee count as text on top of bars
        for bar, count in zip(bars, df['employee_count']):
            plt.text(
                bar.get_x() + bar.get_width()/2,
                bar.get_height() + 1000,
                f"{count} emp",
                ha='center',
                va='bottom'
            )
        
        plt.title('Average Salary by Department')
        plt.xlabel('Department')
        plt.ylabel('Average Salary ($)')
        plt.xticks(rotation=45, ha='right')
        plt.tight_layout()
        
        # Add min and max as annotations
        for i, (dept, min_sal, max_sal) in enumerate(zip(df['department'], df['min_salary'], df['max_salary'])):
            plt.annotate(
                f"Min: ${min_sal:,.2f}",
                (i, df['avg_salary'][i] - 10000),
                ha='center',
                va='top',
                color='red'
            )
            plt.annotate(
                f"Max: ${max_sal:,.2f}",
                (i, df['avg_salary'][i] - 5000),
                ha='center',
                va='top',
                color='green'
            )
        
        plt.grid(axis='y', linestyle='--', alpha=0.7)
        plt.show()
    
    def visualize_attendance_trends(self, start_date, end_date):
        """Visualize attendance trends for all employees"""
        query = """
        SELECT 
            a.work_date,
            COUNT(CASE WHEN a.status = 'Present' THEN 1 END) AS present_count,
            COUNT(CASE WHEN a.status = 'Absent' THEN 1 END) AS absent_count,
            COUNT(CASE WHEN a.status = 'Late' THEN 1 END) AS late_count
        FROM 
            attendance a
        WHERE 
            a.work_date BETWEEN %s AND %s
        GROUP BY 
            a.work_date
        ORDER BY 
            a.work_date
        """
        data = self.execute_query(query, (start_date, end_date))
        if not data:
            print("No attendance data available for visualization")
            return
        
        df = pd.DataFrame(data)
        
        plt.figure(figsize=(12, 6))
        plt.plot(df['work_date'], df['present_count'], marker='o', label='Present', color='green')
        plt.plot(df['work_date'], df['absent_count'], marker='s', label='Absent', color='red')
        plt.plot(df['work_date'], df['late_count'], marker='^', label='Late', color='orange')
        
        plt.title('Daily Attendance Trends')
        plt.xlabel('Date')
        plt.ylabel('Employee Count')
        plt.legend()
        plt.grid(True, linestyle='--', alpha=0.7)
        plt.xticks(rotation=45)
        plt.tight_layout()
        plt.show()


# Example usage
if __name__ == "__main__":
    # Replace with your actual database credentials
    payroll_system = PayrollSystem(
        host="localhost",
        user="root",
        password="your_password",
        database="employee_payroll"
    )
    
    # Example operations
    try:
        # Get all employees
        employees = payroll_system.get_all_employees()
        print(f"Total employees: {len(employees)}")
        for emp in employees[:3]:  # Print first 3 employees
            print(f"ID: {emp['employee_id']}, Name: {emp['first_name']} {emp['last_name']}, Position: {emp['position']}")
        
        # Add a new employee
        # new_emp = payroll_system.add_employee(
        #     'Jane', 'Doe', '2023-05-15', 'jane.doe@company.com', 
        #     '555-9876', 4, 3
        # )
        # print(f"New employee added: {new_emp}")
        
        # Record attendance
        today = datetime.now().date()
        # payroll_system.record_attendance(1, today, '09:00:00', '17:00:00', 'Present')
        # print("Attendance recorded")
        
        # Generate payroll for May 2023
        # may_start = datetime(2023, 5, 1).date()
        # may_end = datetime(2023, 5, 31).date()
        # payment_date = datetime(2023, 6, 5).date()
        # payroll_system.generate_payroll(may_start, may_end, payment_date)
        # print("Payroll generated")
        
        # Get attendance report for employee 1
        # report = payroll_system.get_attendance_report(1, '2023-05-01', '2023-05-05')
        # print("Attendance report:")
        # for record in report:
        #     print(f"Date: {record['work_date']}, Status: {record['status']}, Hours: {record['hours_worked']}")
        
        # Visualize department salaries
        payroll_system.visualize_department_salaries()
        
        # Visualize attendance trends
        payroll_system.visualize_attendance_trends('2023-05-01', '2023-05-05')
        
    finally:
        # Close the connection
        payroll_system.close_connection()

No Output

Run the code to generate an output.