Guided Project: Designing and Creating a Database
In this guided project for this SQL Intermediate course, you'll synthesize all the concepts you have learned in the previous lessons and apply them to designing, creating, and populating a normalized SQL database. By this point, you have the SQL skills to work through a SQL-based data science project from beginning to end, and that’s exactly what you’ll do in this guided project.
Designing and creating a database is an essential skill for anyone in a Data Analyst or Data Scientist role, so this project will also make an excellent showcase of your SQL skills for your portfolio or GitHub.
In this project, you’ll be working with Major League Baseball data sourced from a stats compiler called Retrosheet, which has data going back as far as the 1800s! Your goal will be to build a robust SQL database of game-level statistics using the data we’ve extracted from this statistics compilation. And as with all guided projects, we encourage you to experiment and extend your project, taking it in unique directions to make it a more compelling addition to your portfolio!
1. Getting to Know the Data
2. Importing Data into SQLite
3. Looking for Normalization Opportunities
4. Planning a Normalized Schema
5. Creating Tables Without Foreign Key Relations
6. Adding The Team and Game Tables
7. Adding the Team Appearance Table
8. Adding the Person Appearance Table
9. Removing the Original Tables
10. Next Steps