CIS336_CreateOMTables.docx

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;

2. Log onto the Omnymbus EDUPE-APP cloud at: https://devry.edupe.net:8300/.

3. 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.

4. Once created, select this new database. It will be empty. From the commands at the bottom, click EXECUTE SQL.

5. Click Run SQL from file.

6. Select FROM UPLOADED FILE and browse to the downloaded file create_db_om.sql. Then click EXECUTE.

7. 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.

2. 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.

3. Click RUN.

4. 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

image2.png

image3.png

image4.png

image5.png

image6.png

image7.png

image8.png

image9.png

image10.png

image11.png

image12.png

image13.png

image1.png

CIS336Lab7_Instructions.docx

CIS-336: Lab7: Working with Views

LAB OVERVIEW

Lab 7 will introduce the concept of database views. This lab may be completed using either DeVry’s Omnymbus EDUPE-APP lab environment, or a local copy of the MySQL database running on your own computer using the OM database tables. The lab will utilize a set of tables that are represented by the ERD ( OM_ERD.docx) and are created and populated by the script file ( create_OM_db.sql). Follow the instructions in the file CreateOMTables.docx to create your database, tables, and data.

A few IMPORTANT things to note if using EDUPE MySQL:

**There can be NO SPACES in alias names given to a column. For example:

Select unit_price as “Retail Price “ from items; --this does NOT work in EDUPE MySQL.

Any of the following WILL WORK:

Select unit_price as "RetailPrice" from items;

Select unit_price as "Retail_Price" from items;

Select unit_price as Retail_Price from items;

Select unit_price as RetailPrice from items;

**Any calculated fields MUST be given an alias (and note above NO SPACES in alias). For example:

select unit_price * 2 from items; --this does NOT work in EDUPE MySQL

This will work:

select unit_price * 2 as NewPrice from items;

Deliverables

· Lab Report (Answer Sheet) containing both the student-created SQL command(s) for each exercise, and the output showing the results obtained. Be sure your name is on the file.

LAB STEPS: Complete each of the exercises below.

1. Use an ALTER TABLE statement to update the customers table so that the Primary Key field is an auto-increment field, then create TWO insert statements to test proper operation, using your own first and last name for one (and a name of your choice for the second one), and any data you care to imagine for the remaining fields.

IMPORTANT NOTE: When using a LOCAL copy of MySQL, if you attempt to simply issue the ALTER TABLE command you have composed by itself, you should receive an error similar to the following (try it for yourself!).

ERROR 1833: Cannot change column 'customer_id': used in a foreign key constraint 'orders_fk_customers' of table 'om.orders'

(Note – EDUPE will not give this error message, however you should still follow the CORRECT procedure as discussed here to complete this problem).

The reason for this is that you are attempting to alter data in one column that has a defined PK:FK relationship to a field in another table. Referential Integrity rules prevent this. So, how do you resolve such a problem?

One approach to solving this dilemma is to turn off the foreign key checks that implement referential integrity rules. However, the danger here is that other users and processes operating on the database while these constraints are suspended could create or modify data in a way that compromises integrity. We can solve this second problem by preventing other users and processes from altering the data in the table in which we are working until we have turned the foreign key checks back on. We therefore need to construct a script that does the following.

a) Locks the customer table -- lock table customers write;

b) Turns off FK checks -- set foreign_key_checks = 0;

c) Alters the table to add the auto_increment feature to the PK field

d) Turns FK checks back on -- set foreign_key_checks = 1;

e) Unlocks the customer table -- unlock tables;

It is VERY important to consider that altering tables can require a bit of time for very large tables, and that while the table is locked, other users and processes cannot operate. Consequently, this kind of modification should not be done during peak operating hours in a production operation (as a student in a lab exercise, working on your own database, you may do this at any time) but ideally in hours during which the business does not normally operate. In cases where round-the-clock, high availability of a database is required, other approaches may be required. Addressing this problem in a high-availability, high-demand environment is an advanced topic, study of which is outside the scope of this course. Use the outline below to construct your script. Show all commands in your answer sheet along with the output of the commands.

lock table customers write;

set foreign_key_checks = 0;

-- Replace this comment with your ALTER TABLE command to add the auto_increment feature to the PK field

set foreign_key_checks = 1;

unlock tables;

--statements to insert two rows into the table

--verify auto_increment with a select statement

2. The Vice President of Marketing for your firm wants the firm’s sales representatives to be able to directly view and edit customer details, but only for the state to which a particular sales representative is assigned. You have suggested that this need can be addressed with a view. For example, a view could be created for one particular state, and user account permissions for accessing that view granted only to sales representatives from that state. The VP has asked you to quickly create a simple proof-of-concept demonstrating how this might work. Complete the following steps:

a. Construct a view on the customers table called CA_CUSTOMERS that consists of all data about customers that live in California.

b. Display the data using this view to verify that only customers that reside in California are visible.

c. Prove that It is possible to add or update records through this view by updating the record for Karina Lacy to change the spelling of Karina’s last name to Lacie.

d. Display the data using the customer table to verify that the change has been made.

Show all commands in your answer sheet along with the output of the commands.

3. The Senior Customer Service Manager has requested the ability to create a report at any time that will show shipped orders that took some specified number of days to fulfill.

a. Create a view named SHIPPING_TIME that lists only customer_first_name, customer_last_name, order_date, shipped_date, and the calculated field days_to_fulfill (use the DATEDIFF function) showing the number of days between when the customer placed the order and when it was shipped. Show the data from this view.

Now let’s do some queries by adding sorting and filters USING THIS VIEW, WITHOUT CHANGING IT.

b. Use the view to display the data sorted by highest to lowest days to ship

c. Use the view to display only the orders that took less than 10 days to ship.

d. Use the view to display only the orders that took more than 30 days to ship.

4. Queries that require joins and aggregate functions can be easier to construct when using a view as a “temporary” table. Consider a report to show total sales by artist.

a. First create a view called SalesData that displays the order_id, item_id, the calculated field ItemTotal (which is quantity times price), the title and artist_id.

b. Display the data in the SalesData view sorted by artist_id. Does this help you to “visualize” how to group the data to create the totals?

c. Create a query USING THIS VIEW and the appropriate aggregate function to display artist_id and the total sales for each artist.

d. Now join to the artist table in order to display the artist_name along with the total sales.

5. Now use this same method to display the total sales per customer.

a. Create a view called SalesData with the appropriate data. At a minimum you will need customer_id and the calculated item total. DO NOT use the customer table in this view, it will be joined later.

b. Display the data in your view sorted by customer_id. Does this help you to “visualize” how to group the data to create the totals?

c. Create a query USING THIS VIEW and the appropriate aggregate function to display customer_id and the total sales for each customer.

d. Now join to the customer table in order to display the customer_name as a single field named Customer along with the total sales. Sort the report by Total sales in descending order.

This is the end of Lab 7.

CIS336 Lab 7 Page 4

create_OM_db.sql

-- ************************************************************* -- This script creates the database -- for Labs 4-7 based on OM (Order Management) database -- **REVISED by PRP to remove some orders -- **REVISED by PRP to add artist table -- **REVISED by PRP to link employee table to orders -- ************************************************************* DROP TABLE IF EXISTS customers; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS order_details; DROP TABLE IF EXISTS items; DROP TABLE IF EXISTS artists; DROP TABLE IF EXISTS employees; -- create tables CREATE TABLE customers ( customer_id INT , customer_first_name VARCHAR(20), customer_last_name VARCHAR(20) NOT NULL, customer_address VARCHAR(50) NOT NULL, customer_city VARCHAR(20) NOT NULL, customer_state CHAR(2) NOT NULL, customer_zip CHAR(5) NOT NULL, customer_phone CHAR(10) NOT NULL, customer_fax CHAR(10), CONSTRAINT customers_pk PRIMARY KEY (customer_id) ); CREATE TABLE artists ( artist_id INT NOT NULL, artist_name VARCHAR(30), CONSTRAINT artist_pk PRIMARY KEY (artist_id) ); CREATE TABLE items ( item_id INT NOT NULL, title VARCHAR(50) NOT NULL, artist_id INT NOT NULL, unit_price DECIMAL(9,2) NOT NULL, CONSTRAINT items_pk PRIMARY KEY (item_id), CONSTRAINT items_fk_artists FOREIGN KEY (artist_id) REFERENCES artists (artist_id) ); CREATE TABLE employees ( employee_id INT NOT NULL, last_name VARCHAR(20) NOT NULL, first_name VARCHAR(20) NOT NULL, manager_id INT , CONSTRAINT employees_pk PRIMARY KEY (employee_id), CONSTRAINT emp_fk_mgr FOREIGN KEY (manager_id) REFERENCES employees(employee_id) ); CREATE TABLE orders ( order_id INT NOT NULL, customer_id INT NOT NULL, order_date DATE NOT NULL, shipped_date DATE, employee_id INT, CONSTRAINT orders_pk PRIMARY KEY (order_id), CONSTRAINT orders_fk_customers FOREIGN KEY (customer_id) REFERENCES customers (customer_id), CONSTRAINT orders_fk_employees FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ); CREATE TABLE order_details ( order_id INT NOT NULL, item_id INT NOT NULL, order_qty INT NOT NULL, CONSTRAINT order_details_pk PRIMARY KEY (order_id, item_id), CONSTRAINT order_details_fk_orders FOREIGN KEY (order_id) REFERENCES orders (order_id), CONSTRAINT order_details_fk_items FOREIGN KEY (item_id) REFERENCES items (item_id) ); -- insert rows into tables INSERT INTO customers VALUES (1,'Korah','Blanca','1555 W Lane Ave','Columbus','OH','43221','6145554435','6145553928'), (2,'Yash','Randall','11 E Rancho Madera Rd','Madison','WI','53707','2095551205','2095552262'), (3,'Johnathon','Millerton','60 Madison Ave','New York','NY','10010','2125554800',NULL), (4,'Mikayla','Davis','2021 K Street Nw','Washington','DC','20006','2025555561',NULL), (5,'Kendall','Mayte','4775 E Miami River Rd','Cleves','OH','45002','5135553043',NULL), (6,'Kaitlin','Hostlery','3250 Spring Grove Ave','Cincinnati','OH','45225','8005551957','8005552826'), (7,'Derek','Chaddick','9022 E Merchant Wy','Fairfield','IA','52556','5155556130',NULL), (8,'Deborah','Davis','415 E Olive Ave','Fresno','CA','93728','5595558060',NULL), (9,'Karina','Lacy','882 W Easton Wy','Los Angeles','CA','90084','8005557000',NULL), (10,'Kurt','Nickalus','28210 N Avenue Stanford','Valencia','CA','91355','8055550584','055556689'), (11,'Kelsey','Eulalia','7833 N Ridge Rd','Sacramento','CA','95887','2095557500','2095551302'), (12,'Anders','Rohansen','12345 E 67th Ave NW','Takoma Park','MD','24512','3385556772',NULL), (13,'Thalia','Neftaly','2508 W Shaw Ave','Fresno','CA','93711','5595556245',NULL), (14,'Gonzalo','Keeton','12 Daniel Road','Fairfield','NJ','07004','2015559742',NULL), (15,'Ania','Irvin','1099 N Farcourt St','Orange','CA','92807','7145559000',NULL), (16,'Dakota','Baylee','1033 N Sycamore Ave.','Los Angeles','CA','90038','2135554322',NULL), (17,'Samuel','Jacobsen','3433 E Widget Ave','Palo Alto','CA','92711','4155553434',NULL), (18,'Justin','Javen','828 S Broadway','Tarrytown','NY','10591','8005550037',NULL), (19,'Kyle','Marissa','789 E Mercy Ave','Phoenix','AZ','85038','9475553900',NULL), (20,'Erick','Kaleigh','Five Lakepointe Plaza, Ste 500','Charlotte','NC','28217','7045553500',NULL), (21,'Marvin','Quintin','2677 Industrial Circle Dr','Columbus','OH','43260','6145558600','6145557580'), (22,'Rashad','Holbrooke','3467 W Shaw Ave #103','Fresno','CA','93711','5595558625','5595558495'), (23,'Trisha','Anum','627 Aviation Way','Manhatttan Beach','CA','90266','3105552732',NULL), (24,'Julian','Carson','372 San Quentin','San Francisco','CA','94161','6175550700',NULL), (25,'Kirsten','Story','2401 Wisconsin Ave NW','Washington','DC','20559','2065559115',NULL); INSERT INTO artists(artist_id,artist_name) VALUES (10, 'Umani'), (11, 'The Ubernerds'), (12, 'No Rest For The Weary'), (13, 'Burt Ruggles'), (14, 'Sewed the Vest Pocket'), (15, 'Jess & Odie'), (16, 'Onn & Onn'); INSERT INTO items (item_id,title,artist_id,unit_price) VALUES (1,'Umami In Concert',10,17.95), (2,'Race Car Sounds',11,13), (3,'No Rest For The Weary',12,16.95), (4,'More Songs About Structures and Comestibles',12,17.95), (5,'On The Road With Burt Ruggles',13,17.5), (6,'No Fixed Address',14,16.95), (7,'Rude Noises',15,13), (8,'Burt Ruggles: An Intimate Portrait',13,17.95), (9,'Zone Out With Umami',10,16.95), (10,'Etcetera',16,17); INSERT INTO employees VALUES (1,'Smith', 'Cindy', null), (2,'Jones', 'Elmer', 1), (3,'Simonian', 'Ralph', 2), (9,'Locario', 'Paulo',1), (8,'Leary', 'Rhea',9), (4,'Hernandez','Olivia',9), (5,'Aaronsen', 'Robert',4), (6,'Watson', 'Denise',8), (7,'Hardy', 'Thomas',2); INSERT INTO orders VALUES (19, 1, '2012-10-23', '2012-10-28', 6), (29, 8, '2012-11-05', '2012-11-11', 6), (32, 11, '2012-11-10', '2012-11-13', NULL), (45, 2, '2012-11-25', '2012-11-30', NULL), (70, 10, '2012-12-28', '2013-01-07', 5), (89, 22, '2013-01-20', '2013-01-22', 7), (97, 20, '2013-01-29', '2013-02-02', 5), (118, 3, '2013-02-24', '2013-02-28', 7), (144, 17, '2013-03-21', '2013-03-29', NULL), (158, 9, '2013-04-04', '2013-04-20', NULL), (165, 14, '2013-04-11', '2013-04-13', NULL), (180, 24, '2013-04-25', '2013-05-30', NULL), (231, 15, '2013-06-14', '2013-06-22', NULL), (242, 23, '2013-06-24', '2013-07-06', 3), (264, 9, '2013-07-15', '2013-07-18', 6), (298, 18, '2013-08-18', '2013-09-22', 3), (321, 2, '2013-09-09', '2013-10-05', 6), (381, 7, '2013-11-08', '2013-11-16', 7), (413, 17, '2013-12-05', '2014-01-11', 7), (442, 5, '2013-12-28', '2014-01-03', 5), (479, 1, '2014-01-30', '2014-03-03', 3), (491, 16, '2014-02-08', '2014-02-14', 5), (523, 3, '2014-03-07', '2014-03-15', 3), (548, 2, '2014-03-22', '2014-04-18', NULL), (550, 17, '2014-03-23', '2014-04-03', NULL), (601, 16, '2014-04-21', '2014-04-27', NULL), (607, 20, '2014-04-25', '2014-05-04', NULL), (624, 2, '2014-05-04', '2014-05-09', NULL), (627, 17, '2014-05-05', '2014-05-10', NULL), (630, 20, '2014-05-08', '2014-05-18', 7), (651, 12, '2014-05-19', '2014-06-02', 7), (658, 12, '2014-05-23', '2014-06-02', 7), (687, 17, '2014-06-05', '2014-06-08', NULL), (693, 9, '2014-06-07', '2014-06-19', NULL), (703, 19, '2014-06-12', '2014-06-19', 7), (778, 13, '2014-07-12', '2014-07-21', 7), (796, 17, '2014-07-19', '2014-07-26', 5), (800, 19, '2014-07-21', '2014-07-28', NULL), (802, 2, '2014-07-21', '2014-07-31', NULL), (824, 1, '2014-08-01', NULL, NULL), (827, 18, '2014-08-02', NULL, NULL), (829, 9, '2014-08-02', NULL, NULL); INSERT INTO order_details VALUES (381,1,1),(601,9,1), (442,1,1), (523,9,1), (630,5,1), (778,1,1), (693,10,1), (118,1,1), (264,7,1), (607,10,1), (624,7,1), (658,1,1), (800,5,1), (158,3,1), (321,10,1), (687,6,1), (827,6,1), (144,3,1), (479,1,2), (630,6,2),(796,5,1),(97,4,1), (601,5,1), (800,1,1), (29,10,1), (70,1,1), (165,4,1), (180,4,1), (231,10,1), (413,10,1), (491,6,1), (607,3,1), (651,3,1), (703,4,1), (802,3,1), (824,7,2), (829,1,1), (550,4,1), (796,7,1), (693,6,1), (29,3,1), (32,7,1), (242,1,1), (298,1,1), (479,4,1), (548,9,1), (627,9,1), (778,3,1), (19,5,1), (89,4,1), (242,6,1), (264,4,1), (550,1,1), (693,7,3), (824,3,1), (829,5,1), (829,9,1);

Previous Next

Get help from top-rated tutors in any subject.

Efficiently complete your homework and academic assignments by getting help from the experts at homeworkarchive.com