Our employee management database will manage:

• Employee personal and contact information
• Employee IDs and job titles
• Departments and managers
• Employee salaries
• Joining dates and employment status
• Attendance records
• Department-wise employee reports
• Salary reports using aggregation

Database name: employeeDB

Collections:

• employees
• departments
• attendance

Open MongoDB Shell:

mongosh

Select the database:

use employeeDB

Create the collections:

db.createCollection("employees")
db.createCollection("departments")
db.createCollection("attendance")

MongoDB creates a database when data is first stored. Collections can also be created automatically when documents are inserted.

If these collections already exist, continue using them instead of creating them again.

Example employee document:

{
    employee_id: 1001,
    name: "Rahul Kumar",
    email: "rahul@example.com",
    phone: "9876543210",
    department: "IT",
    designation: "Software Developer",
    salary: 45000,
    joining_date: ISODate("2025-01-15T00:00:00Z"),
    employment_status: "Active"
}

Important fields:

• employee_id stores the business employee ID.
• name stores the employee's name.
• department identifies the department.
• designation stores the employee's job title.
• salary stores the employee's salary.
• joining_date stores the joining date as a BSON Date.
• employment_status stores the current employment status.

MongoDB automatically generates an _id field when one is not provided. The employee_id is a separate application-level identifier.

Insert one employee using insertOne():

db.employees.insertOne({
    employee_id: 1001,
    name: "Rahul Kumar",
    email: "rahul@example.com",
    phone: "9876543210",
    department: "IT",
    designation: "Software Developer",
    salary: 45000,
    joining_date: ISODate("2025-01-15T00:00:00Z"),
    employment_status: "Active"
})

Insert multiple employees:

db.employees.insertMany([
    {
        employee_id: 1002,
        name: "Priya Sharma",
        email: "priya@example.com",
        phone: "9876543211",
        department: "HR",
        designation: "HR Executive",
        salary: 35000,
        joining_date: ISODate("2025-02-10T00:00:00Z"),
        employment_status: "Active"
    },
    {
        employee_id: 1003,
        name: "Amit Singh",
        email: "amit@example.com",
        phone: "9876543212",
        department: "IT",
        designation: "Database Administrator",
        salary: 50000,
        joining_date: ISODate("2025-03-01T00:00:00Z"),
        employment_status: "Active"
    },
    {
        employee_id: 1004,
        name: "Neha Verma",
        email: "neha@example.com",
        phone: "9876543213",
        department: "Finance",
        designation: "Accountant",
        salary: 32000,
        joining_date: ISODate("2025-04-12T00:00:00Z"),
        employment_status: "Active"
    }
])

Run these sample insertions once. Running them repeatedly can create duplicate records unless unique indexes are configured.

Display all employees:

db.employees.find()

Display employees in a readable format:

db.employees.find().pretty()

Find an employee by employee ID:

db.employees.findOne({
    employee_id: 1001
})

Find employees in the IT department:

db.employees.find({
    department: "IT"
})

Find employees earning more than ₹40,000:

db.employees.find({
    salary: { $gt: 40000 }
})

Find active employees:

db.employees.find({
    employment_status: "Active"
})

Display only employee names and salaries:

db.employees.find(
    {},
    {
        _id: 0,
        name: 1,
        salary: 1
    }
)

Use updateOne() with $set to update selected fields.

db.employees.updateOne(
    { employee_id: 1001 },
    {
        $set: {
            designation: "Senior Software Developer",
            salary: 55000
        }
    }
)

Verify the updated employee:

db.employees.findOne({
    employee_id: 1001
})

The $set operator changes the specified fields without replacing the entire document.

When an employee leaves the company, you can update their employment status instead of deleting their entire record.

db.employees.updateOne(
    { employee_id: 1004 },
    {
        $set: {
            employment_status: "Inactive"
        }
    }
)

Find all inactive employees:

db.employees.find({
    employment_status: "Inactive"
})

This approach preserves the employee's record for historical reporting. Define allowed status values consistently in your application.

Insert department information:

db.departments.insertMany([
    {
        department_id: 201,
        department_name: "IT",
        manager: "Amit Singh",
        location: "Patna"
    },
    {
        department_id: 202,
        department_name: "HR",
        manager: "Priya Sharma",
        location: "Patna"
    },
    {
        department_id: 203,
        department_name: "Finance",
        manager: "Neha Verma",
        location: "Gaya"
    }
])

Find the IT department:

db.departments.findOne({
    department_name: "IT"
})

In this introductory project, the employee document stores the department name directly. A larger application could instead store a department ID and use aggregation with $lookup to retrieve department details.

Insert an attendance record:

db.attendance.insertOne({
    attendance_id: 301,
    employee_id: 1001,
    attendance_date: ISODate("2025-06-10T00:00:00Z"),
    check_in: "09:00",
    check_out: "17:00",
    status: "Present"
})

Insert another attendance record:

db.attendance.insertOne({
    attendance_id: 302,
    employee_id: 1002,
    attendance_date: ISODate("2025-06-10T00:00:00Z"),
    check_in: "09:15",
    check_out: "17:00",
    status: "Late"
})

Find attendance records for employee 1001:

db.attendance.find({
    employee_id: 1001
})

Find employees marked late:

db.attendance.find({
    status: "Late"
})

These examples use sample dates and times. A real attendance system should validate date, timezone, check-in, and check-out information.

Sort employees by salary from highest to lowest:

db.employees.find().sort({
    salary: -1
})

Display the three highest-paid employees:

db.employees.find().sort({
    salary: -1
}).limit(3)

Sort employees by name alphabetically:

db.employees.find().sort({
    name: 1
})

Use 1 for ascending order and -1 for descending order.

Use the aggregation pipeline to calculate the number of employees and total salary for each department.

db.employees.aggregate([
    {
        $match: {
            employment_status: "Active"
        }
    },
    {
        $group: {
            _id: "$department",
            totalEmployees: { $sum: 1 },
            totalSalary: { $sum: "$salary" },
            averageSalary: { $avg: "$salary" }
        }
    },
    {
        $sort: {
            totalSalary: -1
        }
    }
])

Explanation:

• $match selects active employees.
• $group groups records by department.
• $sum calculates total employees and salary.
• $avg calculates average salary.
• $sort sorts departments by total salary.

This report uses the current salary field stored in each employee document. It is not a historical payroll report.

Count employees in the IT department:

db.employees.countDocuments({
    department: "IT"
})

Count all active employees:

db.employees.countDocuments({
    employment_status: "Active"
})

These commands return the number of matching employee documents.

MongoDB does not enforce foreign keys automatically. However, aggregation can combine documents from different collections using $lookup.

db.employees.aggregate([
    {
        $lookup: {
            from: "departments",
            localField: "department",
            foreignField: "department_name",
            as: "department_details"
        }
    },
    {
        $project: {
            _id: 0,
            employee_id: 1,
            name: 1,
            department: 1,
            designation: 1,
            department_details: 1
        }
    }
])

The localField is the department field in an employee document, while foreignField is the department name in the departments collection.

Both fields should use consistent values and types for the join to work as intended.

Unique indexes help prevent duplicate business IDs.

db.employees.createIndex(
    { employee_id: 1 },
    { unique: true }
)

db.departments.createIndex(
    { department_id: 1 },
    { unique: true }
)

db.attendance.createIndex(
    { attendance_id: 1 },
    { unique: true }
)

For an attendance system that permits only one attendance record per employee per day, you can also create a compound unique index:

db.attendance.createIndex(
    {
        employee_id: 1,
        attendance_date: 1
    },
    { unique: true }
)

Create this compound index only if your business rules allow one document per employee per attendance date. If duplicate keys already exist, index creation will fail until they are resolved.

1. Create the employeeDB database.
2. Create the employees, departments, and attendance collections.
3. Insert at least five employee documents.
4. Search employees by ID, department, and salary.
5. Update an employee's designation and salary.
6. Change an employee's status to Inactive.
7. Insert department records.
8. Record attendance for at least three employees.
9. Find late employees and attendance records by employee ID.
10. Display the three highest-paid employees.
11. Generate a department-wise salary report using aggregation.
12. Use $lookup to combine employee and department information.
13. Create unique indexes for employee and department IDs.