ITS410 CSU Database Management

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 = 
			when  temp_pop.age < 18 then '00 to 17'
			when  temp_pop.age > 64 then '65 to 80+'
			else  '18 to 64'

“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

School: UIUC


Apa cover page
Part One
Part Two



Database Management
Course Code:
April 6, 2019



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



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

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