CSC-104L Database System

Project Report

Job Management System

Submitted by:
Muhammad Haroon 2024-CS-608
Muhammad Babar Usman 2024-CS-627
Muhammad Wasif Azhar 2024-CS-649

Submitted to:
Ms. Rida

Dated: 30th April 2024

Department of Computer Science

University of Engineering and Technology Lahore, New Campus
 Job Management System
1. Project Overview:
The Job Management System is a relational database designed to manage all aspects of job
applications, employees, employers, interviews, contracts, and employee performance. The system
supports robust data operations with well-defined entities and relationships.

2. Database Design:

Main Entities:

 Department: Stores information about company departments.

 Employer: Stores employer (company) information.
 Employee: Stores employee data.
 Job: Jobs posted by employers.
 Application: Applications submitted by employees for jobs.
 Skill: Skills that jobs or employees can have.
 Interview: Details of interviews conducted.
 Contract: Contracts between employees and employers.
 Performance: Employee performance records.
 SalaryHistory: Employee salary changes over time.
 Education: Academic records of employees.
 WorkExperience: Past work experiences of employees.
 UserAccount: Login credentials for system users.
 JobLocation: Locations where jobs are posted.

Relationships:

 One department has many employees.

 An employer can post many jobs.
 A job can have many applications.
 An employee can have many skills and multiple educational and work records.
 Employees can have multiple interviews, contracts, and salary history records.

3. Implementation Details:
3.1 DDL (Data Definition Language)

 Tables were created with primary keys and foreign key constraints where needed.
 Department
➔ Stores information about departments.
Fields: DepartmentID (Primary Key), Name, Description.
 Employer
➔ Stores employer/company details.
Fields: EmployerID (Primary Key), CompanyName, ContactEmail, and link to a
Department.
 Employee
➔ Stores employee personal details.
Fields: EmployeeID (Primary Key), FullName, Email, Phone, Address.
 Job
➔ Stores job postings and job details.
Fields: JobID (Primary Key), Title, Description, Salary, PostDate,
Deadline, EmployerID, LocationID, and WorkType.
 Application
➔ Tracks job applications submitted by employees.
Fields: ApplicationID (Primary Key), JobID, EmployeeID, ApplyDate, Status.
 Skill
➔ Lists different skills that can be associated with employees and job
requirements.
Fields: SkillID (Primary Key), SkillName.
 JobSkill
➔ Maps skills required for each job.
Fields: JobID, SkillID (Composite Primary Key).
 UserAccount
➔ Stores login credentials and roles (Admin or HR) for system users.
Fields: UserID (Primary Key), Username, PasswordHash, Role.
 Interview
➔ Tracks interviews scheduled for applications.
Fields: InterviewID (Primary Key), ApplicationID, ScheduleDate,
InterviewType, InterviewerName, Notes, Result.
 EmployeeSkill
➔ Links employees to their skills and tracks their proficiency and experience.
Fields: EmployeeID, SkillID (Composite Primary Key), ProficiencyLevel,
YearsExperience.
 JobLocation
➔ Stores information about job locations.
Fields: LocationID (Primary Key), City, State, Country, PostalCode,
IsRemote.
 Contract
➔ Tracks employment contracts issued after successful job applications.
Fields: ContractID (Primary Key), ApplicationID, StartDate, EndDate,
ContractType, SignedDate, ContractDocument, Status.
 Performance
➔ Tracks employee performance reviews.
Fields: PerformanceID (Primary Key), EmployeeID, ReviewDate, ReviewerID,
Rating, Comments, GoalsSet, NextReviewDate.
 SalaryHistory
➔ Tracks changes in employee salaries over time.
 Fields: HistoryID (Primary Key), EmployeeID, PreviousSalary, NewSalary,
EffectiveDate, Reason, ApprovedBy.
 Education
➔ Stores employee education history details.
Fields: EducationID (Primary Key), EmployeeID, InstitutionName, Degree,
FieldOfStudy, StartDate, EndDate, GPA, Achievements.
 WorkExperience
➔ Stores employee past work experience details.
Fields: ExperienceID (Primary Key), EmployeeID, CompanyName, JobTitle,
StartDate, EndDate, IsCurrentJob, JobDescription

3.2 DML (Data Manipulation Language)

 Although DML (INSERT, UPDATE, DELETE) commands are not shown in the snippet, they
would be used to populate and manage the tables.

3.3 Joins

 Multiple tables are designed to allow efficient inner joins, left joins, and multi-table
joins.
 INNER JOIN: List employees who applied for a job with job title
➔ Retrieves a list of employees who have applied for jobs, showing each
employee's full name, the job title, and the application status.
(Only records where the application exists are shown.)
 LEFT JOIN: List all jobs with applicants (if any)
➔ Lists all jobs along with the applicants' names and application status.
Jobs without any applicants are also shown, with NULL for missing applicant data.
 RIGHT JOIN: Show employees who applied for jobs (even if jobs are
missing)
➔ Lists employees who have applied for jobs.
This simulates a scenario where jobs might not be listed anymore, but applications
are still recorded.
 FULL OUTER JOIN: Show all combinations of jobs and applications
➔ Displays all jobs and all applications, whether or not they match.
Jobs without applications and applications without matching jobs are both shown.
 JOIN: Employees with their skills and proficiency levels
➔ Shows each employee along with their skills, skill proficiency level, and years
of experience.
Helps match employee expertise to job requirements.
 JOIN with Grouping: Employees who have skills required for specific jobs
➔ Lists employees and how well their skills match the requirements of specific
jobs.
It calculates the matching skills count and the match percentage for each
employee-job pair.
  LEFT JOIN: Jobs and Applications Overview
➔ Displays a complete list of jobs along with related application details, if any
applications exist.
 FULL JOIN: Jobs and Applications Overview (all inclusive)
➔ Lists all jobs and all applications, ensuring no job or application is left out —
even if they don't have matching records.

3.4 Views
 A view is a saved SQL query that behaves like a virtual table.
It helps simplify complex queries, improves data security, and provides an easier way to
access specific information.
 vw_JobDetails
➔ Displays job titles, salaries, and the associated employer's company name by
joining the Job and Employer tables.
 vw_ApplicationsSummary
➔ Lists application details including the application ID, employee's full name, job
title applied for, and the application status by joining Application, Employee, and
Job tables.
 vw_InterviewSchedule
➔ Shows scheduled interviews, including candidate name, job title, company name,
interview date, type, interviewer name, and result by joining Interview,
Application, Employee, Job, and Employer tables.
 View_Jobs_By_Company
➔ Lists job IDs and job titles posted by a specific company (Operations Pro), along
with the company name, by joining the Job and Employer tables and applying a filter.

3.5 Triggers

 A trigger is a special type of stored procedure in SQL that automatically runs when specific
events occur in a table (such as INSERT, UPDATE, or DELETE).
 trg_BeforeInsertApplication
➔ Prevents inserting an application if the job's application deadline has already
passed.
If someone tries to apply after the job deadline, an error message 'Cannot apply
for a job past its deadline.' is raised, and the insert is cancelled.
 trg_EnsureDeadline
➔ Automatically sets a default job deadline to 30 days from the current date if no
deadline is provided when inserting a new job posting.
 trg_UpdateApplicationStatusAfterInterview
➔ Updates the application status based on interview results:
o If an interview is recorded and the application was under review, it is updated
to 'Interviewed'.
o If the interview result is 'Failed', the application status is updated to
'Rejected'.
 o If the interview result is 'Passed' and the interview type is 'HR' or 'In-
Person', the application status is updated to 'Offered'.

4 Normalization

The design follows Third Normal Form (3NF):

 No repeating groups.
 No partial dependencies.
 No transitive dependencies.

Each table focuses on a single subject, and all non-key attributes are fully functionally
dependent on the primary key.

5 Testing
 Drop if exists: Tested by dropping tables to reset the database for clean
deployment.
 Insertion tests: Tables were populated manually to check referential integrity (if
you inserted test data).
 Join queries: Complex queries tested relationships between employees, jobs,
employers, and skills.
 Constraints test: Testing invalid inserts (like duplicate primary keys or missing
foreign keys) ensured integrity.

6 ER Diagram