Assignment ScenarioIn general, acquiring external data and formatting it to fit the existing structure of the internal database is a standard task. In this assignment, you'll be acquiring NOAA...

Assignment Scenario

In general, acquiring external data and formatting it to fit the existing structure of the internal database is a standard task. In this assignment, you'll be acquiring NOAA (National Oceanic and Atmospheric Association) data to include in your NWS-derived (National Weather Service) database. The big issue here is to figure out what the point of the database is: specifically, what tasks are going to be done. This is a difficult question to answer, and it's easy to get wrong. It typically involves a lot of forward planning and erring on the side of retaining data. However, we have a few days, so our purpose is going to be to tie it in as closely as possible to the NWS data. The new data we'll be using is


MIT5162-IP5-Problem1Data.csv








Download MIT5162-IP5-Problem1Data.csv





.

1. Delete unnecessary columns from the data (too many to list. If it doesn't have a direct correlation to the NWS data, we'll delete it). Remember that we'll be retaining the original data as a backup.




2. Convert the time/date of both NWS and NOAA data to something more reasonable.




3. We'll need a new location in our cities table for the 'idenfitication' column. We won't do it for this assignment, but consider: does the fact that we don't know if this is a city mean we should change the title of the Cities table? How does that change the rest of our database design?




4. Notice that Wind Speed and Wind Direction are vastly different metrics. Which one should we use?




5. Convert the rest of the metrics to the more familiar NWS table.




6. Create a new column for 'source' of the data: NOAA or NWS. This will be a new datatype of an enum, which means we'll need a table for the enums.




7. Input all of this data into the weather database.




8. Create a query showing two tables, one with all of the NOAA data and one with the NWS data, joining as needed to show all related information.

New data available from:https://www.ncei.noaa.gov/cdo-web/datasetsLinks to an external site.





.

Problem 2

For problem 2, grab another set of data from the NOAA site, follow a similar procedure, and enter it into your database. If you would like to stretch out a little, tryhttps://www.aviationweather.gov/adds/Links to an external site.





and see if you can incorporate data from that site..

Directions

Paste all of your code, diagrams, and/or the outputs from your queries into a Word document. You should not need more than a couple pages. If you want to confirm that code or output ("Block" text) looks the same as in SQL console, you can change the font to Console or Console New. Please leave it at 10-12pt font. If the instructor inputs your code into their own database console, they should get the exact same output you present.

You will be graded on whether the input matches the output and generally solves the problem as presented by the client. There should be around 5 rows on each entity table and 10-12 rows in each linking table. Please feel free to add more if you feel it is necessary, but don't go overboard. You are not expected to research 'real' information to populate the tables; fabricated information is acceptable. It is also acceptable to research the real information to populate the tables.

Note: the word 'appropriate' is used in the assessment section to indicate that there is not a 'correct' solution. There are many ways to complete this assignment. However, you should consider whether the assignment is simple enough that this is only a technicality. In short: this may not be the assignment to go the extra mile on, just produce exactly what is asked for in the simplest way.