For Online Class Help or Private Tutoring Service email us at [email protected] or WhatsApp us at +1 (781) 656-7669

Homework Provider

 
  • Take My Class
  • Take My Exam
  • How It Works
  • Solutions
    • Questions
  • Testimonials
  • Blog
  • Who Are We
  • Contact Us
  • Take My Class
  • Take My Exam
  • How It Works
  • Solutions
    • Questions
  • Testimonials
  • Blog
  • Who Are We
  • Contact Us
  1. Home
  2. Questions
  3. DeVry CIS336 iLab 7 Working with Views

DeVry CIS336 iLab 7 Working with Views

iLab 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:

  1. Select unit_price as “RetailPrice” from items;
  2. Select unit_price as “Retail_Price” from items;
  3. Select unit_price as Retail_Price from items;
  4. 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.

take-my-online-class-thumbnail
Play Video
pay-someone-to-take-my-online-class

Name: Jennifer Lucas
Status: Online ⬤
Classes Taken: 3878
Ratings: ⭐⭐⭐⭐⭐

Hire Me

STATUS

 

  • Locks the customer table – lock table customers write;
  • Turns off FK checks – set foreign_key_checks = 0;
  • Alters the table to add the auto_increment feature to the PK field
  • Turns FK checks back on – set foreign_key_checks = 1;
  • 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, the high-demand environment is an advanced topic, the 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 fieldset 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:

 

  • Construct a view on the customer’s table called CA_CUSTOMERS that consists of all data

 

    about customers that live in California.

  • Display the data using this view to verify that only customers that reside in California are visible.
  • 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.
  • 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.

  • 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.
  • Use the view to display the data sorted by highest to lowest days to ship
  • Use the view to display only the orders that took less than 10 days to ship.
  • 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 the artist.

  • 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.
  • 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?
  • Create a query USING THIS VIEW and the appropriate aggregate function to display artist_id and the total sales for each artist.
  • Now join 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.

  • 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.
  • 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?
  • Create a query USING THIS VIEW and the appropriate aggregate function to display customer_id and the total sales for each customer.
  • Now join 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.

Facebook
Twitter
Reddit
Pinterest
WhatsApp
Email
pdf-answers
Get Solved Answers
$2.99
Add to cart

GET QUICK PRICE ESTIMATE FOR THIS TASK

WHY CHOOSE US

  • Over 300 Experts to Work on Your Papers
  • 100% Privacy and Confidentiality
  • Amazing Discounts and Special Offers
  • Delivery within Every Deadline Level
  • Secure Payment Process
  • High-Quality Papers
  • 24/7 Live Customer Support
  • Free Revisions If Required
  • Fair & Cheap Prices
  • Rated 4.9/5 By Students

COMPLETED TASKS

SOCS 325 Week 8 Final Exam (Version 2)

July 13, 2020

SOCS 325 Week 8 Final Exam (Version 1)

July 13, 2020

SOCS 325 Week 8 Final Exam (Collection)

July 13, 2020

SOCS 325 Week 8 Collaborative Group Project Discussion

July 13, 2020

SOCS 325 Week 7 Bundle: Assignments + Group Project + Discussions

July 13, 2020

SOCS 325 Week 6 Bundle: Assignments + Discussions

July 13, 2020

SOCS 325 Week 5 Bundle: Assignments + Discussions

July 13, 2020

SOCS 325 Week 4 Midterm (Version 4)

July 13, 2020

Homework Provider

Facebook-f Twitter Pinterest Whatsapp Reddit Envelope

WE ACCEPT

PayPal
VISA Light
AMERICAN EXPRESS LIGHT
Bitcoin
  • Take My Online Class
  • Do My Case Study
  • Take My Online Exam
  • Online Class Help
  • Do My Homework
  • Take My Online Test
  • Original Homework Help
  • Premium Plagiarism Checker
  • Take My Midterm Exam
  • Research Paper Writing
  • Case Study Writing Help
  • Dissertation Writing Service
  • Online Coursework Help
  • Pay Someone To Write My Paper
  • Pay For Online Classes
  • Programming Classes Help
  • MyMathLab Answers
  • MyStatLab Answers
  • MyAccountingLab Answers
  • Need Help With Online Class
  • Cheap Assignment Help
  • Write My Online Essay
  • Online Student Problems
  • Math Homework Help

Contact Us

  • [email protected]
  • WhatsApp (781) 656-7669
  • twitter.com/hwp_helper
  • fb.com/homeworkprovidercom
  • Privacy Policy
  • Terms of Use
  • DMCA
  • Cookie Policy
  • Money Back Guarantee
  • Premium Plagiarism Checker
  • Contact Us

HomeworkProvider.com is not endorsed or affiliated by any university or college. The services provided are meant to assist the client by providing a guideline and the product provided is intended to be used for research or study purposes.

© 2013-2020 HomeworkProvider. All rights reserved.

ORDER THIS
WHATSAPP

Yay! You are just one step away to get rid of homework stress

  • How It Works
  • Testimonials
  • We are committed to ensuring that your information is secure with us
hire-an-expert-to-do-my-exam

Don't Waste Your Time Searching For Old Plagiarized Courses

Get Your Job Done By Our Anonymous Quality Experts

  • How It Works
  • Reviews
  • 24/7 Support
doc icon homework provider

CIS 155 Lab Help, Leslie​
Thank you for doing my CIS 115 lab. Homework Provider is the best.

Rated By Client
 5/5
doc icon homework provider

Dream Service, William
Thanks God, you came to my rescue. Thanks for your help.

Rated By Client
 5/5
doc icon homework provider

Mind blowing service, Eddie
I could have failed quite a few exams if you guys did not help me

Rated By Client
 5/5
doc icon homework provider

Got A grade in final exam, John
I still couldn’t believe I got an A grade in my exam. Highly vouched service.

Rated By Client
 5/5
doc icon homework provider

Linda, opted for private tutoring
Subscribed to get online class help for devry acct 212 week 1-8 class​​

Week 5 65%
doc icon homework provider

Acct Course Project, Ryan
Thanks a lot to my writer who followed all my requirements.

Rated By Client
 5/5
xls-icon

Genuine service, Steven
The research paper was much more than I expected! Thanks a lot.

Rated By Client
 4.5/5
doc icon homework provider

Karen, opted for private tutoring
Subscribed to get online class help for liberty BUSI 536 week 1-8 class.

Week 7 88%
doc icon homework provider

Marcus, opted for private tutoring
Subscribed to get online class help for ashford psy week 1-5 class.

Week 3 58%
doc icon homework provider

I got A grade in class, Amanda
with the help of homework provider I made it to pass my acct 505 class.

Rated By Client
 5/5