Overview

The purpose of this project is to elucidate data of interest which include

• Position titles of current employees per position title across all departments born between 1952 and 1955 that are entering retirement age,

• Number of current employees per position title across all departments born between 1952 and 1955 that are reaching retirement age, and

• Current employees born in 1965 who are eligible for a mentorship program to train new hires once employees retire.

To accomplish these goals I build an employee database from six comma-separated values (CSV) files using an entity relationship diagram (ERD), then query the relational database system PostgreSQL with pgAdmin as its interface to extract data. Extracted data is then exported as CSV files to report to the Human Resources Department.

---

Results

• A screenshot of the ERD is shown below.

  

• A total of 7 position titles have current employees reaching retirement age.

• Titles

  • Senior Engineer
  • Senior Staff
  • Engineer
  • Staff
  • Technique Leader
  • Assistant Engineer
  • Manager

• Amount of employees of each perspective title that will be retiring:

  • 25,916 Senior Engineers
  • 24,926 Senior Staff
  • 9,285 Engineers
  • 7,636 Staff
  • 3,603 Technique Leaders
  • 1,090 Assistant Engineers
  • 2 Managers

• A screenshot of table retiring_titles is shown below.

  

• A total of 1,549 current employees are eligible for the mentorship program.

• A screenshot of the table mentorship_eligibility is shown below.

  

---

Summary

Based on the provided data, one may conclude the following:

• A total of 7 titles which sum up to 72,458 total employees are reaching retirement age. This implies that an average of approximately 10,351 vacancies per title will need to be filled.

• Only 1,549 employees are considered eligible for the mentorship program therefore unless one mentor is assigned to multiple new hires, the number is insufficient even for only mentoring Senior Engineers positions.

Through further querying, one may also notice:

• No manager is considered eligible for mentorship even if two manager vacancies are anticipated to appear.

  A screenshot of table mentor_titles is shown below.

  

• Of all employees eligible for mentorship, the most recently hired is Oscar Oppitz, employee number 418247, title Senior Staff, who was hired on July 27, 2002. This implies that all potential mentors have at least ten years of experience working with Pewlett Hackard and likely will be excellent mentors.

  A screenshot of table mentor_from_date is shown below.

  

• Employees eligible for mentorship have salaries ranging from $40,000 to $114,784, which suggests that Pewlett Hackard may want to set aside a budget to pay the mentors a reasonable salary.

  A screenshot of table mentor_salary is shown below.

  

---

Resources

Data source:

departments.csv
dept_emp.csv
dept_manager.csv
employees.csv
salaries.csv
titles.csv

Tools:

pgAdmin
PostgreSQL 11.15
Python 3.9.7
Structured Query Language (SQL)
Visual Studio Code (VSCode) 1.63.2

---

Contact

Email: kate.wang00001@gmail.com

LinkedIn: katewang01