Employee Database

Background

My task here was a research project on employees of this corporation from the 1980s and 1990s. All that remained of the database of employees from this period are six CSV files.

I designed tables to hold data in the CSVs, imported the CSVs into a SQL database, and queried the data using Python/Pandas/Jupyter Notebook.

Data Modeling

After inspecting the CSVs, I sketched out an entity relationship diagram (ERD) of the tables

Data Engineering

Using the ERD, I created a table schema for each of the CSV files. I specified data types, primary keys, foreign keys, and other constraints. I then imported each CSV file into its corresponding SQL table.

Data Analysis

The following are a sample of the queries run:

Listing the employee number, last name, first name, gender, and salary of each employee
Listing the manager of each department with specific information
Listing all employees in Sales, including their employee number, last name, first name, and department name
In descending order, listing the frequency count of employee last names

Generating a visualization of the data

I imported the SQL database into Pandas. Using the Python SQL toolkit and Object Relational Mapper "SQLAlchemy", I used the create_engine function to interact directly with the database using Jupyter Notebook.

I used SELECT statements to call in the "salaries" and "titles" tables from the SQL database, then combined the dataframes in order to better aggregate the data. I then found the mean salary of each job title, before using the Python plotting library Matplotlib to create a bar graph visualization.