Assignment ScenarioYour guess was right in the previous assignment. Your client was incredibly pleased to have all of the weather data in a database format. Their response was 'Can you do more?!' But...

Assignment Scenario

Your guess was right in the previous assignment. Your client was incredibly pleased to have all of the weather data in a database format. Their response was 'Can you do more?!' But just dumping more data into single tables will not add functionality, so you've decided to redesign the process as a relational database. At this point, your database will likely start to integrate problems 1 and 2 into a single database within your DBMS. Please still differentiate them in the documentation of your submission.

Problem 1

We will go over this problem step-by-step in the Live Session.

1. Return to Problem 1 from the previous assignment. We need to consider what needs to be added to this database to maximize its utility.
   
   
   
   
   1. In this case, let's consider the following entities: City, Regions, Weather Authority, Weather Maximums, Weather Minimums, and Weather Events. Some of these will have one or only a few entries, but we're future proofing this for later. Side task: On your own, can you assemble an ERD for this assignment
   
   
   
   
   
   
   
   




2. So, we're going to create several tables, one for each of the listed tables.
   
   
   
   
   1. City and Region are a Many-to-One relationship, so we'll create a foreign key in City for which region it belongs to. City will need some new columns (it already had CitID and CitName): CitCode, CitState, and RegID. Why is State different than region? In this case, we're imagining that since the weather forecasting is part of geosciences, we don't care about political boundaries, we care about geographic designations. So the State can be just an attribute. Consider what other solutions we might have, why would you or would you not choose any given solution?
   
   
   
   
   2. Region will need RegID, RegionName, and Comment. You might consider what other attributes it might need.
   
   
   
   
   3. Weather Authority (WeatherAuthority)will need WAuID, WAuName, and WAuWebsite.
   
   
   
   
   4. Weather Maximums (WeatherMaximums) will need WMaID and WeaID. For a more robust solution, we could possibly consider more values here, but right now, it's just going to be noting whether there has been a historic high on a given date.
   
   
   
   
   5. Weather Minimums (WeatherMinimums) will need WMiID and WeaID). Same as Weather Maximums for intent.
   
   
   
   
   6. Optional - Weather Events is a bit of a future proofing table. I want to demonstrate that sometimes we create tables but don't have the data for it. For example, we don't know whether a tornado counts or if we need specific other values. We could also input this several ways, but for right now, we're going to populate it sparsely. We'll have WEvID, WEvType, and WEvComment. Again, there are a number of ways we could handle this, we are going with the simplest method for right now.
      
      
      
      
      1. Weather Events will be related to City in a Many-to-Many relationship (that is, a given weather event can happen to many cities, and each city can have several weather events). This means we will need a transaction table to convert the Many-to-Many relationship to a Many-to-One and a One-to-Many (sometimes referred to as a Many-to-One-to-Many). We will call this transaction table WEvCitTransaction, and it will have the fields WEvCitID, WEvID, CitID, WEvCitStart, and WEvCitStop.
      
      
      
      
      2. For now, we'll simply populate Weather Events with (0, Precipitation, Any Type of Precipitation). We'll enter one example transaction table value between precipitation starting and stopping. We have two options at this point: we can either hire someone to manually enter data into when given weather events start and stop, or we can automate the process as we build out the broader example.
      
      
      
      
      
      
      
      
   
   
   
   
   7. Double check that we've covered everything, and the data has been covered.
   
   
   
   
   8. Let's export the current SQL environment, and see if that brings anything of importance up (hopefully not!).
   
   
   
   
   
   
   
   

Problem 2

Gather data for two more days of weather data from two different cities. Enter them into the database you've created. You have a decision to answer: is it faster to do so manually, or is there a more automated way that will be more effective. What are the pros and the cons of each approach? You should now have a total of 4 cities worth of data in your database (Individual Project 2, Problems 1 and 2, and Individual Project 3, problem 2).

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.