MIS 610 Full Course Assignments GCU
MIS 610 Topic 1 Query Building
The purpose of this assignment is to demonstrate query building skills.
For this assignment, you will construct queries using both static and dynamic word problems for each of five scenarios. You will use Microsoft SQL Server 2016 Developer Edition and the content from the AdventureWorks 2016 and Scripts for SQL Server 2016 CTP3 to complete the assignment.
Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.
Create a Word document that includes the SQL queries used to explore the database tables, and answer the following questions.
Query Scenarios
Problem One
In your new job, you realize you will be calling up the “HumanResources.Employee” table on a frequent basis to answer questions from your manager. Using what you have learned thus far about stored procedures, create an executable “Employees” dataset that will allow you to quickly reference all records in that dataset. However, note that a stored procedure cannot be used in a SELECT statement after it has been created; it simply allows for a quick command to call up a preset data query you have made.
Problem Two
While a stored procedure is an effective method to review a static dataset, you realize the requirements for questions posed to you change frequently. Your manager often will request information specific to a given job title. You realize you need to create a function that allows a variable to be changed in your SELECT statement each time you run it.
Note: To develop the answer to this problem, set the variable to “Design Engineer” and call the Function @JobTitle.
Problem Three
Your manager wants to build an “Employee Time Off” calendar for the year for easy reference and has asked that you compile a list of vacation hours for each employee under his supervision. This task relates to the one you completed in Problem Two of this assignment, but will require that you restructure the code you developed from Problem Two to build a more complex query in or order to complete the task successfully. As with Problem Two, your manager will request this information often, and on an ongoing basis throughout the year. You realize you need to create a function that allows a variable to be changed in your query statement each time you run it so that you do not need to write new code for every request.
The fields you need to bring in are “First and Last Name” and “Sick and Vacation Hours.”
Note: To develop the answer to this problem, create a join from the person table First and Last Name.
Problem Four
Your manager would like you to classify employee work areas in the building using a color coding system that he developed. Since no such system exists in the company database, he has asked you to upload his personal Excel worksheet into the database as a new table. Using SQL Server Import and Export Wizard, load the “Roster” tab from the Excel file titled “Color Coding.” Write a SELECT statement once loaded to confirm that it loaded correctly.
Problem Five
Now that your color coding system is in place, your manager wants to know which employees sit in the “Brown” section, as he will be relocating their desks in the future when the department is expanded. To provide him with this information, use the color coding system you developed for Problem Four, making sure to include the “Color Code,” “Job Title,” and concatenate “Last Name” “First Name” separated by a comma, calling the field “Full Name.”
General Requirements
Compile the Excel data file and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.
APA style is not required, but solid academic writing is expected.
This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.
You are not required to submit this assignment to LopesWrite.
MIS 610 Topic 2 CASE Query Errors
The purpose of this assignment is to evaluate and resolve query errors.
For this assignment, you will group data and resolve query errors via solutions appropriate to each. You will use Microsoft SQL Server 2016 Developer Edition and the content from the AdventureWorks 2016 and Scripts for SQL Server 2016 CTP3 to complete the assignment.
Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.
Create a Word document that includes the SQL queries used to explore the database tables, and answer the following questions.
Query Scenarios
Problem One
Your manager needs you to compile a list of all the products the company sells as well as the time required to manufacture them in order to provide this information to the sales department for the presentation of timelines in an upcoming company-wide sales meeting. Your manager specifically needs to know which products take (1) less than 4 days to manufacture, or (2) more than 4 days to manufacture. Looking at the “Production.Product” and using CASE logic, identify each product with a tag of “Under 4 days” or “More than 4 days” and title the field “Timeframe.”
Problem Two
Using the query you created from Problem One as a basis, your manager wants to know the numbers of products that fall into each of the two categories.
Note: This process requires two separate SUM statements using CASE logic through the “Manufacturing time.”
Problem Three
Also related to the upcoming sales meeting, the sales manager is building a report for which a list of states using a custom market name is required. Using the “Person.StateProvince” table, use CASE logic to turn “TerritoryID” into a named market for each of the following: Western Market, North Eastern Market, Mid-Western Market, South Western Market, and South Eastern Market. Make sure to include the “TerritoryID” and to order your results by this field.
Problem Four
The sales manager has also identified a need to know “Year to Date” sales by the new market segments using the same descriptions you developed in Problem Three.
Problem Five
Now that you have provided the sales manager with year-to-date sales by market segment in Problem Four, the sales manager would like you to provide year-to-date sales by state. This is not realistically possible through the existing company database setup. Explain why and what would be needed for the database to accomplish this dataset in the future.
General Requirements
Compile the Excel data file and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.
APA style is not required, but solid academic writing is expected.
This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.
You are not required to submit this assignment to LopesWrite.
MIS 610 Topic 3 UNIONS and Subqueries
The purpose of this assignment is to practice UNIONS and to use subqueries to analyze data in a record set.
For this assignment, you will analyze data to find similarities and UNION all data via a method that keeps the data uniform. You will use Microsoft SQL Server 2016 Developer Edition and the content from the AdventureWorks 2016 and Scripts for SQL Server 2016 CTP3 to complete the assignment.
Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.
Create a Word document that includes the SQL queries used to explore the database tables, and answer the following questions.
Query Scenarios
Problem One
As part of a yearly fundraising effort for the community, your manager has given you an Excel worksheet titled “Donations.” It includes all employees’ pledged donations for the year. The sheet consists of three tabs that correspond to the 3 months when employees were asked to submit pledges. Load this file into the AdventureWorks database through the Import and Export Wizard. Once loaded, use a UNION ALL statement to combine the three data sets into a temporary table called “#TotalDonations.”
Problem Two
Redoing the UNION ALL statement you created in Problem One, create a temporary table looking exclusively at “SUM of Donations by Month.” Title each of the three fields “Donations and Month” with the temporary table titled “#DonationsbyMonth.” When complete, provide the total of the final amount of donations raised.
Problem Three
Your manager e-mails you about the final results you provided in Problem Three, and believes you must have made a mistake when calculating the total donations. The amount is $100.00 less than the total amount calculated by the manager. The manager thinks it is possible that one employee might have accidently pledged $100.00 twice. Explain how this event might occur with a UNION ALL. Review the UNION ALL, and if there is an error, rewrite the first query with a UNION statement. Discuss whether the manager’s assumption was correct and explain why.
Problem Four
You receive an e-mail request from Human Resources requesting information about the last time a pay rate was changed for an employee. The employee’s “NationalIDNumber” is 486228782. Develop a subquery instead of an INNER JOIN to provide the answer to this request.
Note: A subquery is another method of writing a JOIN. It can be efficient when used at the correct times.
General Requirements
Compile the Excel data file and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.
APA style is not required, but solid academic writing is expected.
This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.
You are not required to submit this assignment to LopesWrite.
MIS 610 Topic 4 Aggregate Functions and NULL Values
The purpose of this assignment is to examine the use of aggregate functions, analyze queries that contain NULL values, and use CASE logic to resolve the data and arrive at a solution.
For this assignment, you will perform a set of exercises in which you will analyze data and develop corresponding aggregate functions to obtain the required information. You will use Microsoft SQL Server 2016 Developer Edition and the content from the AdventureWorks 2016 and Scripts for SQL Server 2016 CTP3 to complete the assignment.
Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.
Create a Word document that includes the SQL queries used to explore the database tables, and answer the following questions.
Query Scenarios
Problem One
The sales manager asks you to provide the average “Pretax Sales” amount throughout the years. Using the “Sales.SalesOrderHeader” table, what answer will you provide to the sales manager?
Problem Two
As part of an internal competition, the CEO requests that you provide the name of the employee who made the highest total single sale in 2014. This data needs to include the filter “Tax & Freight” using “OrderDate” for the year. The CEO has also requested that you provide the winner’s e-mail address so he can send a notification of congratulations.
Problem Three
Jo Berry’s manager indicated that Jo was taking paid time off this month. He wants to ensure the system reflects this information. Import the “Hours” spreadsheet file into the AdventureWorks database. Add Jo Berry’s hours together for the month, and title the field “Monthly Hours.” Use the knowledge you have learned about NULL values to provide the solution to the manager.
Problem Four
The human resources manager wants to classify employees who were absent during the entire month as “Inactive.” Using a CASE statement, write a query that classifies employees as “Active” or “Inactive” in a column titled “Status” with “Full Name” (First and Last) also listed.
Problem Five
Using the same parameters as Problem Four, rewrite the query to provide only the list of “Inactive” employees.
General Requirements
Compile the Excel data file and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.
APA style is not required, but solid academic writing is expected.
This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.
You are not required to submit this assignment to LopesWrite.
MIS 610 Topic 5 RDBMS and OODBMS Short Answer Questions
The purpose of this assignment is to identify similarities and differences between an RDBMS and an OODBMS.
Using the textbook, Topic Materials, and your own research, write a 100-250 word response to each question below. Cite the sources used for answering each question, and include real-world examples in the answer to each question.
- Define a tuple. How and where does a tuple fit into relational database theory? Why is this an important concept to understand?
- How does an object-oriented database management system (OODMS) compare to a relational database management system (RDBMS)? What are the key features of an OODBMS, and how can a developer utilize them?
- What is the difference between a Relation in an RDBMS and a Class in an OODBMS? Are they interchangeable, If so, explain how.
- Contrast an object-relational database management system (ORDBMS) with an RDBMS? Describe the unique features and provide an example of how an OODBMS might be preferable to an RDBMS.
- Contrast the attributes and functionality of an OODBMS versus an ORDBMS. Explain why RDBMS platforms are used more frequently in business than either of these object-oriented platforms.
- From an entity relationship diagram (ERD) standpoint, would there be a significant difference between using an ORDMBS versus an RDBMS? Explain why or why not, and discuss specific instances in which differences are the deciding factor.
Prepare this assignment according to the guidelines found in the APA Style Guide, located in the Student Success Center. An abstract is not required.
This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.
You are required to submit this assignment to LopesWrite. Please refer to the directions in the Student Success Center.
MIS 610 Topic 6 Object Relational Design Short Answer Questions
The purpose of this assignment is to explain and discuss object-relational design and implementation techniques.
Using the textbook, Topic Materials, and your own research, write a 100-250 word response to each question below. Cite the sources used for answering each question.
- Describe simple and complex data types. Provide examples of each and describe their typical uses in a business environment.
- Explain what an impedance mismatch is and why is this important in analytics. Discuss measures a business can institute to address this problem. Describe barriers that might exist for a business to institute such measures.
- Explain an array and its pros and cons. Explain why this structure may be preferred for storing/referencing data.
Prepare this assignment according to the guidelines found in the APA Style Guide, located in the Student Success Center. An abstract is not required.
This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.
You are required to submit this assignment to LopesWrite. Please refer to the directions in the Student Success Center.
MIS 610 Topic 7 Object Based Database Design Short Answer Questions
The purpose of this assignment is to compare and contrast relational and object-oriented databases.
Using the textbook, Topic Materials, and your own research, write a 100-250 word response to each question below. Cite the sources used for answering each question.
- Object query language (OQL) was created by the object database management group (ODMG) and is based on structured query language (SQL). The languages are similar in many ways, but they feature some slight and some not so slight differences. Explain why ODMG created and developed OQL.
- Discuss why developing standards is important for a database management system. Describe issues OODBMS systems face and explain how those issues have allowed RDBMS systems to thrive.
- Explain the “4th Generation Standard.” Discuss how this standard will change the object-based environment. Describe features that will help or hinder its success.
Prepare this assignment according to the guidelines found in the APA Style Guide, located in the Student Success Center. An abstract is not required.
This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.
You are required to submit this assignment to LopesWrite. Please refer to the directions in the Student Success Center.
MIS 610 Topic 8 Benchmark Database Query and Reporting Project Worked Time by Department
The purpose of this assignment is to organize, query, and report data using a database.
The final project consist of two parts and requires you to utilize the skills and knowledge you have learned throughout this course to create and implement a complex set of queries and present the data you have assembled into a PowerPoint presentation. It is essential for you to study the database structure and plan ahead throughout the course to ensure you will have the time to prepare and submit this assignment successfully.
Part One
For this assignment, you will use Microsoft SQL Server 2016 Developer Edition and the content from the AdventureWorks 2016 and Scripts for SQL Server 2016 CTP3 to complete the assignment.
Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.
Create a Word document that includes the SQL queries used to explore the database tables, and address the problem.
Problem: The new scheduling manager has asked for a summary of hours worked each week by department. Accounting for NULL values, use the imported “Hours” sheet from the Topic 4 assignment again to SUM by department title for all employees on the table.
Part Two
Using the dataset you have developed, select one of the following groups to present your findings to at the upcoming company meeting: Senior Management, Engineering, Research and Development, or Marketing. In your presentation, model your data into a hypothetical problem that identifies how this information will impact the operations of the specific target audience you have selected.
Create a 5-7 slide PowerPoint presentation to share your results with your selected group at the upcoming company meeting. Each slide should include speaker notes written in complete sentences.
General Requirements
Compile the Word, Excel, and PowerPoint documents into a .zip file and submit to your instructor.
While APA style is not required for the body of this assignment, solid academic writing is expected, and documentation of sources should be presented using APA formatting guidelines, which can be found in the APA Style Guide, located in the Student Success Center.
This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.
You are required to submit this assignment to LopesWrite. Please refer to the direction in the Student Success Center.
Reviews
There are no reviews yet.