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!

Objectives

• Learn how to design a normalized database schema.
• Learn how to create normalized database tables.
• Learn how to import data into a database and transform it into normalized tables.

Lesson Outline

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