Using PostgreSQL and Pagila Database to practice creating and using ERDs
- Using pgAdmin 4 and PostgreSQL this article will demonstrate how to Create an Entity Relationship Diagram (ERD) of the Pagila Database to gain a greater understanding of the data you will be working with and to create a reference for which to work off of.
- There will display two separate methods for ERD creation. One is manually done and is much more time intensive but a better learning experience, the other is automatically done by pgAdmin 4 and is orders of magnitude faster and easier for those that are just looking for an ERD to reference when using the Pagila Database.
- There will be links to the necessary information, github repositories and completed ERD for your reference.
PRE-NOTE (If you want to create an ERD fast then read this)
When I initially tried to create an ERD using pgAdmin 4 I clicked on the “New ERD Project” option in the Tools dropdown and it gave me a blank grid to work off of. Because of that I actually had to manually create each table, row, and constraint in the diagram and it took me hours. The next time I attempted to create an ERD I first right-clicked on the database I wanted to work with and instead chose “Generate ERD” and initially it still appeared that I had a blank ERD to start with but when I zoomed out I noticed that an ERD was already created for me and it included all of the accurate table names, row names, constraints and relationships. In the end what had previously taken me hours to complete now only took me 30 seconds.
That being said, I found it very useful to create my own ERD manually. It got me thinking about the process, the data types, constraints, and relationships in a way that I felt further solidified my knowledge of SQL.
This tutorial assumes you already have PostgreSQL, pgAdmin 4, and The Pagila Database installed and set-up. If you need instructions on setting this up please visit my previous article: Simulating A Real-world SQL Environment.
Please have pgAdmin 4 open at this point and your database ready to use.
STEPS FOR CREATING ERD AUTOMATICALLY
STEP 1: Generate ERD tool
This is a Beta tool in pgAdmin 4 version 5.0.
This will open a new window in pgAdmin 4 located where the query window would normally be.
STEP 2: Save and Name Your ERD
STEP 3: Export Your ERD
The ERD is a bit difficult to zoom in and move around within pgAdmin 4 so I found it more helpful to export it to an image file for easier referencing.
MANUALLY CREATING YOUR OWN ERD
To manually create your own ERD the very first step is different.
STEP 1: CREATE NEW ERD
STEP 2: Save and Name Your ERD
STEP 2: Create Your First Table
In this step we will create the table diagrams for the ERD. These boxes contain the table, column, constraint and key information for each table in your database.
IN CASE YOU WANT TO GET DEEPER WITH CONSTRAINTS
STEP 3: Creating Relationships
To complete your ERD you need to create relationships between tables.
You have now created an ERD. I recommend practicing by recreating from scratch the Pagila Database ERD. You can check your work by simply creating an automated ERD as outline above. I also have corralled a bunch of question sets here in my Pagila Questions Sets repository that will help you fine-tune your querying skills.