i upload the excel files that you will work on it , and the instructions. please follow the instructions and be careful
data_files_hw4_rap.zip
instructions_hw4_rap.pdf
Unformatted Attachment Preview
HOMEWORK INSTRUCTIONS
Homework #4
Richest Americans Problem
Background Information
Each year since 1982, Forbes magazine has compiled a list of
the 400 richest people in America as ranked by their net
worth. As of 2017, all 400 individuals are billionaires with the
wealthiest, Bill Gates, having a net worth estimated at $89
billion [1].
Members of the Forbes 400 derive their net worth from a
variety of sources. A number have earned their wealth from
high tech businesses, with the founders of Microsoft, Amazon,
Facebook, and Oracle holding four of the top five positions.
Many have successfully built their own businesses, while
others have inherited their wealth [2].
Altogether, the $2.7 trillion of combined wealth from members of the Forbes 400
list is more than the net wealth of the over 200 million Americans who make up the
bottom 64% of the population [1], [3].
Problem Statement
In this assignment, students will explore Forbes 400 data to explore patterns with
the listed individuals, where they live, and the industries they are involved in.
Instructions
IMPORTANT: This assignment requires the Windows version of Microsoft Office.
IMPORTANT: Complete the steps below in the order they are given. Completing the
steps out of order may complicate the assignment or result in an incorrect result.
1.
Download and extract the provided Data Files ZIP file. It contains the following
files for use in this assignment:
a.
people.xml – List of people on the Forbes 400 list [2].
Table: People
Field Name
Name
Rank
Type
Short Text
Number
NetWorth
Age
StateAbbrv
IndustryAbbrv
Currency
Number
Short Text
Short Text
WealthSource
Short Text
Introduction to Computer Applications
West Virginia University
Page 1 of 8
Description
Primary key. Name of the listed person(s).
Rank of person on list, where #1 is the
wealthiest person.
Estimated net worth of the person.
Age of the person.
Abbreviation of state where person resides.
Abbreviation of industry where wealth was
earned.
Source of wealth.
Version 1.2
Modified 3/19/2018
HOMEWORK INSTRUCTIONS
Homework #4
Richest Americans Problem
b.
states.xml – Information on the states, their population, and households
[4]–[7].
Table: States
Field Name
StateAbbrv
Type
Short Text
StateName
Population
Households
Short Text
Number
Number
HouseholdIncome
MedianHouseholdNe
tWorth
AverageHouseholdN
etWorth
Currency
Currency
Description
Primary key. Postal abbreviation for the
state.
Full name of the state.
Census population estimate for 2016.
Census estimated number of households for
2016.
Median household income for 2016.
Median household net worth for 2013.
Currency
Average household net worth for 2013.
2.
Create a new Microsoft Access database named
lastname_firstname_hw4_rap.accdb.
3.
Import the following items into the database:
4.
a.
people.xml file – Import structure and data into a new table.
b.
states.xml file – Import structure and data into a new table.
We need to create a new table to store information about the industries for
which we have data.
a.
Create a table named Industries with the fields below. Use appropriate
field types and designate a good primary key.
Table: Industries
Field Name
IndustryAbbrv
IndustryName
Introduction to Computer Applications
West Virginia University
Description
Abbreviation of the industry.
Name of the industry.
Page 2 of 8
Version 1.2
Modified 3/19/2018
HOMEWORK INSTRUCTIONS
Homework #4
Richest Americans Problem
b.
Enter records for all industries below [2].
HINT: The Industries table will contain 23 records.
IndustryAbbrv
BUSV
CLOT
CONS
COPR
DIV
ENGY
ENT
FINA
GAMB
FOOD
HARD
HC
LOGI
MANU
MEDI
REAL
REST
RETL
SOFT
SPOR
TRAV
UTIL
VEH
5.
IndustryName
Business Services
Clothing and Accessories
Construction and Building Services
Consumer Products
Diversified Businesses
Energy
Entertainment
Finance
Gambling
Food and Beverage
Computer Hardware
Healthcare
Logistics
Manufacturing
Media
Real Estate
Restaurants
Retail
Computer Software
Sports
Travel
Utilities
Vehicles and Transportation
Create the appropriate relationships for the following tables. Do not enforce
referential integrity.
a.
People and States
b.
People and Industries
Introduction to Computer Applications
West Virginia University
Page 3 of 8
Version 1.2
Modified 3/19/2018
HOMEWORK INSTRUCTIONS
Homework #4
Richest Americans Problem
6.
Create separate queries to provide the information requested below. Name
each query after the step in which it appears (e.g., name the query in Step 6a
as Query6A).
HINT: Run your queries to test them. Make sure that they display all and only
the records that you would expect to appear.
a.
Create a query to display information about all people on the Forbes 400
list. List each person’s name, net worth, rank, industry name, and state
name.
Sort by rank in ascending order.
HINT: This query will show 400 records and 5 fields.
b.
We want to view information about people in the energy industry. Create
a query listing each person’s name, state name, industry name, net
worth, and the source of their wealth.
Only display records for people in the Energy industry.
Sort by state name in ascending order and then by net worth in
descending order.
HINT: This query will show 25 records and 5 fields.
c.
Create a query to find information on people in the computer software
industry or who are, at most, 40 years old. List the person’s rank, name,
age, net worth, and industry name.
Only display records for people in the Computer Software industry or
whose age is no more than 40 (≤ 40).
Sort by rank and then by name, both in ascending order.
HINT: This query will show 51 records and 5 fields.
Introduction to Computer Applications
West Virginia University
Page 4 of 8
Version 1.2
Modified 3/19/2018
HOMEWORK INSTRUCTIONS
Homework #4
Richest Americans Problem
d.
We wish to calculate the total household net worth for each state. Create
a query listing the state name, population, number of households, and
median household net worth. Also, include a field that calculates the total
household net worth.
You can calculate the total household net worth using the formula:
[ . ℎ ] ∗ [ . ℎ ℎ]
Format the calculated field as currency with no decimal places.
HINT: This query will show 50 records and 5 fields.
e.
Create a query to calculate the percentage of their home state’s total
household net worth that each Forbes 400-listed person is worth. List
each person’s name, industry name, net worth, and state name. Also,
include a field that calculates the percentage of state total household net
worth represented by the person.
You can calculate the percentage of state total household net worth using
the formula:
[ . ℎ]
([ . ℎ ] ∗ [ . ℎ ℎ])
Only display records for people in the Real Estate industry.
Format the calculated field as a percentage with 3 decimal places.
HINT: This query will show 37 records and 5 fields.
f.
We wish to summarize data about each industry. Create a query listing,
for each industry name, the count of Forbes 400-listed people, their
average age, the sum of their net worth, and their average net worth.
Format the average age as a standard-type number with no decimal
places. Format the average of net worth and sum of net worth both as
currency with no decimal places.
HINT: This query will show 23 records and 5 fields.
Introduction to Computer Applications
West Virginia University
Page 5 of 8
Version 1.2
Modified 3/19/2018
HOMEWORK INSTRUCTIONS
Homework #4
Richest Americans Problem
g.
We wish to determine the prevalence of Forbes 400-listed people in each
state. Copy-and-paste this SQL code into a new query:
SELECT States.StateName, States.Population,
Count(People.[Name]) AS ForbesListPeople,
Round(Count([Name])/[Population]*1000000,2) AS
ForbesListPeoplePerMillionResidents
FROM States LEFT JOIN People ON States.StateAbbrv =
People.StateAbbrv
GROUP BY States.StateName, States.Population, States.StateName
ORDER BY Round(Count([Name])/[Population]*1000000,2) DESC ,
States.StateName;
IMPORTANT: Do not make any modifications to the query other than
entering the above SQL code.
HINT: This query will show 50 records and 4 fields.
7.
We need to create a new table to store analysis questions responses.
a.
Create a table named AnalysisQuestions with the fields below. Use
appropriate field types and designate a good primary key.
Table: AnalysisQuestions
Field Name
QuestionNumber
Response
8.
9.
Description
Question being answered.
Response to the analysis question prompt.
In the AnalysisQuestions table, answer four of the five analysis questions
below. Respond to one question per record.
a.
Are there any noticeable patterns in the age of people on the Forbes 400
list? Why might this be the case?
b.
The average household net worth for each state is much higher than the
median household net worth. What do these values represent, and what
does the average being much higher than the median signify?
c.
Why are most of the listed people in the energy industry from Texas and
Oklahoma?
d.
Almost one-quarter of the people on the Forbes 400 list are in the finance
industry. Why might this industry be so heavily represented?
e.
Other than earning additional income or spending more money, what
might cause a person’s net worth to change?
Run the Compact and Repair Database utility on your database. Ignore any
errors you receive when running the utility.
Introduction to Computer Applications
West Virginia University
Page 6 of 8
Version 1.2
Modified 3/19/2018
HOMEWORK INSTRUCTIONS
Homework #4
Richest Americans Problem
Grading Rubric
This assignment is worth 50 points. It will be graded by your instructor using this
rubric, with partial credit awarded as appropriate:
Step 3
Steps 4a-b
Step 5
3 points
4 points total
3 points
Steps 6a-g
Step 7a
Steps 8a-e (pick 4 of 5)
4 points each
2 points
2.5 points each
The analysis questions in Steps 8a-e will be evaluated using this rubric:
Standard
Answer is reasonable.
Answer is supported.
Meets Requirements
(1.25 points)
Answer addresses the
question prompt and is
factually correct or a
reasonable interpretation of
available data.
Logical rationale is provided
to support the given
answer.
Does Not Meet
Requirements (0 points)
Answer does not address
the question prompt, is
factually incorrect, or is an
unreasonable interpretation
of available data.
Logical rationale is not
provided to support the
given answer.
Acknowledgments
The image in the introduction appears courtesy of Forbes magazine [8].
References
[1] “Forbes Releases 36th Annual Forbes 400 Ranking of the Richest Americans,”
Forbes, Oct. 17, 2017. Available:
https://www.forbes.com/sites/forbespr/2017/10/17/forbes-releases-36thannual-forbes-400-ranking-of-the-richest-americans/. Accessed: Jan. 01, 2018.
[2] “Forbes 400 2017,” Forbes, Nov. 14, 2017. Available:
https://www.forbes.com/forbes-400/list/.
[3] R. Neate, “Bill Gates, Jeff Bezos and Warren Buffett are wealthier than poorest
half of US,” The Guardian, Nov. 08, 2017, Available:
http://www.theguardian.com/business/2017/nov/08/bill-gates-jeff-bezoswarren-buffett-wealthier-than-poorest-half-of-us.
[4] G. G. Guzman, “Household Income: 2016,” U.S. Census Bureau, Washington,
DC, Sep. 2017. Available:
https://www.census.gov/content/dam/Census/library/publications/2017/acs/acs
br16-02.pdf.
[5] R. Chenevert, A. Gottschalck, M. Klee, and X. Zhang, “Where the Wealth is: The
Geographic Distribution of Wealth in the United States,” U.S. Census Bureau,
Washington, DC, 2017. Available:
Introduction to Computer Applications
West Virginia University
Page 7 of 8
Version 1.2
Modified 3/19/2018
HOMEWORK INSTRUCTIONS
Homework #4
Richest Americans Problem
https://www.census.gov/content/dam/Census/newsroom/presskits/2017/assa_geographic_distr_wealth.pdf.
[6] “Households with Grandparents Living with Own Grandchildren Under 18 Years
by Responsibility for Own Grandchildren and Presence of Parent of
Grandchildren,” U.S. Census Bureau, Washington, DC, 2016. Available:
https://factfinder.census.gov/bkmk/table/1.0/en/ACS/16_1YR/B10063/0100000
US.04000.
[7] “Annual Estimates of the Resident Population,” U.S. Census Bureau,
Washington, DC, Jul. 2016. Available:
https://factfinder.census.gov/bkmk/table/1.0/en/PEP/2016/PEPANNRES/010000
0US.04000.
[8] L. Kroll, “The Forbes 400: The Richest People in America,” Forbes, Sep. 12,
2012, Available: https://www.forbes.com/sites/luisakroll/2012/09/19/theforbes-400-the-richest-people-in-america/.
Introduction to Computer Applications
West Virginia University
Page 8 of 8
Version 1.2
Modified 3/19/2018
…
Purchase answer to see full
attachment
