[SOLVED] example of company performance analysis. select
[SOLVED] example of company performance analysis. select two companies and compare their financial structures including WACC, book value, and market value.
United States steel is a steel production company located in Pittsburgh, Pennsylvania. The company was founded in 1901. The company area of operation includes the USA and central Europe. The company generated revenue of 10 billion in 2016 financial year. Facebook, on the other hand, is a social media platform founded in 2004. The company is based in Menlo Park California. The company generated a revenue of 27 billion 2016 financial year. These companies operate in different industries. Therefore, the underlying conditions that influence the earning of companies may be totally different. Besides, their assets and expenses are totally different. A company like Facebook invest most on research and development while a company like US steel invest heavily on plant and equipment.
United States steel (X) have 176,000,000 outstanding shares while Facebook have 2,930,000,000 outstanding shares. The price per share for United States steel (X) is 35.48 USD and for Facebook (FB) is 161.89 USD. Therefore, the value of United States Steel (X) is 6,244,480,000 USD and for Facebook (FB) is 474,337,700,000 USD (Yahoo, 2019). The book value of the company is calculated by taking the total asset value less total liability. Therefore the book value of each company is as calculated below
[SOLVED] UMGC Data 620 Assignment 7.2Your name:
[SOLVED] UMGC Data 620 Assignment 7.2
Your name:
Date:
Each student runs the Python development environment Jupyter Notebook! Using the browser from the computer, prints “hello world” and writes a Celsius to Fahrenheit converter.
Turn in separate document(s) with the following items. Please label each with the TURN IN # statement so we can easily follow it.
Deliverable
Description
Points
TURN IN #1
Turn in a screen shot of your successful editor Jupyter Notebook.
1
TURN IN #2
Turn in screen shot(s) showing “hello world” program execution with your name as the input string.
1
(You can put TURN IN #1 and TURN IN #2 in the same Word document.)
TURN IN #3
Your Python code to perform a Celsius to Fahrenheit conversion. Name your python file “XXXX-temp.ipynb” where XXXX is your name. Your deliverable here is just the Python notebook; you don’t need to include screen shots. (Note there is a Rubric for Python programs (located also in this week’s materials) for how we will grade this.)
8
TOTAL
10
The following walkthrough exercises from “Python for Everybody – Exploring Data Using Python 3” by Charles R. Severance are included here:
Chapter 1: Exercises 5, 7, 9
Chapter 2: Exercises 2, 3, 4
You are encouraged to walk through these exercises. To do this, you should type the commands into your computer and make sure they give the same results as here.
Browsing the Python Jupyter IDLE (Integrated Development Environment), writing and running a program:
Try Jupyter out without installing anything.
Step 1:
Go to https://jupyter.org/try
Sometimes the screen will look slightly different, like this below. In that case, you need to “Try Classic Notebook”
You will get binder starting Python IDE (give it some seconds), then you will see this screen below:
Step 2:
Figure 1 into My Content area.
Figure 1: Nashville Housing Data location
o Navigate to My content and make sure that you see a copy of the Nashville housing data.
o Select Create data module from action menu in Figure 2.
Figure 2: Create data module
o The new data module will open. Click on an arrow next to save icon in Figure 3 and select save as to save new data module into my content.
Figure 3: Save Data module
o Click on Nashville housing data csv in the left panel in Figure 4 to preview data.
Figure 4: Preview data
b) Handle Missing data
Cognos Analytics represents missing data as Null
Apply the approaches in the assigned readings to handle missing data, including filtering rows, removing columns, and replacing the missing value with the average. Discuss the approaches you used in your paper.
c) Handle irrelevant variables – Which variables in the data are irrelevant? Explain why they are irrelevant and how you handle them.
Make sure to save the data module before moving on to the next part of the assignment.
4. Explore the dataset:
Return to the Welcome page and expand My Content
Select Create exploration from the action menu of the Nashville housing data module in Figure 5.
Figure 5: Create Exploration
a) Are there outliers? If so, what fields have outliers and what do you recommend as solutions?
b) Pose an initial set of questions to use for data exploration. Provide any insights gained from using Cognos Analytics with this dataset
a) Develop new specific questions which provide additional insights into and answer specific questions from the dataset. Discuss how these insights could be useful. Discuss how you would improve the relevancy.
b) Develop and explain at least five different visualizations. Experiment with the available options and summarize the results. Provide insights to what the visualizations show.
c) Utilize features (e.g., filters, comparisons) with the visualizations to uncover and explain interesting aspects of the data set.
d) Create and explain at least one insightful calculation. Discuss why this would be useful.
Submission
Each student will submit a single document conforming to the guidelines and standards outlined below.
Document format:
§ limited to 8 pages (excluding title page, references, and appendix),
§ Double-spaced, 12 point Times New Roman font, 1” margins, Bottom-right page numbering.
Note: Submitted report must be either in MS Word or PDF format and titled: “Assignment2_LastName”.
Only one document will be allowed to be submitted.
Content (note that the document must have clearly marked sections for the items listed below)
1) Title page (1 page limit): course number and term, assignment number and project title, student name and contact information, instructor’s name. Format it so it looks pleasant and presentable. Follow formatting guidelines above.
2) Introduction. Provide a brief outline of the dataset you are using for this assignment. Briefly describe the content of the data. Include a screenshot of the data (not all, but partial as far as all relevant variables are visible).
3) Data preparation – handling missing data, removing irrelevant variables
4) Data exploration process. Explain and discuss what data exploration you performed (e.g., questions generated about the data set content). Include any specific ideas or suggestions as to how this could be used in your organization.
5) Visualizations created. Explain the visualizations created. Include the value-added aspects of the visualizations. Include creative aspects for increasing potential for higher assignment grade.
6) Calculation which adds insights or value to the data set. Include and explain the value of the calculation, i.e., insights provided by the calculation.
7) References (1 page limit): List all references in APA format used in preparing this report. It is strongly recommended to use outside knowledge in setting-up the analysis or discussing the results where possible.
8) Appendix (4 page limit):
a) Appendix A: Include any appropriate workbooks and/or screenshots (figures, tables, diagrams) used in this assignment. Make sure all tables, figures, or diagrams are properly numbered and titled. For example, “Table 1. Model Results”. Make sure all tables or figures or diagrams are easily readable and visually presentable.
General guidance
§ Assignments that: 1) adequately address all required tasks; 2) are submitted on time; 3) are properly formatted (APA format for references, no typos or misspelled words, no grammar errors, cover page, etc.) will receive a grade of B (80-89, depending on content).
§ In order to increase (but not guarantee) your chances of receiving a higher grade, you need to show clear evidence of critical thinking. Critical thinking can take many forms, depending on the type of assignment. In some instances, showing greater depth (e.g., such as creating more models, looking at more than one insightful fact or relationships, and comparing them on key criteria) is one method for providing evidence of critical thinking. In other cases, it might include providing more explanation to include the pros and cons of the approach used or the arguments in favor and against the proposal as well as some criteria for choosing among the alternatives. Still another example would be providing significant insights as to how the assignment outcome would benefit (or would meet resistance) in your organization and what steps might be employed to facilitate acceptance. Certainly, this is not a complete list, but gives some examples of critical thinking aspects.
Part 1 - Account access verificationscreenshot of
Part 1 - Account access verification
screenshot of your user information
Part 2 – Upload Data
screenshot of insurance data in My Content
Part 3 - Data Exploration
Variable Properties
How many variables are in the insurance data?
7
What is the datatype of Age?
Integer
What is the datatype of Charges?
decimal
What is the usage type of an Age column?
measure
What aggregation types are available for the Smoker column?
total
Explore the Relationships
What is the relationship strength between smoker and charges?
75%
What is the relationship strength between age and charges?
64%
What does the relative width of connecting line represent?
Strength of relationship
Which variables do not have a direct relationship with charges?
BMI and region
Spiral Diagram
Screenshot of the spiral diagram and the list of drivers
What driver has the highest predictive strength? What is the predictive strength?
BMI, smoker, and age is predictor of charges with predictive strength of 83%
What driver information do you see in the tooltip?
Predictive strength and composition of drivers
Which variables in insurance data are not on the spiral diagram for charges?
Sex, children, region and insuranceclaim
Summary Visualization and Using Filters
Use the summary visualization to return the average age. Take a screenshot of the visualization and paste it into the answer sheet.
Edit the summary visualization to return the minimum age. Take a screenshot of the visualization and paste it into the answer sheet.
Edit the summary visualization to show an average age of an individual with 5 children. Take a screenshot of the visualization and paste it into the answer sheet.
Edit the summary visualization to show an average age of an individual with 5 children who is a smoker (smoker=1). Take a screenshot of the visualization and paste it into the answer sheet.
Comparison Visualization of Your Choice
Visualization Screenshot
Explain the visualization purpose and summarize the insights.
1. What are the hurdles keeping your
1. What are the hurdles keeping your organization from improving its AQ? What steps would you recommend to help improve the AQ?
1. What is your organization's Analytical Quotient
1. What is your organization's Analytical Quotient (AQ)?
1. Where do you see the biggest
1. Where do you see the biggest hurdles in adopting a more 'evidence-based' approach?
1. Which approach (Davenport or CRISP-DM) would
1. Which approach (Davenport or CRISP-DM) would work better in your organization?
1. Which approach (Davenport or CRISP-DM) would work
1. Which approach (Davenport or CRISP-DM) would work better in your organization?
WK 3 – DATA 610 6/3/20 – DISCUSSION Questions/answers
1. Which methodology (Davenport or CRISP-DM) do you prefer, and why?
1. Generate an "analytics requirements
1. Generate an "analytics requirements document"
1. Complete the model (flesh out a
1. Complete the model (flesh out a full model of the decision to include decomposing the decision, refining the needed information sources for the analysis, refining authorities)
1. Specify the decision requirements (input requirements,
1. Specify the decision requirements (input requirements, analytic requirements, other knowledge about the decision, related decisions, etc.)
1. Describe the decision in full (include
1. Describe the decision in full (include business context, organizational context, and application context)
1. Identify the decision to be made
1. Identify the decision to be made (e.g., reduce churn, identify fraud, improve product mix, etc.)
1. How Good is your problem-solving? Please
1. How Good is your problem-solving? Please post the results of your Problem-Solving inventory checklist from Mindtools here. Posting of results is helpful to see how we compare as problem solvers.
1. What assistance have you seen from
1. What assistance have you seen from using analytical approaches to improve decision making?
1. Have you seen instances of poor
1. Have you seen instances of poor decision making in your organization?
1. Where does culture fit in the
1. Where does culture fit in the decision trap?
1. What is the impact of 'poor'
1. What is the impact of 'poor' or 'incomplete' decision making? What measures of effectiveness are used in your organization?
1. What are the 'decision traps' we
1. What are the 'decision traps' we all fall into?
Book Report Assignment This assignment requires that students
Book Report Assignment
This assignment requires that students read the assigned book – The Critical Chain – and submit a 5-page book report online on Canvas as a MS Word document after reading and understanding the above text. The guidelines on how to write the book report are provided below:
1. For this class, the book report must be organized around 3 major elements: Discussion, Application, and Evaluation (DAE approach).
2. Write 1-1½ pages discussing the book. Do not write a summary chapter by chapter or make lists. Write in narrative form. Make sure that your write up includes answers to these questions:
· What is the book about (briefly about 1 paragraph)?
· Describe the major themes (a couple of central ideas or gems) in the book from your perspective. Key word here is “major” themes, not just peripheral ideas such as role of communication or importance of team work etc. Remember this is operations management (OM) class and your gems should revolve around concepts related to OM.
· Give rationale why you think the above identified gems/themes are important from your perspective. (use critical thinking to add some rigor to your rationale)
3. Write 2-2½ pages on the application of the major ideas/themes/gems as identified in the discussion section above to your work place (any work place – past or present? In your own words, what is the meaning or significance of these ideas as they apply to your current or any work-setting? How does the author suggest that they be applied?
4. Finally, evaluate the book (½ to 1 page) along the following lines:
· How useful are the ideas?
· How practical are the ideas?
· How well organized and written is the book?
· Would you recommend it?
The book report must be 5 full pages, double-spaced, number 12 font with no more than one inch margins. Your write-up should make use of headings/sub-headings as needed. Rubric for grading book report is given below:
Rubric for Grading Book Report
A
A-/B+
B/B-
Exceeds Expectations
Meets Expectations
Basic Requirements
Instructions:Put your SQL in the "Problem 6"
Instructions:
Put your SQL in the "Problem 6" location in the SQL template.
Put your row count in the green box in cell D5.
Start with the same problem statement as in Problem 5, except this time generate a summary report. For each course name, list the count of the students who will need to be contacted for each format. Sort your report so it’s alphabetically sorted on Course Name, then by format (online or in person). A sample output is below (your data may vary.)
Paste a screen shot of successful execution below. Be sure your screenshots show all the SQL you use; you are welcome to include several screen shots if necessary.
Instructions:Put your SQL in the "Problem 5"
Instructions:
Put your SQL in the "Problem 5" location in the SQL template.
Put your row count in the green box in cell D5.
Professor Victoria Emmerline is set to retire immediately before spring semester. You want to generate a list of all students who are currently enrolled in any class she is teaching this spring, so you can reach out to them and let them know there will be a different professor. Assume each class she teaches could be assigned to a different professor – i.e. if Professor Emmerline was set to teach Calculus I and Calculus II, it’s possible Calculus I will be assigned to Professor X and Calculus II will be assigned to Professor Y.
Generate SQL code to make a report which will do the following
· Generate a concatenated class name/number string, such as “FIN 200” from something in the Finance Department where the course number is 200
· Print that class name/number string in the first column of your report
· Print the class description (such as “Introduction to Finance”)
· List the student’s first name and then last name
· List the offering term and the format (online or in person)
· List the professor’s first name and last name
· Sort your output ascending by the class name/number string, so “FIN 200”, “FIN 120” and “ART 100” would be sorted in the following order: “ART 100”, “FIN 120”, “FIN 200.”
· Within the same class, further sort your output so it’s alphabetical by student last name, student first name
Instructions:Put your SQL in the "Problem 4"
Instructions:
Put your SQL in the "Problem 4" location in the SQL template.
Put your row count in the green box in cell D5.
You want to see how your various students are doing, GPA-wise. Create a report which will give the student’s last name, student’s first name (should be concatenated into a single field as in the example), the student’s major, and the student’s average GPA.
Include only students who have actually taken at least one class (if they took it and earned 0 GPA points indicating they failed it, that record should be included.) Do not include a student who has not taken any classes.
The average GPA is the average of the GPA Points column in the enrollment table; display this to four or more decimal places. Sort alphabetically by “Last, First” name combination.
Paste a screen shot of successful execution below. Be sure your screenshots show all the SQL you use; you are welcome to include several screen shots if necessary.
For example, if your input data is like this: (note the in the last row – indicates student Tyrone Brown is new and has not taken any classes yet, has only declared a major.)
Instructions:Put your SQL in the "Problem 3"
Instructions:
Put your SQL in the "Problem 3" location in the SQL template.
Put your row count in the green box in cell D5.
Some faculty are grumbling that certain professors have an easy teaching load, defined as only a few courses with low capacity, while others have a heavy load of many courses with high seat counts. Run a query to help answer this question.
Your output should contain the faculty ID, faculty name (first and last – it’s fine to have this as two separate fields, also fine to concatenate together), the largest capacity class the faculty member is assigned to teach, the maximum possible total student load (sum of all the course capacities), and the number of classes the faculty member is teaching.
Include all faculty members, even if they are not assigned to teach any courses. Include courses only if they are taught by a faculty member. Sort your output ascending by the number of courses taught, with the professors with the fewest courses at the top.
For example, if you had the following input data (the blank row at the bottom means Professor Poldark did not teach anything):
Instructions:Put your SQL in the "Problem 2"
Instructions:
Put your SQL in the "Problem 2" location in the SQL template.
Put your row count in the green box in cell D5.
Write SQL which will give us the course id number (idCourse field), course description, the number of units, the offering term, whether it’s online or in person, and the capacity. Include only those courses which are more than 3 units and have a capacity of less than 40 students. We only want to list courses which have actually been offered at least once. Sort it ascending by idCourse.
Paste a screen shot of successful execution below. Be sure your screenshots show all the SQL you use; you are welcome to include several screen shots if necessary.
Instructions:Put your SQL in the "Problem 1"
Instructions:
Put your SQL in the "Problem 1" location in the SQL template.
Put your row count in the green box in cell D5.
Create SQL code to display all fields from the students table. Include only students whose major is Finance (‘FIN’) and who have an in-state residence. Sort it alphabetically by student first name.
Paste a screen shot of successful execution below. Be sure your screenshots show all the SQL you use; you are welcome to include several screen shots if necessary.
1. “XXX_Civiccenter_export.sql” where XXX are your initials.
1. “XXX_Civiccenter_export.sql” where XXX are your initials. This is an .csv file export of your “XXX_Civiccenter_query.sql” execution results. You can export this using the “export” icon in the Result Grid window in MySQL Workbench. (Note the data displayed below may or may not correspond to the data in this assignment.)
1. “XXX_Civiccenter_query.sql” where XXX are your initials.
1. “XXX_Civiccenter_query.sql” where XXX are your initials. This is an SQL script which will run a query on your database and show the results. Write SQL statement(s) which will produce an output table with the following fields in it, sorted with the most expensive tickets at the top, and then alphabetically by customer name:
Name
Opera
Cost
Date
Backstage Pass?
1. “XXX_Civiccenter_insert.sql” where XXX are your initials.
1. “XXX_Civiccenter_insert.sql” where XXX are your initials. This is an SQL script which will insert the problem data into your database.
1. “XXX_Civiccenter_create.sql” where XXX are your initials.
1. “XXX_Civiccenter_create.sql” where XXX are your initials. This is an SQL script which will drop any pre-existing Civic Center schema and create all the tables, keys, and other structures you will need. If we run this script, it should remove any existing civiccenter schema and create yours.
Assignment 3.1 This assignment is worth 70 points
Assignment 3.1
This assignment is worth 70 points and the full rubric is on the bottom of the page.
It’s time to go to the opera! You are going to use MySQL Workbench’s EER tool to create an Entity-Relationship diagram for the Civic Center Opera House. You will then engineer that to produce MySQL script(s) which will create and load the database schema with the information below. Finally, you will query your database to prove it is working.
Here is the relevant information.
· Susie attended Madame Butterfly and spent $25 on the ticket for the 1/11 performance, and then went to see La Traviata on 10/3 for $100. She also went to see Carmen for $30 on 10/10.
· Lee attended Madame Butterfly and spent $25 on the ticket for the 10/21 performance.
· Jackson attended Carmen and spent $30 on the ticket for the 5/12 performance. He also went to see La Nozze di Figaro on 1/20, and paid $100 for the ticket.
· Bobby attended Carmen and spent $30 on the ticket for the 1/26 performance, and then went to see La Boheme on 8/14 for $50.
· Margaret attended La Nozze di Figaro and spent $100 on the ticket for the 2/22 performance, and then went (with Jackson) to see Carmen on 5/12 for $30.
· Ulysses attended Rigoletto on 10/21 and spent $35 on the ticket.
· Each ticket has an option to have a “Backstage Pass” option. There is no additional cost associated with this, just a marking on the ticket that the backstage pass was selected.
· Backstage passes were selected by Margaret at all of her operas, and by Jackson when he went with Margaret to Carmen on 5/12. No other tickets chose a backstage pass.
Additional assumptions you should make:
· Assume all tickets for the same opera cost the same. For example, all tickets for Madame Butterfly are $25.
· All operas occurred last year. For example, if it is 2020 now, and the date says “November 11,” assume it was November 11, 2019.
Normalize this database and implement in MySQL Workbench using this information.
Name your database schema “CivicCenter.” Please try to use this exact name to make it easier to grade. It is not case-sensitive so it’s OK to use “civiccenter” or CIVICcenter.” Do not use “Civic_Center” or “civic-center.”
You may use as many or as few tables as you see fit.
Your deliverables are:
1. “XXX_Civiccenter_ERD.pdf” where XXX are your initials. This is a pdf of an entity-relationship diagram made using MySQL Workbench’s modeling tool (you can find this under File Menu -> Export.)
a. Entity-relationship diagrams made using other tools will not earn full points.
b. The diagram should show all entities you use (it’s OK to use more than one page if you need to, and you can cobble together screenshots if you need.)
c. All entities should show all fields and keys; expand the boxes or rearrange them if you need to.
d. All relationships and cardinalities should be easily visible
e. All relationships should have text sentence(s) labeling them. For example, if this were the pet database, you might write, “Each pet must have one and only one owner. Each owner may have zero or more pets.”
Last one! You want to know how
Last one! You want to know how many times products were shipped to customers. Note you want the total number of shipments, not the number of items shipped – one shipment of 10 items and one shipment of 20 items would be two shipments, not 30 items.
Include all products, even if they had no shipments. In this case, their count would be 0.
Include the manufacturer and the product name
Include the customer company name
Sort it so the smallest count of shipments is on top. After that, sort ascending by Manufacturer name and then product name.
Which of the following statements is true of your output? Check all that apply.
Question 6 options:
The top row of data is the Manufacturer Drill Bits R Us for its "Iron Man" product
The top row of data is from Manufacturer Born, Inc for its "To Be Wild" product
The bottom row of data shows Manufacturer Stephen, Product Name "Curried Basketball," for CompanyName Midland Imports, in 3 shipments
The bottom row of data shows Manufacturer Born, Inc, Product Name "To Be Wild," for CompanyName Bridge Fudge Retail, for 30 shipments
Question 5 (2 points) Question 5 options:[SQL Week 2
Question 5 (2 points)
Question 5 options:
[SQL Week 2 Question 200-505]
You want to report on a certain group of your products. Select
Only customers who have a balance of 0
Only shipments which were sent by employees whose employee location is California, Maryland, or New York
Only products which have had a shipment
Only employees who have made a shipment
Only customers who have received a shipment
Compute the total quantity of shipments which meet these criteria
Aggregate it by manufacturer and by product name. This means each manufacturer/product name combination appears on only one row in the table. If a manufacturer makes more than one product, it’s OK if it shows up once per product.
Sort it descending by the sum of the shipment quantity, so the largest shipment quantity shows up on top. Within shipment quantities, sort it alphabetically on manufacturer and then product name.
Your results should look something like this:
What is the total shipment quantity for General Widgets' "Widget Maximus" product? This would be in the green "A" in the screenshot above.
Which company had Total Shipments of 2 for the Twisters? This would show up in the blue letter "B" above.
Drill Bits R Us
Question 6 (2 points)
Question 4 (2 points) Question 4 options:[SQL Week 2
Question 4 (2 points)
Question 4 options:
[SQL Week 2 Question 200-504]
You want to compute the order total for your shipments. For all of your shipments, put together a report which contains the following column. Sort it ascending on idShipment
Create a new field in the output result called OrderTotal, which is the price per unit multiplied by the order quantity. You will need to calculate this field in SQL.
Your screen output should look something like this:
What is the Scantag for the entry outlined in blue above? Make sure you also type the hyphen in the middle of the scantag.
What is the quantity in the blue box? Enter your answer to the nearest integer.
What is the order total that should be in the blue box? Enter your answer to two decimal places.
Paste the SQL you used to execute this query below.
Question 3 options:[SQL Week 2 Question 200-503]You
Question 3 options:
[SQL Week 2 Question 200-503]
You suspect certain employees are not servicing very many shipments, and some may not have any shipments at all.
Create SQL to run a report which will
List all your employees and the customers they serviced along with the number of shipment for each customer. Include all employees, including those who may not have serviced any customers.
Note you want the number of shipments, not the sum of shipment quantity. For example, if you have one shipment of 10 units, and another shipment of 20 units, our number of shipments here is 2.
Sort the list descending on number of shipments, and then on employee id. If an employee has not serviced any customers, display that employee at the top.
What is the first name of the very first employee you display? (Just type the first name here.)
How many shipments did employee Sophia Moore have to Lake Erie Guitars?
If she didn't have any (or her name is not shown in the results anywhere even when you scroll down), enter the number 0.
[SQL Week 2 Question 200-502]You want to
[SQL Week 2 Question 200-502]
You want to make a report which will give you information about shipments sent by your lower-commission employees. Create a query which will
Include only all those shipments which were sent by an employee whose commission is 10% or less (include commissions of exactly 10%)
Print the state, company name, total quantity in the shipments for that customer, and the maximum employee commission in that order left to right.
Sort it alphabetically by state (ascending). If one state has several companies in it, they should be sorted within the state, with the highest total order quantity on top.
What information is found in the third row down, which should be the first one containing an entry from the State of Michigan? It corresponds to the blue box below.
Question 2 options:
Thumb Exports, 1, 0.04
Midland Imports, 57, 0.09
Something else not found here
Huron Bay Exporters, 68, 0.10
ABC Incorporated, 48, 0.09
WK 2 – DATA 620 QUIZ 2.5B – Multi-Table SQL Queries
SQL Week 2 Assignment 2.5B Questions
Question 1 (1 point)
Question 1 options:
[SQL Week 2 Question 200-501]
For every shipment in the shipment table, you want to know its shipment details, the customer information, and the employee information. Include exactly and only all the records for which a shipment ID exists. Sort it ascending by Scantag.
What is the idShipment of your first record?
What is the idCustomer of the first row returned?
What is the first name (EmpFirstName) of the employee in the first row returned?
(Make sure to type the first name only.)
Egbert
[SQL Week 2 Question 200-002] We want to
[SQL Week 2 Question 200-002]
We want to get a workload by employee printout. For every employee, print the first name, last name, all ticket ID numbers assigned to that employee, the ticket description, and ticket duration of our records. Include only tickets which have been assigned to an employee. Include all employees, even if that person doesn’t have any tickets in the current database. Sort them first alphabetically by first name (starting with letter A at the top) and then ascending by ticket duration.
Below is a screenshot of your query results. Which entry should go in the blue box?
QUIZ 2.3B – TWO TABLE SQL QUERIESSQL
QUIZ 2.3B – TWO TABLE SQL QUERIES
SQL Week 2 Assignment 2.3B Questions
Question 1 (1 point)
Question 1 options:
[SQL Week 2 Question 200-001]
Print the first name, last name, ticket ID, ticket description, and duration of the employees who are assigned with a ticket(s). Sort them first alphabetically by first name (starting with letter A at the top) and then ascending by Duration.
This query should have resulted in employee Ben Johnson as your first several rows.
What is the top row idTicket for Ben?
1. SQL Week 2 Question 200-506] Last
1. SQL Week 2 Question 200-506] Last one! You want to know how many times products were shipped to customers. Note you want the total number of shipments, not the number of items shipped – one shipment of 10 items and one shipment of 20 items would be two shipments, not 30 items.
a. Include all products, even if they had no shipments. In this case, their count would be 0.
b. Include the manufacturer and the product name
c. Include the customer company name
d. Sort it so the smallest count of shipments is on top. After that, sort ascending by Manufacturer name and then product name.
1. [SQL Week 2 Question 200-505] You
1. [SQL Week 2 Question 200-505] You want to report on a certain group of your products. Select
a. Only customers who have a balance of 0
b. Only shipments which were sent by employees whose employee location is California, Maryland, or New York
c. Only products which have had a shipment
d. Only employees who have made a shipment
e. Only customers who have received a shipment
f. Compute the total quantity of shipments which meet these criteria. Here we want the number of items shipped. If you have one shipment of 10 items, and another shipment of 20 items, this number should be 30.
g. Aggregate it by manufacturer and by product name. This means each manufacturer/product name combination appears on only one row in the table. If a manufacturer makes more than one product, it’s OK if it shows up once per product.
h. Sort it descending by the sum of the shipment quantity, so the largest shipment quantity shows up on top. Within shipment quantities, sort it alphabetically on manufacturer and then product name.
Your results should look a little bit like this. Your data may or may not match this example.
1. [SQL Week 2 Question 200-504] You
1. [SQL Week 2 Question 200-504] You want to compute the order total for your shipments. For all of your shipments, put together a report which contains the following columns.
a. Sort it ascending on idShipment
b. Create a new field in the output result called OrderTotal, which is the price per unit multiplied by the order quantity. You will need to calculate this field in SQL.
Your output will look a little bit like the following. Your numbers may vary from this example
1. [SQL Week 2 Question 200-503] You
1. [SQL Week 2 Question 200-503] You suspect certain employees are not servicing very many shipments, and some may not have any shipments at all.
Create SQL to run a report which will
a. List all your employees and the customers they serviced along with the number of shipments for each customer. Include all employees, including those who may not have serviced any customers.
b. Note you want the number of shipments, not the sum of shipment quantity. For example, if you have one shipment of 10 units, and another shipment of 20 units, our number of shipments here is 2.
c. Sort the list ascending on number of shipments (so the smallest numbers of shipments are at the top), and then on employee id. If an employee has not serviced any customers, display that employee at the very top.
For the Question 4, you may want to review the SQL Aliases functionality (“select X from Y as Z”). There is a section in WWW3 SQL schools on this.
1. [SQL Week 2 Question 200-502] You
1. [SQL Week 2 Question 200-502] You want to make a report which will give you information about shipments sent by your lower-commission employees. Create a query which will
a. Include only all those shipments which were sent by an employee whose commission is 10% or less (include commissions of exactly 10%)
b. Print the state, company name, total quantity in the shipments for that customer, and the maximum employee commission in that order left to right.
c. Sort it alphabetically by state (ascending). If one state has several companies in it, they should be sorted within the state, with the highest total order quantity on top.
Your data may or may not match the example below.
For example, if your input data looks like this
Shipment
Customer Company Name
Customer State
Quantity
Employee
Employee Commission
1
ABC
MN
1
Sarah
0.05
2
ABC
MN
2
Thomas
0.10
3
Junior
MN
5
Sarah
0.05
4
DEF
GA
1
Thomas
0.10
5
DEF
GA
1
Victor
0.11
6
G
MD
10
Wally
0.01
7
H
MD
20
Xerxes
0.09
Your answer would look like this:
State
Customer CompanyName
Total Quantity shipped to this customer
Maximum Employee Commission in these shipments
GA
DEF
1
0.10
MD
H
20
0.09
MD
G
10
0.01
MN
Junior
5
0.05
MN
ABC
3
0.10
Part 2 – Construct your Warehouse SQL
Part 2 – Construct your Warehouse SQL statements
This is where you start to turn things in. To turn in your assignment, delete the preceding pages and start with this one. Make sure to put your name up top.
Construct SQL to answer each of the following questions. For each query, paste your SQL code below, and also paste a screenshot of the first 10 or so rows the query gives you.
Give an answer to the question based only on what you see in your MySQL database. (Do not use Excel or other tools to answer these questions; since we are trying to learn MySQL here, you should do these exercises in MySQL.)
You will eventually submit this document as part of your week’s homework.
After you have completed your first draft of this document, you will use the online quiz to verify your answers. The online quiz uses the same numbering system given here, so you can quickly find the question you need.
You are welcome to update your homework document with results from the online quiz, and submit the homework document after you’ve checked your homework online.
1. [SQL Week 2 Question 200-501] For every shipment in the shipment table, you want to know its shipment details, the customer information, and the employee information.
a. Include exactly and only all the records for which a shipment ID exists.
b. Sort it ascending by Scantag.
c. Your output should look a little like this. Note the “shipment information” will be in the form of several distinct columns. (Hint: look up the “ Select * ” command to select several columns at once.)
Assignment 2.5A In this assignment, we walk you
Assignment 2.5A
In this assignment, we walk you through joins which use more than two tables.
Here’s what you are going to do for this assignment.
· Review this document. There are several parts to this document.
o Part 1 is instructions only. There is nothing to turn in, but it contains important information.
o Part 2 of this document is called the TURN IN part. This contains something you should turn in for this assignment.
· Go through this document and write SQL to answer all the questions listed below. As part of your SQL, you will capture screenshots and paste them where indicated.
· Refer to this document as you complete the online quiz which verifies your homework.
· You are welcome to use the quiz results to update your homework file.
· When you have completed the online quiz, submit the TURN IN portion of this document in the Assignment dropbox.
· Remember, you can always reach out for help to your FA or instructor if your SQL is not behaving for you.
· Your completed deliverables for this assignment are:
o The TURN IN section of this document, submitted to the LEO dropbox
o The completed online quiz results
· There is no deliverable of a SQL script for this assignment. You are welcome to create one for your own records, but the TURN IN section of this document will suffice for your faculty.
· And, you probably already know this by now, but you can always reach out to your FA for assistance.
Purpose: get more practice with multi-table SQL queries. We will practice
· Complex joins
· LEFT and FULL joins
· Order by and Group by
Part 0 – Get into your Folder
We will continue to work in the “Week2” folder inside your “DATA620” folder. Download everything into this folder and run it from there.
Part 1 – Fire up the Warehouse database
1. Go ahead and fire up MySQL Workbench and connect to your Azure MySQL database.
2. Download and run the following scripts. They will install a new database schema called “warehouse” which contains information about your customers, your products, your employees, and your shipments.
a. Warehouse_setup.sql – this will create a database schema called “warehouse”
b. Warehouse_insert.sql – this will populate your database with information about your customers, products, employees, and shipments. (Note there may be a numeric suffix on the end of your sql, so its name may be ‘warehouse_insert_v04.sql’ for example. The information in your database may or may not be exactly the same as in this example. That’s OK.)
c. You can make sure that all tables are properly populated by executing;
SELECT * FROM warehouse.<replace-table-name>;
Where <replace-table-name> is in {customer, employee, product, shipment}
d. The insert SQL also contains one query at its bottom, which should give you a list of the company names and shipments. The first one should be from ABC Incorporated, with an idShipment of 1200 and a Quantity of 9.
3. Let’s reverse engineer this database to see what the EER looks like.
a. Remember how? Database -> Reverse Engineer and then click “next” until it gives you the EER. I needed to drag my tables a little bit apart so I could see them all.
b. You can export your diagram using File -> Export -> Export as PNG. You can also grab a screenshot.
c. Verify you can see one primary key for each table. The primary key is denoted by a golden key icon.
d. Verify you understand the relationships. You should be able to see that a customer, product, and employee can exist independently.
e. You should also be able to see that a shipment ties a quantity ordered with the customer who ordered it, the product which was ordered, and the employee who fulfilled the order. A shipment also contains a Scantag and a Reference Number, two internal notations the company sometimes uses.
4. You may want to refresh your memory about how to do three-table joins. In the WWW3 Schools SQL instructions, you can see the “SQL Inner Join” on the left menu, and halfway down that page there’s a note about how to JOIN three tables.
5. There is nothing to turn in from this part.
UMGC Data 620 Assignment 4.3 Your name:Date: DeliverablesPossiblePointsTURN
UMGC Data 620 Assignment 4.3 Your name:
Date:
Deliverables
Possible
Points
TURN IN #1 – SQL script
5
TURN IN #2 – csv file
5
TOTAL
10
This assignment covers how to import data from Excel to MySQL, and how to export data from MySQL to Excel.
If you have a Mac, please see the last page (Appendix) for some helpful notes.
Reading:
? MySQL Reference Manual, Section 13.2.9.1 – how to use the SELECT INTO command
This document contains an EXAMPLE of each type of operation; you do not need to turn in anything related to the examples. The basic logic path of this assignment is:
1. Get your SQL up and running, and connected to the Cloud.
2. Prepare your Excel file into a .csv for import and get it in the right spot.
3. Import: Using Table Import Wizard
4. Export: One option presented; this sends your output file to a designated folder on your local machine.
After the examples, there is an assignment. Your deliverable here is marked with TURN IN and consists of:
? An SQL script
? An output .csv file
I have chosen to err on the side of lavish documentation here, so if you’re feeling Spartan instead, you are welcome to skip straight to the assignment on the last few pages. But if you’d like an extravagant collection of screenshots with commentary, turn straight to page 2.
EXAMPLE of an IMPORT:
Raw data stored in xls/xlsx files can be imported into MySQL databases easily. The following tutorial will demonstrate the data import process. This tutorial demonstrates a simple 50 row Excel sheet import process:
Please be aware of the following facts before you attempt the import process:
? You can only export one worksheet at a time to the MySQL database that you have created. Exporting is fast and easy provided they are in compatible formats and within a reasonable file size.
? The Excel worksheet should be clean (free of fancy formatting such as images, colors, wrap text and fancy fonts) before exporting. (The example we are doing here is well?behaved, but if you are trying to import other data from the wild, you may need to tame it a bit before doing the import.)
Get your Excel file prepared for upload to MySQL:
1. Make yourself a “Week 4” folder. Pay attention to whether you put a space between the “Week” and the “4” – I suggest you put a space there because I did, and then your filename/paths will match these directions. (If the script calls for “Week 4”, and you have “Week4” or “Week_4”, the computer won’t find it.)
UMUC Data 620 Assignment 4.1AThis assignment covers
UMUC Data 620 Assignment 4.1A
This assignment covers entity integrity and referential integrity. This is a group assignment and only one person from your group needs to post your results. It is wise for all members of the group to be familiar with the assignment.
Your group’s deliverable is participation in the Discussion Forum. Review the material below, and post your group’s answers to the discussion forum.
Feel free to read and respond to other classmates’ postings.
Reading:
· Narasimhan, Girija, “Entity Integrity Constraints,”
o https://www.youtube.com/watch?v=0r-Lu48ZTD0 (2 minutes)
· Caleb the Video Maker 2, “MySQL Lecture Series,” specifically:
o MySQL 11 - Entity Integrity: https://www.youtube.com/watch?v=wCOpSR-_VvA
o MySQL 12 - Referential Integrity: https://www.youtube.com/watch?v=Q2XnoPDWwmY
o MySQL 13 - Domain Integrity: https://youtu.be/7g0tyzy2qOk
o MySQL 14 - Primary Key (the video starts with some deer jumping in a yard, but don’t be fooled, Caleb is waiting for you): https://youtu.be/E3Bs3H14C8Y
o (We skip his MySQL 15 - Auto Increment; you can certainly watch if you want to.)
o MySQL 16 - Foreign Key: https://youtu.be/5HQzigiRb5c
Part A: Data Integrity
This week’s reading refers to entity and referential integrity within databases. Without these items, your database would probably not work at all, and if you got it to work, it would break easily or give you crazy responses.
Get the database into MySQL Workbench
This is going to require a little bit of SQL from you.
1. Log into MySQL Workbench and create a new database. Name it “my_university.”
9/7/2020 Data 620 - Lesson 4: Data Manipulation
9/7/2020 Data 620 - Lesson 4: Data Manipulation and SQL File Input/Output - DATA 620 9040 Data Management and V isualization (2208) https://learn.umgc.edu/d2l/le/content/512831/viewContent/19677149/V iew 1/3 OVERVIEW: The first three weeks of this class introduced R X W R W K H J H Q H U D O D U H D R I G D W D P D Q D J H P H Q W D Q d gave R X D W D V W H R I K R Z W R X V H 6 4 / W R W D O N W R our database. Once R X K D Y H D Q R U P D O L ] H G G D W D E D V H D Q G D V N L O O H d SQL programmer , there are additional areas to consider . This week, we cover data quality, database design and operations, data governance, and data storage and indexing. W e also expand our SQL commands to include those to read data in from outside sources, and to write the results of SQL queries to outside sources: in our case, we use a comma separated values (csv) format. Data Qualit refers to how fit R X U G D W D L V I R U W K H S X U S R V H V ou intend. You could have a beautifull Q R U P D O L ] H d relational database with perfectl X Q L T X H S U L P D U ke V D Q G I O D Z O H V V I R U H L J Q N H relationships, but if all the customers’ names are spelled incorrectly, it will still not be ver X V H I X O I R U P D U N H W L Q J S X U S R V H V 7 K L V Z H H N , we’ll cover the major wa V W K L Q J V F D Q J R Z U R Q J Z L W K our data: it could be inaccurate, out of date, incomplete, or inconsistent with itself. We’ll also cover the major wa V W K L Q J V F D Q J R Z U R Q J Z L W K our database itself: R u can fail to have referential integrity , or R X U G D W D F R X O G I D L O W R I R O O R Z E X V L Q H V V U X O H V ' D W D T X D O L W is such a huge corporate issue that man F R P S D Q L H V K D Y H H Q W L U H W H D P V R I S H R S O H G H G L F D W H G W R H Q V X U L Q J G D W D T X D O L W y . In our stud R I Database Design and Operations this week, we investigate structural problems with databases. Because databases are so precisel V S H F L I L H G D Q G E H F D X V H W K H run on computers, a structural problem can be catastrophic. We have seen in previous weeks how to set up a good database, in which all primar N H s are unique and all foreign ke V O H D G W R V R P H Z K H U H 7 K L V Z H H N Z H Z L O O V W X G integrit F R Q V W U D L Q W V Z K L F K K H O p ensure that the foreign ke V U H I H U W R H [ L V W L Q J G H V W L Q D W L R Q V D Q G W K D W L I ou delete a record, R X G R Q ’ t bring down the database. We then turn our attention awa I U R P W K H Q R U P D O L ] H G U H O D W L R Q D O G D W D E D V H V F K H P D V R R I W H Q X V H G L Q R S H U D W L R Q D l data stores. W e look towards an alternative or ganization called the Snowflake or Star Schema . Rather than several smaller tables linked with ke V W K H V Q R Z I O D N H V F K H P D K D V R Q H F H Q W U D O L ] H G I L O H Z L W K Y D U L R X V ? D U P V ? R r points of the star) attached to it. Each “arm” contains the information R X Q H H G W R T X H U along one dimension. For example, a large flat file could contain the date of a purchase, the purchased item, the shipment destination, and the customer information. One arm of one star could break the dates down into months, quarters, and H D U V Another arm of the star might break the shipment destination down into states (such as Mar O D Q G R U U H J L R Q s (such as the Pacific Northwest). This lets the customer run quick queries for things like “all orders which shipped in Quarter 1 of 2015, to the state of Mar O D Q G ? R U ? D O O R U G H U V X Q G H U Z K L F K V K L S S H G L Q 0 D of any H D r, to an Z K H U H L Q W K H 3 D F L I L F 1 R U W K Z H V W ? 4 X H U L H V R I W K L V W pe are included in a method called Dimensional Anal V L s . Data Governance: Or , the care and feeding of R X U G D W a Data is crucial to modern corporate operations, and H W L W F K D Q J H V D O O W K H W L P H - X V W D V Q H Z E X L O G L Q J F R Q V W U X F W L R n needs to be maintained, and periodicall J L Y H Q D Q H Z U R R I P R V W F R U S R U D W H G D W D Q H H G V W R X Q G H r go regular maintenance, and periodicall J L Y H Q D U H R rganization. Even if nothing went wrong with R X U G D W D S H U L R G L F D O O y there are new laws (such as HIPAA) which require new measures to be taken with existing data. Data governance includes the activities a corporation needs to undertake to identif S U R E O H P V Z L W K L W V G D W D I L [ W K H P , and deplo W K H U H V X O W V Data Storage and Indexing: How do R X I L Q G our data quickl ? With our small databases in this class, it’ s not reall Q H F H V V D U to optimize for quer V S H H G D F R P S X W H U F D n simpl O R R N W K U R X J K H D F K R Q H R I R X U I H Z K X Q G U H G D L U O L Q H U H F R U G V W R I L Q G W K H R Q H Z H Z D Q W % X W Z K D W L I ou have a few million records? Or what if R X D U H ) D F H E R R N D Q G D U H J H Q H U D W L Q J P R U H W K D Q P L O O L R Q ? O L N H V ? H Y H U y minute [1] ? Even if R X U T X H U can search one million rows a minute, if R X D U H ) D F H E R R N ou are getting 4 minutes slower in R X U T X H U L H V I R U H Y H U minute of time which passes. Y ou started off behind and R X D U e slowing down. One solution is to use a data index (plural: several data indices ) to help cut R X U V H D U F K W L P H If R X K D Y H D O L V W R I Q D P H V V R U W H G D O S K D E H W L F D O O y , and the one R X Z D Q W V W D U W V Z L W K D ? 4 ? ou don’ t need to start 9/7/2020 Data 620 - Lesson 4: Data Manipulation and SQL File Input/Output - DATA 620 9040 Data Management and V isualization (2208) https://learn.umgc.edu/d2l/le/content/512831/viewContent/19677149/V iew 2/3 searching at the “A.” Data indexing is a wa W R F R Q V W U X F W D V H U L H V R I P D S V I R U our SQL query , so it can make use of alphabetical, numeric, and other sorted orders to accelerate R X U V H D U F K . SQL File Input/Output: How do R X read and write to external files? Up until now, our databases have magicall O L Y H G Z L W K L Q W K H & O R X G & R P S X W L Q J / D E V D Q G Z H K D Y H F U H D W H G D Q d destro H G W K H P Z L W K L Q W K H & O R X G & R P S X W L Q J / D E ’ s confines. But what happens if R X Z D Q W W R V D y, download a data file from the Center for Disease Control and run SQL queries on who got the flu? And then what if R X r quer U H V X O W V D U H V R E U L O O L D Q W ou’d like to share them with R X U E R V V Z K R Z L O O R Q O use Excel? There are commands in SQL R X F D Q X V H W R W H O O L W K R Z W R U H D G G D W D L Q I U R P D Q H [ W H U Q D O I O D W I L O H D Q G K R Z W R Z U L W H R X W S X W W o an external flat file. We will show R X K R Z W K R V H Z R U N W K L V Z H H N Required Readings for the W eek: 1. Data Quality M 6 4 / 11 - Entit , Q W H J U L W y M 6 4 / 5 H I H U H Q W L D O , Q W H J U L W y M 6 4 / ' R P D L Q , Q W H J U L W y M 6 4 / 3 U L P D U Ke V W D U W V Z L W K V R P H G H H U M X P S L Q J L Q D ard) (We skip the M 6 4 / $ X W R , Q F U H P H Q W ou can certainl Z D W F K L I ou want to.) M 6 4 / ) R U H L J Q . H y Chr V O H U V ' D W D 4 X D O L W Management Case Study Data Qualit & R Q F H S W V ' D W D 4 X D O L W T utorial, Data Warehousing Tutorial Data Qualit 0 D W W H U V T ech Vision 2018 T rend (Accenture) 2. Database Design and Operations Inmon vs. Kimball . This is a brief review of the normalized vs. star schema la R X W V . Star Schema Slides from Professor Majed Al-Ghandour . (Link refers to Data 620 Model Classroom 2158, material available in Week 4). 3. Data Governance Data Architecture: A Primer for the Data Scientist , b , Q P R Q D Q G / L Q V W H G W & K D S W H U 5 Data Management Practices Across an Institution: Surve D Q G 5 H S R U t Data Governance and Stewardship 4. Data Storage and Indexing Database Lesson #7 of 8 Database Indexes 5. SQL File input/output M 6 4 / 5 H I H U H Q F H 0 D Q X D l , Section 13.2.6 through Section 13.2.9.1. Also see Section 3.3.3, the LOAD command, for alternative wa V W R O R D G G D W D L Q W R D W D E O H . Optional Readings for the W eek: 1. Data Quality 2. Database Design and Operations 3. Data Governance Managing and Sharing Your Data: Best Practice for Researchers 4. Data Storage and Indexing 5. SQL File Input/Output 9/7/2020 Data 620 - Lesson 4: Data Manipulation and SQL File Input/Output - DATA 620 9040 Data Management and V isualization (2208) https://learn.umgc.edu/d2l/le/content/512831/viewContent/19677149/V iew 3/3 [1] http://editorial.designtaxi.com/editorial-images/news-data14082015/big.jpg
WK 4 – DATA 620 Discussion 4.1B
WK 4 – DATA 620 Discussion 4.1B - Data Quality and Governance Do this as a GROUP ASSIGNMENT. (Once your group has submitted, you should have access to this thread. If you don't, go ahead and submit a little something like 'Please disregard - Bobby from Group 9 posting so I can see responses.') This assignment covers data quality and data governance. Have your primary posting (the response to the boss) completed by Monday midnight, and have all your responses to classmates completed by Tuesday evening. Reading: Data Quality Inmon and Linstedt, Chapter 5.5 (Data Governance) Schwartzrock, Todd, "Chrysler's Data Quality Management Case Study," https://www.youtube.com/watch?v=N78lHpiCD0k (10 minutes) Edureka, "Data Quality Concepts | Data Quality Tutorial | Data Warehousing Tutorial," https://youtu.be/HWaBdqmTqEA (25 minutes) Accenture, "Data Quality Matters - Tech Vision 2018 Trend," https://youtu.be/abNDyLt8ilU (25 minutes) Data Governance and Stewardship (On this webpage, there is a button to download the pdf document) Watch the videos above and read Chapter 5.5 in Inmon and Linstedt. Assume you work at XYZ Automotive, a competitor of Chrysler, and your boss has written you the following email: “Hi, Everybody’s talking about data quality these days, but I’m not sure it’s actually worth the expense. I’ve got three issues here and I want you to enlighten me. First, I’m not really sure what data quality means – I know it means we spell the customer’s name right, but what else does it mean beyond writing down who bought what car when? In other words, can you define data integrity for me? Second, what exactly is data governance and how would it apply to our company? And last, I need to make a business decision. We’re considering having a consulting firm do an engagement which it says will radically improve the quality of our data. What business advantages has Chrysler actually seen from its data quality initiative? What advantages would you think we could get by doing our own?” Compose an email to answer your boss. Please paste the text of the email within the discussion forum; do *not* put your email as an attachment. Make it easy for us to respond! Include in your submission your group name; for example, if my group is Group X, I might title mine, "Group X says we absolutely need better data governance." Choose two other submissions to respond to. In your response, include three stars (three things they did well), plus one wish (constructive criticism.) The responses to classmates may or may not come from the same person who did the initial posting. Again, make sure it's easy to tell which group is responding; title it something like "Group X responds to Group Y." A successful submission will Contain approximately 300-500 words, plus a bibliography Be as formal (or informal) as you would take in a tone with an email to your boss – the only difference here would be that you do need APA citations and a bibliography. Pass a reference citation check for plagiarism – cite all your sources Define data quality and cite your sources. You may use sources outside of the Chrysler and Global Data Store assigned sources, but you must convince me you have watched those two sources as well. Define data governance and cite your sources. Again, you may use sources outside of Inmon and Linstedt, but you must convince me you have understood Chapter 5.5 in our textbook. Be as specific as you can about the business advantages for Chrysler from better data quality. List (or estimate) dollars and any other performance metrics you can find. (You are expected to be able to answer this using mostly just the Chrysler video; you are welcome do to brief Internet searches – and cite your sources - if you want to look up a fact or two, but this is *not* a research question.) Give good feedback to classmates on their postings.
WK 4 – DATA 620 Discussion 4.1C
WK 4 – DATA 620 Discussion 4.1C - Star Schema vs. Relational Databases
Do this as a GROUP ASSIGNMENT.
(Once your group has submitted, you should have access to this thread. If you don't, go ahead and submit a little something like 'Please disregard - Bobby from Group 9 posting so I can see responses.')
This assignment covers the star schema vs. relational database schema.
Have your primary posting (the response to the boss) completed by Monday midnight, and have all your responses to classmates completed by Tuesday evening.
Reading: Database schematics
This is not required reading, but it may be helpful: past students have also found useful information on the James Serra blog posting about data warehouse architecture.
Your boss is at it again. You are still working at XYZ Automotive, which is still a competitor of Chrysler, and your boss has written you the following email:
“Hi,
So I’ve done a little research on star schema vs. relational databases, and I’m just plain old confused. I’ve read the materials above, and Section 3.4 in the Inmon/Linstedt book, and I’m wondering if you could define the relational database model, define the star schema model, and tell me which one we need to use. In plain old English.”
Compose an email to answer your boss. Please paste the text of the email within the discussion forum; do *not* put your email as an attachment. Make it easy for us to respond! Include in your submission your group name; for example, if my Group is X, I might title mine, "Group X recommends the star schema"
Choose two other submissions to respond to. In your response, include three stars (three things they did well), plus one wish (constructive criticism.) The responses to others may or may not be from the same person who posted the initial submission.
A successful submission will
Investing $1,500,000 in TQM's Channel Support Systems
Investing $1,500,000 in TQM's Channel Support Systems initiative will at a minimum increase demand for your products 1.7% in this and in all future rounds. (Refer to the TQM Initiative worksheet in the CompXM Decisions menu.) Looking at the Round 0 Inquirer for Andrews, last year's sales were $163,290,917. Assuming similar sales next year, the 1.7% increase in demand will provide $2,775,946 of additional revenue. With the overall contribution margin of 34.1%, after direct costs this revenue will add $946,598 to the bottom line. For simplicity, assume that the demand increase and margins will remain at last year's levels. How long will it take to achieve payback on the initial $1,500,000 TQM investment, rounded to the nearest month?
Currently Digby is paying a dividend of
Currently Digby is paying a dividend of $11.99 (per share). If this dividend were raised by $3.64, given its current stock price what would be the Dividend Yield?
Select: 1
$15.63
10.3%
13.5%
Assume Baldwin Corp. is downsizing the size
Assume Baldwin Corp. is downsizing the size of their workforce by 15% (to the nearest person) next year from various strategic initiatives. Baldwin is planning to conduct exit interviews to learn more about how they can improve in processes and increase productivity. The exit interviews are estimated to cost $100 per employee in additional to normal separation costs of $5000. How much will the company pay in separation costs if these exit interviews are implemented next year?
Select: 1
$96,600
$546,000
$234,600
$1,326,000
Which company has the least efficient SG&A/Sales
Which company has the least efficient SG&A/Sales ratio?
Select: 1
Chester
Baldwin
Digby
Baldwin's balance sheet has $77,547,000 in equity.
Baldwin's balance sheet has $77,547,000 in equity. Further, the company is expecting net income of 3,000,000 next year, and also expecting to issue $4,000,000 in new stock. If there are no dividends paid what will beBaldwin's book value?
Select: 1
$84,547,000
$76,547,000
$38,810,000
$30,810,000
Assume Baldwin is producing 2,480 units of
Assume Baldwin is producing 2,480 units of Bold next year. What would Bold's plant utilization be?
Select: 1
155.00%
151.90%
111.39%
158.10%
Assume Baldwin is producing 2,480 units of
Assume Baldwin is producing 2,480 units of Bold next year. What would Bold's plant utilization be?
Select: 1
155.00%
151.90%
111.39%
158.10%
Your Competitive Intelligence team is predicting that
Your Competitive Intelligence team is predicting that the Baldwin Company will invest in adding capacity to their Brat product this year. Assume Baldwin's product Brat invests in increasing its capacity by 10% this year. Because of this new information, your company anticipates all other products in the Core segment will increase their capacity by the same amount. How much can the industry produce in the Core segment the next year? Consider only products primarily in the Core segment last year. Ignore current inventories. Figures in thousands (000).
Select: 1
7,575
13,750
9,652
8,552
7,777
8,675
5,150
In the Month of March, Chester received
In the Month of March, Chester received orders of 102 units at a price of $15.00 for their product Cid. Chester uses the accrual method of accounting and offers 30 day credit terms. Chester delivers 102 units in April. They received payment for 51 units in March, and 51 units in April. In the March income statement, how much revenue is recognized on the March income statement from this order? How much in the April Income statement? (Answer in thousands)
0, $765
0, $1,530
$1,530 , 0
$765 , $765
Currently Aft is charged $3,399,200 Depreciation on
Currently Aft is charged $3,399,200 Depreciation on the Income Statement of Andrews. Andrews is planning for an increase in this depreciation. On the financial statements of Andrews will this?
Select: 1
Decrease Net Cash from Operations on the Cash Flow Statement.
Have no impact on the Net Cash from Operations as depreciation appears in both Cash Flow and the Income Statement.
Increase Net Cash from Operations on the Cash Flow Statement.
Just impact the Balance Sheet.
The Andrews company currently has the following
The Andrews company currently has the following balances in their equity accounts:
Common Stock $25,080
Retained earnings $115,836
Suppose next year the Andrews company generates $46,300 in Net Profit, and declares and pays $16,000 in Dividends. What will Andrews ending balance in Retained Earnings be next year?
Select: 1
$146,136
$156,916
$140,916
$71,380
The Digby company will increase its automation
The Digby company will increase its automation for the Dune product by 2.0. Assuming no further change in capacity, how much will this investment in automation cost?
Select: 1
$7,350,000
$14,700,000
$8,400,000
$16,800,000
Suppose you were hired as a consultant
Suppose you were hired as a consultant for a company that wants to penetrate the Comp-XM market. This company wants to pursue a broad cost leader strategy. From last year’s reports, which company would be the strongest competitor?
Select: 1
Andrews
Chester
Baldwin
Digby
How much would it cost for Chester
How much would it cost for Chester Corporation to repurchase all its outstanding shares if the price fell by 10%? Assume no brokerage fees.
Select: 1
$73.9 million
$82.1 million
$84.0 million
$91.1 million
Assuming no brokerage fees, calculate the amount
Assuming no brokerage fees, calculate the amount of cash needed to retire Baldwin's 12.5S2027 bond early.
Select: 1
$13,765,142
$15,598,251
$14,665,611
Chester has negotiated a new labor contract
Chester has negotiated a new labor contract for the next round that will affect the cost for their product Cid. Labor costs will go from $1.77 to $2.37 per unit. Assume all period and other variable costs remain the same. If Chester were to absorb the new labor costs without passing them on in the form of higher prices, how many units of product Cid would need to be sold next round to break even on the product?
Select: 1
1,047
855
921
887
The Baldwin company will continue to train
The Baldwin company will continue to train their existing workforce at their current level to help reduce turnover and improve productivity next year. Employee training costs $20 per hour. How much would their training costs per employee be to the nearest dollar?
Select: 1
$800
$1,182
$1,982
$400
Digby Corp. ended the year carrying $9,701,000
Digby Corp. ended the year carrying $9,701,000 worth of inventory. Had they sold their entire inventory at their current prices, how much more revenue would it have brought to Digby Corp.?
Select: 1
$5,933,000
$9,701,000
$21,467,600
$15,785,000
This year Andrews achieved an ROE of
This year Andrews achieved an ROE of 16.8%. Suppose the Board of Directors of Andrews mandates that management take measures to increase financial Leverage (=Assets/Equity) next year. Assuming Sales, Profits, and Assets remain the same next year, what effect would you expect this new Leverage policy will have on Andrews ROE?
Select: 1
Andrews ROE will remain the same.
Andrews ROE will increase.
Andrews ROE will decrease.
Next year Baldwin plans to include an
Next year Baldwin plans to include an additional performance bonus of 0.25% in its compensation plan. This incentive will be provided in addition to the annual raise, if productivity goals are reached. Assuming the goals are reached, how much will Baldwin pay its employees per hour?
Select: 1
$29.63
$28.15
$31.04
$28.22
Chester's turnover rate for this year is
Chester's turnover rate for this year is 6.32%. This rate is projected to remain the same next year and no further downsizing will occur from automating. What would the total recruiting cost be for Chester, assuming it spends the same amount extra above the $1,000 recruiting base as they did this year?
Select: 1
$161,448
$316
$134,540
$2,128,469
It is January 2nd and senior management
It is January 2nd and senior management of Chester meets to determine their investment plan for the year. They decide to fully fund a plant and equipment purchase by issuing $10,000,000 in bonds. Assume the bonds are issued at face value and leverage changes to 2.8. Which of the following statements are true? Select all that apply.
Select: 3
Total Assets will rise to $207,341,821
Working capital will remain the same at $11,898,413
The total investment for Chester will be $18,828,179
Total liabilities will be $130,138,340
Chester’s long-term debt will rise by $10,000,000
On the income statement, which of the
On the income statement, which of the following would be classified as a variable cost?
Select: 1
Depreciation Expense
Promotion Expense
Direct Material Expense
R&D Expense
Company Baldwin invested $37,940,000 in plant and
Company Baldwin invested $37,940,000 in plant and equipment last year. The plant investment was funded with bonds at a face value of $28,711,622 at 13.9% interest, and equity of $9,228,378. Depreciation is 15 years straight line. For this transaction alone which of the following statements are true?
Select: 5
On the Balance sheet, Plant & Equipment increased by $37,940,000.
Cash went down by $37,940,000 when the plant was purchased.
Cash was pulled from retained earnings to cover the $9,228,378 difference between the plant purchase and bond issue.
Since the new plant was funded with debt and equity, on the Balance sheet Retained Earnings decreased by $9,228,378, the difference between the investment $37,940,000 and the bond $28,711,622.
On the Balance sheet, Long Term Debt changed by $28,711,622.
Buying the plant had no net effect on the Cash account, because the plant was paid for by the bond plus retained earnings.
Depreciation increased by $2,529,333.
Cash went up when the Bond was issued by $28,711,622.
Digby has an asset turnover of 1.51
Digby has an asset turnover of 1.51 (Asset Turnover = Sales/Assets). That means:
Select: 1
Every $1.00 of assets in the firm generates $1.51 of profit.
Every $1.51 of assets in the firm generates $1.00 of sales.
Every $1.51 of profit in the firm comes from each $1.00 of sales.
Each $1.00 of assets in the firm generates $1.51 of sales revenue.
The Andrews Company has just purchased $46,968,000
The Andrews Company has just purchased $46,968,000 of plant and equipment that has an estimated useful life of 15 years. The expected salvage value at the end of 15 years is $4,696,800. What will the book value of this purchase (exclude all other plant and equipment) be after its third year of use? (Use FASB GAAP)
Select: 1
$38,513,760
$37,574,400
$33,816,960
$40,705,600
Last year the Digby company increased their
Last year the Digby company increased their equity. In 2019 their equity was $49,360. Last year (2020) it increased to $50,897.
What are causes of change in equity? Check all that apply.
Select: 3
Profits of $10,148
A change in short term debt of-$4,032.
Plant Improvements of $9,580
A bond issue of$1,279.
Depreciation of -$41,287
An accounts payable change of$1,437.
A change in cash of -$5,803.
Issue and retirement of stock .
A change of plant and equipment of$9,580.
Dividend payment of$7,057.
Change in inventory of$871
What is the Liability to Equity ratio
What is the Liability to Equity ratio of Chester?
Select: 1
1.45
2.10
4.48
.34
Review the Inquirer to determine Chester's current
Review the Inquirer to determine Chester's current strategy. How will they seek a competitive advantage? From the following list, select the top five sources of competitive advantage that Chester would be most likely to pursue.
Select: 5
Offer attractive credit terms
Accept lower plant utilization and higher capacities to insure sufficient capacity is available to meet demand
Increase demand through TQM initiatives
Reduce cost of goods through TQM initiatives
Seek high automation levels
Add additional products
Reduce labor costs through training and recruitment
Seek the lowest price in their target market while maintaining a competitive contribution margin
Seek high plant utilization, even if it risks occasional small stockouts
Seek excellent product designs, high awareness, and high accessibility
which mission statement best represents the Baldwin
which mission statement best represents the Baldwin company?
Select: 1
Innovation meets revolution. We create value for our customers through breakthrough designs that lead to unique high-performance products.
Consistency and affordability are our goals. Our central mission is to offer dependable, low-price products that our customers can count on.
Providing value to our customers is why we get up in the morning. We accomplish this by offering products at a low price our customers can afford across a wide variety of market segments.
Lasting innovation is our motivation. We build premium products that are elegantly designed to meet the needs of a variety of market segments.
Midyear on July 31st, the Baldwin Corporation's
Midyear on July 31st, the Baldwin Corporation's balance sheet reported:
Total Liabilities of $127.704 million
Cash of $10.050 million
Total Assets of $204.791 million
Retained Earnings of $42.497 million.
What was the Baldwin Corporation's common stock?
Select: 1
$34.590 million
$44.640 million
$119.584 million
$129.634 million
Looking forward to next year, if Digby’s
Looking forward to next year, if Digby’s current cash amount is $19,378 (000) and cash flows from operations next period are unchanged from this period and Digby takes ONLY the following actions relating to cash flows from investing and financing activities:
Issues $2,000 (000) of long-term debt
Pays $4,000 (000) in dividends
Retires $10,000 (000) in debt
Which of the following activities will expose Digby to the most risk of needing an emergency loan?
Select: 1
Repurchases $10,000 (000) of stock
Purchases assets at a cost of $15,000 (000)
Issues 100 (000) shares of common stock
Sells $7,000 (000) of long-term assets
select all of the following statements that
select all of the following statements that are true four years from now, in the year 2023.
Select: 2
The Thrift segment will demand 7,744 thousand units
The Nano segment will demand 5,405 thousand units
The Core segment will demand 9,774 thousand units
The Elite segment will demand 5,871 thousand units
Cure is a product of the Chester
Cure is a product of the Chester company which is primarily in the Nano segment, but is also sold in another segment. Chester starts to create their sales forecast by assuming all policies (R&D, Marketing, and Production) for all competitors are equal this year over last. For this question assume that all 700 of units of Cure are sold in the Nano segment. If the competitive environment remains unchanged what will be the Cure product’s demand next year (in 000’s)?
Dot is a product of the Digby company. Digby's sales forecast for Dot is 505 units. Digby wants to have an extra 10% of units on hand above and beyond their forecast in case sales are better than expected. (They would risk the possibility of excess inventory carrying charges rather than risk lost profits on a stock out.) Taking current inventory into account, what will Dot's Production After Adjustment have to be in order to have a 10% reserve of units available for sale?
Refer to the HR Reports in the
Refer to the HR Reports in the Inquirer. Through past investments in recruiting and training Baldwin has obtained a productivity index of 109.4%. This means that Baldwin's labor costs would be increased by 9.4% if it did not have these productivity improvements. This is a competitive advantage that Baldwin can sustain or even widen further if its competitors have no HR initiatives. Now, refer to the Income Statement in Baldwin's Annual Report. How much did Baldwin's productivity improvements save it in direct labor costs (in thousands) last year?
Which description best fits Andrews? For clarity: -
Which description best fits Andrews? For clarity:
- A differentiator competes through good designs, high awareness, and easy accessibility.
- A cost leader competes on price by reducing costs and passing the savings to customers.
- A broad player competes in all parts of the market.
- A niche player competes in selected parts of the market.
Which of these four statements best describes your company's current strategy?
Select: 1
Andrews is a broad differentiator
Andrews is a broad cost leader
Andrews is a niche differentiator
Andrews is a niche cost leader
Chester's product manager is considering lowering the
Chester's product manager is considering lowering the price of the Camp product by $2.50 and wants to know what the impact will be on the product’s contribution margin. Assuming no inventory carry costs, what will Camp's contribution margin be if the price is lowered?
Bid's product manager is under pressure to
Bid's product manager is under pressure to increase market share, but is uncertain about how to make the product more competitive. The product is reasonably well-positioned in the Thrift segment and enjoys relatively high awareness and accessibility. Which of the following would most likely result in a quick increase in market share?
Select: 1
Re-position the product to the ideal spot within the segment
Increase awareness by 5%
Increase the unit contribution margin by decreasing the MTBF
Lower the unit selling price to the bottom limit of the segment price range
Brand managers know that increasing promotional budgets
Brand managers know that increasing promotional budgets eventually result in diminishing returns. The first one million dollars typically results in a 26% increase in awareness, while the second million results in adding another 18% and the third million in a 5% increase. Andrews’s product Agape currently has an awareness level of 79% . While an important product for Andrews, Agape’s promotion budget will be reduced to one million dollars for the upcoming year. Assuming that Agape loses one-third of its awareness each year, what will Agape’s awareness level be next year?
DATA 620 QUIZ 2.5B Muti-Table SQL Queries
DATA 620 QUIZ 2.5B Muti-Table SQL Queries (3rd Attempt) Week 2 Assignment 2.5B Questions Question 1 (1 point) Question 1 options: [SQL Week 2 Question 200-501] For every shipment in the shipment table, you want to know its shipment details, the customer information, and the employee information. Include exactly and only all the records for which a shipment ID exists. Sort it ascending by Scantag. SELECT* from Customer c Join Shipment s on c.idCustomer=s.Customer_idCustomer Join Employee e ON s.employee_idEmployee =e.idEmployee where idShipment is not null order by Scantag asc; What is the idShipment of your first record? What is the idCustomer of the first row returned? What is the first name (EmpFirstName) of the employee in the first row returned? (Make sure to type the first name only.) Egbert Question 2 (1 point)
Investing $1,500,000 in TQM's Channel Support Systems
Investing $1,500,000 in TQM's Channel Support Systems initiative will at a minimum increase demand for your products 1.7% in this and in all future rounds. (Refer to the TQM Initiative worksheet in the CompXM Decisions menu.) Looking at the Round 0 Inquirer for Andrews, last year's sales were $163,290,917. Assuming similar sales next year, the 1.7% increase in demand will provide $2,775,946 of additional revenue. With the overall contribution margin of 34.1%, after direct costs this revenue will add $946,598 to the bottom line. For simplicity, assume that the demand increase and margins will remain at last year's levels. How long will it take to achieve payback on the initial $1,500,000 TQM investment, rounded to the nearest month?
