434HW4

docx

School

New Jersey Institute Of Technology *

*We aren’t endorsed by this school

Course

434

Subject

Computer Science

Date

Jan 9, 2024

Type

docx

Pages

Uploaded by CorporalLyrebirdPerson751

1) The goal of this problem is to load some health data from the government into our Oracle Database and use it to answer a few questions. (This will be continued in the next homework.) Go to: https://ephtracking.cdc.gov/ Click on Explore Data STEP 1: CONTENT Click on “Select Content Area” so that you get “Chronic Obstructive Pulmonary Disease (COPD)”. Choose “Mortality from COPD” on the second drop-down menu. Choose “Crude Death Rate from COPD among people >= 25 years of age per 100,000 population” on the third drop-down menu. STEP 2: GEOGRAPHY TYPE National by State STEP 3: GEOGRAPHY All States. STEP 4: TIME 2020 STEP 5: ADVANCED OPTIONS Race Ethnicity All 4 Choices for Race Then download the data and save it as a COPD.CSV file.

► ► In this homework, we will continue to use health data from the government. 1- You are asked to create a JSON object using a COPD.csv file. The first step is to CLEAN the data. You only need state names, value, and Race Ethnicity attributes; delete the other columns. Replace “Suppressed” values with “null’ in the “values” attribute. Also, replace “White, not including Hispanic” with “white” and other Race Ethnicity strings accordingly. Load the cleaned data into an Oracle table COPD using SQL Developer. Create a table CPOD_JSON in Oracle. It should contain a single-column COPD. Then, write a PL/SQL code using an implicit cursor that reads from the COPD table and write into a COPD_JSON file that contains all data. Each state should have 6 key-value pairs; _id, state, white, black, hispanic, and other. The COPD table has 204 rows whereas the COPD_JSON table should have 51 rows. Copy and paste the complete PL/SQL code at the first red arrow below. (Edit the code so that it is NICELY indented.) Do a SNIP or screen dump to show the first 10 rows of the COPD_JSON file at the second arrow [5].

► ► 2-b ► 2-c ► 2-d ► 2-e ► 2-f ► 2-a) Create a MongoDB collection COPD that contains exactly the same information as the COPD_JSON objects. Show the insertMany operation (10 lines) after the first red arrow. Show the result of "the MongoDB version" of a Select * from COPD at the second red arrow [5]. Write a MongoDB query for the following (use the COPD collection). Use “table view” in Studio3T to display the first 10 documents for each query. 2-b) Write a MongoDB query to display state, white, and hispanic fields if the hispanic is more than or equal to 10 and the white is less than 75. Copy-paste the query, and show the result at the third red arrow [3] . 2-c) Write a MongoDB query to display state, white, and hispanic fields if the hispanic is more than or equal to 10 or the white is less than 75. Copy-paste the query, and show the result at the fourth red arrow [3] . 2-d) Write a MongoDB query to sort CPOD collection by the number of white COPD cases in ascending order for state names starting with A, C, M, W. Copy-paste the query, and show the result at the fifth red arrow [3] . 2-e) Define a variable “ healtyStates ” in which hispanic, black, and other COPD values are null. Use the variable healtyStates to list all healthy states. Copy-paste the queries, and show the result at the sixth red arrow [3]. 2-f) Insert a new field “ HealthInsurance ” and set it to “lower” for all healtyStates . Copy- paste the query, and show the result at the seventh red arrow. [3] .

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

► ► 3- Create a table POPULATION4_JSON in Oracle. It should contain a single-column POPULATION. Then, write a PL/SQL code using an implicit cursor that reads from the COPDobject (from Homework3, question2a) table and write into a POPULATION4_JSON file that contains all data but each state should be a JSON object as follows. {"state":"Alabama", "Year2017":{"white":131, "black":42.2, "multiR":null, "other":null}, "Year2018":{"white":134.6, "black":44.9, "multiR":10.2, "other":null}, "Year2019":{"white":131.2, "black":44.3, "multiR":null, "other":17.3}, "Year2020":{"white":125.8, "black":46.8, "multiR":null, "other":null}} Both the COPDobject and POPULATION4_JSON tables have 51 rows. All object values in the COPDobject table are stored as a nested JSON object in the POPULATION4_JSON table. Copy and paste the complete PL/SQL code at the first red arrow below. (Edit the code so that it is NICELY indented.) Do a SNIP or screen dump to show the first 10 rows of the POPULATION4_JSON file at the second arrow [5].

► ► 4-b ► 4-c ► 4-d ► 4-e ► 4-f ► 4 - a) Create a MongoDB collection population that contains exactly the same information as the POPULATION4_JSON objects. Show the insertMany operation (10 lines) after the first red arrow. Show the result of "the MongoDB version" of a Select * from population at the second red arrow. [5] Write a MongoDB query for the following (use the population collection). Use “table view” in Studio3T to display the first 10 documents for each query. 4-b) Write a MongoDB query to display only the state names and Year2017 nested object if black, multiracial, and other value is null in Year2017 field. How many states did you list? Copy-paste the query, and show the result at the third red arrow [3]. 4-c) Write a MongoDB query to find the summation values of all keys in Year2020. Your query should return 4 sum values for Year2020; namely whiteSum, blackSum, otherSum, and multiSum. Copy-paste the query, and show the result at the forth red arrow [3] . 4-d) Write a MongoDB query to find the summation values of all whites in all four years. Your query should return 4 sum values for whites; namely Sum2017 , Sum2018 , Sum2019 , and Sum2020. Copy-paste the query, and show the result at the fifth red arrow [3]. 4-e) Write a MongoDB query that has all data of Year2017 key-value pairs from the population collection and writes the result into a new collection named Year2017. The Year2017 collection should have only one document where the value is an array with 51 elements. Copy-paste the query, and show the result at the sixth red arrow [3]. 4-f) Write a MongoDB query to display only the black values in Year2017 collection. Copy- paste the query, and show the result at the seventh red arrow [3].

Related Documents

practical 1.docx

midterm_solution.pdf

practice_final_soln.pdf

practice_final.pdf

UCLA-COM-SCI-111-2022F-final.pdf

practice_midterm_soln.pdf

CRIM 101 M07 Assignment Template.docx

a3 (1).pdf

Week 10 assignement.docx

Week 8 Assignment Complete.docx

CEIS209 Module 7 Lesson 2 (Online).docx

Diagram 4.docx

Recommended textbooks for you

Fundamentals of Information Systems

Computer Science

ISBN:9781337097536

Author:Ralph Stair, George Reynolds

Publisher:Cengage Learning

COMPREHENSIVE MICROSOFT OFFICE 365 EXCE

Computer Science

ISBN:9780357392676

Author:FREUND, Steven

Publisher:CENGAGE L

A Guide to SQL

Computer Science

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Course Technology Ptr

Np Ms Office 365/Excel 2016 I Ntermed

Computer Science

ISBN:9781337508841

Author:Carey

Publisher:Cengage

Database Systems: Design, Implementation, & Manag...

Computer Science

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Cengage Learning

MIS

Computer Science

ISBN:9781337681919

Author:BIDGOLI

Publisher:Cengage

SEE MORE TEXTBOOKS

Recommended textbooks for you

Fundamentals of Information Systems
Computer Science
ISBN:9781337097536
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
COMPREHENSIVE MICROSOFT OFFICE 365 EXCE
Computer Science
ISBN:9780357392676
Author:FREUND, Steven
Publisher:CENGAGE L
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
MIS
Computer Science
ISBN:9781337681919
Author:BIDGOLI
Publisher:Cengage