PostgreSQL - ERD - Restore

What follows is a project explaining how to

  1. Create an ERD of a database
  2. Generate and execute an SQL script from an ERD to create a schema
  3. Load the database schema with data

ERD

ERD Designer

ERD Designer can create entity relationship diagrams. pgAdmin includes the ERD tool where you can design your ERD and then generate an SQL script to create your database and objects based on your design.

The db comes from here: HR Sample Db- Copyright Oracle Corporation. Here is an overview of the tables in the HR db

  • Create or Start your PostgreSQL instance
  • Note the password: gHQPQTW5DdfqlIqSVzPL57Zw
  • Proceed to your pgAdmin home page

Create HR db

  • In the tree-view, expand Servers > postgres > Databases
  • Enter your PostgreSQL service session password if prompted during the process
  • Right-click on Databases and go to Create > Database
  • Type HR as the name of the database and click Save.

Create ERD

  • In the tree-view, expand HR
  • Right-click on HR and select ERD For Database

Add Table employees to ERD

  • Click + box in the Tab Menu
  •  Add table
  • On the General tab, in the Name box, type employees as the name of the table
  • Don’t click OK, proceed to the next step

Setup Cols in Table

  • Switch to the Columns tab
  • Click Add new row to add the necessary column placeholders.
  • Now enter the employees table definition information as shown in the image below to create its entity diagram
  • Click OK.

Add Table jobs ERD

  • Repeat the above table process for jobs
  • Here are the columns

Add Table departments ERD

Add Table locations ERD

Now we have the ERD for all the tables

Relationships

Now we’ll create relationships between the entities by adding foreign keys to the tables.

Employees > Departments

  • Select the entity diagram employees and click One-to-Many link

  • Now enter the definition information for a foreign key on the employees table as shown in the image below to create the relationship
  • Then click OK.

Emplyees > Jobs

Departments > Locations

Departments > Employees

Script ERD to Schema

Now we’ll generate and execute a SQL script from the ERD

  • In the ERD view
  • Click on Generate SQL icon

View SQL script

  • Automatically a SQL pane opens displaying the script once you click on the button above
-- This script was generated by the ERD tool in pgAdmin 4.
-- Please log an issue at https://github.com/pgadmin-org/pgadmin4/issues/new/choose if you find any bugs, including reproduction steps.
BEGIN;


CREATE TABLE IF NOT EXISTS public.departments
(
    department_id integer NOT NULL,
    department_name character varying(30) COLLATE pg_catalog."default" NOT NULL,
    manager_id integer,
    location_id integer,
    PRIMARY KEY (department_id)
);

CREATE TABLE IF NOT EXISTS public.employees
(
    employee_id integer NOT NULL,
    first_name character varying(20) COLLATE pg_catalog."default",
    last_name character varying(25) COLLATE pg_catalog."default" NOT NULL,
    email character varying(100) COLLATE pg_catalog."default" NOT NULL,
    phone_number character varying(20) COLLATE pg_catalog."default",
    hire_date date NOT NULL,
    job_id character varying(10) COLLATE pg_catalog."default" NOT NULL,
    salary numeric(8, 2) NOT NULL,
    commission_pct numeric(2, 2),
    manager_id integer,
    department_id integer,
    PRIMARY KEY (employee_id)
);

CREATE TABLE IF NOT EXISTS public.jobs
(
    job_id character varying(10) COLLATE pg_catalog."default" NOT NULL,
    job_title character varying(35) COLLATE pg_catalog."default" NOT NULL,
    min_salary numeric(8, 2),
    max_salary numeric(8, 2),
    CONSTRAINT jobs_pkey PRIMARY KEY (job_id)
);

CREATE TABLE IF NOT EXISTS public.locations
(
    location_id integer NOT NULL,
    street_address character varying(40) COLLATE pg_catalog."default",
    postal_code character varying(12) COLLATE pg_catalog."default",
    city character varying(30) COLLATE pg_catalog."default" NOT NULL,
    state_province character varying(25) COLLATE pg_catalog."default",
    country_id character(2) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT locations_pkey PRIMARY KEY (location_id)
);

ALTER TABLE IF EXISTS public.departments
    ADD FOREIGN KEY (location_id)
    REFERENCES public.locations (location_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.departments
    ADD FOREIGN KEY (manager_id)
    REFERENCES public.employees (employee_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.employees
    ADD FOREIGN KEY (department_id)
    REFERENCES public.departments (department_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.employees
    ADD FOREIGN KEY (job_id)
    REFERENCES public.jobs (job_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;

END;

Execute Script

  • You’ll see at the bottom right of the screen a green popup telling you the time it took to run the script (in milliseconds for this task)

So far we’ve created a database and then created the tables and set all the relationships between the tables. So we have created the schema for the db. Now we’ll

Load db Schema with Data

Here we will load the db schema we’ve just created with data using the pgAdmin Restore feature

  • Download the HR_pgsql_dump_data_for_example-exercise.tar PostgreSQL dump file (containing the partial HR database data) using the link below to your local computer.
  • HR tar location
  • We will follow the same instructions we used in PostgreSQL page to Load Data with CSV File

Restore

Steps

  • Left pane tree>click on HR (name of db)
  • Restore
  • General Tab> Format will be Custom or tar >from the … >Select File> var > lib > pgadmin >

  • Choose the … icon in upper right
  • UPLOAD
  • Drag file to window
  • Once upload is complete, close window

  • Select the file just uploaded

  • From OPTIONS tab
  • Disable > Trigger Click YES
  • Click on RESTORE

Practice

In this practice exercise, first you will finish creating a partially complete ERD for the HR database. Next, you will generate and execute an SQL script to build the complete schema of the HR database from its ERD. Finally, you will load the complete database schema with data by using the Restore feature.

  • Download the HR_pgsql_ERD_for_practice-exercise.pgerd ERD file (containing a partial HR database ERD based on the one that you created in Task A of the Example Exercise) below to your local computer.

  • data is here

  • In pgAdmin, create a new database named HR_Complete.

  • Open the ERD Tool and use Load from file to load the HR_pgsql_ERD_for_practice-exercise.pgerd file downloaded in step 2 above

  • You will see the previous four entity diagrams along with relationships that you created in the Example Exercise. You will also see three new entity diagrams for the job_history, regions, and countries tables and one new relationship within the entity diagram of the employees table between manager_id as local column and employee_id as referenced column.

Countries > Regions

Job_history > departments

Job_history > employees

Job_history > jobs

Locations > countries

ERD

Here is what the ERD will look like