Skip to content

A project combining the data modeling, engineering, and analysis of SQL databases with the data visualization of Python

Notifications You must be signed in to change notification settings

jbizzlefoshizzle/SQL-Employee-Database

Repository files navigation

Employee Database

sql.jpg

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 ERD.png

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. sql screenshot 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. pandas.png

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

About

A project combining the data modeling, engineering, and analysis of SQL databases with the data visualization of Python

Topics

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published