SQL Challenge
In this project, we were hired as a new data engineer at Pewlett Hackard. We were to take the six remaining CSV files and design a database to hold the information. In transitioning the CSV files to the database, we had to define the columns in the tables as well as determine the primary keys and the foreign keys. Some of the data required us to use composite keys for using data between tables.
Initially in this project, I created an ERD file that showed each table that I was going to create in the database. I was then able to layout the connection between the tables and assign foreign keys and primary keys to the elements in the table. From here, using https://www.quickdatabasediagrams.com/, I was able to connect the elements of the tables with other tables. The ERD diagram showed the relationship between each table. This was really cool to see and help me visualize the data better.
After the ERD, I created the database in PGAdmin4. I found this process to be a bit of a bear. Not only did you have to get the primary key and foreign key assignments correct, you also had to put the tables in a certain order for the CSVs to load without issue. Once I was able to discern the correct order, I was able to load the CSV information to the tables without any issue. And finally, I was able to run the analysis queries that pulled specific data from the tables and gave a clean output of the information.
I really enjoyed this project because I felt like I was really working on something that may be done in the real world. I have also found that I like data engineering.
- Class notes and activities
- ChatGPT
- Collaborated with others in the class