🎯 Online Recruitment Database System

A comprehensive database solution for automating company recruitment processes and connecting job seekers with employers

Features • Schema • ER Diagrams • Queries • Installation • Usage

📋 Table of Contents

Overview
Features
Database Schema
ER Diagrams
SQL Queries & Analytics
Installation
Usage
Sample Data
Project Structure
Contributing
License

🌟 Overview

The Online Recruitment Database System is a robust database solution designed to automate and streamline the recruitment process for both job seekers and employers. This system provides a centralized platform where:

🔍 Job Seekers can create profiles, upload their experience and education details, search for vacancies, and apply for jobs
🏢 Employers can manage their company profiles, post job vacancies, and track applications
📊 Administrators can oversee the entire system and generate insightful analytics

The beauty of this online recruitment solution lies in its accessibility and ease of use. Anywhere on the globe, designated individuals are able to receive, process, and keep a record of CVs within a web-based information powerhouse.

✨ Features

👤 Job Seeker Features

✅ User registration and profile management
✅ Add/update personal information
✅ Manage education history
✅ Track work experience
✅ Search vacancies by criteria
✅ Apply for jobs
✅ Save jobs for later

🏢 Employer Features

✅ Company registration and profile
✅ Post new job vacancies
✅ Manage job listings
✅ Set salary ranges
✅ Hide/show job posts
✅ Track applications
✅ Filter candidates

📊 Analytics & Reporting

📈 Most popular job titles by applications
📉 Jobs without applicants tracking
🏆 Top employers by announcements
📋 Available positions per employer
👥 Job seeker activity reports

🗄️ Database Schema

The database consists of 14 interconnected tables designed to handle all aspects of the online recruitment process:

Core Tables

Table	Description	Key Fields
`JobSeeker`	Stores job seeker profiles	firstname, lastname, email, industry, career level
`Employer`	Stores company information	company_name, website_url, establishment_date
`Job_post`	Active job listings	salary range, experience required, description
`Application`	Job applications	apply_date, job post reference, seeker reference

Reference Tables

Table	Description
`Career`	Career levels (Student, Entry Level, Experienced, Manager, Senior Management)
`Industry`	Industry categories (IT, Manufacturing, Real Estate, etc.)
`Job_type`	Employment types (Full Time, Part Time, Freelance, Internship, etc.)
`Job_category`	Job categories (IT/Software Development, Marketing, Finance, etc.)
`Job_title`	Job position titles
`City`	City locations
`states`	State/Country information

Supporting Tables

Table	Description
`Education`	Job seeker education history (degree, major, university, GPA)
`Experience`	Work experience records (company, duration, description)
`Saved_jobs`	Bookmarked jobs by seekers
`admin`	System administrator accounts

🔗 Table Relationships

┌──────────────┐     ┌──────────────┐     ┌──────────────┐
│   Employer   │────▶│   Job_post   │◀────│   Industry   │
└──────────────┘     └──────────────┘     └──────────────┘
                            │
                            ▼
                     ┌──────────────┐
                     │ Application  │
                     └──────────────┘
                            │
                            ▼
┌──────────────┐     ┌──────────────┐     ┌──────────────┐
│  Education   │────▶│  JobSeeker   │◀────│  Experience  │
└──────────────┘     └──────────────┘     └──────────────┘

📐 ER Diagrams

Conceptual ERD (Entity-Relationship Diagram)

The conceptual model shows the high-level entities and their relationships without implementation details:

Key Entities:

Job Seeker - Central entity with attributes for personal info, career level, and industry
Employer - Company entity with establishment details and contact information
Job - Job posting entity linking employers to job seekers through applications
Education & Experience - Supporting entities for job seeker qualifications

Physical ERD (Database Schema)

The physical model shows the actual database implementation with tables, columns, data types, and foreign key constraints:

Implementation Details:

All primary keys use IDENTITY(1,1) for auto-increment
Foreign key constraints ensure referential integrity
Boolean fields use BIT type with appropriate defaults
Date fields use DATE and SMALLDATETIME types
Salary fields use DECIMAL(10,2) for precision

🔍 SQL Queries & Analytics

This project includes 6 powerful analytical queries to extract meaningful insights from the recruitment data:

Query A: Most Popular Job Title

What was the most interesting job "title" that had maximum number of applicants?

SELECT TOP(1) M.job_title AS [Job Title], 
       M.NumOfApplies AS [Number Of Applies]
FROM (
    SELECT Job_title.job_title, COUNT(*) AS NumOfApplies
    FROM Application 
    JOIN Job_post ON Job_post.ID = Application.id_jobpost
    JOIN Job_title ON job_title.ID = Job_post.jobtitle_id 
    GROUP BY job_title
) AS M
ORDER BY M.NumOfApplies DESC;

Query B: Jobs Without Applicants

What was the announced job "title" that hadn't any applicants last month?

SELECT DISTINCT Job_title.job_title 
FROM Job_title 
JOIN Job_post ON Job_title.ID <> ALL (
    SELECT job_title.ID
    FROM Application 
    JOIN Job_post ON Job_post.ID = Application.id_jobpost
    JOIN Job_title ON job_title.ID = Job_post.jobtitle_id
    WHERE created_at BETWEEN 
        DATEADD(day, 1-DAY(EOMONTH(CURRENT_TIMESTAMP,-1)), EOMONTH(CURRENT_TIMESTAMP,-1)) 
        AND EOMONTH(CURRENT_TIMESTAMP,-1)
);

Query C: Top Employer by Announcements

Who was the employer with the maximum announcements last month?

SELECT TOP(1) Employer.ID, Employer.company_name,
       Employer.contact_no, Employer.email,
       City.[name] AS City, states.[name] AS State,
       m.posts AS [Number of announcements]
FROM (
    SELECT Employer.ID AS Employer_id, COUNT(*) AS posts
    FROM Employer 
    JOIN Job_post ON Job_post.id_employer = Employer.ID 
    WHERE created_at BETWEEN 
        DATEADD(day, 1-DAY(EOMONTH(CURRENT_TIMESTAMP,-1)), EOMONTH(CURRENT_TIMESTAMP,-1))
        AND EOMONTH(CURRENT_TIMESTAMP,-1)
    GROUP BY Employer.ID
) AS m 
JOIN Employer ON Employer.ID = m.Employer_id
JOIN City ON City.cities_id = Employer.city_id
JOIN states ON states.ID = City.state_id
ORDER BY [Number of announcements] DESC;

Query D: Inactive Employers

Who were the employers that didn't announce any job last month?

SELECT Employer.ID, Employer.company_name,
       Employer.contact_no, Employer.email,
       City.[name] AS City, states.[name] AS State 
FROM Employer 
JOIN City ON City.cities_id = Employer.city_id
JOIN states ON states.ID = City.state_id
WHERE Employer.ID <> ALL (
    SELECT Employer.ID
    FROM Employer 
    JOIN Job_post ON Employer.ID = Job_post.id_employer
    WHERE created_at BETWEEN 
        DATEADD(day, 1-DAY(EOMONTH(CURRENT_TIMESTAMP,-1)), EOMONTH(CURRENT_TIMESTAMP,-1)) 
        AND EOMONTH(CURRENT_TIMESTAMP,-1)
);

Query E: Available Positions by Employer

What were the available positions at each employer last month?

SELECT Job_post.id_employer AS [Employer ID], Job_post.ID AS [Post ID],
       job_title.job_title, Job_post.[description],
       Job_category.job_category, job_type.job_type,
       Industry.[name] AS Industry, min_salary, max_salary,
       City.[name] AS City, states.[name] AS State,
       Job_post.experience_years
FROM Job_post 
JOIN Job_title ON job_title.ID = Job_post.jobtitle_id
JOIN City ON City.cities_id = Job_post.city_id
JOIN states ON states.ID = Job_post.state_id
JOIN Job_category ON Job_category.ID = Job_post.job_category
JOIN Job_type ON job_type.ID = Job_post.jobtype_id
JOIN Industry ON Industry.id = Job_post.industry_id
WHERE Job_post.is_active = 1 
  AND created_at BETWEEN 
      DATEADD(day, 1-DAY(EOMONTH(CURRENT_TIMESTAMP,-1)), EOMONTH(CURRENT_TIMESTAMP,-1))
      AND EOMONTH(CURRENT_TIMESTAMP,-1)
ORDER BY Job_post.id_employer;

Query F: Job Seeker Applications Summary

For each seeker, retrieve all their information and the number of jobs they applied for

SELECT JobSeeker.ID, JobSeeker.firstname, JobSeeker.lastname,
       JobSeeker.email, JobSeeker.about_me, JobSeeker.contact_no,
       JobSeeker.date_of_birth, JobSeeker.gender,
       states.[name] AS State, City.[name] AS City,
       Career.[name] AS Career, Industry.[name] AS Industry,
       COUNT(Application.jobseeker_id) AS NumberOfApplies
FROM Application
RIGHT JOIN JobSeeker ON Application.jobseeker_id = JobSeeker.ID
LEFT JOIN City ON JobSeeker.city_id = City.cities_id 
LEFT JOIN states ON JobSeeker.state_id = states.ID
LEFT JOIN Career ON JobSeeker.career_id = Career.ID
LEFT JOIN Industry ON JobSeeker.industry_id = Industry.id
GROUP BY JobSeeker.ID, JobSeeker.firstname, JobSeeker.lastname,
         JobSeeker.email, JobSeeker.about_me, JobSeeker.contact_no,
         JobSeeker.date_of_birth, JobSeeker.gender,
         City.[name], states.[name], Career.[name], Industry.[name]
ORDER BY NumberOfApplies DESC;

🚀 Installation

Prerequisites

Microsoft SQL Server 2016 or later
SQL Server Management Studio (SSMS) or Azure Data Studio

Setup Instructions

Clone the repository

git clone https://github.com/Abdo-Essam/Online-Recruitment-Database-Project.git
cd Online-Recruitment-Database-Project

Create the database

CREATE DATABASE OnlineRecruitment;
USE OnlineRecruitment;

Create tables
- Open create tables.TXT in SSMS
- Execute the script to create all tables
Insert sample data
- Open insert in tables.TXT in SSMS
- Execute the script to populate with sample data
Run analytical queries
- Navigate to the queries/ folder
- Execute queries a.TXT through f.TXT as needed

💻 Usage

Creating a Job Seeker Profile

INSERT INTO JobSeeker(firstname, lastname, email, password, about_me, 
                      city_id, state_id, contact_no, career_id, 
                      date_of_birth, gender, industry_id)
VALUES ('John', 'Doe', 'john.doe@email.com', 'securepass123',
        'Experienced software developer...', 1, 1, '01234567890',
        3, '1995-06-15', 'M', 4);

Posting a Job Vacancy

INSERT INTO Job_post(id_employer, jobtitle_id, min_salary, max_salary,
                     city_id, state_id, experience_years, description,
                     job_category, jobtype_id, industry_id)
VALUES (1, 2, 5000, 10000, 1, 1, 2,
        'We are looking for a skilled web designer...',
        6, 1, 4);

Applying for a Job

INSERT INTO Application(id_jobpost, jobseeker_id)
VALUES (1, 1);

📁 Sample Data

The database comes pre-loaded with sample data including:

🏢 Sample Employers

Company	Industry	Location
Facebook	Technology	Fort Wayne, Indiana
Google	Technology	Lincoln, Nebraska
Amazon	E-commerce	Birmingham, Alabama
AE	Technology	Birmingham, Alabama
Souq	E-commerce	Fort Wayne, Indiana

👥 Sample Job Seekers

Name	Career Level	Industry
Abdo Kamal	Experienced	FMCG
Yara Yaser	Entry Level	Manufacturing
Abdo Essam	Student	Computer Software
Ahmed Mostafa	Entry Level	IT Services
Sara Ahmed	Entry Level	Manufacturing

📋 Sample Job Types

Full Time
Part Time
Work From Home
Freelance/Project
Internship
Shift Based
Volunteering
Student Activity

📂 Project Structure

Online-Recruitment-Database-Project/
│
├── 📄 README.md                    # Project documentation
├── 📄 create tables.TXT            # DDL scripts for table creation
├── 📄 insert in tables.TXT         # Sample data insertion scripts
├── 📄 DATA.TXT                     # Reference data values
│
├── 📁 queries/                     # SQL analytical queries
│   ├── a.TXT                       # Most popular job query
│   ├── b.TXT                       # Jobs without applicants query
│   ├── c.TXT                       # Top employer query
│   ├── d.TXT                       # Inactive employers query
│   ├── e.TXT                       # Available positions query
│   └── f.TXT                       # Job seeker summary query
│
├── 🖼️ ERD conceptual .png          # Conceptual ER diagram
├── 🖼️ ERD Physical.png             # Physical database schema
│
├── 📄 IS211 Project Description.pdf # Original project requirements
└── 📄 IS211-20180144.pdf           # Project report

🤝 Contributing

Contributions are welcome! Here's how you can help:

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

Contribution Ideas

🐛 Bug fixes and improvements
📝 Additional SQL queries
🎨 Enhanced ERD visualizations
📖 Documentation improvements
🧪 Performance optimizations

📜 License

This project is licensed under the MIT License - see the LICENSE file for details.

👨‍💻 Author

Abdo Essam

⭐ Star this repository if you find it helpful!

Made with ❤️ for the Database Course (IS211)

Name		Name	Last commit message	Last commit date
Latest commit History 10 Commits
queries		queries
DATA.TXT		DATA.TXT
ERD Physical.png		ERD Physical.png
ERD conceptual .png		ERD conceptual .png
IS211 Project Description.pdf		IS211 Project Description.pdf
IS211-20180144.pdf		IS211-20180144.pdf
README.md		README.md
create tables.TXT		create tables.TXT
insert in tables.TXT		insert in tables.TXT

abdo-essam/Online-Recruitment-Database-Project

Folders and files

Latest commit

History

Repository files navigation