Problem 1This problem is short: Create a clean and professional looking ERD from the existing Individual Project 3 database. The easiest way to approach this is to do two things at once: take each...

Problem 1

This problem is short: Create a clean and professional looking ERD from the existing Individual Project 3 database. The easiest way to approach this is to do two things at once: take each descriptive step and double check that's what our database looks like and then to convert it to an entity in an ERD. The steps from the previous Individual Project have been replicated below for easy referral.

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?
   
   
   
   
   
   
   
   




3. Region will need RegID, RegionName, and Comment. You might consider what other attributes it might need.




4. Weather Authority (WeatherAuthority)will need WAuID, WAuName, and WAuWebsite.




5. 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.




6. Weather Minimums (WeatherMinimums) will need WMiID and WeaID). Same as Weather Maximums for intent.




7. 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.
   
   
   
   
   
   
   
   




8. 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.

Problem 2

Similarly, for problem 2, we'll be returning to the comic book store and tying everything together into a single database. In this case, it's possible to do directly in SQL. But similar tasks are easier to do with an ERD. You don't need to create the SQL for the combined database, the ERD will suffice.

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.

PLEASE NOTE THAT THIS ASSIGNMENT IS A CONTINUATION OF MY PREVIOUS ASSIGNMENT SUBMITTED TO YOU. Thanks.