iLab 4 :Retrieving Data From a Single Table
lqkishgriddivz
createomtables.docxCIS336: Create OM database and tables
Starting with Week 4, all labs will utilize the database represented by the ERD in DBmodel.docx. These tables are created and populated with data by the script file (DBcreate.sql). You will need to download this 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 DBcreate.sql save on your local computer. A good idea is to create a CIS337 directory on your C: drive and save the file there.
OPTION 1: Prepare the database for use with Omnymbus Environment :
1. Log onto the Omnymbus EDUPE-APP cloud at: https://devry.edupe.net:8300/.
2. 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 CIS337_xxxx. Once you type in the name, click CREATE.
3. Once created, select this new database. It will be empty. Click EXECUTE SQL.
4. Click Run SQL from file
5. Select FROM UPLOADED FILE and browse to the downloaded file DBcreate.sql.
Then click EXECUTE.
6. The script should execute without errors (troubleshoot if needed). Click Return to Table list.
You now will see the 6 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 database.
OPTION 2: Prepare the database using Local MySQL Environment:
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. Click the button highlighted below to create a new, appropriately named Database (Schema) to be used for this assignment. A good name would be cis337.
Click Apply at the lower right corner.
Click Apply and then Finish.
You should now see this database in your Navigator. You may have to click the refresh button:
2. Now go to FILE – Run SQL script. Browse to the file DBcreate.sql and click open. Select your cis337 Schema as shown below, then click Run.
The script should execute without errors (troubleshoot if needed).
3. Click Close. Refresh your schemas in Navigator window to see the tables:
To view the data in a table, RIGHT-click on a table and choosing Select Rows – Limit 1000
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 tables Page 1
