Shopping Cart
Your shopping cart is empty!
Categories
CIS 336 iLab7 Working with Views
$9.00
 

CHECK OUT

CIS 336 iLab7 Working with Views

This Tutorial was purchased 1 times & rated No rating by student like you.

 

This Tutorial contains following Attachments

  • CIS 336 N iLab 7 Working with Views.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’sOmnymbus 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.

Write a review

Order Id


Order Id will be kept Confidential
Your Name:


Your Review:
Rating:   A   B   C   D   F  

Enter the code in the box below:



Related Tutorials
$9.00

This Tutorial was purchased 4 times & rated B+ by student like you.

(TCO 6) Which of the following keywords is used to remove a row in a table? (TCO 6) When using the ALTER TABLE...DROP COLUMN command, which of the following is NOT correct? (TCO 6) Which of the following commands will add a new column named FIRSTORDERDATE to the CUSTOMERS table to store t..
$9.00

This Tutorial was purchased 3 times & rated No rating by student like you.

(TCO 8) If a Cartesian join is used to link table A which contains five rows to table B which contains eight rows, there will be _______ rows in the results. (TCO 8) Which of the following statements will produce the value 21.1? (TCO 8) Which of the following format elements will display ..
$8.99

This Tutorial was purchased 3 times & rated B+ by student like you.

(TCO 7) Which of the following types of columns will automatically right-align the data when it is displayed? (TCO 7) Which of the following SQL statements will display the profit generated by each book currently stored in the BOOKS table? (You do not need to see the table structure for this ..
$9.00

This Tutorial was purchased 2 times & rated No rating by student like you.

(TCO 2) Explain what a composite attribute is (TCO 1) Data constitutes the building blocks of (TCO 1) Successful database design is first and foremost based on _____________ requirements. (TCO 1) The lowest level in a database hierarchy is the ____________ or value as it is often refe..
$7.49

This Tutorial was purchased 3 times & rated B+ by student like you.

(TCO 3) Normalization works through a series of stages called normal forms. Typically _________ stages must be completed before a table can be considered normalized. (TCO 3) The conflicts between design efficiency, information requirements, and processing speed are often resolved through ..
$32.00

This Tutorial was purchased 13 times & rated A+ by student like you.

CIS 336 Final Exam 4 Sets of Answers ..
$11.00

This Tutorial was purchased 9 times & rated A+ by student like you.

1. (TCO 7) For every order, list the order number, order date, part number, part description, and item class for each part that makes up the order. 2. (TCO 8) Write a query that will display the customer number, last name, and first name for every customer I whose first name begins with the l..
$11.00

This Tutorial was purchased 6 times & rated B+ by student like you.

1. (TCO 2) Explain what physical independence means. (Points : 15) 2. (TCO 8) Sally wants to query the EMP table and determine how many of the employees live in each of the cities the company has factories in. She writes the following query but it does not work. What is wrong with the way the..
$11.00

This Tutorial was purchased 5 times & rated A+ by student like you.

1. (TCO 1) Most DBMS are referred to as _____________database management systems. (Points : 4) elemental linked hierarchical relational 2. (TCO 1) A relational DBMS provides protection of the _______________________ through security, control, and recovery facilities. (Points : 4) ..
$11.00

This Tutorial was purchased 4 times & rated B+ by student like you.

1. (TCO 1) Most DBMS are referred to as _____________database management systems. (Points : 4) elemental linked hierarchical relational 2. (TCO 1) Data constitutes the building blocks of _____________. (Points : 4) information processing applications programming 3. (TC..
$9.00

This Tutorial was purchased 2 times & rated A+ by student like you.

CIS336 Lab 1: Normal Forms and Entity Relationship Diagrams Introduction In this exercise, you will analyze a de-normalized data set presented in the form of a spreadsheet. You will next construct a series of dependency diagrams, transforming the evolving data model from First Normal Form (1NF), t..
$9.00

This Tutorial was purchased 2 times & rated No rating by student like you.

CIS336 Lab 2: The Expanded Entity Relationship Diagram LAB OVERVIEW Scenario and Summary This lab introduces the next step in creating a data model, the Entity Relationship Diagram (ERD). You will be given a business scenario for a University Medical Center, which is a small community hospital. T..
$10.00

This Tutorial was purchased 3 times & rated A+ by student like you.

CIS-336: Lab 3: Building the Physical LAB OVERVIEW Scenario and Summary The lab for this week addresses taking a logical database design (data model) and transforming it into a physical model (tables, constraints, and relationships). As part of the lab, you will need to download the zip file ti..
$9.00

This Tutorial was purchased 0 times & rated No rating by student like you.

CIS336: Lab 4: Introduction to Select, Insert, Update, and Delete Statements LAB OVERVIEW Lab 4 will introduce the various aspects of the SQL select statement and the methods of retrieving data from the database tables. This lab will also introduce the fundamentals of updating and deleting records..
$9.00

This Tutorial was purchased 2 times & rated A by student like you.

CIS336: Lab5: Joining Tables LAB OVERVIEW Scenario and Summary Lab 5 will introduce the concept of multi-table JOINS in order to work with data in two or more related tables simultaneously. This lab may be completed using either DeVry’sOmnymbus EDUPE-APP lab environment, or a local copy of the My..
$9.00

This Tutorial was purchased 3 times & rated B+ by student like you.

CIS336: Lab 6: Group Functions and Subqueries LAB OVERVIEW Scenario and Summary Lab 6 will introduce the concept of group functions and subqueries to meet more complex report requirements. This lab may be completed using either DeVry’sOmnymbus EDUPE-APP lab environment, or a local copy of the MyS..
$25.00

This Tutorial was purchased 4 times & rated A+ by student like you.

This project is designed to touch all aspects of the fundamental concepts of database design and logical data modeling covered during the class. Each student will be responsible for designing, developing, and demonstrating the functionality of a database created based on a set of business specificat..
$7.00

This Tutorial was purchased 2 times & rated A+ by student like you.

CIS 336 Week 6 Quiz Question 1. (TCO 6) Which of the following keywords can be used to change the size, datatype, or default value of an existing column? Question 2. (TCO 9) If the _____ clause of the UPDATE command is omitted, then all the rows in the specified table will be changed. ..
$7.00

This Tutorial was purchased 1 times & rated No rating by student like you.

CIS 336 Week 5 Quiz Question 1. (TCO 8) The ____ set operator in SQL combines rows from two queries and returns only the values that are common between the two sets. Question 2. (TCO 8) Which SQL function converts a value to a particular string format? Question 3. (TCO 8) Based upo..
$7.00

This Tutorial was purchased 0 times & rated No rating by student like you.

CIS 336 Week 4 Quiz Question 1. (TCO 7) Which of the following types of columns will automatically right-align the data when it is displayed? Question 2. (TCO 7) Which of the following symbols can be used to denote that any and all characters following the sign are eligible when use..
$7.00

This Tutorial was purchased 3 times & rated A+ by student like you.

CIS 336 Week 3 Quiz Question 1. (TCO 3) When designing a database, you should _____ Question 2. (TCO 3) A table that is in 1NF and includes no partial dependencies only is said to be in _____ Question 3. (TCO 3) (TCO 3) A table is not in 1NF if _______________. Question 4. (TCO 9) ..
$7.00

This Tutorial was purchased 5 times & rated B+ by student like you.

CIS 336 Week 2 Quiz Question 1. (TCO 1) Raw facts, such as a telephone number, a birth date, and a customer name, are also referred to as _____ Question 2. (TCO 1) The DBMS allows you to extrapolate information from your data by using a _____ Question 3. (TCO 1) A database row..
$15.00

This Tutorial was purchased 4 times & rated A by student like you.

CIS 336 Final Exam Question 1. 1. (TCO 1) A DBMS performs several important functions that guarantee the integrity and consistency of the data in the database. Which of the following is NOT one of those functions? Question 2. 2. (TCO 1) A relational DBMS provides protection of the _____ t..