For this assignment you will access a subset of data from the MinneMUDAC 2016 co

Question

For this assignment you will access a subset of data from the MinneMUDAC 2016 competition to import into a database tool and analyze. Only do what is explained below – ignore other datasets and questions.

The datasets are at: http://minneanalytics.org/minnemudac/data/

Download Data Set 2 and bring it into a DB (MySQL/Access/SQL Server) and run the queries you need. Extra credit: Use Tableau Public Desktop to analyze further and create visualizations: http://www.tableau.com/academic/students (Download Data Set 2 as text, then import into Tableau - then analyze in Tableau)

Data Set 2: Metropolitan Council Lake Monitoring Data (Description and Questions)

1980–2014

332 Lakes

Key Attributes:

Seasonal Lake Grade

Physical Condition

Recreational Suitability

Secchi Depth

Total Phosphorus

Potential questions for consideration:

What lakes tend to have the best / worst water quality? Are the lakes with the best / worst water quality consistent over time?

What watersheds tend to have the best / worst water quality? Are the watersheds with the best / worst water quality consistent over time?

One may anticipate that the water quality attributes of lakes within a watershed depend on each other. Does this data support this notation? How does time influence this dependence structure?

Explanation / Answer

I have downloaded text file to my desktop and worked on it. if you can repeat the same steps

it can workout for you. I placed the tableau file in below location

http://www.rapidshare.com.cn/KLVxtqc

first question answer in sql server is below:

create table lakes(
PROJECT_ID nvarchar(100),
DATA_SET_TITLE nvarchar(100),
LAKE_NAME   nvarchar(100),
CITY   nvarchar(100),
COUNTY   nvarchar(100),
DNR_ID_Site_Number   nvarchar(100),
MAJOR_WATERSHED   nvarchar(100),
WATER_PLANNING_AUTHORITY   nvarchar(100),
LAKE_SITE_NUMBER   nvarchar(100),
START_DATE   nvarchar(100),
START_HOURMIN24   nvarchar(100),
END_DATE   nvarchar(100),
END_HOURMIN24   nvarchar(100),
SAMPLE_DEPTH_IN_METERS   nvarchar(100),
Seasonal_Lake_Grade_RESULT   nvarchar(100),
Seasonal_Lake_Grade_QUALIFIER   nvarchar(100),
Seasonal_Lake_Grade_Units   nvarchar(100),
Physical_Condition_RESULT   nvarchar(100),
Physical_Condition_QUALIFIER   nvarchar(100),
Physical_Condition_Units   nvarchar(100),
Recreational_Suitability_RESULT   nvarchar(100),
Recreational_Suitability_QUALIFIER   nvarchar(100),
Recreational_Suitability_Units   nvarchar(100),
Secchi_Depth_RESULT_SIGN   nvarchar(100),
Secchi_Depth_RESULT nvarchar(100),
Secchi_Depth_QUALIFIER   nvarchar(100),
Secchi_Depth_Units   nvarchar(100),
Total_Phosphorus_RESULT_SIGN   nvarchar(100),
Total_Phosphorus_RESULT   nvarchar(100),
Total_Phosphorus_QUALIFIER   nvarchar(100),
Total_Phosphorus_Units   nvarchar(100),
longitude   nvarchar(100),
latitude   nvarchar(100)
)
--remove the first row which is header
BULK
INSERT lakes
FROM 'C:\Users\vamshikrishna\Desktop\mces_lakes_1999_2014.txt' --location with filename
WITH
(
FIELDTERMINATOR = ' ',
ROWTERMINATOR = ' '
)
GO
update lakes--to avoid double quotes in the columns
set project_id=REPLACE(project_id,'"',''),latitude=REPLACE(latitude,'"','')

select LAKE_NAME,MIN(physical_condition_result) Worst,MAX(physical_condition_result) Best from lakes group by lake_name
select LAKE_NAME,start_date,MIN(physical_condition_result) Worst,MAX(physical_condition_result) Best from lakes group by lake_name,start_date

select MAJOR_WATERSHED,MIN(physical_condition_result) Worst,MAX(physical_condition_result) Best from lakes group by MAJOR_WATERSHED
select MAJOR_WATERSHED,start_date,MIN(physical_condition_result) Worst,MAX(physical_condition_result) Best from lakes group by MAJOR_WATERSHED,start_date

select LAKE_NAME,MAJOR_WATERSHED,MIN(physical_condition_result) Worst,MAX(physical_condition_result) Best from lakes group by LAKE_NAME,MAJOR_WATERSHED
select LAKE_NAME,MAJOR_WATERSHED,start_date,MIN(physical_condition_result) Worst,MAX(physical_condition_result) Best from lakes group by LAKE_NAME,MAJOR_WATERSHED,start_date

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.