ITS410 CSU Database Management

Anonymous
timer Asked: Mar 29th, 2019
account_balance_wallet $40

Question Description

Option #2:

Your Portfolio Project consists of two items, a lessons learned reflection report and a database design and analysis project.

Lessons Learned and Reflection

Write a 2- to 3-page summary that outlines the lessons you learned in this database class. Reflect on how these lessons can be applied toward more effective database management and reporting.

Database Design and Analysis

The following database project will create an educational attainment “demand” forecast for the state of California for years greater than 2010. The demand forecast is the expected number of population who have obtained a certain level of education. The population is divided into age groups and education attainment is divided into different levels. The population of each group is estimated for each year up to year 2050. Implement the following steps to obtain and educational demand forecast for the state of California. The files can be downloaded below.

  1. Create a ca_pop schema in your MySQL database.
  2. Using your ca_pop schema, create an educational_attainment table which columns match the columns in the Excel spreadsheet ca_pop_educational_attainment.csv.
  3. Using your ca_pop schema, create a pop_proj table which columns match the columns in the Excel spreadsheet pop_proj_1970_2050.csv.
  4. Using the data loading technique for a csv file you learned in Module 1, load the data in ca_pop_educational_attainment.csv into the table educational_attainment.
  5. Using the data loading technique for a csv file you learned in Module 1, load the data in pop_proj_1970_2050.csv into the table pop_proj.
  6. Write a query to select the total population in each age group.
  7. Use the query from Step 6 as a subquery to find each type of education attained by the population in that age group and the fraction of the population of that age group that has that educational attainment. Label the fraction column output as coefficient. For instance, the fraction of the population in age group 00 - 17 who has an education attainment of Bachelor's degree or higher is 0.0015, which is the coefficient.
  8. Create a demographics table from the SQL query from Step 7.
  9. Create a query on the pop_proj table which shows the population count by date_year and age.
  10. Use that query from Step 9 as a subquery and join it to the demographics table using the following case statement:
demographics.age = 
		case
			when  temp_pop.age < 18 then '00 to 17'
			when  temp_pop.age > 64 then '65 to 80+'
			else  '18 to 64'
			end

“temp_pop” is an alias for the subquery. Use the following calculation for the demand output:

round(sum(temp_pop.total_pop * demographics.coefficient)) as demand

Output the demand grouped by year and education level.

Write each query you used in Steps 1 – 8 in a text file. If a query produced a result set, then list the first ten rows of each row set after the query. Bundle your lessons learned report, your queries and your query results text file, and your MySQL query explanations from both before and after adding table indexes into a single zip file and submit that zip file as your final portfolio.

Tutor Answer

Mercury_H
School: UIUC

Attached.

Apa cover page
Part One
Part Two

Attached.

Running head: DATABASE MANAGEMENT

Database Management
Name:
Affiliation:
Course Code:
Instructor:
April 6, 2019

1

DATABASE MANAGEMENT

2
Part One

Data management helps in the today’s world that has been changing drastically each
and every second. This has given it a completely new dimension that shows growth and
challenges that involves the company through recording data accurately, updating and
tracking which happens on a regular basis. Through the collection of accurate data the
companies which use the said information so that they can achieve their goals through a
systematic way and they would empower their businesses through the creation of accounting
reports, calculating estimates and forecasts, and also, the creation of invoices of the
customers. The data and insights when made available then the management and organization
can be able to manage each and every database through the systems of today which is an
integral part of the companies that operate around the world.
In addition, it is important that they provide the effective management of data which
is important because they help in the transfer of the said data in throughout the entire system
which helps in the entire systems. The system helps in the data access used within a specified
time. Also, they are quite instrumental when it comes to the maintenance of strong
relationships between the data. The database management is quite crucial when it comes to
the development of different data tables that relate with one another. The system on the other
hand, can help in the branding managers in understanding the statistics through which they
can be able to make them critical decisions. This form of management helps in the updates of
the current information and the elimination of the data that is not necessary. Normally, the
relational database data will enter the new records and update all the necessary information
which allows the brands to track the information they need in an effective manner.
When information is stored in a predictable and sequential manner it helps them to get
the information quite easily. The database management system will help in enabling them to

DATABASE MANAGEMENT

3

store all the information of all kinds hence making the business fast and efficient than they
were before. Some types of the database include the relationship database management
system, the flat file based database management systems, the hierarchical database
management system, the network database management system and the object oriented
database management systems. However, the usability of database management is quite a
critical factor since they can be used with a lot of ease and the said system and functioning.
Any raw data is not useful because it cannot use be able to solve any purpose which is
important because it helps in visualizing and analyzing the data strategically. The brand
managers are able to create the policies and developing a workable strategy. That is why...

flag Report DMCA
Review

Anonymous
Thank you! Reasonably priced given the quality not just of the tutors but the moderators too. They were helpful and accommodating given my needs.

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors