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
- Insert few records in
departmentsto ensure that sequence generated numbers are used fordepartment_id.
- Here are the commands to launch
psqland 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
DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS customers CASCADE;
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS categories CASCADE;
DROP TABLE IF EXISTS departments CASCADE;
\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.
In [1]:
%load_ext sql
In [2]:
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db
Exercise 1¶
Queries to get maximum values from surrogate primary keys.
In [4]:
%%sql
SELECT max(category_id) FROM categories;
Out[4]:
In [5]:
%%sql
SELECT max(customer_id) FROM customers;
Out[5]:
In [6]:
%%sql
SELECT max(department_id) FROM departments;
Out[6]:
In [7]:
%%sql
SELECT max(order_item_id) FROM order_items;
Out[7]:
In [8]:
%%sql
SELECT max(order_id) FROM orders;
Out[8]:
In [9]:
%%sql
SELECT max(product_id) FROM products;
Out[9]:
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)
In [10]:
%%sql
CREATE SEQUENCE categories_category_id_seq
START WITH 59
INCREMENT BY 1;
Out[10]:
In [11]:
%%sql
CREATE SEQUENCE customers_customer_id_seq
START WITH 12436
INCREMENT BY 1;
Out[11]:
In [12]:
%%sql
CREATE SEQUENCE departments_department_id_seq
START WITH 8
INCREMENT BY 1;
Out[12]:
In [13]:
%%sql
CREATE SEQUENCE orders_order_id_seq
START WITH 68884
INCREMENT BY 1;
Out[13]:
In [15]:
%%sql
CREATE SEQUENCE order_items_order_item_id_seq
START WITH 172199
INCREMENT BY 1;
Out[15]:
In [17]:
%%sql
CREATE SEQUENCE products_product_id_seq
START WITH 1346
INCREMENT BY 1;
Out[17]:
Exercise 3¶
Commands to alter sequences to bind them to corresponding surrogate primary key fields.
In [18]:
%%sql
ALTER TABLE categories
ALTER COLUMN category_id
SET DEFAULT nextval('categories_category_id_seq');
Out[18]:
In [19]:
%%sql
ALTER TABLE customers
ALTER COLUMN customer_id
SET DEFAULT nextval('customers_customer_id_seq');
Out[19]:
In [20]:
%%sql
ALTER TABLE departments
ALTER COLUMN department_id
SET DEFAULT nextval('departments_department_id_seq');
Out[20]:
In [21]:
%%sql
ALTER TABLE orders
ALTER COLUMN order_id
SET DEFAULT nextval('orders_order_id_seq');
Out[21]:
In [22]:
%%sql
ALTER TABLE order_items
ALTER COLUMN order_item_id
SET DEFAULT nextval('order_items_order_item_id_seq');
Out[22]:
In [23]:
%%sql
ALTER TABLE products
ALTER COLUMN product_id
SET DEFAULT nextval('products_product_id_seq');
Out[23]:
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
- Solution should contain the following:
- Commands to add foreign keys to the tables.
In [24]:
%%sql
ALTER TABLE orders
ADD CONSTRAINT orders_customers_fk
FOREIGN KEY (order_customer_id)
REFERENCES customers(customer_id);
Out[24]:
In [25]:
%%sql
ALTER TABLE order_items
ADD CONSTRAINT order_items_orders_fk
FOREIGN KEY (order_item_order_id)
REFERENCES orders(order_id);
Out[25]:
In [26]:
%%sql
ALTER TABLE order_items
ADD CONSTRAINT order_items_products_fk
FOREIGN KEY (order_item_product_id)
REFERENCES products(product_id);
Out[26]:
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.
In [35]:
%%sql
SELECT * FROM information_schema.sequences LIMIT 10
Out[35]:
In [36]:
%%sql
SELECT * FROM information_schema.columns
WHERE table_name = 'orders';
Out[36]:
In [39]:
%%sql
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'orders'
Out[39]:
In [ ]: