Employee data modeling, engineering, and analysis

Goals:

• Table Design: Designing tables to hold data from existing CSV files. (Data generated by [Mockaroo, LLC] (https://mockaroo.com/) Links to an external site., (2022). Realistic Data Generator.)
• Data Import: Importing the CSV files into a SQL database.
• Data Analysis: Answering analytical questions based on the imported data.

Skills Demonstrated:

• Data Modeling: Creating an Entity-Relationship Diagram (ERD) to model the relationships between tables. This ERD represents the structure of an employee database, capturing essential information about employees, their salaries, departments, and titles.

  Table Employees stores basic details for each employee, including their name, date of birth, gender, and hire date.

  Its attributes: emp_no (Primary Key), emp_title_id, birth_date, first_name, last_name, sex, hire_date.

  Its relationships:

  • Linked to the Salaries table by emp_no.

  • Linked to Titles via emp_title_id.

  • Linked to Department_Employee and Department_Manager tables to associate employees with departments.

  

• Data Engineering: Building a schema for each of the six CSV files, specifying data types, primary keys, foreign keys, and constraints to ensure data integrity.

• Data Analysis: Writing SQL queries to analyze data across single and multiple tables.

  

Tools:

• SQL
• PostgreSQL
• Quick Database Diagrams (https://app.quickdatabasediagrams.com/)