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!
- Learn how to create a local Postgres database.
- Learn how to connect to a local Postgres database
- Creating the Crime Database
- Creating an Auxiliary Function
- Finding the Maximum Length
- Creating the Table
- Loading the Data
- Revoking Public Privileges
- Creating User Groups
- Creating Users