MISSION 250

Guided Project: Building a Database for Crime Reports

In the previous lessons of this, we've been exploring PostgreSQL. We discussed the different datatypes and their impact on performance when querying tables. We also evaluated the performance of loading and extracting data to and from Postgres tables. Finally, we discussed user and database management, and best practices. 

In this guided project, you will put all of your new skills together to build a database for storing data related to crimes that occurred in Boston. The goal of this guided project is for you to create a database named `crimes_db` with a table, `boston_crimes,` with appropriate datatypes for storing the data from the `boston.csv file`. 

You will be creating this table inside a schema named crimes. You will also create readonly and readwrite groups with the appropriate privileges, and create a user for each of these groups.

These projects are meant to be challenging to better prepare you for the real world, so don't be discouraged if you have to refer back to previous lessons before you can complete the project. If you haven't worked with Jupyter Notebook before or need a refresher, we recommend completing our Jupyter Notebook Guided Project before continuing.

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

  • Pracitce building a database from scratch.
  • Practice loading data from a CSV into a database.

Mission Outline

1. Introduction
2. Creating the Crime Database
3. Creating an Auxiliary Function
4. Finding the Maximum Length
5. Creating the Table
6. Loading the Data
7. Revoking Public Privileges
8. Creating User Groups
9. Creating Users
10. Testing

postgres-for-data-engineers

Course Info:

Intermediate

The median completion time for this course is 5.2 hours. View Details

This course requires a premium subscription. This course includes five missions, one installation tutorial and one guided project.  It is the first course in the Data Engineer path.

START LEARNING FREE

Take a Look Inside