CIS336: Create OM Database and Tables

Starting with Week 4, all labs will utilize the Order Management (OM) database and sample data. These tables are created by the script file (create_OM_db.sql) found in the Doc Sharing area of the website. You will need to download the file, modify/remove the CREATE DATABASE statements or manually create the correctly named database as appropriate to your lab environment, and then run the script to create your database, tables, and data. The Entity Relationship Diagram (ERD) for this database is provided below, for your reference.

Follow the steps below to create the tables based on the environment you are using.

Option 1: Use DeVry’s Omnymbus EDUPE-APP lab environment

Option 2: Use a local copy of the MySQL database running on your own computer.

For either option, first download the file create_OM_db.sql from Doc Sharing and save on your local computer. A good idea is to create a CIS336 directory on your C: drive and save the file there.

OPTION 1: Prepare the database for use with Omnymbus Environment :

  1. Open the file create_OM_db.sql in notepad (do NOT use Microsoft Word!) and remove the following lines.

-- create database

DROP DATABASE IF EXISTS om;

CREATE DATABASE om;

-- select database

USE om;

  1. Log onto the Omnymbus EDUPE-APP cloud at: https://devry.edupe.net:8300/.
  1. Create a new, appropriately named Database (Schema) to be used for this assignment. Be sure that the database name ends with _xxxx (where xxxx is your login) to comply with Omnymbus naming requirements. A good name would be OM_xxxx. Once you type in the name, click CREATE.
  1. Once created, select this new database. It will be empty. From the commands at the bottom, click EXECUTE SQL.
  1. Click Run SQL from file.
  1. Select FROM UPLOADED FILE and browse to the downloaded file create_db_om.sql. Then click EXECUTE.
  1. The script should execute without errors (troubleshoot if needed). Click Return to Table list.

You will see the four tables.

You can click on a table and view data to confirm that the tables are properly populated. You are now ready to begin queries with the OM database.

OR you can view the data by executing a query.

Go to Execute SQL and type in

Select * from items;

Click Execute

OPTION 2: Prepare the database using Local MySQL Environment.

  1. Make sure that the MySQL database is running, and that you have completed all installation steps, including creating a user account that you will use for your labs. This account must have sufficient privileges to create databases and objects.
  1. Log into MySQL using whatever SQL editor you have chosen (e.g., MySQL Workbench). Click on your Local instance. Then go to FILE—Run SQL script. Browse to the file create_OM_db.sql and click open.
  1. Click RUN.
  1. Click Close. Refresh your schemas in Navigator window.

Now you will see the OM schema.

To view the data in a table, RIGHT-click on a table and choosing Select Rows—Limit 1000.

OR you can type the following query into the query editor screen and run.

This query should produce the following results.

Note that you will ONLY need to create and populate the tables once. The tables and data are saved when you exit EDUPE or local MySQL.

You are now ready to proceed with your lab.

CIS336 Creating OM tables Page 7