Assignment01: PostgreSQL Query Part 1

Introduction

PostgreSQL is an open source relational database management system. Large datasets can be created and maintained using PostgreSQL and queries to retrieve data can be integrated into R. This assignment is intended to introduce you to building tables in PostgreSQL and writing a query to select the set of cases that you would like to use in R.

Directions

Use the lecture material and the pgAdmin 4 documention as your guide for how to create tables and run queries in PostgreSQL.

The completed assignment should adhere to the following guidelines:

a.      Include your answers in the Quarto assignment document. The only changes to the document should be your answers – leave the rest of the document unaltered. Use the R packages demonstrated in the lecture material.

b.      Write your answers using complete sentences with correct punctuation, grammar, and spelling.

c.      Submit your completed qmd file and html file generated by the Quarto file for the assignment Canvas assignment portal.

Open pgAdmin 4 and right click on the database icon and create a new database. Name the database. Within the database, create a table for the City Health Dashboard data.

1. Create a table in pgAdmin for the City Health Dashboard data in the cityhealth.csv file (attached with the assignment). Populate the table with data from the cityhealth.csv file. Save the table sql code and the code used to import the data file together as one .sql file. Paste the complete text from the SQL query below. (2 points)

   CREATE TABLE IF NOT EXISTS public.cityhealth

   (

   citystate character varying COLLATE pg_catalog.”default” NOT NULL,

   absenteeism numeric,

   pollution numeric,

   binge numeric,

   breastcancer numeric,

   cardiodeaths numeric,

   childpoverty numeric,

   colodeaths numeric,

   dentalcare numeric,

   diabetes numeric,

   frequentmental numeric,

   frequentphysical numeric,

   bloodpressure numeric,

   highschool numeric,

   housingcosts numeric,

   housinglead numeric,

   inequality numeric,

   leadriskindex numeric,

   lifeexpectancy numeric,

   limitedhealthyfoods numeric,

   birthweight numeric,

   segregation numeric,

   obesity numeric,

   overdose numeric,

   parks numeric,

   inactivity numeric,

   prematuredeaths numeric,

   prenatal numeric,

   preventative numeric,

   diversity numeric,

   smoking numeric,

   teenbirths numeric,

   thirdgrade numeric,

   unemployment numeric,

   uninsured numeric,

   violentcrime numeric,

   walkability numeric,

   CONSTRAINT cityhealth_pkey PRIMARY KEY (citystate)

   );

   copy public.cityhealth (citystate, absenteeism, pollution, binge, breastcancer, cardiodeaths, childpoverty, colodeaths, dentalcare, diabetes, frequentmental, frequentphysical, bloodpressure, highschool, housingcosts, housinglead, inequality, leadriskindex, lifeexpectancy, limitedhealthyfoods, birthweight, segregation, obesity, overdose, parks, inactivity, prematuredeaths, prenatal, preventative, diversity, smoking, teenbirths, thirdgrade, unemployment, uninsured, violentcrime, walkability)

   FROM ‘C:/Users/Eiley/ONEDRI~1/JHU/AS4707~1.81S/MODULE~2/CITYHE~1.CSV’ DELIMITER ‘,’ CSV HEADER;

2. Use the View/Edit Data menu option or the View Data icon in PgAdmin to open the table. Create a screenshot of the data and paste it into your assignment. (2 points)

3. Select the full table with a query and save as a CSV file named chdb.csv. Open the chdb.csv file in R and run dim(chdb) to confirm that you have correctly created all of the rows and columns in the dataset. Include the code and results below. (1 points)

setwd("C:/Users/Eiley/OneDrive - Johns Hopkins/JHU/AS.470.769.81 SU24/Module 2")

##load in data
chdb <- read.csv("chdb.csv")

##check dimensions of dataframe
dim(chdb)

[1] 408  37

Scoring

The assignment is worth 5 points. You must have a correct and complete answers to receive full credit.