MIS 610 Full Course GCU

Reflect on the content of the Topic Materials and discuss the following.

1. Explain how you used the knowledge you gained from the Topic Materials to complete the topic assignment.
2. Discuss the workplace relevant “takeaways” you gained from this topic and provide an example of how you could apply this knowledge to a workplace project or scenario.

Explain how a UNION function operates and how the resulting data is intended to be used. Discuss the weaknesses that exist, and strategies for making the process stronger to deliver a better end result. Justify your ideas with support from the Topic Materials or relevant industry examples.

Determinism of a function is one of several properties of user-defined functions that determine the ability of SQL to index results. When and for what purposes would you choose to use deterministic versus nondeterministic functions to arrive at your desired results? Justify your ideas with support from the Topic Materials or relevant industry examples.

Explain why it may be more efficient for a company to utilize an RDBMS based on workforce skill and availability. Discuss how a company might be able reduce costs through using an OODMS instead. Identify current or proposed/future developments that might influence a business to continue “as is” or leap to a different system. Justify your ideas with support from the Topic Materials or relevant industry examples.

Describe four key features of an object database management group (ODMG) and what makes this technology so appealing for so many different types of users. From those you previously identified, select the key feature you believe to be the most essential and versatile, and provide your predictions of how this specific feature will be or could be improved to make it even more useful in the future. Justify your ideas with support from the Topic Materials or relevant industry examples.

You have employed several commonly used database automation tools in your work throughout the course. Discuss which of these tools has proven to be the most useful overall. Provide your reflective analysis with support from your work, an industry example, or the Topic Materials.

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.

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.

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.