Exercises - Managing Database Objects¶

This exercise is primarily to assess your capabilities related to put all important DDL concepts in practice by coming up with solution for a typical data migration problem from one database (mysql) to another (postgres).

Here are the high level steps for database migration from one type of database to another type of database.
- Extract DDL Statements from source database (MySQL).
- Extract the data in the form of delimited files and ship them to target database.
- Refactor scripts as per target database (Postgres).
- Create tables in the target database.
- Execute pre-migration steps (disable constraints, drop indexes etc).
- Load the data using native utilities.
- Execute post-migration steps (enable constraints, create or rebuild indexes, reset sequences etc).
- Sanity checks with basic queries.
- Make sure all the impacted applications are validated thoroughly.
We have scripts and data set available in our GitHub repository. If you are using our environment the repository is already cloned under /data/retail_db.
It have scripts to create tables with primary keys. Those scripts are generated from MySQL tables and refactored for Postgres.
- Script to create tables: create_db_tables_pg.sql
- Load data into tables: load_db_tables_pg.sql
Here are the steps you need to perform to take care of this exercise.
- Create tables
- Load data
- All the tables have surrogate primary keys. Here are the details.
  - orders.order_id
  - order_items.order_item_id
  - customers.customer_id
  - products.product_id
  - categories.category_id
  - departments.department_id
- Get the maximum value from all surrogate primary key fields.
- Create sequences for all surrogate primary key fields using maximum value. Make sure to use standard naming conventions for sequences.
- Ensure sequences are mapped to the surrogate primary key fields.
- Create foreign key constraints based up on this information.
  - orders.order_customer_id to customers.customer_id
  - order_items.order_item_order_id to orders.order_id
  - order_items.order_item_product_id to products.product_id
  - products.product_category_id to categories.category_id
  - categories.category_department_id to departments.department_id
- Insert few records in departments to ensure that sequence generated numbers are used for department_id.
Here are the commands to launch psql and run scripts to create tables as well as load data into tables.

psql -U itversity_retail_user \
  -h localhost \
  -p 5432 \
  -d itversity_retail_db \
  -W

\i /data/retail_db/create_db_tables_pg.sql

\i /data/retail_db/load_db_tables_pg.sql

We use this approach of creating tables, loading data and then adding constraints as well as resetting sequences for large volume data migrations from one database to another database.
Here are the commands or queries you need to come up with to solve this problem.

Exercise 1¶

Queries to get maximum values from surrogate primary keys.

Exercise 2¶

Commands to add sequences with START WITH pointing to the maximum value for the corresponding surrogate primary key fields. Make sure to use meaningful names to sequences TABLENAME_SURROGATEFIELD_seq (example: users_user_id_seq for users.user_id)

Exercise 3¶

Commands to alter sequences to bind them to corresponding surrogate primary key fields.

Exercise 4¶

Add Foreign Key constraints to the tables.

Validate if the tables have data violataing foreign key constraints (Hint: You can use left outer join to find rows in child table but not in parent table)
Alter tables to add foreign keys as specified.
Here are the relationships for your reference.
- orders.order_customer_id to customers.customer_id
- order_items.order_item_order_id to orders.order_id
- order_items.order_item_product_id to products.product_id
- products.product_category_id to categories.category_id
- categories.category_department_id to departments.department_id
Solution should contain the following:
- Commands to add foreign keys to the tables.

Exercise 5¶

Queries to validate whether constraints are created or not. You can come up with queries against information_schema tables such as columns, sequences etc.

Mastering SQL using Postgresql